哈嘍,我是星光,咱們又見面了。
今天和大家分享一個名字拖沓,但是應用非常廣泛的函數——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就不同咧,他倆要求個別參數,必須是單元格的區域,比如下面這兩個問題,COUNTIF和SUMIF就犯難了。
三,3月份財務部發放了幾次工資?
3月份財務部發放了幾次工資,這是一個多條件計數的問題哈:
第一個條件,發放工資的時間必須是3月份;
第二個條件,發放工資的部門必須是財務部。
如果使用多條件計數函數COUNTIFS,判斷發放工資的時間是否屬於3月份,會簡單問題複雜化。
而使用SUMPRODUCT函數,咱們可以簡單利索的寫成這樣:
=SUMPRODUCT((MONTH(A2:A13)=3)*(B2:B13="財務部"))
四、統計3月份財務部發放了多少工資?
這是一個常見的多條件求和問題。如果使用SUMIFS函數,判斷發放工資的時間是否屬於3月份,和COUNTIFS類似,也會簡單問題複雜化。
SUMPRODUCT躍然而至:
=SUMPRODUCT((MONTH(A2:A13)=3)*(B2:B13="財務部"),D2:D13)
上面這個公式可以說是SUMPRODUCT多條件求和的典型用法,可以歸納為:
=SUMPRODUCT((條件一)*(條件二)……,求和區域)
五,3月份財務部和市場部合計發放了多少工資?
通過上面的問題,已經曉得如何計算3月份財務部發了多少工資,那麼3月份財務部和市場部合計發了多少工資,又當怎麼計算呢?
我們可以寫成這樣:
=SUMPRODUCT((MONTH(A2:A13)=3)*(B2:B13={"財務部","市場部"})*D2:D13)
好吧,嘮嘮叨叨說了這麼多,是到了該結束的時候啦。我是男神,我是星光,僅以此文獻給我已逝去的青澀小時光,以及時光里的某個人……
圖文作者:看見星光