excel公式中日期的处理引发的探索！我们知道，在Excel中，日期是以序号数字来存储的，虽然你在工作表中看到的是“2020-3-31”，而Excel中存储的实际上是“43921.00”，整数部分是日期的序号，小数部分是当天时间的序号。这样方便了日期的表示和存储，但也同样带来了一些问题，例如我们以为是“2020-3-31”，因此会将数据直接与之比较，导致错误的结果。本文举一个案例来解公式中日期的处理方式。
如下所示，计算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})
文章内容由WPS之家(wps.qiangguoshuo.com)收集于网络，希望能为您提供帮助。
返回不正确的结果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-3-31″)
转载声明：本站发布文章及版权归原作者所有，转载本站文章请注明文章来源！
