连载20：《Excel 数据分析之道：让你的数据更有说服力》连载（20）

1.6.2   处理日期数据

1  处理动态日期

n 案例1-24

图1-89是一个销售流水账，现在要求动态计算截止到今天的累计销售额，此时，单元格E1和E2的计算公式分别为：

单元格E2：=SUMPRODUCT((A3:A37<=TODAY())*B3:B37)

图1-89  TODAY函数的应用

2  拆分日期

要把一个日期拆分成年、月、日数字，可以使用YEAR函数、MONTH函数和DAY函数。

以“案例1 -24 ” 所示的数据为例，要计算上个月的销售总额，则单元格E3的计算公式如下，如图1-90所示：

=SUMPRODUCT((MONTH(A3:A37)=MONTH(TODAY())-1)*B3:B37)

图1-90  MONTH函数的应用

3  合并日期

如果要把3个分别表示年、月、日的数字组合成一个日期，就需要使用DATE函数。比如，年、月、日3个数字分别是2010、4、30，则日期公式为：

=DATE(2010,4,30)

4  判断周次

如果要判断某个日期是该年份的第几周，可以使用WEEKNUM 函数，语法为：

=WEEKNUM（日期，类别）

当参数“类别”省略或为1时，表示将星期日作为一个星期的起始日；当参数“类别”为2时，表示将星期一作为一个星期的起始日。

例如： 2010 年 4 月 30 日 是2010年的WEEKNUM(" 2010-4-30 ",2)=18周

n 案例1-25

以“案例1 -24 ” 所示的数据为例，要计算本周和上周的销售总额，则需要插入一个辅助列，以计算出每个日期对应的周次数，即在单元格C3输入下面的公式，并复制到最后一行：

=WEEKNUM(A3,2)

然后就可以根据C列的周次数字进行判断，计算本周和上周的销售总额，公式如下：

单元格F4：=SUMIF(C:C,WEEKNUM(TODAY(),2)-1,B:B)

计算结果如图1-91所示。

图1-91  计算周销售额

5  判断星期几

要判断某个日期是星期几，需要使用WEEKDAY函数。这个函数常常用在设计日程安排表，或者制作相关的报表方面。

WEEKDAY函数用于获取某日期为星期几。默认情况下，其值为1（星期天）到7（星期六）之间的整数。语法如下：

=WEEKDAY(日期, [类型])

参数Serial_number为日期序列号，可以是日期数据或日期数据单元格得引用。

参数“类型”为确定返回值类型的数字，如下所示：

3：数字0表示星期一，1表示星期二，6表示星期日。

         =WEEKDAY(" 2010-4-10 ",2)= 6

从我国的习惯来说，将参数Return_type设置为2是恰当的。

n 案例1-26

以“案例1 -24 ” 所示的数据为例，要了解分析2010年4月份每个星期几的销售分布，这样可以了解商品在星期几销售较好或者较差。如图1-92所示，此时，相关单元格的计算公式分别为：

单元格E4：

=SUMPRODUCT((MONTH(A4:A38)=4)*(WEEKDAY(A4:A38,2)=2)*B4:B38)

单元格E5：

=SUMPRODUCT((MONTH(A5:A39)=4)*(WEEKDAY(A5:A39,2)=3)*B5:B39)

单元格E6：

=SUMPRODUCT((MONTH(A6:A40)=4)*(WEEKDAY(A6:A40,2)=4)*B6:B40)

单元格E7：

=SUMPRODUCT((MONTH(A7:A41)=4)*(WEEKDAY(A7:A41,2)=5)*B7:B41)

单元格E8：

=SUMPRODUCT((MONTH(A8:A42)=4)*(WEEKDAY(A8:A42,2)=6)*B8:B42)

单元格E9：

=SUMPRODUCT((MONTH(A9:A43)=4)*(WEEKDAY(A9:A43,2)=7)*B9:B43)

图1-92  按星期几汇总计算

6  计算某个具体日期

当需要计算某个具体的日期时，比如计算指定日期往前或往后几个月的日期，或者计算指定日期往前或往后几个月的特定月份的月底日期，就可以使用EDATE函数和EOMONTH函数。

EDATE函数用于获取指定日期往前或往后几个月的日期。语法如下：

=EDATE（开始日期，几个月)

例如：

2010 年4 月30 日 之后3个月的日期：=EDATE(" 2010-4-30 ",3)，为 2010-7-30

2010 年 4 月 30 日 之前3个月的日期：=EDATE(" 2007-4-12 ",-3)，为 2010-1-30

EOMONTH函数用于获取指定日期往前或往后几个月的特定月份的月底日期。语法为：

=EOMONTH（开始日期，几个月)

例如：

2010 年4 月30 日 之后3个月的月底日期：= EOMONTH(" 2010-4-30 ",3)，为 2010-7-31

2010 年 4 月 30 日 之前3个月的月底日期：= EOMONTH(" 2010-4-30 ",-3)，为 2010-1-31

获取当月的最后一天日期：=EOMONTH(TODAY(),0)

n 案例1-27

图1-93是计算合同到期日的表格，其中单元格D2的计算公式为：

=EDATE(B2,C2*12)-1

图1-93  计算合同到期日