行动纲要

在此下载数据集，跟随教程学习。

皇帝的新装:Excel权力中枢教程

在跨越金融、金融分析、金融市场和金融投资的各个领域和子领域中，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是Microsoft Excel的一项功能，最初是作为Excel 2010和2013的加载项推出的，现在是Excel 2016和365的原生功能。正如微软所解释的那样，Power Pivot for Excel“使您能够将来自多个数据源的数百万行数据导入到单个Excel工作簿中，创建异构数据之间的关系，使用公式创建计算列和度量，构建数据透视表和数据透视图，然后进一步分析数据，以便您可以在不需要IT帮助的情况下及时做出业务决策。”

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

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

• 它让您可以导入和操作数亿行数据，而Excel的硬约束只有100多万行。
• 它允许您将多个源中的数据导入到一个单一的源工作簿中，而不必创建受版本控制和可传输性问题困扰的多个源工作表。
• 它可以让你操作导入的数据，分析数据，并得出结论，而不会让你的计算机变慢。
• 它让您可以通过数据透视图和Power BI可视化数据。

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

如何使用动力枢轴

1）导入大型数据集

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

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

使用 数据 选项卡上，我创建了一个 新查询 从CSV文件中（请参见 创建新查询 下面)。这项功能过去被称为PowerQuery，但从Excel 2016和365开始，它被更紧密地集成到Excel的数据选项卡中。

从Excel中的空白工作簿到将所有两百万行加载到Power Pivot中，大约需要一分钟！请注意，我可以通过将第一行提升为列名来执行一些简单的数据格式化。在过去的几年中，超级查询功能已经从Excel加载项大大改进为工具栏上数据选项卡的紧密集成部分。Power Query可以通过它的一套选项和自己的语言m对数据进行透视、展平、清理和整形。

2）从多个来源导入数据

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

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

• 微软Azure
• SQL Server
• Teradata
• 脸谱网
• 销售力量
• JSON文件
• Excel工作簿
• …以及更多

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

3）处理大型数据集

Excel中Power Pivot的另一个关键优势是能够操纵和处理大型数据集以得出相关结论和分析。下面我将介绍几个常见的例子，让您了解该工具的强大功能。

措施

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

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

这将创建一个对“金额”列求和的新度量。同样，我可以在下面的单元格中输入另一个度量值

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

维度表

作为使用Excel的财务分析师，我们变得擅长使用复杂的公式来使技术屈从于我们的意愿。我们掌握，甚至可怕的。然而，通过使用权力中枢，我们可以将其中的大部分扔出窗外。

为了演示这个功能，我创建了一个小的引用表，在表中我将每个类别分配给一个类型。通过选择“添加到数据模型”，该表被加载到Power Pivot中（请参见 向Power Pivot模型添加用户创建的表 以上)。

我还创建了一个用于数据集的数据表（参见 创建日期表格 下面)。Power Pivot for Excel可以轻松地快速创建日期表，以便按月、季度和星期几进行合并。用户还可以创建一个更加自定义的日期表，按周、财政年度或任何特定于组织的分组进行分析。

计算列

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

关系

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

现在，不需要任何或函数，我们就可以创建一个数据透视表，使用事务大小切片器按年份和类型计算总销售额。

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

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

虽然能够执行所有这些整合的报告方案，但我们始终可以深入查看各个行项目。我们保留高度细化的数据。

高级功能

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

时间智能

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

例如，只需在Power Pivot的会计数据表中添加上面的两个度量值，我只需点击几下就可以生成下面的数据透视表。

粒度不匹配

作为一名财务分析师，我经常要解决的一个问题是粒度不匹配。在我们的示例中，实际销售数据显示在类别级别上，但是让我们准备一个仅在季节级别上的预算。为了进一步扩大这种不匹配，我们将编制季度预算，尽管销售数据是每日的。

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

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

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

使用这种方法，我们可以在数据透视表中显示方差。

占总数的百分比

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

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

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

压缩

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

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

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

SSAS表格

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

4）数据可视化和分析

多维数据集公式

我的客户经常要求我创建符合严格定义的布局的报告。我的客户要求特定的列宽、RGB颜色代码以及预定义的字体名称和大小。考虑以下仪表板:

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

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

=CUBEVALUE（“此工作簿数据模型","【措施】。【总销售额】","【日历】。【年份】。[2016]")

公式的第一部分以黄色突出显示，是指Power Pivot模型的名称。一般来说，对于较新版本的Power Pivot for Excel，通常是此工作簿数据模型。绿色部分定义了我们要使用总销售额这一指标。蓝色部分指示Excel只筛选销售日期等于2016年的行。

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

功率BI

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

Power BI的一个伟大之处是自然语言问答。为了演示，我将Power BI模型上传到我的在线Power BI帐户上。在网站上，我可以提问，Power BI会在我输入时构建适当的分析:

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

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

最佳实践

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

首先是深思熟虑地决定首先进口什么。你会使用销售人员的家庭住址吗？在本工作簿中，我需要知道客户的电子邮件地址吗？如果目标是将数据汇总到仪表板中，那么一些可用的数据对于这些计算来说是不必要的。当数据集扩展时，花时间管理传入的数据将极大地缓解问题和内存使用。

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

最后，我建议对度量值使用简单的英文名称。这个花了我很长时间来适应。最初几年，我一直在编造类似的名字，但一旦其他人开始使用相同的工作簿，我就有很多解释要做。现在，当我开始一个新的工作簿时，我使用像。虽然名称较长，但对其他人来说更容易使用。

真实世界的用例

在本文中，我只介绍了Power Pivot for Excel允许您迈出超越普通Excel的重要一步的几种方式。我认为强调一些我发现Power Pivot for Excel非常有用的真实使用案例会很有用。

以下是一些例子:

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

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

作为财务分析师，我们需要在不断扩大的数据集上执行复杂的计算。由于Excel已经是默认的分析工具，Power Pivot学习曲线很容易，并且许多函数都反映了Excel的原生函数。

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

此外，我们现在可以分析比以前使用传统Excel大得多的数据集。通过高效设计的模型，我们可以轻松获得10倍于传统Excel的数据量，同时保持快速的分析灵活性。由于能够将模型从Power Pivot转换为SSAS表格，可以处理的数据量是我们在Excel中可以处理的数据量的100-1000倍。

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

如果您有兴趣尝试Power Pivot for Excel out，下面是一些有用的资料供您开始使用。

有用的参考和指南

柯利，r .，和辛格，a .（2016年）。Power Pivot和Power BI:Excel 2010-2016中的DAX、Power Query、Power BI和Power Pivot的Excel用户指南。美国:圣宏！书籍。

法拉利公司和鲁索公司（2015年）。DAX权威指南:使用Microsoft Excel、SQL Server Analysis Services和Power BI的商业智能。美国:美国微软出版社。