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

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

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

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

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

Создание Power PivotTable

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

• Щелкните вкладку «Главная» на ленте в окне PowerPivot.

• Щелкните сводную таблицу на ленте.

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

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

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

Как вы можете заметить, макет Power PivotTable аналогичен макету сводной таблицы.

Список полей сводной таблицы отображается в правой части рабочего листа. Здесь вы найдете некоторые отличия от сводной таблицы. В списке полей Power PivotTable есть две вкладки - АКТИВНЫЕ и ВСЕ, которые отображаются под заголовком и над списком полей. Вкладка ВСЕ выделена. На вкладке ВСЕ отображаются все таблицы данных в модели данных, а на вкладке АКТИВНЫЙ отображаются все таблицы данных, выбранные для имеющейся Power PivotTable.

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

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

• У каждого имени таблицы будет символ

  
  слева.

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

• Перетащите продавца из таблицы продавца в область ROWS.
• Щелкните вкладку АКТИВНЫЙ.

Поле «Продавец» отображается в Power PivotTable, а таблица «Продавец» отображается на вкладке АКТИВНЫЙ.

• Щелкните вкладку ВСЕ.
• Щелкните Месяц и Сумма заказа в таблице Продажи.
• Щелкните вкладку АКТИВНЫЙ.

Обе таблицы - Продажи и Продавец отображаются на вкладке АКТИВНАЯ.

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

• Щелкните стрелку рядом с надписью «ВСЕ» в поле фильтра «Регион».
• Щелкните Выбрать несколько элементов.
• Щелкните Север и Юг.

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

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

Создание Power PivotChart

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

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

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

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

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

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

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

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

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

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

• В сводной диаграмме по умолчанию отображается столбчатая диаграмма.
• В области ЛЕГЕНДА добавляется ∑ ЗНАЧЕНИЯ.
• Значения отображаются в легенде сводной диаграммы с заголовком «Значения».
• На сводной диаграмме появятся кнопки поля значений.

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

• Нажмите

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

• Снимите флажок с легенды в элементах диаграммы.

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

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

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

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

Как и в случае с Power PivotTable, список полей Power PivotChart также содержит две вкладки - АКТИВНЫЕ и ВСЕ. Далее есть 4 области -

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

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

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

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

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

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

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

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

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

Иерархии в Power Pivot

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

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

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

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

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

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

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

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

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

• Создайте Power PivotTable.

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

• Щелкните стрелку
  
  перед EventHierarchy.
• Щелкните стрелку
  
  перед надписью «Дополнительные поля».

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

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

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

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

• Щелкните значок + перед Aquatics. Отобразятся значения поля DisciplineID в разделе «Водные виды спорта».

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

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

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

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

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

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

• Перейдите на вкладку «Показать значения как».
• Щелкните поле Показать значения как.
• Щелкните% от общей суммы родительской строки.

• Щелкните кнопку ОК.

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

Детализация и детализация иерархии

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

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

• Щелкните инструмент «Быстрый просмотр»,

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

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

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

• Щелкните инструмент «Быстрый просмотр»,

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

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

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

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

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

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

• Щелкните Power PivotChart.

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

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

• Щелкните Power PivotTable.

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

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

• Щелкните вкладку АНАЛИЗ в ИНСТРУМЕНТАХ Сводной таблицы на ленте.
• Нажмите «Вставить слайсер».

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

• Щелкните NOC_CountryRegion и Sport в таблице медалей.
• Щелкните ОК.

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

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

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

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

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

• Щелкните на слайсере NOC_CountryRegion.
• Перейдите на вкладку ОПЦИИ в ИНСТРУМЕНТЫ СЛАЙСЕР на ленте.
• Щелкните Подключения отчетов в группе слайсеров.

Для среза NOC_CountryRegion появится диалоговое окно Report Connections.

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

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

• Щелкните кнопку ОК.

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

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

Затем вы можете добавить дополнительные сведения в Power PivotChart и Power PivotTable.

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

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

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

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

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