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