Excel Power Pivot — Иерархии

Иерархия в модели данных представляет собой список вложенных столбцов в таблице данных, которые рассматриваются как один элемент при использовании в сводной таблице Power. Например, если в таблице данных есть столбцы — Страна, Штат, Город, можно определить иерархию, чтобы объединить три столбца в одно поле.

В списке Поля Power PivotTable иерархия отображается как одно поле. Таким образом, вы можете добавить только одно поле в сводную таблицу вместо трех полей в иерархии. Кроме того, он позволяет вам осмысленно перемещаться вверх или вниз по вложенным уровням.

Рассмотрим следующую модель данных для иллюстраций в этой главе.

Создание Иерархии

Вы можете создавать иерархии в виде диаграммы модели данных. Обратите внимание, что вы можете создать иерархию, основанную только на одной таблице данных.

• Нажмите на столбцы — Спорт, DisciplineID и Событие в таблице данных Medal в указанном порядке. Помните, что порядок важен для создания значимой иерархии.

• Щелкните правой кнопкой мыши по выбору.

• Выберите Создать иерархию из выпадающего списка.

Нажмите на столбцы — Спорт, DisciplineID и Событие в таблице данных Medal в указанном порядке. Помните, что порядок важен для создания значимой иерархии.

Щелкните правой кнопкой мыши по выбору.

Выберите Создать иерархию из выпадающего списка.

Создается поле иерархии с тремя выбранными полями в качестве дочерних уровней.

Переименование Иерархии

Чтобы переименовать поле иерархии, выполните следующие действия:

• Щелкните правой кнопкой мыши на Hierarchy1.

• Выберите Rename из выпадающего списка.

Щелкните правой кнопкой мыши на Hierarchy1.

Выберите Rename из выпадающего списка.

Введите EventHierarchy .

Создание сводной таблицы с иерархией в модели данных

Вы можете создать Power PivotTable, используя иерархию, созданную в модели данных.

• Перейдите на вкладку «Сводная таблица» на ленте в окне Power Pivot.

• Нажмите Сводная таблица на ленте.

Перейдите на вкладку «Сводная таблица» на ленте в окне Power Pivot.

Нажмите Сводная таблица на ленте.

Откроется диалоговое окно « Создание сводной таблицы ». Выберите «Новый лист» и нажмите «ОК».

Пустая сводная таблица создается на новом листе.

В списке Поля сводной таблицы EventHierarchy отображается как поле в таблице медалей. Другие поля в таблице медалей свернуты и показаны как Дополнительные поля.

• Нажмите на стрелку перед EventHierarchy.

• Нажмите на стрелку перед больше полей.

Нажмите на стрелку перед EventHierarchy.

Нажмите на стрелку перед больше полей.

Поля в EventHierarchy будут отображаться. Все поля в таблице медалей будут отображаться в разделе «Дополнительные поля».

Как вы можете заметить, три поля, которые вы добавили в иерархию, также отображаются в разделе Дополнительные поля с флажками. Если вы не хотите, чтобы они отображались в списке полей сводной таблицы в разделе « Дополнительные поля» , необходимо скрыть столбцы в таблице данных — «Медали» в представлении данных в окне Power Pivot. Вы всегда можете показать их, когда захотите.

Добавьте поля в сводную таблицу следующим образом:

• Перетащите EventHierarchy в область ROWS.

• Перетащите Медаль в область ∑ VALUES.

Перетащите EventHierarchy в область ROWS.

Перетащите Медаль в область ∑ VALUES.

Значения поля «Спорт» отображаются в сводной таблице со знаком «+» перед ними. Количество медалей для каждого вида спорта отображается.

• Нажмите на знак + перед водными видами спорта. Будут отображены значения поля DisciplineID в разделе «Водные виды спорта».

• Нажмите на ребенка D22, который появляется. Будут отображены значения поля события под D22.

Нажмите на знак + перед водными видами спорта. Будут отображены значения поля DisciplineID в разделе «Водные виды спорта».

Нажмите на ребенка D22, который появляется. Будут отображены значения поля события под D22.

Как вы можете заметить, количество медалей указано для событий, которые суммируются на родительском уровне — DisciplineID , которые далее суммируются на родительском уровне — спорт.

Создание иерархии на основе нескольких таблиц

Предположим, что вы хотите отобразить Disciplines в сводной таблице, а не DisciplineID, чтобы сделать ее более читабельным и понятным обобщением. Для этого вам нужно иметь поле Discipline в таблице медалей, которого, как вы знаете, нет. Поле Discipline находится в таблице данных Disciplines, но вы не можете создать иерархию с полями из более чем одной таблицы. Но есть способ получить обязательное поле из другой таблицы.

Как вы знаете, таблицы — медали и дисциплины связаны между собой. Вы можете добавить поле Discipline из таблицы Disciplines в таблицу медалей, создав столбец, используя связь с DAX.

• Нажмите на представление данных в окне Power Pivot.

• Перейдите на вкладку «Дизайн» на ленте.

• Нажмите Добавить.

Нажмите на представление данных в окне Power Pivot.

Перейдите на вкладку «Дизайн» на ленте.

Нажмите Добавить.

Столбец — Добавить столбец с правой стороны таблицы будет подсвечен.

Введите = RELATED (Disciplines [Discipline]) в строке формул. Новый столбец — CalculatedColumn1 создается со значениями в качестве значений поля Discipline в таблице Disciplines.

Переименуйте новый столбец, полученный таким образом в таблице медалей, в Discipline. Затем вы должны удалить DisciplineID из Иерархии и добавить Discipline, что вы узнаете в следующих разделах.

Удаление уровня ребенка из иерархии

Как вы можете заметить, иерархия видна только в виде диаграммы, а не в виде данных. Следовательно, вы можете редактировать иерархию только в виде диаграммы.

• Нажмите на представление схемы в окне Power Pivot.

• Щелкните правой кнопкой мыши DisciplineID в EventHierarchy.

• Выберите Удалить из иерархии в раскрывающемся списке.

Нажмите на представление схемы в окне Power Pivot.

Щелкните правой кнопкой мыши DisciplineID в EventHierarchy.

Выберите Удалить из иерархии в раскрывающемся списке.

Откроется диалоговое окно подтверждения. Нажмите Удалить из иерархии .

Поле DisciplineID удаляется из иерархии. Помните, что вы удалили поле из иерархии, но исходное поле все еще существует в таблице данных.

Далее вам нужно добавить поле Discipline в EventHierarchy.

Добавление дочернего уровня в иерархию

Вы можете добавить поле Discipline к существующей иерархии — EventHierarchy следующим образом —

• Нажмите на поле в таблице медалей.

• Перетащите его в поле «События» ниже в иерархии событий.

Нажмите на поле в таблице медалей.

Перетащите его в поле «События» ниже в иерархии событий.

Поле Discipline добавляется в EventHierarchy.

Как вы можете видеть, порядок полей в EventHierarchy имеет вид Sport – Event – ​​Discipline. Но, как вы знаете, это должен быть Sport-Discipline-Event. Следовательно, вам нужно изменить порядок полей.

Изменение порядка дочернего уровня в иерархии

Чтобы переместить поле «Дисциплина» в положение после поля «Спорт», выполните следующие действия:

• Щелкните правой кнопкой мыши на поле Discipline в EventHierarchy.

• Выберите Move Up из выпадающего списка.

Щелкните правой кнопкой мыши на поле Discipline в EventHierarchy.

Выберите Move Up из выпадающего списка.

Порядок полей меняется на Sport-Discipline-Event.

Сводная таблица с изменениями в иерархии

Чтобы просмотреть изменения, внесенные в EventHierarchy в сводную таблицу, вам не нужно создавать новую сводную таблицу. Вы можете просмотреть их в самой существующей сводной таблице.

Нажмите на лист с окном сводной таблицы в Excel.

Как вы можете заметить, в списке полей сводной таблицы дочерние уровни в иерархии событий отражают изменения, сделанные в иерархии в модели данных. Те же изменения также отражаются в сводной таблице соответственно.

Нажмите на знак + перед Aquatics в сводной таблице. Дочерние уровни отображаются как значения поля Discipline.

Скрытие и Отображение Иерархий

Вы можете скрыть иерархии и показывать их в любое время.

• Снимите флажок Иерархии в верхнем меню представления схемы, чтобы скрыть иерархии.

• Установите флажок Иерархии, чтобы показать иерархии.

Снимите флажок Иерархии в верхнем меню представления схемы, чтобы скрыть иерархии.

Установите флажок Иерархии, чтобы показать иерархии.

Создание иерархии другими способами

В дополнение к тому, как вы создали иерархию в предыдущих разделах, вы можете создать иерархию еще двумя способами.

1. Нажмите кнопку «Создать иерархию» в верхнем правом углу таблицы данных «Медали» в виде диаграммы.

Новая иерархия создается в таблице без каких-либо полей в ней.

Перетащите поля Год и Сезон в указанном порядке в новую иерархию. Иерархия показывает дочерние уровни.

2. Другой способ создания той же иерархии заключается в следующем:

• Щелкните правой кнопкой мыши поле Год в таблице данных Медали в виде диаграммы.

• Выберите Создать иерархию из выпадающего списка.

Щелкните правой кнопкой мыши поле Год в таблице данных Медали в виде диаграммы.

Выберите Создать иерархию из выпадающего списка.

В таблице создается новая иерархия с дочерним полем Year.

Перетащите сезон поля в иерархию. Иерархия показывает дочерние уровни.

Удаление иерархии

Вы можете удалить иерархию из модели данных следующим образом:

• Щелкните правой кнопкой мыши по иерархии.

• Выберите Удалить из выпадающего списка.

Щелкните правой кнопкой мыши по иерархии.

Выберите Удалить из выпадающего списка.

Откроется диалоговое окно подтверждения . Нажмите Удалить из модели .

Иерархия удаляется.

Расчеты с использованием иерархии

Вы можете создавать расчеты, используя иерархию. В иерархии событий вы можете отобразить количество медалей на уровне ребенка в процентах от количества медалей на его родительском уровне следующим образом:

• Щелкните правой кнопкой мыши по значению «Количество медалей» события.

• Выберите Value Field Settings из выпадающего списка.

Щелкните правой кнопкой мыши по значению «Количество медалей» события.

Выберите Value Field Settings из выпадающего списка.

Откроется диалоговое окно «Настройки поля значений».

• Перейдите на вкладку « Показать значения как ».

• Выберите% от общего количества родительских строк в списке и нажмите OK.

Перейдите на вкладку « Показать значения как ».

Выберите% от общего количества родительских строк в списке и нажмите OK.

Дочерние уровни отображаются в процентах от родительских итогов. Вы можете убедиться в этом, суммируя процентные значения дочернего уровня родителя. Сумма будет 100%.

Развертывание и сверление иерархии

Вы можете быстро переходить вверх и вниз по уровням в иерархии, используя инструмент Quick Explore.

• Щелкните значение поля «Событие» в сводной таблице.

• Нажмите на инструмент Quick Explore — который появляется в правом нижнем углу ячейки, содержащей выбранное значение.

Щелкните значение поля «Событие» в сводной таблице.

Нажмите на инструмент Quick Explore — который появляется в правом нижнем углу ячейки, содержащей выбранное значение.

Появится окно « Исследовать» с детализацией . Это потому, что из Event вы можете только углубиться, так как под ним нет дочерних уровней.

Нажмите Drill Up .

Данные сводной таблицы развертываются до дисциплины.

Нажмите на инструмент Quick Explore — который появляется в правом нижнем углу ячейки, содержащей значение.

Появится окно «Исследовать» с отображенными параметрами «Детализация вверх» и «Детализация» Это потому, что из Дисциплины вы можете перейти к Спорту или к Событию.

Таким образом, вы можете быстро перемещаться вверх и вниз по иерархии.