學不完．教不停．用不盡

在 Excel 中用來計算平均的方式很多，也有函數可以直接使用。但是，如果要依據多個條件來篩選出數值再計算平均，或許就會比較傷腦筋。

如下圖中的資料表，如果只想取出乙和丁的數值，並計算二者所有數值的平均，該如何處理？通常公式較長時，配合陣列來設計可以縮短公式，又該如何處理？

 

【公式設計與解析】

在儲存格D2中例舉以下幾種公式寫法：

(1) 使用 SUMPRODUCT 函數

=(SUMPRODUCT((人員="乙")*數值)+SUMPRODUCT((人員="丁")*數值))/
(SUMPRODUCT(--(人員="乙"))+SUMPRODUCT(--(人員="丁")))

(A) (SUMPRODUCT((人員="乙")*數值)+SUMPRODUCT((人員="丁")*數值))

用以計算人員是「乙」的數值總和＋人員是「丁」的數值總和

(B) (SUMPRODUCT(--(人員="乙"))+SUMPRODUCT(--(人員="丁")))

用以計算人員是「乙」的個數＋人員是「丁」的個數。其中，公式中的「--」，用以將判斷式傳回的 TRUE／FALSE 陣列轉換為 1／0 陣列。

將 (A)式／(B)式，即為乙和丁的數值平均值。

 

(2) 使用 SUMPRODUCT 函數＋陣列表示法

=SUMPRODUCT((人員={"乙","丁"})*數值)/SUMPRODUCT(--(人員={"乙","丁"}))

使用陣列 {"乙","丁"}，可以將兩個計算式變成一個。

 

(3) 使用陣列公式

｛=AVERAGE(IF((人員="乙")+(人員="丁"),數值,FALSE))｝

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動產生「｛｝」。(以下做法一致)

其中的 FALSE 參數不可以留下空白或是以 0 表示，否則每一個數值都會併入平均計算。

 

(4) 使用陣列公式＋陣列表示法

｛=AVERAGE(IF((人員={"乙","丁"}),數值,FALSE))｝

透過 {"乙","丁"} 將公式縮短，可以將公式中的兩個項目變成一個項目。

或

｛=AVERAGE(IF(ISNUMBER(MATCH(人員,{"乙","丁"},0)),數值))｝

透過 MATCH 函數比對人員中是否為乙或丁，若是則傳回對應的數值，若否則傳回錯誤訊息，二者組成一個陣列。再使用 ISNUMBER 函數取出是數值的內容，最後透過 AVERAGE 函數來計算被取出數值的平均。

或

｛=AVERAGE(IF(NOT(ISERROR(MATCH(人員,{"乙","丁"},0))),數值))｝

原理同上式，改用 NOT 函數和 ISERROR 函數來找出非錯誤訊息的儲存格，相當於找出數值的儲存格。