在 Power Pivot 中重新计算公式

应用对象
Microsoft 365 专属 Excel Excel 2024 Excel 2021

在 Power Pivot 中处理数据时，可能需要从源刷新数据、重新计算计算列中的公式或更新使用数据模型的数据透视表。

本文介绍了数据刷新和重新计算之间的区别，概述了如何触发重新计算，并介绍了控制重新计算的选项。

了解数据刷新与重新计算

Power Pivot 同时使用数据刷新和重新计算：

数据刷新 意味着从外部数据源获取最新数据。 Power Pivot 不会自动检测外部数据源中的更改。 若要引入更新的数据，请刷新数据模型。

重新计算 意味着更新工作簿中包含公式的所有列、表格、图表和数据透视表。 由于重新计算公式会影响性能，因此了解与每个计算关联的依赖项非常重要。

重要

在重新计算公式之前，请勿保存或发布工作簿。

手动重新计算与自动重新计算

默认情况下，Power Pivot 会根据需要自动重新计算，同时优化处理所需的时间。 尽管重新计算可能需要一些时间，但这是一项重要的任务。 在重新计算过程中，Power Pivot 会检查列依赖关系，并在列发生更改、数据无效或以前正常工作的公式中出现错误时通知你。 但是，您可以选择放弃验证并仅手动更新计算，特别是当您使用复杂的公式或非常大的数据集并想要控制更新的时间时。

手动和自动模式都有优点。 但是，请使用自动重新计算模式。 此模式可使 PowerPivot 元数据保持同步，并防止因数据删除、名称或数据类型更改或缺少依赖项而导致的问题。 

使用自动重新计算

使用自动重新计算模式时，对可能导致任何公式结果更改的数据的任何更改都会触发对包含公式的整个列的重新计算。 以下更改始终需要重新计算公式：

  • 刷新来自外部数据源的值。
  • 公式的定义已更改。
  • 更改公式中引用的表或列的名称。
  • 添加、修改或删除表之间的关系。
  • 添加了新的度量值或计算列。
  • 会对工作簿中的其他公式进行更改，因此应刷新依赖于该计算的列或计算。
  • 插入或删除行。
  • 您应用了需要执行查询以更新数据集的筛选器。 可在公式中应用该筛选器，也可以作为数据透视表或数据透视图的一部分应用。

使用手动重新计算

使用手动重新计算可避免在准备就绪之前产生计算公式结果的成本。 手动模式在以下情况下特别有用：

  • 你正在使用模板设计公式，并希望在验证公式之前更改公式中使用的列和表的名称。
  • 你知道工作簿中的某些数据已更改，但你正在使用未更改的其他列，因此想要推迟重新计算。
  • 你正在一个具有许多依赖项的工作簿中工作，并且希望推迟重新计算，直到确定进行了所有必要的更改。

如果将工作簿设置为手动计算模式，则 Excel 中的 Power Pivot 不会对公式执行任何验证或检查。 会发生以下结果：

  • 添加到工作簿的任何新公式都将标记为包含错误。
  • 新计算列中不会显示任何结果。

将工作簿配置为手动重新计算

  1. 在 Power Pivot 中，选择 “设计>计算>”、“计算选项”、“>手动计算模式”。
  2. 若要重新计算所有表，请选择“ 计算选项”“>立即计算”。
    工作簿会检查公式中的错误，并使用结果更新表。 根据数据量和计算数，工作簿可能会暂时停止响应。

重要

发布工作簿之前，请将计算模式更改回自动。 此更改有助于防止在设计公式时出现问题。

重新计算疑难解答

相关性

如果某个列依赖于另一列，并且该列的内容以任何方式更改，则可能需要重新计算所有相关列。 每当对 PowerPivot 工作簿进行更改时，Excel 中的 Power Pivot 都会分析现有 Power Pivot 数据以确定是否需要重新计算，并以尽可能高效的方式执行更新。

例如，假设您有一个表 Sales，它与 表 Product 和 ProductCategory 相关; Sales 表中的公式依赖于其他两个表。 对 Product 或 ProductCategory 表的任何更改都会导致重新计算 Sales 表中的所有计算列。 考虑到可能具有按类别或产品汇总销售额的公式时，此行为很合理。 为确保结果正确，必须根据数据重新计算公式。

Power Pivot 始终对表执行完整的重新计算，因为完整的重新计算比检查更改的值更有效。 触发重新计算的更改可能包括重大更改，例如删除列、更改列的数值数据类型或添加新列。 但是，看似微不足道的更改（例如更改列名）也可能会触发重新计算。 出现此行为是因为列的名称在公式中用作标识符。

在某些情况下，Power Pivot 确定它可以从重新计算中排除列。 例如，如果有一个公式在“产品”表中查找 [产品颜色] 等值，并且更改的列在“销售额”表中为 [数量]，则即使“销售”表和“产品”表相关，也无需重新计算该公式。 但是，如果你有任何依赖 于 Sales[Quantity] 的公式，则需要重新计算。

依赖列的重新计算序列

Power Pivot 会在重新计算任何列之前计算依赖关系。 如果多个列相互依赖，Power Pivot 将遵循依赖关系的顺序。 此顺序可确保 Power Pivot 以正确的顺序和最大速度处理列。

Transactions

重新计算或刷新数据的操作作为事务进行。 如果刷新操作的任何部分失败，则其余操作将回滚。 此行为可确保数据不会处于部分处理状态。 您无法像在关系数据库中那样管理事务，也无法创建检查点。

重新计算可变函数

某些函数（例如 NOW、 RAND或 TODAY）没有固定值。 为避免性能问题，如果在计算列中使用查询或筛选通常不会导致 Power Pivot 重新评估这些函数。 Power Pivot 仅在重新计算整列时才会重新计算这些函数的结果。 这些情况包括从外部数据源刷新或手动编辑导致重新计算包含这些函数的公式的数据。 但是，如果在计算字段的定义中使用 、 或 TODAY 等RAND易失函数NOW，Power Pivot 始终会重新计算这些函数。