Excel文件的計算“速度”是一個被忽視的問題,。你可以很輕易的找到為某個特定問題如何寫一個公式，但是你上網(wǎng)搜“我的Excel文件為什么這么慢,？”,，你會得到一堆似是而非，完全不能解決你的問題的“答案”,。

這是百度上的前幾個答案,，不用打開鏈接也會發(fā)現(xiàn)這些操作幾乎不可能真正解決Excel的計算速度問題。

在看某乎上的幾個答案：

,？,？？

20秒以內(nèi)是正常的,？

我實在是沒法接受這種答案,。

如果你再到所謂的一些專業(yè)Excel論壇中去問，你還會得到這樣的答案：

整理一下磁盤碎片

是不是中毒了

是不是數(shù)據(jù)太多了

該換機器了

……

如果你嘗試過通過上面的各種方法想提高Excel的計算速度,，我相信基本上你會得到下面的結(jié)論：

Excel是不是就這樣，公式多了（或者數(shù)據(jù)多了）就會慢的要死?。,。?/span>

可真的是這樣嗎,？

為什么我們應(yīng)該注重速度呢,？

Excel的計算速度從兩個方面影響我們的工作效率。首先,，速度慢本身就帶來了效率的降低,。其次，計算速度慢還影響我們的心情,，降低對進行該項工作的意愿,，甚至產(chǎn)生畏懼心理。想象一下，如果你每天都要打開一個Excel文件,，記錄幾條當天發(fā)生的數(shù)據(jù),，但是每次在一個單元格中輸入一個數(shù)據(jù)，Excel都要等20秒才能響應(yīng)……

多慢才算是慢呢,？

關(guān)于這個問題,，不同人有不同的答案。根據(jù)某些機構(gòu)做的研究結(jié)果,，下面這個表格大概描述了普通人對不同計算響應(yīng)速度的感受：

實際上關(guān)于這個問題有一個直觀的對比：

比如,，每次上網(wǎng)，如果不是馬上打開網(wǎng)頁,，所有人的感受就是一字：慢,！

為什么你的Excel文件計算速度會慢？

需要說明的是,，在上面列舉的那些原因（加載項,，病毒，機器慢）等都可能造成Excel文件響應(yīng)慢,，但是這些并不是常見的原因,，只不過是最容易指出的一些可能而已。同時,，他們造成的慢往往是文件打開的時候速度慢,，而計算速度受他們的影響相對較少。

也不是因為數(shù)據(jù)太多了,。數(shù)據(jù)量的變化是會對速度造成影響,。比如，一個含有1000行5列數(shù)據(jù)的Excel文件與含有100000行20列的數(shù)據(jù)相比,，前者的相應(yīng)速度一定比后者快,。但是這里的“快”應(yīng)該只在文件打開時有感受（略微快一些），在計算速度的相應(yīng)方面,，二者應(yīng)該相差不大（如果沒有本文后面說的原因的話）,。

很多人覺得公式太多造成了速度的降低。其實不然,，我做過一個表,，有30萬行15列左右的源數(shù)據(jù)，結(jié)果用公式計算,，大概有2萬多行6列都是使用公式,，計算速度基本上就是理解反應(yīng)，最慢也不到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倍左右。

都有哪些公式會造成計算速度的問題,？

深入理解這個問題需要非常大的篇幅,，這里不能詳細的分析?？傮w來說,，我們最常使用的那些函數(shù)和公式都存在著一個正確的使用方式的問題：包括vlookup，sumif,，countif,，sumifs，countifs,，sumproduct等等,，有時甚至是一個簡單sum都會造成性能問題。

這里所說的正確使用方式包括：

●  使用“正確”的函數(shù)（像上面的兩個例子）

●  盡可能避免工作表間的互相引用,，如果有可能,，就把他們放在一個工作表中。

●  盡量避免工作簿間的互相引用,，如果有,，盡可能放在一個工作簿中

●  如果有工作簿間的互相引用，就把他們同時打開,。

●  在使用一個工作簿時,，關(guān)閉其它無關(guān)（沒有引用到的）的工作簿文件。

●  盡可能避免使用數(shù)組公式（數(shù)組公式在大多數(shù)情況下會導致非常長時間的計算,，但是在某些情況下,，精心設(shè)計的數(shù)組公式可以極大的減少計算時間）

注：有些人會建議將Excel的計算模式修改為手動。

在很多情況這么做可以緩解計算速度慢帶來的困擾,。不過這么做有兩個缺陷,，第一是這個過程有可能被打斷（例如你不小心按了ESC鍵或者點了鼠標鍵），但是你還以為已經(jīng)計算過了,，這樣就會導致計算錯誤,。第二,，這個方法在文件保存時或者打開時，或者F9重算時的計算速度仍然很慢,。

所以,，這個方法治標不治本，要想一勞永逸的解決問題,，還是應(yīng)該努力優(yōu)化我們的表格,。

上面的做法當然不能完全讓你將你的表格優(yōu)化到理想的性能，但是至少可以起到相當大的 幫助,。要想做到理想的優(yōu)化,，你需要非常深入的理解Excel的計算機制（我們會在后續(xù)的文章中繼續(xù)揭示Excel是如何完成計算過程的），即使如此,，優(yōu)化也是一個艱巨的任務(wù),。

幸運的是，在絕大多數(shù)情況,，你需要面對的公式很少,。即使一個龐大的文件，計算速度非常慢,，但是其中的主要問題往往只是幾個公式而已,。

為了說明這個問題，我們設(shè)計了一個“Excel文件計算性能分析工具”,，通過這個工具,，可以分析到底這個文件的計算瓶頸在哪里。

以前兩天有一個朋友的文件為例,。這是一個公司的會員管理表格,，大概管理著6000左右的會員數(shù)據(jù)，其中通過一些公式和數(shù)據(jù)透視做各種報表和分析,。每次輸入數(shù)據(jù)大概都需要10-15秒左右的時間,。于是我們做了一個分析，分析結(jié)果如下：

分析顯示,，這個表格的每次計算時間大約在16秒鐘左右,。

其中有5個工作表，最主要的計算時間在會員資料這個表中,，計算時間13秒多,，其次是業(yè)務(wù)記錄表，計算時間不到3秒,。其余的表計算時間可以忽略不計（根據(jù)經(jīng)驗和分析,，計算時間在0.02秒一下的都可以忽略）。

進一步的分析可以幫助我們找到真正的計算瓶頸了：

可以看出,，會員資料的G,，H，I列和業(yè)務(wù)記錄的D,、E列都是需要優(yōu)化的重點列,，P列也需要根據(jù)情況判斷是否需要優(yōu)化。

下面列出了相應(yīng)的計算公式：

接下來就是針對性的優(yōu)化這些公式就行了,。經(jīng)過差不多3個小時左右的努力,，優(yōu)化完成。成果顯著,。下面是針對優(yōu)化后的文件的計算性能分析：

這個速度應(yīng)該說還是提高的挺快的,。如果研究時間再長一些，應(yīng)該可以得到更加滿意的效果,。

如果您的Excel文件計算很慢,，請查看本周公眾號所發(fā)的第二篇文章，或者跟我們的客服聯(lián)系,，我們可以幫您分析Excel文件的計算瓶頸在哪里,。