excel公式中日期的处理引发的探索-英雄云拓展知识分享

excel公式中日期的处理引发的探索！我们知道，在Excel中，日期是以序号数字来存储的，虽然你在工作表中看到的是“2020⑶⑶1”，而Excel中存储的实际上是“43921.00”，整数部份是日期的序号，小数部份是当天时间的序号。这样方便了日期的表示和存储，但也一样带来了一些问题，例如我们以为是“2020⑶⑶1”，因此会将数据直接与之比较，致使毛病的结果。本文举一个案例来解公式中日期的处理方式。

以下所示，计算2020年3月31日对应数据的平均值。

如果使用数组公式：

=AVERAGE($A:$A=2020⑶⑶1,B2:B20)

得到的结果是不正确的。这个公式相当于求单元格区域B2:B20的数值的平均值：

=SUM(B2:B20)/19

首先看看Average函数的语法：

AVERAGE(number1, [number2], …)

其中：

1. 参数number1，必须，想求平均值的数字、单元格援用或单元格区域。

2. 参数number2, …，可选，其他想求平均值的数字、单元格援用或单元格区域。参数个数最大可达255个。

AVERAGE将对函数或提供的区域中的数字求和，然后除以该区域中的条目或单元格的数量。

我们看上面的公式，给AVERAGE函数提供了两个单元格区域，即：

区域1：$A$2:$A$20=2020⑶⑶1

区域2：B2:B20

1. 对$A$2:$A$20=2020⑶⑶1，将解析为数组：

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

本来我们以为单元格区域A2:A20中的前6个单元格应当与2020⑶⑶1相匹配，但事实上返回FALSE，这表示不匹配。

如果我们将其修改成：$A$2:$A$20=”2020⑶⑶1″，仍会被解析为数组：

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

依然与该区域前6个单元格不匹配。

我们再将其修改成：=$A$2:$A$20=DATE(2020,3,31)，将解析为数组：

{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

可以看到，已正确与该区域相应的6个单元格相匹配。

2. 对区域B2:B20，将解析为数组：

{10552.52;10564.38;10567.33;10611.84;10624.69;10642.15;10685.98;10733.67;10779.17;10741.98;10785.89;10888.83;10836.15;10841.21;10850.36;10895.86;10907.42;10856.63;10927.07}

即由该区域单元格中的值组成的数组。

3. 因此，这种情况下，对数组公式：

=AVERAGE($A$2:$A$20=2020⑶⑶1,B2:B20)

使用上述中间数组替换：

=AVERAGE({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{10552.52;10564.38;10567.33;10611.84;10624.69;10642.15;10685.98;10733.67;10779.17;10741.98;10785.89;10888.83;10836.15;10841.21;10850.36;10895.86;10907.42;10856.63;10927.07})

返回不正确的结果10752.27。

4. 我们来看看正确的公式：

=AVERAGE(IF($A$2:$A$20=DATE(2020,3,31),$B$2:$B$20))

这也是一个数组公式。

如果单元格区域A2:A20中的值与日期“2020年3月31日”匹配，则返回TRUE，否则返回FALSE。传递给IF函数后，返回单元格区域B2:B20中对应的值，并对这些值求平均值。公式解析进程以下：

=AVERAGE(IF($A$2:$A$20=DATE(2020,3,31),$B$2:$B$20))

解析为：

=AVERAGE(IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{10552.52;10564.38;10567.33;10611.84;10624.69;10642.15;10685.98;10733.67;10779.17;10741.98;10785.89;10888.83;10836.15;10841.21;10850.36;10895.86;10907.42;10856.63;10927.07}))

转换为：

=AVERAGE({10552.52;10564.38;10567.33;10611.84;10624.69;10642.15;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})

得到正确的结果10593.82。等价于公式：

=AVERAGE(B2:B7)

5. 我们注意到，上面的公式中我们没有提供IF函数的参数value_if_false的值，这是有缘由的。

如果我们添加IF函数的参数value_if_false，将公式修改成：

=AVERAGE(IF($A$2:$A$20=DATE(2020,3,31),$B$2:$B$20,0))

会得到不正确的结果3345.42。

这是由于IF函数会将条件为FALSE的值等于0，这样上述公式解析为：

=AVERAGE({10552.52;10564.38;10567.33;10611.84;10624.69;10642.15;0;0;0;0;0;0;0;0;0;0;0;0;0})

所得结果为上述值求和后除以19，而不是6。

与上述IF函数没有指定参数value_if_false的值相比较，可以看出，AVERAGE函数疏忽提供给其的数组中的FALSE值，并且不会将其计入要平均的数值。

6. 其实，Excel 2007及以后的版本中引入了一个函数AVERAGEIFS，可以很好地解决上述问题，其公式为：

=AVERAGEIFS(B2:B20,A2:A20,DATE(2020,3,31))

或：

=AVERAGEIFS(B2:B20,A2:A20,”2020⑶⑶1″)