Excel Power Pivot：分步指南

分析大型 Excel 文件往往会导致性能变慢。

Power Pivot 提供了不同的方法。它连接表并处理计算，而不会牺牲性能。您无需再与VLOOKUP()链和辅助列周旋，而是可以使用直接内置于 Excel 的结构化系统。

在本指南中，您将学习如何设置数据模型、创建表关系、编写 DAX 公式，并使用 Power Pivot 构建交互式报告。

什么是 Power Pivot，它为何有用？

Power Pivot 是 Excel 的 内置数据建模引擎。它让您能够导入更大的数据集、连接多个表，并在不拖慢传统工作表的情况下运行复杂计算。

Power Pivot 有何不同

Power Pivot 并非将数据直接存储在工作表中，而是把一切加载到 Excel 的内部数据模型里。

标准工作表大约可达一百万行，而且通常更早就会变慢。Power Pivot 通过压缩数据并单独管理，从而绕过该限制，因此您可以在不影响工作簿性能的情况下处理数千万行数据。

用关系型结构替代 VLOOKUP 链

数据进入模型后，您可以像轻量级数据库那样使用键将表关联起来。您无需把所有内容拍扁到一个巨型工作表，并使用嵌套的 VLOOKUP() 函数来强行拼表。Power Pivot 让您能够并行、干净且可靠地分析关联表。

借助 DAX 的更强计算能力

Power Pivot uses DAX（Data Analysis Expressions），一门专为分析工作构建的公式语言。您可以用它创建远超标准数据透视表能力的度量，从简单求和到基于时间的指标、比率、滚动窗口以及其他高级计算。

示例场景

以下是企业在运营中使用 Power Pivot 的两个示例：

• 销售业绩跟踪：合并订单历史、产品表和客户属性，然后使用 DAX 度量计算同比收入或客户生命周期价值，无需手动合并。
• 运营报告：关联库存、发运与供应商数据，然后在同一模型中计算履约率、交付周期或预测偏差。

简而言之，Power Pivot 为 Excel 带来了数据库式体验。如果您处理大型或多表数据集，它可以将凌乱的报表流程转化为快速、可扩展、可持续迭代的模型。

在 Excel 中设置 Power Pivot

下面看看如何在 Excel 中开始使用 Power Pivot。

启用 Power Pivot

您无需下载 Power Pivot，它已内置在 Excel 中。启用方式如下：

1. 打开 Excel 工作表
2. 在功能区中点击“文件”选项
3. 选择“选项 >” “加载项”
4. 然后在下拉菜单中选择“COM 加载项”并点击“转到”
5. 会出现一个弹窗。在此选择“Microsoft Power Pivot for Excel”，然后点击“确定”

现在 Power Pivot 选项卡会出现在您的功能区中。

在 Excel 中启用 Power Pivot 加载项。图片来源：作者。

注意：Power Pivot 仅适用于 Excel Professional Plus or Microsoft 365。如果启用后仍未看到该选项卡，您电脑上的 Excel 版本可能不包含它。

从多个来源导入数据

您现在可以从不同来源导入数据，例如 Excel 文件、CSV 文件，甚至是 SQL Server 数据库。

在本示例中，我们在一个 .xlsb 文件中有两个数据集：

1. sales.xlsb

2. customer.xlsb

将它们导入 Power Pivot 的步骤如下：

1. 点击“Power Pivot”选项卡并选择“管理”。将打开一个新窗口
2. 转到“主页”，点击“获取外部数据”并选择“从其他源”
3. 向下滚动并点击“Excel 文件”

从其他来源获取数据。图片来源：作者。

4. 在弹窗中点击“浏览”并选择 customer.xlsb 文件

5. 勾选“将首行用作列标题”，然后点击“下一步”

将 Excel 文件导入 Power Pivot。图片来源：作者。

在下一个窗口中，点击“预览与筛选”，在导入前查看数据效果。确认无误后点击“确定”，系统会显示所有行已成功传输。然后点击“关闭”。

预览所选数据。图片来源：作者。

对 sales.xlsb 文件重复相同步骤。随后，在屏幕底部可以看到两个文件都已导入。双击并重命名。

两个文件均已导入。图片来源：作者。

构建关系与数据模型

数据已加载到 Power Pivot，接下来需要将表连接起来，让 Excel 理解它们之间的关联。这一步为所有报告打下基础。

创建表之间的关系

要在“Sales”与“Customers”表之间创建关系：

1. 在“主页”选项卡中，点击“图表视图”。您会看到两个已导入的表
2. 在“Sales”表中点击“CustomerID”
3. 将其拖动到“Customer”表中的“CustomerID”上，以创建两表之间的关系

注意：如需编辑关系，右键单击连线并点击“编辑关系..”。在弹窗中选择要用于建立关系的列。

在表之间建立关系。图片来源：作者。

在此关系中，一个客户可以在“Sales”表中出现多次，但在“Customers”表中只出现一次。这是一个简单的一对多关系，使我们无需查找公式即可在数据透视表中使用两表字段并进行计算。

采用星型模式设计

星型模式是构建 Power Pivot 模型最简单的方法之一。它有助于组织表，并让计算更可预期。

首先，您需要选择事实表。本例中，“Sales”充当事实表，因为它包含交易记录：日期、客户、产品、数量和金额。

接着，确定描述“Sales”数据的维度表。常见示例包括：

• Customers（主键：CustomerID）
• Products（主键：ProductID）
• Regions（主键：RegionID）

每个维度表都有主键。将该键连接到事实表中的对应外键：

• Customers.CustomerID → Sales.CustomerID
• Products.ProductID → Sales.ProductID
• Regions.RegionID → Customers.RegionID

建立连接后，“Sales”表位于中心，维度表环绕四周，这就是“星型”。这种结构使模型清晰、加快计算，并提升报告一致性。

创建星型模式。图片来源：作者。

添加计算列

建立关系后，您可以在数据模型中直接创建新字段。

1. 切换到“数据视图”

2. 选择表末尾空白的“添加列”字段。

3. 输入 = [TotalAmount] / [Qty] 并按Enter，Excel 会填充整列

4. 将表头重命名为“PricePerUnit”

这样，计算列就成为表本身的一部分。它们存储在模型中，随数据刷新，并可用于您后续构建的任何数据透视表或 DAX 度量。

添加额外的计算列。图片来源：作者。

为分析编写 DAX 公式

模型准备就绪后，我们就可以开始创建 DAX 公式来分析数据。这些公式可帮助我们在报告中构建总计、对比和基于时间的计算。

创建度量

当您需要在数据透视表中自动刷新的计算时，应使用度量。

创建度量的方法：

1. 打开 Power Pivot 窗口

2. 转到“主页 > 计算 > 新建度量”

3. 输入类似 = SUM(Sales[TotalAmount]) 的公式

4. 将其命名为“Total Sales”并选择“确定”

创建度量。图片来源：作者。

添加总计占比度量

您也可以使用以下公式添加总计占比度量：

= DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Regions)))

这将显示每个地区对总体收入的贡献份额。

添加总计百分比度量。图片来源：作者。

使用时间智能

时间智能函数是能理解数据在天、月、季度和年之间变化的 DAX 公式。它们使您无需手动调整筛选器即可计算年初至今总计、与前期对比并评估趋势。

要在模型中使用这些函数，首先需要一个合格的日期表。

设置日期表

设置步骤如下：

1. 转到“Power Pivot > 添加到数据模型”
2. 在 Power Pivot 中选中该表，然后选择“设计 > 标记为日期表”

创建日期表。图片来源：作者。

3. 然后在“主页” > “图表视图”中，将Date[Date] → Sales[OrderDate] 关联起来。

将 Date Table[Date] 关联到 Sales[OrderDate]。图片来源：作者。

创建时间智能度量

完成 Date 表后，您可以构建跨不同时期评估绩效的度量。

年初至今（YTD）：

Total Sales YTD :=
TOTALYTD([Total Sales], 'Date Table'[Date])

去年同期对比：

Sales Last Year :=
CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date Table'[Date]))

进行时间计算。图片来源：作者。

准备好度量后，返回 Excel 并 create a PivotTable using the Data Model。然后将日期表中的字段放入“行”区域，并将 Total Sales、Total Sales YTD 和 Sales Last Year 添加到“值”。

这展示了时间智能度量如何与模型中的 Date 表配合工作。

数据透视表显示 Total Sales、YTD 和 Last Year。图片来源：作者。

常见 DAX 模式

有些 DAX 公式经常出现，因为它们能快速细分数据并回答常见问题。以下是两个在许多模型中都很实用的模式：

每类别平均值：

Average Sales Per Category :=
AVERAGE(Sales[TotalAmount])

跨日期的累积总计：

Running Total Sales :=
CALCULATE(
    [Total Sales],
    FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]))
)

在创建度量时，建议养成以下习惯：

• 清晰命名
• 保持公式可读
• 当度量较长时使用变量（VAR）。

这样当您回头查看时，模型更易理解。

可视化并与模型交互

模型和度量就绪后，让我们将数据转化为可实时探索和调整的可视化。

创建数据透视表和数据透视图

以下是在数据模型中插入数据透视表，从而直接使用已连接表的方法：

1. 打开一个 Excel 工作表
2. 转到“插入 > 数据透视表 > 自数据模型”
3. 选择“新工作表”

在“数据透视表字段”窗格中，您可以从任意表中拖拽字段。例如：

• 将 Regions 表中的RegionName拖到“行”
• 将Total Sales拖到“值”

由于我们之前已建立关系，Excel 会自动将一切关联到位。

使用 Power Pivot 数据创建数据透视表。图片来源：作者。

如需可视化，点击数据透视表任意位置，转到“插入 > 数据透视图”，选择图表类型（如“簇状柱形图”），并确认。图表会与数据透视表保持联动，因此更新同步进行。

添加数据透视图。图片来源：作者。

添加切片器和筛选器

切片器提供按钮式的快速筛选，使报告具备交互性。添加方法如下：

1. 点击您的数据透视表
2. 转到“插入 > 切片器”
3. 选择诸如RegionName或ProductName

切片器会以一个方框出现在工作表上。点击不同项目，数据透视表和图表会即时更新。如果您有多个数据透视表，还可以将单个切片器连接到它们，实现整页一致筛选。

添加切片器。图片来源：作者。

构建 KPI

KPI 可帮助您在不为工作表添加额外计算的情况下查看相对于目标的绩效。创建步骤如下：

1. 在 Power Pivot 窗口，转到“KPI” > “新建 KPI”
2. 将“Total Sales”设为基准度量
3. 使用“绝对值”，输入目标值（例如4000），调整阈值并选择图标样式
4. 点击“确定”创建 KPI

为度量设置 KPI。图片来源：作者。

5. 在数据透视表字段窗格中展开“Sales”表，然后展开“Total Sales”
6. 从中将“Total Sales”和“Status”拖到“值”区域

现在您可以查看相对于阈值的目标绩效。

在 Excel 数据透视表中显示 KPI 状态。图片来源：作者。

优化 Power Pivot 性能

模型建立后，我们希望保持其快速且易用。Power Pivot 能处理大型数据集，但做些小优化可让文件在随着时间新增数据时仍然响应灵敏。

缩小模型体量

更轻量的模型运行更快，因此请移除不需要的任何内容。

您可以在“数据视图”中删除未使用的列。即便某列从未出现在数据透视表中，它也会占用内存，删除它们有助于保持模型整洁。

在引入新数据时，使用 Power Query 在进入模型之前筛选行和列。这样只加载您关心的字段，整体更干净。

除非必要，尽量避免计算列，因为它们会为每一行存储一个值，文件体量会很快变大。相反，度量更高效，因为只有在数据透视表需要时才进行计算。

选择高效的数据类型

Power Pivot 会根据数据类型以不同方式压缩数据。选择正确类型会带来显著差异。

在“数据视图”中，选择一列并在功能区的“数据类型”下选择最准确的类型。例如：

• 整数 → “整数”
• 小数 → “小数”
• 不参与计算的 ID 或代码 → “文本”

选择正确类型后，Power Pivot 能更好地压缩该列，从而减小体量并加速计算。

检查并使用正确的数据类型。图片来源：作者。

处理刷新与计算问题

如果数据透视表未反映最新数据，请转到“Power Pivot”选项卡并点击“全部刷新”。这会从源文件重新加载所有内容。

当数值看起来不对时，打开“图表视图”检查您的关系，因为缺失或损坏的关系会导致总计异常或筛选出错。

如果遇到 DAX 错误，尤其是在更复杂的度量中，通常意味着公式间接引用了自身。在这种情况下，请以更简单的 logic 重写度量，或使用 VAR 块来解决循环引用。

与 Power Query 和 Power BI 集成

Power Pivot 的一大 advantages 在于它能轻松与微软数据栈的其他工具协同工作。我们可以使用 Power Query 在数据进入模型前进行清洗与整形，或在您需要交互式仪表板时将整个模型迁移到 Power BI。

在 Power Query 中清洗与转换数据

Power Query 是在加载到 Power Pivot 前准备数据的最佳位置。它允许您事先清洗、筛选和整形数据，让模型始终井井有条。

您可以通过转到“数据” > “自文本/CSV” > “转换”打开 Power Query。这会将数据带入编辑器，在其中您可以：

• 删除重复行
• 重命名或重新排序列
• 筛除不需要的值
• 在进入模型前更改数据类型

Power Query 会在窗口右侧记录每一步操作。这意味着每次刷新文件时清洗步骤都会自动运行。

当一切就绪后，选择“关闭并加载到”，然后选择“数据模型”。清洗后的数据将直接加载到 Power Pivot。

将模型导出到 Power BI

当您需要更丰富的可视化或共享仪表板时，也可以将 Power Pivot 模型带入 Power BI。方法如下：

1. 保存您的 Excel 工作簿
2. 打开Power BI Desktop
3. 转到“获取数据” > “Excel 工作簿”
4. 选择您的文件

Power BI 将按在 Power Pivot 中的样子导入表和关系。之后，您可以构建仪表板、与团队协作，并设置计划刷新，让报告无需手动操作即可保持最新。

构建可持续模型的最佳实践

随着模型增长，保持条理能让您更轻松地在后续进行更新、调试和拓展。以下习惯有助于模型长期保持整洁可靠：

命名规范与组织

清晰的命名在您隔数周或数月后回看文件时会产生很大作用。因此，建议使用易读的度量名称，如Total_Sales、Total_Quantity或Profit_Margin ，以便始终清楚每个度量代表的含义。

您还可以在 Power Pivot 窗口中将相关度量分组到“显示文件夹”中。随着模型变大，这些文件夹能让您更快找到所需计算。

数据验证

在信任数字之前，先做几项快速检查：

• 将源数据的总计与数据透视表的总计进行对比
• 使用简单的 DAX 检查，例如：

• COUNTROWS() 用于确认表中的行数

• DISTINCTCOUNT() 用于验证唯一值，例如客户或产品

这些小测试有助于在问题扩大前发现缺失关系、错误筛选或数据问题。

维护并更新模型

在有新数据时，转到“Power Pivot”选项卡并选择“刷新”或“全部刷新”。Power Pivot 会从已连接的数据源重新加载所有内容。

在进行重大结构调整（如添加新关系或重写关键度量）之前，请保存备份副本。这样如果出现意外，您就有安全的回退方案。

结语

Power Pivot 可将您的数据汇集到一处，帮助您构建清晰可靠的报告。模型设置完成后，尽情探索数据、创建可视化，并通过一次刷新更新全部内容。

如果您想学习 Excel 的完整工具集，请查看我们的 Data Analysis with Excel Power Tools 学习路径，以及我们的（当然还有）Power Pivot in Excel 课程。