學習Excel技術，關注微信公眾號：
excelperfect
我們知道，在Excel中，日期是以序號數字來存儲的，雖然你在工作表中看到的是「2020-3-31」，而Excel中存儲的實際上是「43921.00」，整數部分是日期的序號，小數部分是當天時間的序號。這樣方便了日期的表示和存儲，但也同樣帶來了一些問題，例如我們以為是「2020-3-31」，因此會將數據直接與之比較，導致錯誤的結果。本文舉一個案例來講解公式中日期的處理方式。
如下圖1所示，計算2020年3月31日對應數據的平均值。
圖1
如果使用數組公式：
=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")
很有意思的討論！