Power Pivot for Excel 教程：热门用例和示例

执行摘要

在此处下载数据集以跟随教程。

皇帝的新装：Power Pivot 教程

在跨越金融、金融分析、金融市场和金融投资的各个领域和子领域中，Microsoft Excel 是王者。 然而，随着大数据的到来和指数级增长，在数十年的数据聚合和积累、廉价云存储的出现和物联网（即电子商务、社交媒体和设备的互联性）的兴起的推动下，Excel 的遗留功能和能力已被推到了极限。

更具体地说，老一代 Excel 的基础架构和处理限制，例如 1,048,576 行的行数限制，或者在涉及大型数据集、数据表和互连电子表格时不可避免的处理速度减慢，降低了其作为有效大数据工具的可用性。 然而，在 2010 年，微软为 Excel 添加了一个新维度，称为 Power Pivot。 Power Pivot 为 Excel 提供了下一代商业智能和商业分析功能，能够在不影响处理速度的情况下提取、组合和分析几乎无限的数据集。 然而，尽管它在八年前发布，但大多数金融分析师仍然不知道如何使用 Power Pivot，许多人甚至不知道它的存在。

在本文中，我将向您展示如何使用 Power Pivot 来克服常见的 Excel 问题，并通过一些示例了解该软件的其他主要优势。 本 Power Pivot 教程旨在作为您可以使用此工具实现的目标的指南，最后，它将探索一些 Power Pivot 通常证明非常宝贵的示例用例。

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

Power Pivot 是 Microsoft Excel 的一项功能，它是作为 Excel 2010 和 2013 的加载项引入的，现在是 Excel 2016 和 365 的本机功能。正如 Microsoft 解释的那样，Power Pivot for Excel “使您能够导入数百万行将来自多个数据源的数据整合到一个 Excel 工作簿中，创建异构数据之间的关系，使用公式创建计算列和度量，构建数据透视表和数据透视图，然后进一步分析数据，以便您可以在不需要 IT 帮助的情况下及时做出业务决策。 ”

Microsoft 在 Power Pivot 中使用的主要表达式语言是 DAX（数据分析表达式），尽管在特定情况下可以使用其他语言。 同样，正如微软解释的那样，“DAX 是函数、运算符和常量的集合，可用于公式或表达式中，以计算和返回一个或多个值。 更简单地说，DAX 可帮助您从模型中已有的数据中创建新信息。” 幸运的是，对于那些已经熟悉 Excel 的人来说，DAX 公式看起来很熟悉，因为许多公式具有相似的语法（例如SUM 、 AVERAGE 、 TRUNC ）。

为清楚起见，使用 Power Pivot 与基本 Excel 的主要优势可总结如下：

• 它允许您导入和操作数亿行数据，其中 Excel 的硬约束只有超过一百万行。
• 它允许您将来自多个源的数据导入到一个单一的源工作簿中，而无需创建多个受版本控制和可转移性问题困扰的源工作表。
• 它可以让您处理导入的数据、分析它并得出结论，而不会让您的计算机慢到蜗牛的速度。
• 它使您可以使用 PivotCharts 和 Power BI 可视化数据。

在以下部分中，我将逐一介绍上述每个内容，并向您展示 Power Pivot for Excel 如何提供帮助。

如何使用 Power Pivot

1) 导入大型数据集

如前所述，Excel 的主要限制之一与处理超大数据集有关。 对我们来说幸运的是，Excel 现在可以将超过一百万行的限制直接加载到 Power Pivot 中。

为了证明这一点，我生成了一个包含九个不同产品类别和四个地区的体育用品零售商两年销售额的样本数据集。 生成的数据集是 200 万行。

使用功能区上的数据选项卡，我从 CSV 文件创建了一个新查询（请参阅下面的创建新查询）。 此功能以前称为 PowerQuery，但在 Excel 2016 和 365 中，更紧密地集成到 Excel 的“数据”选项卡中。

创建新查询

从 Excel 中的空白工作簿到将所有 200 万行全部加载到 Power Pivot 中，大约需要一分钟！ 请注意，我可以通过将第一行提升为列名来执行一些轻量级的数据格式化。 在过去几年中，Power Query 功能已从 Excel 加载项大幅改进为工具栏上“数据”选项卡的紧密集成部分。 Power Query 可以通过其选项套件和自己的语言 M.

2) 从多个来源导入数据

Power Pivot for Excel 的其他主要优势之一是能够轻松地从多个来源导入数据。 以前，我们中的许多人为我们的各种数据源创建了多个工作表。 通常，此过程涉及编写 VBA 代码并从这些不同的源复制/粘贴。 不过，对我们来说幸运的是，Power Pivot 允许您将来自不同数据源的数据直接导入 Excel，而不必遇到上述问题。

使用图表 1 中的查询功能，我们可以从以下任何来源中提取：

• 微软天青
• SQL 服务器
• 太极数据
• Facebook
• 销售队伍
• JSON 文件
• Excel 工作簿
• …还有很多

此外，可以在查询功能或 Power Pivot 窗口中组合多个数据源以集成数据。 例如，您可以通过 Query 将 Excel 工作簿中的生产成本数据和 SQL Server 中的实际销售结果提取到 Power Pivot 中。 从那里，您可以通过匹配生产批次数来组合这两个数据集以产生每单位毛利率。

3) 使用大型数据集

Power Pivot for Excel 的另一个关键优势是能够操纵和使用大型数据集以得出相关结论和分析。 我将通过下面的一些常见示例让您了解该工具的强大功能。

措施

Excel 爱好者无疑会同意，数据透视表既是最有用的一种，同时也是我们执行的最令人沮丧的任务之一。 在处理更大的数据集时尤其令人沮丧。 幸运的是，Power Pivot for Excel 允许我们在处理更大的数据集时轻松快速地创建数据透视表。

在下图中，标题为“创建度量” ，请注意 Power Pivot 窗口是如何分成两个窗格的。 顶部窗格包含数据，底部窗格包含度量。 度量是在整个数据集上执行的计算。 我通过在突出显示的单元格中输入了一个度量值。

 Total Sales:=SUM('Accounting Data'[Amount])

这将创建一个新的度量值，该度量值在 Amount 列中求和。 同样，我可以在下面的单元格中键入另一个度量

Average Sales:=AVERAGE('Accounting Data'[Amount])

创建度量

从那里，观察在大型数据集上创建熟悉的数据透视表有多快。

创建数据透视表

维度表

作为使用 Excel 的金融分析师，我们擅长使用复杂的公式来使技术符合我们的意愿。 我们掌握了VLOOKUP 、 SUMIF ，甚至是可怕的INDEX(MATCH()) 。 但是，通过使用 Power Pivot，我们可以将其中的大部分内容扔到窗外。

将用户创建的表添加到 Power Pivot 模型

为了演示此功能，我创建了一个小型参考表，其中我将每个类别分配给一个类型。 通过选择“添加到数据模型”，此表将加载到 Power Pivot（请参阅上面的将用户创建的表添加到 Power Pivot 模型）。

我还创建了一个日期表来与我们的数据集一起使用（请参阅下面的创建日期表）。 Power Pivot for Excel 可以轻松快速地创建日期表，以便按月、季度和星期几进行合并。 用户还可以创建更自定义的日期表，以按周、会计年度或任何特定于组织的分组进行分析。

创建日期表

计算列

除了度量之外，还有另一种类型的计算：计算列。 Excel 用户会很乐意编写这些公式，因为它们与在数据表中编写公式非常相似。 我在下面创建了一个新的计算列（请参阅下面的创建计算列），它按金额对会计数据表进行排序。 低于 50 美元的销售额被标记为“小”，而所有其他销售额被标记为“大”。 公式感觉不直观吗？

创建计算列

关系

然后，我们可以使用图表视图在会计数据表的类别字段和类别表的类别字段之间创建关系。 此外，我们可以定义会计数据表的销售日期字段和日历表的日期字段之间的关系。

定义关系

现在，不需要任何SUMIF或VLOOKUP函数，我们可以创建一个按年份计算总销售额的数据透视表，然后键入事务大小的切片器。

使用关系的数据透视表

或者，我们可以使用新的日历表为一周中的每一天创建一个平均销售额图表。

使用关系的数据透视图

虽然这个图表看起来很简单，但令人印象深刻的是，它只用了不到 10 秒的时间就创建了超过 200 万行数据的合并，而无需向销售数据添加新列。

在能够执行所有这些合并报告方案的同时，我们始终可以深入了解各个行项目。 我们保留高度精细的数据。

高级功能

到目前为止，我展示的大部分分析都是相对简单的计算。 现在，我想展示这个平台的一些更高级的功能。

时间智能

通常，当我们检查财务结果时，我们希望将其与上一年的可比时间框架进行比较。 Power Pivot 具有一些内置的时间智能功能。

 Same Period Last Year Sales:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date])) YOY Sales Growth:=if(not(ISBLANK([Same Period Last Year Sales])),([Total Sales]/[Same Period Last Year Sales])-1,BLANK())

例如，将上面的两个度量添加到 Power Pivot 中的记帐数据表中，我只需单击几下即可生成以下数据透视表。

时间智能数据透视表

粒度不匹配

作为一名金融分析师，我经常需要解决的一个问题是粒度不匹配。 在我们的示例中，实际销售数据显示在类别级别，但让我们准备一个仅在季节性级别上的预算。 为了进一步加剧这种不匹配，我们将编制季度预算，即使销售数据是每天的。

不匹配的粒度 - 预算表

使用 Power Pivot for Excel，这种不一致很容易解决。 通过在数据库命名法中创建两个额外的参考表或维度表，我们现在可以创建适当的关系来分析我们的实际销售额与预算金额。

不匹配的粒度 - 关系

在 Excel 中，以下数据透视表可以快速组合在一起。

不匹配的粒度——预算与实际结果

此外，我们可以定义新的度量来计算实际销售额和预算销售额之间的差异，如下所示：

 Actual-to-Budget Variance:=DIVIDE([Total Sales],[Total Budgeted Sales])-1

使用此度量，我们可以在数据透视表上显示方差。

不匹配的粒度 - 方差结果

占总数的百分比

最后，让我们检查特定类别的销售额占所有销售额的百分比（例如，类别对整体销售额的贡献），以及特定类别的销售额占同一类型的所有销售额的百分比（例如，类别对季节性类型的贡献）销售量）。 我创建了以下两个度量：

 Total Sales as Percent of All Sales:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data')) Total Sales as Percent of Type:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'[Category]))

这些度量现在可以部署在新的数据透视表中：

占总数的百分比

请注意如何在类别和季节性类型级别执行计算。 我喜欢在如此庞大的数据集上快速轻松地执行这些计算。 这些只是 Power Pivot 优雅和强大计算能力的几个例子。

压缩

另一个好处是文件大小缩小。 原始文件大小为 91MB，现在小于 4MB。 那是原始文件的 96% 的压缩。

文件大小

这是怎么发生的？ Power Pivot 使用 xVelocity 引擎压缩数据。 简单来说，数据存储在列中而不是行中。 这种存储方法允许计算机压缩重复值。 在我们的示例数据集中，只有四个区域在所有 200 万行中重复。 Power Pivot for Excel 可以更有效地存储这些数据。 结果是对于具有许多重复值的数据，存储这些数据的成本要低得多。

需要注意的一点是，我在此示例数据集中使用了整美元的金额。 如果我包含两个小数点来反映美分，压缩效果将减少到原始文件大小的 80%，仍然令人印象深刻。

SSAS 表格

Power Pivot 模型还可以扩展到整个企业。 假设您构建了一个 Power Pivot 模型，该模型开始在组织中获得许多用户，或者数据增长到一千万行，或者两者兼而有之。 此时，您可能不希望 30 个不同的用户刷新模型或进行更改。 该模型可以无缝转换为 SSAS 表格。 所有的表格和关系都被保留了，但现在您可以控制刷新频率，为不同的用户分配角色（例如，只读、读取和处理），并且只部署一个链接到表格模型的小型 Excel 前端。 结果是您的用户随后可以使用小型工作簿访问已部署的表格模型，但无法访问公式和度量。

4) 数据可视化与分析

立方体公式

我的客户不断提出的要求之一是我创建符合严格定义的布局的报告。 我有客户要求特定的列宽、RGB 颜色代码以及预定义的字体名称和大小。 考虑以下仪表板：

CUBE 公式嵌入

如果我们所有的销售都使用 Power Pivot for Excel，我们如何在不生成数据透视表的情况下填充销售数字？ 使用 CUBE 公式！ 我们可以在任何 Excel 单元格中编写 CUBE 公式，它将使用我们已经构建的 Power Pivot 模型执行计算。

例如，在“2016 年总销售额”下的单元格中键入以下公式：

=立方值（“ ThisWorkbook 数据模型"," [措施].[总销售额] "," [日历].[年份].[2016] ")

公式的第一部分以黄色突出显示，指的是 Power Pivot 模型的名称。 通常，对于较新版本的 Power Pivot for Excel，它通常是 ThisWorkbookDataModel。 绿色部分定义我们要使用总销售额度量。 蓝色部分指示 Excel 仅筛选销售日期为 2016 年的行。

在幕后，Power Pivot 构建了一个包含数据、计算列和度量的在线分析处理 (OLAP) 多维数据集。 这种设计允许 Excel 用户通过直接使用 CUBE 函数获取数据来访问数据。 使用 CUBE 公式，我已经能够构建符合预定义布局的完整财务报表。 此功能是使用 Power Pivot for Excel 进行财务分析的亮点之一。

电源 BI

Power Pivot for Excel 的另一个优势是您可以快速获取您构建的任何 Power Pivot 工作簿并将其快速转换为 Power BI 模型。 通过将 Excel 工作簿直接导入 Power BI Desktop 应用或 Power BI Online，您可以分析、可视化并与组织中的任何人共享您的数据。 从本质上讲，Power BI 是 Power Pivot、PowerQuery 和 SharePoint 合而为一。 下面，我通过将以前的 Power Pivot for Excel 工作簿导入 Power BI 桌面应用程序来创建仪表板。 注意界面的交互性：

电源 BI

Power BI 的一大优点是自然语言问答。 为了演示，我将 Power BI 模型上传到我的在线 Power BI 帐户。 在网站上，我可以提出问题，Power BI 在我键入时构建适当的分析：

自然语言问答

这种类型的查询功能使用户能够以比 Excel 更简单的方式询问数据模型问题并与数据交互。

Power BI 的另一个好处是 Microsoft 的开发人员会不断发布更新。 每月推出许多用户要求的新功能。 最重要的是，它是从 Power Pivot for Excel 的无缝过渡。 因此，您投入学习 DAX 公式的时间可以部署在 Power BI 中！ 对于需要在不同设备上与许多用户分享其分析的分析师来说，Power BI 可能值得探索。

最佳实践

一旦开始，您应该遵循一些最佳实践。

首先是要深思熟虑地首先决定要导入什么。 你会使用销售人员的家庭住址吗？ 在本工作簿的上下文中，我是否需要知道我的客户的电子邮件地址？ 如果目标是将数据聚合到仪表板中，则某些可用数据对于这些计算将不是必需的。 花时间整理传入的数据将在以后扩展数据集时大大缓解问题和内存使用。

另一个最佳做法是记住 Power Pivot 不是 Excel。 在 Excel 中，我们习惯于通过不断向右扩展工作表来创建计算。 如果我们限制这种对明显命运的渴望，Power Pivot for Excel 可以最有效地处理数据。 学习在底部窗格中编写度量，而不是不断地在数据右侧创建计算列。 这种习惯将确保更小的文件大小和更快的计算。

最后，我建议使用简单的英文名称进行度量。 这个我花了很长时间才采用。 最初几年我花了一些时间来编造SumExpPctTotal类的名称，但是一旦其他人开始使用相同的工作簿，我就有很多解释工作要做。 现在，当我开始一个新的工作簿时，我使用诸如Expense Line Item as Percent of Total Expenses类的度量名称。 虽然名称较长，但对其他人来说更容易使用。

实际用例

在本文中，我仅介绍了 Power Pivot for Excel 允许您在普通 Excel 之外迈出重要一步的几种方法。 我认为强调一些我发现 Power Pivot for Excel 非常有用的实际用例会很有用。

这里有一些：

• 分析大型资产组合在不同时间范围内的表现：由于 Excel 的 Power Pivot 允许我们定义将时间段与前一个时间段进行比较的度量，我们可以快速获得季度环比、同比、只需编写几个度量值，所有这些都在滚动的基础上进行。
• 使用自定义聚合级别汇总会计数据：通过按名称、类别和财务报表识别每个总帐行项目，可以快速创建包含适当行项目的报告。
• 连锁店可以识别同店销售额：使用一张映射商店何时上线的表格，可以在同店的基础上比较财务结果。
• 查明销售表现出色和表现不佳的企业：可以创建数据透视表，按销售额、毛利率、生产时间框架等突出显示前五个 SKU 和后五个 SKU。
• 零售商可以定义使用 4-4-5 配置的日历表：使用自定义日期表，零售商可以轻松地将每一天分配到特定的 4-4-5 月份，然后可以将每日销售结果滚动到相应的月份。

从笨重的电子表格到现代工作簿

作为金融分析师，我们需要对不断扩展的数据集执行复杂的计算。 由于 Excel 已经是默认的分析工具，Power Pivot 的学习曲线很容易，并且许多功能都反映了 Excel 的本机功能。

通过使用 CUBE 函数，Power Pivot for Excel 可以无缝融入您现有的 Excel 工作簿。 计算效率增益不容忽视。 假设处理速度提高 20%（这是保守的），金融分析师每天在 Excel 中花费 6 小时可以节省 300 小时！

此外，我们现在可以分析比以前使用传统 Excel 大得多的数据集。 借助高效设计的模型，我们可以轻松获得 10 倍于传统 Excel 之前允许的数据量，同时保持快速的分析敏捷性。 借助将模型从 Power Pivot 转换为 SSAS 表格的能力，可以处理的数据量是我们在 Excel 中可以实现的 100-1,000 倍。

Power Pivot for Excel 能够对大量数据执行闪电般的快速计算，并且仍然保留深入研究细节的能力，可以将财务分析从笨重的电子表格转变为现代工作簿。

如果您有兴趣尝试 Power Pivot for Excel，以下是一些有用的材料，可帮助您入门。

有用的参考和指南

Collie, R. 和 Singh, A. (2016)。 Power Pivot 和 Power BI：Excel 2010-2016 中的 DAX、Power Query、Power BI 和 Power Pivot 用户指南。 美国：神圣宏！ 图书。

Ferrari, A. 和 Russo, M. (2015)。 DAX 权威指南：Microsoft Excel、SQL Server Analysis Services 和 Power BI 的商业智能。 美国：微软出版社，美国。