Excel 性能：提升计算性能

  • 项目

适用于： 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 具有两种计算模式，可让你控制何时执行计算：

  • 自动计算 - 进行更改时自动重新计算公式。

  • 手动计算 - 仅在你要求时（例如通过按 F9）才重新计算公式。

如果计算时间不到 1/10 秒，则用户感觉系统在立即响应。 即使在输入数据时，他们也能应用自动计算。

如果计算时间在十分之一秒至一秒之间，则用户可以成功地保持连贯的思路，但他们会注意到响应时间延迟。

随着计算时间的增加（通常介于 1 至 10 秒之间），用户必须在输入数据时切换到手动计算。 此时，用户出错率和厌烦程序开始增加（尤其时在处理重复任务时），而且用户变得很难保证思路连贯。

如果计算时间超过 10 秒，则用户开始不耐烦，而且常会在等待期间切换到其他任务。 如果计算是一系列任务中的一环，而用户不再跟踪进展，则这可能导致出错。

了解 Excel 中的计算方法

要提高 Excel 中的计算性能，你必须了解所提供的计算方法及其控制方式。

完整计算和重新计算的依赖项

Excel 中的智能重新计算引擎尝试通过持续跟踪每个公式的引用单元格和从属单元格（公式引用的单元格）以及自上次计算起所作的全部更改，来最大程度缩短计算时间。 在下一次重新计算时，Excel 仅重新计算以下内容：

  • 已更改或标记为需要重新计算的单元格、公式、值或名称。

  • 依赖于需要重新计算的其他单元格、公式、名称或值的单元格。

  • 可变函数和可见条件格式。

Excel 继续计算依赖于之前已计算的单元格的单元格，即使之前计算的单元格的值在计算时并未更改。

因为大多数情况下在两次计算之间仅更改部分输入数据或少量公式，所以此智能重新计算所花费的时间通常仅占完整计算所有公式所花费时间的一小部分。

在手动计算模式下，可通过按 F9 触发此智能重新计算。 你可通过按 Ctrl+Alt+F9 强制对所有公式执行完整计算，也可通过按 Shift+Ctrl+Alt+F9 强制执行依赖项的完全重建并强制进行完整计算。

计算过程

引用其他单元格的 Excel 公式可放在被引用单元格的前面（向前引用），也可放在后面（向后引用）。 这是因为 Excel 不按固定顺序，也不按行或列计算单元格。 相反，Excel 根据要计算的所有公式的列表（计算链）和每个公式的相关依赖项信息动态确定计算顺序。

Excel 具有不同的计算阶段：

  1. 创建初始计算链并确定计算的起始位置。 在将工作簿加载到内存中时发生此阶段。

  2. 跟踪依赖项、将单元格标记为“未计算”，并更新计算链。 对每个单元格条目或在每次更改时执行此阶段，即使是在手动计算模式下也是如此。 执行速度通常快到让你察觉不到，但如果情景复杂，则响应可能很慢。

  3. 计算所有公式。 在计算过程中，Excel 会重新排列和重新构造计算链，从而优化后续的重新计算。

  4. 更新 Excel 窗口的可见部分。

第 3 阶段在每次计算或重新计算时执行。 Excel 尝试对计算链中的每个公式依次进行计算，但是如果某公式依赖于尚未计算的一个或多个公式，则该公式将传递到计算链的稍后部分，以便之后重新计算。 这意味着根据重新计算，公式可计算多次。

与第一次计算相比，第二次计算工作簿时通常速度明显加快。 原因有很多：

  • Excel 通常仅重新计算已更改的单元格及其依赖项。

  • Excel 会存储并重复使用最新的计算顺序，从而能节省确定计算顺序时所用的大部分时间。

  • 在多核计算机中，Excel 会尝试根据上一次计算的结果优化将计算分摊到多核的方式。

  • 在 Excel 会话中，Windows 和 Excel 会缓存最近使用的数据和程序，从而加快访问速度。

计算工作簿、工作表和区域

你可使用不同的 Excel 计算方法来控制计算内容。

计算所有打开的工作簿

每次重新计算和完整计算都会计算当前打开的所有工作簿、解析工作簿与工作表内部及之间的所有依赖项，并将之前未计算的单元格（“脏”单元格）重置为“已计算”。

计算所选工作表

还可以使用 Shift+F9 仅重新计算所选工作表。 这将解析工作表的依赖项，并重置所有以前未计算的单元格 (脏) 计算结果。

在早期版本的 Excel 中，行为不同，脏单元格未设置为计算完成后的计算结果。 如果用户定义的函数依赖于此行为，则应改为将这些函数设置为可变函数，如本文的 Volatile 函数 部分所述。

计算单元格区域

Excel 还能通过 Basic for Applications (VBA) 方法 Range.CalculateRowMajorOrder 和 Range.Calculate 计算单元格区域：

  • 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 计算，则打开工作簿通常会导致完整计算。
  • 如果选择“保存前计算”选项，则在手动模式下保存工作簿。

公式和名称求值环境

执行下述任一操作时，立即计算（求值）公式或公式的一部分，即使在手动计算模式下也是如此：

  • 输入或编辑公式。
  • 通过函数向导输入或编辑公式。
  • 在函数向导中输入公式作为参数。
  • 在编辑栏中选择公式并按 F9（按 Esc 可撤消并还原到公式），或单击“公式求值”。

公式在引用（依赖于）满足下述任一条件的单元格或公式时，被标记为“未计算”：

  • 它已输入。
  • 公式已更改。
  • 它在自动筛选列表中，并且启用了条件下拉列表。
  • 它将标记为未计算。

对于标记为“未计算”的公式，在计算或重新计算包含它的工作表、工作簿或 Excel 实例时对它求值。

与单元格中的公式相比，导致计算已定义名称的条件有所不同：

  • 每次对引用已定义名称的公式进行求值时都对已定义名称进行求值，因此在多个公式中使用一个名称可能会导致多次对该名称进行求值。
  • 不计算未被任何公式引用的名称，即使是完整计算也是如此。

数据表

Excel 数据表 (数据选项卡 >Data Tools 组 >What-If Analysis>数据表) 不应与表功能混淆 (“开始”选项卡“样式>”组>格式为“表格”或“插入选项卡表>”组>表) 。 Excel 数据表会对工作簿执行多次重算，每次计算由表格中的不同值驱动。 Excel 首先按通常方式计算工作簿。 对于每个行和列值对，它随后替换这些值、执行单线程重算，并将结果存储到数据表中。

数据表重算始终仅使用一个处理器。

借助数据表，可便捷计算多个变体并查看和对比变体的结果。 使用“自动重算(表格除外)”计算选项停止 Excel 对每个计算触发多次计算，但仍计算除表格以外的所有相关公式。

控制计算选项

Excel 具有一系列可用于控制其计算方式的选项。 你可通过功能区上“公式”选项卡中的“计算”组，更改 Excel 中最常使用的选项。

图 1. “公式”选项卡上的“计算”组

“公式”选项卡上的计算选项

要查看更多 Excel 计算选项，请在“文件”选项卡上单击“选项”。 在“Excel 选项”对话框中，单击“公式”选项卡。

图 2. Excel 选项中“公式”选项卡上的计算选项

Backstage 视图中的计算选项

很多计算选项（“自动”、“除模拟运算表外，自动重算”、“手动”、“保存工作簿前重新计算”）和迭代设置（“启用迭代计算”、“最多迭代次数”、“最大误差”）在应用程序级别而不是工作簿级别起作用（它们在所有打开的工作簿中是相同的）。

要查找高级计算选项，请在“文件”选项卡上单击“选项”。 在“Excel 选项”对话框中，单击“高级”。 在“公式”部分，设置计算选项。

图 3. 高级计算选项

Backstage 视图中的高级计算选项

启动 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 属性

设置 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 或 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 用户定义的函数的运行速度”部分。

#If VBA7 Then
    Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias _
        "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias _
        "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
    Private Declare Function getFrequency Lib "kernel32" Alias _
        "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare Function getTickCount Lib "kernel32" Alias _
        "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If
Function MicroTimer() As Double
'

' Returns seconds.
    Dim cyTicks1 As Currency
    Static cyFrequency As Currency
    '
    MicroTimer = 0

' Get frequency.
    If cyFrequency = 0 Then getFrequency cyFrequency

' Get ticks.
    getTickCount cyTicks1                            

' Seconds
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency 
End Function

要测量计算时间，必须调用相应的计算方法。 下面的子例程为你提供了区域的计算时间、工作表或所有打开的工作簿的重算时间，或者所有打开的工作簿的完整计算时间。

将所有这些子例程和函数如何到标准 VBA 模块中。 要打开 VBA 编辑器，请按 Alt+F11。 在“插入”菜单上，选择“模块”，然后将代码复制到模块中。


Sub RangeTimer()
    DoCalcTimer 1
End Sub
Sub SheetTimer()
    DoCalcTimer 2
End Sub
Sub RecalcTimer()
    DoCalcTimer 3
End Sub
Sub FullcalcTimer()
    DoCalcTimer 4
End Sub

Sub DoCalcTimer(jMethod As Long)
    Dim dTime As Double
    Dim dOvhd As Double
    Dim oRng As Range
    Dim oCell As Range
    Dim oArrRange As Range
    Dim sCalcType As String
    Dim lCalcSave As Long
    Dim bIterSave As Boolean
    '
    On Error GoTo Errhandl

' Initialize
    dTime = MicroTimer              

    ' Save calculation settings.
    lCalcSave = Application.Calculation
    bIterSave = Application.Iteration
    If Application.Calculation <> xlCalculationManual Then
        Application.Calculation = xlCalculationManual
    End If
    Select Case jMethod
    Case 1

        ' Switch off iteration.

        If Application.Iteration <> False Then
            Application.Iteration = False
        End if
        
        ' Max is used range.

        If Selection.Count > 1000 Then
            Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
        Else
            Set oRng = Selection
        End If

        ' Include array cells outside selection.

        For Each oCell In oRng
            If oCell.HasArray Then
                If oArrRange Is Nothing Then 
                    Set oArrRange = oCell.CurrentArray
                End If
                If Intersect(oCell, oArrRange) Is Nothing Then
                    Set oArrRange = oCell.CurrentArray
                    Set oRng = Union(oRng, oArrRange)
                End If
            End If
        Next oCell

        sCalcType = "Calculate " & CStr(oRng.Count) & _
            " Cell(s) in Selected Range: "
    Case 2
        sCalcType = "Recalculate Sheet " & ActiveSheet.Name & ": "
    Case 3
        sCalcType = "Recalculate open workbooks: "
    Case 4
        sCalcType = "Full Calculate open workbooks: "
    End Select

' Get start time.
    dTime = MicroTimer
    Select Case jMethod
    Case 1
        If Val(Application.Version) >= 12 Then
            oRng.CalculateRowMajorOrder
        Else
            oRng.Calculate
        End If
    Case 2
        ActiveSheet.Calculate
    Case 3
        Application.Calculate
    Case 4
        Application.CalculateFull
    End Select

' Calculate duration.
    dTime = MicroTimer - dTime
    On Error GoTo 0

    dTime = Round(dTime, 5)
    MsgBox sCalcType & " " & CStr(dTime) & " Seconds", _
        vbOKOnly + vbInformation, "CalcTimer"

Finish:

    ' Restore calculation settings.
    If Application.Calculation <> lCalcSave Then
         Application.Calculation = lCalcSave
    End If
    If Application.Iteration <> bIterSave Then
         Application.Iteration = bIterSave
    End If
    Exit Sub
Errhandl:
    On Error GoTo 0
    MsgBox "Unable to Calculate " & sCalcType, _
        vbOKOnly + vbCritical, "CalcTimer"
    GoTo Finish
End Sub

要在 Excel 中运行子例程，请按 Alt+F8。 选择所需的子例程，然后单击“运行”。

图 5. 显示计算计时器的 Excel“宏”窗口

Excel 宏窗口

查找计算障碍并确定优先级

大多数计算速度缓慢的工作簿仅具有一些问题区域或障碍，它们占用大部分计算时间。 如果不知道这些区域或障碍位于何处，请使用本部分中所列的深化方法进行查找。 如果知道其位置，则必须测量每个障碍所用的计算时间，以便可确定消除这些障碍所需工作的优先级。

用于查找障碍的深化方法

深化方法依次对工作簿的计算、每张工作表的计算和计算缓慢的工作表上的公式块进行计时。 请按顺序执行每一步，并记下计算时间。

使用深化方法查找障碍

  1. 确保您仅打开了一个工作簿并且没有运行其他任务。

  2. 将计算设置为手动。

  3. 创建工作簿的备份副本。

  4. 打开包含计算计时器宏的工作簿，或者将其添加到工作簿中。

  5. 依次在每张工作表上按 Ctrl+End 检查已用区域。

    这将显示上次使用的单元格位于何处。 如果该单元格超出你期望其所在的位置，请考虑删除多余的列和行，再保存工作簿。 有关详细信息，请参阅 Excel 性能：性能障碍优化提示中的“尽量缩小所用区域”部分。

  6. 运行 FullCalcTimer 宏。

    在工作簿中计算所有公式所用的时间通常是最坏情况下的所用时间。

  7. 运行 RecalcTimer 宏。

    完整计算后立即重算通常得出最佳情况下的所用时间。

  8. 以重新计算时间和完整计算时间比的形式计算工作簿可变性。

    这用于测量可变公式和计算链求值在多大程度上成为障碍。

  9. 激活每张表，再依次运行 SheetTimer 宏。

    由于仅重新计算工作簿，因此这得出每张工作表的重算时间。 这应会让你能够确定哪些工作表存在问题。

  10. 在所选的公式块上运行 RangeTimer 宏。

  11. 对于每个存在问题的工作表，请将行或列划分为少量的块。

  12. 依次选择每个块，然后在块上运行 RangeTimer 宏。

  13. 必要时，请将每个块再细分为少量的块进行进一步的向下钻取。

  14. 确定障碍的优先级。

加快计算速度并减少障碍

占用计算时间的不是公式数量或工作簿大小。 而是单元格引用和计算操作的数量，以及所使用函数的效率。

因为大多数工作表都是通过复制同时包含绝对引用和相对引用的公式来构造的，所以它们通常包含大量公式，这些公式包含重复的或相同的计算和引用。

避免复杂的宏大公式和数组公式。 通常，最好增加行和列的数量，并减少复杂的计算。 这样，Excel 中的智能重算和多线程计算都能有更好的机会来优化计算。 这还可简化理解并更易调试。 下面是可加快工作簿计算的几项规则。

规则 1：删除相同、重复和不必要的计算

查找相同、重复和不必要的计算，并确定 Excel 计算此障碍的结果大概需要多少个单元格引用和计算。 然后，考虑如何使用更少的引用和计算得出相同的结果。

这通常涉及到下述一个或多个步骤：

  • 减少每个公式中的引用数。

  • 将重复的计算移动到一个或更多帮助器单元格中，然后从原始公式引用帮助器单元格。

  • 再使用一些行和列来计算和存储一次中间结果，以便可在其他公式中重复使用它们。

规则 2：尽可能使用最高效的函数

在找到涉及函数或数组公式的障碍时，请确定能否使用更高效的方式获得相同的结果。 例如：

  • 与在未排序的数据上进行查找相比，在已排序的数据上查找可能快数十倍或数百倍。

  • VBA 用户定义的函数通常比 Excel 中的内置函数慢（但精细编写的 VBA 函数可能很快）。

  • 尽量减少 SUM 和 SUMIF 等函数中所用单元格的数量。 计算时间与所用单元格的数量成正比（忽略未使用的单元格）。

  • 请考虑将运行较慢的数组公式替换为用户定义的函数。

规则 3：充分利用智能重算和多线程计算

Excel 中的智能重算和多线程计算使用得越充分，每次 Excel 重算时要执行的处理越少，因此：

  • 如果可以，请避免使用 INDIRECT 和 OFFSET 等可变函数，除非它们的效率明显优于替代项。 （经过精心设计后使用 OFFSET 通常速度很快。）

  • 尽量减少数组公式和函数中正在使用的区域的大小。

  • 将数组公式和宏大公式拆分到单独的帮助程序列和行。

  • 避免单线程函数：

    • PHONETIC
    • CELL（使用“格式”或“地址”参数时）
    • INDIRECT
    • GETPIVOTDATA
    • CUBEMEMBER
    • CUBEVALUE
    • CUBEMEMBERPROPERTY
    • CUBESET
    • CUBERANKEDMEMBER
    • CUBEKPIMEMBER
    • CUBESETCOUNT
    • ADDRESS，其中给定第 5 个参数 (sheet_name)
    • 引用数据透视表的任何数据库函数（DSUM、DAVERAGE 等）
    • ERROR.TYPE
    • HYPERLINK
    • VBA 和 COM 加载项用户定义的函数

  • 避免迭代使用数据表和循环引用：这两者均执行单线程计算。

规则 4：对每次更改进行计时和测试

你所做的一些更改可能令你感到惊讶，这可能是由于未得到你认为应得到的结果，或者计算速度比预期的慢。 因此，应按如下方式对每次更改进行计时和测试：

  1. 使用 RangeTimer 宏对要更改的公式进行计时。

  2. 进行更改。

  3. 通过使用 RangeTimer 宏对已更改的公式进行计时。

  4. 检查已更改的公式是否仍得出正确答案。

规则示例

以下部分提供有关如何通过规则提升计算速度的示例。

即时更新总和

例如，你需要计算一列包含 2,000 个数字的即时更新总和。 假设 A 列包含数字，B 和 C 列包含即时更新总和。

你可使用 SUM（高效函数）编写公式。

  B1=SUM($A$1:$A1)
  B2=SUM($A$1:$A2)

图 6. 期间至本日 SUM 公式的示例

即时更新总和 SUM 公式示例

将公式向下复制到 B2000。

SUM 总共使用了多少单元格引用？ B1 引用一个单元格，B2000 引用 2,000 个单元格。 平均每个单元格是 1,000 个引用，因此总共是 200 万个引用。 如果选择 2,000 个公式并使用 RangeTimer 宏，则显示 B 列 2,000 个公式的计算用时为 80 毫秒。 其中大部分计算都重复多次：B2:B2000 的每个公式都使用 SUM A1:A2。

如果按如下方式编写公式，则可消除此重复。

  C1=A1
  C2=C1+A1

将此公式向下复制到 C2000。

现在总共使用了多少单元格引用？ 每个公式均使用两个单元格引用（第一个公式除外）。 因此，总数为 1999*2+1=3999。 这可减少 500 个单元格引用。

RangeTimer 显示 C 列 2,000 个公式的计算用时为 3.7 毫秒，而 B 列的计算用时为 80 毫秒。这一改变的性能改进系数仅为 80/3.7=22（而非 500），因为每个公式都有少量开销。

错误处理

如果您具有执行大量计算的公式，您希望在出现错误时结果显示为零（在完全匹配查找中经常遇到这种情况），则可以通过多种方式编写它。

  • 可以将它编写为单个公式，这样运行速度很慢：

    B1=IF(ISERROR(time expensive formula),0,time expensive formula)

  • 可编写成两个公式（此方法较快）：

    A1=time expensive formula

    B1=IF(ISERROR(A1),0,A1)

  • 或者，可使用 IFERROR 函数，它运行速度快且简单，还是单个公式：

    B1=IFERROR(time expensive formula,0)

动态统计唯一值

图 7. 统计唯一值的数据列表示例

计算唯一数据示例

如果 A 列中包含 11,000 行数据的列表（这些数据经常更改），而你需要动态计算列表中唯一项数目的公式并且忽略空值，则可采用下面几种解决方案。

  • 数组公式（使用 Ctrl+Shift+Enter）；RangeTimer 显示其耗时 13.8 秒。

    {=SUM(IF(LEN(A2:A11000)>0,1/COUNTIF(A2:A11000,A2:A11000)))}

  • SUMPRODUCT 的计算速度通常比等效数组公式的快。 此公式耗时 10.0 秒，得到的改进系数为 13.8/10.0=1.38，因而此方法更好，但不足够好。

    =SUMPRODUCT((A2:A11000<>"")/COUNTIF(A2:A11000,A2:A11000&amp;""))

  • 用户定义的函数。 下面的代码示例显示了一个 VBA 用户定义的函数，它遵循集合索引必须唯一这个事实。 要查看部分所用技术的相关阐释，请参阅 Excel 性能：性能障碍优化提示的“有效使用函数”部分中的“用户定义的函数”部分。 公式 =COUNTU(A2:A11000) 仅耗时 0.061 秒。 它得到的改进系数为 13.8/0.061=226。

    Public Function COUNTU(theRange As Range) As Variant
    Dim colUniques As New Collection
    Dim vArr As Variant
    Dim vCell As Variant
    Dim vLcell As Variant
    Dim oRng As Range

    Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
    vArr = oRng
    On Error Resume Next
    For Each vCell In vArr
    If vCell <> vLcell Then
        If Len(CStr(vCell)) > 0 Then
             colUniques.Add vCell, CStr(vCell)
        End If
    End If
    vLcell = vCell
    Next vCell

    COUNTU = colUniques.Count
End Function

  • 添加一列公式。 如果查看上一个数据示例，可发现它已排序（Excel 耗时 0.5 秒对 11,000 行进行排序）。 你可利用这一点，方式是添加一列公式，以检查该行的数据是否与上一行中的数据相同。 如果不同，则公式返回 1。 否则，将返回 0。

    将此公式添加到单元格 B2。

      =IF(AND(A2<>"",A2<>A1),1,0)

    复制公式，然后添加一个将 B 列相加的公式。

      =SUM(B2:B11000)

    上述所有公式的完整计算将耗时 0.027 秒。 它得到的改进系数为 13.8/0.027=511。

结论

Excel 让你能够高效管理更大型的工作表，与早期版本相比，它还能大幅提升计算速度。 创建大型工作表时，很容易就采用会导致计算缓慢的方式进行构建。 计算缓慢的工作表会增加错误，因为用户发现在执行计算时很难进行维护。

通过使用一组简单的技术，可将大部分计算缓慢的工作表提速 10 倍或 100 倍。 此外，你还可在设计和创建工作表时应用这些技术来确保其快速计算。

另请参阅

支持和反馈

有关于 Office VBA 或本文档的疑问或反馈？ 请参阅 Office VBA 支持和反馈，获取有关如何接收支持和提供反馈的指南。