Excel 性能

郗peng 2021-06-17

展开全文

2020/08/06

Excel M365 引入了一些新功能，可用于在处理大型或复杂的 Excel 工作簿时提高性能

SUMIFS、AVERAGEIFS、COUNTIFS、MAXIFS、MINIFS 改进

在 Office 365 的 2005 版月度频道及更高版本中，Excel 的 SUMIFS、AVERAGEIFS、COUNTIFS、MAXIFS 和 MINIFS 函数，及其单数对应项 SUMIF、AVERAGEIF 和 COUNTIF 的速度比 Excel 2010 在电子表格中聚合字符串数据的速度快出许多。这些函数现在将在每个表达式中为所搜索的范围创建内部缓存的索引。在从同一范围内提取的任何后续聚合中，将重用该缓存的索引。

速度提升非常显著：例如，同样使用 4 核 2 GHz CPU 从 100 万个单元格中聚合数据，计算 1200 个 SUMIFS、AVERAGEIFS 和 COUNTIFS 公式。过去在 Excel 2010 中需要 20 秒时间，现在在 Excel M365 2006 中只需 8 秒钟。

RealTimeData 函数 (RTD)

在 Excel M365 版本 2002 每月频道或更高版本中，Excel 的 RealTimeData (RTD) 函数要比 Excel 2010 计算电子表格中的数据的速度快得多。我们在其基本内存和数据结构中删除瓶颈，并使其变得线程安全，这能允许其在多线程计算（MTR）的所有可用线程上进行计算。

例如，在总共 500,0000 个单元格中模拟 125,000 个有关诸如“最新价格”、“询价”、“出价”等的 RTD 更新，以计算“交易量”、“市场价值”、“交易损益”等值时，在使用同一硬件的情况下，使用 Excel 2010 需时 47 秒，而使用 Excel M365 2002 版本仅需 7 秒。

使 RTD 函数处于线程安全状态的另一个明显效果是，多线程重新计算（MTR）无需暂停即可运行 RTD 函数。这将能显著地提高运行 RTD 和许多其他计算时的性能。

例如，我们运行含 10,000 RTD 和 10,000 VLOOKUP 函数的工作簿，其中每个 VLOOKUP 都是由 RTD 函数结果所决定的。如果没有线程安全的 RTD，完整的重新计算将用时 10.20 秒，而线程安全的 RTD 则会用时 5.84 秒。

VLOOKUP、HLOOKUP、MATCH 改进

在 Office 365 版本 1809 和更高版本中，在从相同表区域查找多个列（或使用 HLOOKUP 查找行）时，Excel 的 VLOOKUP、HLOOKUP 和 MATCH 对未排序数据进行完全匹配要比以往快得多。

这些查找函数现在将为所搜索的列范围创建内部缓存的索引。在从同一行（VLOOKUP 和 MATCH）或列 (HLOOKUP)中拉取的任何后续查找中，将重用这一缓存的索引。效果引人注目：在同一表范围中的 5 个不同列上进行的查找可能比使用 Excel 2010 或 Excel 2016 进行的相同查找最多快 4 倍，并且查找的列越多，速度提升就越明显。

例如，使用 Excel 2010 计算 100 行这 5 个 VLOOKUP 公式的需要 37 秒的计算时间，而使用 Excel 2016 只需要 12 秒。

excel

    =VLOOKUP($A900000,$A$2:$E$1000000,1,FALSE)
    =VLOOKUP($A900000,$A$2:$E$1000000,2,FALSE)
    =VLOOKUP($A900000,$A$2:$E$1000000,3,FALSE)
    =VLOOKUP($A900000,$A$2:$E$1000000,4,FALSE)
    =VLOOKUP($A900000,$A$2:$E$1000000,5,FALSE)

32 位 Excel 的 LAA 内存改进

尽管 64 位版本的 Excel 有大虚拟内存限制，但 32 位版本只有 2 GB 的虚拟内存。某些客户之所以使用 32 位版本，原因是某些第三方外接程序和控件在 64 位版本中不可用。

32 位版本的 Excel 2013 和 Excel 2016 现在启用了大地址识别 (LAA)。此功能将最大程度地减少内存不足错误消息。

在 64 位版本的 Windows 上，LAA 将可用虚拟内存提高了两倍（从 2 GB 提高到 4 GB），在 32 位版本的 Windows 上将可用虚拟内存从 2 GB 增加到 3 GB。

有关详细信息，请参阅 Excel 的大地址识别功能更改。

若要下载一种可显示可用虚拟内存量和已用虚拟内存量的工具，请参阅 Excel 内存检查工具。

完整列引用

在早期版本的 Excel 中，当打开使用大量完整列引用和多个工作表（例如 =COUNTIF(Sheet2!A:A,Sheet3!A1)）的工作簿或删除行时，这些工作簿可能会占用大量的内存和 CPU。

Excel 2016 内部版本 16.0.8212.1000 减少了这些情况下占用的内存和 CPU。

在对包含 600 万条公式的工作簿进行的示例测试中，对于 Excel 2013 LAA 和 Excel 2010，在虚拟内存为 4 GB 时，使用完整列引用会失败并出现内存不足消息，而对于 Excel 2016，则只会使用 2 GB 的虚拟内存。

结构化引用

在 Excel 2013 及更低版本中，如果工作簿中的公式使用结构化引用来引用表，编辑表的速度会很慢。这就造成了不应将表与大量行一起使用的感觉。 Excel 2016 中不再出现此问题。

例如，在 Excel 2013 和 Excel 2010 中花费 1.9 秒的编辑操作在 Excel 2016 中只需大约 2 毫秒便可完成。

复制、排序和复制/粘贴

我们对在大型工作簿中进行筛选、排序以及复制/粘贴时的响应时间进行了许多改进。

在 Excel 2013 中，在对多个行进行筛选、排序或复制/粘贴操作后，Excel 可能会响应缓慢或挂起。性能取决于顶部可见行和底部可见行之间所有行的计数。我们在内部版本 16.0.8431.2058 中改进了对垂直用户界面位置的内部计算之后，这些操作的速度快了很多。

如果工作簿包含多个经过筛选的行或隐藏的行、合并的单元格或者大纲，打开该工作簿可能会导致高 CPU 负载。我们在内部版本 Build 16.0.8229.1000 中对这一方面进行了修复。

如果某个表包含经过筛选的行（其中的筛选器生成了大量的行区块），在粘贴从该表中复制的一列单元格之后，响应时间非常缓慢。内部版本 16.0.8327.1000 中已改进了这一点。

一项对从 44,000 行中筛选出的 22,000 行进行复制/粘贴的示例测试展现出了巨大的提升：

对于表，时间从 Excel 2013 中的 39 秒和 Excel 2010 中的 18 秒缩短到了 Excel 2016 中的 2 秒。
对于区域，时间从 Excel 2013 中的 30 秒和 Excel 2010 中的 13 秒缩短到了 Excel 2016 中的瞬间完成。

复制条件格式

在 Excel 2013 中，复制/粘贴包含条件格式的单元格可能速度很慢。 Excel 2016 内部版本 16.0.8229.0 中已显著改进了这一点。

一项对包含共 386,000 个条件格式规则的 44,000 个单元格进行复制的示例测试展现出了明显的改进：

Excel 2010：70 秒
Excel 2013：68 秒
Excel 2016：7 秒

添加和删除工作表

当添加和删除大量的工作表时，与 Excel 2013 相比，一项对 Excel 2016 内部版本 16.0.8431.2058 的示例测试在速度上要快 15%–20%，但与 Excel 2010 相比则慢 5-10%。

新增函数

Excel 2016 内部版本 16.0.7920.1000 引入了多个有用工作表函数：

MAXIFS 和 MINIFS 扩展了 COUNTIFS/SUMIFS 系列函数。这些函数具有良好的性能特征。使用它们来替代等效数组公式。
TEXTJOIN 和 CONCAT 使你能轻松地合并单元格区域中的文本字符串。使用它们来替代等效的 VBA UDF。

Excel 2016 for Windows 的其他更新

有关 Excel 2016 的每月改进的更多详细信息，请参阅 Excel 2016 for Windows 新增功能。

Excel 2010 性能改进

基于有关 Excel 2007 的用户反馈，Excel 2010 引入了一些功能改进。

Excel 2010 性能改进
功能	改进
打印机和页面布局视图	为了提高页面布局视图中的基本用户交互（如输入数据、处理公式或设置页边距）的性能，Excel 2010 缓存打印机设置并引入了优化的呈现计算。缓存打印机设置减少了网络调用数并且降低了对运行缓慢或无响应打印机的依赖。此外，连接到打印机是可取消的，以便用户无需等待运行缓慢或无响应的打印机。
图表	从 Excel 2010 开始，图表的呈现速度已提高（特别是对于大型数据集），并且文本呈现性能已改进。此外，Excel 2010 缓存图表图像并在可能的情况下使用缓存的版本，以避免不必要的计算和呈现。
VBA 解决方案	改进对象模型以及对象模型与 Excel 的交互方式后，很多 VBA 解决方案在 Excel 2010 中运行时的性能速度比在 Excel 2007 中运行时快。

大型数据集和 64 位版本的 Excel

64 位版本的 Excel 2010 不像 32 位版本应用程序那样只能使用 2 GB RAM，也不像大地址识别 32 位版本应用程序那样最多只能使用 4 GB RAM。因此，64 位版本的 Excel 2010 使用户能够创建大很多的工作簿。 64 位版本的 Windows 支持更大的可寻址内存容量，Excel 已设计为可利用该容量。例如，与在早期版本的 Excel 中使用数据填充的网格相比，用户能够填充网格的更大部分。随着不断向计算机添加更多 RAM，Excel 会使用该附加内存，允许创建越来越大的工作簿，并且随可用 RAM 量扩展。

此外，由于 64 位版本的 Excel 支持更大的数据集，因此 32 位和 64 位版本的 Excel 2010 都引入了对常见大型数据集任务（如输入和向下填充数据、排序、筛选以及复制和粘贴数据）的改进。在 32 位和 64 位版本的 Excel 中，还对内存使用进行了优化以提高效率。

有关 64 位版本 Office 2010 的详细信息，请参阅 32 位和 64 位版本的 Office 2010 之间的兼容性，若要在 64 位和 32 位之间进行选择，请参阅选择 64 位或 32 位版本的 Office。

形状

Excel 2010 在 Excel 中的图形性能方面引入了重要改进。概括地说，这些改进体现在两个方面：可伸缩性和呈现。

由于工作表中包含大量图形，因此可伸缩性改进对 Excel 应用场景产生了重大影响。通常，大量形状是在从网站复制并粘贴数据时意外创建的，或者因经常运行创建形状但从不删除这些形状的自动化程序而创建的。大量图形以及这些图形与 Excel 中数据网格的关联方式带来一些特殊的性能挑战。 Excel 2010 中的改进提高了包含很多形状的工作表的性能速度。

此外，从 Excel 2010 开始，对硬件加速的支持提高了呈现速度。 Excel 2010 还引入了对 VBA 对象模型中 Shape 对象的 Select 方法的性能改进。

形状
功能	改进
基本应用	Excel 2010 中的第一组改进是围绕基本应用场景进行的。这些应用场景包括操作和功能（如排序、筛选、插入或者调整行或列，或者合并单元格）。执行这些操作时，可能需要更新图形对象在网格中的位置。在最坏的情况下，需要更新工作表中的每个对象。在 Excel 2010 中，这些基本应用场景的性能得到了提高，即使工作表上有数千个对象也是如此。这些改进不是通过单个功能或修补程序实现的，而是通过专门关注性能（包括改进形状查找机制、测试压力文件和调查障碍）实现的。
文本链接	形状上的文本链接是在用户指定用于定义给定形状文本的公式（例如，“=A1”）时创建的。对于包含大量对象的工作表和/或对单元格内容进行更改时，这些特殊形状容易导致性能问题。从 Excel 2010 开始，Excel 跟踪和更新这些形状的方式已改进，以便优化更改单元格内容的性能。这方面的工作改进了应用场景，如在单元格中键入新值或执行复杂的对象模型操作。
大网格	从 Excel 2007 开始，网格的大小从 65,000 行扩展到超过一百万行。网格大小的增加导致处理更大网格的新区域中的图形对象时出现一些性能和呈现问题。从 Excel 2010 开始，Excel 优化了依赖使用网格的左上角作为原点的功能，以改进处理网格新区域中的图形的体验。相对于 Excel 2007，呈现保真度和性能也有所改进。
呈现：硬件加速	从 Excel 2010 开始，通过添加呈现三维对象时对硬件加速的支持改进了图形平台。尽管 GPU 可以比 CPU 更快地呈现这些对象，但在 Excel 2010 中的体验取决于工作表上的内容。如果您的工作表充满三维形状，则与仅包含二维形状（不利用 GPU）的工作表相比，硬件加速改进可以带来更多好处。

计算改进

从 Excel 2007 开始，多线程计算提高了计算性能。

从 Excel 2010 开始，进行了其他性能改进以进一步提高计算速度。 Excel 2010 可以异步调用用户定义函数。异步调用函数通过允许多个计算同时运行来提高性能。在计算机群集中运行用户定义函数时，异步调用函数允许使用多台计算机完成计算。有关详细信息，请参阅异步用户定义函数。

多核处理

Excel 2010 进行了其他投资，以利用多核处理器并提高例程任务的性能。从 Excel 2010 开始，以下功能使用多核处理器：保存文件、打开文件、刷新数据透视表（对于外部数据源，OLAP 和 SharePoint 除外）、对单元格表进行排序、对数据透视表进行排序和自动调整列的大小。

对于涉及读取以及加载或写入数据的操作（如打开文件、保存文件或刷新数据），将操作拆分为两个进程可以提高性能速度。第一个进程获取数据，第二个进程将数据加载到内存中的相应结构或将数据写入文件。这样，第一个进程开始读取一部分数据后，第二个进程可以立即开始加载或写入该数据，同时第一个进程继续读取下一部分数据。以前，第一个进程必须首先完成对某一节中所有数据的读取，然后第二个进程才能将该节数据加载到内存中或将数据写入文件。

PowerPivot

PowerPivot 引用应用程序和服务集合，这些应用程序和服务提供用于在 Excel 工作簿中创建数据驱动的用户管理商业智能解决方案的端到端方法。 PowerPivot for Excel 是一种数据分析工具，它直接在 Excel 中提供无与伦比的计算能力。利用熟悉的 Excel 功能，用户能够以惊人的速度将几乎任何源中的大量数据转换为有意义的信息，以在几秒的时间内得到所需的结果。

PowerPivot 还与 SharePoint 集成。在 SharePoint 场中，PowerPivot for SharePoint 是一组支持以工作组形式协作处理商业智能数据的服务器端应用程序、服务和功能。 SharePoint 提供了用于在小型和大型组织中协作处理和共享商业智能的平台。工作簿作者和所有者发布和管理他们面向 SharePoint 网站开发的商业智能。

有关 PowerPivot 的详细信息，请参阅 PowerPivot 概述。

HPC Services for Excel 2010

凭借大量统计分析函数、对构造复杂分析的支持和广泛的扩展性，Excel 2010 成为分析业务数据的理想工具。随着模型的不断增大以及工作簿复杂性的不断增加，生成的信息的价值也不断增加。然而，工作簿越复杂，需要的计算时间越长。若要进行复杂分析，用户通常需要花费数小时、数天、甚至数周的时间才能完成此类复杂的工作簿。

一种解决方法是使用 Windows HPC Server 2008 将 Excel 计算并行扩展到 Windows 高性能计算 (HPC) 群集中的多个节点。可以通过三种方法在基于 Windows HPC Server 2008 的群集中运行 Excel 2010 计算：在群集中运行 Excel 工作簿，在群集中运行 Excel 用户定义函数 (UDF)，以及使用 Excel 作为群集的面向服务的体系结构 (SOA) 客户端。

有关 HPC Services for Excel 2010 的详细信息，请参阅使用 Windows HPC Server 2008 R2 加快 Excel 2010 的速度。