有一個函數,它隸屬Sum家族,卻比Sum好用十倍。原本它只是一個很簡單的一個分組乘積後再求和的函數,後被高手挖掘出了很多擴展用法。它就是:
Sumprodcut函數
基本用法
Sumproduct函數的本職工作是返回兩組數乘積的和,如:
=SUMPRODUCT(B2:B7,C2:C7)
等同於B和C列一一對應求積,然後再用SUM求和。
這個函數特別不守本份,到處插足。不信請向下看:
01.插足單條件計數(搶Countif飯碗)
例:統計奧迪品牌型號個數
=SUMPRODUCT((A2:A5=B8)*1)
注:(A2:A5=B8)返回的是TRUE或FALSE,*1可以轉換為1或0
02.插足單條件求和(搶Sumif飯碗)
例:統計A產品的銷售合計
=SUMPRODUCT((B3:B9="A")*C3:C9)
02.插足多條件計數(搶Countifs飯碗)
例:計算A產品3月的銷售次數
=SUMPRODUCT((B3:B9="A")*(MONTH(A3:A9)=3))
03.插足多條件求和(搶Sumifs飯碗)
例:計算A產品3月的銷量
=SUMPRODUCT((B3:B9="A")*(MONTH(A3:A9)=3)*C3:C9)
04.插足多條件查找(搶Lookup飯碗)
如果滿足以下兩個條件,Sumproduct函數還可以輕鬆的查找。
- 查找的內容在表中唯一
- 被查找的為數字
例:查找奧迪Q7的庫存數最
=SUMPRODUCT((A2:A5=A8)*(B2:B5=B8)*C2:C5)
05.插足數組求和(搶數組公式飯碗)
由於Sumproudct支持數組運算,它搶了數組公式的飯碗。
例:計算不重複個數
=SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))
例:
含文本數字的求和=SUMPRODUCT(B2:B10*1)
注:*1可以把文本型數字轉換成數值型數字
蘭色說:可能有同學擔心Sumproduct函數運算比其他函數慢,蘭色以前做過測試,如果數據量不是太大速度和Sumif等函數差不了多少的。如果數據量非常大,任何函數都會讓你的錶慢蝸牛。建議把表中不需要再更新的公式轉換成值。
最後還要提醒一點:Sumproduct函數千萬不要引用整列數據如 =Sumproduct(A:A*1)
閱讀更多 蘭色教你Excel 的文章