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

С помощью веб-запроса и макроса загружаются сведения о погоде на каждый день в течение трех лет. Рассматриваются примеры мер DAX, которые представляют собой совокупность метеопрогнозов и данных о продажах. Подобная совокупность позволяет определить связь между погодой и объемом продаж.

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

• WeekdayName, использующий формулу =FORMAT(Sales,»dddd») для преобразования даты в день недели.

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

• В вычисляемом столбце WeekdaylD используется функция =WEEKDAY(Sales,2). Аргумент 2 имеет то же значение, что и соответствующий аргумент в функции Excel. В частности, он нумерует дни недели таким образом, что понедельнику присваивается значение 1, вторнику - 2 и т.д. Воскресенье получает значение 7. Состоящая из 7 строк таблица Weekday устанавливает соответствие между днями недели (с понедельника по воскресенье включительно) и числами (с 1 до 7 включительно). В результате имена дней недели отображаются корректно.
• В вычисляемом столбце LocationDays находится формула CONCATENATE (Sales,Sales). Эта формула используется в дальнейшем при вычислении различных номеров для дней, в течение которых выполняются продажи.

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

В вычисляемом столбце LocationWeek применяется следующая формула: =Concatenate(Sales,Sales). Обратите внимание на то, что все перечисленные выше вычисляемые столбцы могут ссылаться на другие вычисляемые столбцы. Этот вычисляемый столбец с помощью установленной связи ссылается на таблицу, в которой представлены сведения о персонале.

Многие пользователи думают, что поскольку PowerPivot «понимает» связи, установленные между таблицами, которые содержат сведения об объемах продаж и персонале, можно создать такую формулу, как =Sales/Staffing (рис. 10.25). Но, к сожалению, при выполнении этой формулы появляется сообщение об ошибке.

Причина появления этой ошибки заключается в том, что при выполнении вычислений предпринимается попытка деления значения 2202 в столбце Net Sales на значения в столбце Staff Level, находящемся в таблице Staffing. Для устранения этой проблемы используется функция Related)). Перепишите формулу в виде =Sales/Related(Staffing). Функция Related () сообщает DAX о том, что нужно делить 2202 не на все 14 значений в столбце Staff Level из таблицы Staffing, а лишь на одно значение, которое связано со значением AirportSunday.

На рис. 10.26 показан результат применения этой формулы. При просмотре первой строки приходим к выводу о том, что в торговой точке, расположенной в аэропорту, работают два человека. Поэтому объем продаж по отношению к одному человеку составляет половину от значения 2202, т.е. 1101. По вторникам (Tuesday) в магазине работает один человек, поэтому значение в столбце SalesPerPerson совпадает со значением в столбце Net Sales.

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

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

Продолжая выполнение анализа данных, получим результаты, которые близки к ожидаемым. На рис. 10.28 демонстрируется зависимость объема продаж от того, шел ли дождь и насколько сильным он был. Если ваши торговые точки расположены во Флориде, где дождей практически не бывает, вряд ли подобный анализ будет полезным на практике.

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

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

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

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

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

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

Справка

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

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

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

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

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

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

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

Microsoft Excel 2019/2016. Level 6. Business Intelligence with PowerPivot, PowerView and PowerMap

Обучение проходит на последней версии Excel 2019!

Устали анализировать данные и Вам кажется, что это сложно? Вы сможете анализировать данные из внешних источников, таких как базы данных Access , SQL-сервер , OLAP-куб, файлы Excel и т.д., загружая их в модель PowerPivot напрямую или предварительно обработав в PowerQuery. При необходимости создавать связи между всеми этими источниками, выполнять различные расчеты как с использованием простых вычислений, так и с применением функций. Вы больше не будете зависеть от разработчиков, т.к. все отчеты Вы сможете строить сами.

Полученные данные можно будет представить в комбинированных отчетах сводных таблиц и сводных диаграмм, причем как зависимых, так и независимых друг от друга. Вы научитесь строить интуитивно понятные динамические отчеты PowerView для изучения, визуализации и представления данных как в табличном, так и графическом виде. Есть необходимость строить отчеты с привязкой к географической карте мира? Тогда без отчетов, созданных с PowerView и PowerMap Вам не обойтись!

PowerPivot, PowerQuery, PowerView и PowerMap – это мощные современные инструменты анализа данных в Microsoft Excel!

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

Этот курс читают только сертифицированные тренеры Microsoft!

Обратите внимание на необходимое ПО для каждой версии Excel!

Для 2010 :

1. Microsoft Power Pivot для Excel

Для 2013 :

1. Microsoft Power Query для Excel
2. Power Map Preview for Excel 2013
3. Microsoft Silverlight

Для 2016/2019

1. Microsoft Silverlight

ВАЖНО! Данные надстройки не поддерживаются в Excel для Mac.

[в связи со спорным переносом 1 части поста на geektimes (при том что 2-я часть осталась на хабре) возвращаю 1-ю часть на место]

Работая в сфере аналитики и мониторя различные инструменты BI рано или поздно наталкиваешься на обзор или упоминание надстройки Power Pivot Excel. В моем случае знакомство с ним произошло на конференции Microsoft Data Day.

Особых впечатлений после презентации инструмент не оставил: Да, бесплатен (в рамках лицензии Office), да - есть некий ETL функционал в части получения данных с разрозненных источников (БД,csv,xls, и т.д.), Join-ов этих источников и скармливания в оперативку записей на порядки выше 1 млн.строк в Excel. Короче, посмотрел и забыл.

А вспомнить пришлось, когда появилась необходимость идентификации определённых явлений в данных

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

Собственно, постановка задачи (на обезличенном примере) следующая:

В исходных данных csv файла:

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

Поиску и очистке данных штатными средствами office мешают следующие обстоятельства:

Детализация данных до строк накладной
Количество записей в несколько миллионов строк
Отсутствие sql инструментария (К примеру: Access - не в комплекте)

Конечно можно залить любую бесплатную СУБД (хоть десктоп версию, хоть серверную) но для этого во-первых нужны админские права, во-вторых статья была бы уже не про Power Pivot.

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

Думаю, удобнее всего решать и рассказывать итерационно, с допущением что у нас знания по DAX в зачаточном уровне.
Поэтому предлагаю пока оторваться от задачи и рассмотреть некоторые базовые аспекты.

Шаг 1. Чем отличается вычисляемый столбец от вычисляемой меры?
Вот пример вычисляемого столбца для выделения НДС из поля отгрузки с НДС используя встроенные формулы DAX:

ROUND([Отгрузка с НДС]*POWER(1,18;-1)*0,18;2)

Как видно из примера вычисляемый столбец (Назовем его НДС) работает с каждой атомарной записью по горизонтали.
Теперь добавим вычисляемое поле для цены за штуку без НДС:

ROUND([Отгрузка с НДС]*POWER(1,18;-1)/[Отгрузка шт];2)

Теперь для сравнения добавим в меру расчет средней цены за штуку:

Средняя цена за штуку без НДС: =ROUND(AVERAGE([Поле_Цена за штуку без НДС]);2)

Как видно из формулы, мера работает со столбцом исходных данных по вертикали, поэтому она всегда должна содержать в себе какую то работающую с множеством функцию (Сумму, среднюю, дисперсию и т.д.)

При возврате в сводную таблицу Excel это выглядит так:

Обратите внимание, если вычисляемое поле НДС на каждом уровне данных (зеленая обводка на уровне торговой точки, города или итого по таблице) показывает сумму, что в принципе – корректно, то сумма цен вычисляемого поля «Цена за штуку без НДС» (красная обводка) вызывает вопросы.
А вот вычисляемая мера «Средняя цена за штуку без НДС» вполне имеет право на жизнь в рамках данного аналитического куба.

Отсюда делаем вывод, что вычисляемое поле «Цена за штуку без НДС» является вспомогательным инструментом для расчета меры «Средняя цена за штуку без НДС» и дабы не смущать пользователя этим полем мы скроем его из списка клиентских средств, оставив меру средней цены.

Еще одно отличие меры от столбца – она позволяет добавить визуализацию:

К примеру, построим KPI степени разброса цен с целевой границей 35% путем деления корня из дисперсии на среднюю арифметическую.

К_вар:=STDEV.P([Поле_Цена за штуку без НДС])/AVERAGE([Поле_Цена за штуку без НДС])

В итоге видим такую таблицу в Excel (кстати расчетное вспомогательное поле цен уже не в списке доступных полей справа):

Двойной клик на 80%-м коэффициенте показывает, что цены действительно колбасит вокруг средней:

Cильнее чем при коэффициенте 15%:

Итак, на данном шаге мы рассмотрели основные отличия мер от полей в рамках PowerPivot.

Шаг 2. Усложняем: Посчитаем долю каждой записи в общих продажах.
Вот первый пример сравнения подходов оконных функций MS SQL Server и DAX:

Понятно, что в рамках сводных таблиц это делается буквально в 2 клика мышкой не касаясь клавиатуры, но для понимания попробуем это непосредственно в PowerPivot с применением формул.

На sql я бы это написал так (за огрехи не пинать, ибо Word синтаксис SQL Server не проверяет):

Begin Select "t1.Имя ТТ", "t1.Город", "t1.Адрес", "t1.Продукт", "t1.№ ТТН", "t1.Дата ТТН", "t1.Отгрузка, шт", "t1.Отгрузка с НДС", "t1.Отгрузка, шт"/sum("t1.Отгрузка, шт") over () as share from Table as t1 order by "t1.Отгрузка, шт"/sum("t1.Отгрузка, шт") desc

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

=[Отгрузка шт]/CALCULATE(SUM([Отгрузка шт]);ALL("Таблица1"))

Основное внимание обратим к знаменателю: Я уже упоминал выше что основное отличие вычисляемого поля от меры заключается в том что в поле формулы считают по горизонтали (в рамках одной записи) а меры – по вертикали (в рамках одного атрибута). Здесь мы смогли скрестить свойства поля и свойство меры через метод CALCULATE. И если ширину окна в SQL мы отрегулировали через Over() то здесь мы сделали это через All().

Попробуем теперь, обладая данным навыком, сделать с нашими данными что –нибудь полезное, например, вспомнив что показатель разброса цен вокруг средних варьировался в широком диапазоне, попробуем выделить статистические выбросы цен через правило 3-х сигм.

Оконные функции на sql будут смотреться так:

Select "t1.Имя ТТ", "t1.Город", "t1.Адрес", "t1.Продукт", "t1.№ ТТН", "t1.Дата ТТН", "t1.Отгрузка, шт", "t1.Отгрузка с НДС", "t1.Цена за шт без НДС", CASE WHEN ABS("t1.Цена за шт без НДС" - AVG("t1.Цена за шт без НДС") OVER()) > 3 * STDEV("t1.Цена за шт без НДС") OVER() THEN 1 ELSE 0 END as Outlier from Table as t1 Go

А вот то же самое в DAX:

If(ABS([Поле_Цена за штуку без НДС]-CALCULATE(AVERAGE([Поле_Цена за штуку без НДС]);ALL("Таблица1")))>(3*CALCULATE(STDEV.P([Поле_Цена за штуку без НДС]);all("Таблица1")));1;0)

Как видите, цена несколько высоковата при средней арифметической 40,03 руб.

Шаг 3. Сужаем окна.
Попробуем теперь посчитать в вычисляемом поле каждой записи общее количество записей в рамках того города, к которому принадлежит и данная запись.
На MS sql Server оконные функции будут выглядеть так:

Select "t1.Имя ТТ", "t1.Город", "t1.Адрес", "t1.Продукт", "t1.№ ТТН", "t1.Дата ТТН", "t1.Отгрузка, шт", "t1.Отгрузка с НДС", "t1.Цена за шт без НДС", count("t1.*) OVER(partition by "t1.Город") as cnt from Table as t1 Go

В DAX:
=CALCULATE(COUNTROWS("Таблица1");ALLEXCEPT("Таблица1";"Таблица1"[Город]))

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

Отчетлива видна разница: если обычный расчет количества адресов идет для каждой точки в городе и потом только выводит промежуточный итог для агрегата «Город» то использование оконных функций позволяет присвоить каждой атомарной записи значение любого агрегата, либо использовать его в каких-то промежуточных расчетах вычисляемого поля (как было показано выше).

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

Запрос на SQL Server:

With a1 as (Select "t1.Имя ТТ", "t1.Город", "t1.Адрес", "t1.Продукт", "t1.№ ТТН", "t1.Дата ТТН", "t1.Отгрузка, шт", "t1.Отгрузка с НДС", "t1.Цена за шт без НДС", count(Distinct "t1.Адрес") OVER(partition by "t1.Город", "t1.Имя ТТ") as adrcnt from Table as t1) Select * from a1 where adrcnt>1

Теперь нам ничего не мешает это сделать и в DAX:

CALCULATE(DISTINCTCOUNT("Таблица1"[Адрес]);ALLEXCEPT("Таблица1";"Таблица1"[Город];"Таблица1"[Имя ТТ]))

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

Конечно в процессе изучения (пробежавшись взглядом на другие формулы) становится понятно что DAX в PowerPivot гораздо мощнее чем показано в данном топике, но объять необъятное за раз – точно не получится.

Надеюсь было интересно.
Продолжение статьи

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

Наиболее распространенные агрегаты, например, с помощью функции СРЗНАЧ , счёт , DistinctCount , Max , min или Sum , могут автоматически создаваться в измерении с помощью функции автосуммирования. Другие типы агрегатов, например AVERAGEX , COUNTX , CountRows или SUMX возвращают таблицу и требуют формулы, созданной с помощью выражений анализа данных (DAX) .

Основные сведения об агрегатах в Power Pivot

Выбор групп для агрегата

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

Метод группировки данных определяется поставленным бизнес-вопросом. Например, агрегаты могут ответить на следующие вопросы.

Счетчики Сколько транзакций было выполнено за месяц?

Средние значения Какие показатели средних продаж в этом месяце у каждого менеджера по продажам?

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

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

Если поступившие данные не содержат значений, которые можно использовать для группирования (таких как категория товара или географический регион, где расположен магазин), можно создать группы данных путем добавления категорий. При создании групп в Excel необходимо вручную ввести или выделить нужные группы из числа столбцов в рабочем листе. Однако в реляционных системах многие иерархии (например, категории продуктов) хранятся не в той таблице, где хранятся факты или значения. Обычно таблица категорий связана с данными фактов с использованием какого-либо ключа. Например, предположим, что в данных содержатся идентификаторы продуктов, но не их имена или категории. Чтобы добавить категорию в неструктурированный рабочий лист Excel, потребовалось бы скопировать столбец, содержащий названия категорий. Используя Power Pivot, можно импортировать таблицу с категориями продуктов в вашу модель данных, создать связь между таблицей с числовыми данными и списком категорий продуктов, затем использовать категории для группирования данных. Дополнительные сведения можно найти в разделе Создание связи между таблицами .

Выбор функции для агрегата

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

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

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

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

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

Добавление агрегатов к формулам и сводным таблицам

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

Добавление группирований в сводную таблицу

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

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

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

Работа с группированиями в формуле

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

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

Дополнительные сведения о создании формул с подстановками см. в статье Подстановка в формулах PowerPivot .

Использование фильтров в агрегатах

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

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

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

Дополнительные сведения см. в статье Фильтрация данных в формулах .

Сравнение агрегатных функций Excel с агрегатными функциями DAX

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

Стандартные агрегатные функции

Агрегатные функции DAX

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

В следующей таблице перечислены агрегатные функции, доступные в DAX.

Различия между агрегатными функциями DAX и Excel

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

Способ вычисления столбцов в статистическом выражении также отличается от способа обработки статистических выражений в Excel. Проиллюстрировать это поможет пример.

Предположим, требуется получить сумму значений в столбце Amount таблицы Sales, для чего создается следующая формула:

SUM("Sales")

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

Функции логики операций со временем DAX

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

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

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