☰

Excel 在嘗試計算一個或多個公式時耗盡資源

tag-icon • tag-icon microsoft-excel vba memory resources microsoft-excel-2007
Excel 在嘗試計算一個或多個公式時耗盡資源

我收到錯誤訊息“Excel 在嘗試計算一個或多個公式時耗盡資源”一我的計算機（共 2 台）。

我的工作表包含：（在單一工作表中，而不是工作簿中，另一個工作表中有公式）

  • 1,000,000 個公式

  • 基於 900,000 行資料的資料透視表

當我在「計算表」操作（僅限目前工作表）上執行 excel/vba 時，程式將彈出以下錯誤訊息：

Excel ran out of resources while attempting to calculate one or more formulas

我既不“刷新”（Excel或VBA）中的資料透視表，也不“刷新”（Excel或VBA）中的“計算表”

我有2台電腦：

  • 均運行 64 位元 Windows 7，

  • 兩者都運行 Excel 2007 32 位，

  • 我啟動 Windows 後立即執行 Excel，

  • 我的開發 PC 具有 2GB RAM 可以毫無問題地運行，

  • 另一台具有 6GB RAM 的電腦顯示ran out of resources錯誤訊息

  • 在同一組資料、同一 Excel 檔案上執行

我還注意到，在我的開發 PC 上，它使用 ~ 1.2G RAM，而不是工作 PC，在單擊“刷新”/“計算”操作之前，它使用 900M RAM。

編輯

非工作計算機可處理10萬行資料以內的數據

我的問題：

  1. 為什麼它適用於內存較小的計算機，但不適用於內存較大的計算機？ （主要問題）
  2. 如何減少 Excel 使用的記憶體？ （子問題）（刪除資料除外）

感謝任何幫助，請指出我正確的方向或只是提供一些線索。

編輯：我正在考慮刪除公式，並將邏輯移至 vba 中，並透過可能每 10,000 行快取資料來實現。但是，如果“刷新”資料透視表顯示相同的錯誤，這將無法解決我的問題。

答案1

原因可能是 2 台電腦上的 32 位元記憶體碎片不同（通常很難使用所有理論上可用的 2 GB）。
您可以透過減少列數和/或減少某些列所需的記憶體（文字字串是一個不錯的選擇）來減少資料透視表使用的記憶體量。
（您可以使用 VBA PivotCache.memoryUsed 測量資料透視快取使用的記憶體量）
我假設您透過將查詢中的資料直接讀取到資料透視快取中來建立資料透視緩存，而不是將查詢資料放到工作表上並基於工作表上的樞軸，這將使用更多記憶體。
您沒有說 >1000000 個公式是什麼，所以我沒有任何改進建議。
如果您想在 Excel 資料透視表等中使用大量數據，您可以從 64 位元版本的 Excel 2010 獲得更好的結果，該版本沒有 2 GB 的限制

答案2

感謝所有的建議、閱讀和幫助。我已經解決了這個問題

  1. 每約 100,000 行將所有公式逐步更改為值 --- 這將 excel 佔用的記憶體減少 10%
  2. 刪除了工作簿中任何不必要的資料/工作表（資料按摩之前的原始資料）---這將 Excel 佔用的記憶體減少了 40%

還有閱讀http://www.add-ins.com/support/out-of-memory-or-not-enough-resource-problem-with-microsoft-excel.htm說明為什麼 6G RAM PC 會面臨這個問題，而不是 2G RAM PC。我認為這是因為 6G PC 上有很多 excel 插件也消耗 RAM。

感謝您的幫助。

答案3

舊問題但對所有版本仍然有效，當我使用的公式範圍太大時，即使大多數字段為空，我也會遇到類似的問題，例如：DO_CALCULATION(D1:D100000) 即使會佔用大量時間和內存如果只有儲存格D1:D10 有任何值。因此，在嘗試使公式為資料擴展做好準備時要小心，保持較小的範圍:)

答案4

答案是升級到 64 位元版本的 Excel。它可以處理更大的記憶體範圍，而且通常速度更快，如果您有一個很大的電子表格，那麼它是適合該工作的工具。

相關內容