有時(shí)候即使文件只有1、2M大小的時(shí)候，在有較多公式運(yùn)算時(shí)右下角還是會(huì)跳出來運(yùn)算進(jìn)度，一等就是一兩分鐘，運(yùn)氣不好就死掉，這種情況很讓人捉急。
很多大型企業(yè)做財(cái)務(wù)、資產(chǎn)管理、數(shù)據(jù)管理崗位的，操作數(shù)十萬行左右規(guī)模記錄，同時(shí)還掛著VLOOKUP運(yùn)算的Excel更是常見。動(dòng)輒數(shù)分鐘的等待，甚至程序卡死，電腦卡死的情況屢見不鮮。
針對(duì)這些惱人的場(chǎng)景，除去提升計(jì)算機(jī)性能外，還有什么其他的習(xí)慣能讓EXCEL運(yùn)算速度加快？跟數(shù)據(jù)格式、公式用法之類的有直接關(guān)系么？
Excel數(shù)據(jù)計(jì)算的物理基礎(chǔ)依賴CPU和內(nèi)存，大數(shù)據(jù)量和復(fù)雜公式的處理方式，直接影響著內(nèi)存的占用，不合理的設(shè)計(jì)，很容易導(dǎo)致內(nèi)存占滿。
Excel
文件優(yōu)化方法
更多Excel與統(tǒng)計(jì)分析知識(shí)和資料，請(qǐng)關(guān)注本公眾號(hào)！
01
善待Excel，合理設(shè)計(jì)數(shù)據(jù)的布局
評(píng)估你的工作需求是否應(yīng)該使用EXCEL，文字處理用WORD，多媒體演示用PPT，批量復(fù)雜重查詢多關(guān)聯(lián)數(shù)據(jù)表用Access。超過3w行的數(shù)據(jù)盡管理論上EXCEL還是可以處理(最多1048576行)，但要審慎選擇。
相同性質(zhì)或加在一起解決某類特定問題的sheet頁組成一個(gè)工作簿文件(就是EXCEL文件)，既不要一個(gè)工作表一個(gè)文件(太散，沒有結(jié)構(gòu))也不要所有工作表堆在一個(gè)文件里(后續(xù)很難歸類檢索)。
不要跨工作簿引用數(shù)據(jù)。
02
善待數(shù)據(jù)，原始數(shù)據(jù)要規(guī)范
公式結(jié)果適當(dāng)?shù)臅r(shí)候轉(zhuǎn)化為數(shù)字，可使用選擇性粘貼功能（避免因引用或數(shù)據(jù)源改變影響計(jì)算結(jié)果）。
不要在同一列大量混合存放文本和數(shù)字，除非有明確理由，應(yīng)拆開存放。
除非表格本身已經(jīng)是最終輸出狀態(tài)，否則確保數(shù)據(jù)有列字段名，字段名不要使用多行（回車換行或者合并單元格等）不要有表名（會(huì)破壞列字段名稱默認(rèn)位置）。
全稱、簡(jiǎn)稱、別稱不要混用，穩(wěn)妥起見建議設(shè)置數(shù)據(jù)驗(yàn)證只支持下拉選擇。
如果有必要，使用數(shù)據(jù)庫思維檢查一下你的表格是否規(guī)范(這章也提到了一部分)。
04
要關(guān)注公式函數(shù)的計(jì)算效率，盡量使用效率高的函數(shù)，或使用其他功能代替。
1. 非必要的情況下，不使用可變函數(shù)（易失性函數(shù)）。
2. 使用其他行和列計(jì)算并存儲(chǔ)中間結(jié)果一次，以便在其他公式中重復(fù)使用它們。并且，如前所述，盡可能引用其他單元格已有的計(jì)算結(jié)果，這樣可提高運(yùn)算效率。
3. 減少每個(gè)公式中的引用數(shù)，最大程度地減少函數(shù)中的引用單元格范圍。
4.盡可能使用最有效的函數(shù)（一般情況下自定義函數(shù)慢于 Excel 中的內(nèi)置函數(shù)），編制適當(dāng)?shù)墓剑M可能減少公式的計(jì)算次數(shù)
=IF(ISERROR(VLOOKUP('張三',$A$2:$C$1000,3,0)),'查無此人',VLOOKUP('張三',$A$2:$C$1000,3,0))
如果表格A2：A1000中有“張三”, 使用上面這個(gè)公式，則 Excel 要運(yùn)算VLOOKUP函數(shù)兩次。Excel 2007以后的版本中，可以使用 IFERROR 來減少運(yùn)算的次數(shù)：
05
非必要的情況下不使用會(huì)觸發(fā)重新計(jì)算的操作
06
可能的情況下先對(duì)數(shù)據(jù)進(jìn)行排序，再使用查找引用。盡可能避免對(duì)未排序數(shù)據(jù)執(zhí)行查找，因?yàn)樗俣群苈?/span>
07
關(guān)閉自動(dòng)計(jì)算
在手動(dòng)計(jì)算模式下，可以通過按 【F9】觸發(fā)智能重新計(jì)算。使用【Shift+F9】 僅重新計(jì)算所選工作表，按【Ctrl+Alt+F9】強(qiáng)制對(duì)所有公式執(zhí)行完整計(jì)算，也可以通過按【Ctrl+Shift+ Alt+F9】 強(qiáng)制徹底重新構(gòu)建依賴項(xiàng)和執(zhí)行完整計(jì)算。
F9計(jì)算所有打開的工作簿中的所有工作表。
按 Shift+F9 可計(jì)算活動(dòng)工作表。
按 Ctrl+Alt+F9 可計(jì)算所有打開的工作簿中的所有工作表，不管它們自上次計(jì)算以來是否已更改。
如果按Ctrl+Alt+Shift+F9，則會(huì)重新檢查相關(guān)公式，然后計(jì)算所有打開的工作簿中的所有單元格，其中包括未標(biāo)記為需要計(jì)算的單元格。
Excel優(yōu)化
小技巧
01
公式只保留第一行
這個(gè)模仿數(shù)據(jù)庫的更新方法。如果你使用的是一個(gè)數(shù)十萬行的大數(shù)據(jù)表，而里面涉及計(jì)算，為什么要把公式儲(chǔ)存幾十萬遍呢？?jī)?chǔ)存一個(gè)結(jié)果比如42，比儲(chǔ)存計(jì)算過程要簡(jiǎn)便的多。
操作時(shí)，一列30萬行公式計(jì)算，平時(shí)只留第一行為公式，剩下粘貼為值。數(shù)據(jù)有更新時(shí)重新填充公式，再F9，然后再粘貼為值只留第一行公式。能剩下小一半的文件體積。
02
謹(jǐn)慎使用數(shù)組公式
數(shù)組公式非常絢麗，但是也非常吃CPU。沒有什么問題是一個(gè)“好的數(shù)據(jù)結(jié)構(gòu)”+“簡(jiǎn)單公式”解決不了的。
03
復(fù)雜公式使用VBA代替
3行原則：公式超過3行，請(qǐng)考慮VBA！
04
謹(jǐn)慎使用照相機(jī)功能
05
不要把Excel當(dāng)數(shù)據(jù)庫用
大量的原始數(shù)據(jù)和中間計(jì)算結(jié)果直接存在表格里，一開始用的方便，數(shù)據(jù)量一大就是SB了。我們公司一個(gè)老交易員的Excel表格有250MB. 打開表格要1分鐘。這個(gè)問題并沒有什么簡(jiǎn)的解決辦法。最好的作法當(dāng)然是把數(shù)據(jù)儲(chǔ)存交給專業(yè)語言如sql，但一般初學(xué)者要搞這個(gè)還是太麻煩。我的建議是當(dāng)你的數(shù)據(jù)達(dá)到100MB的時(shí)候，恭喜你，你處理的工作已經(jīng)開始有點(diǎn)復(fù)雜了，去學(xué)門專業(yè)的編程語言吧。建議的順序是：VBA, MATLAB, SQL。
05
關(guān)閉自動(dòng)計(jì)算
聯(lián)系客服
