Excel Power Pivot - 层次结构
数据模型中的层次结构是数据表中嵌套列的列表,当在 Power PivotTable 中使用时,这些列被视为单个项目。 例如,如果数据表中有列 - Country、State、City,则可以定义层次结构以将三列合并为一个字段。
在 Power PivotTable 字段列表中,层次结构显示为一个字段。 因此,您可以只向数据透视表添加一个字段,而不是在层次结构中添加三个字段。 此外,它使您能够以有意义的方式向上或向下移动嵌套级别。
考虑以下数据模型以用于本章中的说明。
![层次结构](/excel_power_pivot/images/hierarchies.jpg)
创建层次结构
您可以在数据模型的图表视图中创建层次结构。 请注意,您只能基于单个数据表创建层次结构。
依次单击数据表 Medal 中的列 - Sport、DisciplineID 和 Event。 请记住,顺序对于创建有意义的层次结构很重要。
右键单击所选内容。
从下拉列表中选择"创建层次结构"。
![创建层次结构](/excel_power_pivot/images/create_hierarchies.jpg)
已创建具有三个选定字段作为子级别的层次结构字段。
![层次结构字段](/excel_power_pivot/images/hierarchy_field.jpg)
重命名层次结构
要重命名层次结构字段,请执行以下操作 −
右键单击 Hierarchy1。
从下拉列表中选择重命名。
![重命名层次结构](/excel_power_pivot/images/renaming_hierarchy.jpg)
键入EventHierarchy。
![事件层次结构](/excel_power_pivot/images/eventhierarchy.jpg)
在数据模型中创建具有层次结构的数据透视表
您可以使用在数据模型中创建的层次结构创建 Power PivotTable。
单击 Power Pivot 窗口中功能区上的"数据透视表"选项卡。
单击功能区上的数据透视表。
![数据模型中的层次结构](/excel_power_pivot/images/hierarchy_in_data_model.jpg)
出现创建数据透视表对话框。 选择新建工作表并单击确定。
![选择新工作表并单击确定](/excel_power_pivot/images/select_new_worksheet_click_ok.jpg)
在新工作表中创建了一个空的数据透视表。
![数据透视表为空](/excel_power_pivot/images/pivottable_empty.jpg)
在数据透视表字段列表中,EventHierarchy 显示为奖牌表中的一个字段。 奖牌表中的其他字段已折叠并显示为更多字段。
点击 EventHierarchy 前面的箭头
。
点击 More Fields 前面的箭头
。
将显示 EventHierarchy 下的字段。 奖牌表中的所有字段将显示在更多字段下。
![Under EventHierarchy](/excel_power_pivot/images/under_eventhierarchy.jpg)
如您所见,您添加到层次结构中的三个字段也显示在带有复选框的更多字段 下。 如果您不希望它们出现在更多字段 下的数据透视表字段列表中,则必须隐藏数据表中的列 - Power Pivot 窗口数据视图中的奖牌。 您随时可以取消隐藏它们。
向数据透视表添加字段如下 −
将 EventHierarchy 拖到 ROWS 区域。
将 Medal 拖到 ∑ VALUES 区域。
![拖动事件层次结构](/excel_power_pivot/images/drag_eventhierarchy.jpg)
Sport 字段的值出现在数据透视表中,前面带有一个 + 号。 显示每项运动的奖牌数。
点击 Aquatics 前的 + 号。 将显示 Aquatics 下的 DisciplineID 字段值。
点击出现的子 D22。 将显示 D22 下的事件字段值。
![DisciplineID](/excel_power_pivot/images/disciplineid.jpg)
如您所见,奖牌数是针对事件给出的,在父级汇总 − DisciplineID,在父级别进一步汇总 − Sport。
基于多个表创建层次结构
假设您想要在数据透视表中显示 Discipline 而不是 DisciplineID,以使其成为更易读和更易理解的摘要。 为此,您需要在 Medals 表中拥有 Discipline 字段,而您知道这不是。 Discipline 字段在 Disciplines 数据表中,但您不能使用多个表中的字段创建层次结构。 但是,有一种方法可以从其他表中获取所需的字段。
如您所知,表 - Medals 和 Disciplines 是相关的。 您可以通过使用与 DAX 的关系创建一个列,将 Disciplines 表中的字段 Discipline 添加到 Medals 表。
单击 Power Pivot 窗口中的数据视图。
单击功能区上的"设计"选项卡。
点击添加。
表格右侧的列 - 添加列突出显示。
Type = RELATED (Disciplines [Discipline]) 在公式栏中。 新专栏 − CalculatedColumn1 创建时使用的值作为 Disciplines 表中的 Discipline 字段值。
![Disciplines 字段](/excel_power_pivot/images/discipline_field.jpg)
将 Medals 表中由此获得的新列重命名为 Discipline。 接下来,您必须从层次结构中删除 DisciplineID 并添加 Discipline,您将在以下部分中学习。
从层次结构中删除子级别
如您所见,层次结构仅在图表视图中可见,在数据视图中不可见。 因此,您只能在图表视图中编辑层次结构。
单击 Power Pivot 窗口中的图表视图。
右键单击 EventHierarchy 中的 DisciplineID。
从下拉列表中选择从层次结构中删除。
![从层次结构中删除](/excel_power_pivot/images/remove_from_hierarchy.jpg)
出现"确认"对话框。 单击从层次结构中删除。
![确认对话框](/excel_power_pivot/images/confirm_dialog_box.jpg)
字段 DisciplineID 从层次结构中删除。 请记住,您已从层次结构中删除该字段,但源字段仍然存在于数据表中。
接下来,您需要将 Discipline 字段添加到 EventHierarchy。
向层次结构添加子级别
您可以将字段 Discipline 添加到现有层次结构 - EventHierarchy,如下所示 −
单击 Medals 表中的字段。
将它拖到 EventHierarchy 下方的 Events 字段。
![添加子级别](/excel_power_pivot/images/addind_child_level.jpg)
Discipline 字段被添加到 EventHierarchy。
![已添加字段](/excel_power_pivot/images/field_added.jpg)
如您所见,EventHierarchy 中字段的顺序是 Sport–Event–Discipline。 但是,如您所知,它必须是 Sport-Discipline-Event。 因此,您需要更改字段的顺序。
更改层次结构中子级别的顺序
要将字段 Discipline 移动到字段 Sport 之后的位置,请执行以下操作 −
右键单击 EventHierarchy 中的 Discipline 字段。
从下拉列表中选择上移。
![子级顺序](/excel_power_pivot/images/order_of_child_level.jpg)
字段顺序更改为 Sport-Discipline-Event。
![字段顺序](/excel_power_pivot/images/fields_order.jpg)
层次结构发生变化的数据透视表
要在数据透视表中查看您在 EventHierarchy 中所做的更改,您无需创建新的数据透视表。 您可以在现有的数据透视表中查看它们。
在 Excel 窗口中单击带有数据透视表的工作表。
![单击工作表](/excel_power_pivot/images/click_on_worksheet.jpg)
如您所见,在数据透视表字段列表中,EventHierarchy 中的子级别反映了您在数据模型的层次结构中所做的更改。 同样的更改也会相应地反映在数据透视表中。
单击数据透视表中 Aquatics 前面的 + 号。 子级别显示为字段 Discipline 的值。
![字段列表](/excel_power_pivot/images/fields_list.jpg)
隐藏和显示层次结构
您可以选择隐藏层次结构并在需要时显示它们。
取消选中图表视图顶部菜单中的层次结构框以隐藏层次结构。
选中层次结构框以显示层次结构。
![隐藏和显示层次结构](/excel_power_pivot/images/hiding_and_showing_hierarchy.jpg)
以其他方式创建层次结构
除了前面部分中创建层次结构的方式外,您还可以通过另外两种方式创建层次结构。
1. 单击图表视图中奖牌数据表 Medals 右上角的"创建层次结构"按钮。
![创建层次结构](/excel_power_pivot/images/create_hierarchy.jpg)
在表中创建了一个新的层次结构,其中没有任何字段。
![表中的新层次结构](/excel_power_pivot/images/new_hierarchy_in_table.jpg)
按顺序将 Year 和 Season 字段拖到新层次结构中。 层次结构显示子级别。
![字段 Year](/excel_power_pivot/images/fields_year.jpg)
2。 另一种创建相同层次结构的方法如下 −
在图表视图中的奖牌数据表中右键单击字段 Year。
从下拉列表中选择"创建层次结构"。
![相同层次结构](/excel_power_pivot/images/same_hierarchy.jpg)
在表中创建了一个新的层次结构,其中 Year 作为子字段。
![Child 字段](/excel_power_pivot/images/child_field.jpg)
将字段 season 拖到层次结构中。 层次结构显示子级别。
![Season 字段](/excel_power_pivot/images/field_season.jpg)
删除层次结构
您可以按如下方式从数据模型中删除层次结构 −
右键单击层次结构。
从下拉列表中选择"删除"。
![删除层次结构](/excel_power_pivot/images/deleting_hierarchy.jpg)
出现确认对话框。 点击从模型中删除。
![从模型中删除](/excel_power_pivot/images/delete_from_model.jpg)
层次结构被删除。
![层次结构已删除](/excel_power_pivot/images/hierarchy_deleted.jpg)
使用层次结构进行计算
您可以使用层次结构创建计算。 在 EventsHierarchy 中,您可以将子级别的奖牌数量显示为其父级别的奖牌数量的百分比,如下所示 −
右键单击事件的奖牌值计数。
从下拉列表中选择值字段设置。
![使用层次结构的计算](/excel_power_pivot/images/calculations_using_hierarchy.jpg)
出现"值字段设置"对话框。
单击将值显示为选项卡。
从列表中选择 % of Parent Row Total,然后单击确定。
![字段设置](/excel_power_pivot/images/field_settings.jpg)
子级显示为父级总计的百分比。 您可以通过汇总父项的子级别的百分比值来验证这一点。 总和为 100%。
![父级总计](/excel_power_pivot/images/parent_totals.jpg)
向上钻取和向下钻取层次结构
您可以使用"快速浏览"工具在层次结构中的各个级别快速向上钻取和向下钻取。
单击数据透视表中事件字段的值。
单击快速浏览工具 -
,它显示在包含所选值的单元格的右下角。
![向上钻取](/excel_power_pivot/images/drilling_up.jpg)
出现带向上钻取的分析框选项。 这是因为您只能从 Event 向上钻取,因为它下面没有子级别。
单击向上钻取。
![单击向上钻取](/excel_power_pivot/images/click_drill_up.jpg)
数据透视表数据向上钻取到 Discipline。
![向上钻取](/excel_power_pivot/images/drilled_up.jpg)
单击快速浏览工具 - ,它出现在包含值的单元格的右下角。
出现"分析"框,其中显示"向上钻取"和"向下钻取"选项。 这是因为您可以从 Discipline 向上钻取到 Sport 或向下钻取到 Event。
![分析框](/excel_power_pivot/images/explore_box.jpg)
这样您就可以快速在层次结构中上下移动。