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

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})

返回不正确的结果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″)

赞(1)
office办公分享 » excel公式中日期的处理引发的探索

本文链接:excel公式中日期的处理引发的探索https://www.officeapi.cn/99121.html

OFFICE,天天学一点,进步多一点

联系我们