☰

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. VOLATILE 函数（OFFSET、INDIRECT、NOW、RAND 等）是任何大型电子表格的死穴。您可以通过编辑底部空白、未引用、孤立的单元格来检查它们。如果此编辑引发多次延迟，则易变函数可能是罪魁祸首。哦，别忘了条件格式也是易挥发的。
  6. 挥发性触发器包括导致（电子）表格范围重新计算的操作，例如更改自动过滤器、调整单元格宽度和高度、目标搜索、隐藏/取消隐藏/插入/删除/移动/重命名（单元格和表格）、刷新与 CSV 文件或 DB 的连接、更改命名单元格和范围。如果其中任何一个也在 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并监视您的即时窗口以确认您正在做什么。

您还可以考虑禁用，并仅在绝对必要时Application.EnableEvents使用。Application.Calculation = xlAutomatic

关于磁盘成本。我鼓励您（如果您还没有这样做的话）将文件放在本地机器上，如果可能的话，将源 MatLab 数据放在本地机器上。与本地磁盘相比，从网络共享读取/写入的速度本来就很慢。

相关内容