別被Sumproduct函數(shù)拖慢了你的excel表格

Sumproduct函數(shù)

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

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

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

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

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

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代碼進(jìn)行了測(cè)試：

Sub 測(cè)試運(yùn)算速度()

Dim t

t = Timer - t

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

MsgBox Timer - t

End Sub

測(cè)試的結(jié)果是 Sumifs函數(shù)所需時(shí)間是37秒，而Sumrpoduct所需時(shí)間是Sumifs函數(shù)公式的6倍，也就是說(shuō)Sumifs函數(shù)的運(yùn)算比Sumproduct函數(shù)快6倍。

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

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