基础知识 · 2022年5月30日

如何清理 Excel 工作簿以减少内存占用

  • 适用于:Excel 2016、Excel 2013

症状

升级到 Microsoft Office 2013/2016/Office 365 后,您会遇到以下一种或多种症状:

  • 当您打开多个 Microsoft Excel 2013 工作簿、保存 Excel 工作簿或在 Excel 工作簿中进行计算时,计算机会使用更多内存。
  • 在升级到 Excel 2013/2016 之前,您不能再在同一实例中打开尽可能多的 Excel 工作簿。
  • 在 Excel 工作簿中插入列时,您会收到有关可用内存的错误。
  • 使用 Excel 电子表格时,您会收到以下错误消息:There isn't enough memory to complete this action. Try using less data or closing other applications. To increase memory availability, consider: - Using a 64-bit version of Microsoft Excel. - Adding memory to your device.
使用 Excel 工作簿时发生的有关没有足够内存来完成此操作的错误的详细信息。

原因

从 Excel 2013 开始,进行了一些改进,需要比早期版本更多的系统资源。本文确定了 Excel 工作簿中使用大量内存的区域,并介绍了如何使工作簿文件更有效地工作。

有关我们在 Excel 2013 中所做更改的详细信息,请参阅 Excel 2013的 32 位版本中的内存使用情况

解析度

若要解决此问题,请按照它们出现的顺序使用以下方法。如果其中一种方法没有帮助,请继续使用下一种方法。

 笔记

许多 Excel 工作簿有几个问题可能会出现问题。消除这些问题后,您的工作簿将运行得更加顺畅。

格式化注意事项

格式化可能会导致 Excel 工作簿变得如此之大,以至于它们无法正常工作。由于格式问题,Excel 经常挂起或崩溃。

方法一:消除过多的格式化

Excel 工作簿中过多的格式可能会导致文件增长并可能导致性能下降。如果您使用颜色或边框格式化整列或整行,则格式化将被视为过度。当格式化需要从网页或数据库复制或导入数据时,也会出现此问题。要消除多余的格式,请使用清理工作表上多余的单元格格式中提供的格式清理插件。

如果您在消除多余的格式后仍然遇到问题,请继续使用方法 2。

方法 2:删除未使用的样式

您可以使用样式来标准化您在整个工作簿中使用的格式。当单元格从一个工作簿复制到另一个工作簿时,它们的样式也会被复制。当您保存回旧文件版本时,这些样式会继续使文件增长,并最终可能导致 Excel 中出现“太多不同的单元格格式”错误消息。

许多实用程序都可以删除未使用的样式。只要您使用的是基于 XML 的 Excel 工作簿(即 .xlsx 文件或 .xlsm 文件),就可以使用样式清理工具。您可以在此处找到此工具。

如果您在删除任何未使用的样式后仍然遇到问题,请转到方法 3。

方法 3:删除形状

在电子表格中添加大量形状也需要大量内存。形状被定义为位于 Excel 网格上的任何对象。一些例子如下:

  • 图表
  • 绘制形状
  • 注释
  • 剪贴画
  • 智能艺术
  • 图片
  • 文字艺术

通常,这些对象是从网页或其他工作表中复制而来的,并且被隐藏或相互重叠。用户经常不知道他们的存在。

要检查形状,请按照下列步骤操作:

  1. 在主页功能区上,单击查找并选择,然后单击选择窗格
  2. 单击此工作表上的形状。形状显示在列表中。
  3. 删除任何不需要的形状。(眼睛图标指示形状是否可见。)
  4. 对每个工作表重复步骤 1 到 3。

如果您在删除形状后仍然遇到问题,则应检查与格式无关的注意事项。

方法 4:删除条件格式

条件格式会导致文件增长。当文件中的条件格式损坏时会发生这种情况。您可以删除条件格式,以测试问题是否与格式损坏有关。要删除条件格式,请按照下列步骤操作:

  1. 保存文件的备份。
  2. 在主页功能区上,单击条件格式
  3. 从整个工作表中清除规则。
  4. 对工作簿中的每个工作表执行步骤 2 和 3。
  5. 使用不同的名称保存工作簿。
  6. 看看问题是否解决。

如果删除条件格式可以解决问题,您可以打开原始工作簿,删除条件格式,然后重新应用它。

问题依然存在?

如果这些方法都不起作用,您可以考虑迁移到 64 位版本的 Excel,将您的问题工作簿分成不同的工作簿,或者联系支持以获取更多故障排除。

计算注意事项

除了格式化之外,计算还可能导致 Excel 崩溃和挂起。

方法一:在最新版 Excel 中打开工作簿

如果工作簿包含大量计算,则在新版本的 Excel 中首次打开 Excel 工作簿可能需要很长时间。首次打开工作簿时,Excel 必须重新计算工作簿并验证工作簿中的值。有关详细信息,请参阅以下文章:

如果在 Excel 完全重新计算文件并保存文件后文件继续缓慢打开,请继续执行方法 2。

方法 2:公式

浏览您的工作簿并检查您正在使用的公式类型。有些公式会占用大量内存。其中包括以下数组公式:

  • 抬头
  • 间接
  • 偏移量
  • 指数
  • 匹配

使用它们很好。但是,请注意您引用的范围。

引用整列的公式可能会导致 .xlsx 文件的性能不佳。网格大小从 65,536 行增加到 1,048,576 行,从 256 (IV) 列增加到 16,384 (XFD) 列。创建公式的一种流行方法是引用整列,尽管不是最佳实践。如果您在旧版本中仅引用一列,则仅包含 65,536 个单元格。在新版本中,您引用了超过 100 万列。

假设您有以下 VLOOKUP:

擅长复制

=VLOOKUP(A1,$D:$M,2,FALSE) 

在 Excel 2003 和更早版本中,此 VLOOKUP 引用了仅包含 655,560 个单元格(10 列 x 65,536 行)的整行。但是,对于新的更大的网格,相同的公式引用了近 1050 万个单元格(10 列 x 1,048,576 行 = 10,485,760)。

这已在 Office 2016/365 版本 1708 16.0.8431.2079 及更高版本中修复。有关如何更新 Office 的信息,请参阅安装 Office 更新

对于早期版本的 Office,您可能需要重新构建公式以仅引用公式所需的那些单元格。

 笔记

检查您定义的名称以确保您没有引用整个列或行的其他公式。

 笔记

如果您使用整行,也会出现这种情况。

如果您在更改公式以仅引用正在使用的单元格后仍然遇到问题,请继续使用方法 3。

方法 3:跨工作簿计算

限制跨工作簿进行计算的公式。这很重要,原因有两个:

  • 您正在尝试通过网络打开文件。
  • Excel 正在尝试计算大量数据。

不要跨网络进行计算,而是将公式包含在一个工作簿中,然后创建一个从一个工作簿到另一个工作簿的简单链接。

如果在将公式更改为仅引用单元格而不是跨工作簿计算后仍然遇到此问题,请继续使用方法 4。

方法 4:易失性函数

限制在工作簿中使用 volatile 函数。您不必拥有数百个使用 TODAY 或 NOW 函数的单元格。如果您必须在电子表格中包含当前日期和时间,请使用该函数一次,然后通过定义的链接名称引用该函数。

如果您在限制易失性公式后仍然遇到此问题,请继续使用方法 5。

方法 5:数组公式

数组公式很强大。但必须正确使用它们。重要的是不要向您的阵列添加比您必须拥有的更多的单元格。当数组中的单元格具有需要计算的公式时,将对该公式中引用的所有单元格进行计算。

有关数组如何工作的详细信息,请参阅Excel 2010 性能:优化性能障碍的提示

如果您在更新数组公式后仍然遇到此问题,请继续使用方法 6。

方法 6:定义名称

定义的名称用于在整个工作簿中引用单元格和公式,以向公式添加“友好名称”。您应该检查链接到其他工作簿或临时 Internet 文件的任何已定义名称。通常,这些链接是不必要的,并且会减慢 Excel 工作簿的打开速度。

您可以使用名称管理器工具查看在 Excel 界面中看不到的隐藏的已定义名称。此工具使您能够查看和删除不需要的已定义名称。

如果删除任何不必要的已定义名称后 Excel 继续崩溃和挂起,请转到方法 7。

Excel 的强大之处在于它能够从其他电子表格中引入实时数据。清点文件及其链接的外部文件。Excel 对可以链接的 Excel 工作簿的数量没有限制,尽管您可能会遇到几个问题。测试没有链接的文件,以确定问题出在此文件中还是在链接文件之一中。

继续

这些是导致 Excel 挂起和崩溃的最常见问题。如果您仍然在 Excel 中遇到崩溃和挂起的问题,您应该考虑向 Microsoft 开具支持票。

更多信息

如果这些方法都不起作用,您应该考虑迁移到 64 位版本的 Excel 或将您的问题工作簿分成不同的工作簿。

如何解决 Excel 中的“可用资源”错误

Excel:如何解决 Excel 工作簿中的崩溃和“无响应”问题