适用于: Excel | Excel 2013 | Excel 2016 | VBA Office Excel 2016 中包含 100 万行和 16,000 列的“大网格”,加上多项其他上限被调高,与 Excel 先前版本相比极大地增加了你可创建的工作表的大小。 Excel 中单个工作表可包含的单元格数现达到先前版本的 1,000 倍以上。 在 Excel 早期版本中,很多用户创建的工作表计算速度慢,而工作表越大,计算速度通常更慢。 随着“大网格”在 Excel 2007 中的引入,性能变得非常重要。 排序和筛选等运行较慢的计算和数据操作任务使得用户更难专注于手头的任务,而缺乏专注力导致了错误增加。 新的 Excel 版本引入了多项功能,可帮助你应对容量增加的问题,例如能够一次使用多个处理器来进行计算并执行刷新、排序和打开工作簿等常见的数据集操作。 多线程计算可大幅缩短工作表计算时间。 但是,影响 Excel 计算速度的最重要的因素仍然是工作表的设计和创建方式。 你可修改大多数计算速度缓慢的工作表,将其速度提升 10 倍、100 倍,甚至 1000 倍。 通过确定、衡量工作表中的计算阻碍因素,然后加以改进,可加快计算速度。 计算速度的重要性计算速度缓慢会影响工作效率,还会增加用户出错率。 响应时间越长,用户的工作效率越低,专注处理任务的能力越低。 Excel 具有两种计算模式,可让你控制何时执行计算:
如果计算时间不到 1/10 秒,则用户感觉系统在立即响应。 即使在输入数据时,他们也能应用自动计算。 如果计算时间在十分之一秒至一秒之间,则用户可以成功地保持连贯的思路,但他们会注意到响应时间延迟。 随着计算时间的增加(通常介于 1 至 10 秒之间),用户必须在输入数据时切换到手动计算。 此时,用户出错率和厌烦程序开始增加(尤其时在处理重复任务时),而且用户变得很难保证思路连贯。 如果计算时间超过 10 秒,则用户开始不耐烦,而且常会在等待期间切换到其他任务。 如果计算是一系列任务中的一环,而用户不再跟踪进展,则这可能导致出错。 了解 Excel 中的计算方法要提高 Excel 中的计算性能,你必须了解所提供的计算方法及其控制方式。 完整计算和重新计算的依赖项Excel 中的智能重新计算引擎尝试通过持续跟踪每个公式的引用单元格和从属单元格(公式引用的单元格)以及自上次计算起所作的全部更改,来最大程度缩短计算时间。 在下一次重新计算时,Excel 仅重新计算以下内容:
Excel 继续计算依赖于之前已计算的单元格的单元格,即使之前计算的单元格的值在计算时并未更改。 因为大多数情况下在两次计算之间仅更改部分输入数据或少量公式,所以此智能重新计算所花费的时间通常仅占完整计算所有公式所花费时间的一小部分。 在手动计算模式下,可通过按 F9 触发此智能重新计算。 你可通过按 Ctrl+Alt+F9 强制对所有公式执行完整计算,也可通过按 Shift+Ctrl+Alt+F9 强制执行依赖项的完全重建并强制进行完整计算。 计算过程引用其他单元格的 Excel 公式可放在被引用单元格的前面(向前引用),也可放在后面(向后引用)。 这是因为 Excel 不按固定顺序,也不按行或列计算单元格。 相反,Excel 根据要计算的所有公式的列表(计算链)和每个公式的相关依赖项信息动态确定计算顺序。 Excel 具有不同的计算阶段:
第 3 阶段在每次计算或重新计算时执行。 Excel 尝试对计算链中的每个公式依次进行计算,但是如果某公式依赖于尚未计算的一个或多个公式,则该公式将传递到计算链的稍后部分,以便之后重新计算。 这意味着根据重新计算,公式可计算多次。 与第一次计算相比,第二次计算工作簿时通常速度明显加快。 原因有很多:
计算工作簿、工作表和区域你可使用不同的 Excel 计算方法来控制计算内容。 计算所有打开的工作簿每次重新计算和完整计算都会计算当前打开的所有工作簿、解析工作簿与工作表内部及之间的所有依赖项,并将之前未计算的单元格(“脏”单元格)重置为“已计算”。 计算所选工作表你还通过按 Shift+F9 仅重新计算所选的工作表。 这不会解析工作表之间的任何依赖项,也不会将“脏”单元格重置为“已计算”。 计算单元格区域Excel 还能通过 Basic for Applications (VBA) 方法 Range.CalculateRowMajorOrder 和 Range.Calculate 计算单元格区域:
由于 CalculateRowMajorOrder 并未解析当前计算的区域中的任何依赖项,因此其速度通常比 Range.Calculate 快得多。 但是,须谨慎使用此方法,因为它得出的结果可能与 Range.Calculate 的不同。 Range.Calculate 是 Excel 中用于优化性能的最有用的工具之一,因为你可用它来安排和比较不同公式的计算速度。 有关详细信息,请参阅Excel 性能:性能和限制提升。 可变函数每次重新计算时都要重新计算可变函数,即使它似乎并未包含任何已更改的引用单元格也是如此。 使用多个可变函数会降低每次重新计算的速度,但它不影响完整计算。 你可通过在函数代码中包含 Application.Volatile,将用户定义的函数设置为可变函数。 Excel 中的一些内置函数明显是可变函数,例如 RAND()、NOW() 和 TODAY()。 其他一些的可变性质不太明显,例如 OFFSET()****CELL()、INDIRECT() 和 INFO()。 之前记录为可变函数的一些函数实际上并不可变,例如 INDEX()、ROWS()、COLUMNS() 和 AREAS()。 可变操作可变操作是触发重新计算的操作,其中包括:
公式和名称求值环境执行下述任一操作时,立即计算(求值)公式或公式的一部分,即使在手动计算模式下也是如此:
公式在引用(依赖于)满足下述任一条件的单元格或公式时,被标记为“未计算”:
对于标记为“未计算”的公式,在计算或重新计算包含它的工作表、工作簿或 Excel 实例时对它求值。 与单元格中的公式相比,导致计算已定义名称的条件有所不同:
数据表Excel 数据表不得与表功能混淆,前者访问方式为“数据”选项卡>“数据工具”组>“What-If 分析” > “数据表”,而后者的访问方式是“开始”>“样式”组>“套用表格格式”,或者“插入”选项卡>“表格”组>“表格”。 Excel 数据表会对工作簿执行多次重算,每次计算由表格中的不同值驱动。 Excel 首先按通常方式计算工作簿。 对于每个行和列值对,它随后替换这些值、执行单线程重算,并将结果存储到数据表中。 数据表重算始终仅使用一个处理器。 借助数据表,可便捷计算多个变体并查看和对比变体的结果。 使用“自动重算(表格除外)”计算选项停止 Excel 对每个计算触发多次计算,但仍计算除表格以外的所有相关公式。 控制计算选项Excel 具有一系列可用于控制其计算方式的选项。 你可通过功能区上“公式”选项卡中的“计算”组,更改 Excel 中最常使用的选项。 图 1. “公式”选项卡上的“计算”组 要查看更多 Excel 计算选项,请在“文件”选项卡上单击“选项”。 在“Excel 选项”对话框中,单击“公式”选项卡。 图 2. Excel 选项中“公式”选项卡上的计算选项 很多计算选项(“自动”、“除模拟运算表外,自动重算”、“手动”、“保存工作簿前重新计算”)和迭代设置(“启用迭代计算”、“最多迭代次数”、“最大误差”****)在应用程序级别而不是工作簿级别起作用(它们在所有打开的工作簿中是相同的)。 要查找高级计算选项,请在“文件”选项卡上单击“选项”。 在“Excel 选项”对话框中,单击“高级”。 在“公式”部分,设置计算选项。 图 3. 高级计算选项 启动 Excel 时,或者在未打开任何工作簿的情况下运行 Excel 时,通过所打开的第一个非模式、非加载项工作簿设置初始计算模式和迭代设置。 这意味着将忽略之后打开的工作簿中的计算设置,但当然,你可随时在 Excel 中手动更改设置。 保存工作簿时,当前计算设置存储在工作簿中。 自动计算自动计算模式是指在每次更改以及你打开工作簿时,Excel 自动重新计算所有打开的工作簿。 通常当你在自动模式中打开工作簿且 Excel 执行重算时,看不到重算结果,因为自工作簿保存起未更改任何内容。 如果使用上次计算工作簿所用 Excel 版本的更高版本打开工作簿(例如,Excel 2016 与 Excel 2013),则可能注意到此计算结果。 由于 Excel 计算引擎不同,Excel 在打开使用其更低版本保存的工作簿时会执行完整计算。 手动计算手动计算模式是 Excel 指仅在你通过按 F9 或 Ctrl+Alt+F9 请求重算或在保存工作簿时重新计算所有打开的工作簿。 对于重算时间不到 1 秒的工作簿,必须将计算设置为手动模式,从而避免在你进行更改时出现延迟。 Excel 通过在状态栏中显示“计算”,告诉你如何需要重新计算处于手动模式下的工作簿。 如果工作簿包含循环引用且已选中迭代选项,则状态栏也会显示“计算”。 迭代设置如果工作簿中具有故意设置的循环引用,则通过迭代设置可控制工作簿的最大重算(迭代)次数和收敛条件(最大误差:停止时间)。 请清除迭代框,以便在出现意外的循环引用时,Excel 将发出警告且不会尝试解决它们。 工作簿 ForceFullCalculation 属性将此工作簿属性设置为 True 时,Excel 的智能重算将关闭,且每次重算都会重新计算所有打开的工作簿中的所有公式。 对于某些复杂的工作簿,在构建和维护智能重算所需的依赖项树时,所花费的时间比智能重算所保存的时间长。 如果打开工作簿所用的时间太长,或者即使在手动计算模式下执行细微更改也会花费很长时间,则可能需要尝试 ForceFullCalculation。 如果工作簿的 ForceFullCalculation 属性设置为 True,则状态栏中将显示“计算”。 你可控制此设置,方式是使用 VBE (Alt+F11)、在项目资源管理器中选择 ThisWorkbook (Ctrl+R),随即将显示“属性窗口”(F4)。 图 4. 设置 Workbook.ForceFullCalculation 属性 加快工作簿的计算速度采用以下步骤和方法加快工作簿的计算速度。 处理器速度和多个内核对于大多数 Excel 版本,当然是处理器运行速度越快,Excel 计算速度越快。 Excel 2007 中引入了多线程计算引擎,它可让 Excel 充分利用多处理器系统,且可大幅提升大部分工作簿的性能。 在多数大型工作簿中,来自多个处理器的计算性能增益几乎按物理处理器数量呈线性扩缩。 但是,物理处理器的超线程仅带来很少的性能增益。 有关详细信息,请参阅Excel 性能:性能和限制提升。 RAM分页到虚拟内容分页文件时速度较慢。 你必须具有足够的物理 RAM 用于操作系统、Excel 和工作簿。 如果计算期间具有略频繁的硬盘活动,且未运行可触发磁盘活动的用户定义的函数,则需要更多 RAM。 如上所述,Excel 的近期版本可有效利用大量内存,而 32 位版本的 Excel 2007 和 Excel 2010 既可处理单个工作簿,也可处理使用多达 2 GB 内存的工作簿组合。 使用大地址感知 (LAA) 功能的 32 位版本的 Excel 2013 和 Excel 2016 可占用多达 3 GB 或 4 GB 内存,具体取决于所安装的 Windows 版本。 64 位版本的 Excel 可处理更大型的工作簿。 有关详细信息,请参阅 Excel 性能:性能和限制提升中的“大数据集、LAA 和 64 位 Excel”部分。 要实现有效计算,大致准则是具有足够的 RAM 来容纳需要同时打开的最大工作簿集,同时留出 1 至 2 GB 供 Excel 和操作系统使用,再留出一些 RAM 供其他所有正在运行的应用程序使用。 测量计算时间为加快工作簿计算速度,你必须能够正确测量计算时间。 你需要一个比 VBA Time 函数运行速度更快且更准确的计时器。 以下代码示例中显示的 MICROTIMER() 函数对系统高分辨率计时器使用 Windows API 调用。 它可将时间间隔的测量精度调至毫秒小数位。 请注意,由于 Windows 是一个多任务操作系统,并且其第二次计算的速度可能快于第一次,因此所获得的时间通常不完全一致。 为达到最高准确率,请多次测量时间计算任务并计算结果的平均值。 要详细了解 Visual Basic 编辑器可如何大幅影响 VBA 用户定义的函数的性能,请参阅 Excel 性能:性能障碍优化提示中的“提升 VBA 用户定义的函数的运行速度”部分。 VB
要测量计算时间,必须调用相应的计算方法。 下面的子例程为你提供了区域的计算时间、工作表或所有打开的工作簿的重算时间,或者所有打开的工作簿的完整计算时间。 将所有这些子例程和函数如何到标准 VBA 模块中。 要打开 VBA 编辑器,请按 Alt+F11。 在“插入”菜单上,选择“模块”,然后将代码复制到模块中。 VB
要在 Excel 中运行子例程,请按 Alt+F8。 选择所需的子例程,然后单击“运行”。 图 5. 显示计算计时器的 Excel 宏窗口 查找计算障碍并确定优先级大多数计算速度缓慢的工作簿仅具有一些问题区域或障碍,它们占用大部分计算时间。 如果不知道这些区域或障碍位于何处,请使用本部分中所列的深化方法进行查找。 如果知道其位置,则必须测量每个障碍所用的计算时间,以便可确定消除这些障碍所需工作的优先级。 用于查找障碍的深化方法深化方法依次对工作簿的计算、每张工作表的计算和计算缓慢的工作表上的公式块进行计时。 请按顺序执行每一步,并记下计算时间。 使用深化方法查找障碍
加快计算速度并减少障碍占用计算时间的不是公式数量或工作簿大小。 而是单元格引用和计算操作的数量以及正在使用的函数的效率。 因为大多数工作表都是通过复制同时包含绝对引用和相对引用的公式来构造的,所以它们通常包含大量公式,这些公式包含重复的或相同的计算和引用。 避免复杂的宏大公式和数组公式。 通常,最好增加行和列的数量,并减少复杂的计算。 这样,Excel 中的智能重算和多线程计算都能有更好的机会来优化计算。 这还可简化理解并更易调试。 下面是可加快工作簿计算的几项规则。 规则 1:删除相同、重复和不必要的计算查找相同、重复和不必要的计算,并确定 Excel 计算此障碍的结果大概需要多少个单元格引用和计算。 然后,考虑如何使用更少的引用和计算得出相同的结果。 这通常涉及到下述一个或多个步骤:
规则 2:尽可能使用最高效的函数在找到涉及函数或数组公式的障碍时,请确定能否使用更高效的方式获得相同的结果。 例如:
规则 3:充分利用智能重算和多线程计算Excel 中的智能重算和多线程计算使用得越充分,每次 Excel 重算时要执行的处理越少,因此:
规则 4:对每次更改进行计时和测试你所做的一些更改可能令你感到惊讶,这可能是由于未得到你认为应得到的结果,或者计算速度比预期的慢。 因此,应按如下方式对每次更改进行计时和测试:
规则示例以下部分提供有关如何通过规则提升计算速度的示例。 即时更新总和例如,你需要计算一列包含 2,000 个数字的即时更新总和。 假设 A 列包含数字,B 和 C 列包含即时更新总和。 你可使用 SUM(高效函数)编写公式。 VB
图 6. 即时更新总和 SUM 公式示例 将公式向下复制到 B2000。 SUM 总共使用了多少单元格引用? B1 引用一个单元格,B2000 引用 2,000 个单元格。 平均每个单元格是 1,000 个引用,因此总共是 200 万个引用。 如果选择 2,000 个公式并使用 RangeTimer 宏,则显示 B 列 2,000 个公式的计算用时为 80 毫秒。 其中大部分计算都重复多次:B2:B2000 的每个公式都使用 SUM A1:A2。 如果按如下方式编写公式,则可消除此重复。 VB
将此公式向下复制到 C2000。 现在总共使用了多少单元格引用? 每个公式均使用两个单元格引用(第一个公式除外)。 因此,总数为 1999*2+1=3999。 这可减少 500 个单元格引用。 RangeTimer 显示 C 列 2,000 个公式的计算用时为 3.7 毫秒,而 B 列的计算用时为 80 毫秒。这一改变的性能改进系数仅为 80/3.7=22(而非 500),因为每个公式都有少量开销。 错误处理如果您具有执行大量计算的公式,您希望在出现错误时结果显示为零(在完全匹配查找中经常遇到这种情况),则可以通过多种方式编写它。
动态统计唯一值图 7. 统计唯一值的数据列表示例 如果 A 列中包含 11,000 行数据的列表(这些数据经常更改),而你需要动态计算列表中唯一项数目的公式并且忽略空值,则可采用下面几种解决方案。
结论Excel 让你能够高效管理更大型的工作表,与早期版本相比,它还能大幅提升计算速度。 创建大型工作表时,很容易就采用会导致计算缓慢的方式进行构建。 计算缓慢的工作表会增加错误,因为用户发现在执行计算时很难进行维护。 通过使用一组简单的技术,可将大部分计算缓慢的工作表提速 10 倍或 100 倍。 此外,你还可在设计和创建工作表时应用这些技术来确保其快速计算。 另请参阅支持和反馈有关于 Office VBA 或本文档的疑问或反馈? 请参阅 Office VBA 支持和反馈,获取有关如何接收支持和提供反馈的指南。 |
|