Excel Power Pivot 快速指南

Excel Power Pivot - 概述

Excel Power Pivot 是一个高效强大的工具，作为加载项随 Excel 一起提供。使用 Power Pivot，您可以从外部数据源加载数亿行数据，并使用其强大的 xVelocity 引擎以高度压缩的形式有效地管理数据。这使得执行计算、分析数据并得出报告以得出结论和决策成为可能。因此，具有 Excel 实践经验的人可以在几分钟内完成高端数据分析和决策。

本教程将涵盖以下内容：

Power Pivot 功能

Power Pivot 的强大之处在于其功能集。您将在“Power Pivot 功能”章节中学习各种 Power Pivot 功能。

来自各种来源的 Power Pivot 数据

Power Pivot 可以整理来自各种数据源的数据以执行所需的计算。您将在“将数据加载到 Power Pivot”章节中学习如何将数据导入 Power Pivot。

Power Pivot 数据模型

Power Pivot 的强大功能在于其数据库——数据模型。数据以数据表的形式存储在数据模型中。您可以创建数据表之间的关系，以组合来自不同数据表的数据以进行分析和报告。“理解数据模型（Power Pivot 数据库）”章节将详细介绍数据模型。

管理数据模型和关系

您需要了解如何管理数据模型中的数据表及其之间的关系。您将在“管理 Power Pivot 数据模型”章节中获得这些详细信息。

创建 Power Pivot 表和 Power Pivot 图表

Power Pivot 表和 Power Pivot 图表为您提供了一种分析数据以得出结论和/或决策的方法。

您将在“创建 Power Pivot 表”和“展平的 PivotTable”章节中学习如何创建 Power Pivot 表。

您将在“Power Pivot 图表”章节中学习如何创建 Power Pivot 图表。

DAX 基础

DAX 是 Power Pivot 中用于执行计算的语言。DAX 公式类似于 Excel 公式，但有一点不同——Excel 公式基于单个单元格，而 DAX 公式基于列（字段）。

您将在“DAX 基础”章节中了解 DAX 的基础知识。

探索和报告 Power Pivot 数据

您可以使用 Power Pivot 表和 Power Pivot 图表浏览数据模型中的 Power Pivot 数据。您将在这个教程中学习如何探索和报告数据。

层次结构

您可以在数据表中定义数据层次结构，以便于在 Power Pivot 表中一起处理相关数据字段。您将在“Power Pivot 中的层次结构”章节中学习创建和使用层次结构的详细信息。

美观的报表

您可以使用 Power Pivot 图表和/或 Power Pivot 图表创建美观的报表来展示您的数据分析结果。您可以使用多种格式化选项来突出显示报表中的重要数据。这些报表具有交互性，使查看简洁报表的使用者能够快速轻松地查看任何所需详细信息。

您将在“使用 Power Pivot 数据创建美观的报表”章节中学习这些细节。

Excel Power Pivot - 安装

Excel 中的 Power Pivot 提供了一个数据模型，该模型连接各种不同的数据源，基于这些数据源可以分析、可视化和探索数据。Power Pivot 提供的易于使用的界面使用户能够轻松地加载数据、将数据管理为数据表、创建数据表之间的关系以及执行所需的计算以生成报表。

在本节中，您将学习是什么让 Power Pivot 成为分析师和决策者梦寐以求的强大工具。

功能区上的 Power Pivot

使用 Power Pivot 的第一步是确保功能区上显示 POWERPIVOT 选项卡。如果您使用的是 Excel 2013 或更高版本，则 POWERPIVOT 选项卡会显示在功能区上。

如果您使用的是 Excel 2010，如果您尚未启用 Power Pivot 加载项，则功能区上可能不会显示POWERPIVOT选项卡。

Power Pivot 加载项

Power Pivot 加载项是一个 COM 加载项，需要启用它才能在 Excel 中获得 Power Pivot 的完整功能。即使 POWERPIVOT 选项卡显示在功能区上，您也需要确保已启用该加载项才能访问 Power Pivot 的所有功能。

步骤 1 - 单击功能区上的“文件”选项卡。

步骤 2 - 在下拉列表中单击“选项”。将出现“Excel 选项”对话框。

步骤 3 - 按如下说明操作。

单击“加载项”。
在“管理”框中，从下拉列表中选择“COM 加载项”。
单击“转到”按钮。将出现“COM 加载项”对话框。
选中“Power Pivot”，然后单击“确定”。

什么是 Power Pivot？

Excel Power Pivot 是一个用于集成和操作大量数据的工具。使用 Power Pivot，您可以轻松加载、排序和筛选包含数百万行的数据集，并执行所需的计算。您可以将 Power Pivot 用作临时报表和分析解决方案。

如下所示的 Power Pivot 功能区包含各种命令，从管理数据模型到创建报表。

Power Pivot 窗口将具有如下所示的功能区：

为什么 Power Pivot 是一个强大的工具？

当您调用 Power Pivot 时，Power Pivot 会创建数据定义和连接，这些定义和连接将以压缩形式存储在您的 Excel 文件中。当源数据更新时，它会自动在您的 Excel 文件中刷新。这有助于使用存储在其他地方但需要定期研究并据此做出决策的数据。源数据可以是任何形式——从文本文件或网页到不同的关系数据库。

Power Pivot 在 Power Pivot 窗口中的用户友好界面使您无需了解任何数据库查询语言即可执行数据操作。然后，您可以在几秒钟内创建分析报告。这些报表用途广泛、动态且交互性强，使您可以进一步深入研究数据以获得见解并得出结论/决策。

您在 Excel 和 Power Pivot 窗口中处理的数据存储在 Excel 工作簿中的分析数据库中，一个强大的本地引擎加载、查询和更新该数据库中的数据。由于数据位于 Excel 中，因此它可以直接用于数据透视表、数据透视图表、Power View 和您用于聚合和与数据交互的其他 Excel 功能。数据演示和交互性由 Excel 提供，数据和 Excel 演示对象包含在同一个工作簿文件中。Power Pivot 支持大小高达 2GB 的文件，并允许您在内存中使用高达 4GB 的数据。

Power Pivot 为 Excel 提供强大的功能

Power Pivot 功能在 Excel 中是免费的。Power Pivot 通过以下强大的功能增强了 Excel 的性能：

能够以惊人的速度处理大量数据，并将其压缩成小型文件。
在导入时筛选数据并重命名列和表。
将表组织到 Power Pivot 窗口中的各个选项卡页面中，而不是将 Excel 表分布在整个工作簿中或同一工作表中的多个表中。
创建表之间的关系，以便可以集体分析表中的数据。在 Power Pivot 之前，人们必须依赖大量使用 VLOOKUP 函数将数据组合到单个表中才能进行此类分析。这曾经是费力且容易出错的。
通过许多附加功能增强简单的透视表。
提供数据分析表达式 (DAX) 语言来编写高级公式。
向数据表添加计算字段和计算列。
创建可在数据透视表和 Power View 报表中使用的 KPI。

您将在下一节中详细了解 Power Pivot 功能。

Power Pivot 的用途

您可以将 Power Pivot 用于以下用途：

执行强大的数据分析并创建复杂的数据模型。
快速混合来自多个不同来源的大量数据。
执行信息分析并以交互方式共享见解。
使用数据分析表达式 (DAX) 语言编写高级公式。
创建关键绩效指标 (KPI)。

使用 Power Pivot 进行数据建模

Power Pivot 在 Excel 中提供高级数据建模功能。Power Pivot 中的数据在数据模型中进行管理，数据模型也称为 Power Pivot 数据库。您可以使用 Power Pivot 来帮助您获得对数据的新的见解。

您可以创建数据表之间的关系，以便您可以集体对表执行数据分析。使用 DAX，您可以编写高级公式。您可以在数据模型中的数据表中创建计算字段和计算列。

您可以在数据中定义层次结构，以便在整个工作簿（包括 Power View）中使用。您可以创建可在数据透视表和 Power View 报表中使用的 KPI，以一目了然地显示一个或多个指标的绩效是否达到目标。

使用 Power Pivot 进行商业智能

商业智能 (BI) 本质上是一组工具和流程，人们可以使用这些工具和流程来收集数据，将其转化为有意义的信息，然后做出更好的决策。Excel 中 Power Pivot 的 BI 功能使您可以收集数据、可视化数据以及与组织中的多人共享信息，跨多个设备。

您可以将工作簿共享到启用了 Excel Services 的 SharePoint 环境。在 SharePoint 服务器上，Excel Services 会在浏览器窗口中处理和呈现数据，以便其他人可以分析这些数据。

Excel Power Pivot - 功能

Power Pivot 最重要和最强大的功能是其数据库——数据模型。下一个重要的功能是 xVelocity 内存分析引擎，它使在几分钟内处理大型多数据库成为可能。PowerPivot 加载项还附带一些其他重要功能。

本章将简要概述 Power Pivot 的功能，这些功能将在后面详细说明。

从外部数据源加载数据

您可以通过两种方式从外部数据源加载数据到数据模型：

将数据加载到 Excel，然后创建 Power Pivot 数据模型。
直接将数据加载到 Power Pivot 数据模型。

由于 Power Pivot 以高效的方式处理内存中的数据，因此第二种方式更高效。

有关更多详细信息，请参阅“将数据加载到 Power Pivot”一章。

Excel 窗口和 Power Pivot 窗口

当您开始使用 Power Pivot 时，将同时打开两个窗口：Excel 窗口和 Power Pivot 窗口。您可以通过 Power Pivot 窗口直接将数据加载到数据模型中，在数据视图和图表视图中查看数据，创建表之间的关系，管理关系以及创建 Power PivotTable 和/或 PowerPivot 图表报表。

当您从外部数据源导入数据时，不需要在 Excel 表中拥有数据。如果工作簿中已有 Excel 表形式的数据，您可以将其添加到数据模型中，从而在数据模型中创建与 Excel 表链接的数据表。

当您从 Power Pivot 窗口创建数据透视表或数据透视图表时，它们将在 Excel 窗口中创建。但是，数据仍然由数据模型管理。

您可以随时轻松地在 Excel 窗口和 Power Pivot 窗口之间切换。

数据模型

数据模型是 Power Pivot 最强大的功能。从各种数据源获得的数据在数据模型中作为数据表维护。您可以创建数据表之间的关系，以便您可以组合表中的数据进行分析和报告。

您将在“了解数据模型（Power Pivot 数据库）”一章中详细了解数据模型。

内存优化

Power Pivot 数据模型使用 xVelocity 存储，在数据加载到内存时会进行高度压缩，这使得可以在内存中存储数亿行数据成为可能。

因此，如果您直接将数据加载到数据模型中，则将以高效的高度压缩形式进行加载。

紧凑的文件大小

如果数据直接加载到数据模型中，则保存 Excel 文件时，它在硬盘上占用的空间非常小。您可以比较 Excel 文件的大小，第一个是将数据加载到 Excel 中，然后创建数据模型；第二个是直接将数据加载到数据模型中，跳过第一步。第二个文件大小将比第一个文件小 10 倍。

Power Pivot 表

您可以从 Power Pivot 窗口创建 Power Pivot 表。创建的这些数据透视表基于数据模型中的数据表，从而可以组合相关表中的数据进行分析和报告。

Power Pivot 图表

您可以从 Power Pivot 窗口创建 Power Pivot 图表。创建的这些数据透视图表基于数据模型中的数据表，从而可以组合相关表中的数据进行分析和报告。Power Pivot 图表具有 Excel 数据透视图的所有功能以及更多功能，例如字段按钮。

您还可以组合 Power Pivot 表和 Power Pivot 图表。

DAX 语言

Power Pivot 的强大功能来自于 DAX 语言，它可以有效地用于数据模型上，对数据表中的数据执行计算。您可以拥有由 DAX 定义的计算列和计算字段，这些字段可用于 Power Pivot 表和 Power Pivot 图表。

Excel Power Pivot - 数据加载

在本章中，我们将学习如何将数据加载到 Power Pivot 中。

您可以通过两种方式将数据加载到 Power Pivot 中：

将数据加载到 Excel 并将其添加到数据模型
直接将数据加载到 PowerPivot 中，填充数据模型（即 PowerPivot 数据库）。

如果您想要 Power Pivot 的数据，请使用第二种方式，甚至无需 Excel 参与。这是因为您只需加载一次数据，并且以高度压缩的格式加载。为了理解差异的程度，假设您首先将数据添加到数据模型中，然后将其加载到 Excel 中，文件大小为 10 MB。

如果您将数据加载到 PowerPivot 中，然后跳过 Excel 的额外步骤加载到数据模型中，则文件大小可能只有 1 MB。

Power Pivot 支持的数据源

您可以将数据从各种数据源导入 Power Pivot 数据模型，也可以建立连接和/或使用现有连接。Power Pivot 支持以下数据源：

SQL Server 关系数据库
Microsoft Access 数据库
SQL Server Analysis Services
SQL Server Reporting Services (SQL 2008 R2)
ATOM 数据馈送
文本文件
Microsoft SQL Azure
Oracle
Teradata
Sybase
Informix
IBM DB2
对象链接与嵌入数据库/开放数据库连接
(OLEDB/ODBC) 源
Microsoft Excel 文件
文本文件

直接将数据加载到 PowerPivot

要直接将数据加载到 Power Pivot，请执行以下操作：

打开一个新工作簿。
单击功能区上的 POWERPIVOT 选项卡。
单击“数据模型”组中的“管理”。

PowerPivot 窗口打开。现在您有两个窗口：Excel 工作簿窗口和与您的工作簿连接的 PowerPivot for Excel 窗口。

单击 PowerPivot 窗口中的“主页”选项卡。
单击“获取外部数据”组中的“从数据库”。
选择“从 Access”。

将出现“表导入向导”。

浏览到 Access 数据库文件。
提供友好的连接名称。
如果数据库受密码保护，也请填写这些详细信息。

单击“下一步”→按钮。“表导入向导”将显示选择如何导入数据的选项。

单击“从表和视图列表中选择”以选择要导入的数据。

单击“下一步”→按钮。“表导入向导”将显示您已选择的 Access 数据库中的表和视图。

选中“奖牌”复选框。

您可以看到，您可以通过选中复选框来选择表，在添加到数据透视表之前预览和筛选表，以及选择相关表。

单击“预览和筛选”按钮。

您可以看到，您可以通过选中列标签中的复选框来选择特定列，通过单击列标签中的下拉箭头来筛选列，以选择要包含的值。

单击“确定”。
单击“选择相关表”按钮。
如果存在关系，Power Pivot 将检查哪些其他表与选定的“奖牌”表相关。

您可以看到 Power Pivot 发现“学科”表与“奖牌”表相关并将其选中。单击“完成”。

“表导入向导”显示“正在导入”并显示导入状态。这将需要几分钟时间，您可以通过单击“停止导入”按钮来停止导入。

导入数据后，“表导入向导”将显示“成功”并显示导入结果（如下面的屏幕截图所示）。单击“关闭”。

Power Pivot 在两个选项卡中显示两个导入的表。

您可以使用选项卡下方的“记录”箭头滚动浏览记录（表的行）。

表导入向导

在上一节中，您学习了如何通过“表导入向导”从 Access 导入数据。

请注意，“表导入向导”选项会根据选择的连接到的数据源而变化。您可能想知道您可以从哪些数据源中选择。

在 Power Pivot 窗口中单击“从其他数据源”。

将出现“表导入向导” – “连接到数据源”。您可以创建到数据源的连接，也可以使用已存在的连接。

您可以滚动浏览“导入表向导”中的连接列表，以了解与 Power Pivot 兼容的数据连接。

向下滚动到“文本文件”。
选择“Excel 文件”。

单击“下一步”→按钮。“表导入向导”显示“连接到 Microsoft Excel 文件”。
在“Excel 文件路径”框中浏览到 Excel 文件。
选中“使用第一行作为列标题”复选框。

单击“下一步”→按钮。“表导入向导”显示“选择表和视图”。
选中“产品目录$”复选框。单击“完成”按钮。

您将看到以下“成功”消息。单击“关闭”。

您已导入一个表，并且还创建了与包含其他几个表的 Excel 文件的连接。

打开现有连接

建立到数据源的连接后，您可以稍后打开它。

在 PowerPivot 窗口中单击“现有连接”。

将出现“现有连接”对话框。从列表中选择“Excel 销售数据”。

单击“打开”按钮。将出现“表导入向导”，显示表和视图。

选择要导入的表，然后单击“完成”。

将导入所选的五个表。单击“关闭”。

您可以看到五个表已添加到 Power Pivot 中，每个表都在一个新的选项卡中。

创建链接表

链接表是 Excel 中的表和数据模型中的表之间的实时链接。对 Excel 中表的更新会自动更新模型中数据表中的数据。

您可以按照以下步骤将 Excel 表链接到 Power Pivot：

使用数据创建一个 Excel 表。
单击功能区上的 POWERPIVOT 选项卡。
单击“表”组中的“添加到数据模型”。

Excel 表将链接到 PowerPivot 中相应的数据库表。

您可以看到，带有“链接表”选项卡的“表工具”已添加到 Power Pivot 窗口中。如果您单击“转到 Excel 表”，您将切换到 Excel 工作表。如果您单击“管理”，您将切换回 Power Pivot 窗口中的链接表。

您可以自动或手动更新链接表。

请注意，只有当 Excel 表存在于包含 Power Pivot 的工作簿中时，才能链接该表。如果您的 Excel 表位于单独的工作簿中，则必须按照下一节中的说明加载它们。

从 Excel 文件加载数据

如果要从 Excel 工作簿加载数据，请记住以下几点：

Power Pivot 将其他 Excel 工作簿视为数据库，并且只导入工作表。
Power Pivot 将每个工作表加载为一个表。
Power Pivot 无法识别单个表。因此，Power Pivot 无法识别工作表上是否存在多个表。
Power Pivot 无法识别工作表上表以外的任何其他信息。

因此，请将每个表放在单独的工作表中。

准备好工作簿中的数据后，您可以按如下步骤导入数据：

在 Power Pivot 窗口中，单击“获取外部数据”组中的**“来自其他来源”**。
按照“表导入向导”部分中的说明进行操作。

以下是链接的 Excel 表和导入的 Excel 表之间的区别：

链接的表必须位于存储 Power Pivot 数据库的同一 Excel 工作簿中。如果数据已存在于其他 Excel 工作簿中，则使用此功能毫无意义。
Excel 导入功能允许您从不同的 Excel 工作簿加载数据。
从 Excel 工作簿加载数据不会在两个文件之间创建链接。Power Pivot 导入时仅创建数据的副本。
更新原始 Excel 文件后，Power Pivot 中的数据不会刷新。您需要将更新模式设置为自动，或在 Power Pivot 窗口的“链接表”选项卡中手动更新数据。

从文本文件加载数据

一种流行的数据表示样式是逗号分隔值 (csv) 格式。每行数据/记录都由一行文本表示，其中列/字段用逗号分隔。许多数据库都提供保存为 csv 格式文件的选项。

如果要将 csv 文件加载到 Power Pivot 中，则必须使用“文本文件”选项。假设您有以下 csv 格式的文本文件：

单击 PowerPivot 选项卡。
在 PowerPivot 窗口中单击“开始”选项卡。
单击“获取外部数据”组中的**“来自其他来源”**。将出现“表导入向导”。
向下滚动到“文本文件”。

单击“文本文件”。
单击**“下一步”**→按钮。“表导入向导”将显示“连接到平面文件”。
在“文件路径”框中浏览到文本文件。csv 文件通常以第一行表示列标题。
如果第一行有标题，请选中“使用第一行作为列标题”框。
在“列分隔符”框中，默认值为逗号 (,)，但如果您的文本文件使用其他运算符，例如制表符、分号、空格、冒号或竖线，请选择该运算符。

您可以看到数据表的预览。单击“完成”。

Power Pivot 将在数据模型中创建数据表。

从剪贴板加载数据

假设您在 Power Pivot 未将其识别为数据源的应用程序中拥有数据。要将这些数据加载到 Power Pivot 中，您有两个选项：

将数据复制到 Excel 文件，并将 Excel 文件用作 Power Pivot 的数据源。
复制数据，使其位于剪贴板中，然后将其粘贴到 Power Pivot 中。

您已经在前面的小节中学习了第一个选项。正如您在本节末尾将看到的，此选项优于第二个选项。但是，您应该知道如何将数据从剪贴板复制到 Power Pivot 中。

假设您在 Word 文档中拥有以下数据：

Word 不是 Power Pivot 的数据源。因此，请执行以下操作：

选择 Word 文档中的表。
将其复制并粘贴到 PowerPivot 窗口中。

将出现**“粘贴预览”**对话框。

将其命名为**“Word-员工表”**。
选中“使用第一行作为列标题”框，然后单击“确定”。

复制到剪贴板中的数据将粘贴到 Power Pivot 中的新数据表中，选项卡名为“Word-员工表”。

假设您想用新内容替换此表。

从 Word 中复制该表。
单击“粘贴替换”。

将出现“粘贴预览”对话框。验证您用于替换的内容。

单击“确定”。

您可以看到，Power Pivot 中的数据表内容已替换为剪贴板中的内容。

假设您想向数据表添加两行新数据。在 Word 文档中的表中，您有两行新数据。

选择这两行新数据。
单击“复制”。
在 Power Pivot 窗口中单击**“粘贴追加”**。“粘贴预览”对话框将出现。
验证您用于追加的内容。

单击“确定”继续。

您可以看到，Power Pivot 中的数据表内容已追加了剪贴板中的内容。

在本节开头，我们提到将数据复制到 Excel 文件并使用链接表比从剪贴板复制更好。

这是因为以下原因：

如果您使用链接表，您就知道数据的来源。另一方面，以后您将不知道数据的来源，或者不知道它是否被其他人使用。
您在 Word 文件中拥有跟踪信息，例如数据何时被替换以及数据何时被追加。但是，无法将这些信息复制到 Power Pivot。如果您先将数据复制到 Excel 文件，则可以保留这些信息以供以后使用。
从剪贴板复制时，如果要添加一些注释，则无法这样做。如果您先复制到 Excel 文件，则可以在 Excel 表中插入与 Power Pivot 链接的注释。
无法刷新从剪贴板复制的数据。如果数据来自链接表，则始终可以确保数据已更新。

刷新 Power Pivot 中的数据

您可以随时刷新从外部数据源导入的数据。

如果只想刷新 Power Pivot 中的一个数据表，请执行以下操作：

单击数据表选项卡。
单击“刷新”。
从下拉列表中选择“刷新”。

如果要刷新 Power Pivot 中的所有数据表，请执行以下操作：

单击“刷新”按钮。
从下拉列表中选择“全部刷新”。

Excel Power Pivot - 数据模型

数据模型是 Excel 2013 中引入的一种新方法，用于集成来自多个表的数据，有效地在 Excel 工作簿内构建关系数据源。在 Excel 中，数据模型以透明的方式使用，提供在数据透视表和数据透视图中使用的表格数据。在 Excel 中，您可以通过包含表名和相应字段的数据透视表/数据透视图字段列表访问表及其对应的值。

数据模型在 Excel 中的主要用途是 Power Pivot 的使用。数据模型可以视为 Power Pivot 数据库，Power Pivot 的所有强大功能都由数据模型管理。所有 Power Pivot 数据操作都是显式的，并且可以在数据模型中可视化。

在本章中，您将详细了解数据模型。

Excel 和数据模型

Excel 工作簿中只有一个数据模型。当您使用 Excel 时，数据模型的使用是隐式的。您无法直接访问数据模型。您只能在数据透视表或数据透视图的字段列表中看到数据模型中的多个表并使用它们。创建数据模型和添加数据也是在 Excel 中隐式完成的，同时您正在将外部数据导入 Excel。

如果要查看数据模型，可以按如下步骤操作：

单击功能区上的 POWERPIVOT 选项卡。
单击“管理”。

如果工作簿中存在数据模型，则会将其显示为表，每个表都带有一个选项卡。

**注意** - 如果您将 Excel 表添加到数据模型，您不会将 Excel 表转换为数据表。Excel 表的副本将作为数据表添加到数据模型中，并且会在两者之间创建一个链接。因此，如果对 Excel 表进行了更改，数据表也会更新。但是，从存储的角度来看，存在两个表。

Power Pivot 和数据模型

数据模型本质上是 Power Pivot 的数据库。即使您从 Excel 创建数据模型，它也只会构建 Power Pivot 数据库。创建数据模型和/或添加数据是在 Power Pivot 中显式完成的。

实际上，您可以从 Power Pivot 窗口管理数据模型。您可以向数据模型添加数据，从不同的数据源导入数据，查看数据模型，创建表之间的关系，创建计算字段和计算列等。

创建数据模型

您可以将表从 Excel 添加到数据模型，也可以直接将数据导入 Power Pivot，从而创建 Power Pivot 数据模型表。您可以通过单击 Power Pivot 窗口中的“管理”来查看数据模型。

您将在“通过 Excel 加载数据”一章中了解如何将表从 Excel 添加到数据模型。您将在“将数据加载到 Power Pivot”一章中了解如何将数据加载到数据模型中。

数据模型中的表

数据模型中的表可以定义为一组保持它们之间关系的表。这些关系能够组合来自不同表的关系数据以进行分析和报告。

数据模型中的表称为数据表。

数据模型中的表被认为是一组记录（记录是行），由字段（字段是列）组成。您无法编辑数据表中的单个项目。但是，您可以追加行或向数据表添加计算列。

Excel 表和数据表

Excel 表只是一组单独的表。工作表上可以有多个表。可以分别访问每个表，但是不可能同时访问多个 Excel 表中的数据。这就是为什么创建数据透视表时，它仅基于一个表的原因。如果需要集体使用两个 Excel 表中的数据，则需要先将它们合并到单个 Excel 表中。

另一方面，数据表与其他具有关系的数据表共存，从而促进了组合来自多个表的数据。当您将数据导入 Power Pivot 时，将创建数据表。您还可以在创建数据透视表时将 Excel 表添加到数据模型，获取外部数据或来自多个表的数据。

数据模型中的数据表可以通过两种方式查看：

数据视图。
图表视图。

数据模型的数据视图

在数据模型的数据视图中，每个数据表都位于单独的选项卡上。数据表行是记录，列表示字段。选项卡包含表名，列标题是该表中的字段。您可以使用数据分析表达式 (DAX) 语言在数据视图中进行计算。

数据模型的图表视图

在数据模型的图表视图中，所有数据表都由带有表名的框表示，并包含表中的字段。您可以通过拖动来排列图表视图中的表。您可以调整数据表的大小，以便显示表中的所有字段。

数据模型中的关系

您可以在图表视图中查看关系。如果两个表之间定义了关系，则会显示一条连接源表和目标表的箭头。如果想知道关系中使用了哪些字段，只需双击箭头即可。箭头和两个表中的两个字段将被高亮显示。

如果您导入具有主键和外键关系的相关表，则会自动创建表关系。Excel 可以使用导入的关系信息作为数据模型中表关系的基础。

您也可以在以下两种视图中显式创建关系：

数据视图 - 使用“创建关系”对话框。
图表视图 - 通过单击并拖动来连接两个表。

“创建关系”对话框

在一个关系中，涉及四个实体：

表 - 关系起始的数据表。
列 - 表中也存在于相关表中的字段。
相关表 - 关系结束的数据表。
相关列 - 相关表中与表中“列”字段相同的字段。请注意，“相关列”的值应唯一。

在图表视图中，您可以通过单击表中的字段并将其拖动到相关表来创建关系。

您将在“使用 Power Pivot 管理数据表和关系”一章中了解更多关于关系的信息。

Excel Power Pivot - 数据模型管理

Power Pivot 的主要用途是它能够管理数据表及其之间的关系，以便于对来自多个表的数据进行分析。您可以在创建数据透视表时或直接从 Power Pivot 功能区添加 Excel 表到数据模型。

只有在表之间存在关系时，才能分析来自多个表的数据。使用 Power Pivot，您可以从数据视图或图表视图创建关系。此外，如果您选择将表添加到 Power Pivot，则也需要添加关系。

使用数据透视表将 Excel 表添加到数据模型

在 Excel 中创建数据透视表时，它仅基于单个表/区域。如果您想向数据透视表添加更多表，可以使用数据模型。

假设您的工作簿中有两个工作表：

一个包含销售人员及其代表的区域的数据，在一个名为“销售人员”的表中。
另一个包含按销售额、区域和月份分类的数据，在一个名为“销售额”的表中。

您可以按如下所示汇总按销售人员分类的销售额。

单击“销售额”表。
单击功能区上的“插入”选项卡。
在“表格”组中选择“数据透视表”。

将创建一个空的数据透视表，其中包含来自“销售额”表的字段：区域、月份和订单金额。您可以看到，数据透视表字段列表下方有一个更多表格命令。

单击“更多表格”。

将出现创建新的数据透视表消息框。显示的消息是：要在分析中使用多个表，需要使用数据模型创建新的数据透视表。单击“是”。

将创建一个新的数据透视表，如下所示：

在“数据透视表字段”下，您可以看到两个选项卡：活动和全部。

单击“全部”选项卡。
数据透视表字段列表中将出现两个表 - “销售额”和“销售人员”，以及相应的字段。
单击“销售人员”表中的“销售人员”字段，并将其拖动到“行”区域。
单击“销售额”表中的“月份”字段，并将其拖动到“行”区域。
单击“销售额”表中的“订单金额”字段，并将其拖动到“∑ 值”区域。

数据透视表已创建。数据透视表字段中将显示一条消息：可能需要表之间的关系。

单击消息旁边的“创建”按钮。“创建关系”对话框将出现。

在表下，选择“销售额”。
在列（外键）框中，选择“区域”。
在相关表下，选择“销售人员”。
在相关列（主键）框中，选择“区域”。
单击“确定”。

您来自两个工作表上的两个表的数据透视表已准备就绪。

此外，正如 Excel 在将第二个表添加到数据透视表时所述，数据透视表是使用数据模型创建的。要验证，请执行以下操作：

单击功能区上的 POWERPIVOT 选项卡。
单击数据模型组中的管理。将出现 Power Pivot 的数据视图。

您可以看到，您在创建数据透视表时使用的两个 Excel 表已转换为数据模型中的数据表。

将来自不同工作簿的 Excel 表添加到数据模型

假设“销售人员”和“销售额”这两个表位于两个不同的工作簿中。

您可以按如下方式将来自不同工作簿的 Excel 表添加到数据模型：

单击“销售额”表。
单击“插入”选项卡。
在“表格”组中单击“数据透视表”。将出现创建数据透视表对话框。

在“表/区域”框中，键入“销售额”。
单击“新工作表”。
选中“将此数据添加到数据模型”复选框。
单击“确定”。

您将在新工作表上获得一个空的数据透视表，其中只有与“销售额”表对应的字段。

您已将“销售额”表数据添加到数据模型。接下来，您必须按如下方式将“销售人员”表数据也添加到数据模型：

单击包含“销售额”表的工作表。
单击功能区上的“数据”选项卡。
在“获取外部数据”组中单击“现有连接”。将出现“现有连接”对话框。
单击“表”选项卡。

在此工作簿数据模型，1 个表下显示（这是您之前添加的“销售额”表）。您还会找到显示其中表的两个工作簿。

单击“Salesperson.xlsx”下的“销售人员”。
单击“打开”。将出现导入数据对话框。
单击“数据透视表报表”。
单击“新工作表”。

您可以看到“将此数据添加到数据模型”复选框处于选中且不可用状态。单击“确定”。

将创建数据透视表。

您可以看到这两个表都在数据模型中。您可能需要像上一节一样创建这两个表之间的关系。

从 Power Pivot 功能区将 Excel 表添加到数据模型

将 Excel 表添加到数据模型的另一种方法是从Power Pivot 功能区进行操作。

假设您的工作簿中有两个工作表：

一个包含销售人员及其代表的区域的数据，在一个名为“销售人员”的表中。
另一个包含按销售额、区域和月份分类的数据，在一个名为“销售额”的表中。

您可以在进行任何分析之前先将这些 Excel 表添加到数据模型。

单击 Excel 表“销售额”。
单击功能区上的 POWERPIVOT 选项卡。
单击“表格”组中的“添加到数据模型”。

Power Pivot 窗口出现，其中添加了数据表“销售人员”。此外，在 Power Pivot 窗口的功能区中将出现一个选项卡 - “链接表”。

单击功能区上的“链接表”选项卡。
单击“Excel 表：销售人员”。

您可以发现工作簿中显示的两个表的名称，并且“销售人员”名称已勾选。这意味着数据表“销售人员”已链接到 Excel 表“销售人员”。

单击转到 Excel 表。

将出现包含“销售人员”表的工作表 Excel 窗口。

单击“销售额”工作表选项卡。
单击“销售额”表。
单击功能区上的“表格”组中的“添加到数据模型”。

Excel 表“销售额”也已添加到数据模型。

如果您想基于这两个表进行分析，正如您所知，您需要在这两个数据表之间创建关系。在 Power Pivot 中，您可以通过两种方式执行此操作：

从数据视图
从图表视图

从数据视图创建关系

您知道在数据视图中，您可以查看以记录作为行和字段作为列的数据表。

单击 Power Pivot 窗口中的“设计”选项卡。
单击“关系”组中的“创建关系”。将出现创建关系对话框。

单击“表”框中的“销售额”。这是关系起始的表。正如您所知，“列”应该是存在于相关表“销售人员”中并包含唯一值的字段。
单击“列”框中的“区域”。
单击“相关链接表”框中的“销售人员”。

“相关链接列”将自动填充为“区域”。

单击“创建”按钮。关系已创建。

从图表视图创建关系

从图表视图创建关系相对容易。请按照以下步骤操作。

单击 Power Pivot 窗口中的“主页”选项卡。
单击“视图”组中的“图表视图”。

数据模型的图表视图将出现在 Power Pivot 窗口中。

单击“销售额”表中的“区域”。“销售额”表中的“区域”将高亮显示。
拖动到“销售人员”表中的“区域”。“销售人员”表中的“区域”也将高亮显示。将出现一条箭头指向您拖动方向的线。
将出现一条从“销售额”表到“销售人员”表的线，表示关系。

您可以看到，从“销售额”表到“销售人员”表出现了一条线，指示关系和方向。

如果想知道属于关系的字段，请单击关系线。线和两个表中的字段将高亮显示。

管理关系

您可以编辑或删除数据模型中现有的关系。

单击 Power Pivot 窗口中的“设计”选项卡。
单击“关系”组中的“管理关系”。将出现“管理关系”对话框。

将显示数据模型中存在的所有关系。

要编辑关系

单击关系。
单击编辑按钮。将出现编辑关系对话框。

对关系进行必要的更改。
单击“确定”。更改将反映在关系中。

要删除关系

单击关系。
单击“删除”按钮。将出现一条警告消息，显示删除关系会如何影响受影响的表以及相应的报表。
如果您确定要删除，请单击“确定”。选定的关系将被删除。

刷新 Power Pivot 数据

假设您修改了 Excel 表中的数据。您可以添加/更改/删除 Excel 表中的数据。

要刷新 Power Pivot 数据，请执行以下操作：

单击 Power Pivot 窗口中的“链接表”选项卡。
单击“全部更新”。

数据表将使用在 Excel 表中进行的修改进行更新。

您可以看到，不能直接修改数据表中的数据。因此，最好在将 Excel 表添加到数据模型时将数据保存在链接到数据表的 Excel 表中。这有助于在更新 Excel 表中的数据时更新数据表中的数据。

Excel Power Pivot 表 - 创建

Power PivotTable 基于 Power Pivot 数据库，该数据库称为数据模型。您已经学习了数据模型的强大功能。Power Pivot 的强大之处在于它能够在 Power PivotTable 中汇总来自数据模型的数据。您知道，数据模型可以处理包含数百万行且来自各种输入的大量数据。这使得 Power PivotTable 能够在几分钟内汇总来自任何位置的数据。

Power PivotTable 在布局上类似于数据透视表，但存在以下区别：

数据透视表基于 Excel 表，而 Power PivotTable 基于数据模型中的一部分数据表。
数据透视表基于单个 Excel 表或数据区域，而 Power PivotTable 可以基于多个数据表，前提是这些数据表已添加到数据模型中。
数据透视表是在 Excel 窗口中创建的，而 Power PivotTable 是在 Power Pivot 窗口中创建的。

创建 Power PivotTable

假设数据模型中有两个数据表：销售人员和销售额。要从这两个数据表创建 Power Pivot 表，请按照以下步骤操作：

在 Power Pivot 窗口的“功能区”中单击“开始”选项卡。
单击功能区上的“数据透视表”。
从下拉列表中选择“数据透视表”。

出现“创建数据透视表”对话框。您可以看到，这是一个简单的对话框，没有任何关于数据的问题。这是因为 Power PivotTable 始终基于数据模型，即定义了它们之间关系的数据表。

选择“新建工作表”，然后单击“确定”。

在 Excel 窗口中创建一个新的工作表，并出现一个空的数据透视表。

您可以看到，Power PivotTable 的布局与数据透视表的布局相似。“数据透视表工具”出现在功能区上，其中包含“分析”和“设计”选项卡，与数据透视表相同。

数据透视表字段列表出现在工作表的右侧。在这里，您会发现与数据透视表的一些不同之处。

Power PivotTable 字段

数据透视表字段列表有两个选项卡：在标题下方和字段列表上方显示的“活动”和“全部”。“全部”选项卡突出显示。

请注意，“全部”选项卡显示数据模型中的所有数据表，“活动”选项卡显示当前 Power PivotTable 中选择的所有数据表。由于 Power PivotTable 为空，这意味着尚未选择任何数据表；因此，默认情况下选择“全部”选项卡，并显示当前数据模型中的两个表。此时，如果单击“活动”选项卡，字段列表将为空。

单击“全部”下数据透视表字段列表中的表名。相应的字段将带有复选框出现。
每个表名左侧都会显示符号。
如果将光标放在此符号上，将显示该数据表的“数据源”和“模型表名”。

将“销售人员”从“销售人员”表拖到“行”区域。
单击“活动”选项卡。

您可以看到，“销售人员”字段出现在数据透视表中，“销售人员”表按预期出现在“活动”选项卡下。

单击“全部”选项卡。
单击“销售额”表中的“月份”和“订单金额”。

再次单击“活动”选项卡。“销售额”和“销售人员”两个表都出现在“活动”选项卡下。

将“月份”拖到“列”区域。
将“区域”拖到“筛选器”区域。

单击“区域”筛选器框中“全部”旁边的箭头。
单击“选择多个项目”。
选择“北部”和“南部”，然后单击“确定”。

按升序对列标签进行排序。

Power PivotTable 可以动态修改以探索和报告数据。

Excel Power Pivot - DAX 基础

**DAX（数据分析表达式）**是 Power Pivot 的语言。Power Pivot 使用 DAX 进行数据建模，方便您进行自助式 BI。DAX 基于数据表和数据表中的列。请注意，它不基于表中的单个单元格，就像 Excel 中的公式和函数一样。

您将在本章中学习数据模型中存在的两个简单计算：计算列和计算字段。

计算列

计算列是数据模型中由计算定义并扩展数据表内容的列。它可以被视为由公式定义的 Excel 表中的新列。

使用计算列扩展数据模型

假设您在数据表中按区域拥有产品的销售数据，以及数据模型中的产品目录。

使用此数据创建 Power PivotTable。

您可以看到，Power PivotTable 已汇总了所有区域的销售数据。假设您想知道每个产品的毛利润。您知道每个产品的价格、销售成本和销售数量。

但是，如果您需要计算毛利润，则需要在每个区域的数据表中再添加两列：总产品价格和毛利润。这是因为数据透视表需要数据表中的列来汇总结果。

您知道，总产品价格 = 产品价格 * 数量，毛利润 = 总金额 - 总产品价格。

您需要使用 DAX 表达式添加计算列，如下所示：

单击 Power Pivot 窗口“数据视图”中的“East_Sales”选项卡以查看“East_Sales”数据表。
单击功能区上的“设计”选项卡。
单击“添加”。

右侧带有标题“添加列”的列突出显示。

在公式栏中键入 = **[产品价格] * [数量]**，然后按 **Enter**。

插入一个新的列，其标题为 **CalculatedColumn1**，其中包含您输入的公式计算的值。

双击新计算列的标题。
将标题重命名为 **总产品价格**。

再添加一个计算列用于计算毛利润，如下所示：

单击功能区上的“设计”选项卡。
单击“添加”。
右侧带有标题“添加列”的列突出显示。
在公式栏中键入 = **[总销售额] - [总产品价格]**。
按 Enter。

插入一个新的列，其标题为 **CalculatedColumn1**，其中包含您输入的公式计算的值。

双击新计算列的标题。
将标题重命名为毛利润。

以类似的方式在 **North_Sales** 数据表中添加计算列。整合所有步骤，请按照以下步骤操作：

单击功能区上的“设计”选项卡。
单击“添加”。右侧带有标题“添加列”的列突出显示。
在公式栏中键入 = **[产品价格] * [数量]**，然后按 Enter。
插入一个新的列，其标题为 CalculatedColumn1，其中包含您输入的公式计算的值。
双击新计算列的标题。
将标题重命名为 **总产品价格**。
单击功能区上的“设计”选项卡。
单击“添加”。右侧带有标题“添加列”的列突出显示。
在公式栏中键入 = **[总销售额] - [总产品价格]**，然后按 Enter。插入一个新的列，其标题为 **CalculatedColumn1**，其中包含您输入的公式计算的值。
双击新计算列的标题。
将标题重命名为 **毛利润**。

对“South Sales”数据表和“West Sales”数据表重复上述步骤。

您拥有汇总毛利润所需的列。现在，创建 Power PivotTable。

您可以汇总 Power Pivot 中计算列生成的 **毛利润**，所有这些都可以在几个无错误的步骤中完成。

您还可以按如下所示按区域对产品进行汇总：

计算字段

假设您想计算每个区域按产品计算的利润百分比。您可以通过向数据表添加计算字段来做到这一点。

单击 Power Pivot 窗口中 **East_Sales** 表中“毛利润”列下方。
在公式栏中键入 **EastProfit: = SUM([毛利润]) / SUM([总销售额])**。
按 Enter。

计算字段 EastProfit 插入到“毛利润”列下方。

右键单击计算字段 - EastProfit。
从下拉列表中选择“格式”。

出现“格式”对话框。

在“类别”下选择“数字”。
在“格式”框中，选择“百分比”，然后单击“确定”。

计算字段 EastProfit 的格式设置为百分比。

重复这些步骤以插入以下计算字段：

在 North_Sales 数据表中插入 NorthProfit。
在 South_Sales 数据表中插入 SouthProfit。
在 West_Sales 数据表中插入 WestProfit。

**注意** - 您不能使用给定的名称定义多个计算字段。

单击 Power PivotTable。您可以看到计算字段出现在表中。

从数据透视表字段列表中的表中选择字段 - EastProfit、NorthProfit、SouthProfit 和 WestProfit。
排列字段，使毛利润和利润百分比一起出现。Power PivotTable 如下所示：

**注意** - 在早期版本的 Excel 中，“计算字段”称为“度量值”。

Excel Power Pivot - 数据探索

在上一章中，您学习了如何从一组普通数据表创建 Power PivotTable。本章将学习如何在数据表包含数千行时使用 Power PivotTable 探索数据。

为了更好地理解，我们将从 Access 数据库导入数据，您知道这是一个关系数据库。

从 Access 数据库加载数据

要从 Access 数据库加载数据，请按照以下步骤操作：

在 Excel 中打开一个新的空白工作簿。
单击“数据模型”组中的“管理”。
单击功能区上的 POWERPIVOT 选项卡。

出现 Power Pivot 窗口。

单击 Power Pivot 窗口中的“主页”选项卡。
单击“获取外部数据”组中的“从数据库”。
从下拉列表中选择“从 Access”。

将出现“表导入向导”。

提供“友好连接”名称。
浏览到 Access 数据库文件 Events.accdb（Events 数据库文件）。
单击“下一步 >”按钮。

“表导入”向导显示用于选择如何导入数据的选项。

单击“从表和视图列表中选择要导入的数据”，然后单击“下一步”。

“表导入”向导显示您选择的所有 Access 数据库中的所有表。选中所有复选框以选择所有表，然后单击“完成”。

“表导入”向导显示 - “正在导入”，并显示导入状态。这可能需要几分钟时间，您可以单击“停止导入”按钮停止导入。

数据导入完成后，“表导入”向导显示 - “成功”，并显示导入结果。单击“关闭”。

Power Pivot 在“数据视图”的不同选项卡中显示所有导入的表。

单击“图表视图”。

您可以看到表之间存在关系 - **学科和奖牌**。这是因为，当您从关系数据库（如 Access）导入数据时，数据库中存在的关联也会导入到 Power Pivot 中的数据模型中。

从数据模型创建数据透视表

使用上一节中导入的表创建数据透视表，如下所示：

单击功能区上的“数据透视表”。
从下拉列表中选择“数据透视表”。
在出现的“创建数据透视表”对话框中选择“新建工作表”，然后单击“确定”。

在 Excel 窗口的新工作表中创建一个空的透视表。

Power Pivot 数据模型中包含的所有导入表都显示在透视表字段列表中。

将“奖牌”表中的 **NOC_CountryRegion** 字段拖动到“列”区域。
将“项目”表中的“项目名称”字段拖动到“行”区域。
筛选“项目名称”，仅显示五项运动：射箭、跳水、击剑、花样滑冰和速度滑冰。这可以通过透视表字段区域或透视表本身的行标签筛选器来完成。
将“奖牌”表中的“奖牌”字段拖动到“值”区域。
再次选择“奖牌”表中的“奖牌”，并将其拖动到“筛选器”区域。

透视表已填充添加的字段，并采用从各个区域选择的布局。

使用透视表浏览数据

您可能只想显示奖牌数 > 80 的值。要执行此操作，请按照以下步骤操作：

单击列标签右侧的箭头。
从下拉列表中选择 **值筛选器**。
从第二个下拉列表中选择 **大于…**。
单击“确定”。

将出现 **值筛选器** 对话框。在最右边的框中键入 80，然后单击“确定”。

透视表仅显示奖牌总数超过 80 个的地区。

您可以通过几个简单的步骤从不同的表中获得所需的特定报表。这之所以成为可能，是因为 Access 数据库中的表之间存在预先存在的关系。由于您同时从数据库中导入了所有表，因此 Power Pivot 在其数据模型中重新创建了这些关系。

汇总 Power Pivot 中不同来源的数据

如果您从不同的数据源获取数据表，或者您没有同时从数据库导入表，或者您在工作簿中创建新的 Excel 表并将其添加到数据模型，则必须创建要在透视表中用于分析和汇总的表之间的关系。

在工作簿中创建一个新工作表。
创建一个 Excel 表 - “项目”。

将“项目”表添加到数据模型。

使用 **SportID** 字段在 **项目** 和 **运动** 表之间创建关系。

将 **运动** 字段添加到透视表。

在“行”区域中重新排列 **项目名称** 和 **运动** 字段。

扩展数据浏览

您还可以将 **赛事** 表纳入进一步的数据浏览。

使用 **DisciplineEvent** 字段在 **赛事** 和 **奖牌** 表之间创建关系。

将 **举办地** 表添加到工作簿和数据模型。

使用计算列扩展数据模型

要将“举办地”表连接到任何其他表，它应该包含一个字段，该字段的值唯一地标识“举办地”表中的每一行。由于“举办地”表中不存在此类字段，因此您可以在“举办地”表中创建一个计算列，使其包含唯一值。

在 PowerPivot 窗口的数据视图中转到“举办地”表。
单击功能区上的“设计”选项卡。
单击“添加”。

标题为“添加列”的最右边列突出显示。

在公式栏中键入以下 DAX 公式：**= CONCATENATE([版本], [季节])**
按 Enter。

将创建一个新的列，其标题为 **CalculatedColumn1**，并且该列将由上述 DAX 公式产生的值填充。

右键单击新列，然后从下拉列表中选择“重命名列”。

在新列的标题中键入 **EditionID**。

如您所见，“举办地”表中的 **EditionID** 列包含唯一值。

使用计算列创建关系

如果您必须在 **举办地** 表和 **奖牌** 表之间创建关系，则 **EditionID** 列也应存在于“奖牌”表中。请按照以下步骤在“奖牌”表中创建计算列：

在 Power Pivot 的数据视图中单击“奖牌”表。
单击功能区上的“设计”选项卡。
单击“添加”。

在公式栏中键入 DAX 公式 **= YEAR([版本])** 并按 Enter 键。

将创建的新列重命名为“年份”，然后单击“添加”。

在公式栏中键入以下 DAX 公式：**= CONCATENATE([年份], [季节])**
将创建的新列重命名为 **EditionID**。

您可以观察到，“奖牌”表中的 EditionID 列与“举办地”表中的 EditionID 列具有相同的值。因此，您可以使用 EditionID 字段在“奖牌”和“运动”表之间创建关系。

切换到 PowerPivot 窗口中的图表视图。
使用从计算列获得的字段（即 **EditionID**）在“奖牌”和“举办地”表之间创建关系。

现在您可以将“举办地”表中的字段添加到 Power Pivot 表中。

Excel Power Pivot - 展平

当数据有多个级别时，有时读取透视表报表会变得很麻烦。

例如，考虑以下数据模型。

我们将创建一个 Power Pivot 表和一个 Power 展平透视表，以了解其布局。

创建透视表

您可以按照以下步骤创建 Power Pivot 表：

单击 PowerPivot 窗口中功能区的“开始”选项卡。
单击“透视表”。
从下拉列表中选择“数据透视表”。

将创建一个空的透视表。

将字段 - 销售人员、地区和产品从透视表字段列表拖动到“行”区域。
将字段 - **总销售额** 从表 - 东区、北区、南区和西区拖动到“∑ 值”区域。

如您所见，阅读此类报表有点麻烦。如果条目数量增加，则会更加困难。

Power Pivot 提供了一种使用展平透视表更好地表示数据的解决方案。

创建展平透视表

您可以按照以下步骤创建 Power 展平透视表：

单击 PowerPivot 窗口中功能区的“开始”选项卡。
单击“透视表”。
从下拉列表中选择 **展平透视表**。

出现 **创建展平透视表** 对话框。选择“新建工作表”，然后单击“确定”。

您可以观察到数据在此透视表中已展平。

**注意** - 在这种情况下，销售人员、地区和产品仅位于“行”区域，与之前的案例相同。但是，在透视表布局中，这三个字段显示为三个列。

浏览展平透视表中的数据

假设您想汇总产品 - 空调的销售数据。您可以使用展平透视表以简单的方式执行此操作，如下所示：

单击列标题 - 产品旁边的箭头。
选中“空调”复选框，取消选中其他复选框。单击“确定”。

展平透视表已过滤为空调销售数据。

您可以通过将“∑ 值”从“列”区域拖动到“行”区域来使其看起来更平坦。

重命名“∑ 值”区域中求和值的自定义名称，使其更有意义，如下所示：

单击一个求和值，例如，东区“总销售额”的和。
从下拉列表中选择“值字段设置”。
将自定义名称更改为“东区总销售额”。
对其他三个求和值重复这些步骤。

您还可以汇总销售的单位数量。

将“单位数量”从每个表（东区销售、北区销售、南区销售和西区销售）拖动到“∑ 值”区域。
分别将值重命名为“东区总单位数”、“北区总单位数”、“南区总单位数”和“西区总单位数”。

您可以观察到，在上表中，都存在具有空值的列，因为每个销售人员代表一个地区，每个地区仅由一个销售人员代表。

选择具有空值的列。
右键单击并从下拉列表中单击“隐藏”。

所有具有空值的列都将被隐藏。

您可以观察到，尽管没有显示具有空值的列，但有关销售人员的信息也被隐藏了。

单击列标题 - 销售人员。
单击功能区上的“分析”选项卡。
单击“字段设置”。将出现“字段设置”对话框。
单击“布局和打印”选项卡。
选中 - **重复项目标签** 复选框。
单击“确定”。

您可以观察到，显示了销售人员信息，并且隐藏了具有空值的列。此外，报表中的“地区”列是冗余的，因为“值”列中的值是不言自明的。

将“地区”字段拖出区域。

反转“行”区域中“销售人员”和“产品”字段的顺序。

您已获得一份简洁的报表，其中结合了 Power Pivot 中六个表的数据。

Excel Power Pivot 图表 - 创建

基于数据模型并从 Power Pivot 窗口创建的透视图表是 Power Pivot 图表。虽然它具有一些与 Excel 透视图表类似的功能，但也有一些其他功能使其更强大。

在本章中，您将了解 Power Pivot 图表。为简便起见，我们以后将其称为透视图表。

创建透视图表

假设您想基于以下数据模型创建透视图表。

单击 Power Pivot 窗口中功能区的“开始”选项卡。
单击“透视表”。
从下拉列表中选择“透视图表”。

出现 **创建透视图表** 对话框。选择“新建工作表”，然后单击“确定”。

在 Excel 窗口的新工作表中创建一个空的透视图表。

您可以观察到，数据模型中的所有表都显示在透视图表字段列表中。

单击透视图表字段列表中的“销售人员”表。
将字段 - 销售人员和地区拖动到“轴”区域。

透视图表上将出现两个所选字段的字段按钮。这些是轴字段按钮。字段按钮用于筛选在透视图表上显示的数据。

将 **总销售额** 从四个表（东区销售、北区销售、南区销售和西区销售）中的每一个拖动到“∑ 值”区域。

工作表上将显示以下内容：

默认情况下，透视图表中显示的是柱状图。
在“图例”区域中添加了“∑ 值”。
值将显示在透视图表的图例中，标题为“值”。
值字段按钮出现在透视图表上。您可以删除图例和值字段按钮，使透视图表的显示更简洁。
单击透视图表右上角的按钮。将出现 **图表元素** 下拉列表。

取消选中“图表元素”列表中的“图例”复选框。图例将从透视图表中删除。

右键单击值字段按钮。
从下拉列表中选择“隐藏图表上的值字段按钮”。

图表上的值字段按钮将被删除。

**注意** - 字段按钮和/或图例的显示取决于透视图表的上下文。您需要确定需要显示的内容。

透视图表字段列表

与 Power Pivot 表一样，Power Pivot 图表字段列表也包含两个选项卡 - “活动”和“全部”。在“全部”选项卡下，将显示 Power Pivot 数据模型中的所有数据表。在“活动”选项卡下，将显示已从中将字段添加到透视图表的表。

同样，区域与 Excel 透视图表的区域相同。有四个区域：

轴（类别）
图例（系列）
∑ 值
筛选器

如上一节所述，图例中填充了“∑ 值”。此外，为了方便筛选正在显示的数据，还向透视图表添加了字段按钮。

透视图表中的筛选器

您可以使用图表上的轴字段按钮来筛选正在显示的数据。单击轴字段按钮 - 地区的箭头。

出现的下拉列表如下所示：

您可以选择要显示的值。或者，您可以将字段放置在“筛选器”区域中以筛选值。

将“地区”字段拖动到“筛选器”区域。透视图表上将出现“报表筛选器”按钮 - 地区。

单击“报表筛选器”按钮 - 地区的箭头。出现的下拉列表如下所示：

您可以选择要显示的值。

数据透视图中的切片器

使用切片器是筛选 Power Pivot 数据透视图中数据的另一种方法。

单击功能区上“数据透视图工具”下的“分析”选项卡。
单击“筛选”组中的“插入切片器”。将出现“**插入切片器**”对话框。

所有表格和相应的字段都显示在“插入切片器”对话框中。

在“插入切片器”对话框中，单击“销售人员”表中的“区域”字段。

“区域”字段的切片器将出现在工作表上。

您可以看到，“区域”字段仍然存在于轴字段中。您可以通过单击切片器按钮来选择要显示的值。

请记住，由于 Power Pivot 数据模型和已定义的关系，您可以在几分钟内完成所有这些操作，并且这些操作还是动态的。

数据透视图工具

在 Power Pivot 数据透视图中，“数据透视图工具”在功能区上有三个选项卡，而 Excel 数据透视图只有两个选项卡：

分析
设计
格式

第三个选项卡“格式”是 Power Pivot 数据透视图中新增的选项卡。

单击功能区上的“格式”选项卡。

功能区上“格式”选项卡下的所有选项都用于美化您的数据透视图。您可以谨慎地使用这些选项，避免过度使用。

表格和图表组合

Power Pivot 提供了 Power PivotTable 和 Power PivotChart 的不同组合，用于数据探索、可视化和报告。您在前面的章节中学习了数据透视表和数据透视图。

在本节中，您将学习如何在 Power Pivot 窗口中创建表格和图表组合。

考虑以下 Power Pivot 中的数据模型，我们将使用它进行说明：

图表和表格（水平）

使用此选项，您可以在同一工作表中水平创建 Power PivotChart 和 Power PivotTable，一个挨着一个。

单击 Power Pivot 窗口中的“开始”选项卡。
单击“透视表”。
从下拉列表中选择“图表和表格（水平）”。

出现“创建数据透视图和数据透视表（水平）”对话框。选择“新建工作表”并单击“确定”。

一个空的数据透视图和一个空的数据透视表将出现在新的工作表上。

单击数据透视图。
将“Medals”表中的**NOC_CountryRegion** 拖动到“轴”区域。
将“Medals”表中的“Medal”拖动到“∑ 值”区域。
右键单击图表，然后从下拉列表中选择“更改图表类型”。
选择“面积图”。
将图表标题更改为“**奖牌总数 - 按国家/地区分类**”。

您可以看到，美国获得的奖牌数量最多（> 4500）。

单击数据透视表。
将“Sports”表中的“Sport”拖动到“行”区域。
将“Medals”表中的“Medal”拖动到“∑ 值”区域。
将“Medals”表中的**NOC_CountryRegion** 拖动到“筛选器”区域。
将**NOC_CountryRegion** 字段筛选为“USA”值。

将**数据透视表报表**布局更改为“大纲”形式。

取消选择“Sports”表中的“Sport”。
将“Medals”表中的“Gender”拖动到“行”区域。

图表和表格（垂直）

使用此选项，您可以在同一工作表中垂直创建 Power PivotChart 和 Power PivotTable，一个在另一个下方。

单击 Power Pivot 窗口中的“开始”选项卡。
单击“透视表”。
从下拉列表中选择“图表和表格（垂直）”。

将出现“**创建数据透视图和数据透视表（垂直）**”对话框。选择“新建工作表”并单击“确定”。

一个空的数据透视图和一个空的数据透视表将垂直出现在新的工作表上。

单击数据透视图。
将“Medals”表中的“Year”拖动到“轴”区域。
将“Medals”表中的“Medal”拖动到“∑ 值”区域。
右键单击图表，然后从下拉列表中选择“更改图表类型”。
选择“折线图”。
选中“图表元素”中的“数据标签”复选框。
将图表标题更改为“**奖牌总数 - 按年份分类**”。

您可以看到，2008 年的奖牌数量最多 (2450)。

单击数据透视表。
将“Sports”表中的“Sport”拖动到“行”区域。
将“Medals”表中的“Gender”拖动到“行”区域。
将“Medals”表中的“Medal”拖动到“∑ 值”区域。
将“Medals”表中的“Year”拖动到“筛选器”区域。
将“Year”字段筛选为“2008”值。
将数据透视表的报表布局更改为“大纲”形式。
使用“值筛选器”将“Sport”字段筛选为“大于或等于 80”。

Excel Power Pivot - 层次结构

数据模型中的层次结构是数据表中嵌套列的列表，在 Power PivotTable 中使用时，这些列被视为单个项目。例如，如果数据表中包含“国家/地区”、“州/省”和“城市”列，则可以定义层次结构以将这三列组合成一个字段。

在 Power PivotTable 字段列表中，层次结构显示为一个字段。因此，您可以只向数据透视表添加一个字段，而不是层次结构中的三个字段。此外，它使您可以以有意义的方式向上或向下移动嵌套级别。

考虑本章中说明的以下数据模型。

创建层次结构

您可以在数据模型的图表视图中创建层次结构。请注意，您只能基于单个数据表创建层次结构。

按顺序单击数据表“Medal”中的“Sport”、“DisciplineID”和“Event”列。请记住，顺序对于创建有意义的层次结构非常重要。
右键单击所选内容。
从下拉列表中选择“创建层次结构”。

将创建具有三个选定字段作为子级别的层次结构字段。

重命名层次结构

要重命名层次结构字段，请执行以下操作：

右键单击 Hierarchy1。
从下拉列表中选择“重命名”。

键入“**EventHierarchy**”。

使用数据模型中的层次结构创建数据透视表

您可以使用在数据模型中创建的层次结构来创建 Power PivotTable。

单击 Power Pivot 窗口中功能区的“数据透视表”选项卡。
单击功能区上的“数据透视表”。

将出现“创建数据透视表”对话框。选择“新建工作表”并单击“确定”。

将在新的工作表中创建一个空的数据透视表。

在“数据透视表字段”列表中，“EventHierarchy”显示为“Medals”表中的一个字段。“Medals”表中的其他字段将被折叠并显示为“更多字段”。

单击“EventHierarchy”前面的箭头。
单击“更多字段”前面的箭头。

将显示“EventHierarchy”下的字段。“更多字段”下将显示“Medals”表中的所有字段。

您可以看到，添加到层次结构中的三个字段也显示在“更多字段”下，并带有复选框。如果您不希望它们显示在“更多字段”下的“数据透视表字段”列表中，则必须在 Power Pivot 窗口的数据视图中隐藏数据表“Medals”中的列。您可以随时取消隐藏它们。

按如下方式向数据透视表添加字段：

将“EventHierarchy”拖动到“行”区域。
将“Medal”拖动到“∑ 值”区域。

“Sport”字段的值将显示在数据透视表中，其前面带有“+”号。将显示每种运动的奖牌计数。

单击“Aquatics”之前的“+”号。“Aquatics”下的“DisciplineID”字段值将显示。
单击显示的子项 D22。“D22”下的“Event”字段值将显示。

您可以看到，给出了事件的奖牌计数，这些计数在父级别（“DisciplineID”）进行汇总，然后在父级别（“Sport”）再次进行汇总。

基于多个表创建层次结构

假设您想在数据透视表中显示“Disciplines”，而不是“DisciplineIDs”，以便使其更易于阅读和理解。为此，您需要在“Medals”表中具有“Discipline”字段，但您知道该字段不存在。“Discipline”字段位于“Disciplines”数据表中，但您不能使用多个表中的字段创建层次结构。但是，有一种方法可以从其他表中获取所需的字段。

您知道，“Medals”和“Disciplines”表是相关的。您可以通过使用 DAX 创建列，将“Disciplines”表中的“Discipline”字段添加到“Medals”表中。

单击 Power Pivot 窗口中的数据视图。
单击功能区上的“设计”选项卡。
单击“添加”。

表右侧的“添加列”列突出显示。

在公式栏中键入 = **RELATED (Disciplines [Discipline])**。将创建一个新列“CalculatedColumn1”，其值为“Disciplines”表中的“Discipline”字段值。

将这样获得的“Medals”表中的新列重命名为“Discipline”。接下来，您必须从层次结构中删除“DisciplineID”，并添加“Discipline”，这将在以下部分中学习。

从层次结构中删除子级别

您可以看到，层次结构仅在图表视图中可见，而不是在数据视图中可见。因此，您只能在图表视图中编辑层次结构。

单击 Power Pivot 窗口中的图表视图。
右键单击“EventHierarchy”中的“DisciplineID”。
从下拉列表中选择“从层次结构中删除”。

将出现“确认”对话框。单击“从层次结构中删除”。

将从层次结构中删除“DisciplineID”字段。请记住，您已从层次结构中删除了该字段，但源字段仍然存在于数据表中。

接下来，您需要将“Discipline”字段添加到“EventHierarchy”。

向层次结构添加子级别

您可以按如下方式将“Discipline”字段添加到现有的层次结构“EventHierarchy”：

单击“Medals”表中的字段。
将其拖动到“EventHierarchy”中“Events”字段的下方。

“Discipline”字段将添加到“EventHierarchy”。

您可以看到，“EventHierarchy”中字段的顺序是“Sport - Event - Discipline”。但是，您知道它应该是“Sport - Discipline - Event”。因此，您需要更改字段的顺序。

更改层次结构中子级别的顺序

要将“Discipline”字段移动到“Sport”字段之后的位置，请执行以下操作：

右键单击“EventHierarchy”中的“Discipline”字段。
从下拉列表中选择“向上移动”。

字段的顺序将更改为“Sport - Discipline - Event”。

包含层次结构更改的数据透视表

要查看在 Excel 窗口中的数据透视表中对“EventHierarchy”所做的更改，您无需创建新的数据透视表。您可以在现有的数据透视表中查看它们。

单击 Excel 窗口中包含数据透视表的工作表。

您可以看到，在“数据透视表字段”列表中，“EventHierarchy”中的子级别反映了您在数据模型中对层次结构所做的更改。相同的更改也会相应地反映在数据透视表中。

单击数据透视表中“Aquatics”前面的“+”号。子级别将显示为“Discipline”字段的值。

隐藏和显示层次结构

您可以选择隐藏层次结构，并在需要时显示它们。

取消选中图表视图顶部菜单中的“层次结构”复选框以隐藏层次结构。
选中“层次结构”复选框以显示层次结构。

以其他方式创建层次结构

除了在前面各节中创建层次结构的方法之外，您还可以通过另外两种方式创建层次结构。

1. 单击图表视图中“Medals”数据表右上角的“创建层次结构”按钮。

将在这个表中创建一个新的层次结构，其中没有任何字段。

按顺序将“Year”和“Season”字段拖动到新的层次结构中。层次结构将显示子级别。

2. 创建相同层次结构的另一种方法如下：

右键单击图表视图中“Medals”数据表中的“Year”字段。
从下拉列表中选择“创建层次结构”。

将在表中创建一个新的层次结构，其中“Year”作为子字段。

将“season”字段拖动到层次结构中。层次结构将显示子级别。

删除层次结构

您可以按如下方式从数据模型中删除层次结构：

右键单击层次结构。
从下拉列表中选择“删除”。

将出现“确认”对话框。单击“从模型中删除”。

层次结构将被删除。

使用层次结构进行计算

您可以使用层次结构创建计算。在 EventsHierarchy 中，您可以按如下方式将子级奖牌数量显示为其父级奖牌数量的百分比：

右键单击事件的“奖牌计数”值。
从下拉列表中选择“值字段设置”。

将出现“值字段设置”对话框。

单击“显示值方式”选项卡。
从列表中选择“父行总计的百分比”，然后单击“确定”。

子级将显示为父级总计的百分比。您可以通过将父级的子级百分比值相加来验证这一点。总和将为 100%。

在层次结构中向上和向下钻取

您可以使用快速浏览工具快速地在层次结构的各级之间向上和向下钻取。

单击数据透视表中“事件”字段的值。
单击快速浏览工具 - ，该工具显示在包含所选值的单元格的右下角。

将出现带有“向上钻取”选项的“浏览”框。这是因为从“事件”只能向上钻取，因为在其下没有子级。

单击“向上钻取”。

数据透视表数据将向上钻取到“学科”。

单击快速浏览工具 - ，该工具显示在包含值的单元格的右下角。

将出现“浏览”框，其中显示“向上钻取”和“向下钻取”选项。这是因为从“学科”可以向上钻取到“运动”，或向下钻取到“事件”。

这样，您可以快速地在层次结构中上下移动。

Excel Power Pivot - 美观的报表

您可以使用数据模型中的 Power Pivot 数据创建数据分析的美观报表。

重要功能包括：

您可以使用数据透视图表生成数据的可视化报表。您可以使用报表布局来构建数据透视表，使其易于阅读。
您可以插入切片器以筛选报表中的数据。
您可以对同一报表中的数据透视图表和数据透视表使用一个公共切片器。
最终报表准备好后，您可以选择隐藏显示表单的切片器。

本章将学习如何在 Power Pivot 中使用可用的选项来获取报表。

考虑本章中说明的以下数据模型。

基于 Power Pivot 图表的报表

按如下方式创建 Power Pivot 图表：

在 Power Pivot 窗口的“功能区”中单击“开始”选项卡。
单击“透视表”。
从下拉列表中选择“透视图表”。
在“创建数据透视图表”对话框中单击“新建工作表”。

在 Excel 窗口的新工作表中创建一个空数据透视图表。

将“奖牌”表中的“运动”拖到“轴”区域。
将“奖牌”表中的“奖牌”拖到“∑ 值”区域。

单击功能区上“数据透视表工具”下的“分析”选项卡。
单击“筛选器”组中的“插入切片器”。将出现“插入切片器”对话框。
单击“奖牌”表中的“NOC_CountryRegion”字段。
单击“确定”。

将出现切片器“NOC_CountryRegion”。

选择“美国”。
将“奖牌”表中的“性别”拖到“性别”区域。

右键单击数据透视图表。
从下拉列表中选择“更改图表类型”。

将出现“更改图表类型”对话框。

单击“堆积柱形图”。

为“运动”字段插入切片器。
将“学科”表中的“学科”拖到“轴”区域。
从“轴”区域中删除“运动”字段。
在“运动”切片器中选择“水上运动”。

报表布局

按如下方式创建数据透视表：

在 PowerPivot 窗口的功能区中单击“开始”选项卡。
单击“数据透视表”。
在下拉列表中单击“数据透视表”。将出现“创建数据透视表”对话框。
单击“新建工作表”，然后单击“确定”。在新工作表中将创建一个空数据透视表。
将“奖牌”表中的“NOC_CountryRegion”拖到“轴”区域。
将“奖牌”表中的“运动”拖到“列”区域。
将“学科”表中的“学科”拖到“列”区域。
将“Medal”拖动到“∑ 值”区域。

单击“列标签”旁边的箭头按钮，然后选择“水上运动”。

单击“行标签”旁边的箭头按钮。
从下拉列表中选择“值筛选器”。
从第二个下拉列表中选择“大于或等于”。

在“值筛选器”对话框中，“奖牌计数大于或等于”旁边的框中键入 80。

单击功能区上“数据透视表工具”下的“设计”选项卡。
单击“小计”。
从下拉列表中选择“不显示小计”。

将删除“小计”列 - “水上运动总计”。

单击“报表布局”，然后从下拉列表中选择“以大纲形式显示”。

选中“带状行”复选框。

字段名称将显示在“行标签”和“列标签”的位置，并且报表看起来不言自明。

使用公共切片器

在彼此旁边创建数据透视图表和数据透视表。

在 PowerPivot 选项卡的功能区中单击“开始”选项卡。
单击“透视表”。
从下拉列表中选择“图表和表格（水平）”。

将出现“创建数据透视图表和数据透视表（水平）”对话框。

选择“新建工作表”，然后单击“确定”。在新工作表中将出现一个空数据透视图表和一个空数据透视表，它们彼此相邻。

单击数据透视图表。
将“学科”表中的“学科”拖到“轴”区域。
将“奖牌”表中的“奖牌”拖到“∑ 值”区域。
单击“透视表”。
将“学科”表中的“学科”拖到“行”区域。
将“奖牌”表中的“奖牌”拖到“∑ 值”区域。

单击功能区上“数据透视表工具”下的“分析”选项卡。
单击“插入切片器”。将出现“插入切片器”对话框。
单击“奖牌”表中的“NOC_CountryRegion”和“运动”。
单击“确定”。

将出现两个切片器 - “NOC_CountryRegion”和“运动”。对其进行排列和调整大小以使其与数据透视表正确对齐。

在“NOC_CountryRegion”切片器中选择“美国”。
在“运动”切片器中选择“水上运动”。数据透视表将被筛选为所选值。

正如您所看到的，数据透视图表未被筛选。要使用相同的筛选器筛选数据透视图表，您无需再次为数据透视图表插入切片器。您可以使用已用于数据透视表的相同切片器。

单击“NOC_CountryRegion”切片器。
单击功能区上“切片器工具”下的“选项”选项卡。
单击“切片器”组中的“报表连接”。“NOC_CountryRegion”切片器的“报表连接”对话框将出现。

您可以看到工作簿中的所有数据透视表和数据透视图表都列在对话框中。

单击与所选数据透视表位于同一工作表中的数据透视图表，然后单击“确定”。
对“运动”切片器重复此操作。

数据透视图表也被筛选为在两个切片器中选择的 value。

接下来，您可以向数据透视图表和数据透视表添加详细信息。

单击数据透视图表。
将“性别”拖到“图例”区域。
右键单击数据透视图表。
选择“更改图表类型”。
在“更改图表类型”对话框中选择“堆积柱形图”。
单击数据透视表。
将“事件”拖到“行”区域。
单击功能区上“数据透视表工具”下的“设计”选项卡。
单击“报表布局”。
从下拉列表中选择“大纲形式”。

选择要在报表中显示的对象

您可以选择不在最终报表中显示切片器。

单击功能区上“切片器工具”下的“选项”选项卡。
在“排列”组中单击“选择窗格”。“选择窗格”将显示在窗口的右侧。

正如您所看到的，在“选择窗格”中对象的旁边出现符号。这意味着这些对象是可见的。

单击“NOC_CountryRegion”旁边的符号。
单击“运动”旁边的符号。符号将更改为（两者都是）。这意味着这两个切片器的可见性已关闭。

关闭“选择窗格”。

您可以看到报表中这两个切片器是不可见的。

打印页面