Excel Data Analysis 简明教程
Cleaning Data Containing Date Values
从不同来源获取的数据可能包含日期值。在本章中，你将了解如何准备包含数据值以供分析的数据。
你将了解：
-
日期格式 序列格式中的日期不同月-日-年格式
-
将序列格式中的日期转换为月-日-年格式
-
将月-日-年格式中的日期转换为序列格式
-
Obtaining Today’s Date
-
查找指定天数后的工作日
-
自定义周末的定义
-
两个给定日期之间的工作日数
-
从日期中提取年、月、日
-
从日期中提取星期
-
从年、月、日获取日期
-
计算两个日期之间的年数、月数和天数
Date Formats
Excel 支持通过两种方式解析 Date 值−
-
Serial Format
-
In different Year-Month-Day Formats
您可以转换−
-
按序列格式的 Date 为按年-月-日格式的 Date
-
按年-月-日格式的 Date 为按序列格式的 Date
Date in Serial Format
按序列格式的 Date 是一个正整数，表示给定日期与 1900 年 1 月 1 日之间的天数。当前 Date 与 1900 年 1 月 1 日都包含在计数中。例如，42354 是一个 Date ，代表 2015 年 12 月 16 日。
Date in Month-Day-Year Formats
Excel 支持基于您选择的 Locale （位置）的不同 Date 格式。因此，您需要首先确定您的 Date 格式和手头的数据分析的兼容性。请注意，某些 Date 格式以 *(星号) 为前缀−
-
以 (星号) 开头的 *Date 格式会响应为操作系统指定的对区域性日期和时间设置进行的更改
-
不带 (星号) 的 *Date 格式不受操作系统设置的影响
为了理解，你可以把美国作为地点。你会发现以下 {s0} 格式可供选择 {s1} - 2016 年 6 月 8 日 −
-
*6/8/2016 （受操作系统设置的影响）
-
*2016 年 6 月 8 日星期三 （受操作系统设置的影响）
-
6/8
-
6/8/16
-
06/08/16
-
8-Jun
-
8-Jun-16
-
08-Jun-16
-
Jun-16
-
June-16
-
J
-
J-16
-
6/8/2016
-
8-Jun-2016
如果你输入仅仅两位数来表示年份而且如果 −
-
数字大于或等于 30，Excel 假设数字代表 20 世纪的年份。
-
数字小于 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
要从序列格式将日期转换为月-日-年格式，请按照以下步骤操作：
-
单击 Number 对话框中的 Format Cells 选项卡。
-
Click Date under Category.
-
选择 Locale 。可用的 Date 格式将作为列表显示在 Type 下方。
-
单击 Type 下方的 Format 以在 Sample 旁边的框中查看预览。
选择格式后，单击 OK.
Converting Dates in Month-Day-Year Format to Serial Format
您可以使用两种方式将月-日-年格式的日期转换为序列格式：
-
使用 Format Cells 对话框
-
Using Excel DATEVALUE function
Obtaining Today’s Date
如果您需要根据今天的日期进行计算，只需使用 Excel 函数 TODAY()。结果反映了它使用的日期。
TODAY() 函数在 2016 年 5 月 16 日使用了以下屏幕截图：
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 日）。
假设 2016 年 5 月 25 日和 2016 年 6 月 1 日是节假日。那么你的计算如下 −
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。
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 日是节假日。
同样，假设周末是周六和周日。您可以根据自己的定义设置周末，并使用 NETWORKDAYS.INTL 函数计算两个日期之间的工作日数量。在下图截屏中，只将周五定义为周末。
Extracting Year, Month, Day from Date
您可以从日期列表中的每个日期中提取对应的星期、月份和年份，方法是使用 Excel 函数 DAY、MONTH 和 YEAR。
例如，考虑以下日期 −
您可以从这些日期中分别提取星期、月份和年份，如下所示 −
