Excel Data Analysis 简明教程

Cleaning Data Containing Date Values

从不同来源获取的数据可能包含日期值。在本章中，你将了解如何准备包含数据值以供分析的数据。

你将了解：

  1. 日期格式 序列格式中的日期不同月-日-年格式

  2. 将序列格式中的日期转换为月-日-年格式

  3. 将月-日-年格式中的日期转换为序列格式

  4. Obtaining Today’s Date

  5. Finding a Workday after specified Days

  6. Customizing the Definition of a Weekend

  7. Number of Workdays between two given Dates

  8. Extracting Year, Month, Day from Date

  9. Extracting Day of the Week from Date

  10. Obtaining Date from Year, Month and Day

  11. Calculating Number of Years, Months and Days between two Dates

Date Formats

Excel supports Date values in two ways −

  1. Serial Format

  2. In different Year-Month-Day Formats

You can convert −

  1. A Date in Serial Format to a Date in Year-Month-Day Format

  2. A Date in Year-Month-Day Format to a Date in Serial Format

Date in Serial Format

A Date in serial format is a positive integer that represents the number of days between the given date and January 1, 1900. Both the current Date and January 1, 1900 are included in the count. For example, 42354 is a Date that represents 12/16/2015.

Date in Month-Day-Year Formats

Excel supports different Date Formats based on the Locale (Location) you choose. Hence, you need to first determine the compatibility of your Date formats and the Data Analysis at hand. Note that certain Date formats are prefixed with *(asterisk) −

  1. Date formats that begin with *(asterisk) respond to changes in regional date and time settings that are specified for the operating system

  2. Date formats without an *(asterisk) are not affected by operating system settings

For understanding purpose, you can assume United States as the Locale. You find the following Date formats to choose for the Date - 8th June, 2016 −

  1. *6/8/2016 (affected by operating system settings)

  2. *Wednesday, June 8, 2016 (affected by operating system settings)

  3. 6/8

  4. 6/8/16

  5. 06/08/16

  6. 8-Jun

  7. 8-Jun-16

  8. 08-Jun-16

  9. Jun-16

  10. June-16

  11. J

  12. J-16

  13. 6/8/2016

  14. 8-Jun-2016

If you enter only two digits to represent a year and if −

  1. 数字大于或等于 30，Excel 假设数字代表 20 世纪的年份。

  2. 数字小于 30，Excel 假设数字代表 21 世纪的年份。

例如，1/1/29 被视为 2029 年 1 月 1 日，1/1/30 被视为 1930 年 1 月 1 日。

Converting Dates in Serial Format to Month-Day-Year Format

要从序列格式将日期转换为月-日-年格式，请按照以下步骤操作：

  1. 单击 Number 对话框中的 Format Cells 选项卡。

  2. Click Date under Category.

  3. 选择 Locale 。可用的 Date 格式将作为列表显示在 Type 下方。

  4. 单击 Type 下方的 Format 以在 Sample 旁边的框中查看预览。

converting dates in serial format

选择格式后，单击 OK.

Converting Dates in Month-Day-Year Format to Serial Format

您可以使用两种方式将月-日-年格式的日期转换为序列格式：

  1. 使用 Format Cells 对话框

  2. Using Excel DATEVALUE function

Using Format Cells dialog box

  1. 单击 Number 对话框中的 Format Cells 选项卡。

  2. Click General under Category.

format cells

Using Excel DATEVALUE Function

您可以使用 Excel DATEVALUE 函数将 Date 转换为 Serial Number 格式。您需要用引号将 Date 参数括起来。例如，

=DATEVALUE("6/8/2016") 结果为 42529

Obtaining Today’s Date

如果您需要根据今天的日期进行计算，只需使用 Excel 函数 TODAY()。结果反映了它使用的日期。

TODAY() 函数在 2016 年 5 月 16 日使用了以下屏幕截图：

obtaining todays date

Finding a Workday after Specified Days

您可能需要根据您的工作日进行某些计算。

工作日不包括周末和任何节假日。这意味着，如果您可以定义您的周末和节假日，那么您所做的任何计算都将基于工作日。例如，您可以计算发票到期日、预期的交货时间、下一次会议日期等。

您可以使用 Excel WORKDAY 和 WORKDAY.INTL 函数执行此类操作。

S.No.

Function & Description

1.

WORKDAY 返回指定天数前或后的日期的序列号

2.

WORKDAY.INTL 使用参数指示哪些天和多少天是周末日，返回指定天数前或后的日期的序列号

例如，您可以使用函数 TODAY 和 WORKDAY 指定从今天开始的第 15 个工作日（下图截取于 2016 年 5 月 16 日）。

finding workday

假设 2016 年 5 月 25 日和 2016 年 6 月 1 日是节假日。那么你的计算如下 −

calculation

Customizing the Definition of a Weekend

默认情况下，周末是周六和周日，即两天。您还可以使用 WORKDAY.INTL 函数选择自定义周末。您可以通过周末编号指定自己的周末，此编号对应于下表中指定的周末日期。您无需记住这些编号，因为当您开始键入该函数时，您可以在下拉列表中获得编号和周末日期。

Weekend Days

Weekend-number

Saturday, Sunday

1 or omitted

Sunday, Monday

2

Monday, Tuesday

3

Tuesday, Wednesday

4

Wednesday, Thursday

5

Thursday, Friday

6

Friday, Saturday

7

Sunday only

11

Monday only

12

Tuesday only

13

Wednesday only

14

Thursday only

15

Friday only

16

Saturday only

17

假设只有周五是周末，则需要在 WORKDAY.INTL 函数中使用编号 16。

workday intl function

Number of Workdays between two given Dates

可能需要计算两个日期之间的工作日数量，例如，需要计算按天支付的合同工的工资。

您可以使用 Excel 函数 NETWORKDAYS 和 NETWORKDAYS.INTL 查找两个日期之间的工作日数量。与 WORKDAYS 和 WORKDAYS.INTL 的用法相同，NETWORKDAYS 和 NETWORKDAYS.INTL 允许您指定节假日，另外，使用 NETWORKDAYS.INTL 还可以指定周末。

S.No.

Function & Description

1.

NETWORKDAYS 返回两个日期之间的完整工作日数

2.

NETWORKDAYS.INTL 使用参数返回两个日期之间的完整工作日数，这些参数表明哪几天是周末以及有几天是周末

您可以使用函数 TODAY 和 NETWORKDAYS 计算今天和另一个日期之间的工作日数量。在下图截屏中，今天是 2016 年 5 月 16 日，结束日期是 2016 年 6 月 16 日。2016 年 5 月 25 日和 2016 年 6 月 1 日是节假日。

calculate workdays

同样，假设周末是周六和周日。您可以根据自己的定义设置周末，并使用 NETWORKDAYS.INTL 函数计算两个日期之间的工作日数量。在下图截屏中，只将周五定义为周末。

weekend friday

Extracting Year, Month, Day from Date

您可以从日期列表中的每个日期中提取对应的星期、月份和年份，方法是使用 Excel 函数 DAY、MONTH 和 YEAR。

例如，考虑以下日期 −

dates

您可以从这些日期中分别提取星期、月份和年份，如下所示 −

extracting year month day from date

Extracting Day of the Week from Date

您可以从日期列表中的每个日期中提取对应的星期，方法是使用 Excel WEEKDAY 函数。

考虑上面给出相同的示例。

extracting day of week from date

Obtaining Date from Year, Month and Day

您的数据可能分别包含年、月和日信息。您需要将这三个值组合起来以获取日期，以便进行任何计算。您可以使用 DATE 函数获取日期值。

考虑以下数据 -

obtaining date from year month and day

使用 DATE 函数获取 DATE 值。

date function

Calculating Years, Months and Days between two Dates

您可能需要计算从给定日期开始逝去的时间。您可能需要以年、月和日格式的信息。一个简单的示例是计算一个人的当前年龄。它的计算方式是生日和今天之间的差。您可以使用 Excel 函数 DATEDIF、TODAY 和 CONCATENATE 进行此操作。

calculate time lapsed

输出如下 −

calculate time lapsed output