我們對Excel的直觀感受就是公式一多，Excel計算速度就會很慢。但是實際上并不是這樣的。Excel中很多公式并不必然導致計算速度變慢，讓計算速度變慢的原因是你對公式的選擇以及公式的寫法。
我們這一系列文章都是基于這個工具進行的。這個工具是用VBA寫的，你可以通過下面的方式獲得這個工具：
回復：計算速度分析工具
常用的求和公式及計算速度比較
我們現在有一份數據如上圖所示，記錄了逐日的銷售明細，共20000行。
我們需要制作如下的報表：
我們需要分別統(tǒng)計每類產品在每個季度的銷售量。
如果我們要用函數實現這個報表（這是很多人的第一選擇），我們有以下的函數可以選擇：
SUM和IF的組合
SUMIFS
SUMPRODUCT
我們下面先為大家介紹一下如何使用這些函數完成這個報表。
首先，為了簡化將來的公式，我們添加了兩行數據，分別表示每個季度的開始日期和結束日期：
">="&Index!C$22,數據!$B$3:$B$20002,"<="&Index!C$23)
--(數據!$B$3:$B$20002>=Index!C$22),--(數據!$B$3:$B$20002<=Index!C$23))
(--(數據!$B$3:$B$20002>=Index!C$22))*(--(數據!$B$3:$B$20002<=Index!C$23)))
(數據!$B$3:$B$20002<=Index!C$23),數據!$F$3:$F$20002,0))
可以看出，條件求和的SUMIFS最快，只要0.74秒的時間，而數組SUM的計算最慢，需要2.12秒的時間。SUMPRODUCT的兩種寫法居于中間，接近差不多2秒左右，第二種寫法比第一種寫法慢10%左右。
一個表格本身2秒左右的計算時間已經算是比較慢的了，尤其是考慮到我們只有168個公式。如果這個報表中還有其他計算公式，比如再做一個計算的分析，按照銷量區(qū)間的分析等等，那么這個報表的計算時間很容易就會超過10秒了。即使你用最慢的SUMIFS也不會好很多（會快200%左右，大約3-4秒，也是個很慢的計算速度）。
深入分析
我們稍微分析一下就會發(fā)現，這四個公式在引用單元格的數量上是一致的，把那些作為參數的比如季度起始日期和產品列除掉不考慮（數量太少，對公式計算速度的影響可以忽略不記），那么他們引用你的單元格分別是：
數據列：數據!$F$3:$F$20002
小類列：數據!$E$3:$E$20002
日期列：數據!$B$3:$B$20002
當然了，就Excel本身的潛力來說，這個數據量和公式計算量再擴大個幾十倍也是可以輕松應對的。
如何將速度提高10倍甚至更多
要想從根本上提高計算速度，還需要從我們上次講到的Excel那個根本秘密出發(fā)，想方設法減少引用的單元格數量。
我們來看這個報表中的一個單元格，
然后，在報表中添加兩個輔助行：起始行和行數。分別代表在數據表中每個季度的起始行號和每個季度的總行數，分別使用下面兩個公式：
起始行號公式：
=MATCH(C22,數據!$B$3:$B$20002,0)
行數公式：
=MATCH(C23,數據!$B$3:$B$20002,1)-C25+1
然后在C7輸入公式：
=SUMIFS(OFFSET(數據!$F$2,C$25,0,C$26,1),
OFFSET(數據!$E$2,C$25,0,C$26,1),$B7)
并填充到整個報表區(qū)域：
這個公式還是用了SUMIFS函數，但是區(qū)域發(fā)生了變化，用了OFFSET從整個列中返回一部分區(qū)域。第一個黃色加亮的是銷量列，第二個綠色加量的是小類列。返回的區(qū)域行數比原來少多了，只有1777列。
我們看到這個公式的計算結果跟前面介紹的公式是一樣的。那么計算速度呢？
經過計算速度分析工具的計算，耗時為：
只有0.08秒。是原來公式中最快的條件求和（SUMIFS）耗時的1/10。我們沒有選用特別的公式，還是用的SUMIFS，只不過通過MATCH(輔助行數據）和OFFSET（返回部分區(qū)域）減少了引用的單元格數量。而且，我們只是把其中符合日期條件的數據挑出來了，如果再從其中把滿足小類條件的數據挑出來，計算速度還會提高一個數量級。
總結
這個公式的調整策略在所有的場景下都非常有效。而且方法大同小異，只要你用到了求和（計數），那么就要想方設法減少所引用的單元格數量。只不過在不同場景中減少的方法有所差異，我后面會陸續(xù)給大家介紹。
取得本文案例文件的方式：
關注本公眾號
點擊底部菜單“聯(lián)系客服”，與客服取得聯(lián)系，索取“求和公式效率分析”案例文件
聯(lián)系客服
