Excel Power Pivot 简明教程

Excel Power Pivot - Hierarchies

数据模型中的层次结构是数据表中嵌套列的列表，在 Power PivotTable 中使用时被视为单一项目。例如，如果数据表中具有国家/地区、州、城市这些列，则可以定义一个层次结构，将这三列组合成一个字段。

A hierarchy in Data Model is a list of nested columns in a data table that are considered as a single item when used in a Power PivotTable. For example, if you have the columns − Country, State, City in a data table, a hierarchy can be defined to combine the three columns into one field.

在 Power PivotTable 字段列表中，层次结构显示为一个字段。因此，您可以只向数据透视表添加一个字段，而不是层次结构中的三个字段。此外，它使您可以以一种有意义的方式在嵌套级别中向上或向下移动。

In the Power PivotTable Fields list, the hierarchy appears as one field. So, you can add just one field to the PivotTable, instead of the three fields in the hierarchy. Further, it enables you to move up or down the nested levels in a meaningful way.

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

Consider the following Data Model for illustrations in this chapter.

hierarchies

Creating a Hierarchy

您可以在数据模型的图表视图中创建层次结构。请注意，您只能根据单个数据表创建层次结构。

You can create Hierarchies in the diagram view of the Data Model. Note that you can create a hierarchy based on a single data table only.

  1. Click on the columns − Sport, DisciplineID and Event in the data table Medal in that order. Remember that the order is important to create a meaningful hierarchy.

  2. Right-click on the selection.

  3. Select Create Hierarchy from the dropdown list.

create hierarchies

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

The hierarchy field with the three selected fields as the child levels gets created.

hierarchy field

Renaming a Hierarchy

若要重命名层次结构字段，请执行以下操作 −

To rename the hierarchy field, do the following −

  1. Right click on Hierarchy1.

  2. Select Rename from the dropdown list.

renaming hierarchy

键入 EventHierarchy 。

Type EventHierarchy.

eventhierarchy

Creating a PivotTable with a Hierarchy in Data Model

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

You can create a Power PivotTable using the hierarchy that you created in the Data Model.

  1. Click the PivotTable tab on the Ribbon in the Power Pivot window.

  2. Click PivotTable on the Ribbon.

hierarchy in data model

显示 Create PivotTable 对话框。选择“New Worksheet”，然后单击“OK”。

The Create PivotTable dialog box appears. Select New Worksheet and click OK.

select new worksheet click ok

在新的工作表中创建空的透视表。

An empty PivotTable is created in a new worksheet.

pivottable empty

在“透视表字段”列表中， EventHierarchy 显示为“Medals”表中的一个字段。Medals 表中的其他字段已折叠并显示为“More Fields”。

In the PivotTable Fields list, EventHierarchy appears as a field in Medals table. The other fields in the Medals table are collapsed and shown as More Fields.

  1. Click on the arrow in front of EventHierarchy.

  2. Click on the arrow in front of More Fields.

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

The fields under EventHierarchy will be displayed. All the fields in the Medals table will be displayed under More Fields.

under eventhierarchy

正如您所看到的，您添加到层次结构中的三个字段也会出现在 More Fields 下方，并带有复选框。如果您不希望它们出现在 More Fields 下方的“透视表字段”列表中，则必须隐藏 Power Pivot 窗口的数据视图中 Medals 数据表中的列。您可以随时取消隐藏它们。

As you can observe, the three fields that you added to the hierarchy also appear under More Fields with check boxes. If you do not want them to appear in the PivotTable Fields list under More Fields, you have to hide the columns in the data table – Medals in data view in Power Pivot Window. You can always unhide them whenever you want.

按如下方式将字段添加到透视表 −

Add fields to the PivotTable as follows −

  1. Drag EventHierarchy to ROWS area.

  2. Drag Medal to ∑ VALUES area.

drag eventhierarchy

“Sport”字段的值以正号 (+) 开头显示在透视表中。每项运动的奖牌数显示在后面。

The values of Sport field appear in the PivotTable with a + sign in front of them. The medal count for each sport is displayed.

  1. Click on the + sign before Aquatics. The DisciplineID field values under Aquatics will be displayed.

  2. Click on the child D22 that appears. The Event field values under D22 will be displayed.

disciplineid

正如您所看到的，奖牌数给出了按父级 − DisciplineID 汇总的赛事，按父级 − “Sport” 进一步汇总的赛事。

As you can observe, medal count is given for the Events, that get summed up at the parent level − DisciplineID, that get further summed up at the parent level − Sport.

Creating a Hierarchy based on Multiple Tables

假设您要在透视表中显示学科而不是学科 ID，以使其成为更易于阅读和理解的汇总。为此，您需要在 Medals 表中包含 Discipline 字段，而您知道它并不存在。Discipline 字段位于 Disciplines 数据表中，但您无法用来自多个表的字段创建层次结构。不过，有一种方法可以从其他表中获取所需的字段。

Suppose you want to display the Disciplines in the PivotTable rather than DisciplineIDs to make it a more readable and understandable summarization. In order to do this, you need to have the field Discipline in Medals table that as you know is not. Discipline field is in Disciplines data table, but you cannot create a hierarchy with fields from more than one table. But, there is a way to obtain the required field from the other table.

您知道 Medals 表和 Disciplines 表是相关的。您可以通过使用 DAX 与关系创建列，将 Disciplines 表中的 Discipline 字段添加到 Medals 表中。

As you are aware, the tables − Medals and Disciplines are related. You can add the field Discipline from Disciplines table to the Medals table, by creating a column using the relationship with DAX.

  1. Click data view in Power Pivot window.

  2. Click the Design tab on the Ribbon.

  3. Click Add.

表右侧的列 – 添加列被高亮显示。

The column − Add Column on the right side of the table is highlighted.

在公式栏中输入 = RELATED (Disciplines [Discipline]) 。新列 − CalculatedColumn1 创建，值与 Disciplines 表中的 Discipline 字段值相同。

Type = RELATED (Disciplines [Discipline]) in the formula bar. A new column − CalculatedColumn1 is created with the values as Discipline field values in the Disciplines table.

discipline field

将 Medals 表中如此获取的新列重命名为 Discipline。接下来，你必须从层次结构中移除 DisciplineID，并添加 Discipline（这将在后续章节中学习）。

Rename the new column thus obtained in the Medals table as Discipline. Next, you have to remove DisciplineID from the Hierarchy and add Discipline, which you will learn in the following sections.

Removing a Child Level from a Hierarchy

你可以观察到，层次结构仅在图表视图中可见，在数据视图中不可见。因此，你仅能在图表视图中编辑层次结构。

As you can observe, the hierarchy is visible in the diagram view only, and not in the data view. Hence, you can edit a hierarchy in the diagram view only.

  1. Click on the diagram view in the Power Pivot window.

  2. Right click DisciplineID in EventHierarchy.

  3. Select Remove from Hierarchy from the dropdown list.

remove from hierarchy

确认对话框出现。单击 Remove from Hierarchy 。

The Confirm dialog box appears. Click Remove from Hierarchy.

confirm dialog box

DisciplineID 字段从层次结构中删除。请记住，你已从层次结构中移除该字段，但源字段仍存在于数据表中。

The field DisciplineID gets deleted from the hierarchy. Remember that you have removed the field from hierarchy, but the source field still exists in the data table.

接下来，你需要将 Discipline 字段添加到 EventHierarchy。

Next, you need to add Discipline field to EventHierarchy.

Adding a Child Level to a Hierarchy

你可以向现有层次结构 - EventHierarchy 添加 Discipline 字段，如下所示 −

You can add the field Discipline to the existing hierarchy - EventHierarchy as follows −

  1. Click on the field in Medals table.

  2. Drag it to the Events field below in the EventHierarchy.

addind child level

Discipline 字段会添加到 EventHierarchy。

The Discipline field gets added to EventHierarchy.

field added

你可以看到，EventHierarchy 中的字段顺序为运动 – 项目 – 学科。但是，你应知道其顺序应为运动 – 学科 – 项目。因此，你需要更改字段顺序。

As you can observe, the order of the fields in EventHierarchy is Sport–Event–Discipline. But, as you are aware it has to be Sport–Discipline-Event. Hence, you need to change the order of the fields.

Changing the Order of a Child Level in a Hierarchy

要将字段 Discipline 移到字段 Sport 之后的位置，请执行以下操作 −

To move the field Discipline to the position after the field Sport, do the following −

  1. Right click on the field Discipline in EventHierarchy.

  2. Select Move Up from the dropdown list.

order of child level

字段顺序将更改为运动 – 学科 – 项目。

The order of the fields changes to Sport-Discipline-Event.

fields order

PivotTable with Changes in Hierarchy

若要查看你在事件层级中对数据透视表所做的更改，你不需要新建数据透视表。你可以在现有的数据透视表上查看它们。

To view the changes that you made in EventHierarchy in the PivotTable, you need not create a new PivotTable. You can view them in the existing PivotTable itself.

单击 Excel 窗口中的包含数据透视表的工作表。

Click on the worksheet with the PivotTable in Excel window.

click on worksheet

正如你观察到的，在数据透视表字段列表中，事件层级中的子级别反映了你在数据模型中层级中所做的更改。同样的更改也会相应地反映在数据透视表中。

As you can observe, in the PivotTable Fields list, the child levels in the EventHierarchy reflect the changes you made in the Hierarchy in Data Model. The same changes also get reflected in the PivotTable accordingly.

单击数据透视表中水上运动前的加号。子级别将作为学科字段的值显示。

Click the + sign in front of Aquatics in the PivotTable. The child levels appear as values of the field Discipline.

fields list

Hiding and Showing Hierarchies

你可以选择隐藏这些层级，并在需要时显示它们。

You can choose to hide the Hierarchies and show them whenever you want.

  1. Uncheck the box Hierarchies in the top menu of diagram view to hide the hierarchies.

  2. Check the box Hierarchies to show the hierarchies.

hiding and showing hierarchy

Creating a Hierarchy in Other Ways

除了在前面的部分中创建层级的方式外，你还可以另外两种方式创建层级。

In addition to the way you created hierarchy in the previous sections, you can create a hierarchy in another two ways.

  1. Click the Create Hierarchy button on the top right corner of the Medals data table in diagram view.

create hierarchy

将在表格中创建新的层级，其中没有任何字段。

A new hierarchy gets created in the table without any fields in it.

new hierarchy in table

按该顺序将字段年份和赛季拖到新层级。层级会显示子级别。

Drag the fields Year and Season, in that order to the new hierarchy. The hierarchy shows the child levels.

fields year

  1. Another way of creating the same hierarchy is as follows −[style="arabic"]

    1. Right click on the field Year in the Medals data table in diagram view.

    2. Select Create Hierarchy from the dropdown list.

same hierarchy

会在表中创建一个新的层级，其中年份是子字段。

A new hierarchy is created in table with Year as a child field.

child field

将字段赛季拖到层级。层级会显示子级别。

Drag the field season to the hierarchy. The hierarchy shows the child levels.

field season

Deleting a Hierarchy

你可以通过以下方式从数据模型中删除层级−

You can delete a hierarchy from the Data Model as follows −

  1. Right click on the hierarchy.

  2. Select Delete from the dropdown list.

deleting hierarchy

将显示 Confirm 对话框。单击 Delete from Model 。

The Confirm dialog box appears. Click Delete from Model.

delete from model

分层结构已被删除。

The hierarchy gets deleted.

hierarchy deleted

Calculations Using Hierarchy

您可以通过分层结构创建计算。在 EventsHierarchy 中，您可以按以下方式将子级别奖牌数显示为其父级别奖牌数的百分比 −

You can create calculations using a hierarchy. In the EventsHierarchy, you can display the number of medals at a child level as a percentage of the number of medals at its parent level as follows −

  1. Right click on a Count of Medal value of an Event.

  2. Select Value Field Settings from the dropdown list.

calculations using hierarchy

将出现Value Field Settings对话框。

Value Field Settings dialog box appears.

  1. Click the Show Values As tab.

  2. Select % of Parent Row Total from the list and click OK.

field settings

子级别显示为父总计的百分比。您可以通过对父项的子级别的百分比值求和来验证这一点。总和应为 100%。

The child levels are displayed as the percentage of the Parent Totals. You can verify this by summing up the percentage values of the child level of a parent. The sum would be 100%.

parent totals

Drilling Up and Drilling Down a Hierarchy

您可以使用快速浏览工具快速查看分层结构的各级别。

You can quickly drill up and drill down across the levels in a hierarchy using Quick Explore tool.

  1. Click on a value of Event field in the PivotTable.

  2. Click the Quick Explore tool - that appears at the bottom right corner of the cell containing the selected value.

drilling up

出现了 Explore box with Drill Up 选项。这是因为从 Event 中您只能向上钻取，因为其下方没有子级别。

The Explore box with Drill Up option appears. This is because from Event you can only drill up as there are no child levels under it.

单击 Drill Up 。

Click Drill Up.

click drill up

数据透视表数据向上钻取至 Disciplin 。

PivotTable data is drilled up to Discipline.

drilled up

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

Click on the Quick Explore tool - that appears at the bottom right corner of the cell containing a value.

会出现一个包含“向上钻取”和“向下钻取”选项的浏览框。这是因为从 Discipline 中您可以向上钻取至 Sport 或向下钻取至 Event。

Explore box appears with Drill Up and Drill Down options displayed. This is because from Discipline you can drill up to Sport or drill down to Event.

explore box

通过这种方式，您可以快速浏览分层结构。

This way you can quickly move up and down the hierarchy.