分享一下:EXCEL中數(shù)據(jù)量過多而導致運行速度慢了解辦法,我試過,還不錯.
一個工作簿中，并沒有多少數(shù)據(jù)，但文件卻很大，運行速度較慢，有時幾近無法忍受的地步。針對這個問題，陸續(xù)也有不少朋友參與過討論，也提出過一些解決方法，但還不完整?，F(xiàn)在我將比較完整地分析Excel工作簿體積膨脹的原因，以及常用的幾種方法。（建議讀完全文，同時，強烈建議在按照本文所提到的一些方法進行操作之前要保留文件的備份） 第一部分：如何給Excel工作簿減肥除了工作簿本身有宏病毒（關于如何識別宏病毒，本文的結束處有說明）外，有下列的幾個原因可能導致工作簿異常膨脹 一。工作表中的格式過于復雜（如多種字體，多種顏色，條件格式等等） 我們都知道，一個工作表有65536*256個單元格，這個非常大的一個范圍。我們在應用一些格式設置時，往往為了方便，直接在整行，或整列，或者在多行和多列中應用，這樣其實是不妥當?shù)?。我相信很少有人會用滿整列，或者整行。大量的沒有用到的單元格被加上了一些格式后，Excel并不總是知道這個單元格是不需要用的，而往往在計算時會包括這些單元格。另外，格式的多樣性（包括字體，顏色等），勢必是要造成Excel文件體積變大，這是不難理解的。用盡量少的格式樣式，如字體和顏色，畢竟Excel的優(yōu)勢不是在于文書，版面的展示（那是Word，PowerPoint等的強項） 刪除“空白”的單元格。找到工作表中最右下角的單元格（請注意：除非你有自信，請不要過分依賴Excel 的"定位"=>"最后一個單元格"所給你的結果），我是說你可以手工找到這個單元格。然后選中這個單元格右邊所有的列，然后執(zhí)行菜單命令："編輯"=〉"清除"=〉"全部"，接下來，選中這個單元格下面所有的行，同樣執(zhí)行菜單命令："編輯"=〉"清除"=〉"全部",然后保存 二。圖片或者其他繪圖圖形較多，或者圖片選擇了不恰當?shù)母袷?選擇適當?shù)膱D片格式　　在文檔中引用的圖片格式一般有三種，BMP、JPG、GIF。BMP格式保存的圖片保真度較高，但大小往往是其它兩種格式的幾倍至幾十倍，而照片、掃描圖片等用GIF格式保存則失真非常嚴重，因此建議圖片先采用JPG格式保存，然后再引入到文檔中，這種格式的圖片大小不僅比BMP格式小很多，而且往往也比GIF的格式小，而保真度與BMP格式則相差無幾?！　±谩安迦搿币雸D片　　插入圖片有兩種方法，第一種是將圖片保存為一個JPG格式的文件，選擇“插入→圖片→來自文件”，打開“插入圖片”對話框，選中所需插入的圖片文件，點擊“插入”即可。另一種是用“畫圖”、“Microsoft照片編輯器”等打開這個JPG文件，選中并復制，然后在文檔中點擊“粘貼”即可插入。強烈建議大家采用第一種方法，雖然兩種方法得到的圖片質量是一樣的，但第一種方法所形成的文檔的大小可能會比第二種小幾十倍! 徹底清除圖形對象：例如我們自己畫的一些圖形，比如你現(xiàn)在不要用它們了，你選擇行或者列范圍刪除，清除都是清除不掉了，他們只是可能縮小了。這個時候，就可以用到本版"流浪的風"提到過的一個方法 1.先找到其中的一個文本框（找不到就自己添加一個） 2.選中這個文本框，按F5--->定位條件--->對象-->確定 3.按Delete清除 4.保存關閉 5.看看現(xiàn)在文件有多大，打開看看速度還慢不慢 三。公式和名稱較多或者公式，名稱，數(shù)據(jù)透視表等所引用的單元格范圍過大 由于和第二點類似的原因，我們在定義名稱，編寫公式，指定數(shù)據(jù)透視表的數(shù)據(jù)源時往往圖一時方便，而指定了過大的單元格范圍。例如在A列中有包括標題在內的10個數(shù)據(jù)（A1：A10），標題為“姓名”,我們現(xiàn)在要定義一個名稱，例如"姓名"，很多人會用 插入=〉名稱=〉指定=〉首行，這當然是方便的，但這樣的話“姓名”這個名稱就引用了A2:A65536,而不是實際的A2：A10。你能想象到兩者的差別嗎？ 這時候，有的朋友要說：我這樣做的原因是因為我的數(shù)據(jù)是在不斷增加的呀，我可不想每次在變動的時候都去改這個名稱。 當然，你是對的，誰會愿意這樣做呢？ 當我確信我定義的這個名稱所引用的范圍不可能是固定的時候，我采用了一個方法就是“動態(tài)命名”。聽起來有點耳熟對嗎？請看這個例子：為簡單起見，我們假設數(shù)據(jù)都是連續(xù)地在A10后面開始添加，也就是說我們希望當我們添加到A15時，這個"姓名"就如我所愿地指向A2：A15，而這一切都是自動完成的。那么你可以在"插入"=〉"名稱"=>"定義"對話框中，找到"姓名"，然后修改引用位置為=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)，然后點擊"添加"。請留步，先不要急著關閉這個對話框。你現(xiàn)在可以將鼠標放在"引用位置"的這個框里面，由此來驗證你要的結果。看到了嗎？工作表中那一閃一閃的區(qū)域就表示了目前"姓名"所引用的單元格范圍。 這只是一個簡單的示范，利用這種技巧，可能讓我們用最經(jīng)濟的方式得到我們需要的結果。 在公式引用中，在指定數(shù)據(jù)透視表的數(shù)據(jù)源時，都可以運用類似的技巧。當然，我不會推薦你寫類似這樣的公式=CountA（OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)），而是推薦你先定義好這個名稱，然后這樣寫公式：=CountA（姓名) 這種效果在數(shù)組公式中更明顯，除非你有足夠的耐心和勇氣，請不要在數(shù)組公式用引用過大的單元格范圍，特別是那些不必要的單元格。 請注意，以上的檢查應該是針對工作簿中的所有工作表，包括隱藏的 四。VBA代碼，尤其是用戶窗體的影響 現(xiàn)在很多朋友都已經(jīng)學會了用VBE來構建自己的工程，這是多么令人振奮的一件事！但今天我們要討論的是如何處理VBA工程可能帶來一些副作用以及如何壓縮它？ 有很多測試證明，用戶窗體會是增加文件大小的比較突出的原因之一。而反復地改寫工程中模塊的代碼，也或多或少地會增加文件的大?。ㄎ覀兛梢赃@樣理解：反復地改寫工程代碼，總是會留下一些痕跡和碎片） 對于這個問題，可以考慮將所有模塊，窗體，都導出為文件，然后保存，然后再依次導入。 關于用戶窗體，作為開發(fā)者應該考慮的是：是否真的有必要用某個窗體？舉個簡單的例子，如果只是接收用戶輸入數(shù)值，就完全可以用InputBox方法或者屬性，而不需專門用一個窗體。同時，工作表本身可以用來做用戶交互的界面，應該充分利用。 五。外部鏈接（特別是死鏈接）的影響 有的時候，我們的工作簿中可能包含了一些外部引用，這樣我們可以共享其他工作簿的一些信息。如果你的工作簿中包含外部鏈接，你可以通過"編輯"=>"鏈接"對話框中查看到。默認情況下，文件在打開時，總是嘗試去鏈接源文件，以刷新數(shù)據(jù)。在保存時，會紀錄鏈接的變化情況。 當源文件的位置或者內容發(fā)生變化時，就可能產生死鏈接。 我個人非常不主張用鏈接方式來實現(xiàn)不同文件間數(shù)據(jù)的共享，這既不是最方便的方法，而且在文件分發(fā)過程中會遇到一些問題。我當然知道數(shù)據(jù)共享是有意義的，但我經(jīng)常會嘗試用其他的途徑來實現(xiàn)。 六。關于自動保存選項的考量 選中這個選項時，每次進行保存文檔的操作則只保存文檔的修改部分，保存速度較快，但文檔的大小也會增加，即使是對文檔進行刪減操作也是如此。目前計算機的速度較快，因此開啟這項功能所帶來速度的增加毫不明顯，但付出的代價是文檔的大小急劇膨脹，建議不要選中這個選項。試著關閉這個選項，再對文檔稍作修改，然后保存，你會驚奇地發(fā)現(xiàn)文檔的大小會大幅度縮小! 七。文件異常退出（或者其他不可預見的原因）造成的工作簿內工作表結構方面的損壞 有時候，由于一些不可預見的原因（例如停電），Excel被迫異常退出。雖然目前沒有專門的工具可以檢測這種情況對工作簿內部可能造成的損壞程度，但是有理由相信多少是有影響的。 如果你的文件中不存在前面提到的幾個問題，同時你還是確信文件體積不正常，你可以嘗試如下的方法 新建一個工作簿，把現(xiàn)有文件中的工作表一一剪切到新的工作簿中。請注意這里用的是“剪切”方法，不是“移動或復制工作表”，也不是“復制”。沒錯，就是先選中工作表中的內容，然后“剪切”，光標移動到目標工作表，然后“粘貼”。這樣做唯一不足的地方就是，目標工作表的行和列格式可能要稍微調整一下。 第二部分：如何給Excel加速 可能影響Excel的運行速度的幾個方面 1。設備的配置是否合理？特別是Office 2003，功能的確是很強大了，但相應的對硬件的要求也提高了。一般用戶都用Windows Xp系統(tǒng)，然后裝Office 2003 ，當然還有其他的軟件了，這樣的情況，我推薦的配置是：CPU P4 1G及以上，內存256MB及以上，硬盤40G及以上。根據(jù)自己的計算機的配置情況，你也可能選擇只安裝部分（而不一定是全部）Office組件和工具。 2。加載宏。Excel內置了幾個加載宏程序，可以幫助我們實現(xiàn)一些高級功能。另外，我們自己也可以編寫，或者在網(wǎng)上也可以找到更多的各式各樣的加載宏程序。在使用加載宏時我可以給出的建議有：首先一定要確認加載宏的來源是否正當？下載后，安裝前，強烈推薦要先殺毒。其次，要用的時候再加載它，而不主張一直把所有的加載宏都加載上。 3。自動重算，自動保存選項的考量。這兩個選項在一定程度上給我們的工作帶來了便利。但在有些時候（例如是公式比較多的時候），特別是自動重算，可能導致運行速度變慢。前面提到：自動保存選項也是可能導致文件增大的一個因素。有選擇性地在操作一些文件時關閉這兩個選項，可能會對運行速度有幫助。 4。迭代計算選項的考量。出于某種特殊的需求，我們可能會允許工作簿進行迭代計算，也就是循環(huán)引用（這種有目的的循環(huán)引用可以被用到解聯(lián)立方程等一些特殊的場合）。但是這種循環(huán)引用會導致工作表頻繁地被計算，當工作表任何地方發(fā)生變化時，就被重新計算一次。所以，這一點也是影響Excel速度的原因之一。建議不要勾上這個選項。 5。文件不正常（主要是本文第一部分所提到的一些情況）。這一項的解決不再贅述。 6。不恰當?shù)厥褂昧似渌恍┩獠抗ぞ呖赡軐е碌膯栴}。例如一些測試版的軟件，就好比目前的VSTO 2005 BETA2 ，這里面有一些針對Excel進行編程的工具。有的朋友(請注意：只是可能）就會發(fā)現(xiàn)，安裝了這個工具后，或者即使后來卸載了這個工具后，Excel在讀文件，特別是內部有宏的文件時非常奇怪地“吃”內存，具體地說就是Excel會瘋狂地占用內存，以致根本就無法正常工作。對于這種情況，有一個比較簡便的方法就是使用：幫助=〉檢測與修復 ，有兩個選項：修復時恢復快捷方式；放棄自定義設置并恢復默認設置。如果兩個選項都選上了，或者至少第二個選項選上了的話，執(zhí)行修復完后一般都能解決這個問題。請注意：在執(zhí)行這個操作之前，請確定你已經(jīng)清楚這樣操作可能導致的一些問題（例如Outlook的個人數(shù)據(jù)文件可能需要重新指定：文件不會丟失，但修復完重新開Outlook前，請到控制面板=〉郵件 對話框中指定你的個人數(shù)據(jù)文件）。強烈建議你要先閱讀相應的幫助文檔。 第三部分：如何識別宏病毒？ 在VBE（Visual Basic編輯器）中，激活工程資源管理器，并定位到你要檢查的工作簿。 檢查工作簿中所有模塊（包括ThisWorkBook模塊）中的代碼，查看是否有你不熟悉的VBA代碼。病毒代碼往往格式混亂并且含有大量奇怪的變量名稱。另一個選擇是使用市售的查毒軟件。 為保證安全，建議將OFFICE 的宏安全性級別設置為中級后者以上（請注意：如果設置為高級的話，你將不會收到任何的通知，Excel自動禁止所有宏運行） 第四部分：結語 1。正如我不止一次在新聞組中提到的一樣，Excel并不是設計用來存儲數(shù)據(jù)的。所以我不推薦在Excel中存放太多的數(shù)據(jù)。雖然Excel工作簿所允許的工作表數(shù)量并沒有什么具體的限制（Excel2003），但我推薦的工作表數(shù)量不要超過10個。上次有個朋友說他有一個文件足有81MB，這是在是令人擔憂的。即使前三部分提到的一些問題都解決好了，而數(shù)據(jù)量如此之大的話，速度還肯定是慢的。 2。以上為個人經(jīng)驗，僅供參考和研究交流之用 3。在你決定按照以上提到的一些方法進行操作之前，建議你保留一份文件備份到安全的地方。