實用技巧：Sumproduct函數(shù)的應用

【例】如下圖所示為銷售明細表，要求在G3設置公式，統(tǒng)計出E3日期、商品名稱為T的銷售數(shù)量之和。

=SUMPRODUCT((A2:A100=E3)*(B2:B100=F3)*C2:C100)

Sumproduct函數(shù)后來被很多同學運用的爐火純青，多條件求和變得不再是什么難題。雖然知道這個函數(shù)運算速度慢，但一直也沒覺得有什么，直到最近幾個同學的提問，才意識到這個函數(shù)的副作用有多么的大。

最近在企業(yè)培訓時，有幾個同學說自已的表格運算非常的慢，不知道是什么原因。打開表后發(fā)現(xiàn)，表中到處是Sumproduct函數(shù)設置的多條件求和公式。后來換成sumifs函數(shù)后，運算速度果然有所提升。

為了驗證Sumproduct函數(shù)公式對表格運算速的影響，今天特意做了2個相同的表格(源表樣式同上圖)，同樣有15000行源數(shù)據(jù)，分別設置同樣數(shù)量的了10462個公式：

sumproudct函數(shù)公式

=SUMPRODUCT((Sheet1!$A$2:$A$15000=$A2)*(Sheet1!$B$2:$B$15000=B$1)*Sheet1!$C$2:$C$15000)

Sumifs函數(shù)公式：

=SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,匯總表!$A2,Sheet1!$B:$B,匯總表!B$1)

用一段VBA代碼進行了測試：

Sub 測試運算速度()

Dim t

t = Timer - t

Range("c1:c150") = Range("c1:c150").Value

MsgBox Timer - t

End Sub

測試的結果是 Sumifs函數(shù)所需時間是37秒，而Sumrpoduct所需時間是Sumifs函數(shù)公式的6倍，也就是說Sumifs函數(shù)的運算比Sumproduct函數(shù)快6倍。

后來，又插入excel數(shù)據(jù)透視表進行速度測試，所需時間是0秒。只是數(shù)據(jù)透視表需要手工刷新后才能更新數(shù)據(jù)。

補充：自Excel2007版開始，Excel新增了Sumifs函數(shù)(多條件求和)和Countis函數(shù)(多條件計數(shù))，再加上數(shù)據(jù)透視表強大的分類匯總功能，所以Sumrpoduct函數(shù)主導的多條件求和時代必須要結束了。但這不意味著Sumprduct函數(shù)再無用武之地，比如直接支持數(shù)組運算、支持源數(shù)據(jù)二次處理后再對比、處理文本型數(shù)值求和等方面還會發(fā)揮作用。