別被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ā)揮作用。
繼續(xù)查找其他問(wèn)題的答案?
-
Excel 2019如何使用復(fù)制、粘貼命令插入Word數(shù)據(jù)?(視頻講解)
2021-01-257次播放
-
Excel 2019如何制作數(shù)據(jù)透視表?(視頻講解)
2021-01-2227次播放
-
Excel 2019如何設(shè)置相對(duì)引用?(視頻講解)
2021-01-2210次播放
-
2021-01-2212次播放
-
Excel 2019如何使用命令復(fù)制公式?(視頻講解)
2021-01-226次播放
-
2021-01-223次播放
-
2021-01-222次播放
-
Excel 2019如何利用開(kāi)始選項(xiàng)卡進(jìn)入篩選模式?(視頻講解)
2021-01-223次播放
-
Excel 2019如何設(shè)置指定數(shù)據(jù)的篩選?(視頻講解)
2021-01-223次播放
-
Excel 2019如何設(shè)置自定義排序?(視頻講解)
2021-01-222次播放
點(diǎn)擊加載更多評(píng)論>>