☰

Excel 文件詳細狀態緩慢

tag-icon • tag-icon microsoft-excel performance logging debug
Excel 文件詳細狀態緩慢

我繼承了一組優化不佳的大型 Excel 文檔，用於對定制的 matlab 模擬器的輸出進行後期處理，但速度非常慢。它們打開、重新計算和保存的速度很慢；使用它們只會使它們變慢（打開/保存最多需要 45 分鐘）。我被允許優化它們以使它們運行得更好，但特別不允許將它們燒毀並重新開始。我已經採取了容易實現的目標，改進了格式，刪除了多餘的公式，並修正、刪除或發現了任何錯誤。

有沒有辦法可以啟用某種調試或詳細日誌記錄來查看該過程在哪裡花費了這麼長時間。這樣我就可以集中精力而不是追求最小的收穫。

答案1

要回答您的問題，Excel 中沒有效能日誌記錄或偵錯輸出。您可以在 VBA 程式碼中執行計時器函數來對各種 VBA 子程式、函數或巨集的執行進行計時（但不能直接對 Excel 重新計算進行計時）。

我關於優化 Excel 工作簿速度的重要提示：

  1. 比較工作簿資料大小（列、行、表）和檔案大小。過大的檔案可能包含一些隱藏的圖表或繪圖，佔用大量記憶體。您可以將 .xlsx 重新命名為 .zip，然後解壓縮到資料夾中並尋找最大的檔案以給您提示。
  2. 另存為不同的文件類型，例如舊的.xls儲存到新的.xlsx，新的.xlsx儲存到二進位.xlsb。這對於大型資料集的長時間載入和保存時間特別有用。
  3. 檢查CPU和記憶體使用情況。沒有太多 CPU 移動的高記憶體使用率表明它主要只是大量數據，而不是真正的處理密集型。二進位格式 (.xlsb) 加上 RAM（RAM 的兩倍）和磁碟 (SSD) 升級將會有所幫助。請同時檢查自動儲存/備份設置，以減少處理文件時儲存/備份文件的次數。
  4. 如果CPU和記憶體使用情況很高，您很可能必須進入公式才能優化。密集公式計算的另一個跡像是，在編輯一個早期輸入單元格後，速度會突然大幅減慢，而僅在查看期間相對較快（除了初始加載時間）。
  5. 易失性函數（OFFSET、INDIRECT、NOW、RAND 等）是任何大型電子表格的死亡。您可以透過編輯底部的空白、未引用、孤立的儲存格來檢查它們。如果此編輯引發了多次延遲，則可能是不穩定的函數造成的。哦，別忘了條件格式也是不穩定的。
  6. 揮發性觸發器包括導致（擴展）工作表範圍重新計算的操作，例如更改自動過濾器、調整單元格寬度和高度、目標搜尋、隱藏/取消隱藏/插入/刪除/移動/重新命名（單元格和工作表）、刷新與CSV 等的連接檔案或資料庫，更改命名單元格和範圍。如果其中任何一個也在 VBA 巨集中執行，那麼您就明白了。
  7. 重新計算設定可以主要影響性能。如果可能，請關閉自動重新計算並在更新/編輯後手動重新計算。避免迭代計算。
  8. 追蹤依賴者/先例使用「公式」功能區上的選項，或使用 CTRL+[ 和 CTRL+]。後者非常有用，因為您可以多次按 CTRL+[ 並可以確定家屬的家屬的家屬。它們實際上是在您進行操作時選擇的（與功能區上的按鈕添加的箭頭相反），並且可以根據您的喜好進行顏色編碼/標記。
  9. 顯示或找出麻煩的公式。您可以切換「公式」功能區 >顯示公式(CTRL+~)，輕鬆發現麻煩的公式。您也可以做一個工作簿範圍尋找對於易失性函數，例如搜尋 OFFSET。
  10. 不要忘記命名範圍。檢查名稱管理器中包含易失性函數的命名範圍。
  11. 去非揮發性。將 INDIRECT 替換為 INDEX(..MATCH(..)) 並將 OFFSET 替換為相對命名範圍（例如“CellAbove”命名範圍）
  12. 資料記憶體優化。減少使用範圍（您使用的範圍內沒有空白行和列）。避免交叉引用其他工作簿甚至其他工作表。避免將資料儲存為文字。確保它是編號資料。避免使用虛數，因為這些數字實際上儲存為文字。如果您有 Office 365，請使用 LET 以避免一遍又一遍地重新計算公式的相同部分。例如=IF(LOG(A1)>B1,LOG(A1),B1)可以是=LET(x, LOG(A1), IF(x > B1, x, B1))。另外，如果不同的單元格一遍又一遍地執行方程式的相同部分，請在單獨的單元格中計算或定義命名。
  13. 優化您的巨集/VBA 程式碼/javascript。在每個子或函數呼叫開始時關閉重新計算/螢幕更新/事件。運行其餘程式碼並再次打開它們。
  14. 使用區塊單元讀取和寫入。在 VBA/巨集/javascript 程式碼中尤其如此。單一單元的存取速度很慢，尤其是在螢幕更新開啟的情況下。試著將你的答案累積在一個陣列中，並阻止將其寫回儲存格中。與此相關，您可以使用陣列公式（以及 Excel 365 中的溢位）來執行與 Excel 公式中的區塊 r/w 相同的操作。我還沒有驗證這是否真的更快，但我的直覺說是的。
  15. 檢查數據連接。如果您開啟工作簿，某些連線會設定為自動更新，這會大大增加整體載入時間，尤其是在刷新線上/網路資料時。

答案2

我將假設工作簿使用 VBA，並根據此假設發布答案。

大部分成本將用於刷新螢幕和/或將檔案寫入磁碟/網路共用。

為了刷新螢幕，將執行繁重工作的模組包裹在

Application.ScreenUpdating = True
    'Do Something
Application.ScreenUpdating = False

這些設定看起來好像什麼也沒發生，但隱藏的程式碼仍然會執行。您可以添加一些debug.print並監視您的即時視窗以確認您的進度。

您也可以查看 disabling ，並且僅在絕對必要時Application.EnableEvents使用。Application.Calculation = xlAutomatic

關於磁碟成本。我鼓勵您（如果您還沒有）將文件放在本機上，如果可能的話，將來源 MatLab 資料放在本機電腦上。與本機磁碟相比，從網路共用讀取/寫入本質上很慢。

相關內容