很多朋友知道,Excel裡的Sumproduct函數是一個乘積求和函數,即
兩列數值相應乘積之和。其基本用法前面我們已經講解過了,本期給大家講解一下sumproduct函數那些令人意想不到、功能卻令人驚歎的的高級用法。
1. 普通求和
當sumproduct函數的參數只有一個區域(數組)時,那就沒有了相應數值的乘積,得到的是該區域之和。
如上圖,當只有一個區域時,使用sumproduct和sum函數得到的值是一樣的。
2.乘積之和
Sum(和)+product(乘積)=sumproduct(乘積之和)
各產品的總銷售額=sumproduct(B2:B8,C2:C8)
其實,sumproduct除了上面這種用法,還可以使用"*",如上題各產品的總銷售額=sumproduct((B2:B8)*(C2:C8)),所求得的值一樣的,大家可以試一下。
同理,當參數是三個或多個區域(數組)時,得到的是相對應數值的乘積之和。
二、高級用法
1.單條件計數
如下圖,要求總分大於等於180的人數,可以使用countif和sumproduct函數實現。
=COUNTIF(E2:E9,">=180")
=SUMPRODUCT((E2:E9>=180)*1) "*1"將其轉換為數組,才可以參與計算。
2.單條件求和
如下圖,要求總分大於等於180的分數之和,可以使用sumif和sumproduct函數實現。
=SUMIF(E2:E9,">=180",E2:E9)
=SUMPRODUCT((E2:E9>=180)*E2:E9)。
3.多條件計數
提到"多條件計數",大家第一反應就是"countifs"函數了,沒錯。不過sumproduct函數也是可以實現該功能的,具體如下:
上圖是個人語文、數學、英語及總分成績,如果我要求語文、數學、英語三門都及格(>=60)的人有多少。
Countifs函數:=COUNTIFS(B2:B9,">=60",C2:C9,">=60",D2:D9,">=60"),得到3。
sumproduct函數:=SUMPRODUCT((B2:B9>=60)*(C2:C9>=60)*(D2:D9>=60))
"*"在這裡起到"and"的作用,表示三門成績都大於等於60的個數。
從這個例子可以看到,sumproduct還有這種作用,對多個條件計數時,如果一個條件成立則為1,不成立則為0,最後將所有值相加即為所求值:
=sumproduct((區域1=條件1)*(區域2=條件2)*(區域3=條件3)...),其中等號"="還可以是">""""<="">="等。
4.多條件求和
如下圖,要求語文、數學、英語及格的分數之和(有一門及格就行)
=SUMPRODUCT((B2:B9>=60)*(B2:B9)+(C2:C9>=60)*(C2:C9)+(D2:D9>=60)*(D2:D9))
以上公式的含義:(B2:B9>=60)*(B2:B9),條件乘以分數,即語文及格的分數,三者相加得到三門科目及格的分數之和。
好了,以上就是sumproduct函數的高級使用方法,可以實現單條件計數、單條件求和、多條件計數、多條件求和,你學會了麼?
如果您喜歡本文內容,請點擊上方紅色按鈕進行關注,在這裡,我將為你持續分享Excel的相關知識、實用技巧以及各種精品軟件,歡迎關注。