Excel Power Pivot - Иерархии
Иерархия в модели данных - это список вложенных столбцов в таблице данных, которые рассматриваются как один элемент при использовании в Power PivotTable. Например, если у вас есть столбцы - Страна, Штат, Город в таблице данных, можно определить иерархию для объединения трех столбцов в одно поле.
В списке полей сводной таблицы Power иерархия отображается как одно поле. Таким образом, вы можете добавить в сводную таблицу только одно поле вместо трех полей в иерархии. Кроме того, это позволяет вам осмысленно перемещаться вверх или вниз по вложенным уровням.
Рассмотрим следующую модель данных для иллюстраций в этой главе.
Создание иерархии
Вы можете создавать иерархии в представлении схемы модели данных. Обратите внимание, что вы можете создать иерархию только на основе одной таблицы данных.
Щелкните столбцы - Sport, DisciplineID и Event в таблице данных Medal в указанном порядке. Помните, что порядок важен для создания значимой иерархии.
Щелкните выделение правой кнопкой мыши.
В раскрывающемся списке выберите «Создать иерархию».
Будет создано поле иерархии с тремя выбранными полями в качестве дочерних уровней.
Переименование иерархии
Чтобы переименовать поле иерархии, сделайте следующее:
Щелкните правой кнопкой мыши Hierarchy1.
В раскрывающемся списке выберите Переименовать.
Тип EventHierarchy.
Создание сводной таблицы с иерархией в модели данных
Вы можете создать Power PivotTable, используя иерархию, созданную в модели данных.
Щелкните вкладку сводной таблицы на ленте в окне Power Pivot.
Щелкните сводную таблицу на ленте.
В Create PivotTableпоявится диалоговое окно. Выберите «Новый лист» и нажмите «ОК».
На новом листе создается пустая сводная таблица.
В списке полей сводной таблицы EventHierarchyотображается как поле в таблице медалей. Остальные поля в таблице медалей свернуты и показаны как Дополнительные поля.
Щелкните стрелку
перед EventHierarchy.
Щелкните стрелку
перед надписью «Дополнительные поля».
Отобразятся поля в иерархии событий. Все поля в таблице медалей будут отображаться в разделе «Дополнительные поля».
Как видите, три поля, которые вы добавили в иерархию, также отображаются под More Fieldsс флажками. Если вы не хотите, чтобы они отображались в списке полей сводной таблицы вMore Fields, вам необходимо скрыть столбцы в таблице данных - Медали в представлении данных в окне Power Pivot. Вы всегда можете показать их, когда захотите.
Добавьте поля в сводную таблицу следующим образом:
Бремя EventHierarchy в область РЯДОВ.
Перетащите медаль в область ∑ ЗНАЧЕНИЯ.
Значения поля Sport отображаются в сводной таблице со знаком + перед ними. Отображается количество медалей для каждого вида спорта.
Щелкните значок + перед Aquatics. Отобразятся значения поля DisciplineID в разделе «Водные виды спорта».
Щелкните на появившемся дочернем D22. Отобразятся значения поля событий в D22.
Как вы можете заметить, количество медалей дается для событий, которые суммируются на родительском уровне - DisciplineID, которые далее суммируются на родительском уровне - Спорт.
Создание иерархии на основе нескольких таблиц
Предположим, вы хотите отобразить в сводной таблице дисциплины, а не идентификаторы дисциплин, чтобы сделать ее более читаемой и понятной сводкой. Для этого вам необходимо иметь поле Дисциплина в таблице медалей, которого, как вы знаете, нет. Поле Discipline находится в таблице данных Disciplines, но вы не можете создать иерархию с полями из более чем одной таблицы. Но есть способ получить необходимое поле из другой таблицы.
Как вы знаете, таблицы - Медали и Дисциплины связаны. Вы можете добавить поле Дисциплина из таблицы Дисциплины в таблицу медалей, создав столбец, используя связь с DAX.
Щелкните представление данных в окне Power Pivot.
Щелкните вкладку «Дизайн» на ленте.
Щелкните Добавить.
Столбец - Добавить столбец в правой части таблицы выделен.
Тип = RELATED (Disciplines [Discipline])в строке формул. Новая колонка -CalculatedColumn1 создается со значениями как значения поля Дисциплина в таблице Дисциплины.
Переименуйте полученный таким образом новый столбец в таблице медалей как «Дисциплина». Затем вам нужно удалить DisciplineID из иерархии и добавить дисциплину, о чем вы узнаете в следующих разделах.
Удаление дочернего уровня из иерархии
Как видите, иерархия видна только в представлении диаграммы, а не в представлении данных. Следовательно, вы можете редактировать иерархию только в виде диаграммы.
Щелкните представление диаграммы в окне Power Pivot.
Щелкните правой кнопкой мыши DisciplineID в EventHierarchy.
Выбрать Remove from Hierarchy из раскрывающегося списка.
Появится диалоговое окно подтверждения. НажмитеRemove from Hierarchy.
Поле DisciplineID удаляется из иерархии. Помните, что вы удалили поле из иерархии, но исходное поле все еще существует в таблице данных.
Затем вам нужно добавить поле Discipline в EventHierarchy.
Добавление дочернего уровня в иерархию
Вы можете добавить поле Дисциплина в существующую иерархию - Иерархия событий следующим образом -
Щелкните поле в таблице медалей.
Перетащите его в поле «События» ниже в иерархии событий.
Поле Discipline добавляется в EventHierarchy.
Как видите, порядок полей в иерархии событий следующий: Спорт – Событие – Дисциплина. Но, как вы знаете, это должно быть мероприятие по спортивной дисциплине. Следовательно, вам необходимо изменить порядок полей.
Изменение порядка дочернего уровня в иерархии
Чтобы переместить поле Дисциплина на позицию после поля Спорт, выполните следующие действия:
Щелкните правой кнопкой мыши поле «Дисциплина» в иерархии событий.
Выберите «Вверх» из раскрывающегося списка.
Порядок полей изменится на Спорт-Дисциплина-Событие.
Сводная таблица с изменениями в иерархии
Чтобы просмотреть изменения, внесенные в EventHierarchy в сводной таблице, вам не нужно создавать новую сводную таблицу. Вы можете просмотреть их в самой существующей сводной таблице.
Щелкните рабочий лист со сводной таблицей в окне Excel.
Как вы можете заметить, в списке полей сводной таблицы дочерние уровни в иерархии событий отражают изменения, внесенные вами в иерархию в модели данных. Те же изменения также отражаются в сводной таблице соответственно.
Щелкните знак + перед надписью «Водные виды спорта» в сводной таблице. Дочерние уровни отображаются как значения поля Дисциплина.
Скрытие и отображение иерархий
Вы можете скрыть иерархии и показывать их, когда захотите.
Снимите флажок Иерархии в верхнем меню представления диаграммы, чтобы скрыть иерархии.
Установите флажок Иерархии, чтобы отобразить иерархии.
Создание иерархии другими способами
Помимо того, как вы создавали иерархию в предыдущих разделах, вы можете создать иерархию еще двумя способами.
1. Нажмите кнопку «Создать иерархию» в правом верхнем углу таблицы данных «Медали» в виде диаграммы.
В таблице создается новая иерархия без каких-либо полей.
Перетащите поля Год и Сезон в указанном порядке в новую иерархию. Иерархия показывает дочерние уровни.
2. Другой способ создания такой же иерархии заключается в следующем:
Щелкните правой кнопкой мыши поле Год в таблице данных медалей в виде диаграммы.
В раскрывающемся списке выберите «Создать иерархию».
В таблице создается новая иерархия с дочерним полем Год.
Перетащите сезон поля в иерархию. Иерархия показывает дочерние уровни.
Удаление иерархии
Вы можете удалить иерархию из модели данных следующим образом:
Щелкните правой кнопкой мыши иерархию.
Выберите Удалить из раскрывающегося списка.
В Confirmпоявится диалоговое окно. НажмитеDelete from Model.
Иерархия удаляется.
Расчеты с использованием иерархии
Вы можете создавать вычисления с использованием иерархии. В иерархии событий вы можете отобразить количество медалей на дочернем уровне в процентах от количества медалей на родительском уровне следующим образом:
Щелкните правой кнопкой мыши на значении количества медалей события.
В раскрывающемся списке выберите Параметры поля значений.
Откроется диалоговое окно «Параметры поля значений».
Щелкните значок Show Values As таб.
Выберите% от общей суммы родительской строки из списка и нажмите OK.
Дочерние уровни отображаются как процент от родительских итогов. В этом можно убедиться, суммируя процентные значения дочернего уровня родителя. Сумма будет 100%.
Детализация и детализация иерархии
Вы можете быстро переходить по уровням иерархии вверх и вниз с помощью инструмента Quick Explore.
Щелкните значение поля «Событие» в сводной таблице.
Щелкните инструмент «Быстрый просмотр»,
который появляется в правом нижнем углу ячейки, содержащей выбранное значение.
В Explore box with Drill Upпоявляется опция. Это связано с тем, что из Event вы можете только развернуть, так как под ним нет дочерних уровней.
Нажмите Drill Up.
Данные сводной таблицы детализированы до дисциплины.
Щелкните инструмент «Быстрый просмотр»,
Появится поле «Исследовать» с отображенными параметрами «Развернуть» и «Развернуть». Это потому, что из Дисциплины вы можете перейти к спорту или к событию.
Таким образом, вы можете быстро перемещаться вверх и вниз по иерархии.