Power Pivot: как научиться создавать наглядные аналитические отчеты в Excel

25756
Сухарев Павел
начальник отдела планирования, бюджетного контроля и финансовой отчетности ЗАО «Глобус Телеком»
Excel Power Pivot – это инструмент, позволяющий создавать системы бизнес-аналитики. В статье рассмотрим методику определения одного и того же показателя из области управленческой отчетности - сначала на базе формул Excel, затем при помощи Excel Power Pivot, чтобы оценить все его возможности и преимущества.

Что такое Power Pivot

Excel Power Pivot – это инструмент, позволяющий пользователям создавать собственные системы бизнес-аналитики средствами Excel – на основе табличных баз данных.

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

Надстройка Excel Power Pivot работает только в среде Office 2010 и выше и обладает рядом возможностей:

  • выполняет все вычисления непосредственно в оперативной памяти компьютера, что позволяет добиться высокой производительности системы, а также обеспечить возможность обработки больших объемов данных (размеры исходных таблиц могут достигать миллионов строк);
  • позволяет создавать на своей базе сводные таблицы. Прикладная ценность Excel PowerPivot  для финансистов и аналитиков заключается в том, что с помощью надстройки теперь можно самостоятельно обрабатывать такие объемы информации, для которых ранее требовалось использовать специализированное ПО (СУБД) и привлекать квалифицированных ИТ-специалистов;
  • содержит язык выражений анализа данных (Data Analysis eXpression, сокращенно DAX), предназначенный для разработки новых правил бизнес-логики. Упрощенно его можно считать продвинутым вариантом формул массива. Формулы языка DAX специально разрабатывались таким образом, чтобы быть максимально приближенными к синтаксису Excel. Несмотря на свое сходство со стандартными функциями листа Excel, формулы DAX составляются только внутри оболочки Power Pivot. Язык DAX обладает широким спектром возможностей, а информация о различных способах его применения доступна в интернете.

Power Pivot: как научиться создавать наглядные аналитические отчеты в ExcelСкачайте файл Excel, анализируемый в статье

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

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

  1. В пределах одного календарного периода клиент может воспользоваться сразу несколькими услугами (приобрести несколько товаров) - поэтому для расчета совокупного уровня доходности нужно просуммировать доходы, полученные по всем услугам, оказанным клиенту в анализируемом периоде;
  2. В зависимости от величины рассчитанного дохода клиента нужно отнести к одной из заранее определенных групп.

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

Справка

Использование формул массива 

Напомним, кроме обычных функций листа (массив аргументов на входе - одно значение на выходе, которое записывается в ячейку таблицы) существует отдельный класс функций, которые называются формулами массива (массив аргументов на входе - массив значений на выходе). Такие операторы вводятся сразу для диапазона ячеек. При этом значение конкретного элемента зависит не только от общей для всех составляющей (единой формулы расчета), но и его места в массиве (так называемого контекста исполнения). Именно контекст исполнения является одним из тех краеугольных камней, на которых держится логика аналитических вычислений. Поэтому далее кратко объясним, в чем заключается принцип его работы в Excel.

Давайте рассмотрим выражение (см. рис. 1). Входными параметрами у него выступают два диапазона ячеек: «Строка», выделенная желтым цветом, и «Столбец» с синей заливкой. В области, ограниченной этими диапазонами, введена формула массива следующего вида: {=Строка Столбец}.

Пример использовая формул массива в Ecxel

Эта формула - компактная запись операции сложения пар элементов из исходных массивов. Как видно из рисунка, у нее одинаковое написание для всех элементов диапазона из области действия. При этом значения в ячейках получившейся таблицы отличаются друг от друга, что объясняется контекстом исполнения. Например, выделенная на рисунке ячейка E4 расположена в третьем столбце и второй строке массива, что соответствует второму элементу массива «Строка» и третьему элементу массива «Столбец».

Описанный режим организации вычислений выглядит не совсем привычно, ведь в формуле нет явного указания на то, какие именно ячейки для сложения следует выбирать. Поэтому для освоения этого приема требуется определенное время. Но после изучения данной техники пользователь сможет составлять выражения и для многомерной OLAP-среды. Таким образом, базовые возможности программы Excel расширяются за счет добавления в нее аналитических вычислений, основанных на синтаксисе формул листа.

Работа со стандартными формулами Excel

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

На рисунке 2 исходные данные представлены столбцами «Компания», «Услуга», «Период», «Доход». Мы хотим создать два новых вычисляемых столбца «Доход_Сумм», где рассчитан совокупный доход по клиенту в определенном периоде, а также «Доход_Уровень», который в зависимости от уровня «Доход_Сумм» определяет доходную группу (уровни дохода указаны в правой части рис. 2). 

Для удобства и наглядности последующих вычислений сразу преобразуем исходные данные в режим «Таблицы». Для этого воспользуемся соответствующей командой на вкладке «Вставка» и присвоим ей собственное имя «Таблица_Фактов».

Исходные данные для анализа

Наша цель. Для каждой строки таблицы фактов рассчитать суммарный доход, который соответствует клиенту и месяцу. Например, «Клиенту, А» в феврале 2013 года было оказано три услуги на сумму 15, 15 и 25 единиц. Поэтому в каждой из строк, которые относятся к данному периоду, должно быть проставлено одно и то же значение — 55 единиц (15 + 15 + 25). Такая операция выполняется в два этапа.

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

Этап 2. Суммируются значения атрибута «Доход» в получившемся отфильтрованном множестве.

Для выполнения данной задачи хорошо подходит функция СУММЕСЛИМН, позволяющая суммировать диапазоны сразу по нескольким условиям. Главный вопрос: каким образом вводить эту формулу в вычисляемый столбец таблицы. Ведь в таблицах любая формула должна задаваться сразу для всех строк одновременно. Нам же требуется определить всего одну, но такую, чтобы контекст ее исполнения менялся в зависимости от параметров активной строки.

Введем в любую из строк столбца «Доход_Сумм» выражение 1. Достаточно нажать Enter, после чего оно поместится во всех строках таблицы.

Выражение 1:
СУММЕСЛИМН([Доход]; [Компания]; Таблица_Фактов[[#Эта строка];[Компания]]; [Период]; Таблица_Фактов[[#Эта строка]; [Период]])

Разберем, как работает формула. При каждом вызове (в каждой строке) функции СУММЕСЛИМН используется сразу вся таблица фактов (ее столбец «Доход»). Но при каждой итерации суммируются только те строки, которые соответствуют значениям атрибутов текущей строки. Для этого в формулу добавлен специальный аргумент - [# Эта строка], который играет роль ссылки на текущую строку таблицы. В частности, для строк, относящихся к «Клиенту, А» и февралю 2013, значение параметра [[#Эта строка];[Компания]] будет равным «Клиент, А», а [[#Эта строка]; [Период]] - «01.02.2013».

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

Отметим, что представленный формат записи существенно отличается от штатного режима Excel, где входные параметры указываются в формулах явным образом. Например, мы можем преобразовать таблицу фактов обратно в обычный диапазон («Преобразовать в диапазон» на вкладке «Работа с таблицами»). Тогда, к примеру, выражение 1 для строки 5 примет вид выражения 2:

Выражение 2:
=СУММЕСЛИМН(Обычный_Диапазон!$D$2:$D$37; Обычный_Диапазон!$A$2:$A$37; Обычный_Диапазон!$A5; Обычный_Диапазон!$C$2:$C$37; Обычный_Диапазон!$C5)

Для строки 6 выражение будет выглядеть идентично, с тем лишь отличием, что вместо $A5 будет $A6 (а вместо $C5 - $C6). При этом результат вычисления выражения 2 совпадает со значением выражения 1 в соответствующей строке, что позволяет считать их тождественными.

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

В частности, функция ИНДЕКС позволяет в любой ячейке рабочей книги элементарно получить значение из двумерного массива, нам нужно лишь знать соответствующий идентификатор объекта. Напишем в столбце «Доход_Уровень» выражение 3:

Выражение 3:
=ИНДЕКС(Уровни[Уровень]; ПОИСКПОЗ(Таблица_Фактов[[#Эта строка]; [Доход_Сумм]]; Уровни[Начало]))

Представленная в выражении 3 формула, как и выражение 1, выполняется в текущем контексте таблицы фактов - в нем используется ссылка на активную строку. Кроме того, при помощи функции ИНДЕКС мы обращаемся к внешнему объекту - таблице «Уровни». В ней с помощью функции ПОИСКПОЗ ищется подходящая строка.

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

Работа в Excel Power Pivot

Рассмотрим, как в Power Pivot выполняются операции, описанные в первой части статьи.

Установка Excel Power Pivot. Сначала надстройку следует установить (скачать ее можно бесплатно с официального сайта Microsoft). На ленте Excel должна появиться новая вкладка «Power Pivot». Если надстройка не включилась сразу, ее нужно включить в ручном режиме: «Параметры Excel» - «Надстройки», в списке «Управление» выбрать значение «Надстройки COM» - «Перейти». Остается поставить галочку напротив пункта «Power Pivot for Excel». Окно активно только в документах формата xlsx.

Подготовка исходных данных. В БД PowerPivot поддерживается широкий перечень разнообразных источников. В частности, в нее можно загрузить данные из СУБД MS Access или SQL Server. В этом случае объем обрабатываемой информации может исчисляться миллионами строк. Но для обычных пользователей более ценной является возможность хранить исходную информацию на листах обычной книги Excel.

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

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

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

При копировании исходных данных на новые листы последним рекомендуется сразу присваивать информативные названия. При последующем импорте данных в среду Power Pivot исходные имена объектов становятся названиями соответствующих таблиц в модели.

Далее создаем новую книгу Excel, которую назовем «ИсхДанные». Поместим в нее на один лист с названием «Таблица_Фактов» таблицу с исходными данными, а на другой лист «Уровни» - справочную таблицу с уровнями доходности.

Теперь давайте создадим еще одну книгу Excel, вызовем в ней окно PowerPivot и импортируем в нее данные из файла «ИсхДанные». Для импорта данных нужно вызвать в меню «Файл» команду «Получить внешние данные из других источников», а затем тип «Файл Excel», запустив мастер импорта данных.

На первом этапе его работы указываем физическое размещение файла с данными. Советуем сразу включить опцию «Использовать первую строку для заголовков столбцов» - тогда столбцы таблицы PowerPivot в автоматическом режиме получат имена столбцов из книги Excel.

На втором этапе выбираем, какие именно таблицы будут использоваться в модели. В нашем случае следует выбрать обе.

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

Данные в среде PowerPivot

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

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

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

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

Как и в обычных таблицах, формулы DAX выполняются в контексте строки. Но в отличие от таблиц, в них нет необходимости указывать параметр [# Эта строка] явным образом. Проще говоря, даже не записывая параметр [# Эта строка] в формулу DAX, можно считать, что он в ней присутствует, и использовать его в вычислениях. Такая особенность хоть и делает формулы DAX менее наглядными, но зато позволяет записывать выражения более компактно.

Учитывая перечисленные особенности работы с формулами DAX, создадим новый столбец «Доходность» со следующей формулой (выражение 4):

Выражение 4:
=CALCULATE(sum([Сумма]); ALLEXCEPT('Таблица_Фактов'; 'Таблица_Фактов'[Компания]; 'Таблица_Фактов'[Период]))

Разберем принцип работы выражения 4.

Сначала данный оператор для каждой строки таблицы фактов получает на вход временную таблицу, совпадающую с самой таблицей фактов. Затем к этой промежуточной таблице применяются фильтры, определенные текущим контекстом. В ней остаются строки, атрибуты которых совпадают со значениями текущей строки: [[#Эта строка]; [Компания]], [[#Эта строка]; [Услуга]], [[#Эта строка]; [Период]], [[#Эта строка]; [Сумма]].

Однако выражение ALLEXCEPT ('Таблица_Фактов'; 'Таблица_Фактов' [Компания]; 'Таблица_Фактов' [Период]) требует, чтобы для всех столбцов, кроме [Компания] и [Период], фильтры текущего контекста были убраны. Получается, что в промежуточной таблице мы оставляем только те строки, у которых с текущей строкой совпадают значения атрибутов [Компания] и [Период]. В отфильтрованной таким образом таблице выбирается атрибут [Сумма], значения которого затем суммируются.

На первый взгляд, формула может показаться чересчур запутанной, но в действительности она лишь воспроизводит вычисления выражения 1, которое мы приводили в самом начале статьи. Значения в столбце «Доходность» будут наглядным подтверждением, поскольку они совпадают со столбцом «Доход_Сумм» на рис. 2.

Теперь добавим в таблицу еще один вычисляемый столбец - «Техн_Уровень». В нем напротив каждой рассчитанной на первом этапе суммы укажем численное значение шкалы из таблицы «Уровни». Фактически нам требуется в базовой «Таблице_Фактов» получить значения из другой таблицы. Как и в случае с обычными расчетами в Excel, для этого достаточно выбрать подходящую функцию и передать ей в качестве аргумента идентификатор нужного объекта.

Введем в столбец «Техн_Уровень» следующую формулу (выражение 5):

Выражение 5:
=CALCULATE(max('Уровни'[Начало]);filter(all('Уровни'[Начало]);'Уровни'[Начало]

В представленном выражении агрегирующая функция MAX() применяется уже к внешней таблице «Уровни». Сначала она фильтруется в соответствии с ограничениями текущего контекста — в таблице оставляются только те строки, которые меньше числа в столбце «Доходность» активной строки.

Наконец, давайте добавим в нашу таблицу последний вычисляемый столбец — «Дох_Уровень». В нем мы будем выводить строку с названием уровня из таблицы «Уровни». Такие операции выполняются с помощью функций ВПР в Excel и ГПР (VLOOKUP и HLOOKUP). В языке DAX существует функция LOOKUPVALUE, аналогичная оператору ВПР.

Поэтому в столбец «Дох_Уровень» достаточно записать простое выражение следующего вида (выражение 6):

Выражение 6:
=LOOKUPVALUE('Уровни'[Уровень]; 'Уровни'[Начало]; [Техн_Уровень])

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

Для ограничения множества аналитик в Excel воспользуемся командой «Скрыть из набора клиентских средств» (для этого достаточно кликнуть правой кнопкой мыши на нужном нам столбце и выбрать соответствующий пункт в контекстном меню). Скрытые столбцы в конструкторе моделей закрашиваются серым цветом. Для перехода в среду Excel и работы с получившимся сводным отчетом достаточно выбрать команду «Переключиться в книгу Excel».

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

Справочную информацию по работе с надстройкой PowerPivot и языком DAX, в том числе руководство «Обучение основам DAX за 30 минут» на официальном сайте MS Office, можно найти в интернете. 

Power Pivot: как научиться создавать наглядные аналитические отчеты в ExcelСкачайте файл Excel, анализируемый в статье



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

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

Школа

Школа

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

Записаться

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

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

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

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

А еще...


Рассылка



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

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

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


  • Мы в соцсетях
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

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

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