Excel中總有那麼幾個讓人驚豔的函數公式,例如Sumproduct函數,它不僅能求和,還能計數和排名,你信嗎?
一、功能及語法結構。
功能:返回相應的數組或區域乘積的和。
語法:=Sumproduct(數組或區域)。
示例:
目的:計算銷量和。
方法:
在目標單元格中輸入公式:=SUMPRODUCT(D3:D9)。
解讀:
當Sumproduct函數的參數只有一維數組或指定的值時,其作用和Sum函數相同,就是對指定範圍的值求和。
二、先乘積再求和。
目的:計算總銷售額。
方法:
在目標單元格中輸入公式:=SUMPRODUCT(C3:C9,D3:D9)。
解讀:
先乘積再求和也是Sumproduct函數的基本功能,其計算過程為:C3×D3+C4×D4+C5×D5+C6×D6+C7×D7+C8×D8+C9×D9=12860.05。
三、單條件求和。
目的:按照「地區」統計銷量。
方法:
在目標單元格中輸入公式:=SUMPRODUCT((E3:E9=H3)*D3:D9)。
解讀:
條件E3:E9=H3成立時,返回True,暨1,否則返回False,暨0。所以上述公式形成一個以0和1為數組,以D3:D9為數組的計算區域,計算過程就是先成績在求和。其功能和Sumif函數相同哦!
四、多條件求和。
目的:計算相應地區銷量>=50的銷量和。
方法:
在目標單元格中輸入公式:=SUMPRODUCT((E3:E9=H3)*(D3:D9>=I3)*D3:D9)。
解讀:
當兩個條件同時成立時,返回True,暨1,其中一個或兩個都不成立時,返回False,暨0。上述公式首先判斷兩個條件的情況,然後先成績,再求和。其功能和Sumifs相同哦!
五、單條件計數。
目的:求銷量>=50的筆數。
方法:
在目標單元格中輸入公式:=SUMPRODUCT(1*(D3:D9>=H3))。
解讀:
首先判斷條件成立情況,然後×1,再求和。其功能相當於Countif函數。
六、多條件計數。目的:計算相應地區銷量>=50的銷量筆數。
方法:
在目標單元格中輸入公式:=SUMPRODUCT((E3:E9=H3)*(D3:D9>=I3)*1)。
七、中國式排名。
目的:對銷量進行排序。
方法:
在目標單元格中輸入公式:=SUMPRODUCT((D$3:D$9>D3)/COUNTIF(D$3:D$9,D$3:D$9))+1。
解讀:
1、此用法為Sumproduct函數的經典用法,當範圍大於值(D$3:D$9>D3)時為降序,當值大於範圍(D3>D$3:D$9)時,為升序。+1為輔助值。
2、此方法的排名結果不會出現「跳躍」的情況哦!
結束語:
一個函數,不僅可以求和、計數,還具有排名等實用功能,是不是很高大上了?
學習過程中如果有不懂、不明白的地方,歡迎在留言區留言討論哦,如果親覺著實用,別忘了「點轉評」哦,有親的支持,小編會進一步努力的哦!