贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

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

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

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


留言列表 (6)

發表留言
  • 惠婷
  • 老師您好,請問一般 EXCEL 在拖曳選擇列的時候會秀出列數。
    如選擇四列,會秀出 4R;選擇四欄時,會秀出 4C。
    現在我 4R、4C 這個小方格不見了 (在做拖曳的時候不會顯示)
    我試了好多方法,爬了好多文但還是找不回它。
    能向您請教,該如果找到這個功能呢?
    感激不盡。
  • s3579513
  • 版大好。想請問一下。如果。我是要選人員丁群組中,411數值有幾個(COUNTIF),應該如何寫???
  • 公式:=SUMPRODUCT((人員="丁")*(數值=411))

    vincent 於 2016/02/07 16:05 回覆

  • 等老師救命的人~
  • 板大好~
    想求EXCEL公式: "I3:I12"是我要看的地方達成 5萬 顯示$200 達成10萬顯示$500 達成15萬點顯示$1000 達成20萬顯示$2000 請問公式應該怎麼打?
  • =VLOOKUP(I3,{50000,200;100000,500;150000,1000},2)

    vincent 於 2016/03/10 20:35 回覆

  • 訪客
  • 板大,請問如果1- 100之間,我只要找出 1--2.3--------19
    22.23 24...29.
    33 ,34,35 ...39
    44 45,46-----49
    55--------------59
    66-----------------69
    77---------------------79
    88--------------------89
    99--100
    的數字,並顯出 紅色字型,如何設定,...
  • 請參考:http://isvincent.pixnet.net/blog/post/46126945

    vincent 於 2016/04/18 19:48 回覆

  • lingo
  • 有沒有基本一點只算乙或丁的教學呢?謝謝
  • 哭哭
  • 老師您好,如果是想要知道人員丁的數值最大值,不用陣列有辦法寫嗎?
  • 如果依照本例中的資料清單,可能你得使用輔助欄位,挑出丁的內容,再取其最大值。

    vincent 於 2017/11/10 15:26 回覆

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼