Excel教程下載和軟件下載中心,Microsoft技術社區聯盟成員,全球極具影響力的Excel門戶,Office技術培訓社區

SUMPRODUCT函數詳解

2017-10-25  作者:ExcelHome  閱讀:
今天和大家分享一個名字拖沓,但是應用非常廣泛的函數——SUMPRODUCT。
開門見山,咱們通過幾個例子,一起來看下SUMPRODUCT具體能干什么樣的工作。

假設上面這張圖,是某個公司工資發放的部分記錄表,A列是工資發放的時間,B列是員工所屬的部門,C列是員工姓名,D列是相關員工領取的工資金額。
那么,問題來了:

一,西門慶同志領取了幾次工資?

這是一個單條件計數的問題,通常我們使用COUNTIF函數,如果使用SUMPRODUCT函數,一般寫成這樣:
=SUMPRODUCT((C2:C13="西門慶")*1)
先判斷C2:C13的值是否等于”西門慶”,相等則返回TRUE,不等則返回FALSE,由此建立一個有邏輯值構成的內存數組,SUMPRODUCT有一個特性,它會將非數值型的數組元素作為0處理,邏輯值自然是屬于非數值型的數組元素,我們使用*1的方式,把邏輯值轉化為數值,TRUE轉化為1,FALSE轉化為0,最后統計求和。

二,西門慶同志一共領取了多少工資?

這是一個單條件求和的問題,通常我們使用SUMIF函數,如果使用SUMPRODUCT函數,我們可以寫成這樣:
=SUMPRODUCT((C2:C13="西門慶")*D2:D13)
依然首先判斷C2:C13的值是否等于”西門慶”,得到邏輯值FALSE或TRUE,再和D2:D13的值對應相乘,最后統計求和得出結果。
看完了上面兩個問題,有些表親心里就嘀咕了,貌似SUMPRODUCT能干的事,SUMIF和COUNTIF也能做到,而且做的更好,那么還要SUMPRODUCT干啥?
鄉親們吶,話不能這么說,SUMPRODUCT可是上得廳堂下得廚房,對工作環境不挑不揀,它對參數類型沒有啥特別要求,COUNTIF和SUMIF就不同咧,他倆要求個別參數,必須是區域數組(Range),比如下面這兩個問題,COUNTIF和SUMIF就犯難了。

三,六月份財務部發放了幾次工資?累計發放了多少?
六月份財務部發放了幾次工資,這是一個多條件計數的問題哈:
第一個條件,發放工資的時間必須是六月份;
第二個條件,發放工資的部門必須是財務部。
如果使用多條件計數函數COUNTIFS,判斷發放工資的時間是否屬于六月份,會簡單問題復雜化。
而使用SUMPRODUCT函數,咱們可以簡單利索的寫成這樣:
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="財務部"))
統計六月份財務部發放了多少工資?
表親們都曉得,這是一個常見的多條件求和問題。如果使用SUMIFS函數,判斷發放工資的時間是否屬于六月份,和COUNTIFS類似,也會簡單問題復雜化。
SUMPRODUCT躍然而至:
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="財務部"),D2:D13)
上面這個公式可以說是SUMPRODUCT多條件求和的典型用法啦,可以歸納為:
=SUMPRODUCT((條件一)*(條件二)……,求和區域)

四,六月份財務部和市場部合計發放了多少工資?
通過上面第三個問題,表親們已經曉得如何計算六月份財務部發了多少工資,那么六月份財務部和市場部合計發了多少工資,又當怎么計算呢?
我們經常見有些表親把公式寫成這樣:
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="財務部")*D2:D13)+SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="市場部")*D2:D13)
這些表親們估計心想,不就是計算兩個部門嗎?甭說兩個,二十個咱也能算,一個加一個,一直加到二十個,世上無難事,只怕有心人嘛……
公式寫的那么長,先不談計算速度,首先它累手啊,其次萬一寫錯了,要修改,那也是麻煩他媽哭麻煩——麻煩死了。
其實我們可以寫成這樣:
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13={"財務部","市場部"})*D2:D13)

轉載請注明來源"ExcelHome"并保留原文鏈接。
固定鏈接:http://www.fvtgnn.tw/lesson/article/excel/1881.html
分享到:
Copyright 1999 - 2020 Excel Home.All Rights Reserved.
本站特聘法律顧問:徐懷玉律師 李志群律師   滬ICP備11019229號

滬公網安備 31011702000001號

征信       
35选7摇奖器