计算速度慢会影响生产力并增加用户错误。随着响应时间的延长，用户的工作效率和专注于任务的能力会下降。

Excel 有两种主要的计算模式，可让您控制何时进行计算：

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

• 手动计算- 仅当您请求时（例如，按 F9）才重新计算公式。

对于小于大约十分之一秒的计算时间，用户感觉系统正在立即响应。他们甚至在输入数据时也可以使用自动计算。

在十分之一秒到一秒之间，用户可以成功地保持思路，尽管他们会注意到响应时间延迟。

随着计算时间的增加（通常在1到10秒之间），用户在输入数据时必须切换到手动计算。用户错误和烦恼程度开始增加，尤其是对于重复性任务，并且很难保持思路。

当计算时间超过 10 秒时，用户会变得不耐烦，通常会在等待时切换到其他任务。当计算是一系列任务中的一个并且用户失去跟踪时，这可能会导致问题。

要提高 Excel 中的计算性能，您必须了解可用的计算方法以及如何控制它们。

Excel 中的智能重新计算引擎通过持续跟踪每个公式的先例和依赖项（公式引用的单元格）以及自上次计算以来所做的任何更改，尝试最大限度地减少计算时间。下次重新计算时，Excel 仅重新计算以下内容：

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

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

• 易失性函数和可见的条件格式。

即使先前计算的单元格的值在计算时没有更改，Excel 也会继续计算依赖于先前计算的单元格的单元格。

由于在大多数情况下，您仅更改部分输入数据或计算之间的几个公式，因此这种智能重新计算通常只需要完整计算所有公式所需时间的一小部分。

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

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

Excel 有不同的计算阶段：

1. 构建初始计算链并确定从哪里开始计算。当工作簿加载到内存中时，会发生此阶段。

2. 跟踪依赖关系，将单元标记为未计算，并更新计算链。即使在手动计算模式下，此阶段也会在每个单元格输入或更改时执行。通常，它执行得如此之快，以至于您不会注意到它，但在复杂的情况下，响应可能会很慢。

3. 计算所有公式。作为计算过程的一部分，Excel 会对计算链进行重新排序和重组，以优化未来的重新计算。

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

第三阶段在每次计算或重新计算时执行。Excel 尝试依次计算计算链中的每个公式，但如果某个公式依赖于一个或多个尚未计算的公式，则该公式会沿着链发送，以便稍后再次计算。这意味着每次重新计算可以多次计算公式。

第二次计算工作簿通常比第一次要快得多。出现这种情况有几个原因：

• Excel 通常仅重新计算已更改的单元格及其相关单元格。

• Excel存储并重复使用最近的计算顺序，这样可以节省大部分用于确定计算顺序的时间。

• 对于多核计算机，Excel 会尝试根据先前计算的结果来优化跨内核的计算方式。

• 在 Excel 会话中，Windows 和 Excel 都会缓存最近使用的数据和程序，以便更快地访问。

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

每次重新计算和完整计算都会计算当前打开的所有工作簿，解决工作簿和工作表内部以及之间的任何依赖关系，并将所有以前未计算的（脏）单元格重置为计算结果。

您还可以使用Shift+F9仅重新计算选定的工作表。这将解决工作表的依赖性，并将所有以前未计算的（脏）单元格重置为计算值。

在以前版本的 Excel 中，行为有所不同，计算完成后不会将脏单元格设置为计算结果。如果用户定义的函数依赖于此行为，则应将这些函数设置为易失性函数，如本文易失性函数部分中所述。

Excel 还允许使用 Visual 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 数据表（“数据”选项卡 > “数据工具”组 > “假设分析” > “数据表”）不应与表格功能（“开始”选项卡 > “样式”组 > “格式为表格”，或“插入”选项卡 > “表格”组 > “表格”）混淆。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 请求或保存工作簿时，Excel 才会重新计算所有打开的工作簿。对于重新计算时间超过一秒的工作簿，您必须将计算设置为手动模式，以避免在进行更改时出现延迟。

Excel 通过在状态栏中显示“计算”来告诉您手动模式下的工作簿何时需要重新计算。如果工作簿包含循环引用并且选择了迭代选项，状态栏还会显示“计算” 。

如果您的工作簿中有故意的循环引用，则迭代设置使您能够控制工作簿重新计算（迭代）的最大次数和收敛标准（最大更改：何时停止）。清除迭代框，以便如果意外出现循环引用，Excel 会警告您并且不会尝试解决它们。

当您将此工作簿属性设置为 True 时，Excel 的智能重新计算将关闭，并且每次重新计算都会重新计算所有打开的工作簿中的所有公式。对于某些复杂的工作簿，构建和维护智能重新计算所需的依赖关系树所需的时间比智能重新计算节省的时间要长。

如果您的工作簿需要很长时间才能打开，或者即使在手动计算模式下进行小的更改也需要很长时间，则可能值得尝试 ForceFullCalculation。

如果工作簿ForceFullCalculation属性已设置为 True，则计算将显示在状态栏中。

您可以使用VBE (Alt+F11)控制此设置，在项目资源管理器(Ctrl+R) 中选择ThisWorkbook并显示属性窗口(F4)。

图 4. 设置 Workbook.ForceFullCalculation 属性

使用以下步骤和方法可以使您的工作簿计算速度更快。

对于大多数版本的 Excel，更快的处理器当然可以实现更快的 Excel 计算。Excel 2007 中引入的多线程计算引擎使 Excel 能够充分利用多处理器系统，并且您可以预期大多数工作簿都会获得显着的性能提升。

对于大多数大型工作簿，多处理器带来的计算性能增益几乎与物理处理器的数量呈线性关系。然而，物理处理器的超线程只能带来很小的性能提升。

有关详细信息，请参阅Excel 性能：性能和限制改进。

分页到虚拟内存分页文件的速度很慢。您必须有足够的物理 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时间函数更快、更准确的计时器。以下代码示例中显示的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 宏窗口

大多数计算速度慢的工作簿只有几个问题区域或障碍，消耗了大部分计算时间。如果您还不知道它们在哪里，请使用本节中概述的向下钻取方法来查找它们。如果您确实知道它们在哪里，则必须测量每个障碍物所用的计算时间，以便您可以优先考虑消除它们的工作。

向下钻取方法首先对工作簿的计算、每个工作表的计算以及慢速计算工作表上的公式块进行计时。按顺序执行每个步骤并记下计算时间。

1. 确保您只打开了一本工作簿，并且没有其他任务正在运行。

2. 将计算设置为手动。

3. 制作工作簿的备份副本。

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

5. 在每个工作表上依次按 Ctrl+End 检查已使用的范围。

   这显示了最后使用的单元格的位置。如果这超出了您的预期，请考虑删除多余的列和行并保存工作簿。有关详细信息，请参阅Excel 性能：优化性能障碍的提示中的“最小化已用范围”部分。

6. 运行FullCalcTimer宏。

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

7. 运行RecalcTimer宏。

   完整计算后立即重新计算通常会为您提供最佳情况时间。

8. 将工作簿波动性计算为重新计算时间与完整计算时间的比率。

   这衡量了不稳定的公式和计算链的评估受到阻碍的程度。

9. 激活每个工作表并依次运行SheetTimer宏。

   因为您刚刚重新计算了工作簿，所以这为您提供了每个工作表的重新计算时间。这应该使您能够确定哪些是有问题的工作表。

10. 对选定的公式块运行RangeTimer宏。

11. 对于每个问题工作表，将列或行分成少量的块。

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

13. 如有必要，可通过将每个块细分为更少数量的块来进一步深入。

14. 优先考虑障碍物。

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

由于大多数工作表是通过复制包含绝对引用和相对引用混合的公式来构造的，因此它们通常包含大量包含重复或重复计算和引用的公式。

避免复杂的大型公式和数组公式。一般来说，行数和列数越多，复杂的计算越少越好。这为 Excel 中的智能重新计算和多线程计算提供了更好的机会来优化计算。它也更容易理解和调试。以下是一些可帮助您加快工作簿计算速度的规则。

查找重复、重复和不必要的计算，并计算出 Excel 大约需要多少个单元格引用和计算才能计算此障碍的结果。想想如何用更少的参考和计算获得相同的结果。

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

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

• 将重复计算移至一个或多个辅助单元格，然后引用原始公式中的辅助单元格。

• 使用额外的行和列一次性计算和存储中间结果，以便您可以在其他公式中重用它们。

当您发现涉及函数或数组公式的障碍时，请确定是否有更有效的方法来实现相同的结果。例如：

• 对已排序数据的查找效率可能比对未排序数据的查找效率高数十倍或数百倍。

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

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

• 考虑用用户定义的函数替换缓慢的数组公式。

在 Excel 中更好地利用智能重新计算和多线程计算，每次 Excel 重新计算时需要执行的处理就越少，因此：

• 尽可能避免INDIRECT和OFFSET等易失性函数，除非它们比其他函数效率更高。（精心设计的OFFSET使用通常很快。）

• 最小化数组公式和函数中使用的范围的大小。

• 将数组公式和大型公式分解为单独的辅助列和行。

• 避免单线程函数：

  • 语音
  • 使用“格式”或“地址”参数时的 CELL
  • 间接
  • 获取枢轴数据
  • 立方体会员
  • 立方体值
  • 立方体成员属性
  • 立方体集
  • CUBER排名会员
  • 立方体成员
  • 立方集计数
  • 给出第五个参数（sheet_name）的地址
  • 引用数据透视表的任何数据库函数（DSUM、DAVERAGE 等）
  • 错误类型
  • 超级链接
  • VBA 和 COM 插件用户定义函数

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

您所做的一些更改可能会让您感到惊讶，要么没有给出您认为会给出的答案，要么计算速度比您预期的要慢。因此，您应该计时并测试每个更改，如下所示：

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

2. 做出改变。

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

4. 检查更改后的公式是否仍然给出正确的答案。

以下部分提供了如何使用规则来加速计算的示例。

例如，您需要计算包含 2,000 个数字的列的期初至今总和。假设 A 列包含数字，B 列和 C 列应包含期间至今的总计。

您可以使用SUM编写公式，这是一个高效的函数。

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

图 6. 期初至今 SUM 公式示例

将公式复制到 B2000。

SUM总共加起来了多少个单元格引用？B1 指 1 个单元，B2000 指 2,000 个单元。每个单元格平均有 1,000 个引用，因此引用总数为 200 万个。选择 2,000 个公式并使用RangeTimer宏会显示 B 列中的 2,000 个公式的计算时间为 80 毫秒。大多数这些计算都会重复多次：SUM将 B2:B2000 中每个公式中的 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 秒。

  VB复制

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

• SUMPRODUCT通常比等效的数组公式计算得更快。这个公式需要10.0秒，给出的改进因子为13.8/10.0=1.38，这是更好的，但还不够好。

  VB复制

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

• 用户定义的函数。以下代码示例显示了一个 VBA 用户定义函数，该函数使用集合的索引必须是唯一的这一事实。有关所使用的一些技术的说明，请参阅Excel 性能：优化性能障碍的提示中的“高效使用函数”部分中有关用户定义函数的部分。这个公式 ，=COUNTU(A2:A11000)只需要 0.061 秒。这给出了 13.8/0.061=226 的改进系数。

  VB复制

  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。

  VB复制

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

  复制公式，然后添加公式以将 B 列相加。

  VB复制

    =SUM(B2:B11000)
  

  所有这些公式的完整计算需要 0.027 秒。这给出了 13.8/0.027=511 的改进系数。

Excel 使您能够有效地管理更大的工作表，并且与早期版本相比，它显着提高了计算速度。当您创建大型工作表时，很容易以导致计算缓慢的方式构建它们。计算缓慢的工作表会增加错误，因为用户发现在计算时很难保持注意力。

通过使用一组简单的技术，您可以将大多数计算速度较慢的工作表速度加快 10 或 100 倍。您还可以在设计和创建工作表时应用这些技术，以确保它们快速计算。

Excel performance - Improving calculation performance | Microsoft Learn