Excel 仪表板 - Power 数据透视表和数据透视图
当您的数据集很大时，您可以使用可以处理数亿行数据的 Excel Power Pivot。 数据可以在外部数据源中，Excel Power Pivot 构建了一个在内存优化模式下工作的数据模型。 您可以执行计算、分析数据并得出得出结论和决策报表。 该报表可以是 Power PivotTables(数据透视表)或 Power PivotCharts(数据透视图)或两者的组合。
您可以将 Power Pivot 用作临时报告和分析解决方案。 因此，具有 Excel 实践经验的人可以在几分钟内执行高端数据分析和决策制定，并且是包含在仪表板中的重要资产。
Power Pivot 的使用
您可以将 Power Pivot 用于以下用途 −
- 执行强大的数据分析并创建复杂的数据模型。
- 快速整合来自多个不同来源的大量数据。
- 执行信息分析并以交互方式分享见解。
- 创建关键绩效指标 (KPI)。
- 创建 Power PivotTables(数据透视表)。
- 创建 Power PivotCharts(数据透视图)。
数据透视表和 Power PivotTables(数据透视表)的区别
Power PivotTables(数据透视表)在布局上类似于数据透视表，但有以下区别 −
数据透视表基于 Excel 表格，而 Power PivotTables(数据透视表)基于属于数据模型一部分的数据表。
数据透视表基于单个 Excel 表或数据范围，而 Power PivotTables(数据透视表)可以基于多个数据表，前提是将它们添加到数据模型中。
数据透视表是从 Excel 窗口创建的，而 Power PivotTables(数据透视表)是从数据透视表窗口创建的。
创建 Power PivotTables(数据透视表)
假设您有两个数据表——数据模型中的销售人员和销售人员。 要从这两个数据表创建 Power PivotTables(数据透视表)，请执行以下操作 −
在 PowerPivot 窗口的功能区上单击"主页"选项卡。
单击功能区上的"数据透视表"。
单击下拉列表中的数据透视表。
创建数据透视表对话框出现。 单击新建工作表。
单击"确定"按钮。 在 Excel 窗口中创建新工作表，并出现一个空的 Power PivotTables(数据透视表)。
如您所见，Power PivotTables(数据透视表)的布局类似于数据透视表。
数据透视表字段列表显示在工作表的右侧。 在这里，您会发现与数据透视表的一些不同之处。 Power PivotTables(数据透视表)字段列表有两个选项卡 − ACTIVE 和 ALL，显示在标题下方和字段列表上方。 所有选项卡突出显示。 ALL 选项卡显示数据模型中的所有数据表，而 ACTIVE 选项卡显示为当前 Power PivotTables(数据透视表)选择的所有数据表。
单击"全部"下"数据透视表字段"列表中的表名称。
将出现带有复选框的相应字段。
每个表名的左侧都有符号
。如果将光标放在该符号上，将显示该数据表的数据源和模型表名称。
- 将 Salesperson 从 Salesperson 表拖到 ROWS 区域。
- 单击"活动"选项卡。
字段 Salesperson 出现在 Power PivotTable 中，表 Salesperson 出现在 ACTIVE 选项卡下。
- 单击"全部"选项卡。
- 点击"销售额"表中的"月份"和"订单金额"。
- 单击"活动"选项卡。
两个表 – Sales 和 Salesperson 都显示在 ACTIVE 选项卡下。
- 将月份拖到 COLUMNS 区域。
- 将 Region 拖到 FILTERS 区域。
- 单击区域过滤器框中"全部"旁边的箭头。
- 单击"选择多个项目"。
- 点击"北"和"南"。
- 点击确定按钮。 按升序对列标签进行排序。
可以动态修改 Power PivotTable 以浏览和报告数据。
创建 Power PivotCharts(数据透视图)
Power PivotCharts(数据透视图)是一种基于数据模型并从 Power Pivot 窗口创建的数据透视图。 虽然它有一些类似于 Excel 数据透视图的功能，但还有其他功能使其更强大。
假设您要基于以下数据模型创建 Power PivotCharts(数据透视图)。
- 在 Power Pivot 窗口中单击功能区上的"主页"选项卡。
- 单击"数据透视表"。
- 单击下拉列表中的数据透视图。
出现创建数据透视图对话框。 单击"新建工作表"。
单击"确定"按钮。 在 Excel 窗口的新工作表上创建一个空的数据透视图。 在本章中，当我们说数据透视图时，我们指的是 Power PivotCharts(数据透视图)。
如您所见，数据模型中的所有表都显示在数据透视图字段列表中。
- 单击"数据透视图字段"列表中的"Salesperson"表。
- 将字段 – Salesperson 和 Region 拖到 AXIS 区域。
两个选定字段的按钮出现在数据透视图中。 这些是轴字段按钮。 字段按钮的用途是过滤数据透视图上显示的数据。
将 TotalSalesAmount 从 4 个表（East_Sales、North_Sales、South_Sales 和 West_Sales）中的每一个拖到 ∑ VALUES 值区域。
如您所见，工作表上出现以下内容 −
- 在数据透视图中，默认显示柱形图。
- 在图例区域，∑ 添加了 VALUES。
- 值显示在数据透视图中的图例中，标题为值。
- 值字段按钮显示在数据透视图中。
您可以删除图例和值字段按钮以使数据透视图看起来更整洁。
单击数据透视图右上角的
< /li>
按钮。在图表元素中取消选择图例。
右键单击值字段按钮。
单击下拉列表中的隐藏图表上的值字段按钮。
图表上的值字段按钮将被隐藏。
请注意，字段按钮和/或图例的显示取决于数据透视图的上下文。 您需要决定需要显示的内容。
与 Power PivotTable 的情况一样，Power PivotChart 字段列表也包含两个选项卡 − ACTIVE 和 ALL。 此外，还有4个区域 −
- AXIS（类别）
- 图例（系列）
- ∑ 值
- 过滤器
如您所见，图例中填充了 ∑ 值。 此外，字段按钮被添加到数据透视图中，以便于过滤正在显示的数据。 您可以单击字段按钮上的箭头并选择/取消选择要在 Power PivotCharts(数据透视图)中显示的值。
表格和图表组合
Power Pivot 为您提供 Power PivotTables(数据透视表)和 Power PivotCharts(数据透视图)的不同组合，用于数据浏览、可视化和报告。
考虑 Power Pivot 中的以下数据模型，我们将使用它来进行说明 −
您可以在 Power Pivot 中使用以下表格和图表组合。
图表和表格（水平）- 您可以创建一个 Power PivotCharts(数据透视图)和一个 Power PivotTables(数据透视表)，它们在同一工作表中水平相邻。
图表和表格（垂直）- 您可以创建一个 Power PivotCharts(数据透视图)和一个 Power PivotTables(数据透视表)，它们在同一工作表中垂直放置在另一个下方。
当您单击 Power Pivot 窗口中功能区上的数据透视表时，出现的下拉列表中提供了这些组合以及其他一些组合。
Power Pivot 中的层次结构
您可以使用 Power Pivot 中的层次结构进行计算以及向上钻取和向下钻取嵌套数据。
考虑以下数据模型以用于本章中的说明。
您可以在数据模型的图表视图中创建层次结构，但只能基于单个数据表。
依次单击数据表 Medal 中的列 – Sport、DisciplineID 和 Event。 请记住，顺序对于创建有意义的层次结构很重要。
右键单击所选内容。
在下拉列表中单击"创建层次结构"。
已创建具有三个选定字段作为子级别的层次结构字段。
- 右键单击层次结构名称。
- 在下拉列表中单击"重命名"。
- 键入一个有意义的名称，例如 EventHierarchy。
您可以使用在数据模型中创建的层次结构创建 Power PivotTables(数据透视表)。
- 创建 Power PivotTables(数据透视表)。
如您所见，在数据透视表字段列表中，EventHierarchy 显示为 Medals 表中的字段。 Medals 表中的其他字段已折叠并显示为更多字段。
- 单击 EventHierarchy 前面的箭头
。 - 单击更多字段前面的箭头 。
将显示 EventHierarchy 下的字段。 Medals 表中的所有字段将显示在"更多字段"下。
在Power PivotTable中添加字段如下 -
- 将 EventHierarchy 拖到 ROWS 区域。
- 将 Medal 拖到 ∑ VALUES 区域。
如您所见，Sport 字段的值显示在 Power PivotTables(数据透视表)中，前面带有一个 + 号。 显示每项运动的奖牌数。
点击 Aquatics 前的 + 号。 将显示 Aquatics 下的 DisciplineID 字段值。
点击出现的子 D22。 将显示 D22 下的 Event 事件字段值。
正如您所观察到的，奖牌数是针对 Event 给出的，在父级别 - DisciplineID 中汇总，在父级别 - Sport 中进一步汇总。
在 Power PivotTables(数据透视表)中使用层次结构进行计算
您可以使用 Power PivotTables(数据透视表)中的层次结构创建计算。 例如，在 Event 事件层次结构中，您可以显示编号。 子级别的奖牌数占总奖牌数的百分比。 其父级别的奖牌如下 –
- 右键单击 Event 的奖牌值计数。
- 在下拉列表中单击"值字段设置"。
值字段设置对话框出现。
- 单击"将值显示为"选项卡。
- 单击"将值显示为"框。
- 单击"父行总计的百分比"。
- 单击"确定"按钮。
如您所见，子级显示为父级总计的百分比。 您可以通过对父项的子级的百分比值求和来验证这一点。 总和为 100%。
向上钻取和向下钻取层次结构
您可以使用"快速浏览"工具在 Power PivotTables(数据透视表)的层次结构中快速向上钻取和向下钻取。
单击 Power PivotTables(数据透视表)中 Event 字段的值。
单击快速浏览工具 -
，它显示在包含所选值的单元格的右下角。
带有"向上钻取"选项的"分析"框出现。 这是因为您只能从 Event 中向上钻取，因为它下面没有子级别。
单击"向上钻取"。 Power PivotTables(数据透视表)数据被钻取到项目级别。
单击快速浏览工具 -
，它显示在包含值的单元格的右下角。
显示"分析"框，其中显示"向上钻取"和"向下钻取"选项。 这是因为您可以从 Discipline 向上钻取到 Sport 或向下钻取到 Event 级别。
这样您就可以在 Power PivotTables(数据透视表)中快速上下移动层次结构。
使用通用切片器
您可以插入切片器并在 Power PivotTables 和 Power PivotCharts 中共享它们。
创建一个水平相邻的 Power PivotChart 和 Power PivotTable。
点击"Power PivotChart"。
将 Discipline 从 Disciplines 表拖到 AXIS 区域。
将 Medal 从 Medals 表拖到 ∑ VALUES 区域。
单击 Power PivotTable。
将 Discipline 从 Disciplines 表拖到 ROWS 区域。
将 Medal 从 Medals 表拖到 ∑ VALUES 区域。
- 单击功能区上"数据透视工具"中的"分析"选项卡。
- 点击"插入切片器"。
出现"插入切片器"对话框。
- 点击奖牌表中的 NOC_CountryRegion 和 Sport。
- 点击确定。
出现两个切片器 – NOC_CountryRegion 和 Sport。
排列它们并调整它们的大小，使其在 Power PivotTable 旁边正确对齐，如下所示。
- 在 NOC_CountryRegion Slicer 中点击 USA。
- 点击 Sport Slicer 中的 Aquatics。
Power PivotTable 被筛选为选定的值。
如您所见，Power PivotChart 未被筛选。 要使用相同的筛选器筛选 Power PivotChart，您可以使用与 Power PivotTable 相同的切片器。
- 点击 NOC_CountryRegion 切片器。
- 单击功能区上"切片工具"中的"选项"选项卡。
- 单击"切片器"组中的"报表连接"。
为 NOC_CountryRegion 切片器显示报告连接对话框。
您可以看到，工作簿中的所有 Power PivotTables 和 Power PivotCharts 都列在对话框中。
单击与所选 Power PivotTable 位于同一工作表中的 Power PivotChart。
单击"确定"按钮。
对 Sport 切片器重复上述操作。
Power PivotChart 也被过滤为在两个切片器中选择的值。
接下来，您可以向 Power PivotChart 和 Power PivotTable 添加更多详细信息。
- 单击 Power PivotChart。
- 将性别(Gender)拖到图例区域。
- 右键单击 Power PivotChart。
- 点击更改图表类型。
- 在"更改图表类型"对话框中选择"堆积柱"。
- 点击 Power PivotTable。
- 将 Event 拖到 ROWS 区域。
- 单击功能区上"可透视工具"中的"设计"选项卡。
- 单击"报告布局"。
- 在下拉列表中单击"大纲表单"。
仪表板创建美观的报告
您可以使用 Power PivotTables 和 Power PivotCharts 创建美观的报告，并将它们包含在仪表板中。 正如您在上一节中看到的，您可以使用报表布局选项来选择报表的外观。 例如，选择"以大纲形式显示"并选择"带状行"，您将获得如下所示的报告。
正如您所观察到的，字段名称出现在行标签和列标签的位置，并且报告看起来比较美观。
您可以在"选择"窗格中选择要在最终报告中显示的对象。 例如，如果您不想显示您创建和使用的切片器，您可以通过在"选择"窗格中取消选择它们来隐藏。
