星期三, 12月 05, 2007

用SUMPRODUCT函數做「兩條件加總」

Major John @ 星期三, 12月 05, 2007
最近工作上剛好碰到需要使用到這樣的功能,那時候有點趕著要,所以我用自己的解法,用最笨、最原始的方式做出來,那樣會需要好幾個欄位當中繼欄位,才能得到最後的結果。

單一條件的加總,可以用SUMIF()這個函數輕鬆達到。很簡單。我就是用這個函數解決問題的。但是就是多了兩欄中繼欄位,表格不夠漂亮啦!(←雖然可以隱藏欄位,囧)

在Google搜尋到Excel Help論壇的解法,酷!
參考 兩條件加總 from Excel Help。




假設有筆資料如上圖。我想要統計出「楊三郎」這個人休假的總時數,用SUMIF解決:
=SUMIF(A3:A24,"楊三郎",C3:C24)
是指:從A3~A24欄位裡面找出「楊三郎」這關鍵字,有這關鍵字的對應到C3~C24欄位去找值,並將值全部加起來。

但是,如果還要加上一個條件:我想要統計「楊三郎」的「特休假」放的總時數,SUMIF就辦不到了。(也許辦得到啦!但我不會。 :p)
這裡需要SUMPRODUCT()這函數來解了!
範例檔:(強烈建議下載回去看,比較好懂)


需要用到的函數:
  1. SUMPRODUCT()
  2. INDIRECT()
SUMPRODUCT(),望文生義就是『相乘(PRODUCT)之後再相加(SUM)』。拿現成的這筆資料來說,陣列「C3:C5」與陣列「C6:C8」這兩個陣列做SUMPRODUCT。
=SUMPRODUCT(C3:C5,C6:C8)
結果為60。
其實Excel是對這兩個矩陣做「=C3*C6+C4*C7+C5*C8」這樣的動作啦!這和線性代數裡的矩陣計算很像唄!!

  • 各陣列必須有相同的維度 (相同的列數,相同的欄數)。否則 SUMPRODUCT 函數會傳回錯誤值 #VALUE!。
  • SUMPRODUCT 函數會將所有非數值資料的陣列元素當成 0 來處理。

哇靠,這樣看起來這個函數只能處理數字嘛!?怎麼判斷文字?判斷某些關鍵字存在的話,就加起來呢?把所有「楊三郎」和「特休假」這兩個關鍵字條件丟進SUMPRODUCT咧?…………用真假值!TRUE=1、FALSE=0啊!

Final的正解是:=SUMPRODUCT((INDIRECT("A3:A50")="楊三郎")*(INDIRECT("B3:B50")="特休假")*(INDIRECT("C3:C50")))
在A3:A50中等於「楊三郎」這個關鍵字的就是TRUE,也就是1。
在B3:B50中等於「特休假」的就為TRUE,1。
最後一個不需要判斷真假值,這個就直接是數字用來相加的了。

用表格來表示SUMPRODUCT出來的矩陣:(只是舉例,非原始那筆資料)
真假1真假2數字
楊三郎補休假3103
馬新中特休假4014
陳國義特休假8018
楊三郎特休假4114
楊三郎補休假4104
真假1:找到「楊三郎」的就是1(TRUE),如果不是,就是0;真假2:找到「特休假」就是1(TRUE),如果不是就是0;數字不用判斷真假值。

精彩的來了,對這三個陣列做「相乘之後相加」得到什麼呢?
1*0*3 + 0*1*4 + 0*1*8 + 1*1*4 + 1*0*4 = 4

個人認為,最屌的想法是把文字的東西轉化成數字的巧妙結合。酷!

總之,這個問題的解法就是
=SUMPRODUCT((INDIRECT("A3:A50")="楊三郎")*(INDIRECT("B3:B50")="特休假")*(INDIRECT("C3:C50")))


補充說一下INDIRECT()這函數在之前有寫過「轉置同時保持連結 」。

3 則留言:

  1. 你好..我有一個問題...
    照理說SUMPRODUCT(ARR1, ARR2, ARR3,...)
    不同array間是使用 , 相隔..
    但這個例子卻是使用 * 來直接做運算?
    看起來似乎是只有一個array..只是這個array已經先乘了..
    是否換成 , 結果也一樣呢?

    回覆刪除
  2. 這個方法裡面用的矩陣還有做個判斷ㄟ,要等於你篩選的條件才讓SUMPRODUCT處理,INDIRECT("B3:B50")=特休假,這個判斷。
    如果只用逗點分開矩陣的話,可以做這個判斷嗎?....我不確定。

    回覆刪除
  3. 你好~可以請教嗎?
    K8*L8+M8*N8+........+U8*V8
    這種狀況要怎麼用 SUMPRODUCT ?

    回覆刪除