
- Excel 仪表板教程
- Excel 仪表板 - 主页
- 介绍
- Excel 功能创建仪表板
- 条件格式
- Excel 图表
- 互动控制
- 高级 Excel 图表
- Excel 数据透视表
- 电源数据透视表和数据透视图
- 电源视图报告
- 关键绩效指标
- 构建仪表板
- 例子
- Excel 仪表板有用的资源
- Excel 仪表板 - 快速指南
- Excel 仪表板 - 资源
- Excel 仪表板 - 讨论
功率数据透视表和功率数据透视图
当您的数据集很大时,您可以使用可以处理数亿行数据的 Excel Power Pivot。数据可以位于外部数据源中,Excel Power Pivot 构建一个在内存优化模式下工作的数据模型。您可以执行计算、分析数据并得出报告以得出结论和决策。该报表可以是 Power PivotTable 或 Power PivotChart 或两者的组合。
您可以利用 Power Pivot 作为临时报告和分析解决方案。因此,具有 Excel 实践经验的人可以在几分钟内执行高端数据分析和决策,并且是包含在仪表板中的重要资产。
Power Pivot 的用途
您可以使用 Power Pivot 进行以下操作 -
- 执行强大的数据分析并创建复杂的数据模型。
- 快速整合来自多个不同来源的大量数据。
- 进行信息分析并以交互方式分享见解。
- 创建关键绩效指标 (KPI)。
- 创建 Power 数据透视表。
- 创建 Power 数据透视图。
数据透视表和 Power PivotTable 之间的差异
Power PivotTable 的布局类似于数据透视表,但有以下差异 -
数据透视表基于 Excel 表,而 Power 数据透视表基于属于数据模型一部分的数据表。
数据透视表基于单个 Excel 表或数据范围,而 Power 数据透视表可以基于多个数据表,前提是将它们添加到数据模型中。
数据透视表是从 Excel 窗口创建的,而 Power 数据透视表是从 PowerPivot 窗口创建的。
创建电源数据透视表
假设数据模型中有两个数据表 - Salesperson 和 Sales。要从这两个数据表创建 Power PivotTable,请按以下步骤操作 -
单击 PowerPivot 窗口中功能区上的“主页”选项卡。
单击功能区上的数据透视表。
单击下拉列表中的数据透视表。

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

单击“确定”按钮。在 Excel 窗口中创建新工作表,并出现一个空的 Power PivotTable。

正如您所观察到的,Power PivotTable 的布局与数据透视表的布局类似。
数据透视表字段列表显示在工作表的右侧。在这里,您会发现与数据透视表的一些差异。Power PivotTable 字段列表有两个选项卡 - ACTIVE 和 ALL,它们显示在标题下方和字段列表上方。所有选项卡均突出显示。ALL 选项卡显示数据模型中的所有数据表,ACTIVE 选项卡显示为当前 Power PivotTable 选择的所有数据表。
单击“全部”下“数据透视表字段”列表中的表名称。
将出现带有复选框的相应字段。
每个表名称的左侧都有 符号。
如果将光标放在该符号上,将显示该数据表的数据源和模型表名称。

- 将 Salesperson 从 Salesperson 表拖到 ROWS 区域。
- 单击“活动”选项卡。
字段“销售人员”显示在 Power 数据透视表中,表“销售人员”显示在“活动”选项卡下。
- 单击“全部”选项卡。
- 单击“销售”表中的“月份”和“订单金额”。
- 单击“活动”选项卡。
这两个表 – 销售和销售人员都显示在“活动”选项卡下。

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

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

- 单击“确定”按钮。按升序对列标签进行排序。

可以动态修改 Power PivotTable 以探索和报告数据。
创建功率数据透视图
Power PivotChart 是基于数据模型并从 Power Pivot 窗口创建的数据透视图。尽管它具有一些与 Excel 数据透视图类似的功能,但还有其他功能使其更加强大。
假设您要基于以下数据模型创建 Power 数据透视图。

- 单击 Power Pivot 窗口中功能区上的“主页”选项卡。
- 单击数据透视表。
- 单击下拉列表中的数据透视图。

将出现“创建数据透视图”对话框。单击“新建工作表”。

单击“确定”按钮。将在 Excel 窗口中的新工作表上创建一个空数据透视图。在本章中,当我们说数据透视图时,我们指的是 Power 数据透视图。

正如您所观察到的,数据模型中的所有表都显示在数据透视图字段列表中。
- 单击数据透视图字段列表中的销售人员表。
- 将字段 – 销售人员和区域拖至 AXIS 区域。
两个选定字段的两个字段按钮出现在数据透视图上。这些是轴字段按钮。字段按钮的用途是过滤数据透视图上显示的数据。

将 TotalSalesAmount 从 4 个表(East_Sales、North_Sales、South_Sales 和 West_Sales)中的每一个拖到 Σ VALUES 区域。

正如您所观察到的,以下内容出现在工作表上 -
- 在数据透视图中,默认显示柱形图。
- 在图例区域中,添加了 Σ VALUES。
- 值显示在数据透视图的图例中,标题为“值”。
- 值字段按钮出现在数据透视图上。
您可以删除图例和值字段按钮以使数据透视图看起来更整洁。
单击
数据透视图右上角的按钮。
取消选择图表元素中的图例。

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

请注意,字段按钮和/或图例的显示取决于数据透视图的上下文。您需要决定需要显示什么。
与 Power 数据透视表一样,Power 数据透视图字段列表也包含两个选项卡 - ACTIVE 和 ALL。此外,还有4个领域 -
- 轴(类别)
- 传奇(系列)
- Σ 值
- 过滤器
正如您所观察到的,图例中填充了 Σ 值。此外,字段按钮被添加到数据透视图中,以便于过滤正在显示的数据。您可以单击字段按钮上的箭头,然后选择/取消选择要在 Power PivotChart 中显示的值。
表格和图表组合
Power Pivot 为您提供 Power PivotTable 和 Power PivotChart 的不同组合,用于数据探索、可视化和报告。
考虑 Power Pivot 中的以下数据模型,我们将使用它来进行说明 -

您可以在 Power Pivot 中拥有以下表格和图表组合。
图表和表格(水平)- 您可以创建一个 Power 数据透视图和一个 Power 数据透视表,在同一工作表中水平相邻。

图表和表格(垂直)- 您可以创建一个 Power 数据透视图和一个 Power 数据透视表,在同一工作表中一个垂直地位于另一个之下。

当您单击 Power Pivot 窗口中功能区上的数据透视表时,会在出现的下拉列表中提供这些组合以及更多组合。
Power Pivot 中的层次结构
您可以使用 Power Pivot 中的层次结构进行计算以及向上钻取和向下钻取嵌套数据。
请考虑以下数据模型作为本章的说明。

您可以在数据模型的图表视图中创建层次结构,但仅基于单个数据表。
按顺序单击数据表 Medal 中的列 – Sport、DisciplineID 和 Event。请记住,顺序对于创建有意义的层次结构非常重要。
右键单击所选内容。
单击下拉列表中的“创建层次结构”。
将创建具有三个选定字段作为子级别的层次结构字段。
- 右键单击层次结构名称。
- 单击下拉列表中的重命名。
- 键入一个有意义的名称,例如 EventHierarchy。

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

您可以观察到,在数据透视表字段列表中,EventHierarchy 显示为奖牌表中的字段。奖牌表中的其他字段已折叠并显示为更多字段。
- 单击
EventHierarchy 前面的箭头。
单击更多字段前面的箭头。
将显示 EventHierarchy 下的字段。奖牌表中的所有字段都将显示在更多字段下。

将字段添加到 Power PivotTable,如下所示 -
- 将 EventHierarchy 拖至 ROWS 区域。
- 将奖章拖至 Σ VALUES 区域。

正如您所观察到的,“运动”字段的值出现在 Power 数据透视表中,并且前面有一个 + 号。显示每项运动的奖牌数。
单击水上运动之前的+号。将显示 Aquatics 下的 DisciplineID 字段值。
单击出现的子 D22。将显示 D22 下的事件字段值。

正如您所观察到的,奖牌数是针对赛事给出的,这些奖牌计数在父级别 - DisciplineID 中进行汇总,并在父级别 - 体育 中进一步进行汇总。
使用 Power PivotTables 中的层次结构进行计算
您可以使用 Power PivotTable 中的层次结构创建计算。例如,在 EventsHierarchy 中,您可以显示编号。儿童级别的奖牌数占奖牌数的百分比。其父级别的奖牌数量如下:
- 右键单击事件的奖牌计数值。
- 单击下拉列表中的值字段设置。

出现值字段设置对话框。
- 单击“值显示为”选项卡。
- 单击“将值显示为”框。
- 单击父行总计的百分比。

- 单击“确定”按钮。

正如您所观察到的,子级显示为父级总计的百分比。您可以通过对父级的子级别的百分比值进行求和来验证这一点。总和将为 100%。
向上钻取和向下钻取层次结构
您可以使用快速浏览工具在 Power PivotTable 的层次结构中快速向上钻取和向下钻取。
单击 Power 数据透视表中事件字段的值。
单击“快速浏览”工具 -
该工具出现在包含所选值的单元格的右下角。

出现带有“向上钻取”选项的“浏览”框。这是因为您只能从事件中向上钻取,因为它下面没有子级别。

单击“向上钻取”。Power PivotTable 数据被钻取到学科级别。

单击“快速浏览”工具 -
该工具出现在包含值的单元格的右下角。
出现“浏览”框,并显示“向上钻取”和“向下钻取”选项。这是因为您可以从“纪律”中向上钻取到“运动”或向下钻取到“赛事”级别。

通过这种方式,您可以在 Power PivotTable 的层次结构中快速上下移动。
使用通用切片器
您可以插入切片器并在 Power PivotTables 和 Power PivotCharts 之间共享它们。
创建水平相邻的 Power 数据透视图和 Power 数据透视表。
单击“电源数据透视图”。
将“学科”从“学科”表拖动到“轴”区域。
将奖牌从奖牌表拖至 Σ VALUES 区域。
单击“电源数据透视表”。
将“学科”从“学科”表拖到“ROWS”区域。
将奖牌从奖牌表拖至 Σ VALUES 区域。

- 单击功能区上的“数据透视表工具”中的“分析”选项卡。
- 单击插入切片器。
将出现“插入切片器”对话框。
- 单击奖牌表中的 NOC_CountryRegion 和 Sport。
- 单击“确定”。
出现两个切片器 - NOC_CountryRegion 和 Sport。
对它们进行排列和调整大小,使其在 Power PivotTable 旁边正确对齐,如下所示。

- 单击 NOC_CountryRegion 切片器中的 USA。
- 单击“运动切片器”中的“水上运动”。
Power PivotTable 被筛选为选定的值。

正如您所观察到的,Power PivotChart 未被过滤。要使用相同的筛选器筛选 Power 数据透视图,您可以使用与 Power 数据透视表相同的切片器。
- 单击 NOC_CountryRegion 切片器。
- 单击功能区上切片器工具中的选项选项卡。
- 单击切片器组中的报告连接。
将出现 NOC_CountryRegion 切片器的“报告连接”对话框。

正如您所观察到的,工作簿中的所有 Power 数据透视表和 Power 数据透视图都列在对话框中。
单击与所选 Power 数据透视表位于同一工作表中的 Power 数据透视图。
单击“确定”按钮。
对运动切片机重复此操作。

Power PivotChart 还会过滤为在两个切片器中选择的值。

接下来,您可以向 Power 数据透视图和 Power 数据透视表添加更多详细信息。
- 单击电源数据透视图。
- 将性别拖至图例区域。
- 右键单击电源数据透视图。
- 单击更改图表类型。
- 在“更改图表类型”对话框中选择“堆积柱形图”。
- 单击电源数据透视表。
- 将事件拖至 ROWS 区域。
- 单击功能区上的“数据透视表工具”中的“设计”选项卡。
- 单击报告布局。
- 单击下拉列表中的大纲表格。

仪表板美学报告
您可以使用 Power PivotTables 和 Power PivotCharts 创建美观报告并将其包含在仪表板中。正如您在上一节中所看到的,您可以使用报告布局选项来选择报告的外观和风格。例如,使用选项 - 以大纲形式显示并选择带状行,您将获得如下所示的报告。

正如您所观察到的,字段名称出现在行标签和列标签的位置,并且报告看起来不言自明。
您可以在“选择”窗格中选择要在最终报告中显示的对象。例如,如果您不想显示您创建和使用的切片器,则可以通过在“选择”窗格中取消选择它们来隐藏它们。