Excel Data Analysis 简明教程

Cleaning Data Containing Date Values

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

The data that you obtain from different sources might contain date values. In this chapter, you will understand how to prepare your data that contains data values for analysis.

你将了解：

You will learn about −

  1. Date Formats Date in Serial FormatDate in different Month-Day-Year Formats

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

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

  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 支持通过两种方式解析 Date 值−

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

按序列格式的 Date 是一个正整数，表示给定日期与 1900 年 1 月 1 日之间的天数。当前 Date 与 1900 年 1 月 1 日都包含在计数中。例如，42354 是一个 Date ，代表 2015 年 12 月 16 日。

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 支持基于您选择的 Locale （位置）的不同 Date 格式。因此，您需要首先确定您的 Date 格式和手头的数据分析的兼容性。请注意，某些 Date 格式以 *(星号) 为前缀−

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

为了理解，你可以把美国作为地点。你会发现以下 {s0} 格式可供选择 {s1} - 2016 年 6 月 8 日 −

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. The digits are 30 or higher, Excel assumes the digits represent years in the twentieth century.

  2. The digits are lower than 30, Excel assumes the digits represent years in the twenty-first century.

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

For example, 1/1/29 is treated as January 1, 2029 and 1/1/30 is treated as January 1, 1930.

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

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

To convert dates from serial format to Month-Day-Year format, follow the steps given below −

  1. Click the Number tab in the Format Cells dialog box.

  2. Click Date under Category.

  3. Select Locale. The available Date formats will be displayed as a list under Type.

  4. Click on a Format under Type to look at the preview in the box adjacent to Sample.

converting dates in serial format

选择格式后，单击 OK.

After choosing the Format, click OK.

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

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

You can convert dates in Month-Day-Year format to Serial format in two ways −

  1. Using Format Cells dialog box

  2. Using Excel DATEVALUE function

Using Format Cells dialog box

  1. Click the Number tab in the Format Cells dialog box.

  2. Click General under Category.

format cells

Using Excel DATEVALUE Function

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

You can use Excel DATEVALUE function to convert a Date to Serial Number format. You need to enclose the Date argument in “”. For example,

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

=DATEVALUE ("6/8/2016") results in 42529

Obtaining Today’s Date

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

If you need to perform calculations based on today’s date, simply use the Excel function TODAY (). The result reflects the date when it is used.

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

The following screenshot of TODAY () function usage has been taken on 16th May, 2016 −

obtaining todays date

Finding a Workday after Specified Days

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

You might have to perform certain calculations based on your workdays.

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

Workdays exclude weekend days and any holidays. This means if you can define your weekend and holidays, whatever calculations you do will be based on workdays. For example, you can calculate invoice due dates, expected delivery times, the next meeting date, etc.

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

You can use Excel WORKDAY and WORKDAY.INTL functions for such operations.

S.No.

Function & Description

1.

WORKDAY Returns the serial number of the date before or after a specified number of workdays

2.

WORKDAY.INTL Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days

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

For example, you can specify the 15th working day from today (the screenshot below is taken on 16th May 2016) using the Functions TODAY and WORKDAY.

finding workday

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

Suppose 25th May 2016 and 1st June 2016 are holidays. Then, your calculation will be as follows −

calculation

Customizing the Definition of a Weekend

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

By default, weekend is Saturday and Sunday, i.e. two days. You can also optionally define your weekend with the WORKDAY.INTL function. You can specify your own weekend by a weekend-number that corresponds to the weekend days as given in the table below. You need not remember these numbers, because when you start typing the function, you get a list of numbers and the weekend days in the drop-down list.

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。

Suppose, if weekend is Friday only, you need to use the number 16 in the WORKDAY.INTL function.

workday intl function

Number of Workdays between two given Dates

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

There might be a requirement to calculate the number of workdays between two dates, for example, in the case of calculating payment to a contract employee who is paid on per day basis.

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

You can find the number of workdays between two dates with the Excel functions NETWORKDAYS and NETWORKDAYS.INTL. Just as in the case of WORKDAYS and WORKDAYS.INTL, NETWORKDAYS and NETWORKDAYS.INTL allow you to specify holidays and with NETWORKDAYS.INTL you can additionally specify the weekend.

S.No.

Function & Description

1.

NETWORKDAYS Returns the number of whole workdays between two dates

2.

NETWORKDAYS.INTL Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days

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

You can calculate the number of workdays between today and another date with the functions TODAY and NETWORKDAYS. In the screen shot given below, today is 16th May 2016 and end date is 16th June 2016. 25th May 2016 and 1st June 2016 are holidays.

calculate workdays

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

Again, the weekend is assumed to be Saturday and Sunday. You can have your own definition for weekend and calculate the number of workdays between two dates with the NETWORKDAYS.INTL function. In the screen shot given below, only Friday is defined as weekend.

weekend friday

Extracting Year, Month, Day from Date

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

You can extract from each date in a list of dates, the corresponding day, month and year using the excel functions DAY, MONTH and YEAR.

例如，考虑以下日期 −

For example, consider the following dates −

dates

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

From each of these dates, you can extract day, month and year as follows −

extracting year month day from date

Extracting Day of the Week from Date

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

You can extract from each date in a list of dates, the corresponding day of the week with Excel WEEKDAY function.

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

Consider the same example given above.

extracting day of week from date

Obtaining Date from Year, Month and Day

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

You data might have the information about Year, Month and Day separately. You need to get the date combining these three values to perform any calculation. You can use the DATE function for getting the date values.

考虑以下数据 -

Consider the following data −

obtaining date from year month and day

使用 DATE 函数获取 DATE 值。

Use the DATE function to obtain DATE values.

date function

Calculating Years, Months and Days between two Dates

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

You might have to calculate the time lapsed from a given date. You might need this information in the form of years, months and days. A simple example would be calculating the current age of a person. It is effectively the difference between the birth date and today. You can use Excel DATEDIF, TODAY and CONCATENATE functions for this purpose.

calculate time lapsed

输出如下 −

The output is as follows −

calculate time lapsed output