2015年8月30日 星期日

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

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

沒有留言:

張貼留言

好康東東