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 и включать их в информационные панели. Как вы видели в предыдущем разделе, вы можете использовать параметры макета отчета, чтобы выбрать внешний вид отчетов. Например, с опцией - Показать в форме структуры и с выбранными чередующимися строками вы получите отчет, как показано ниже.

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

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