Excel中的SUMPRODUCT函數是一個非常通用的函數,根據輸入的參數可以給出不同的結果。
SUMPRODUCT函數通常所做的是將一個或多個數組的元素相乘,然後將這些乘積相加或相加。
但是通過調整參數的形式,SUMPRODUCT將計算給定範圍內包含符合特定標準的數據的單元數。
01之04
SUMPRODUCT與COUNTIF和COUNTIFS
自Excel 2007以來,該程序還具有COUNTIF和COUNTIFS函數,可以讓您對滿足一個或多個設置條件的單元格進行計數。
然而,有時候,SUMPRODUCT更容易處理,以找到與上圖中示例所示的相同範圍有關的多個條件。
04年02月
SUMPRODUCT函數語法和計數單元格的參數
函數的語法引用函數的佈局,並包含函數的名稱,括號,逗號分隔符和參數 。
要獲得計算單元格的功能而不是執行其標準目的,必須在SUMPRODUCT中使用以下非標準語法:
= SUMPRODUCT([condition1] * [condition2])
下面的例子說明了這種語法是如何工作的。
示例:計數滿足多個條件的單元格
如上圖中的示例所示,SUMPRODUCT用於查找數據范圍A2到B6中包含數值介於25和75之間的數據的總數。
03之04
輸入SUMPRODUCT功能
通常,在Excel中輸入函數的最好方法是使用它們的對話框 ,這樣可以方便地一次輸入一個參數,而無需輸入括號或逗號作為參數之間的分隔符。
但是,因為此示例使用SUMPRODUCT函數的不規則形式,所以不能使用對話框方法。 相反,該功能必須輸入到工作表單元格中。
在上圖中,使用以下步驟將SUMPRODUCT輸入到單元格B7中:
- 單擊工作表中的單元格B7 - 將顯示功能結果的位置
- 在工作表的單元格E6中鍵入以下公式:
= SUMPRODUCT(($ A $ 2:$ B $ 6> 25)*($ A $ 2:$ B $ 6 <75))
- 答案5應該出現在單元格B7中,因為範圍中只有5個值 - 40,45,50,55和60 - 它們之間的值介於25和75之間
- 當您單擊單元格B7時,完成的公式= SUMPRODUCT(($ A $ 2:$ B $ 6> 25)*($ A $ 2:$ B $ 6 <75))出現在工作表上方的公式欄中
04年4月
打破SUMPRODUCT功能
當為參數設置條件時,SUMPRODUCT根據條件評估每個數組元素並返回一個布爾值 (TRUE或FALSE)。
為了進行計算,Excel為那些為TRUE的數組元素賦值1 ,為FALSE數組元素賦值為0 。
每個數組中相應的1和0相乘在一起:
- 1 x 1返回1的值
- 1 x 0返回值為0
然後通過函數對這些零和零進行求和,以使我們能夠計算滿足這兩個條件的值的數量。
或者,想想這樣...
想想SUMPRODUCT正在做什麼的另一種方法是將乘法符號視為AND條件。
考慮到這一點,只有當兩個條件都滿足時(數字大於25且小於75),才會返回TRUE值(相當於一個記憶)。
該函數然後總結所有真值,以得出5的結果。