Power PivotTables & Power PivotCharts

Когда ваши наборы данных большие, вы можете использовать Excel Power Pivot, который может обрабатывать сотни миллионов строк данных. Данные могут находиться во внешних источниках данных, и Excel Power Pivot создает модель данных, которая работает в режиме оптимизации памяти. Вы можете выполнить расчеты, проанализировать данные и получить отчет, чтобы сделать выводы и решения. Отчет может быть либо Power PivotTable, либо Power PivotChart, либо их комбинацией.

Вы можете использовать Power Pivot в качестве специального решения для отчетности и аналитики. Таким образом, для человека, имеющего практический опыт работы с Excel, было бы возможно выполнить анализ данных высокого уровня и принять решение за считанные минуты, и это является большим преимуществом для включения в информационные панели.

Использование Power Pivot

Вы можете использовать Power Pivot для следующих целей:

• Для выполнения мощного анализа данных и создания сложных моделей данных.
• Быстрое объединение больших объемов данных из нескольких разных источников.
• Для анализа информации и обмена знаниями в интерактивном режиме.
• Создать ключевые показатели эффективности (KPI).
• Для создания Power PivotTables.
• Создать Power PivotCharts.

Различия между сводной таблицей и мощной сводной таблицей

Power PivotTable по своей структуре напоминает сводную таблицу со следующими отличиями:

• Сводная таблица основана на таблицах Excel, тогда как Power PivotTable основана на таблицах данных, которые являются частью модели данных.

• Сводная таблица основана на одной таблице Excel или диапазоне данных, тогда как Power PivotTable может основываться на нескольких таблицах данных, если они добавлены в модель данных.

• Сводная таблица создается из окна Excel, тогда как Power PivotTable создается из окна PowerPivot.

Сводная таблица основана на таблицах Excel, тогда как Power PivotTable основана на таблицах данных, которые являются частью модели данных.

Сводная таблица основана на одной таблице Excel или диапазоне данных, тогда как Power PivotTable может основываться на нескольких таблицах данных, если они добавлены в модель данных.

Сводная таблица создается из окна Excel, тогда как Power PivotTable создается из окна PowerPivot.

Создание Power PivotTable

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

• Нажмите на вкладку «Главная» в окне «Лента в PowerPivot».

• Нажмите на сводную таблицу на ленте.

• Нажмите на сводную таблицу в раскрывающемся списке.

Нажмите на вкладку «Главная» в окне «Лента в PowerPivot».

Нажмите на сводную таблицу на ленте.

Нажмите на сводную таблицу в раскрывающемся списке.

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

Нажмите кнопку ОК. Новый лист создается в окне Excel, и появляется пустая сводная таблица Power.

Как вы можете заметить, макет Power PivotTable похож на макет PivotTable.

Список полей сводной таблицы появится в правой части листа. Здесь вы найдете некоторые отличия от сводной таблицы. В списке полей Power PivotTable есть две вкладки — ACTIVE и ALL, которые отображаются под заголовком и над списком полей. Вкладка ВСЕ выделена. На вкладке ALL отображаются все таблицы данных на модели данных, а на вкладке ACTIVE отображаются все таблицы данных, выбранные для сводной таблицы Power под рукой.

• Щелкните по именам таблиц в списке Поля сводной таблицы в разделе ВСЕ.

Щелкните по именам таблиц в списке Поля сводной таблицы в разделе ВСЕ.

Появятся соответствующие поля с флажками.

• Каждое имя таблицы будет иметь символ с левой стороны.

• Если вы поместите курсор на этот символ, отобразятся Источник данных и Имя таблицы модели этой таблицы данных.

Каждое имя таблицы будет иметь символ с левой стороны.

Если вы поместите курсор на этот символ, отобразятся Источник данных и Имя таблицы модели этой таблицы данных.

• Перетащите Продавца из таблицы Продавца в область ROWS.
• Нажмите на вкладку ACTIVE.

Поле Salesperson отображается в Power PivotTable, а таблица Salesperson отображается на вкладке ACTIVE.

• Нажмите на вкладку ВСЕ.
• Нажмите на месяц и сумму заказа в таблице продаж.
• Нажмите на вкладку ACTIVE.

Обе таблицы — Sales и Salesperson отображаются на вкладке ACTIVE.

• Перетащите Месяц в область КОЛОННЫ.
• Перетащите область в область ФИЛЬТРЫ.

• Нажмите на стрелку рядом с ALL в окне фильтра Region.
• Нажмите «Выбрать несколько элементов».
• Нажмите на север и юг.

• Нажмите кнопку ОК. Сортируйте метки столбцов в порядке возрастания.

Power PivotTable можно динамически изменять, чтобы исследовать и сообщать данные.

Создание Power PivotChart

Power PivotChart — это сводная диаграмма, основанная на модели данных и созданная из окна Power Pivot. Хотя он имеет некоторые функции, аналогичные Excel PivotChart, есть и другие функции, которые делают его более мощным.

Предположим, вы хотите создать сводную диаграмму Power на основе следующей модели данных.

• Нажмите на вкладку «Главная» на ленте в окне Power Pivot.
• Нажмите на сводную таблицу.
• Нажмите на сводную диаграмму в раскрывающемся списке.

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

• Нажмите кнопку ОК. Пустая сводная диаграмма создается на новом листе в окне Excel. В этой главе, когда мы говорим PivotChart, мы имеем в виду Power PivotChart.

Нажмите кнопку ОК. Пустая сводная диаграмма создается на новом листе в окне Excel. В этой главе, когда мы говорим PivotChart, мы имеем в виду Power PivotChart.

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

• Щелкните по таблице «Продавец» в списке «Поля сводной диаграммы».
• Перетащите поля — Продавец и Регион в область AXIS.

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

• Перетащите TotalSalesAmount из каждой из 4 таблиц — East_Sales, North_Sales, South_Sales и West_Sales в область ∑ VALUES.

Перетащите TotalSalesAmount из каждой из 4 таблиц — East_Sales, North_Sales, South_Sales и West_Sales в область ∑ VALUES.

Как вы можете заметить, на листе появляется следующее:

• В сводной диаграмме столбчатая диаграмма отображается по умолчанию.
• В области LEGEND добавляется ∑ VALUES.
• Значения отображаются в легенде в сводной диаграмме с заголовком «Значения».
• Кнопки поля значения отображаются на сводной диаграмме.

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

• Нажми на Кнопка в правом верхнем углу сводной диаграммы.

• Отмените выделение легенды в элементах диаграммы.

Нажми на Кнопка в правом верхнем углу сводной диаграммы.

Отмените выделение легенды в элементах диаграммы.

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

• Нажмите кнопку «Скрыть значение поля кнопки на диаграмме» в раскрывающемся списке.

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

Нажмите кнопку «Скрыть значение поля кнопки на диаграмме» в раскрывающемся списке.

Кнопки полей значений на графике будут скрыты.

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

Как и в случае Power PivotTable, список полей Power PivotChart также содержит две вкладки — ACTIVE и ALL. Далее есть 4 зоны —

• ОСЬ (Категории)
• ЛЕГЕНДА (серия)
• ∑ ЦЕННОСТИ
• ФИЛЬТРЫ

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

Комбинации таблиц и диаграмм

Power Pivot предоставляет вам различные комбинации Power PivotTable и Power PivotChart для исследования данных, визуализации и создания отчетов.

Рассмотрим следующую модель данных в Power Pivot, которую мы будем использовать для иллюстраций:

В Power Pivot вы можете использовать следующие комбинации таблиц и диаграмм.

• Диаграмма и таблица (горизонтальные) — вы можете создать Power PivotChart и Power PivotTable, расположенные рядом друг с другом по горизонтали на одном листе.

Диаграмма и таблица (горизонтальные) — вы можете создать Power PivotChart и Power PivotTable, расположенные рядом друг с другом по горизонтали на одном листе.

Диаграмма и таблица (по вертикали) — вы можете создать Power PivotChart и Power PivotTable, расположенные один под другим вертикально в одном листе.

Эти комбинации и некоторые другие доступны в раскрывающемся списке, который появляется при нажатии на сводную таблицу на ленте в окне Power Pivot.

Иерархии в Силе Власти

Вы можете использовать иерархии в Power Pivot для выполнения вычислений, для детализации и детализации вложенных данных.

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

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

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

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

• Нажмите «Создать иерархию» в раскрывающемся списке.

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

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

Нажмите «Создать иерархию» в раскрывающемся списке.

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

• Щелкните правой кнопкой мыши имя иерархии.
• Нажмите на Rename в выпадающем списке.
• Введите значимое имя, скажем, EventHierarchy.

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

• Создать Power PivotTable.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Нажмите на вкладку «Показать значения как».
• Нажмите на поле Показать значения как.
• Нажмите на% от общего количества родительских строк.

• Нажмите кнопку ОК.

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

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

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

• Щелкните по значению поля «Событие» в Power PivotTable.

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

Щелкните по значению поля «Событие» в Power PivotTable.

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

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

• Нажмите на Drill Up. Данные Power PivotTable развернуты до уровня Дисциплины.

Нажмите на Drill Up. Данные Power PivotTable развернуты до уровня Дисциплины.

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

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

Появится окно EXPLORE с отображенными параметрами Drill Up и Drill Down. Это связано с тем, что в Дисциплине вы можете перейти к спорту или к уровням событий.

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

Использование общего слайсера

Вы можете вставлять слайсеры и делиться ими между Power PivotTables и Power PivotCharts.

• Создайте Power PivotChart и Power PivotTable рядом друг с другом по горизонтали.

• Нажмите на Power PivotChart.

• Перетащите дисциплину из таблицы дисциплин в область ОСей.

• Перетащите медаль из таблицы медалей в область ∑ ЗНАЧЕНИЯ.

• Нажмите на Power PivotTable.

• Перетащите Discipline из таблицы Disciplines в область ROWS.

• Перетащите медаль из таблицы медалей в область ∑ ЗНАЧЕНИЯ.

Создайте Power PivotChart и Power PivotTable рядом друг с другом по горизонтали.

Нажмите на Power PivotChart.

Перетащите дисциплину из таблицы дисциплин в область ОСей.

Перетащите медаль из таблицы медалей в область ∑ ЗНАЧЕНИЯ.

Нажмите на Power PivotTable.

Перетащите Discipline из таблицы Disciplines в область ROWS.

Перетащите медаль из таблицы медалей в область ∑ ЗНАЧЕНИЯ.

• Нажмите на вкладку ANALYZE в PIVOTTABLE TOOLS на ленте.
• Нажмите на Вставить слайсер.

Откроется диалоговое окно «Вставить слайсеры».

• Нажмите на NOC_CountryRegion и Спорт в таблице медалей.
• Нажмите на ОК.

Появятся два слайсера — NOC_CountryRegion и Sport.

• Расположите их по размеру, чтобы они правильно совмещались рядом с Power PivotTable, как показано ниже.

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

• Нажмите на США в слайсере NOC_CountryRegion.
• Нажмите на водные виды спорта в спортивном слайсере.

Power PivotTable фильтруется по выбранным значениям.

Как вы можете видеть, Power PivotChart не фильтруется. Чтобы фильтровать Power PivotChart с теми же фильтрами, вы можете использовать те же слайсеры, которые вы использовали для Power PivotTable.

• Нажмите на слайсер NOC_CountryRegion.
• Нажмите на вкладку OPTIONS в SLICER TOOLS на ленте.
• Нажмите на Соединения отчетов в группе Slicer.

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

Как вы можете заметить, все Power PivotTables и Power PivotChart в книге перечислены в диалоговом окне.

• Нажмите на Power PivotChart, которая находится на том же рабочем листе, что и выбранная Power PivotTable.

• Нажмите кнопку ОК.

• Повторите для Sport Slicer.

Нажмите на Power PivotChart, которая находится на том же рабочем листе, что и выбранная Power PivotTable.

Нажмите кнопку ОК.

Повторите для Sport Slicer.

Power PivotChart также фильтруется по значениям, выбранным в двух слайсерах.

Далее вы можете добавить больше деталей к Power PivotChart и Power PivotTable.

• Нажмите на Power PivotChart.
• Перетащите Пол в область LEGEND.
• Щелкните правой кнопкой мыши Power PivotChart.
• Нажмите на Изменить тип диаграммы.
• Выберите столбец с накоплением в диалоговом окне «Изменить тип диаграммы».
• Нажмите на Power PivotTable.
• Перетащите событие в область строк.
• Нажмите на вкладку ДИЗАЙН в СРЕДСТВАХ СМЕНЫ на Ленте.
• Нажмите на макет отчета.
• Нажмите Outline Form в раскрывающемся списке.

Эстетические отчеты для инструментальных панелей

Вы можете создавать эстетические отчеты с помощью Power PivotTables и Power PivotCharts и включать их в информационные панели. Как вы видели в предыдущем разделе, вы можете использовать параметры макета отчета, чтобы выбрать внешний вид отчетов. Например, с опцией «Показать в форме контура» и с выбранными полосчатыми строками вы получите отчет, как показано ниже.

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

Вы можете выбрать объекты, которые вы хотите отобразить в окончательном отчете на панели выбора. Например, если вы не хотите отображать срезы, которые вы создали и использовали, вы можете просто скрыть их, отменив выбор на панели выбора.