題 巨大的Excel電子表格花費太長時間來更新鏈接或計算公式


我有一個包含5000行和列的Excel電子表格,直到AY(大小為12MB)。除前六列外,其餘列均包含 vlookups 或其他公式。所有vlookup都在一個單獨的Excel工作表中。我已將Excel設置更改為手動更新鏈接併計算公式。現在每次我嘗試更新鏈接時,Excel都會掛起或者需要15分鐘。

現在我正在努力將工作表分成四個實例;我將按順序更新四個不同的Excel工作表。但它需要手動將索引列複製到下一階段。更新鏈接需要3-4個小時。昨天我開始收到錯誤消息:

Excel沒有足夠的資源來完成操作。

我別無選擇,只能將整個操作分成四個小步驟,需要人工干預。

我看了INDEX。不適用於我的情況。關於如何快速完成任務的任何想法?


4
2017-08-20 19:33


起源


這聽起來像是數據庫應用程序(ms-access或其他)的好候選者,而不是電子表格。 Excel確實沒有處理大量數據。 - Stewbob


答案:


5000行乘50列不是一個大工作表。 12MB是一個完全合理的中小文件大小,你不需要做任何如此重大的事情,如重做數據庫或類似的東西。

根據您的描述,問題似乎是鏈接。引用的工作簿有多大?他們鏈接到他們時會重新計算嗎?我喜歡開業前的想法:你可以看到當時正在發生的事情。

您的內存使用情況(查看任務管理器)是否超出了可用的物理RAM?一旦內存必須換成磁盤,事情開始變慢。

除了預打開和排序幫助之外,我建議看看非查找公式:是否可以轉換為數組公式並且每次計算調用一次而不是每行調用一次?

關於VLOOKUP的事情:你試過用過嗎? INDEX(value_range, MATCH(lookup_key, key_range))?有些情況下它會更快。

你沒有說你是否有任何VBA。如果這樣做,請查看調用VBA函數的次數以及它們花費的時間。對Excel對象進行大量引用的VBA代碼可能很昂貴。


3
2017-09-12 13:50





如果您的文件中的任何數據不需要是有條件的並且將保持不變(例如一個人的出生日期),那麼在您第一次獲取數據後,“複製”和“選擇性粘貼 - 值”整個列/ row以阻止文件在每次運行計算時重新計算這些單元格。對源自另一個vlookup的數據運行vlookup我注意到需要相當長的時間。


3
2017-07-27 19:04





不幸的是,這就是Excel電子表格的方式...... VBA並不是那裡最快的選擇。

您可能需要考慮將數據遷移到Microsoft Access數據庫。


2
2017-08-20 19:49





在打開包含鏈接的工作簿之前,打開要鏈接的工作簿。

然後對數據進行排序,然後使用VLOOKUP中的sorted選項(對最後一個參數使用True)。


2
2017-09-12 10:55





我使用Office 10來解決這個問題。

  1. 我打開了我的巨大文件,將其重新保存為文件類型Excel工作簿1997-2003。
    • 它提示一條消息問我一些事情(我記不起來了)。
  2. 我單擊確定,我有一個新的Excel 1997-2003文件。
    • 該文件從25MB變為1MB以下。
  3. 然後我再次將它從舊的1997-2003文件再次保存到一個新的工作簿,它變得更小了。

我有多個工作簿的問題,這一直是我解決它。


2
2017-11-04 18:06





也許可以獲得更快的計算機或升級您的RAM


1
2017-08-20 19:44



這只是到目前為止,遺憾的是:Excel不會利用多個CPU或CPU核心:( - warren


我同意Mike Woodhouse的觀點。這不是很多數據。

作為測試,我會製作一個新的電子表格,其中兩個電子表格中的數據合併為一個新的電子表格。這將告訴您問題是否發生,因為數據位於兩個單獨的電子表格中。這應該不是問題,但它將是一個很好的測試。


0
2017-09-12 14:51





我不知道這是否對您的情況有任何幫助,因為它涉及數據庫鏈接,但我遇到了同樣的問題,除了更新運行數據庫查詢的電子表格花了太長時間。

我將事情加速15倍的方法是首先使查詢選擇必要的數據,而不是讓Excel過濾它。其次,在我的數據庫連接的“連接屬性”窗口中,我勾選了“在保存工作簿之前從外部數據范圍中刪除數據”選項。這極大地幫助了我的文件不會隨著我可能正在查詢的大量數據而不斷增長。

現在我的整個刷新大約需要2-3秒而不是30-45秒。


0
2018-05-11 21:48





根據我的經驗,我發現鏈接到外部文件是最慢的過程。我讀過這是因為Excel每次都要通過一些不同的鏈接文件重新更新計算模型,而不僅僅是一個。

我從你的第一篇文章中懷疑這是開始減慢計算速度的原因。您是否嘗試將所有數據移動到同一個工作文件中並更新該文件內部的所有鏈接?


0
2017-08-06 13:30