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

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

Excel-計算多條件的篩選的數值平均(陣列公式,SUMPRODUCT,MATCH)

 

【公式設計與解析】

在儲存格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 函數來找出非錯誤訊息的儲存格,相當於找出數值的儲存格。

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(6) 人氣()