excel公式中日期的处理引发的探索

网友投稿 · 2023-11-22 12:11 · 行业资讯 · 阅读 349

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

excel公式中日期的处理引发的探索

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

如果使用数组公式：

=AVERAGE($A$2:$A$20=2020-3-31,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-3-31

区域2：B2:B20

1. 对于$A$2:$A$20=2020-3-31，将解析为数组：

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

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

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

{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-3-31,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值，并且不会将其计入要平均的数值。