Excel函數之——SUMPRODUCT函數太強大了
Excel中的SUMPRODUCT()函數是個乘法累加函數,其實這個函數還有查詢,統計,條件統計等各種用法,今天就跟大家分享下這些用法。
1,SUMPRODUCT函數基本用法
先介紹下SUMPRODUCT函數的基本用法
格式:SUMPRODUCT(array1,[array2], [array3], ...)
該函數可以有多個參數,但只要第一個參數是必須的,其餘的參數都可以省略。
每個參數都必須是有相同維度的數組。
返回的結果是,將各數組中相應位置的數字相乘,再將這些結果累加後返回
具體操作如下:
2,單個條件計數
由於該函數有一個特點,就是隻有第一個參數是必須的,所以,利用這個特點,可以實現條件計數
以下是單條件計數
在E2單元格,使用的公式為:
“=SUMPRODUCT(N(B2:B7=D2))”
其中“B2:B7=D2”,返回的是一個數組,數組中的元素是“TRUE”或“FALSE”, 滿足條件的是“TRUE”, 不滿足條件的是“FALSE”
在使用N()函數,將“TRUE”轉換成“1”,“FALSE”轉換成“0”,
最後,將N()返回的數組中的所有元素,即所有的“1”和“0”,累加後,返回,即得到了滿足條件的個數
具體操作如下:
3,多條件計數
多條件計數和單條件計數的思路是一樣的。
如下面這個例子
在F2單元格使用的公式為:
“=SUMPRODUCT((B2:B7=E2)*(C2:C7>80))”
表示統計三班,分數大於80的人數
兩個條件分別是“B2:B7=E2”和“C2:C7>80”,中間使用乘法“*”運算
由於使用了乘法運算,結果自動轉換成數字,所以就不在需要使用N()函數了
具體操作如下:
4,多條件統計-1
在F2單元格使用公式:
“=SUMPRODUCT((B2:B7=E2)*(C2:C7>80),C2:C7)”
表示,計算三班中分數大於80分的同學,他們的總分,並返回
公式中“(B2:B7=E2)*(C2:C7>80)” 返回的是滿足條件的一個數組,這個數組是有“1”和“0”組成,其中“1”表示滿足條件,“0”表示不滿足條件,再將這個數組與C2:C7(即分數數組)相乘累加後返回
具體操作如下:
5,多條件統計-2
下面這個例子也是多條件統計,與上面不同的是,需要相乘的數組多了一個。
C12單元格使用的公式為:
“=SUMPRODUCT((A2:A9=A12)*(B2:B9=B12),C2:C9,D2:D9)”
SUMPRODUCT函數的參數可以有多個,最多是255個。
具體操作如下:
6,跨列求和
SUMPRODUCT函數也可以用來進行跨列求和
H3單元格使用的函數為:
“=SUMPRODUCT(($B$2:$G$2=H$2)*($A3=$A$3:$A$7)*$B$3:$G$7)”
其中這也是多條件統計,公式中的兩個條件分別是
“$B$2:$G$2=H$2”,和 “$A3=$A$3:$A$7”
公式中的用到了兩個“*”乘號,其中最後一個“*”改用逗號“,”,也是一樣的。
具體操作如下:
7,生成二維彙總數據表
這個例子與上面的例子類似,也是多條件統計
F2單元格中,使用的公式為:
“=SUMPRODUCT(($A$2:$A$9=$E2)*($B$2:$B$9=F$1),$C$2:$C$9)”
具體操作如下:
8,自動生成排名
D2單元格使用的公式為:
“=SUMPRODUCT((C2<$C$2:$C$7)*1)+1”
其中“SUMPRODUCT((C2<$C$2:$C$7)*1)” 返回的是,分數大於自己的人數,最後在+1,就是排名了。
具體操作如下:
以上就是Excel中SUMPRODUCT()函數的用法了,如果有不明白的就留言吧。別為了點贊啊。