- 帖子
- 586
- 主題
- 123
- 精華
- 0
- 積分
- 763
- 點名
- 0
- 作業系統
- WINDOW7
- 軟體版本
- Office 2003
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2010-5-16
- 最後登錄
- 2017-3-14
 
|
16#
發表於 2012-11-6 14:01
| 只看該作者
回復 14# c_c_lai
以下資料是從excelhome找到的希望對你有幫助
Excel多條件求和 & SUMPRODUCT函數用法詳解
龍逸凡
日常工作中,我們經常要用到多條件求和,方法有多種,第一類:使用基本功能來實現。主要有:篩選、分類匯總、數據透視表、多條件求和向導;第二類:使用公式來實現方法。主要有:使用SUM函數編寫的數組公式、聯用SUMIF和輔助列(將多條件變爲單條件)、使用SUMPRODUCT函數、使用SUMIFS函數(限于Excel2007及以上的版本),方法千差萬別、效果各有千秋。本人更喜歡用SUMPRODUCT函數。由于Excel幫助對SUMPRODUCT函數的解釋太簡短了,與SUMPRODUCT函數的作用相比實在不匹配,爲了更好地掌握該函數,特將其整理如下。
龍逸凡注:歡迎轉貼,但請注明作者及出處。
一、 基本用法
在給定的幾組數組中,將數組間對應的元素相乘,並返回乘積之和。
語法:
SUMPRODUCT(array1,array2,array3, ...)
Array1, array2, array3, ... 爲 2 到 30 個數組,其相應元素需要進行相乘並求和。
公式:=SUMPRODUCT(A2:B4, C2:D4)
A B C D
1 Array 1 Array 1 Array 2 Array 2
2 3 4 2 7
3 8 6 6 7
4 1 9 5 3
公式解釋:兩個數組的所有元素對應相乘,然後把乘積相加,即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3。計算結果爲156
二、 擴展用法
1、 使用SUMPRODUCT進行多條件計數
語法:
=SUMPRODUCT((條件1)*(條件2)*(條件3)* …(條件n))
作用:
統計同時滿足條件1、條件2到條件n的記錄的個數。
實例:
=SUMPRODUCT((A2:A10="男")*(B2:B10="中級職稱"))
公式解釋:
統計性別爲男性且職稱爲中級職稱的職工的人數
2、 使用SUMPRODUCT進行多條件求和
語法:
=SUMPRODUCT((條件1)*(條件2)* (條件3) *…(條件n)*某區域)
作用:
匯總同時滿足條件1、條件2到條件n的記錄指定區域的匯總金額。
實例:
=SUMPRODUCT((A2:A10="男")*(B2:B10="中級職稱")*C2:C10)
公式解釋:
統計性別爲男性且職稱爲中級職稱的職工的工資總和(假設C列爲工資)
三、 注意事項
1、數組參數必須具有相同的維數,否則,函數 SUMPRODUCT 將返回錯誤值 #VALUE!。
2、SUMPRODUCT函數將非數值型的數組元素作爲 0 處理。
3、在SUMPRODUCT中,2003及以下版本不支持整列(行)引用,必須指明範圍,不可在SUMPRODUCT函數使用A:A、B:B,Excel2007及以上版本可以整列(列)引用,但並不建議如此使用,公式計算速度慢。
4、SUMPRODUCT函數不支持“*”和“?”通配符
SUMPRODUCT函數不能象SUMIF、COUNTIF等函數一樣使用“*”和“?”等通配符,要實現此功能可以用變通的方法,如使用LEFT、RIGHT、ISNUMBER(FIND())或ISNUMBER(SEARCH())等函數來實現通配符的功能。如:
=SUMPRODUCT((A2:A10="男")*(B2:B10="中級職稱")*(LEFT(D2:D10,1)="龍")*C2:C10)
=SUMPRODUCT((A2:A10="男")*(B2:B10="中級職稱")*((ISNUMBER(FIND("龍逸凡",D2:D10)))*C2:C10))
注:以上公式假設D列爲職工姓名。ISNUMBER(FIND())、ISNUMBER(SEARCH())作用是實現“*”的通配功能,只是前者區分大小寫,後者不區分大小寫。
5、SUMPRODUCT函數多條件求和時使用“,”和“*”的區別:當擬求和的區域中無文本時兩者無區別,當有文本時,使用“*”時會出錯,返回錯誤值 #VALUE!,而使用“,”時SUMPRODUCT函數會將非數值型的數組元素作爲 0 處理,故不會報錯。 也就是說:
公式1:=SUMPRODUCT((A2:A10="男")*(B2:B10="中級職稱")*C2:C10)
公式2:=SUMPRODUCT((A2:A10="男")*(B2:B10="中級職稱"),C2:C10)
當C2:C10中全爲數值時,兩者計算結果一樣,當C2:C10中有文本時公式1會返回錯誤值 #VALUE!,而公式2會返回忽略文本以後的結果。
四、 網友們的精彩實例
1、求指定區域的奇數列的數值之和
=SUMPRODUCT(MOD(COLUMN(A1:F1),2)*A1:F1)
2、求指定區域的偶數行的數值之和
=SUMPRODUCT(((MOD(ROW(A1:A22),2))-1)*A1:A22)*(-1)
3、求指定行中列號能被4整除的列的數值之和
=SUMPRODUCT((MOD(COLUMN(A1:P1),4)=0)*A1:P1)
4、.求某數值列前三名分數之和
=SUMPRODUCT(LARGE(B1:B16,ROW(1:3)))
5、統計指定區域不重複記錄的個數
=SUMPRODUCT(1/COUNTIF(V11:V15,V11:V15)) |
|