學不完．教不停．用不盡

有網友問到如下圖左的 Excel 資料清單中，如果要將編號為奇數／偶數的項目，分別取出計算其平均，該如何處理？其中有部分儲存格的內容為空白。

計算平均時，如果儲存格的內容為空白，理應不併入計算。而 AVERAGE 函數，也是會將儲存格為空白儲存格者不列入平均。

先選取儲存格A1:B20，按一下 Ctrl+Shfit+F3 鍵，定義名稱：編號、數值。

【錯誤結果】

儲存格E2：=SUMPRODUCT((MOD(編號,2)=1)*數值)/SUMPRODUCT(--(MOD(編號,2)=1))

MOD(編號,2)=1：判斷編號除以 2 的餘數是否為 1 (該數為奇數)，得到一個 TRUE／FALSE 的陣列。

SUMPRODUCT((MOD(編號,2)=1)*數值)：計算編號為奇數者的數值總和。

SUMPRODUCT(--(MOD(編號,2)=1))：藉由「--」運算，將計算結果為 TRUE／FALSE 的陣列轉換為 1／0 的陣列。

儲存格E3：=SUMPRODUCT((MOD(編號,2)=0)*數值)/SUMPRODUCT(--(MOD(編號,2)=0))

MOD(編號,2)=0：判斷編號除以 2 的餘數是否為 0 (該數為偶數)，得到一個 TRUE／FALSE 的陣列。

但是以上的公式，會將儲存格B4和儲存格B13這二個空白儲存格的內容視為 0，在計算平均值時會產生錯誤。

【正確結果】

為了將空白儲存格不在計算平均時併入計算，將公式稍做修改：

儲存格E4：=SUMPRODUCT((MOD(編號,2)=1)*數值)/SUMPRODUCT((NOT(ISBLANK(數值)))*(MOD(編號,2)=1))

NOT(ISBLANK(數值))：透過 ISBLANK 函數來判斷數值陣列中是否為空白儲存格，得到一個 TRUE／FALSE 陣列，再藉由 NOT 函數將結果轉換為 FALSE／TRUE 陣列。

儲存格E5：=SUMPRODUCT((MOD(編號,2)=0)*數值)/SUMPRODUCT((NOT(ISBLANK(數值)))*(MOD(編號,2)=0))