excel公式中日期的處理引發(fā)的探索！我們知道，在Excel中，日期是以序號(hào)數(shù)字來(lái)存儲(chǔ)的，雖然你在工作表中看到的是“2020-3-31”，而Excel中存儲(chǔ)的實(shí)際上是“43921.00”，整數(shù)部分是日期的序號(hào)，小數(shù)部分是當(dāng)天時(shí)間的序號(hào)。這樣方便了日期的表示和存儲(chǔ)，但也同樣帶來(lái)了一些問(wèn)題，例如我們以為是“2020-3-31”，因此會(huì)將數(shù)據(jù)直接與之比較，導(dǎo)致錯(cuò)誤的結(jié)果。本文舉一個(gè)案例來(lái)解公式中日期的處理方式。
如下所示，計(jì)算2020年3月31日對(duì)應(yīng)數(shù)據(jù)的平均值。
如果使用數(shù)組公式：
=AVERAGE($A$2:$A$20=2020-3-31,B2:B20)
得到的結(jié)果是不正確的。這個(gè)公式相當(dāng)于求單元格區(qū)域B2:B20的數(shù)值的平均值：
=SUM(B2:B20)/19
首先看看Average函數(shù)的語(yǔ)法：
AVERAGE(number1, [number2], …)
其中：
1. 參數(shù)number1，必需，想求平均值的數(shù)字、單元格引用或單元格區(qū)域。
2. 參數(shù)number2, …，可選，其他想求平均值的數(shù)字、單元格引用或單元格區(qū)域。參數(shù)個(gè)數(shù)最大可達(dá)255個(gè)。
AVERAGE將對(duì)函數(shù)或提供的區(qū)域中的數(shù)字求和，然后除以該區(qū)域中的條目或單元格的數(shù)量。
我們看上面的公式，給AVERAGE函數(shù)提供了兩個(gè)單元格區(qū)域，即：
區(qū)域1：$A$2:$A$20=2020-3-31
區(qū)域2：B2:B20
1. 對(duì)于$A$2:$A$20=2020-3-31，將解析為數(shù)組：
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
本來(lái)我們以為單元格區(qū)域A2:A20中的前6個(gè)單元格應(yīng)該與2020-3-31相匹配，但事實(shí)上返回FALSE，這表明不匹配。
如果我們將其修改為：$A$2:$A$20=”2020-3-31″，仍會(huì)被解析為數(shù)組：
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
仍然與該區(qū)域前6個(gè)單元格不匹配。
我們?cè)賹⑵湫薷臑椋?$A$2:$A$20=DATE(2020,3,31)，將解析為數(shù)組：
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
可以看到，已正確與該區(qū)域相應(yīng)的6個(gè)單元格相匹配。
2. 對(duì)于區(qū)域B2:B20，將解析為數(shù)組：
{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}
即由該區(qū)域單元格中的值組成的數(shù)組。
3. 因此，對(duì)于數(shù)組公式：
=AVERAGE($A$2:$A$20=2020-3-31,B2:B20)
使用上述中間數(shù)組替換：
=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})
返回不正確的結(jié)果10752.27。
4. 我們來(lái)看看正確的公式：
=AVERAGE(IF($A$2:$A$20=DATE(2020,3,31),$B$2:$B$20))
這也是一個(gè)數(shù)組公式。
如果單元格區(qū)域A2:A20中的值與日期“2020年3月31日”匹配，則返回TRUE，否則返回FALSE。傳遞給IF函數(shù)后，返回單元格區(qū)域B2:B20中對(duì)應(yīng)的值，并對(duì)這些值求平均值。公式解析過(guò)程如下：
=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}))
轉(zhuǎn)換為：
=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})
得到正確的結(jié)果10593.82。等價(jià)于公式：
=AVERAGE(B2:B7)
5. 我們注意到，上面的公式中我們沒(méi)有提供IF函數(shù)的參數(shù)value_if_false的值，這是有原因的。
如果我們添加IF函數(shù)的參數(shù)value_if_false，將公式修改為：
=AVERAGE(IF($A$2:$A$20=DATE(2020,3,31),$B$2:$B$20,0))
會(huì)得到不正確的結(jié)果3345.42。
這是因?yàn)镮F函數(shù)會(huì)將條件為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})
所得結(jié)果為上述值求和后除以19，而不是6。
與上述IF函數(shù)沒(méi)有指定參數(shù)value_if_false的值相比較，可以看出，AVERAGE函數(shù)忽略提供給其的數(shù)組中的FALSE值，并且不會(huì)將其計(jì)入要平均的數(shù)值。
6. 其實(shí)，Excel 2007及以后的版本中引入了一個(gè)函數(shù)AVERAGEIFS，可以很好地解決上述問(wèn)題，其公式為：
=AVERAGEIFS(B2:B20,A2:A20,DATE(2020,3,31))
或者：
=AVERAGEIFS(B2:B20,A2:A20,”2020-3-31″)
