Excel文件的計算“速度”是一個被忽視的問題。你可以很輕易的找到為某個特定問題如何寫一個公式，但是你上網(wǎng)搜“我的Excel文件為什么這么慢？”，你會得到一堆似是而非，完全不能解決你的問題的“答案”。
這是百度上的前幾個答案，不用打開鏈接也會發(fā)現(xiàn)這些操作幾乎不可能真正解決Excel的計算速度問題。
在看某乎上的幾個答案：
？？？
20秒以內(nèi)是正常的？
我實在是沒法接受這種答案。
如果你再到所謂的一些專業(yè)Excel論壇中去問，你還會得到這樣的答案：
整理一下磁盤碎片
是不是中毒了
是不是數(shù)據(jù)太多了
該換機器了
……
如果你嘗試過通過上面的各種方法想提高Excel的計算速度，我相信基本上你會得到下面的結(jié)論：
Excel是不是就這樣，公式多了（或者數(shù)據(jù)多了）就會慢的要死?。?！
可真的是這樣嗎？
為什么我們應該注重速度呢？
Excel的計算速度從兩個方面影響我們的工作效率。首先，速度慢本身就帶來了效率的降低。其次，計算速度慢還影響我們的心情，降低對進行該項工作的意愿，甚至產(chǎn)生畏懼心理。想象一下，如果你每天都要打開一個Excel文件，記錄幾條當天發(fā)生的數(shù)據(jù)，但是每次在一個單元格中輸入一個數(shù)據(jù)，Excel都要等20秒才能響應……
多慢才算是慢呢？
關于這個問題，不同人有不同的答案。根據(jù)某些機構(gòu)做的研究結(jié)果，下面這個表格大概描述了普通人對不同計算響應速度的感受：
實際上關于這個問題有一個直觀的對比：
比如，每次上網(wǎng)，如果不是馬上打開網(wǎng)頁，所有人的感受就是一字：慢！
需要說明的是，在上面列舉的那些原因（加載項，病毒，機器慢）等都可能造成Excel文件響應慢，但是這些并不是常見的原因，只不過是最容易指出的一些可能而已。同時，他們造成的慢往往是文件打開的時候速度慢，而計算速度受他們的影響相對較少。
也不是因為數(shù)據(jù)太多了。數(shù)據(jù)量的變化是會對速度造成影響。比如，一個含有1000行5列數(shù)據(jù)的Excel文件與含有100000行20列的數(shù)據(jù)相比，前者的相應速度一定比后者快。但是這里的“快”應該只在文件打開時有感受（略微快一些），在計算速度的相應方面，二者應該相差不大（如果沒有本文后面說的原因的話）。
很多人覺得公式太多造成了速度的降低。其實不然，我做過一個表，有30萬行15列左右的源數(shù)據(jù)，結(jié)果用公式計算，大概有2萬多行6列都是使用公式，計算速度基本上就是理解反應，最慢也不到0.5秒。而我見過太多的表，只有幾百行源數(shù)據(jù)，公式充其量也就是1000以內(nèi)的級別，結(jié)果每次計算都需要超過10秒鐘（很多超級慢的表可能會需要好幾分鐘）。
真正的原因是公式用的不對。在Excel中實現(xiàn)一個事情有很多方法。大部分人就會使用自己最熟悉的那種方法。這種方法可能是上網(wǎng)搜了一個公式，或者自己通過學習寫了一個公式。一般來說這么用沒太大問題，因為可以得到正確的結(jié)果。但是這么寫公式就會造成 計算效率太低，這個計算速度隨著數(shù)據(jù)量的增加成幾何級數(shù)的增加。這時就需要尋找計算速度最快的方法了
這里舉兩個例子說明一下不同的函數(shù)的寫法在計算速度上會有多大的差別。
1、YTD銷售額合計
這里，我們?yōu)榱擞嬎憬刂沟疆斍暗睦塾嬩N售額，我們寫了如下的公式：
=Sum($C$3:C3)
這個公式當然很漂亮，很有技巧性，重要的是，它也得到了正確的結(jié)果。
但是當你的數(shù)據(jù)超過10000行的時候，這個計算大概需要0.5秒左右的時間。
我們可以試試另外的寫法：
這里G列的公式實現(xiàn)了和E列公式同樣的功能。不過很多人可能不喜歡，因為這個公式有兩個缺點：第一，第一行和其他的公式不一致。第二，這個公式一點也不“高級”，不過如果同樣是10000行數(shù)據(jù)，這個公式大概可以在不到0.01秒的時間內(nèi)計算完成。
實現(xiàn)同樣功能的兩個公式，前一個公式的計算時間是后一個的大約600-800倍。而且隨著數(shù)據(jù)量的增加，這個差距會變得更大。
2、計算唯一項的個數(shù)
如果我們想要計算B列中唯一值有多少個，有一個非常經(jīng)典的公式（這是一個數(shù)組公式，，需要按CTRL SHIFT ENTER輸入）：
{=SUM(IF(LEN(B3:B10000)>0,1/COUNTIF(B3:B10000,B3:B10000)))}
想象很多人都見過這個公式，而且?guī)缀跻欢ǖ氖菍@個公式和寫公式的人都佩服的五體投地：這個公式太巧妙了，太高級了。
唯一的問題就是，這個公式大約需要15秒左右的時間才能計算出結(jié)果。
（要得到同樣的結(jié)果方法太多，我們這里只是比較不同公式的差別）
考慮一下這份數(shù)據(jù)，我們可以給它做一個排序（排序是一個非?？焖俚牟僮鳎?，然后添加一個輔助列，如下圖：
這樣只需要寫一個簡單的公式：
=sum(H2:H10000)
這個計算大約可以在0.02秒左右完成。兩者的差距大約在800倍左右。
都有哪些公式會造成計算速度的問題？
深入理解這個問題需要非常大的篇幅，這里不能詳細的分析。總體來說，我們最常使用的那些函數(shù)和公式都存在著一個正確的使用方式的問題：包括vlookup，sumif，countif，sumifs，countifs，sumproduct等等，有時甚至是一個簡單sum都會造成性能問題。
這里所說的正確使用方式包括：
● 使用“正確”的函數(shù)（像上面的兩個例子）
● 盡可能避免工作表間的互相引用，如果有可能，就把他們放在一個工作表中。
● 盡量避免工作簿間的互相引用，如果有，盡可能放在一個工作簿中
● 如果有工作簿間的互相引用，就把他們同時打開。
● 在使用一個工作簿時，關閉其它無關（沒有引用到的）的工作簿文件。
● 盡可能避免使用數(shù)組公式（數(shù)組公式在大多數(shù)情況下會導致非常長時間的計算，但是在某些情況下，精心設計的數(shù)組公式可以極大的減少計算時間）
注：有些人會建議將Excel的計算模式修改為手動。
在很多情況這么做可以緩解計算速度慢帶來的困擾。不過這么做有兩個缺陷，第一是這個過程有可能被打斷（例如你不小心按了ESC鍵或者點了鼠標鍵），但是你還以為已經(jīng)計算過了，這樣就會導致計算錯誤。第二，這個方法在文件保存時或者打開時，或者F9重算時的計算速度仍然很慢。
所以，這個方法治標不治本，要想一勞永逸的解決問題，還是應該努力優(yōu)化我們的表格。
上面的做法當然不能完全讓你將你的表格優(yōu)化到理想的性能，但是至少可以起到相當大的 幫助。要想做到理想的優(yōu)化，你需要非常深入的理解Excel的計算機制（我們會在后續(xù)的文章中繼續(xù)揭示Excel是如何完成計算過程的），即使如此，優(yōu)化也是一個艱巨的任務。
幸運的是，在絕大多數(shù)情況，你需要面對的公式很少。即使一個龐大的文件，計算速度非常慢，但是其中的主要問題往往只是幾個公式而已。
為了說明這個問題，我們設計了一個“Excel文件計算性能分析工具”，通過這個工具，可以分析到底這個文件的計算瓶頸在哪里。
以前兩天有一個朋友的文件為例。這是一個公司的會員管理表格，大概管理著6000左右的會員數(shù)據(jù)，其中通過一些公式和數(shù)據(jù)透視做各種報表和分析。每次輸入數(shù)據(jù)大概都需要10-15秒左右的時間。于是我們做了一個分析，分析結(jié)果如下：
分析顯示，這個表格的每次計算時間大約在16秒鐘左右。
其中有5個工作表，最主要的計算時間在會員資料這個表中，計算時間13秒多，其次是業(yè)務記錄表，計算時間不到3秒。其余的表計算時間可以忽略不計（根據(jù)經(jīng)驗和分析，計算時間在0.02秒一下的都可以忽略）。
進一步的分析可以幫助我們找到真正的計算瓶頸了：
可以看出，會員資料的G，H，I列和業(yè)務記錄的D、E列都是需要優(yōu)化的重點列，P列也需要根據(jù)情況判斷是否需要優(yōu)化。
下面列出了相應的計算公式：
接下來就是針對性的優(yōu)化這些公式就行了。經(jīng)過差不多3個小時左右的努力，優(yōu)化完成。成果顯著。下面是針對優(yōu)化后的文件的計算性能分析：
這個速度應該說還是提高的挺快的。如果研究時間再長一些，應該可以得到更加滿意的效果。
如果您的Excel文件計算很慢，請查看本周公眾號所發(fā)的第二篇文章，或者跟我們的客服聯(lián)系，我們可以幫您分析Excel文件的計算瓶頸在哪里。
聯(lián)系客服
