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 PivotTables - 创建 Power PivotTables 和 Flattened PivotTables。

您将在“Power PivotCharts”一章中学习如何创建Power PivotCharts。

DAX 基础知识

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

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

探索和报告 Power Pivot 数据

您可以使用 Power 数据透视表和 Power Pivot 图表探索数据模型中的 Power Pivot 数据。在本教程中，您将了解如何探索和报告数据。

层次结构

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

美学报告

您可以使用 Power Pivot Charts 和/或 Power Pivot Charts 创建数据分析的美观报告。您可以使用多种格式选项来突出显示报告中的重要数据。这些报告本质上是交互式的，使查看紧凑报告的人员能够快速轻松地查看任何所需的详细信息。

您将在本章中了解这些详细信息 - 使用 Power Pivot 数据的美学报告。

Excel Power Pivot - 安装

Excel 中的 Power Pivot 提供了连接各种不同数据源的数据模型，可以基于该模型对数据进行分析、可视化和探索。Power Pivot 提供的易于使用的界面使具有 Excel 实践经验的人员能够轻松加载数据、将数据作为数据表进行管理、在数据表之间创建关系并执行所需的计算以得出报告。

在本章中，您将了解是什么让 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 并单击“确定”。

什么是动力枢轴？

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

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

Power Pivot 窗口将具有功能区，如下所示 -

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

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

PowerPivot 窗口中 Power Pivot 的用户友好界面使您无需了解任何数据库查询语言即可执行数据操作。然后，您可以在几秒钟内创建分析报告。这些报告具有多功能性、动态性和交互性，使您能够进一步探究数据以获得见解并得出结论/决策。

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

通过 Power Pivot 增强功能以实现卓越

Excel 中的 Power Pivot 功能是免费的。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 窗口。通过 PowerPivot 窗口，您可以直接将数据加载到数据模型中，在数据视图和图表视图中查看数据，创建表之间的关系，管理关系以及创建 Power PivotTable 和/或 PowerPivot Chart 报表。

从外部源导入数据时，不需要将数据存储在 Excel 表格中。如果工作簿中有 Excel 表形式的数据，则可以将它们添加到数据模型，从而在数据模型中创建链接到 Excel 表的数据表。

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

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

数据模型

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

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

内存优化

Power Pivot 数据模型使用 xVelocity 存储，当数据加载到内存中时，该存储会被高度压缩，从而可以在内存中存储数亿行。

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

文件大小紧凑

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

功率数据透视表

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

功率数据透视图

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

您还可以组合使用 Power PivotTable 和 Power PivotChart。

DAX语言

Power Pivot 的优势来自 DAX 语言，它可以有效地在数据模型上使用，对数据表中的数据执行计算。您可以拥有由 DAX 定义的可在 Power 数据透视表和 Power 数据透视图中使用的计算列和计算字段。

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 关系数据库
微软Access数据库
SQL Server 分析服务
SQL Server 报告服务 (SQL 2008 R2)
ATOM 数据源
文本文件
微软 SQL Azure
甲骨文
泰拉数据
赛贝斯
信息系统
IBM DB2
对象链接和嵌入数据库/开放数据库连接
(OLEDB/ODBC) 来源
微软Excel文件
文本文件

将数据直接加载到 PowerPivot 中

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

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

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

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

将出现表导入向导。

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

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

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

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

选中奖牌框。

正如您所观察到的，您可以通过选中复选框来选择表格，在添加到数据透视表之前预览和筛选表格和/或选择相关表格。

单击预览和过滤按钮。

如您所见，您可以通过选中列标签中的框来选择特定列，通过单击列标签中的下拉箭头来选择要包含的值来过滤列。

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

您可以看到 Power Pivot 发现 Disciplines 表与 Medals 表相关并选择了它。单击“完成”。

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

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

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-Employee table。
选中“使用第一行作为列标题”框，然后单击“确定”。

复制到剪贴板的数据将粘贴到 Power Pivot 中的新数据表中，其中包含选项卡 - Word-Employee 表。

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

从 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 对其的使用。Data Model 可以被认为是 Power Pivot 数据库，Power Pivot 的所有强大功能都是通过 Data Model 来管理的。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 的主要用途是能够管理数据表及其之间的关系，以便于分析多个表中的数据。您可以在创建数据透视表时或直接从 PowerPivot 功能区将 Excel 表添加到数据模型。

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

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

当您在 Excel 中创建数据透视表时，它仅基于单个表/范围。如果您想向数据透视表添加更多表，可以使用数据模型来执行此操作。

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

其中包含销售人员及其代表的区域的数据，位于表“销售人员”中。
另一个包含销售数据、区域和月份的表格 - 销售。

您可以总结销售人员的销售情况，如下所示。

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

将创建一个空数据透视表，其中包含销售表中的字段 - 区域、月份和订单金额。正如您所观察到的，数据透视表字段列表下方有一个“更多表”命令。

单击更多表。

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

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

在数据透视表字段下，您可以观察到有两个选项卡 - ACTIVE和ALL。

单击全部选项卡。
两个表 - 销售人员和销售人员，以及相应的字段出现在数据透视表字段列表中。
单击“销售人员”表中的“销售人员”字段，并将其拖至“ROWS”区域。
单击“Sales”表中的“Month”字段并将其拖至“ROWS”区域。
单击“销售”表中的“订单金额”字段并将其拖至“Σ VALUES”区域。

数据透视表已创建。数据透视表字段中会出现一条消息 -可能需要表之间的关系。

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

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

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

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

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

您可以观察到用于创建数据透视表的两个 Excel 表已转换为数据模型中的数据表。

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

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

您可以将不同工作簿中的 Excel 表添加到数据模型中，如下所示 -

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

在“表/范围”框中，输入“销售额”。
单击“新建工作表”。
选中将此数据添加到数据模型框。
单击“确定”。

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

您已将销售表数据添加到数据模型中。接下来，您必须将销售人员表数据也放入数据模型中，如下所示 -

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

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

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

您可以看到框 -将此数据添加到数据模型已选中且处于非活动状态。单击“确定”。

将创建数据透视表。

正如您所看到的，这两个表位于数据模型中。您可能必须像上一节一样在两个表之间创建关系。

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

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

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

一张表中包含销售人员及其代表的区域的数据 - 销售人员。
另一个包含销售数据、区域和月份的表格 - 销售。

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

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

将出现 Power Pivot 窗口，其中添加了数据表 Salesperson。此外，Power Pivot 窗口的功能区上会出现一个选项卡 - 链接表。

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

您可以发现工作簿中存在的两个表的名称已显示，并且名称 Salesperson 已被勾选。这意味着数据表 Salesperson 链接到 Excel 表 Salesperson。

单击转至 Excel 表格。

将出现 Excel 窗口，其中包含包含销售人员表的工作表。

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

Excel 表 Sales 也被添加到数据模型中。

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

从数据视图
从图表视图

从数据视图创建关系

如您所知，在数据视图中，您可以查看以记录为行、字段为列的数据表。

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

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

从图表视图创建关系

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

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

数据模型的图表视图显示在 Power Pivot 窗口中。

单击“销售”表中的“区域”。销售表中的区域突出显示。
拖至“销售人员”表中的“区域”。销售人员表中的区域也突出显示。一条线将出现在您拖动的方向上。
从表 Sales 到表 Salesperson 会出现一条线，指示关系。

正如您所看到的，从“销售”表到“销售人员”表出现一条线，指示关系和方向。

如果您想了解关系中的字段，请单击关系线。两个表中的行和字段均突出显示。

管理关系

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

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

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

编辑关系

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

对关系进行必要的改变。
单击“确定”。这些变化反映在关系中。

删除关系

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

刷新 Power Pivot 数据

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

要刷新 PowerPivot 数据，请执行以下操作 -

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

数据表将根据 Excel 表中所做的修改进行更新。

正如您所观察到的，您无法直接修改数据表中的数据。因此，最好在将数据添加到数据模型时链接到数据表的 Excel 表中维护数据。这有助于在更新 Excel 表中的数据时更新数据表中的数据。

Excel Power PivotTable - 创建

Power PivotTable 基于 Power Pivot 数据库，称为数据模型。您已经了解了数据模型的强大功能。Power Pivot 的强大之处在于它能够汇总 Power PivotTable 中数据模型中的数据。如您所知，数据模型可以处理跨越数百万行并来自不同输入的大量数据。这使得 Power PivotTable 能够在几分钟内汇总来自任何地方的数据。

Power PivotTable 的布局类似于数据透视表，但有以下差异 -

数据透视表基于 Excel 表，而 Power 数据透视表基于属于数据模型一部分的数据表。
数据透视表基于单个 Excel 表或数据范围，而 Power 数据透视表可以基于多个数据表，前提是将它们添加到数据模型中。
数据透视表是从 Excel 窗口创建的，而 Power 数据透视表是从 PowerPivot 窗口创建的。

创建电源数据透视表

假设您有两个数据表 - 数据模型中的销售人员和销售人员。要从这两个数据表创建 PowerPivot 表，请按以下步骤操作 -

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

将出现“创建数据透视表”对话框。正如您所观察到的，这是一个简单的对话框，没有任何数据查询。这是因为，Power PivotTable 始终基于数据模型，即数据表及其之间定义的关系。

选择“新建工作表”并单击“确定”。

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

正如您所观察到的，Power PivotTable 的布局与数据透视表的布局类似。数据透视表工具出现在功能区上，带有“分析”和“设计”选项卡，与数据透视表相同。

数据透视表字段列表显示在工作表的右侧。在这里，您会发现与数据透视表的一些差异。

强大的数据透视表字段

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

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

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

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

正如您所观察到的，“销售人员”字段出现在数据透视表中，并且“销售人员”表按预期出现在“活动”选项卡下。

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

再次单击“活动”选项卡。这两个表 - 销售和销售人员都显示在“活动”选项卡下。

将“月份”拖至“列”区域。
将“区域”拖至“过滤器”区域。

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

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

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 的新列，其中包含根据您输入的公式计算出的值。

双击新计算列的标题。
将标头重命名为TotalProductPrice。

再添加一列毛利润计算列，如下 -

单击功能区上的“设计”选项卡。
单击添加。
右侧带有标题的列 - 添加列突出显示。
在公式栏中输入 = [TotalSalesAmount] − [TotaProductPrice] 。
按 Enter 键。