|
我們對Excel的直觀感受就是公式一多，Excel計算速度就會很慢。但是實際上并不是這樣的。Excel中很多公式并不必然導致計算速度變慢，讓計算速度變慢的原因是你對公式的選擇以及公式的寫法。
上一篇我們?yōu)榇蠹医沂玖薊xcel公式計算的一個秘密：影響Excel公式計算效率的主要因素是該公式中引用的單元格數(shù)量。今天我們就基于這個原則為大家分析一下最常用的各種求和公式的效率。我們這一系列文章都是基于這個工具進行的。這個工具是用VBA寫的，你可以通過下面的方式獲得這個工具： 回復：計算速度分析工具
我們現(xiàn)在有一份數(shù)據(jù)如上圖所示，記錄了逐日的銷售明細，共20000行。 我們需要制作如下的報表：
我們需要分別統(tǒng)計每類產(chǎn)品在每個季度的銷售量。 如果我們要用函數(shù)實現(xiàn)這個報表（這是很多人的第一選擇），我們有以下的函數(shù)可以選擇：
SUM和IF的組合
SUMIFS SUMPRODUCT 我們下面先為大家介紹一下如何使用這些函數(shù)完成這個報表。
首先，為了簡化將來的公式，我們添加了兩行數(shù)據(jù)，分別表示每個季度的開始日期和結(jié)束日期：
然后，我們就可以使用下面公式了。先看SUMIFS的寫法：SUMIFS(數(shù)據(jù)!$F$3:$F$20002,數(shù)據(jù)!$E$3:$E$20002,Index!$B7,數(shù)據(jù)!$B$3:$B$20002,">="&Index!C$22,數(shù)據(jù)!$B$3:$B$20002,"<="&Index!C$23) 這個公式非常簡單，不多加解釋了。在本文中，我們就稱呼這個公式為“條件求和公式”。再看SUMPRODUCT的寫法（SUMPRODUCT是一個非常有用的函數(shù)，可以解決很多Excel的實際問題，我們會在其他文章中為大家詳細介紹這個函數(shù)）：SUMPRODUCT(數(shù)據(jù)!$F$3:$F$20002,--(數(shù)據(jù)!$E$3:$E$20002=Index!$B7),--(數(shù)據(jù)!$B$3:$B$20002>=Index!C$22),--(數(shù)據(jù)!$B$3:$B$20002<=Index!C$23)) 其實，從寫法上看這個公式與SUMIFS差不多，只不過判斷條件的寫法不太一樣。我們稱呼這個公式為“SUMPRODUCT第一種寫法”。
還可以換用另外一個寫法的SUMPRODUCT公式：
SUMPRODUCT(數(shù)據(jù)!$F$3:$F$20002*(--(數(shù)據(jù)!$E$3:$E$20002=Index!$B7))*(--(數(shù)據(jù)!$B$3:$B$20002>=Index!C$22))*(--(數(shù)據(jù)!$B$3:$B$20002<=Index!C$23))) 這個寫法與第一種寫法的區(qū)別是把“,”換成了“*”，從實際計算效果上看，這兩個寫法是等價的。之所以把這個寫法單獨拿出來介紹，是因為這兩個不同的寫法，計算速度是不同的。我們稱呼這個公式為“SUMPRODUCT第二種寫法”。
最后是SUM和IF的組合，這是一個數(shù)組公式：
SUM(IF((數(shù)據(jù)!$E$3:$E$20002=Index!$B7)*(數(shù)據(jù)!$B$3:$B$20002>=Index!C$22)*(數(shù)據(jù)!$B$3:$B$20002<=Index!C$23),數(shù)據(jù)!$F$3:$F$20002,0)) 先用IF判斷是否滿足條件，滿足條件的返回原數(shù)值，不滿足條件的返回0，然后對這些返回值求和。
要注意的是，這是一個數(shù)組公式，在輸入后需要按Ctrl+Shift+Enter鍵。這個公式是以前版本的Excel中沒有SUMIFS函數(shù)時經(jīng)常使用的，時至今日，還有很多人在很多場景中使用，我們稱呼它為“數(shù)組SUM”。好了，現(xiàn)在我們可以使用“計算速度分析工具”來計算一下這幾個公式的分別的計算時間（單位是秒）：
可以看出，條件求和的SUMIFS最快，只要0.74秒的時間，而數(shù)組SUM的計算最慢，需要2.12秒的時間。SUMPRODUCT的兩種寫法居于中間，接近差不多2秒左右，第二種寫法比第一種寫法慢10%左右。 一個表格本身2秒左右的計算時間已經(jīng)算是比較慢的了，尤其是考慮到我們只有168個公式。如果這個報表中還有其他計算公式，比如再做一個計算的分析，按照銷量區(qū)間的分析等等，那么這個報表的計算時間很容易就會超過10秒了。即使你用最慢的SUMIFS也不會好很多（會快200%左右，大約3-4秒，也是個很慢的計算速度）。
我們稍微分析一下就會發(fā)現(xiàn)，這四個公式在引用單元格的數(shù)量上是一致的，把那些作為參數(shù)的比如季度起始日期和產(chǎn)品列除掉不考慮（數(shù)量太少，對公式計算速度的影響可以忽略不記），那么他們引用你的單元格分別是： 數(shù)據(jù)列：數(shù)據(jù)!$F$3:$F$20002 小類列：數(shù)據(jù)!$E$3:$E$20002 日期列：數(shù)據(jù)!$B$3:$B$20002 
引用單元格的數(shù)量=20000+20000+20000=60000。既然引用單元格數(shù)量都一樣，那么這個計算速度為什么會有差距的呢？原因是這些公式的內(nèi)部處理方式是不同的。
以數(shù)組SUM為例，它的計算方式是先用IF判斷每一個單元格是否滿足條件，然后再把根據(jù)條件返回的結(jié)果集（數(shù)組）進行求和，相當于每個單元格都用到了。SUMPRODUCT的兩種寫法也是這樣，每個單元格都用枚舉的方式用到了。而之所以比數(shù)組SUM快一些的原因是因為數(shù)組SUM多了內(nèi)存中開辟數(shù)組的開銷。
SUMPRODUCT的兩種寫法的計算速度的差別則是它們內(nèi)部不同處理方式造成的，具體原因不詳，大家只要記住盡量采用第一種寫法就好了，因為這兩種寫法在結(jié)果上是一樣的。而SUMIFS的處理方式與另外3個公式不同。它首先去結(jié)果集篩選出滿足條件的記錄（從實現(xiàn)方式上看，我推測應該是使用了類似SQL的數(shù)據(jù)庫算法），然后再進行求和，從而導致速度大幅提高（大約節(jié)約了200%左右的時間）。
如果你要在這四個公式中做選擇，請選擇使用SUMIFS（COUNTIFS, AVERAGEIFS, SUMIF, COUNTIF）。有心的朋友可能會問，我們的公式只引用了60000個單元格，為什么會需要2秒的時間？要注意，我們有168個公式，所以總共引用單元格的數(shù)字是：
你可以自己留意一下，如果你是使用這些普通公式的寫法，那么只要引用的總單元格超過一千萬，就會感受到明顯的速度影響了。
當然了，就Excel本身的潛力來說，這個數(shù)據(jù)量和公式計算量再擴大個幾十倍也是可以輕松應對的。 通過上面的分析我們知道，盡管這些公式之間有速度的差異（差異從10%到300%不等），但是只要你引用的單元格數(shù)太多了，那么想通過換個公式來降低速度是收效甚微的。在實際中，即使使用SUMIFS這個速度最快的求和公式，那么速度也是不太容易接受的）。要想從根本上提高計算速度，還需要從我們上次講到的Excel那個根本秘密出發(fā)，想方設法減少引用的單元格數(shù)量。 我們來看這個報表中的一個單元格， 
我們發(fā)現(xiàn)，其實這個單元格計算我們根本不需要用到數(shù)據(jù)表中的整列日期和整列小類，和整列數(shù)量。如果我們能夠把所有的2017年Q2的上衣銷售記錄挑出來，那么他們只有83行，只要將這83行進行簡單的求和，那么引用單元格就從60000變成了83+83+83=249，而168個這樣的公式加起來，引用的單元格數(shù)量也不過是41832，還不如原來一個公式引用的單元格數(shù)量多，效率的改進是顯然的。首先，我們需要將源數(shù)據(jù)按照日期進行升序排序。點擊數(shù)據(jù)區(qū)域日期列的任意單元格，然后點擊“數(shù)據(jù)”選項卡下的“升序排序”按鈕：
然后，在報表中添加兩個輔助行：起始行和行數(shù)。分別代表在數(shù)據(jù)表中每個季度的起始行號和每個季度的總行數(shù)，分別使用下面兩個公式：
起始行號公式： =MATCH(C22,數(shù)據(jù)!$B$3:$B$20002,0) 行數(shù)公式： =MATCH(C23,數(shù)據(jù)!$B$3:$B$20002,1)-C25+1
然后在C7輸入公式： =SUMIFS(OFFSET(數(shù)據(jù)!$F$2,C$25,0,C$26,1), OFFSET(數(shù)據(jù)!$E$2,C$25,0,C$26,1),$B7) 并填充到整個報表區(qū)域：
這個公式還是用了SUMIFS函數(shù)，但是區(qū)域發(fā)生了變化，用了OFFSET從整個列中返回一部分區(qū)域。第一個黃色加亮的是銷量列，第二個綠色加量的是小類列。返回的區(qū)域行數(shù)比原來少多了，只有1777列。 我們看到這個公式的計算結(jié)果跟前面介紹的公式是一樣的。那么計算速度呢？
經(jīng)過計算速度分析工具的計算，耗時為：
只有0.08秒。是原來公式中最快的條件求和（SUMIFS）耗時的1/10。我們沒有選用特別的公式，還是用的SUMIFS，只不過通過MATCH(輔助行數(shù)據(jù)）和OFFSET（返回部分區(qū)域）減少了引用的單元格數(shù)量。而且，我們只是把其中符合日期條件的數(shù)據(jù)挑出來了，如果再從其中把滿足小類條件的數(shù)據(jù)挑出來，計算速度還會提高一個數(shù)量級。 這個公式調(diào)整策略的威力是驚人的。作為比較，我們可以看看數(shù)據(jù)透視表的計算速度。我們知道，數(shù)據(jù)透視表一旦創(chuàng)建完成，一般情況下，刷新速度是很快的，
在我們的例子中，這個透視表的計算速度是：0.14秒左右。比我們上面的速度慢了整整一倍。這個公式的調(diào)整策略在所有的場景下都非常有效。而且方法大同小異，只要你用到了求和（計數(shù)），那么就要想方設法減少所引用的單元格數(shù)量。只不過在不同場景中減少的方法有所差異，我后面會陸續(xù)給大家介紹。
關注本公眾號 點擊底部菜單“聯(lián)系客服”，與客服取得聯(lián)系，索取“求和公式效率分析”案例文件
|
