Excel Dashboards 简明教程

Power PivotTables & Power PivotCharts

当您的数据集较大时，可以使用 Excel Power Pivot，它可以处理数亿行数据。数据可以位于外部数据源中，而 Excel Power Pivot 会构建一个数据模型，该模型以内存优化模式工作。您可以执行计算、分析数据并得出报告，以得出结论和决策。报告可以是 Power 透视表或 Power 透视图表，也可以是二者的组合。

您可以将 Power Pivot 用作即席报告和分析解决方案。因此，对于具有 Excel 实践经验的人来说，有可能在几分钟内执行高端数据分析和决策，并且是仪表板中的一项宝贵资产。

Uses of Power Pivot

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

  1. 执行强大的数据分析并创建复杂的数据模型。

  2. 快速整合来自多个不同来源的大量数据。

  3. 执行信息分析并以交互方式共享见解。

  4. 创建关键绩效指标 (KPI)。

  5. To create Power PivotTables.

  6. To create Power PivotCharts.

Differences between PivotTable and Power PivotTable

Power 透视表在布局上类似于透视表，具有以下不同之处−

  1. 数据透视表基于 Excel 表格，而 Power PivotTable 基于数据模型中包含的数据表格。

  2. 数据透视表基于单个 Excel 表格或数据范围，而 Power PivotTable 可基于多个数据表格，前提是这些表格已添加到数据模型中。

  3. 数据透视表从 Excel 窗口创建，而 Power PivotTable 从 PowerPivot 窗口创建。

Creating a Power PivotTable

假设数据模型中有两个数据表格——“销售人员”和“销售额”。要从这两个数据表格创建 Power PivotTable，请执行以下操作：

  1. 在 PowerPivot 窗口的功能区上单击“主页”选项卡。

  2. 单击功能区上的“数据透视表”。

  3. 单击下拉列表中的“数据透视表”。

home pivot

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

create pivot

单击“确定”按钮。Excel 窗口中会创建新工作表，并会显示一个空 Power PivotTable。

active tab

正如你所看到的，Power PivotTable 的布局与此数据透视表类似。

数据透视表字段列表会显示在工作表的右侧。在此处，你会发现与数据透视表之间存在的某些差异。Power PivotTable 字段列表有两个选项卡——“活动”和“全部”，这两个选项卡显示在标题下方且位于字段列表上方。“全部”选项卡被高亮显示。“全部”选项卡显示数据模型中的所有数据表格，而“活动”选项卡显示为当前 Power PivotTable 选择的所有数据表格。

  1. 单击“全部”下数据透视表字段列表中的表格名称。

将显示出带有多选框的相应字段。

  1. 每个表格名称将在左侧显示此符号。

  2. 如果将光标置于此符号之上，将显示该数据表格的数据源和模型表格名称。

data source

  1. 将“销售人员”从“销售人员”表格拖动到“行”区域。

  2. 单击“活动”选项卡。

字段“销售人员”显示在 Power PivotTable 中，并且表格“销售人员”显示在“活动”选项卡下。

  1. 单击“全部”选项卡。

  2. 在销售表中单击“月份”和“订单金额”。

  3. 单击“活动”选项卡。

销售和销售人员这两个表均显示在“活动”选项卡下。

sales salesperson

  1. 将“月份”拖到“列”区域。

  2. 将“区域”拖到“筛选器”区域。

drag filter area

  1. 单击区域筛选器框中“全部”旁边的箭头。

  2. 单击“选择多项”。

  3. 单击“北方”和“南方”。

region filter box

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

explore data

可以通过动态方式修改 Power PivotTable 以浏览和报告数据。

Creating a Power PivotChart

Power PivotChart 是基于数据模型并在 Power Pivot 窗口中创建的透视表。虽然它具有一些与 Excel 透视表相似的功能，但其他功能使它变得更加强大。

假设你要基于以下数据模型创建 Power PivotChart。

table tools

  1. 在 Power Pivot 窗口功能区中单击“开始”选项卡。

  2. Click on PivotTable.

  3. 在下拉列表中单击“透视表”。

dropdown list

“创建透视表”对话框随即出现。单击“新建工作表”。

create pivot west

  1. 单击“确定”按钮。在 Excel 窗口的新工作表上创建一个空透视表。在本章中，当我们提到透视表时，我们指的是 Power PivotChart。

pivot chart

如你所见，数据模型中的所有表格会显示在“透视表字段”列表中。

  1. 单击“透视表字段”列表中的销售人员表格。

  2. 将字段“销售人员”和“区域”拖到“轴”区域。

透视表中将出现两个轴字段按钮，分别对应于两个选定字段。这些是轴字段按钮。字段按钮用于筛选透视表中显示的数据。

axis field

  1. 从 4 张表（East_Sales、North_Sales、South_Sales 和 West_Sales）中的每一张表将 TotalSalesAmount 拖动到 ∑ VALUES 区域。

legend value

正如您所看到的，工作表中出现以下内容：

  1. 在透视图表中，默认显示柱形图。

  2. 在图例区域，添加了 ∑ VALUES。

  3. 值显示在透视图表中的图例中，标题为值。

  4. 值字段按钮显示在透视图表上。

您可以移除图例和值字段按钮，以便让透视图表看起来更简洁。

  1. 单击透视图表右上角的按钮。

  2. 取消选择图表元素中的图例。

total sales amount

  1. 右键单击值字段按钮。

  2. 在下拉列表中单击在图表上隐藏值字段按钮。

图表上的值字段按钮将被隐藏。

hidden value field

请注意，字段按钮和/或图例的显示取决于透视图表的内容。您需要决定要显示的内容。

与动态数据透视表一样，动态数据透视图表字段列表也包含两个选项卡：活动和全部。此外，还有 4 个区域：

  1. AXIS (Categories)

  2. LEGEND (Series)

  3. ∑ VALUES

  4. FILTERS

正如您所看到的，图例中填充了 ∑ 值。此外，字段按钮被添加到透视图表中，以便于过滤显示的数据。您可以单击字段按钮上的箭头，选择/取消选择要在动态数据透视图表中显示的值。

Table and Chart Combinations

动态数据透视图为您提供了动态数据透视表和动态数据透视图的不同组合，以便探索数据、可视化数据和报告数据。

考虑我们在动态数据透视图中用于图示的以下数据模型：

table chart

您可以在动态数据透视图中使用以下表和图表组合。

  1. 图表和表格（水平） - 可以在同一工作表中创建一个动态数据透视图表和一个动态数据透视表，一个紧挨着另一个水平排列。

table horizontal

图表和数据透视表（垂直）——您可以在同一工作表的垂直方向上创建一个 Power PivotChart 和一个 Power PivotTable。

table vertical

单击 Power Pivot 窗口中功能区的“数据透视表”时出现的下拉列表中提供了这些组合和其他一些组合。

Hierarchies in Power Pivot

您可以在 Power Pivot 中使用层次结构进行计算，以及进行嵌套数据向上钻取和向下钻取。

考虑此章节中插图的以下数据模型。

table chart

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

  1. 按如下顺序单击数据表“Medal”中的列——Sport、DisciplineID 和 Event。请记住，此顺序对于创建有意义的层次结构非常重要。

  2. Right-click on the selection.

  3. 单击下拉列表中的“创建层次结构”。

将使用三个选定字段作为子级的层次结构字段创建。

  1. 右键单击层次结构名称。

  2. 单击下拉列表中的“重命名”。

  3. 键入一个有意义的名称，比如 EventHierarchy。

event hierarchy

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

  1. Create a Power PivotTable.

event hierarchy more fields

如您所见，在“数据透视表字段”列表中，EventHierarchy 显示为 Medal 表中的字段。Medal 表中的其他字段已折叠，并以“更多字段”显示。

  1. 单击 EventHierarchy 前面的箭头。

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

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

event fields

按如下方式将字段添加到 Power PivotTable——

  1. 将 EventHierarchy 拖到 ROWS 区域。

  2. 将 Medal 拖动到 ∑ VALUES 区域。

sport field

如你所见，Power透视表中的Sport字段值前面带有+符号。显示了每个体育项目的奖牌数。

  1. 单击Aquatics前面的+符号。将显示Aquatics下的DisciplineID字段值。

  2. 单击出现的子级D22。将显示D22下的Event字段值。

aquatics

如你所见，给了Event的奖牌数，它在父级DisciplineID处相加，而它又进一步在父级Sport处相加。

Calculations Using Hierarchy in Power PivotTables

你可以使用Power透视表中的层次结构创建计算。例如，在EventsHierarchy中，你可以将子级处的奖牌数以父级处奖牌数的百分比的形式显示，如下所示：

  1. 右键单击某个Event的Medal value的Count。

  2. 在下拉列表中单击Value Field Settings。

value field setting

将出现Value Field Settings对话框。

  1. 单击Show Values As选项卡。

  2. 单击Show values ass框。

  3. 单击% of Parent Row Total。

show value

  1. Click the OK button.

diving

如你所见，子级显示为Parent Totals的百分比。你可以通过对父级的子级的百分比值求和来验证这一点。和为100%。

Drilling Up and Drilling Down a Hierarchy

你可以使用Quick Explore tool快速浏览Power透视表中层次结构中的级别。

  1. 单击Power透视表中Event字段的值。

  2. 单击Quick Explore tool-出现于所选值所在单元格的右下角。

quick explore

EXPLORE框带有Drill Up选项。这是因为从Event只能向上钻取，因为它下面没有子级。

drill up

  1. 单击Drill Up。Power透视表数据被向上钻取至Discipline级别。

discipline level

  1. 单击Quick Explore tool-出现于包含值的单元格的右下角。

EXPLORE框出现，显示Drill Up和Drill Down选项。这是因为从Discipline可以向上钻取至Sport或向下钻取至Event级别。

drill down

这样，您可以快速地在 Power PivotTable 中上下移动层级。

Using a Common Slicer

您可以插入切片器，并在 Power PivotTable 和 Power PivotChart 中共享它们。

  1. 在彼此相邻的水平方向上创建一个 Power PivotChart 和 Power PivotTable。

  2. Click on Power PivotChart.

  3. 将 Discipline 从 Disciplines 表拖到 AXIS 区域。

  4. 将 Medal 从 Medals 表拖到 ∑ VALUES 区域。

  5. Click on Power PivotTable.

  6. 将 Discipline 从 Disciplines 表拖到 ROWS 区域。

  7. 将 Medal 从 Medals 表拖到 ∑ VALUES 区域。

common slicer

  1. 单击功能区上“PIVOTTABLE TOOLS”中的“ANALYZE”选项卡。

  2. Click on Insert Slicer.

将显示“插入切片器”对话框。

  1. 单击 Medals 表中的 NOC_CountryRegion 和 Sport。

  2. Click on OK.

会显示两个切片器——NOC_CountryRegion 和 Sport。

  1. 按如下方式排列和调整它们的大小，以便恰当对齐在 Power PivotTable 旁边。

noc country region

  1. 单击 NOC_CountryRegion 切片器中的 USA。

  2. 单击 Sport 切片器中的 Aquatics。

Power PivotTable 将被筛选到选定的值。

sport slicer

正如您所看到的，Power PivotChart 未被筛选。要使用相同的筛选器筛选 Power PivotChart，您可以使用与 Power PivotTable 中所用相同的切片器。

  1. Click on NOC_CountryRegion Slicer.

  2. 单击功能区上“SLICER TOOLS”中的“OPTIONS”选项卡。

  3. 单击切片器组中的“Report Connections”。

将显示 NOC_CountryRegion 切片器的“Report Connections”对话框。

report connections

正如您所看到的，工作簿中的所有 Power PivotTable 和 Power PivotChart 都会列在该对话框中。

  1. 单击位于与选定的 Power PivotTable 相同的工作表中的 Power PivotChart。

  2. Click the OK button.

  3. Repeat for Sport Slicer.

sport report connection

Power PivotChart 也将过滤为在两个切片器中所选的值。

filtered slicer

接下来，可以在 Power PivotChart 和 Power PivotTable 中添加更多详细信息。

  1. 单击 Power PivotChart。

  2. 将性别拖到图例区域。

  3. 右键单击 Power PivotChart。

  4. 单击更改图表类型。

  5. 在更改图表类型对话框中选择堆积柱形图。

  6. 单击 Power PivotTable。

  7. 将事件拖到行区域。

  8. 单击功能区中 PIVOTTABLE 工具中的设计选项卡。

  9. Click on Report Layout.

  10. 从下拉列表中单击大纲形式。

outline form

Aesthetic Reports for Dashboards

您可以使用 Power PivotTables 和 Power PivotCharts 创建美学报告，并将它们包含在仪表板中。如您在前一部分所见，可以使用报表布局选项来选择报表的外观。例如，通过选择 - 显示为大纲形式和显示带状行选项，您将获得如下所示的报表。

aesthetic report

正如您所看到的，字段名称显示在行标签和列标签的位置，而报表看起来不言自明。

您可以在选择窗格中选择要在最终报表中显示的对象。例如，如果您不想显示已创建且使用的切片器，则可以通过在选择窗格中取消选中来隐藏它们。