Excel powerpivot курсы. С помощью PowerPivot в Microsoft Excel

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

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

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

Примечание: перед освоение сведения,

Основные возможности PowerPivot для Excel

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

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

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

Безопасность и управление Power Pivot Панель управления позволяет ИТ-администраторов мониторинга и управления ими общих приложений для обеспечения безопасности, непрерывность работы и производительности.

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

Задачи в Power Pivot или в Excel

Основное различие между Power Pivot и Excel заключается в том, что в окне Power Pivot можно создавать более сложные модели данных. Вот некоторые задачи для сравнения.

Импорт данных из различных источников, таких как большие корпоративные базы данных, публичные ленты новостей, электронные таблицы и текстовые файлы на локальном компьютере.	Импорт всех данных из источника данных.	Фильтрация данных и переименование столбцов и таблиц при импорте.
Создание таблиц	Таблицы могут находиться на любом листе книги. Листы могут содержать несколько таблиц.	Таблицы организованы в виде отдельных страниц с вкладками в окне Power Pivot.
Редактирование данных в таблице	Можно изменять значения в отдельных ячейках таблицы.	Нельзя изменять отдельные ячейки.
Создание связей между таблицами	В диалоговом окне «Связи».	В представлении диаграммы или диалоговом окне «Создание связей».
Создание вычислений	Формулы Excel.	Расширенные формулы на языке выражений анализа данных (DAX).
Создание иерархий		Нажмите кнопку PivotTable в окне Power Pivot.
Расширение модели для Power View	Создается базовая модель данных.	Доступны расширения, например определение полей по умолчанию, изображений и уникальных значений.
Использование Visual Basic для Applications (VBA)		Не используйте VBA в окне Power Pivot.
Группировка данных		Получение справочной информации Дополнительные сведения Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте Excel User Voice .

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

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

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, можно найти в интернете.

Модуль PowerPivot представляет собой надстройку Excel 2010, которая совершенно бесплатно распространяется командой разработчиков SQL Server Analysis Services из компании Microsoft. При создании Excel 2010 ставилась цель создания превосходного инструмента для бизнеса. И надстройка PowerPivot позволила воплотить мечту в реальность - с ее помощью можно выполнять исчерпывающий анализ огромных массивов данных в Excel.

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

• Возможность обработки массивов данных, исчисляемых десятками и сотнями миллионов строк. Если приходится создавать отчеты сводных таблиц для массивов данных, объем которых превышает 1 048 576 строк, либо сортировать, фильтровать такие массивы и выполнять их прокрутку, обратитесь к надстройке PowerPivot.
• Создание сводных таблиц на основе нескольких исходных таблиц без использования функции ВПР. Теперь вам не придется обращаться к ресурсозатратной функции ВПР (VLOOKUP) для создания сводной таблицы на основе двух исходных таблиц. С помощью надстройки PowerPivot можно на основе нескольких таблиц Excel создать отчет сводной таблицы, не обращаясь к функции ВПР.
• Выборка данных из различных источников. Благодаря PowerPivot можно легко и просто импортировать текст и данные в формате Access, RSS, SQLServer либо Excel, создавая на их основе отчет сводной таблицы.
• Доступ к наборам данных. В сводных таблицах Excel 2010 появилось новый замечательный инструмент - наборы, с помощью которых обеспечивается создание асимметричных отчетов. Единственное ограничение наборов - возможность их использования исключительно при работе со сводными таблицами OLAP. Если же открыть обычную таблицу с помощью надстройки PowerPivot, она автоматически преобразуется в сводную таблицу OLAP. Именно благодаря возможности работы с наборами я создаю сводные таблицы с помощью надстройки PowerPivot.
• Никогда еще выполнение вычислений не было столь простым. При разработке надстройки PowerPivot специалисты из компании Microsoft внедрили новый язык вычисления формул, который получил название Data Analysis Expressions (DAX). Этот язык включает 117 функций, с помощью которых можно выполнять два вида вычислений. С помощью 81 обычной функции Excel можно добавлять вычисляемые столбцы в таблицу, которая отображается в окне надстройки PowerPoint. Также можно воспользоваться 54 функциями для создания новых мер в сводной таблице. С помощью этих 54 функций сводные таблицы обретают невиданную доселе мощь. Например, функция COUNTROWS (DISTINCT) позволяет подсчитать количество различающихся между собой строк. Функция CALCULATE (выражение, фильтрХ, фильтр2, …. фильтры) напоминает функцию СУММБСЛИ (SUMIFS), но является более универсальной. А 34 продвинутые функции по работе со временем (например, TOTALYTD и PARALLELPERIOD) позволят выполнять самые разные хронометрические вычисления.

Второстепенные преимущества PowerPivot

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

• Компактное представление. Рабочие книги Excel, включающие данные PowerPivot, имеют меньшие размеры, чем рабочие книги, использующие традиционные сводные таблицы PivotCache. Хотя данные по-прежнему хранятся в файле рабочей книги.xlsx, но в PowerPivot используются лучшие алгоритмы сжатия данных.
• Объединение двух сводных таблиц с помощью единого набора срезов. С помощью единственного набора срезов можно контролировать две отдельные таблицы PowerPivot.
• Авторазметка среза. Срезы, созданные в стандартной программе Excel, изначально имеют один столбец и один и тот же размер. Если же срез создается в PowerPivot, предпринимается попытка автоматической настройки его размеров. Конечно, далеко не всегда достигается идеальный результат, но все же срезы, создаваемые в PowerPivot, выглядят гораздо лучше срезов, создаваемых в традиционной среде Excel.
• Создание сводных диаграмм без сводных таблиц. На самом деле это немного не так. С помощью надстройки PowerPivot диаграмма автоматически создается на рабочем листе, а затем связывается со сводной таблицей, находящейся на другом листе.

Недостатки PowerPivot

Пользователи PowerPivot отмечают наличие следующих недостатков.

• Отсутствие группировки. В PowerPivot невозможна группировка сводных таблиц. Свойство группировки применяется для распределения дат по месяцам, кварталам и годам. Можно воспользоваться средствами языка DAX для определения столбцов года, квартала и месяца, но это намного сложнее, чем использование свойства группировки.
• Невозможно воспользоваться отменой. PowerPivot является надстройкой. Как известно, в результате выполнения макроса либо другого внешнего кода стек отмены очищается. Поэтому после запуска надстройки PowerPivot вы не сможете вернуться к выполняемым ранее действиям.
• Невозможность использования VBA. При работе со сводными таблицами можно обращаться к средствам VBA. В случае использования PowerPivot применение VBA невозможно.
• Отсутствие возможности развертывания. Для просмотра строк, образующих ячейку обычной сводной таблицы, достаточно дважды щелкнуть мышью на этой ячейке, В первой версии PowerPivot эта операция недоступна.
• Только для Excel 2010. Надстройка PowerPivot может работать только совместно с Excel 2010. Ее невозможно использовать совместно с Excel 2007 либо при работе с файлами, сохраненными в режиме совместимости.