Excel Power Pivot 简明教程
Excel Power Pivot - Overview
Excel Power Pivot 是随 Excel 附带的强大高效工具。借助 Power Pivot,您可以从外部源加载数亿行数据,并使用其强大的 xVelocity 引擎以高度压缩的形式有效管理数据。这样一来,就可以执行计算、分析数据,然后汇总成报告,以得出结论并做出决策。因此,对于具有 Excel 实践经验的人来说,可以在几分钟内执行高端数据分析并做出决策。
本教程将涵盖以下内容 -
Power Pivot Data from Various Sources
Power Pivot 可以汇总来自各种数据源的数据以执行必要的计算。您将在章节 - 将数据加载到 Power Pivot 中学习如何将数据导入到 Power Pivot。
Power Pivot Data Model
Power Pivot 的强大之处在于其数据库 - 数据模型。数据以数据表的形式存储在数据模型中。您可以在数据表之间创建关系,以结合来自不同数据表的数据进行分析和报告。章节 - 了解数据模型(Power Pivot 数据库)提供了有关数据模型的详细信息。
Managing Data Model and Relationships
您需要知道如何管理数据模型中的数据表及其之间的关系。您将在章节 - 管理 Power Pivot 数据模型中获取这些详细信息。
Creating Power Pivot Tables and Power Pivot Charts
Power PivotTable 和 Power Pivot 图表为您提供一种方式来分析数据以得出结论和/或决策。
您将在章节 - 创建 Power PivotTable 和扁平化 PivotTable 中学习如何创建 Power PivotTable。
您将在章节 - Power PivotChart 中学习如何创建 Power PivotChart。
DAX Basics
DAX 是 Power Pivot 中用于执行计算的语言。DAX 中的公式类似于 Excel 公式,有一点区别 - Excel 公式基于各个单元格,而 DAX 公式基于列(字段)。
你将在本章——DAX 基础中了解 DAX 的基础知识。
Excel Power Pivot - Installing
Excel 中的 Power Pivot 提供了一个数据模型,可以连接各种不同的数据源,基于这些数据源可以分析、可视化和探索数据。Power Pivot 提供的易于使用的界面使具有 Excel 实践经验的人员能够毫不费力地加载数据、管理数据(作为数据表)、创建数据表之间的关系并执行必要的计算以形成报告。
在本章中,你将了解是什么使 Power Pivot 成为分析师和决策者不可或缺的强大工具。
Power Pivot on the Ribbon
开始使用 Power Pivot 的第一步是确保功能区上显示 POWERPIVOT 选项卡。如果您使用的是 Excel 2013 或更高版本,则功能区上会显示 POWERPIVOT 选项卡。
如果您使用的是 Excel 2010,则 POWERPIVOT 选项卡可能不会显示在功能区上,只要您尚未启用 Power Pivot 加载项。
Power Pivot Add-in
Power Pivot 加载项是一个 COM 加载项,需要启用它才能在 Excel 中获取 Power Pivot 的完整功能。即使功能区上显示了 POWERPIVOT 选项卡,您还需要确保启用该加载项才能访问 Power Pivot 的所有功能。
Step 1 - 单击功能区上的文件选项卡。
Step 2 - 单击下拉列表中的选项。此时会显示 Excel 选项对话框。
Step 3 - 按照以下说明进行操作。
-
Click Add-Ins.
-
在管理框中,从下拉列表中选择 COM 加载项。
-
单击转到按钮。此时会显示 COM 加载项对话框。
-
选中 Power Pivot,然后单击确定。
What is Power Pivot?
Excel Power Pivot 是用于集成和处理大量数据的一个工具。使用 Power Pivot,您可以轻松地在包含数百万行的各种数据中加载、排序和筛选数据,以及执行所需的计算。您可以利用 Power Pivot 作为一种即席报告和分析解决方案。
如下所示的 Power Pivot 功能区包含各种命令,从管理数据模型到创建报表。
Power Pivot 窗口的特色功能区如下所示 -
Why is Power Pivot a Strong Tool?
调用 Power Pivot 时,Power Pivot 会创建数据定义和连接,并将其以压缩形式与您的 Excel 文件一起存储。当源中的数据更新时,它会在您的 Excel 文件中自动刷新。这就使得可以方便地使用在别处维护的数据,而只需不时地进行研究和决策制定即可。源数据可以采用任何形式 - 从文本文件或网页到不同的关系数据库。
PowerPivot 窗口中 Power Pivot 的用户友好界面使您无需了解任何数据库查询语言即可执行数据操作。然后,您可以在几秒钟内创建分析报告。报告通用、动态且交互性强,可让您进一步探查数据,以获得见解并得出结论/决策。
您在 Excel 中及 Power Pivot 窗口中处理的数据存储在 Excel 工作簿中一个分析数据库中,一个强大的本地引擎会加载、查询和更新该数据库中的数据。因为数据位于 Excel 中,所以会立即提供给数据透视表、数据透视图、Power View 以及 Excel 中您用来聚合数据和与数据交互的其他功能。由 Excel 提供数据演示和交互性,且数据和 Excel 演示对象包含在同一个工作簿文件中。Power Pivot 支持大小高达 2 GB 的文件,并且能让您在内存中处理高达 4 GB 的数据。
Power Features to Excel with Power Pivot
Power Pivot 功能随 Excel 免费提供。Power Pivot 通过强大的功能增强了 Excel 性能,这些功能包括:
-
有能力以惊人的速度处理大量数据,并将这些数据压缩成小文件。
-
在导入时筛选数据以及重命名列和表。
-
将表整理到 Power Pivot 窗口中各个带标签的页面中,而不是像在 Excel 中将表分散在整个工作簿中或将多个表放在同一工作表中。
-
建立表之間的關聯,以便分析表格中的資料。在 Power Pivot 之前,必須大量使用 VLOOKUP 函數來將資料結合到單一表格中,才能進行此類分析。這 costumava ser 費力且容易出錯。
-
藉由許多新增功能提升簡單樞紐分析表的效能。
-
提供資料分析運算式 (DAX) 語言來撰寫進階公式。
-
將計算欄位和計算欄新增到資料表中。
-
建立 KPI,以便在樞紐分析表和 Power View 報告中使用。
您將在下一章詳細了解 Power Pivot 功能。
Uses of Power Pivot
您可以将 Power Pivot 用于以下用途−
-
执行强大的数据分析并创建复杂的数据模型。
-
快速整合来自多个不同来源的大量数据。
-
执行信息分析并以交互方式共享见解。
-
使用資料分析運算式 (DAX) 語言來撰寫進階公式。
-
创建关键绩效指标 (KPI)。
Data Modelling with Power Pivot
Power Pivot 提供 Excel 中的進階資料建模功能。Power Pivot 中的資料是由資料模型管理,資料模型也稱為 Power Pivot 資料庫。您可以使用 Power Pivot 協助您深入了解您的資料。
您可以建立資料表之間的關聯,以便對資料表進行資料分析。透過 DAX,您可以撰寫進階公式。您可以在資料模型中資料表中建立計算欄位和計算欄。
您可以定義資料中要在工作簿各處使用的階層,包括 Power View。您可以建立 KPI,以便在樞紐分析表和 Power View 報告中使用,用以一目了然地顯示是否超過或低於一個或多個指標的效能目標。
Excel Power Pivot - Features
Power Pivot 最重要且功能最强大的功能是其数据库 - 数据模型。下一个重要功能是 xVelocity 内存分析引擎,该引擎能够在几分钟内对大型多数据库执行操作。PowerPivot 加载项还附带一些更重要的功能。
在本章中,您将简要了解 Power Pivot 的各项功能,这些功能将在后面进行详细说明。
Loading Data from External Sources
您可以通过两种方式将数据从外部源加载到数据模型中 -
-
先将数据加载到 Excel 中,然后创建 Power Pivot 数据模型。
-
直接加载数据到 Power Pivot 数据模型。
第二种方法的效率更高,因为 Power Pivot 会以一种高效的方式处理内存中的数据。
有关更多详细信息,请参阅章节 − 将数据加载到 Power Pivot。
Excel Window and Power Pivot Window
当你开始使用 Power Pivot 时,会同时打开两个窗口 − Excel 窗口和 Power Pivot 窗口。可以通过 Power Pivot 窗口直接将数据加载到数据模型中,在数据视图和图表视图中查看数据、创建表之间的关系、管理关系以及创建 Power Pivot 表和/或 Power Pivot 图表报告。
从外部来源导入数据时,你不必将数据存放在 Excel 表格中。如果你在工作簿中将数据作为 Excel 表格,则可以将它们添加到数据模型中,在数据模型中创建链接到 Excel 表格的数据表。
当你从 Power Pivot 窗口创建数据透视表或数据透视图表时,它们会在 Excel 窗口中创建。但是,数据仍然由数据模型管理。
你随时可以在 Excel 窗口和 Power Pivot 窗口之间轻松切换。
Data Model
数据模型是 Power Pivot 最强大的功能。从各种数据源获得的数据作为数据表保存在数据模型中。你可以在数据表之间创建关系,以便将表中的数据合并起来进行分析和报告。
你将在章节 − 了解数据模型(Power Pivot 数据库)中详细了解数据模型。
Memory Optimization
Power Pivot 数据模型使用 xVelocity 存储,当数据加载到内存中时会对该数据进行高度压缩,这使得可以在内存中存储数亿行数据。
因此,如果你直接将数据加载到数据模型中,则会以高效的高度压缩形式执行此操作。
Compact File Size
如果数据直接加载到数据模型中,则在保存 Excel 文件时,它在硬盘上占用的空间非常小。你可以比较 Excel 文件的大小,第一个是将数据加载到 Excel 中然后创建数据模型,第二个是跳过第一步直接将数据加载到数据模型中。第二个文件将比第一个小 10 倍。
Power PivotTables
你可以从 Power Pivot 窗口创建 Power Pivot 表。这样创建的数据透视表基于数据模型中的数据表,使得可以将来自相关表的数据合并起来进行分析和报告。
Excel Power Pivot - Loading Data
在本章中,我们将学习如何将数据加载到 Power Pivot。
你可以使用两种方式将数据加载到 Power Pivot 中——
-
将数据加载到 Excel 中,并将其添加到数据模型
-
直接将数据加载到 PowerPivot 中,填充数据模型(即 PowerPivot 数据库)
如果你需要 Power Pivot 数据,无需通过 Excel 便可以使用第二种方法。这是因为,你只需要将数据加载一次,以高度压缩的格式。为了理解差异的程度,假设你通过先将数据添加到数据模型中并将数据加载到 Excel 中,文件大小可能是 10 MB。
如果你直接将数据加载到 PowerPivot 中,便可以跳过 Excel 的额外步骤,进入数据模型,此时你的文件大小可能只有不到 1 MB。
Data Sources Supported by Power Pivot
你可以将数据从各种数据源导入到 Power Pivot 数据模型,也可以建立连接和/或使用现有连接。Power Pivot 支持以下数据源——
-
SQL Server relational database
-
Microsoft Access database
-
SQL Server Analysis Services
-
SQL Server Reporting Services(SQL 2008 R2)
-
ATOM data feeds
-
Text files
-
Microsoft SQL Azure
-
Oracle
-
Teradata
-
Sybase
-
Informix
-
IBM DB2
-
对象链接和嵌入数据库/开放数据库连接
-
(OLEDB/ODBC) sources
-
Microsoft Excel File
-
Text File
Loading Data Directly into PowerPivot
要直接将数据加载到 Power Pivot,请执行以下操作——
-
Open a new workbook.
-
单击功能区上的 POWERPIVOT 选项卡。
-
点击数据模型组中的管理。
将会打开 PowerPivot 窗口。现在你有两个窗口——Excel 工作簿窗口和连接到你的工作簿的 PowerPivot for Excel 窗口。
-
单击 PowerPivot 窗口中的 Home 选项卡。
-
单击获取外部数据组中的 From Database 。
-
Select From Access.
表导入向导出现。
-
浏览到 Access 数据库文件。
-
Provide Friendly connection name.
-
如果数据库受密码保护,也填写这些详细信息。
单击 Next → 按钮。表导入向导显示用于选择如何导入数据的选项。
单击从表和视图的列表中选择以选择要导入的数据。
单击 Next → 按钮。表导入向导显示您选择的 Access 数据库中的表和视图。
选中 Medals 复选框。
您可能会注意到,您可以通过选中复选框来选择表、在添加到数据透视表之前预览和筛选表,或者选择相关表。
单击 Preview & Filter 按钮。
您会注意到,您可以通过选中列标签中的复选框来选择特定的列、通过单击列标签中的下拉箭头来选择包含的值来筛选列。
-
Click OK.
-
单击 Select Related Tables 按钮。
-
如果存在关系,Power Pivot 就会检查哪些其他表与选定的 Medals 表相关。
您会发现,Power Pivot 发现 Disciplines 表与 Medals 表相关并将其选中。单击完成。
表导入向导显示 − Importing 并显示导入的状态。这将需要几分钟的时间,您可通过单击 Stop Import 按钮来停止导入。
导入数据后,表导入向导显示 – Success 并显示导入的结果,如下面的截图所示。单击关闭。
Power Pivot 在两个选项卡中显示两个导入的表。
您可以使用选项卡下方的 Record 箭头滚动记录(表格的行)。
Table Import Wizard
在上一个部分中,您已经了解了如何通过表导入向导从 Access 导入数据。
请注意,根据所选用于连接的数据源,表导入向导选项会发生更改。您可能想知道可以选择哪些数据源。
单击 Power Pivot 窗口中的 From Other Sources 。
“表导入向导 − Connect to a Data Source ”出现。你可以创建一个到数据源的连接或使用已存在的连接。
可以在导入表向导中滚动浏览连接列表以了解与 Power Pivot 兼容的数据连接。
-
向下滚动到“文本文件”。
-
Select Excel File.
-
单击 Next → 按钮。表导入向导显示“连接至 Microsoft Excel 文件”。
-
浏览到 Excel 文件路径框中的 Excel 文件。
-
选中 Use first row as column headers 框。
-
单击 Next → 按钮。表导入向导显示 Select Tables and Views 。
-
选中 Product Catalog$ 框。单击 Finish 按钮。
你将看到以下 Success 消息。单击“关闭”。
你已导入一张表,并且还创建了连接至包含其他表的 Excel 文件的连接。
Opening Existing Connections
建立与数据源的连接后,你可以在稍后打开它。
单击 Power Pivot 窗口中的“现有连接”。
“现有连接”对话框出现。从列表中选择“Excel 销售数据”。
单击“打开”按钮。表导入向导出现,显示表和视图。
选择要导入的表,然后单击 Finish 。
选定的五张表将被导入。单击 Close 。
你可以看到这五张表已添加到 Power Pivot 中,每张都在一个新选项卡中。
Creating Linked Tables
链接表是 Excel 中表与数据模型中表之间的实时链接。对 Excel 中表的更新将自动更新模型中数据表中的数据。
你可以按照以下几个步骤将 Excel 表格链接到 Power Pivot 中−
-
使用数据创建 Excel 表格。
-
单击功能区上的 POWERPIVOT 选项卡。
-
在表格组中单击 Add to Data Model 。
Excel 表格链接到 PowerPivot 中的对应数据表。
你可以看到具有链接表格选项卡的表格工具已添加到 Power Pivot 窗口。如果你单击 Go to Excel Table ,你将切换到 Excel 工作表。如果你单击 Manage ,你将切换回 Power Pivot 窗口中的链接表格。
你可以自动或手动更新链接表格。
请注意,你只能在你与 Power Pivot 在工作簿中存在时链接 Excel 表格。如果你在一个单独的工作簿中有 Excel 表格,那么你必须按下一节中的解释加载它们。
Loading from Excel Files
如果你想要从 Excel 工作簿加载数据,请记住以下内容−
-
Power Pivot 将另一个 Excel 工作簿视为数据库,只会导入工作表。
-
Power Pivot 将每个工作表加载为一个表格。
-
Power Pivot 无法识别单个表格。因此,Power Pivot 无法识别工作表上是否存在多个表格。
-
Power Pivot 无法识别工作表表格以外的任何其他信息。
因此,请将每个表格保留在单独的工作表中。
准备好工作簿中的数据后,您可以按以下步骤导入数据-
-
单击 Power Pivot 窗口中“获取外部数据”组中的 From Other Sources 。
-
按照“表格导入向导”一节中的步骤进行操作。
以下是链接的 Excel 表格和导入的 Excel 表格之间的差异 −
-
链接的表格必须存储在与 Power Pivot 数据库相同的 Excel 工作簿中。如果数据已经存在于其他 Excel 工作簿中,则使用此功能没有任何意义。
-
Excel 导入功能允许您从不同的 Excel 工作簿中加载数据。
-
从 Excel 工作簿加载数据并不会在两个文件之间创建链接。导入时,Power Pivot 只会创建数据副本。
-
当最初的 Excel 文件更新时,Power Pivot 中的数据不会刷新。您需要在 Power Pivot 窗口的“链接表格”选项卡中,将更新模式设置为“自动”或者手动更新数据。
Loading from Text Files
一种流行的数据表示形式是逗号分隔值(csv)格式。每行数据/记录都由文本行表示,其中列/字段由逗号分隔。许多数据库都提供保存为 csv 格式文件的选择。
如果您想将 csv 文件加载到 Power Pivot 中,则必须使用“文本文件”选项。假设您有以下带 csv 格式的文本文件:
-
Click the PowerPivot tab.
-
单击 PowerPivot 窗口中的“主页”选项卡。
-
单击“获取外部数据”组中的@ {s0}。表导入向导就会显示。
-
向下滚动到“文本文件”。
-
Click Text File.
-
单击 Next → 按钮。表导入向导随即显示,屏幕上会显示“连接到平面文件”。
-
在“文件路径”框中,找到该文本文件。csv 文件通常的首行代表列标题。
-
如果首行有标题,则勾选“将首行用作列标题”框。
-
在“列分隔符”框中,默认是逗号 (,),但是如果您的文本文件有其他操作符,例如制表符、分号、空格、冒号或竖线,可以选择该操作符。
您可以观察到,这里是对数据表的预览。单击“完成”。
Power Pivot 在数据模型中创建数据表。
Loading from the Clipboard
假设您有应用程序中的数据,而 Power Pivot 无法将其识别为数据源。要将这些数据加载到 Power Pivot 中,您有两个选择:
-
将数据复制到 Excel 文件中,并使用 Excel 文件作为 Power Pivot 的数据源。
-
复制数据,以便在剪贴板上,然后将其粘贴到 Power Pivot 中。
您已经在较早的部分学习了第一个选项。而且这比第二个选项更好,您将在本部分的末尾找到它。但是,您应该知道如何将数据从剪贴板复制到 Power Pivot 中。
假设您在 word 文档中按以下方式拥有数据:
Word 不是 Power Pivot 的数据源。所以,执行以下操作:
-
在 Word 文档中选择表格。
-
将其复制并粘贴到 PowerPivot 窗口中。
Paste Preview 对话框将出现。
-
将名称指定为 Word-Employee table 。
-
选中 Use first row as column headers 框并单击确定。
复制到剪贴板的数据将粘贴到 Power Pivot 中的新数据表中,标签为 − Word-Employee 表。
假设您想要用新内容替换此表。
-
从 Word 中复制表格。
-
Click Paste Replace.
“粘贴预览”对话框将出现。验证您用来替换的内容。
单击确定。
正如您所观察到的,Power Pivot 中数据表的内容将被剪贴板中的内容替换。
假设您想要向数据表添加两行新数据。在 Word 文档的表格中,您有两个新行。
-
选择这两个新行。
-
Click Copy.
-
单击 Power Pivot 窗口中的 Paste Append 。“粘贴预览”对话框将出现。
-
验证您用来追加的内容。
单击确定继续。
正如您所观察到的,Power Pivot 中数据表的内容将追加剪贴板中的内容。
在本节的开头,我们已经说过将数据复制到 excel 文件并使用链接表比从剪贴板中复制更好。
这是由于以下原因:
-
· 如果您使用链接表,您将知道数据的来源。另一方面,您不会知道数据的来源时间或是否被其他人使用。
-
在 Word 文件中,您有跟踪信息,例如,数据替换的时间以及数据追加的时间。但是,无法将该信息复制到 Power Pivot。如果首先将数据复制到 Excel 文件,则可以保留该信息以供以后使用。
-
从剪贴板复制时,如果您想添加一些批注,则无法这样做。如果您首先复制到 Excel 文件,可以在 Excel 表中插入将链接到 Power Pivot 的批注。
-
无法刷新从剪贴板复制的数据。如果数据来自链接表,您始终可以确保数据已更新。
Excel Power Pivot - Data Model
数据模型是 Excel 2013 中引入的一种新方法,用于集成来自多个表的数据,有效地在 Excel 工作簿中构建关系数据源。在 Excel 中,数据模型以透明的方式使用,可提供用于数据透视表和数据透视图形的数据。在 Excel 中,您可以通过包含表名称和相应字段的数据透视表/数据透视图形字段列表访问表及其相应的值。
数据模型在 Excel 中的主要用途是 Power Pivot 使用它。数据模型可以看作是 Power Pivot 数据库,并且 Power Pivot 的所有强大功能都是通过数据模型进行管理的。Power Pivot 的所有数据操作本质上都是显式的,并且可以在数据模型中视化。
在本章中,您将详细了解数据模型。
Excel and Data Model
Excel 工作簿中只有一个数据模型。当您使用 Excel 时,数据模型使用是隐式的。您不能直接访问数据模型。您只能在数据透视表或数据透视图形的“字段”列表中查看“数据模型”中的多个表并使用它们。创建数据模型和添加数据也是在 Excel 中隐式完成的,此时将外部数据获取到 Excel 中。
如果你要查看数据模型,你可以按以下步骤进行…
-
单击功能区上的 POWERPIVOT 选项卡。
-
Click Manage.
如果工作簿中存在数据模型,它将显示为表格,每个表格都有一个选项卡。
Note − 如果你将 Excel 表添加到数据模型,你不会将 Excel 表转化为数据表。Excel 表的副本将作为数据表添加到数据模型中,并在两者之间创建一个链接。因此,如果在 Excel 表中进行了更改,数据表也会更新。但是,从存储的角度来看,有两个表。
Power Pivot and Data Model
数据模型本质上是 Power Pivot 的数据库。即使你从 Excel 创建数据模型,它也只构建 Power Pivot 数据库。创建数据模型和/或添加数据是在 Power Pivot 中明确完成的。
事实上,你可以从 Power Pivot 窗口管理数据模型。你可以向数据模型添加数据,从不同的数据源导入数据,查看数据模型,在表之间创建关系,创建计算字段和计算列,等等。
Creating a Data Model
你既可以从 Excel 添加表到数据模型,也可以直接将数据导入 Power Pivot,从而创建 Power Pivot 数据模型表。你可以在 Power Pivot 窗口中单击“管理”来查看数据模型。
你将在“通过 Excel 加载数据”一章中了解如何从 Excel 向数据模型添加表。你将在“将数据加载到 Power Pivot”一章中了解如何将数据加载到数据模型中。
Tables in Data Model
数据模型中的表可以定义为一组跨不同表持有关系的表。这些关系使人们能够出于分析和报告目的将来自不同表的相关数据进行组合。
数据模型中的表称为数据表。
数据模型中的表被认为是一组记录(记录是一行),由字段(字段是一列)组成。你不能编辑数据表中的各个项目。但是,你可以向数据表中追加行或添加计算列。
Excel Tables and Data Tables
Excel 表仅仅是单独表的集合。一个工作表上可以有多个表。每个表可以单独访问,但不可能同时访问来自多个 Excel 表的数据。这就是当您创建数据透视表时,它仅基于一个表的原因。如果您需要同时使用来自两个 Excel 表的数据,您需要首先将它们合并到一个 Excel 表中。
另一方面,数据表与其他有关系的数据表共存,有助于组合多表中的数据。将数据导入 Power Pivot 中时,将创建数据表。在创建数据透视表时,您还可以将 Excel 表添加到数据模型,以获取外部数据或从多表中获取数据。
数据模型中的数据表可以通过两种方式查看 −
-
Data View.
-
Diagram View.
Data View of Data Model
在数据模型的数据视图中,每个数据表都存在于一个单独的选项卡中。数据表行是记录,列表示字段。选项卡包含表名称,列标题是该表中的字段。您可以在数据视图中使用数据分析表达式 (DAX) 语言进行计算。
Diagram View of Data Model
在数据模型的图表视图中,所有数据表都由包含表名称的框表示,并包含表中的字段。您只需拖动即可安排图表视图中的表。您可以调整数据表的大小,以便显示表中的所有字段。
Relationships in Data Model
您可以在图表视图中查看关系。如果两表之间已定义关系,则会显示连接源表和目标表的箭头。如果您想知道关系中使用了哪些字段,只需双击箭头。箭头和两表中的两个字段将被突出显示。
如果您导入具有主键和外键关系的相关表,表关系会自动创建。Excel 可以使用导入的表关系信息作为数据模型中表关系的基础。
您还可以在两种视图中的任何一种中显式创建关系 −
-
Data View − 使用创建关系对话框。
-
Diagram View − 通过单击和拖动连接两个表。
Create Relationship Dialog Box
在一个关系中,涉及四个实体 −
-
Table − 关系开始的数据表。
-
Column − 表中同样也存在于相关表中的字段。
-
Related Table − 关系结束的数据表。
-
Related Column − 相关表中与表中列表示的字段相同的字段。请注意,相关列的值应该是唯一的。
在图表视图中,可以通过单击表中的字段并将鼠标拖动到相关表来创建关系。
您将在本章中了解有关关系的更多信息 - 使用 Power Pivot 管理数据表和关系。
Excel Power Pivot - Managing Data Model
Power Pivot 的主要用途是管理数据表及其之间的关系,以便于分析来自多张表的数据。在创建数据透视表或直接从 PowerPivot 功能区时,可以将 Excel 表添加到数据模型中。
仅当它们之间存在关系时,您才能分析来自多个表的数据。使用 Power Pivot,您可以从“数据视图”或“图表视图”创建关系。此外,如果您已选择将表添加到 Power Pivot,您还需要添加关系。
Adding Excel Tables to Data Model with PivotTable
当您在 Excel 中创建数据透视表时,它只基于单个表/范围。如果您想向数据透视表添加更多表,可以使用数据模型来实现。
假设您的工作簿中有两个工作表:
-
一个在表中包含业务员及其代表区域的数据——“业务员”。
-
另一个在表——“销售”中包含销售数据、区域和按月分类的数据。
您可以按业务员总结销售,如下所示。
-
单击表——“销售”。
-
单击功能区上的插入选项卡。
-
在 报表 组中选择 数据透视表。
将创建一个包含销售表字段(地区、月份和订单金额)的空数据透视表。正如你所观测到的,在数据透视表字段列表下面有一个 MORE TABLES 命令。
-
Click on MORE TABLES.
出现 Create a New PivotTable 消息框。显示的消息是 - 为在分析中使用多张报表,需要使用数据模型创建一个新的数据透视表。单击 是
将创建一个如下所示的新数据透视表 -
在数据透视表字段下方,你可以看到有两个选项卡 - ACTIVE 和 ALL 。
-
Click the ALL tab.
-
两张表 - 销售和销售人员,连同对应的字段出现在数据透视表字段列表中。
-
单击销售人员表中的销售人员字段,并将其拖动到 行 区域。
-
单击销售表中的月份字段,并将其拖动到 行 区域。
-
单击销售表中的订单金额字段,并将其拖动到 ∑ 值 区域。
数据透视表已创建。在数据透视表字段中出现一条消息 - Relationships between tables may be needed 。
单击消息旁边的 创建 按钮。出现 Create Relationship 对话框。
-
Under Table, select Sales.
-
在 Column (Foreign) 框下方,选择 地区。
-
在 Related Table 下方,选择 销售人员。
-
在 Related Column (Primary) 框下方,选择 地区。
-
Click OK.
你从两张工作表中的两张表的 PivotTable 已准备就绪。
此外,正如 Excel 在将第二张表添加到数据透视表时所述,数据透视表已使用数据模型创建。要进行验证,请执行以下操作:
-
单击功能区上的 POWERPIVOT 选项卡。
-
单击数据模型组中的 Manage 。出现 Power Pivot 的数据视图。
你可以看到,你在创建数据透视表时所使用的两张 Excel 表已转换为数据模型中的数据表。
Adding Excel Tables from a Different Workbook to Data Model
假设两张表 - 销售人员和销售位于两个不同的工作簿中。
你可以将其他工作簿中的 Excel 表格添加到数据模型中 ,如下所示 −
-
Click the Sales table.
-
Click the INSERT tab.
-
单击“表格”组中的“数据透视表”。出现 Create PivotTable 对话框。
-
在“表/区域”框中,键入“销售额”。
-
Click on New Worksheet.
-
选中“将此数据添加到数据模型”框。
-
Click OK.
你将获得一个新工作表上的空枢纽分析表,其中仅包含与“销售额”表相对应的字段。
你已将销售额表数据添加到数据模型中。接下来,你还必须按照以下步骤将销售人员表数据获取到数据模型中 −
-
单击包含销售额表的 worksheet。
-
单击功能区上的“数据”选项卡。
-
单击获取外部数据组中的现有连接。将显示现有连接对话框。
-
单击“表格”选项卡。
在 This Workbook Data Model, 1 table 下显示(这是先前添加的销售额表)。你还将找到两个在其中显示表格的工作簿。
-
Click Salesperson under Salesperson.xlsx.
-
单击“打开”。将显示 Import Data 对话框。
-
Click on PivotTable Report.
-
Click on New worksheet.
你可以看到 — Add this data to the Data Model 框已选中且处于非活动状态。单击“确定”。
将创建枢纽分析表。
正如你所观察到的,两个表格都在数据模型中。你可能需要在两个表之间创建关系,如前一部分所示。
Adding Excel Tables to Data Model from the PowerPivot Ribbon
将 Excel 表格添加到数据模型的另一种方法是执行 so from the PowerPivot Ribbon 。
假设您的工作簿中有两个工作表:
-
一个包含销售人员和他们所代表区域的数据,在一个表格中 — 销售人员。
-
另一个在表——“销售”中包含销售数据、区域和按月分类的数据。
可以在进行任何分析之前,首先将这些 Excel 表格添加到数据模型中。
-
单击 Excel 表格 - 销售额。
-
单击功能区上的 POWERPIVOT 选项卡。
-
点击表格组中的添加到数据模型。
Power Pivot 窗口会出现,其中添加了数据表“销售人员”。在 Power Pivot 窗口的 Ribbon 中会出现一个选项卡 — 链接的表格。
-
单击 Ribbon 上的“链接的表格”选项卡。
-
单击“Excel 表格:销售人员”。
你会发现工作簿中显示了两个表的名字,其中“销售人员”已经被选中。这意味着数据表“销售人员”已经链接到 Excel 表“销售人员”。
单击 Go to Excel Table 。
出现包含“销售人员”表格的工作表的 Excel 窗口。
-
单击“销售”工作表的选项卡。
-
Click the Sales table.
-
单击功能区“表格”组中的“添加到数据模型”。
Excel 表格“销售”也会被添加到数据模型中。
如果你希望基于这两个表格进行分析,正如你所了解的那样,你需要创建两个数据表格之间的关系。在 Power Pivot 中,可以通过两种方式进行操作 −
-
From Data View
-
From Diagram View
Creating Relationships from Data View
正如你所了解的那样,在数据视图中,你可以查看作为行和记录、作为列的字段中的数据表。
-
单击 Power Pivot 窗口中的“设计”选项卡。
-
单击“关系”组中的“创建关系”。 Create Relationship 对话框出现。
-
单击表格框中的“销售”。这是关系开始的表格。正如你所了解的那样,列应该是与包含唯一值的相关表“销售人员”中存在的字段。
-
单击列框中的“区域”。
-
单击相关链接表格框中的“销售人员”。
相关链接列会自动填入“区域”。
单击“创建”按钮。关系就会创建。
Creating Relationships from Diagram View
从图表视图中创建关系会相对更容易。按照给定的步骤进行操作。
-
单击 Power Pivot 窗口中的“主页”选项卡。
-
在“视图”组中单击“图表视图”。
数据模型的图表视图出现在 Power Pivot 窗口中。
-
单击“销售”表格中的“区域”。“销售”表格中的“区域”被突出显示。
-
拖到销售人员表中的区域。销售人员表中的区域也高亮显示。一条线出现在您拖动方向。
-
一条线从销售表显示到销售人员表,表示关系。
正如您所见,一条线从销售表显示到销售人员表,表示关系和方向。
如果您想知道是关系的一部分哪个字段,请单击关系线。关系线和两个表中的字段都会高亮显示。
Excel Power PivotTable - Creation
Power 透视表基于 Power 透视表数据库,该数据库被称为数据模型。你已经学习了数据模型的强大功能。Power 透视表的力量在于其在 Power 透视表中汇总数据模型数据的的能力。正如你所知道的,数据模型可以处理数百万行的数据,而且这些数据来自不同的输入。这让 Power 透视表可以在几分钟内汇总来自任何地方的数据。
Power 透视表在布局上类似于透视表,具有以下不同之处−
-
数据透视表基于 Excel 表格,而 Power PivotTable 基于数据模型中包含的数据表格。
-
数据透视表基于单个 Excel 表格或数据范围,而 Power PivotTable 可基于多个数据表格,前提是这些表格已添加到数据模型中。
-
数据透视表从 Excel 窗口创建,而 Power PivotTable 从 PowerPivot 窗口创建。
Creating a Power PivotTable
假设你在数据模型中有两个数据表 − Salesperson 和 Sales。要从这两个数据表创建 Power 透视表,请执行以下操作 −
-
在 PowerPivot 窗口中的功能区上单击“主页”选项卡。
-
单击功能区上的“PivotTable”。
-
从下拉列表中选择数据透视表。
创建透视表对话框出现。正如你所观察到的,这是一个简单的对话框,没有任何关于数据的查询。这是因为 Power 透视表总是基于数据模型,即定义了它们之间关系的数据表。
选择新建工作表并单击确定。
在 Excel 窗口中创建一个新工作表并会出现一个空透视表。
正如你所观察到的,Power 透视表的布局类似于透视表。 PIVOTTABLE TOOLS 出现于功能区,其中 ANALYZE 和 DESIGN 选项卡与透视表相同。
透视表字段列表出现在工作表的右侧。在这里,你会发现与透视表的一些不同之处。
Power PivotTable Fields
透视表字段列表有两个选项卡 − 标题下方和字段列表上方出现的活动状态和全部状态。 ALL 选项卡被高亮显示。
请注意, ALL 标签显示“数据模型”中的所有数据表,而 ACTIVE 标签显示所有为所选 Power PivotTable 选择的数据表。由于 Power PivotTable 为空,这意味着尚未选择任何数据表;因此,默认情况下会选择 ALL 标签,并显示数据模型中当前存在的两个表。此时,如果您单击 ACTIVE 标签,则“字段”列表将为空。
-
单击 ALL 中 PivotTable 字段列表中的表名。将显示带复选框的相应字段。
-
每个表格名称将在左侧显示此符号。
-
如果将光标置于此符号之上,将显示该数据表格的数据源和模型表格名称。
-
将“销售人员”从“销售人员”表拖动到 ROWS 区域。
-
Click the ACTIVE tab.
正如您所观察到的,“销售人员”字段如期显示在 PivotTable 中,“销售人员”表显示在 ACTIVE 标签下。
-
Click the ALL tab.
-
在销售表中单击“月份”和“订单金额”。
再次单击 ACTIVE 标签。两张表 - “销售额”和“销售人员”显示在 ACTIVE 标签下。
-
将“月份”拖到“列”区域。
-
将“区域”拖到“筛选器”区域。
-
单击区域筛选器框中 ALL 旁边的箭头。
-
Click Select Multiple Items.
-
选择 North 和 South,然后单击 OK。
以升序对列标签进行排序。
Power PivotTable 可以动态修改,以浏览和报告数据。
Excel Power Pivot - Basics of DAX
DAX (Data Analysis eXpression) 语言是 Power Pivot 的语言。DAX 由 Power Pivot 用于数据建模,它方便你用于自助 BI。DAX 是基于数据表和数据表中的列。请注意它不是基于表中的各个单元格,Excel 中的公式和函数就是这种情况。
你将学习数据模型中存在的两个简单计算 − 本章中的度量值列和度量值字段。
Extending the Data Model using Calculated Columns
假设你具有区域明智的产品销售数据以及数据模型中的产品目录。
使用此数据创建 Power PivotTable。
正如你所看到的,Power PivotTable 已汇总了所有区域的销售数据。假设你要知道每种产品获得的总利润。你了解每种产品的价格、销售成本和已售出单位数。
但是,如果你需要计算总利润,则需要在每个区域的数据表中再有两列 − 总产品价格和总利润。这是因为,数据透视图需要数据表中的列来汇总结果。
如你所知,总产品价格是产品价格 * 单位数,总利润是总金额 − 总产品价格。
你需要使用 DAX 表达式来添加度量值列,如下所示 −
-
单击 Power Pivot 窗口数据视图中的 East_Sales 选项卡以查看 East_Sales 数据表。
-
在功能区上点击“设计”选项卡。
-
Click Add.
突出显示标题为 − 添加列的右侧列。
在公式栏中键入 = [Product Price] * [No. of Units] 并按 Enter 。
将插入一个标题为 CalculatedColumn1 的新列,其中包含由你输入的公式计算的值。
-
双击新度量值列的标题。
-
将标题重命名为 TotalProductPrice 。
为总利润再添加一列度量值列,如下所示 −
-
在功能区上点击“设计”选项卡。
-
Click Add.
-
突出显示标题为 − 添加列的右侧列。
-
在公式栏中键入 = [TotalSalesAmount] − [TotaProductPrice] 。
-
Press Enter.
将插入一个标题为 CalculatedColumn1 的新列,其中包含由你输入的公式计算的值。
-
双击新度量值列的标题。
-
将标题重命名为毛利。
以同样的方式添加 North_Sales 数据表中的计算列。合并所有步骤,像如下进行-
-
在功能区上点击“设计”选项卡。
-
点击添加。右侧标题为-添加列的列被高亮。
-
在公式栏中输入= [Product Price] * [No. of Units] 并按下回车键。
-
由你输入的计算的值被插入到带有标题CalculatedColumn1的新列中。
-
双击新度量值列的标题。
-
将标题重命名为 TotalProductPrice 。
-
在功能区上点击“设计”选项卡。
-
点击添加。右侧标题为-添加列的列被高亮。
-
在公式栏中输入= [TotalSalesAmount] − [TotaProductPrice] 并按下回车键。由你输入的计算的值被插入到带有标题 CalculatedColumn1 的新列中。
-
双击新度量值列的标题。
-
将标题重命名为 Gross Profit 。
针对 South Sales 数据表和 West Sales 数据表重复上面给出的步骤。
你有必要的部分来汇总毛利。现在,创建动力透视表。
因为动力透视表中的计算列,你可以汇总 Gross Profit 且所有操作都可以在几个无差错步骤中完成。
你也可以按以下区域为产品进行汇总-
Calculated Field
假设你想要按产品和地区计算利润百分比。你可以通过向数据表添加计算字段来执行此操作。
-
在动力透视表窗口中,点击 East_Sales 表中的毛利列下方。
-
在公式栏中输入 EastProfit: = SUM ([Gross Profit]) / sum ([TotalSalesAmount]) 。
-
Press Enter.
计算字段EastProfit被插入到毛利列的下方。
-
右键点击计算字段-EastProfit。
-
从下拉列表中选择 Format 。
格式设置对话框会出现。
-
Select Number under Category.
-
在格式框中,选择百分比并单击确定。
计算出的字段 EastProfit 被格式化为百分比。
重复这些步骤来插入以下计算出的字段 −
-
North_Sales 数据表中的 NorthProfit。
-
South_Sales 数据表中的 SouthProfit。
-
West_Sales 数据表中的 WestProfit。
Note − 你不能为一个给定的名称定义多个计算出的字段。
单击 Power 透视表。你可以看到计算出的字段出现在表中。
-
从透视表字段列表中的表中选择字段 − EastProfit、NorthProfit、SouthProfit 和 WestProfit。
-
排列这些字段,以使毛利润和百分比利润同时出现。Power 透视表如下所示 −
Note − 在 Excel 较早版本中, Calculate Fields 被称为 Measures 。
Excel Power Pivot - Exploring Data
在上一章中,您学习了如何从一组普通数据表中创建 Power Pivot 表。在本章中,您将学习当数据表包含数千行时,如何使用 Power Pivot 表来探索数据。
为了更好地理解,我们将从一个访问数据库导入数据,该数据库是一个关系数据库。
Loading Data from Access Database
要从 Access 数据库加载数据,请按照给定的步骤操作 -
-
在 Excel 中打开一个新的空白工作簿。
-
在“数据模型”组中单击“管理”。
-
单击功能区上的 POWERPIVOT 选项卡。
“透视表”窗口会出现。
-
单击 Power Pivot 窗口中的“主页”选项卡。
-
单击获取外部数据组中的 From Database 。
-
从下拉列表中选择 From Access 。
表导入向导出现。
-
Provide Friendly connection name.
-
浏览到 Access 数据库文件 Events.accdb,即 Events 数据库文件。
-
单击“下一步 >”按钮。
Table Import 向导会显示用于选择如何导入数据的选项。
单击 Select from a list of tables and views to choose the data to import ,然后单击 Next 。
Table Import 向导会显示您已选取的 Access 数据库中的所有表格。选中所有的方框以选中所有的表格,然后单击“完成”。
Table Import 向导会显示 - Importing ,并呈现导入的状态。这可能需要几分钟的时间,您也可以单击 Stop Import 按钮来停止导入。
一旦数据导入完成,“导入表格向导”就会显示 - Success ,并显示导入的结果。单击 Close 。
透视表在数据视图的不同标签中显示所有的导入表格。
单击图表视图。
您可以观察到表格之间存在关系 - Disciplines and Medals 。这是因为当您从关系数据库(例如 Access)导入数据时,数据库中存在的相关性也导入到了透视表中的数据模型中。
Creating a PivotTable from the Data Model
使用先前导入的表格创建一个数据透视表,如下:
-
单击功能区上的“PivotTable”。
-
从下拉列表中选择数据透视表。
-
在随后出现的“创建数据透视表”对话框中选择“新建工作表”,然后单击“确定”。
在 Excel 窗口的新工作表中创建了一个空数据透视表。
在 Power 透视数据模型中列入的所导入表格将显示在数据透视表字段列表中。
-
将 Medals 表中的 NOC_CountryRegion 字段拖动到列区域。
-
将 Disciplines 表中的 Discipline 拖动到 ROWS 区域。
-
筛选学科以只显示五项体育:射箭、潜水、击剑、花样滑冰和速度滑冰。这可通过数据透视表字段区域或数据透视表本身中的行标签筛选器完成。
-
将表 Medals 中的 Medal 拖至 VALUES 区域。
-
再次从 Medals 表选择奖牌并将其拖动到筛选器区域。
数据透视表将填充选定区域的添加字段和布局。
Exploring Data with PivotTable
你可能只想显示奖牌数 > 80 的值。要执行此操作,请按照以下步骤操作:
-
单击“列标签”右边的箭头。
-
从下拉列表中选择 Value Filters 。
-
从第二个下拉列表中选择 Greater Than… 。
-
Click OK.
将会出现 Value Filter 对话框。在最靠右的框中输入 80,然后单击“确定”。
数据透视表只会显示奖牌总数高于 80 的地区。
你只需按照几个步骤,就可以用不同的表格生成你想要的特定报表。由于 Access 数据库中的表之间存在预先的关系,因此才能够实现这一点。由于你同时从数据库中导入了所有表格,因此 Power 透视会在其数据模型中重新创建关系。
Summarizing Data from Different Sources in Power Pivot
如果你从不同来源获取数据表,或者没有同时从数据库中导入表格,或者如果你在工作簿中创建了新的 Excel 表格并将它们添加到数据模型中,则必须创建希望用于在数据透视表中进行分析和汇总的表格之间的关系。
-
在工作簿中创建新的工作表。
-
创建 Excel 表格 - Sports(体育)。
将“体育”表格添加到数据模型中。
使用字段 SportID 在表格 Disciplines and Sports 之间创建关系。
将字段 Sport 添加到数据透视表中。
整理字段 - Discipline and Sport 在行区域。
Extending Data Exploration
您还可以进一步探索 Events 表中的数据。
创建 Events 和 Medals 表格之间的关系,字段为 DisciplineEvent 。
将表格 Hosts 添加到工作簿和数据模型中。
Extending the Data Model using Calculated Columns
为了将主机表格连接到任何其他表格,它应该具有一个字段,该字段的值唯一标识主机表格中的每一行。由于主机表格中不存在这样的字段,因此您可以在主机表格中创建计算列,以便其中包含唯一值。
-
转到 PowerPivot 窗口的数据视图中的主机表格。
-
在功能区上点击“设计”选项卡。
-
Click Add.
最右边的名为添加列的列被高亮显示。
-
在公式栏中键入以下 DAX 公式 = CONCATENATE ([Edition], [Season])
-
Press Enter.
使用上述 DAX 公式生成的值创建了一个新列,标题为 CalculatedColumn1 ,并且该列已填满。
右键单击新列,然后从下拉列表中选择重命名列。
在新列的标题中键入 EditionID 。
如您所见,列 EditionID 在 Hosts 表格中有唯一值。
Creating a Relationship Using Calculated Columns
如果您必须在 Hosts 表格和 Medals 表格之间创建关系,则列 EditionID 还应存在于 Medals 表格中。按照以下步骤在 Medals 表格中创建计算列-
-
单击 Power Pivot 中的数据视图中的 Medals 表格。
-
在功能区上点击“设计”选项卡。
-
Click Add.
在公式栏中键入 DAX 公式 = YEAR ([EDITION]) ,然后按 Enter。
将创建的新列重命名为 Year,然后单击 Add 。
-
在公式栏中键入以下 DAX 公式 = CONCATENATE ([Year], [Season])
-
将创建的新列重命名为 EditionID 。
正如您所观察到的,Medals 表格中的 EditionID 列与 Hosts 表格中的 EditionID 列具有相同的值。因此,您可以使用 EditionID 字段在表格之间(Medals 和 Sports)创建关系。
-
切换到 PowerPivot 窗口中的图表视图。
-
使用 Gemini 将表(奖牌和主办方)与从计算列获取的字段建立关系,即 EditionID 。
现在可以将主办方表中的字段添加到 Power 透视表中。
Excel Power Pivot - Flattened
当数据具有很多级别时,有时阅读数据透视表报表会变得很麻烦。
例如,考虑以下数据模型。
我们将创建 Power PivotTable 和 Power Flattened PivotTable 以了解布局。
Creating a PivotTable
您可以按以下方式创建 Power PivotTable −
-
单击 PowerPivot 窗口中功能区的“主页”选项卡。
-
Click PivotTable.
-
从下拉列表中选择数据透视表。
将创建一个空的数据透视表。
-
将字段 - 销售员、区域和产品从数据透视表字段列表拖至 ROWS 区域。
-
将字段 - TotalSalesAmount 从表 - 东部、北部、南部和西部拖至 ∑ VALUES 区域。
如您所见,阅读此类报告有点麻烦。如果条目数量增多,难度会更大。
Power Pivot 提供了一个使用扁平数据透视表更好地表示数据的解决方案。
Creating a Flattened PivotTable
您可以按照以下步骤创建 Power 扁平数据透视表 −
-
单击 PowerPivot 窗口中功能区的“主页”选项卡。
-
Click PivotTable.
-
从下拉列表中选择 Flattened PivotTable 。
将出现 Create Flattened PivotTable 对话框。选择新建工作表,然后单击确定。
您可以观察到数据在此数据透视表中已展开。
Note − 在本例中,销售员、区域和产品仅在 ROWS 区域中,如前例所示。然而,在数据透视表布局中,这三个字段显示为三列。
Exploring Data in Flattened PivotTable
假设你要汇总产品空调的销售数据。您可以使用扁平数据透视表以下述方式简单地完成此操作 −
-
单击列标题 - 产品旁边的箭头。
-
选中空调复选框,取消选中其他复选框。单击确定。
扁平数据透视表已筛选为空调销售数据。
您可以通过从 COLUMNS 区域将 ∑ VALUES 拖到 ROWS 区域来使它看起来更扁平。
在 ∑ VALUES 区域中重命名求和值的自定义名称,使其更具意义,如下所示 −
-
单击求和值,例如 East 的 TotalSalesAmount 总和。
-
从下拉列表中选择“值字段设置”。
-
将自定义名称更改为 East TotalSalesAmount。
-
针对其他三个求和值重复这些步骤。
您还可以总结已销售的单位数。
-
将各个表格中的单位数拖动到 ∑ VALUES 区域,即 East_Sales、North_Sales、South_Sales 和 West_Sales。
-
将这些值重命名为 East Total No. of Units、North Total No. of Units、South Total No. of Units 和 West Total No. of Units。
如您所见,上述两个表格中都有包含空值的行,因为每位销售人员代表一个地区,而每个地区也仅由一位销售人员代表。
-
选择包含空值的行。
-
右键单击,然后单击下拉列表中的隐藏。
所有包含空值的行都将隐藏。
如您所见,虽然包含空值的行没有显示,但销售人员的信息也隐藏了。
-
单击列标题 - 销售人员。
-
单击功能区中的“分析”选项卡。
-
单击字段设置。将显示“字段设置”对话框。
-
单击“布局和打印”选项卡。
-
选中 Repeat Item Labels 框。
-
Click OK.
如您所见,销售人员信息已显示且包含空值的行已隐藏。此外,报表中的区域列是多余的,因为值列中的值具有自解释性。
将区域字段拖出区域。
反转 ROWS 区域中销售人员和产品字段的顺序。
您已在一个简洁的报表中合并源自 Power Pivot 中六个表格的数据。
Excel Power Pivot Charts - Creation
基于数据模型并从 Power 透视窗口创建的透视图表是一个 Power 透视图表。虽然它拥有与 Excel 透视图表类似的一些功能,但还有其他一些功能使其更加强大。
在本章中,你将学习有关 Power 透视图表的内容。从今以后,我们将称其为透视图表以示简单。
Creating a PivotChart
假设你希望基于以下数据模型创建透视图表。
-
单击 Power Pivot 窗口功能区上的“主页”选项卡。
-
Click PivotTable.
-
从下拉列表中选择“数据透视表”。
出现 Create PivotChart 对话框。选择新建工作表,然后单击确定。
在 Excel 窗口的新工作表上创建了一个空透视图表。
如你所见,数据模型中的所有表格会显示在“透视表字段”列表中。
-
单击“透视表字段”列表中的销售人员表格。
-
将字段销售人员和区域拖动到 AXIS 区域。
透视表中将出现两个轴字段按钮,分别对应于两个选定字段。这些是轴字段按钮。字段按钮用于筛选透视表中显示的数据。
将 TotalSalesAmount 从四张表(East_Sales、North_Sales、South_Sales 和 West_Sales)中的每张表拖动到 ∑ VALUES 区域。
工作表上会出现以下内容 −
-
在透视图表中,默认显示柱形图。
-
在 LEGEND 区域,添加了 ∑ VALUES。
-
值显示在透视图表中的图例中,标题为值。
-
透视图表上显示了值字段按钮。你可以删除图例和值字段按钮,使透视图表看起来更简洁。
-
单击透视图表右上角的按钮。出现 Chart Elements 下拉列表。
取消选中图表元素列表中的图例复选框。图例将从透视图表中删除。
-
右键单击值字段按钮。
-
从下拉列表中选择隐藏图表上的值字段按钮。
图表上的值字段按钮被删除。
Note − 字段按钮和/或图例的显示取决于透视图表的上下文。你需要决定需要显示哪些内容。
PivotChart Fields List
与 Power 透视表一样,Power 透视图表字段列表也包含两个选项卡 - ACTIVE 和 ALL。在 ALL 选项卡下,显示数据模型中的所有数据表。在 ACTIVE 选项卡下,显示已将字段添加到透视图表的那些表。
同样,这些区域与 Excel 透视图表中的区域相同。有四个区域:
-
AXIS (Categories)
-
LEGEND (Series)
-
∑ VALUES
-
FILTERS
正如你在上一个章节中看到的,图例中填充了∑值。此外,已将字段按钮添加到数据透视表中,以便于过滤所显示的数据。
Filters in PivotChart
你可以在图表上使用轴字段按钮来过滤所显示的数据。单击轴字段按钮的箭头 - 区域。
出现的下拉列表如下所示 −
你可以选择你想要显示的值。或者,你可以将该字段放在过滤器区域中以过滤值。
将区域字段拖放到过滤器区域。透视报表筛选器按钮 - 区域出现在数据透视表上。
单击报表筛选器按钮的箭头 - 区域。出现的下拉列表如下所示 −
你可以选择你想要显示的值。
Table and Chart Combinations
Power Pivot 为您提供各种 Power 透视表和 Power 透视图表组合,以用于数据探索、可视化和报告。您已在前面的章节中学习了数据透视表和数据透视图表。
在本章节中,您将了解如何从 Power 透视窗口中创建表格和图表组合。
考虑我们在动态数据透视图中用于图示的以下数据模型:
Chart and Table (Horizontal)
利用这个选项,您可以在同一张工作表中创建一个 Power 透视图表和一个 Power 透视表,它们以水平方式彼此相邻。
-
单击 Power 透视窗口中的“主页”选项卡。
-
Click PivotTable.
-
从下拉列表中选择“图表和表格(水平)”。
将出现“创建数据透视表和数据透视表(水平)”对话框。选择“新建工作表”,然后单击“确定”。
空白的数据透视图表和空白的数据透视表将显示在新的工作表中。
-
Click on the PivotChart.
-
将 NOC_CountryRegion 从奖牌表拖动到“轴”区域。
-
将奖牌从奖牌表拖动到“∑ 值”区域。
-
右键单击图表,然后从下拉列表中选择 Change Chart Type 。
-
Select Area Chart.
-
将图表标题更改为 Total No. of Medals − Country Wise 。
正如您所见,美国拥有奖牌数量最多(>4500 枚)。
-
Click on the PivotTable.
-
将运动从运动表拖动到行区域。
-
将奖牌从奖牌表拖动到求和值区域。
-
将 NOC_CountryRegion 从奖牌表拖动到筛选器区域。
-
将 NOC_CountryRegion 字段筛选为值 USA。
将 PivotTable Report 布局更改为 Outline 表单。
-
从运动表中取消选择运动。
-
将性别从奖牌表拖动到行区域。
Chart and Table (Vertical)
使用此选项,您可以在同一工作表的垂直下方创建 Power PivotChart 和 Power PivotTable。
-
单击 Power 透视窗口中的“主页”选项卡。
-
Click PivotTable.
-
从下拉列表中选择图表和表格(垂直)。
将会显示 Create PivotChart and PivotTable (Vertical) 对话框。选择新建工作表,然后单击确定。
空 PivotChart 和空 PivotTable 将垂直显示在新的工作表上。
-
Click on the PivotChart.
-
将年份从奖牌表拖动到轴区域。
-
将奖牌从奖牌表拖动到求和值区域。
-
右键单击图表,然后从下拉列表中选择更改图表类型。
-
Select Line Chart.
-
选中图表元素中的数据标签复选框。
-
将图表标题更改为 Total No. of Medals – Year Wise 。
正如您所见,2008 年拥有奖牌数量最多(2450 枚)。
-
Click on the PivotTable.
-
将运动从运动表拖动到行区域。
-
将性别从奖牌表拖动到行区域。
-
将奖牌从奖牌表拖动到求和值区域。
-
将年份从奖牌表拖动到筛选器区域。
-
将年字段筛选到值 2008。
-
将透视表的报表布局更改为提纲形式。
-
使用大于或等于 80 的值筛选器来筛选运动字段。
Excel Power Pivot - Hierarchies
数据模型中的层次结构是数据表中嵌套列的列表,在 Power PivotTable 中使用时被视为单一项目。例如,如果数据表中具有国家/地区、州、城市这些列,则可以定义一个层次结构,将这三列组合成一个字段。
在 Power PivotTable 字段列表中,层次结构显示为一个字段。因此,您可以只向数据透视表添加一个字段,而不是层次结构中的三个字段。此外,它使您可以以一种有意义的方式在嵌套级别中向上或向下移动。
考虑此章节中插图的以下数据模型。
Creating a Hierarchy
您可以在数据模型的图表视图中创建层次结构。请注意,您只能根据单个数据表创建层次结构。
-
按此顺序单击数据表“Medal”中的列 − “Sport”、“DisciplineID”和“Event”。请记住,此顺序对于创建有意义的层次结构非常重要。
-
Right-click on the selection.
-
从下拉列表中选择“Create Hierarchy”。
将使用三个选定字段作为子级的层次结构字段创建。
Renaming a Hierarchy
若要重命名层次结构字段,请执行以下操作 −
-
Right click on Hierarchy1.
-
从下拉列表中选择“Rename”。
键入 EventHierarchy 。
Creating a PivotTable with a Hierarchy in Data Model
可以使用在数据模型中创建的层次结构创建 Power PivotTable。
-
单击 Power Pivot 窗口中功能区的“PivotTable”选项卡。
-
单击功能区上的“PivotTable”。
显示 Create PivotTable 对话框。选择“New Worksheet”,然后单击“OK”。
在新的工作表中创建空的透视表。
在“透视表字段”列表中, EventHierarchy 显示为“Medals”表中的一个字段。Medals 表中的其他字段已折叠并显示为“More Fields”。
-
单击 EventHierarchy 前面的箭头。
-
单击“更多字段”前面的箭头。
将显示 EventHierarchy 下的字段。Medal 表中的所有字段将显示在“更多字段”下。
正如您所看到的,您添加到层次结构中的三个字段也会出现在 More Fields 下方,并带有复选框。如果您不希望它们出现在 More Fields 下方的“透视表字段”列表中,则必须隐藏 Power Pivot 窗口的数据视图中 Medals 数据表中的列。您可以随时取消隐藏它们。
按如下方式将字段添加到透视表 −
-
将 EventHierarchy 拖动到“ROWS”区域。
-
将 Medal 拖动到 ∑ VALUES 区域。
“Sport”字段的值以正号 (+) 开头显示在透视表中。每项运动的奖牌数显示在后面。
-
单击Aquatics前面的+符号。将显示Aquatics下的DisciplineID字段值。
-
单击出现的子级D22。将显示D22下的Event字段值。
正如您所看到的,奖牌数给出了按父级 − DisciplineID 汇总的赛事,按父级 − “Sport” 进一步汇总的赛事。
Creating a Hierarchy based on Multiple Tables
假设您要在透视表中显示学科而不是学科 ID,以使其成为更易于阅读和理解的汇总。为此,您需要在 Medals 表中包含 Discipline 字段,而您知道它并不存在。Discipline 字段位于 Disciplines 数据表中,但您无法用来自多个表的字段创建层次结构。不过,有一种方法可以从其他表中获取所需的字段。
您知道 Medals 表和 Disciplines 表是相关的。您可以通过使用 DAX 与关系创建列,将 Disciplines 表中的 Discipline 字段添加到 Medals 表中。
-
单击 Power Pivot 窗口中的数据视图。
-
在功能区上点击“设计”选项卡。
-
Click Add.
表右侧的列 – 添加列被高亮显示。
在公式栏中输入 = RELATED (Disciplines [Discipline]) 。新列 − CalculatedColumn1 创建,值与 Disciplines 表中的 Discipline 字段值相同。
将 Medals 表中如此获取的新列重命名为 Discipline。接下来,你必须从层次结构中移除 DisciplineID,并添加 Discipline(这将在后续章节中学习)。
Removing a Child Level from a Hierarchy
你可以观察到,层次结构仅在图表视图中可见,在数据视图中不可见。因此,你仅能在图表视图中编辑层次结构。
-
单击 Power Pivot 窗口中的图表视图。
-
右键单击 EventHierarchy 中的 DisciplineID。
-
从下拉列表中选择 Remove from Hierarchy 。
确认对话框出现。单击 Remove from Hierarchy 。
DisciplineID 字段从层次结构中删除。请记住,你已从层次结构中移除该字段,但源字段仍存在于数据表中。
接下来,你需要将 Discipline 字段添加到 EventHierarchy。
Adding a Child Level to a Hierarchy
你可以向现有层次结构 - EventHierarchy 添加 Discipline 字段,如下所示 −
-
单击 Medals 表中的该字段。
-
将其拖动到 EventHierarchy 中下方的 Events 字段。
Discipline 字段会添加到 EventHierarchy。
你可以看到,EventHierarchy 中的字段顺序为运动 – 项目 – 学科。但是,你应知道其顺序应为运动 – 学科 – 项目。因此,你需要更改字段顺序。
Changing the Order of a Child Level in a Hierarchy
要将字段 Discipline 移到字段 Sport 之后的位置,请执行以下操作 −
-
右键单击 EventHierarchy 中的字段 Discipline。
-
从下拉列表中选择向上移动。
字段顺序将更改为运动 – 学科 – 项目。
PivotTable with Changes in Hierarchy
若要查看你在事件层级中对数据透视表所做的更改,你不需要新建数据透视表。你可以在现有的数据透视表上查看它们。
单击 Excel 窗口中的包含数据透视表的工作表。
正如你观察到的,在数据透视表字段列表中,事件层级中的子级别反映了你在数据模型中层级中所做的更改。同样的更改也会相应地反映在数据透视表中。
单击数据透视表中水上运动前的加号。子级别将作为学科字段的值显示。
Creating a Hierarchy in Other Ways
除了在前面的部分中创建层级的方式外,你还可以另外两种方式创建层级。
-
单击图表视图中奖牌数据表右上角的创建层级按钮。
将在表格中创建新的层级,其中没有任何字段。
按该顺序将字段年份和赛季拖到新层级。层级会显示子级别。
-
创建相同层级的另一种方法如下−[style="arabic"]
-
在图表视图中右击奖牌数据表中的字段年份。
-
从下拉列表中选择“Create Hierarchy”。
-
会在表中创建一个新的层级,其中年份是子字段。
将字段赛季拖到层级。层级会显示子级别。
Deleting a Hierarchy
你可以通过以下方式从数据模型中删除层级−
-
右击层级。
-
从下拉列表中选择删除。
将显示 Confirm 对话框。单击 Delete from Model 。
分层结构已被删除。
Calculations Using Hierarchy
您可以通过分层结构创建计算。在 EventsHierarchy 中,您可以按以下方式将子级别奖牌数显示为其父级别奖牌数的百分比 −
-
右键单击 Event 的奖牌计数值。
-
从下拉列表中选择“值字段设置”。
将出现Value Field Settings对话框。
-
单击 Show Values As 选项卡。
-
从列表中选择“父行总计百分比”,并单击“确定”。
子级别显示为父总计的百分比。您可以通过对父项的子级别的百分比值求和来验证这一点。总和应为 100%。
Drilling Up and Drilling Down a Hierarchy
您可以使用快速浏览工具快速查看分层结构的各级别。
-
在数据透视表中单击 Event 字段的值。
-
单击快速浏览工具 - 该工具显示在所选值所在单元格的右下角。
出现了 Explore box with Drill Up 选项。这是因为从 Event 中您只能向上钻取,因为其下方没有子级别。
单击 Drill Up 。
数据透视表数据向上钻取至 Disciplin 。
单击Quick Explore tool-出现于包含值的单元格的右下角。
会出现一个包含“向上钻取”和“向下钻取”选项的浏览框。这是因为从 Discipline 中您可以向上钻取至 Sport 或向下钻取至 Event。
通过这种方式,您可以快速浏览分层结构。
Excel Power Pivot - Aesthetic Reports
您可以利用資料模型中的 Power Pivot 資料建立美觀的資料分析報告。
其重要特性有 −
-
您可以使用樞紐圖表產生資料的視覺化報告。您可以使用報告版面來結構您的樞紐分析表,以使其易於閱讀。
-
您可以插入切片器來篩選報告中的資料。
-
您可以使用一般切片器來同時套用於樞紐圖表和樞紐分析表,這些切片器在同一個報告中。
-
在您的最後報告準備好後,您可以選擇隱藏切片器的顯示。
在本章節中,您將會了解如何取得 Power Pivot 中可用的選項所產生的報告。
考虑此章节中插图的以下数据模型。
Reports based on Power PivotChart
建立 Power Pivot 圖表,如下所示−
-
在 PowerPivot 窗口中的功能区上单击“主页”选项卡。
-
Click PivotTable.
-
从下拉列表中选择“数据透视表”。
-
在“创建数据透视表”对话框中单击“@ {s0}”。
将在 Excel 窗口中的新工作表中创建一个空的数据透视表。
-
将“运动”从“奖牌”表拖动到“轴”区域。
-
将“奖牌”从“奖牌”表拖动到“Σ 值”区域。
-
单击功能区上 PIVOTTABLE TOOLS 中的“分析”选项卡。
-
单击“筛选组”中的“插入切片器”。将出现“插入切片器”对话框。
-
单击“奖牌”表中的字段“@ {s1}”。
-
Click OK.
切片器 NOC_CountryRegion 出现。
-
Select USA.
-
将“性别”从“奖牌”表拖动到“性别”区域。
-
右键单击“数据透视表”。
-
从下拉列表中选择“更改图表类型”。
将出现“更改图表类型”对话框。
单击“堆积柱形图”。
-
插入“运动”字段的切片器。
-
将 Discipline 从 Disciplines 表拖到 AXIS 区域。
-
从轴区域中删除字段“运动”。
-
在切片器 – 运动中选择“水上运动”。
Report Layout
创建数据透视表如下 −
-
在 PowerPivot 窗口中单击“功能区”上的“主页”选项卡。
-
Click on PivotTable.
-
单击下拉列表中的“数据透视表”。“创建数据透视表”对话框将显示。
-
单击“新建工作表”,然后单击“确定”。将为空的数据透视表在新的工作表中创建。
-
将 Medals 表中的 NOC_CountryRegion 拖动到 AXIS 区域。
-
将 Medals 表中的 Sport 拖动到 COLUMNS 区域。
-
将 Disciplines 表中的 Discipline 拖动到 COLUMNS 区域。
-
将 Medal 拖动到 ∑ VALUES 区域。
单击“列标签”旁边的箭头按钮,然后选择“水生运动”。
-
单击“行标签”旁边的箭头按钮。
-
从下拉列表中选择“值筛选器”。
-
从第二个下拉列表中选择“大于或等于”。
在“值筛选器”对话框中“奖牌计数大于或等于”旁边的框中键入 80。
-
单击“功能区”上的“数据透视表工具”中“设计”选项卡。
-
Click on Subtotals.
-
从下拉列表中选择“ Do Not Show Subtotals ”。
小计列(水生运动合计)移除。
单击“报表布局”,然后从下拉列表中选择“ Show in Outline Form ”。
选中“条带行”框。
字段名称显示的行标签和列标签位置,并且报表清晰明了。
Using a Common Slicer
创建数据透视图和数据透视表,彼此相邻。
-
单击 PowerPivot 选项卡上“功能区”的“主页”选项卡。
-
Click PivotTable.
-
从下拉列表中选择“图表和表格(水平)”。
创建数据透视表和数据透视图 (水平) 对话框将显示。
选择新建工作表,然后单击确定。在新建工作表中,空数据透视图和空数据透视表将并排显示。
-
Click PivotChart.
-
将 Discipline 从 Disciplines 表拖到 AXIS 区域。
-
将 Medal 从 Medals 表拖到 ∑ VALUES 区域。
-
Click PivotTable.
-
将 Discipline 从 Disciplines 表拖到 ROWS 区域。
-
将 Medal 从 Medals 表拖到 ∑ VALUES 区域。
-
单击功能区上 PIVOTTABLE TOOLS 中的“分析”选项卡。
-
单击插入筛选器。插入筛选器对话框将显示。
-
单击 Medals 表中的 NOC_CountryRegion 和 Sport。
-
Click OK.
将显示两个筛选器 - NOC_CountryRegion 和 Sport。排列并调整其大小使其与数据透视表正确对齐。
-
在 NOC_CountryRegion 筛选器中选择 USA。
-
在 Sport 筛选器中选择 Aquatics。数据透视表将根据所选值进行筛选。
如您所见,数据透视图未进行筛选。要使用相同筛选器筛选数据透视图,您不需要为数据透视图重新插入筛选器。您可以使用为数据透视表使用的相同筛选器。
-
Click on NOC_CountryRegion Slicer.
-
单击功能区上 SLICER TOOLS 中的 OPTIONS 选项卡。
-
单击筛选器组中的 Report Connections 。 Report Connections 对话框将针对 NOC_CountryRegion 筛选器显示。
您会看到对话框中列出了工作簿中的所有数据透视表和数据透视图。
-
单击与所选数据透视表在同一工作表中的数据透视图,然后单击确定。
-
Repeat for Sport Slicer.
数据透视图也将根据在两个筛选器中所选的值进行筛选。
接下来,您可以在数据透视图和数据透视表中添加详细信息。
-
Click the PivotChart.
-
将性别拖到图例区域。
-
右键单击“数据透视表”。
-
Select Change Chart Type.
-
在更改图表类型对话框中选择堆积柱形图。
-
Click on the PivotTable.
-
将事件拖到行区域。
-
单击“功能区”上的“数据透视表工具”中“设计”选项卡。
-
Click Report Layout.
-
从下拉列表中选择轮廓表单。