Как сделать сводную таблицу в Excel c помощью надстройки PowerPivot

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

С помощью надстройки PowerPivot (доступна для версий Excel 2010 и выше) можно сформировать сводные таблицы в Excel, которые «умеют» намного больше, чем ваши обычные отчеты. Рассмотрим подробнее, как создавать сводные таблицы в Excel используя PowerPivot.

Сначала я знакомлю вас с базовыми возможностями этой надстройки для Excel, а также встроенного в нее языка DAX (Data Analysis eXpression). Затем  рассмотрю некоторые улучшения в функциональности сводных таблиц в Excel в случае их перевода на платформу PowerPivot. В качестве примера использую модель, созданную в версии Excel 2013, которая не обладает обратной совместимостью с надстройками младших версий. Ее вы можете скачать в ополнительных материалах" к статье» (также вы можете скачать краткую инструкцию по работе со сводным отчетом на базе PowerPivot». — Прим. ред.).

Создание моделей данных

Слабым местом «классических» сводных таблиц в Excel является способ хранения исходных данных. В основе любого сводного отчета всегда лежит единственная плоская таблица, состоящая из набора столбцов. Причем каждый из столбцов может быть измерением либо мерой. Аналитики делятся на две группы: связанные с фактом непосредственно и связанные с фактом опосредованно через другие аналитики. Так, например, в таблице фактов хранится информация о выручке компаний, детализированная по типам услуг и календарным периодам. Предположим, что за каждой из  компаний-клиентов закреплен ответственный менеджер, который, в свою очередь, работает в одной из коммерческих дирекций. Нас же интересует управленческий отчет, позволяющий оценить эффективность работы на уровне дирекций. Если мы решим составить «классическe.» сводный отчет, исходную таблицу фактов придется сначала дополнить новым столбцом «Менеджер», а затем рассчитать на его базе вспомогательный столбец «Дирекция». Тогда таблица фактов вместо трех исходных столбцов измерений («Дата», «Сервис» и «Компания») будет содержать целых пять («Дата», «Сервис», «Компания», «Сотрудник», «Дирекция»).

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

В среде PowerPivot хранение информации основано на альтернативном подходе. Вместо того чтобы сводить все данные в одну большую сводную таблицу в Excel, предлагается логически разбить их на отдельные структурные блоки — таблицы фактов и  таблицы-справочники (содержат вторичные аналитические признаки). Затем все таблицы объединяются в единое логическое пространство посредством установления связей типа «один ко многим» между отдельными атрибутами различных таблиц. Рисунок 1 демонстрирует реализацию такой модели — дополнительная информация по компаниям, сотрудникам и дирекциям вынесена в отдельные таблицы. При этом таблица «Компания_Изм» связана с таблицей фактов напрямую (см. рис. 2), а таблица «Сотрудник_Изм» — опосредованно через промежуточное отношение.

Рисунок 1. Структура формирования отчета в PowerPivot

Как сделать сводную таблицу в Excel c помощью надстройки PowerPivot

Рисунок 2. Формирование связей в PowerPivot

Как сделать сводную таблицу в Excel c помощью надстройки PowerPivot

Переход на PowerPivot

Работа в среде PowerPivot во многом похожа на разработку обычных баз данных. Для пользователей, имеющих минимальный опыт создания БД в одной из стандартных СУБД, например MS Access, переход на новую платформу будет делом простым и интуитивно понятным. Нужно лишь помнить об основных отличительных особенностях баз данных табличного формата (Tabular Model). Перечислю их.

1. Установление связей возможно только между атрибутами разных таблиц. То есть запрещено «замыкание» одних атрибутов таблицы на другие атрибуты этой же таблицы (self-joins).

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

3. Табличные базы данных предназначены для выполнения задач представления данных (representing data). Поэтому операция установления связей между двумя таблицами на логическом уровне сводит их в единую общую «виртуальную» таблицу, ко всем атрибутам которой можно сразу обращаться из среды отчетов.

Последнее обстоятельство является, пожалуй, самой ценной особенностью баз данных нового формата. Поэтому раскроем эту тему более подробно. В среде MS Access установление связей между таблицами фактов и измерения «Компании» (из рис. 1) будет лишь способом задать ограничения целостности на значения вторичного ключа в таблице фактов. Существование такой связи требуется на этапе заведения информации в таблицу фактов, но ее наличие само по себе еще не позволяет просматривать данные из двух таблиц одновременно. Если вам, к примеру, потребуется отчет, в котором доходы детализируются по отдельным компаниям, придется сначала создать на базе исходных множеств новое производное множество посредством исполнения инструкции FROM оператора SELECT. Причем в зависимости от  бизнес-потребностей таких новых множеств (представлений) может потребоваться несколько.

Программа PowerPivot, напротив, не контролирует процесс заведения новых записей в БД — они загружаются в нее уже в готовом виде из внешних источников. Поэтому в ней установление связей — это именно создание постоянно действующего объединения исходных таблиц посредством выполнения операции INNER JOIN.

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

  • объединяют в одном месте информацию из разнородных источников, например Excel, текстового файла и базы данных;
  • позволяют задать семантику атрибутов, то есть заменить технические названия столбцов на имена, понятные пользователю. При необходимости в модели можно изменить порядок сортировки атрибутов. Типичный пример — показывать названия месяцев в естественном, а не алфавитном порядке: «Янв», «Фев», «Мар» и т. д.
  • Что такое CPM-система и зачем она нужна финансовому директору

Добавление контекста фильтра

Задание ракурса пространства посредством выставления набора фильтров относится к числу ключевых возможностей сводных отчетов. Причем контекст фильтров определяет результат вычисления полей и объектов сводной таблицы. Выражения в PowerPivot, написанные на языке DAX, работают аналогичным образом. Рассмотрим в качестве примера работу с мерой «Доход_Пред», которая для каждого календарного периода вычисляет доход, относящийся к прошлому месяцу.

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

Выражение 1:

=CALCULATE('Таблица_Фактов'[Доход];PREVIOUSMONTH('Дата_Изм'[Дата]))

Учитывая, что различные типы оператора CALCULATE применяются в разных расчетах, для выражения 1 есть более удобный вариант записи (выражение 2), позволяющий существенно упростить формулы DAX.

Выражение 2:

=[Доход](PREVIOUSMONTH('Дата_Изм'[Дата]))

Новые меры добавляются в сводный отчет двумя способами. Первый — в среде PowerPivot как вычисляемое поле таблицы фактов. Второй — непосредственно в Excel с помощью вызова команды «Добавить новую меру» в диспетчере метаданных. Этот способ предназначен прежде всего для конечных пользователей аналитических отчетов, поэтому в рамках данной статьи мы ограничимся только его рассмотрением. Добавление новой меры начинается с вывода на экран списка полей PowerPivot. Для этого нужно вызвать опцию «Список полей» в группе меню «Power Pivot», после чего окно с нужными объектами будет показываться на экране в автоматическом режиме при выборе макета соответствующей сводной таблицы.

Далее в диспетчере метаданных выбираем таблицу фактов, в которую нужно добавить новую меру, а затем команду «Добавить новую меру» в контекстном меню. Она выведет на экран диалоговую форму, позволяющую задать конкретные параметры показателя (см. рис. 3).

Рисунок 3. Диалоговое окно «Параметры меры» в PowerPivot

Как сделать сводную таблицу в Excel c помощью надстройки PowerPivot

В поле «Формула» введем выражение 2, а в поле «Имя меры» — строку «Доход_Пред». После подтверждения кнопкой «ОК» в многомерное пространство будет добавлена новая аналитика. Работать с ней можно так же, как и с обычным полем сводной таблицы.

Подготовлено по материалам журнала «Финансовый директор»

Методические рекомендации по управлению финансами компании

Вложенные файлы

Доступно только авторизованным пользователям
  • Документ №1.zip


Ваша персональная подборка

    Подписка на статьи

    Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

    Рекомендации по теме

    Школа

    Школа

    Проверь свои знания и приобрети новые

    Записаться

    Самое выгодное предложение

    Самое выгодное предложение

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

    Живое общение с редакцией

    А еще...




    © 2007–2017 ООО «Актион управление и финансы»

    «Финансовый директор» — практический журнал по управлению финансами компании

    Зарегистрировано Федеральной службой по надзору в сфере связи,
    информационных технологий и массовых коммуникаций (Роскомнадзор)
    Свидетельство о регистрации ПИ № ФС77-62253 от 03.07.2015;
    Политика обработки персональных данных
    Все права защищены. email: fd@fd.ru

    
    • Мы в соцсетях
    Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.
    ×
    Чтобы скачать документ, зарегистрируйтесь на сайте!

    Это бесплатно и займет всего 1 минуту.

    У меня есть пароль
    напомнить
    Пароль отправлен на почту
    Ввести
    Я тут впервые
    И получить доступ на сайт Займет минуту!
    Введите эл. почту или логин
    Неверный логин или пароль
    Неверный пароль
    Введите пароль

    Внимание!
    Вы читаете профессиональную статью для финансиста.
    Зарегистрируйтесь на сайте и продолжите чтение!

    Это бесплатно и займет всего 1 минут.

    У меня есть пароль
    напомнить
    Пароль отправлен на почту
    Ввести
    Я тут впервые
    И получить доступ на сайт Займет минуту!
    Введите эл. почту или логин
    Неверный логин или пароль
    Неверный пароль
    Введите пароль