Excel运行缓慢的原因与解决办法

丿负二代

场景
   在前期MS Excel四部曲里，首篇文章讲述了绝大多数场景下正确使用MS Excel的逻辑（见Excel的正确使用逻辑）。以这篇文章为总纲，杰哥展开讲述了源数据表的构建规范（见Excel源数据表的构建规范），以及达到这个规范的操作流程（见Excel源数据表的构建流程（上）和Excel源数据表的构建流程（下））。这系列文章基本阐明了使用MS Excel的逻辑与方法学问题，即“三表”逻辑构建为最高层次，每张表（包括源数据表、参数表、统计表）的逻辑构建为中间层次，每张表具体数据的录入技巧，包括行列设计、函数公式技巧、分列筛选、数据透视、图表制作等为最低方法论的层次。
　　本篇文章就来讲讲我们日常在使用MS Excel中最容易出现的问题——运行缓慢。
   我们在Excel的正确使用逻辑中提到过，在工作簿的3张表中源数据表是随时更新的，为了回溯历史通常会保留所有历史数据。因此，随着时间的推移，源数据表的数据逐渐增加，因此它在同一个工作簿3张表中所占的空间是最大的。在每天更新源数据表的过程中，由于使用者的各种不规范操作，会导致源数据表数据混乱，根本原因就是冗余成分多和设置不够优化。冗余成分包括冗余数据、冗余格式，这些成分占用了大量内存空间，导致运行缓慢，下面就展开来讲述。


一、冗余数据
1、结构性冗余数据
这是指由于源数据表设计不合理，导致重复数据大量地存在于源数据表中，如本应该放在参数表中的数据列（哪些数据适合放在参数表有2个判断标准，见Excel的正确使用逻辑）、在统计过程中根本不会用到的机器导出的数据列、数据不全的列等。这类数据的处理方法是删除相应列。
2、指向其他工作表和工作簿的链接
这主要是指公式直接引用其他工作表或工作簿，或使用超链接hyperlink链接到其他本地或网络路径。MS Excel在打开含有这种引用和链接的文件时会检测链接是否可用，这就导致资源消耗增加，影响运行速度。这个问题的处理方法如下：将外部数据放到本工作簿的参数表，然后从本工作簿建立引用；将外部数据作为值直接放到本工作簿源数据表中。假如你需要的外部数据需要即时更新，那更新的过程自然比较耗时，无法避免。
3、过多使用函数
当你的工作表中含有的函数过多，每次打开文件和函数重算都会占用大量系统资源，导致运行缓慢。这个问题的解决办法是对于计算后不会频繁更新的函数单元格要转换为常数单元格。可以使用复制后原位选择性粘贴的办法，或者利用方方格子等插件的“只保留数值（数值化）”功能。见下图：


4、使用的函数占用资源过多
第一，在MS Excel中，函数分为两种类型——易失性函数（可变函数）与非易失性函数（不可变函数）。其中，易失性函数指的是使用这些函数后，会引发工作表的重新计算，有时我们打开一个工作薄但不做任何更改就关闭时，Excel却提醒我们是否要保存，这就是因为文件用到了一些易失性函数。易失函数包括7个：NOW、RAND、TODAY、OFFSET、INDIRECT、CELL、INFO，除了在插删工作表行列后需要重算外，任意单元格内容发生变更和打开工作簿时都会重算；相反，非易失性函数指的是只有改变函数参数单元格的内容时才会引发函数重算。对易失性函数的理解可以是这些函数的值在一定条件下容易失去（失去后会获得新的函数值），这些条件通常是一定的时间流逝或者文件更改，如NOW函数保持其值不变的时间段是1秒，即其易失周期为1秒，TODAY的易失周期为1天，其他易失函数的易失周期由该Excel文件打开的时间与引用单元格内容变更的时间决定（即人为操作的时间间隔成为了它们的易失周期）。由于易失函数的存在，在打开Excel文件或者更改特定单元格内容时，易失函数的计算占用了系统资源，导致运行卡顿。
　　第二，MS Excel中不少函数可以使用数组，数组函数的使用节省了大量公式的输入，但同时增加了计算机本身的资源占用。但是MS Office 365中新增的数组自动扩展除外，因为它的运行机制与非数组函数相似，不会占用太多资源。
　　第三，在MS Excel中，使用者可以自行定义函数，即自定义函数。它可以将自定义的运算过程使用一个函数表达式表示，使用时就像内置函数一样直接使用，如在聊聊字符那些事儿（二）中，杰哥使用了一个自定义函数，用来检查单元格是否选择整行或整列。代码见下：
Function 检查单元格是否选择整行或整列() As Boolean*If Selection.Rows.Count = 1048576 Or Selection.Columns.Count = 16384 Then    检查单元格是否选择整行或整列 = TrueElse    检查单元格是否选择整行或整列 = FalseEnd IfEnd Function
这个自定义函数用于判断用户是否选择整行或者整列单元格。在“*”处如果添加以下代码，则该自定义函数变为易失函数：
Application.Volatile
这个自定义函数如果使用在单元格中，会导致工作表中任意一个单元格发生变化时进行一次重算。更多自定义函数，杰哥将会在后续文章中陆续讲到。
　　第四，函数运行机制不合理。这里指的是函数的自动重算设置。该设置在“公式”选项卡——计算选项，默认设置为自动重算，见下图①~③。当“自动重算”功能打开时，在Excel文档打开时，所有函数会自动重算。选择手动重算时，每次更改值、公式或名称，只计算当前单元格的公式。单击“手动重算”时，Excel 将自动选中“保存前自动重算”复选框。这时需要手动点击旁边的“开始计算”或者“计算工作表”见下图④和⑤，它俩的区别是，“开始计算”将会计算所有打开的工作簿中所有工作表的公式，“计算工作表”将会计算当前激活工作表的所有公式。


第五，函数中的引用单元格范围过大。如在使用Vlookup函数时，许多人为了方便会将其第2参数选择整列，导致大量空白单元格被纳入查找范围，拖慢计算速度。
解决此类问题的方法是减少易失性函数、数组函数、自定义函数的使用、减少引用整行整列，选择非易失性函数、非数组函数或更高级的动态数组函数（MS Office 365版本支持）、添加辅助列代替自定义函数、设置手动重算代替自动重算功能、选择有数据的区域代替选择整行整列。
5、非控件对象过多
　　我们在其他地方复制到MS Excel中的数据很容易把复制的一些“脏东西”粘贴到MS Excel中，比如小图片、图标，这些在MS Excel中均属于“对象”，这些对象并不是像命令按钮那样的控件对象，它们是无用的，会增大文件体积、拖慢运行速度。这个问题的解决办法是利用定位功能，定位到工作表中的非控件对象将其删除，这个操作在网上很容易查到，这里不再赘述，在聊聊字符那些事儿（二）中，杰哥分享了一个模块，其中就包含一键清理工作簿中非控件对象的功能，即下图中红框所示，有需要的读者可以去那篇文章下载。




二、冗余格式
1、数据有效性
数据有效性是对单元格或单元格区域输入的数据从内容到数量上的限制。对于符合条件的数据，允许输入；对于不符合条件的数据，则禁止输入。这样就可以依靠系统检查数据的正确性，避免错误的数据录入。如下图所示：


当工作表中对大量单元格使用了数据有效性验证，尤其是整列时，会导致运行缓慢。此类问题的解决方法为删除数据有效性，或者只对前面一定数量的单元格设置，不要对整列设置。此外，在单元格输入内容时善用Alt加下箭头可以提示已经输入的数据，以此可以代替数据有效性的部分功能，达到输入数据内容与前面一致的效果，见下图：


2、条件格式
条件格式是对符合特殊条件的单元格实现特殊的格式，如最常用的显示重复项。如下图所示：


在应用条件格式时，最容易出现的问题是对整列应用。当工作表中存在大量条件格式时，其运行速度会显著变慢。因此，这类问题的处理方法与数据有效性的处理方法类似，即短暂使用条件格式进行筛选查看后，将条件格式删除，或者只对有限数量的单元格使用条件格式，避免整列使用。
3、单元格样式：文字字体、字号，背景色，单元格框线
我们在使用MS Excel的时候，不少读者喜欢将表格做成红红绿绿的样子，对表格应用字体、字号、文字颜色、单元格背景颜色、边框线颜色、边框线粗细进行修改。这些格式的信息在MS Excel后台都需要占用资源进行储存，当一个工作簿中的格式信息过多，就会导致运行速度变慢。因此，这类问题的解决方法同时也是杰哥提倡的，在源数据表中尽量少对单元格格式进行修改，而应该使用辅助列，在统计表中为了醒目可以适量使用。杰哥在Excel源数据表的构建流程（下）中分享了一个删除单元格样式的模块，即下图②，大家可以去文章中下载。


​
​4、设置不够优化
设置不够优化的问题包括以下几项：
A、加载项过多：MS Excel的打开过程中，通常需要加载加载项。当你的Excel加载项过多时，每个文件在打开时所花费的时间会明显增多，如下图有5项加载项需要加载：


这个问题的解决办法就是删除不常用的加载项。我们进入Excel选项中的加载项设置，可以发现MS Excel通常有5类加载项（见下图）：Excel加载项、COM加载项、操作、XML扩展包和禁用项目（图中绿框），所有5类的加载项会按照是否处于活动状态列于上方的表中（图中红框），每种加载项属于什么类型会有相应说明（图中蓝框）。我们需要做的就是在绿框中选择需要关闭的活动加载项，点击“转到”在弹出的窗口中关闭该加载项（图中绿框）。


B、自建样式过多：在MS Excel中，每当你设置并保存过单元格的字体、字号、字体颜色、背景颜色、边框等单元格样式，就会在单元格样式窗口中出现在“自定义”区域，见下图红框，而绿框中表示内置样式：


实际上，我们经常会发现在别人传给你或者网上下载的MS Excel文件中存在过多的自定义样式，如下图：


过多的自定义样式会严重拖慢MS Excel的运行速度。同时，样式所携带的信息不容易被其他阅读者搞清楚，不如说明列或者注释列简洁明了。因此，杰哥鼓励在任何源数据表中都少用样式。删除自定义样式成为了这个问题的解决办法。将光标放在要删除的自定义样式上点击右键可以弹出“删除”选项，但是如果要批量删除所有自定义样式就不够方便。杰哥在Excel源数据表的构建流程（下）中分享过一个样式修改模块，其中的①就是用于删除自定义样式，见下图。


C、MS Office为32位版本：微软MS Office分为32位版本和64位版本。理论上讲，64位版本的软件比32位相同软件速度提升1倍左右。当你安装的是32位MS Office，若出现内存不足的情况时，软件也会推荐使用64位版本，见下图。因此，在当今64位操作系统普及的今天，建议使用64位MS Office。安装方法在Excel源数据表的构建规范提到过。




总结

本文分析了MS Excel运行缓慢的常见原因与解决方法，但无法包含所有会导致MS Excel运行慢的原因。希望大家在使用MS Excel的过程中养成良好的使用习惯，充分发挥MS Excel为数据服的根本功能，适当减少华而不实的其他用处。如果以上措施仍然无法提升某些MS Excel文件的运行速度，那么还有一项终极绝招——壮士断腕，即将xlsx格式降级另存为xls格式，打开xls后再另存为高级版本的xlsx格式。
今天的内容就这么多，希望能为大家带来一些启发。感谢大家关注！