Excel Power Pivot 简明教程
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。
通过这种方式,您可以快速浏览分层结构。