再好的電腦也一樣帶不動!為什麼你的Excel文件會這麼慢?

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

再好的電腦也一樣帶不動!為什麼你的Excel文件會這麼慢?

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

在看某乎上的幾個答案:

再好的電腦也一樣帶不動!為什麼你的Excel文件會這麼慢?

???

20秒以內是正常的?

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

如果你再到所謂的一些專業Excel論壇中去問,你還會得到這樣的答案:

整理一下磁盤碎片

是不是中毒了

是不是數據太多了

該換機器了

……

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

Excel是不是就這樣,公式多了(或者數據多了)就會慢的要死!!!

可真的是這樣嗎?

為什麼我們應該注重速度呢?

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

多慢才算是慢呢?

關於這個問題,不同人有不同的答案。根據某些機構做的研究結果,下面這個表格大概描述了普通人對不同計算響應速度的感受:

再好的電腦也一樣帶不動!為什麼你的Excel文件會這麼慢?

實際上關於這個問題有一個直觀的對比:

比如,每次上網,如果不是馬上打開網頁,所有人的感受就是一字:慢!

為什麼你的Excel文件計算速度會慢?

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

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

很多人覺得公式太多造成了速度的降低。其實不然,我做過一個表,有30萬行15列左右的源數據,結果用公式計算,大概有2萬多行6列都是使用公式,計算速度基本上就是理解反應,最慢也不到0.5秒。而我見過太多的表,只有幾百行源數據,公式充其量也就是1000以內的級別,結果每次計算都需要超過10秒鐘(很多超級慢的表可能會需要好幾分鐘)。

真正的原因是公式用的不對。在Excel中做到一個事情有很多方法。大部分人就會使用自己最熟悉的那種方法。這種方法可能是上網搜了一個公式,或者自己通過學習寫了一個公式。一般來說這麼用沒太大問題,因為可以得到正確的結果。但是這麼寫公式就會造成 計算效率太低,這個計算速度隨著數據量的增加成幾何級數的增加。這時就需要尋找計算速度最快的方法了

這裡舉兩個例子說明一下不同的函數的寫法在計算速度上會有多大的差別。

1、YTD銷售額合計

再好的電腦也一樣帶不動!為什麼你的Excel文件會這麼慢?

這裡,我們為了計算截止到當前的累計銷售額,我們寫了如下的公式:

=Sum($C$3:C3)

這個公式當然很漂亮,很有技巧性,重要的是,它也得到了正確的結果。

但是當你的數據超過10000行的時候,這個計算大概需要0.5秒左右的時間。

我們可以試試另外的寫法:

再好的電腦也一樣帶不動!為什麼你的Excel文件會這麼慢?

這裡G列的公式做到了和E列公式同樣的功能。不過很多人可能不喜歡,因為這個公式有兩個缺點:第一,第一行和其他的公式不一致。第二,這個公式一點也不「高級」,不過如果同樣是10000行數據,這個公式大概可以在不到0.01秒的時間內計算完成。

做到同樣功能的兩個公式,前一個公式的計算時間是後一個的大約600-800倍。而且隨著數據量的增加,這個差距會變得更大。

2、計算唯一項的個數

再好的電腦也一樣帶不動!為什麼你的Excel文件會這麼慢?

如果我們想要計算B列中唯一值有多少個,有一個非常經典的公式(這是一個數組公式,,需要按CTRL SHIFT ENTER輸入):

{=SUM(IF(LEN(B3:B10000)>0,1/COUNTIF(B3:B10000,B3:B10000)))}

想像很多人都見過這個公式,而且幾乎一定的是對這個公式和寫公式的人都佩服的五體投地:這個公式太巧妙了,太高級了。

唯一的問題就是,這個公式大約需要15秒左右的時間才能計算出結果。

(要得到同樣的結果方法太多,我們這裡只是比較不同公式的差別)

考慮一下這份數據,我們可以給它做一個排序(排序是一個非常快速的操作),然後添加一個輔助列,如下圖:

再好的電腦也一樣帶不動!為什麼你的Excel文件會這麼慢?

這樣只需要寫一個簡單的公式:

=sum(H2:H10000)

這個計算大約可以在0.02秒左右完成。兩者的差距大約在800倍左右。

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

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

這裡所說的正確使用方式包括:

● 使用「正確」的函數(像上面的兩個例子)

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

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

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

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

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

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

再好的電腦也一樣帶不動!為什麼你的Excel文件會這麼慢?

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

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

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

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

為了說明這個問題,我們設計了一個「Excel文件計算性能分析工具」,通過這個工具,可以分析到底這個文件的計算瓶頸在哪裡。

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

再好的電腦也一樣帶不動!為什麼你的Excel文件會這麼慢?

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

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

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

再好的電腦也一樣帶不動!為什麼你的Excel文件會這麼慢?

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

下面列出了相應的計算公式:

再好的電腦也一樣帶不動!為什麼你的Excel文件會這麼慢?

接下來就是針對性的優化這些公式就行了。經過差不多3個小時左右的努力,優化完成。成果顯著。下面是針對優化後的文件的計算性能分析:

再好的電腦也一樣帶不動!為什麼你的Excel文件會這麼慢?

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

如果您的Excel文件計算很慢,請查看本周公眾號所發的第二篇文章,或者跟我們的客服聯繫,我們可以幫您分析Excel文件的計算瓶頸在哪裡。