excel公式中日期的处理引发的探索
2023-01-26 餐饮美食网 【 字体:大 中 小 】
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″)
如何自制蛋挞
肉火烧面怎么和面
奶茶几分糖好喝
过桥米线是哪里的?
面霜和乳液的区别
怎么摘隐形眼镜
粉饼和散粉的区别
冷烫和热烫的区别
高品质香水如何鉴别?用三步就可以解决
如何去除黑眼圈