2009年1月3日 星期六

多條件式加總_SUMPRODUCT

前面證明SUM(IF())可以用在單條件式加總,其實另外一個函數SUMIF()也具有同樣的功能,但是條件在多個以上時,SUMIF()就無法勝任。此始就需要使用其他的函數,例如:SUMPRODUCT()SUM()SUM(IF())

首先,從「函數說明」中可以顯示出EXCELSUMPRODUCT()的說明,如下所述:


傳回各陣列中所有對應元素乘積的總和。
語法:

SUMPRODUCT(array1,array2,array3,...) Array1, array2, array3, ... 230個要求其對應元素乘積之和的陣列。

##CONTINUE##

1.基本操作

依照Excel的「說明」解釋此函數是陣列乘積的和,例如:A1A2的值是12B1B2的值是34, 則=SUMPRODUCT(A1:A2,B1:B2)A1×B1A2×B2,答案就是11

這個公式是:


A1×B1+A2×B2

*」與「,」所得到的結果都是一樣。


另外SUMPRODUCT()也可以像SUM(IF())一樣,執行邏輯判斷的加總。例如:在D67輸入:


=SUMPRODUCT((E$2:E$62=B67)*(D$2:D$62))

答案就與C67一樣,運算的過程都與SUM(IF())一樣。SUMPRODUCT()比較特殊,它也是以陣列公式計算,但是不用按Ctrl-Shift-Enter。注意引數中間需要用「*」隔開。

2.多條件式總和操作
SUMPRODUCT()有個特殊功能,就是可以進行多條件總和,如果條件是計算「段政存」的「影印卡」支出多少金額?就可以利用多條件進行邏輯判斷,然後加總。

找出「段政存」的「影印卡」支出多少金額。當然也可以找出其他單項或多項項目。

C78:C86是「段政存」的「影印卡」到「辦公用品」的支出金額,點選C78可以顯示SUMPRODUCT(),其的公式為:





=SUMPRODUCT((E$2:E$62=A$73)* (C$2:C$62=A78) * D$2:D$62)







兩邊必須都是TRUE。「*」不能改成
,」。「*」類似AND的功能。













至於三個條件或更多條件式的用法,只要將條件式依序填入*後面即可,但是最後一個引數是執行加總的範圍。

3.其他函數多條件式加總

SUM()的公式與SUM(IF())一樣可以計算多條件加總,與SUMPRODUCT()的條件句類似,但SUM()必須同時按Crtl-Shift-Enter而且「*」與「,」不能隨意改變。


B78:D86是使用這三種公式所得到的答案,三種公式都一樣,到底哪一種比較好呢?端賴使用者的喜好程度。


接下來需要思考如果要加總「段政存」與「周博東」兩個人所支出的費用時,應該如何計算才好?或則想要加總支出金額等於2000或文具印刷費時應該如何進行才妥當呢?

參考:<<業務寶典>>

2 則留言:

PAUL 提到...

請問大大
SUMIF的篩選標準是要大於某個欄位(比方:=SUMIF(D11:D500,">M15",D11:D500)),這樣卻無法對M15欄做判斷,導致結果結為0,請問有什麼辦法解決嗎??

ChiaHsiang LU 提到...

Try this:

=SUMPRODUCT((D11:D500>M15),D11:D500)