別被Sumproduct函數拖慢了你的excel表格

在excel2003版中，由於sumif函數和countif函數只能設定一個條件，一個神奇的多條件求和和計數函數走上了歷史舞台，它就是：

Sumproduct函數

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

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

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

最近在企業培訓時，有幾個同學說自已的表格運算非常的慢，不知道是什麼原因。開啟表後發現，表中到處是Sumproduct函數設定的多條件求和公式。後來換成sumifs函數後，運算速度果然有所提升。

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

sumproudct函數公式

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

Sumifs函數公式：

=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函數所需時間是37秒，而Sumrpoduct所需時間是Sumifs函數公式的6倍，也就是說Sumifs函數的運算比Sumproduct函數快6倍。

後來，又插入excel資料透視表進行速度測試，所需時間是0秒。只是資料透視表需要手工刷新後才能更新資料。

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