Марк Мур. Power Pivot

В последнее время Microsoft развивает Excel в новом направлении. MS включила в Excel инструменты для анализа больших объемов данных, так называемую бизнес-аналитику (BI). Ранее обработка больших данных выполнялась опытными специалистами с использованием дорогостоящих пакетов ПО. MS хочет предоставить возможности BI среднему пользователю. Набор инструментов MS Excel BI включает:

• Power Query – для импорта данных;
• Power Pivot ­– для анализа данных;
• Power View – для создания презентаций;
• Power Map – для создания географических презентаций

Скачать заметку в формате Word или pdf, примеры в архиве

Excel быстро становится очень мощным инструментом управления данными. Лист Excel 2003 вмещал 65 536 строк и 256 столбцов. Начиная с Excel 2007 на листе доступно 1 048 576 строк и 16 000 столбцов. Это много, но в век больших данных и этого недостаточно. Power Pivot решает эту проблему. Power Pivot – это мини приложение, которое живет внутри Excel и может управлять десятками миллионов записей. Можно создавать отношения между различными таблицами и создавать пользовательские вычисления, прежде чем помещать что-либо на лист. Power Pivot позволяет обработать сырые данные, чтобы затем их визуализировать в Power Map или Power View.

Power Pivot включает модель данных – совокупность нескольких таблиц и их связи. То есть, один столбец одной таблицы соответствует другому столбцу другой таблицы. Можно говорить о реляционной модели данных (от англ. relation – отношение, зависимость, связь).

Активация надстройки Power Pivot

К сожалению, для ряда версий надстройка Power Pivot недоступна (подробнее см. справку MS). В современных версиях Excel надстройка Power Pivot установлена, но не активирована. Для того чтобы активировать Power Pivot откройте Excel, пройдите по меню Файл –> Параметры –> Надстройки. Внизу открывшегося окна в поле Управление выберите Надстройки СОМ и нажмите Перейти (рис. 1). При использовании Power Pivot для Excel 2010 изображения у вас на экране будут отличаться от изображений в этой заметке.

Рис. 1. Надстройки Excel

В открывшемся окне поставьте галку напротив Microsoft Power Pivot for Excel (рис. 2). Нажимать Ok. В Excel появится новая вкладка Power Pivot (рис. 3).

Рис. 2. Надстройки СОМ

Рис. 3. Вкладка Power Pivot

Подключение к источнику данных

Power Pivot может получить данные из нескольких источников: из файлов Excel, MS Access, иных корпоративных реляционных баз данных, текстовых файлов и Power Query. Power Query – самый простой источник получения данных для Power Pivot. Вы использовали Power Query для подключения к источнику данных и предварительной их обработки (подробнее см. Марк Мур. Power Query). Вместо загрузки данных на лист Excel, они загружаются в модель данных.

Поток данных будет примерно таким: Источник данных –> Power Query –> Power Pivot –> сводная таблица Excel, или Power View, или Power Map

Если вы получаете данные в Power Query, то находясь в окне Мастера импорта, выберите Загрузить в… (рис. 4).

Рис. 4. Опция Загрузить в… Мастера импорта Power Query

В открывшемся окне Импорт данных установите флажок Добавить эти данные в модель данных (рис. 5).

Рис. 5. Импорт данных

Реляционная база данных

Установка и настройка движка реляционной базы данных выходит за рамки темы (если это вас интересует, рекомендую Установка SQL Server; это бесплатная база данных Microsoft, которую можно скачать и установить на ПК).

Подключение к реляционной базе данных я покажу на примере MS Access (все рабочие файлы к этой заметке я разместил в папке С:\Common\Power Pivot). Откройте новую книгу Excel, перейдите на вкладку Power Pivot, кликните кнопку Управление. В окне Power Pivot для Excel пройдите по меню Получение внешних данных –> Из базы данных –> Из Access (рис. 6).

Рис. 6. Извлечение данных из реляционной базы MS Access

В окне Мастера импорта таблиц кликните на кнопке Обзор…, и найдите тестовую базу Access (рис. 7).

Рис. 7. Мастер импорта таблиц; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Нажимать Далее. На следующем шаге можно выбрать таблицу для импорта или написать собственный SQL-запрос для извлечения данных. Укажите Выбрать из списка таблиц и представлений, чтобы определить данные для импорта (рис. 8).

Рис. 8. Шаг 2; выбор способа импорта данных

Тестовая база данных содержит одну таблицу – Location. Поставьте галочку (рис. 9). Если вы не собираетесь импортировать в модель данных таблицу целиком, то можете отфильтровывать строки из исходной базы перед импортом. Для этого нажмите кнопку Просмотр и фильтрация (рис. 10).

Рис. 9. Шаг 3; выбор таблиц для импорта

Рис. 10. Просмотр и фильтрация базы перед импортом

Это предварительный просмотр импортируемой таблицы. Здесь есть фильтры аналогичные тем, что и в сводных таблицах, и можно задать критерии для пропуска строк. Несмотря на то, что Power Pivot намного мощнее «голого» Excel, он по-прежнему будет замедлять работу, если в него загрузить миллионы строк. Импортируйте только то, что вам нужно. Если позднее вы обнаружите, что вам нужно больше данных, вы просто измените настройки импорта. При работе с огромными объемами данных необходимо обращать внимание на эффективность. Нажмите Отмена, чтобы выйти из этого окна. Нажмите Готово (см. рис. 9), чтобы импортировать таблицу в модель данных.

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

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

Рис. 11. Данные, импортированные в Power Pivot

Рис. 12. Ярлык вкладки Power Pivot

Импорт из Excel

Существует два способа импорта данных из Excel в Power Pivot: в виде статической таблицы и в виде связанной таблицы. Если данные не изменяются (например, список регионов продаж), то подойдет статическая таблица (обратите внимание, что вы всегда можете вернуться в Excel, оставив окно Power Pivot открытым; для этого нажмите кнопку Назад в Excel; рис. 13).

Рис. 13. Кнопка возврата в Excel из окна Power Pivot

Итак, откройте книгу Regions.xlsx (рис. 14). Выделите ячейки A1:A5 (пока не выделяйте A6). Щелкните правой кнопкой мыши и выберите Копировать. Вернитесь в окно Power Pivot. На вкладке Главная нажмите на кнопку Вставить (рис. 15).

Рис. 14. Книга Regions.xlsx

Рис. 15. Кнопка Вставить

Введите новое имя таблицы – Regions. Обратите внимание, что Power Pivot позволяет использовать первую строку в качестве заголовка столбца (рис. 16).

Рис. 16. Просмотр вставки

Нажимать Ok. Данные регионов отобразятся в новой таблице Power Pivot (рис. 17).

Рис. 17. Таблица Regions в Power Pivot

Добавление новых записей

Скопируйте ячейку A6 из книги Excel (рис. 14) с записью International в буфер обмена. Вернитесь в окно Power Pivot в таблицу Regions. Кликните на копке Вставить из буфера (рис. 18).

Рис. 18. Вставить из буфера

Появится окно предварительного просмотра вставки. Нажмите Оk, чтобы добавить новую запись.

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

Вставить связанную таблицу

Это очень похоже на то, как работают сводные таблицы. Вы импортируете данные из Excel в Power Pivot, но сохраняете ссылку на исходную таблицу. После обновления данных в Excel можно обновить данные и в Power Pivot. Кликните правой кнопкой мыши вкладку Regions в Power Pivot. Выберите Удалить (рис. 19). Нажмите Да при появлении запроса на удаление.

Рис. 19. Удаление таблицы из Power Pivot

Вернитесь в книгу Regions.xlsx. Перейдите на вкладку Power Pivot, нажмите кнопку Добавить в модель данных (рис. 20). В появившемся окне Создание таблицы проверьте правильность диапазона данных и установите флажок Таблица имеет верхние колонтитулы. Нажимать Ok.

Рис. 20. Добавить в модель данных

В Power Pivot появится новая таблица, связанная с таблицей Excel. Чтобы проверить это, вернитесь в файл Excel, измените одно из значений в ячейках А1:А6. Перейдите в Power Pivot, на вкладке Главная кликните кнопку Обновить. Значение изменится также и в таблице Power Pivot.

Типы данных

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

Power Pivot понимает следующие шесть типов данных: целые числа, десятичные числа, логические значения ИСТИНА и ЛОЖЬ, текст, даты, валюта. Кроме того, Н/Д используется для пустых значений. Подробнее см. справку Microsoft.

Управление данными

Теперь, когда вы знаете, как импортировать данные в Power Pivot, можно приступать к созданию модели данных. Конечно, можно использовать только одну таблицу, создав на ее основе сводную… Но зачем тогда связываться с Power Pivot!? Одно из достоинств Power Pivot – возможность создавать связи между таблицами.

Начнем с импорта нескольких наборов данных в Power Pivot. Откройте SalesData.xlsx. Перейдите на лист Clients, кликните на любой ячейке таблицы. Перейдите на вкладку Power Pivot, нажмите кнопку Добавить в модель данных. В модель данных добавится связанная таблица. Аналогичным образом добавьте в модель данных таблицы с листов States, Products и SalesData. В модели данных появятся 4 таблицы (рис. 21).

Рис. 21. Четыре таблицы в модели Power Pivot

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

Рис. 22. Режим Представление диаграммы Power Pivot

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

Отступление о ключевых столбцах

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

Рис. 23. Системы хранения данных: (а) эффективные; (б) неэффективные

При эффективном хранении каждая запись в синей таблице должна быть идентифицирована уникальным номером. Этот номер является ключевым. Так как он однозначно идентифицирует запись, он называется первичным ключом. ClientID – это первичный ключ в таблице Clients. Зеленая таблица также содержит ClientID, но значения могут встречаться несколько раз. Это означает, что ключ в этой таблице не уникален; он не может быть первичным ключом. Поле ClientID в зеленой таблице называется внешним ключом. Внешний ключ всегда является первичным ключом другой таблицы.

Эти понятия очень важны для управления данными. Ключи обеспечивают ссылочную целостность. Т.е., каждая строка в зеленой таблице должна иметь связанную запись в таблице Clients, идентифицированную ClientID. Ключи поддерживают целостность ссылок между таблицами. Если бы мы не применяли это правило, было бы возможно жульничество. Некто мог ввести любое количество продаж в зеленую таблицу, не указывая клиентов! Данные о продажах были бы «раздуты», при том, что клиенты не получили бы счета.

Вернемся к Power Pivot…

Для установления связей кликните на поле ClientID в таблице Clients и перетащите его в таблицу SalesData (рис. 24). Вы увидите стрелку связи между таблицами.

Рис. 24. Установление связей между таблицами через ключевое поле

Установленная связь будет показана условными обозначениями (рис. 25). В данном случае графический образ говорит, что установлена связь «один ко многим» от таблицы Clients к таблице SalesData. Другими словами, один клиент может совершить множество сделок.

Рис. 25. Обозначение установленной связи

Модель данных

Если вы случайно установили неверную связь, кликните правой кнопкой мыши на линии связи (но не на значках), и выберите Удалить. Кроме того, можно дважды щелкнуть по линии связи, чтобы изменить ее. Откроется окно Изменение связи (рис. 26). С помощью раскрывающихся списков (1) можно выбрать иные таблицы, а затем щелкнуть заголовок столбца (2), чтобы изменить связанные поля.

Рис. 26. Окно Изменение связи

Усовершенствуйте свою модель. Свяжите поля StatelD в таблицах States и Clients, а также поля ProductID в таблицах Products и SalesData (рис. 27).

Рис. 27. Модель данных

Эту модель данных уже можно использоваться в сводной таблице. Перейдите в Excel. Power Pivot можно оставить открытым или закрыть на ваш выбор. Вставьте новый лист в книгу SalesData.xlsx. Перейдите на вкладку Вставка, кликните кнопку Сводная таблица. В открывшемся окне задайте источник данных – Использовать модель данных этой книги. Выберите На существующий лист. Укажите ячейку, в которой расположится левый верхний угол сводной таблицы. Нажимать Ok (рис. 28).

Рис. 28. Вставка сводной таблицы на лист Excel

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

Рис. 29. Поля сводной таблицы, основанной на модели данных

Выберите поля ClientName, Product, Cost, SalePrice, чтобы добавить их в сводную таблицу (рис. 30).

Рис. 30. Сводная таблица

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

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

Иерархии

В Power Pivot можно создавать иерархии, которые позволяют осуществлять навигацию по полям сводной таблицы. Допустим, вы собираетесь добавить новую таблицу в книгу SalesData.xlsx с регионами, а затем создать иерархию, в которой каждый штат будет относиться к соответствующему региону. Вставьте новый лист в SalesData.xlsx. Назовите его Regions. Введите данные, как на рис. 31.

Рис. 31. Регионы

Теперь необходимо сопоставить каждый штат региону. Откройте файл StatebyRegion.xlsx (рис. 32). Порядок расположения штатов такой же, как и на листе States файла SalesData.xlsx. Скопируйте и вставьте столбец RegionID в качестве четвертого столбца на лист States. Перейдите в Power Pivot на лист States и обновите данные. Добавьте таблицу Regions в модель данных. Установите новую связь таблиц Regions и States по полю RegionID. Вернитесь в Excel. Обновите сводную таблицу, представленную на рис. 30.

Рис. 32. Соответствие штатов идентификаторам регионов

Иерархия позволяет переходить от родительского элемента к дочернему. В нашем случае пользователь сможет раскрыть регионы, чтобы увидеть штаты. Существует несколько условий, которым необходимо следовать при создании иерархий. Первое условие (и это немного раздражает) – поля иерархии не могут находиться в разных таблицах (рис. 33). Хорошая база данных требует раздельных таблиц для регионов и штатов (как на рис. 33), но иерархиям это не нравится.

Рис. 33. Элементы иерархии расположены в разных таблицах

Исправим эту ситуацию. Нажмите на кнопку Таблица (рис. 34), чтобы перейти от режима Представление диаграммы к режиму Представление данных.

Рис. 34. Переход к режиму Представление данных

Перейдите на вкладку States. Если бы вы работали в Excel, вы, вероятно, воспользовались бы функцией ВПР, и добавили еще один столбец Имя региона, на основе идентификатора региона. Нечто подобное можно сделать и в Power Pivot. Добавьте вычисляемый столбец, который будет извлекать имя региона из таблицы регионов. Концептуально это похоже на написание формулы ВПР. В Power Pivot это даже проще, так как связи таблиц уже настроены. Заметьте, вы не изменяете исходную таблицу в Excel, чтобы поместить имена регионов непосредственно в таблицу штатов. Если у вас миллионы строк, это существенно замедлит работу.

Итак, щелкните первую строку в пустом столбце (номер 1 на рис. 35), затем щелкните в строке формул (2) и начните вводить формулу =Related(… Поскольку в таблице States существует только одна связь – с таблицей Regions – Power Pivot выведет подсказку с возможными вариантами связи с одним из двух полей таблицы Regions. Выберите Regions[RegionName]. Введите закрывающую скобку ), чтобы закончить ввод формулы. Нажать Ввод. Имена регионов отобразятся в таблице States (рис. 36). Переименуйте столбец.

Рис. 35. Создаем вычисляемое поле с именем региона в таблице Штаты

Рис. 36. Вычисляемый столбец с именами регионов в таблице States

Теперь, когда у вас и родитель (RegionName) и ребенок (Name) в одной таблице, можно построить иерархию. Перейдите в режим Представление диаграммы. Щелкните правой кнопкой мыши на поле RegionName в таблице States. Выберите опцию Создать иерархию (рис. 37).

Рис. 37. Опция Создать иерархию

Power Pivot предложить создать имя иерархии. Введите Geography (рис. 38). Щелкните и перетащите поле Name таблицы States ниже строки RegionName (RegionName).

Рис. 38. Назовите иерархию Geography

Вернитесь в Excel. Обновите сводную таблицу. На панели Поля сводной таблицы раскройте таблицу Штаты (States). Вы увидите географическую иерархию. Преобразуйте сводную таблицу, чтобы увидеть, как работает иерархия (рис. 39).

Рис. 39. Географическая иерархия в сводной таблице

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

Иерархию Geography можно использовать в качестве среза. Щелкните любую ячейку сводной таблицы. Перейдите на вкладку Анализ. Нажмите кнопку Вставить срез. Выберите Geography. Нажимать Ok (рис. 40). Создаются два среза. Самое замечательное, что, если вы выберете регион, все связанные штаты также будут выбраны. Пользователи могут выбрать регион целиком (и все штаты в регионе) или отдельный штат.

Рис. 40. Срез Geography

Вычисляемый столбец

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

Power Pivot использует новый язык формул, называемый Data Analysis Expressions (DAX). Основные функции DAX аналогичны обычным формулам Excel. Давайте создадим вычисляемый столбец, подсчитывающий прибыль в таблице SalesData. В Power Pivot перейдите к таблице SalesData. Щелкните в любой ячейке столбца Добавить столбец. В строке формул начните набирать =[… Power Pivot отображает список доступных полей. Введите формулу =[SalePrice]-[Стоимость]-[DeliveryCharge]. Нажать Ввод. Переименуйте столбец (рис. 41). Теперь это поле доступно для использования в сводной таблице.

Рис. 41. Вычисляемое поле Чистая прибыль

Вычисляемое поле

Вычисляемые поля (также известные как меры) – это вычисления, которые не вычисляются по строкам; вместо этого они вычисляются по всей таблице. Например, вычисление количества проданных единиц в регионе или по кварталам не зависит от строки. Вычисляемые поля вводятся в область вычислений под таблицей данных. В Power Pivot перейдите на вкладке SalesData. Щелкните пустую ячейку в области вычислений (рис. 42).

Рис. 42. Область вычислений

Вычисляемые поля также используют формулы DAX. Простой способ узнать, какие формулы доступны, – использовать мастер формул Power Pivot DAX. Нажмите кнопку Мастер формул (рис. 43). Найдите функцию Countrows. Выберите её. Кликните Ok. Завершите ввод формулы =COUNTROWS(SalesData).

Рис. 43. Мастер формул

Измените текст перед двоеточием на SalesRecords. Окончательная формула должна выглядеть следующим образом: SalesRecords: =COUNTROWS(SalesData). Обратите внимание, что синтаксис формулы немного отличаются. Между именем и формулой находится :=  Нажать Ввод. Новое вычисляемое поле появится в области вычислений (рис. 44).

Рис. 44. Вычисляемое поле в Power Pivot

Перейдите в Excel и выберите ячейку в сводной таблице. В области Поля сводной таблицы раскройте таблицу SalesData, и вы увидите вычисляемое поле (рис. 45).

Рис. 45. Вычисляемое поле в сводной таблице

Перетащите поле SalesRecords в область значений сводной таблицы (рис. 46).

Рис. 46. Число записей по регионам и штатам

Вычисление SalesRecord в Power Pivot дает только одно число – общее число строк. Однако в сводной таблице расчет SalesRecord «прослушивает» сводную таблицу и пересчитывает вычисляемое поле на основе установленных фильтров.

По любому значению в сводной таблице можно получить детализацию. Например, сводная таблица говорит, что для Illinois есть пять записей. Дважды щелкните на 5 или на 211 000. Детализация появится на отдельном листе (рис. 47).

Рис. 47. Детализация записей по штату Illinois

KPI

KPI (или ключевые показатели эффективности, КПЭ) – это разрабатываемые метрики, которые создают визуальные предупреждения в Power Pivot для привлечения внимания к значениям, выходящим за некие пределы. Для KPI требуется вычисляемое поле. На вкладке SalesData щелкните в области вычислений. В строке формул введите новое вычисляемое поле: AvgNetProfit:=AVERAGE([Net Profit]). Нажмите правой кнопкой мыши на ячейке AvgNetProfit и выберите Создать KPI (рис. 48).

Рис. 48. Создать KPI

Обратите внимание, что KPI доступны только на меры, созданные в Power Pivot. Если создать вычисление в Excel, а затем включить его в Power Pivot, вычисление будет недоступно для использования KPI.

В окне Ключевой показатель эффективности установите параметры как на рис. 49. Нажмите Ok.

Рис. 49. Настройка KPI

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

Рис. 50. Пиктограмма KPI

Перейдите в Excel, в области Поля сводной таблицы разверните таблицу SalesData (рис. 51). Настройте сводную таблицу, как показано на рисунке.

Рис. 51. Поля KPI в сводной таблице

В этой сводной таблице отображена иерархия регионов и штатов; показана чистая прибыль, которая никогда не вычислялась в Excel (это вычисляемый столбец в Power Pivot); вычисляемое поле AvgNetProfit рассчитывается для каждого клиента; значками показаны состояния KPI средней чистой прибыли.

Календарь

Взгляните на данные в таблице SalesData (рис. 52). Допустим вы хотите проанализировать TotalDiscount по месяцам или NetProfit по кварталам? В Power Pivot это невозможно сделать. Power Pivot не знает месяцев и кварталов. Помните, что нельзя изменить данные в Power Pivot, если они не загружены или не рассчитаны в Power Pivot.

Рис. 52. Таблица SalesData

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

Рис. 53. Автоматическая группировка дат в сводной таблице Excel

Если вы лишь строите сводные таблицы, то можете не заморачиваться с календарем. Однако, если вы хотите использовать Power Map, Power View или другие меры даты, вам потребуется создать таблицу Календарь. Самый простой способ построить таблицу календаря – использовать Excel. Перечислите дни в одном столбце и используйте встроенные функции даты Excel для остальных столбцов. Можно воспользоваться готовым календарем из файла CalendarTable.xlsx.

Перейдите к Power Pivot yа вкладку Главная. Нажмите кнопку Из других источников. Найдите строку Файл Excel. Кликните на ней.

Рис. 54. Импорт таблицы из файла Excel

Нажимать Далее. Перейдите в папку с рабочими книгами к этой заметке, выберите CalendarTable.xlsx. Установите флажок Использовать первую строку в качестве заголовков столбцов. Нажимать Далее (рис. 54).

Рис. 55. Мастер импорта таблиц

Переименуйте Понятное имя в CalendarTable (рис. 56). Нажмите Готово. Некоторое время займет импорт таблицы в Power Pivot. Нажмите Закрыть.

Рис. 56. Мастер импорта таблиц, шаг 2

Перейдите на вкладку Конструктор (рис. 57), нажмите кнопку Пометить как таблицу дат. И в выпавшем меню выберите еще раз Пометить как таблицу дат.

Рис. 57. Помечаем, как таблицу дат

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

Рис. 58. Выбор ключа Календаря

Создайте связь между таблицей Календарь и таблицей, содержащей данные, которые вы хотите анализировать по периодам (SalesData). На вкладке Конструктор нажмите кнопку Создание связи (рис. 59). В нижней части окна Создание связи выберите таблицу SalesData, а в ней поле InvoiceData. Нажмите Ok. Иногда может появляться сообщение об ошибке. Повторите попытку, как правило, связь всё же создается.

Рис. 59. Настройка связи Календаря с таблицей SalesData

Временная шкала сводной таблицы

При наличии полей, отформатированных как даты, можно использовать временную шкалу для управления сводными таблицами (подробнее см. Excel 2013. Срезы сводных таблиц; создание временной шкалы). Вернитесь в Excel, кликните на любой ячейке сводной таблицы. Перейдите на вкладку Анализ. Нажмите кнопку Вставить временную шкалу. Выберите InvoiceData. Нажмите Ok.

Рис. 60. Вставка временной шкалы в сводную таблицу

У пользователей сводной таблицы появится более интуитивный способ выбора диапазона дат для просмотра связанных с ними данных в сводной таблице (рис. 61). Можно задать масштаб шкалы, а также, двигая ползунок, настроить фильтр по датам.

Рис. 61. Фильтр сводной таблицы по датам с использованием временной шкалы

Инструментальная панель (дашборд)

Power Pivot можно использовать для построения дашбордов. Для извлечения данных из модели данных используем функцию КУБЗНАЧЕНИЕ (подробнее см. Аналитические функции в Excel, функции кубов). Она позволяет Excel подключаться к кубу данных. Пользователи могут «вращать» данные в кубе для детализации и анализа. Эта технология называется OLAP (Online Analytical Processing), а кубы называются OLAP-кубами. Модель данных Power Pivot является одним из таких кубов. Синтаксис функции:

=КУБЗНАЧЕНИЕ(подключение;[выражение_элемента1];[выражение_элемента2];…)

КУБЗНАЧЕНИЕ() извлекает данные из области вычислений Power Pivot. Пока вы создали только две меры: SalesRecords и AvgNetProfit. Однако Power Pivot создал некоторые неявные меры. Другими словами, Power Pivot произвел вычисления, но не сообщил о них.

Итак, вставьте новый лист в книгу SalesData.xlsx. Назовите его Дашборд. В Power Pivot перейдите на вкладку SalesData и в меню Дополнительно нажмите кнопку Отображение явных мер (пожалуй, русский перевод кнопки неудачен; лучше было бы назвать Отображение неявных мер). Под таблицей вы увидите все меры на вкладке SalesData (рис. 62).

Рис. 62. Меры, созданные Power Pivot автоматически (неявные меры)

Вы планируете извлечь NetProfit из модели данных. Вернитесь в Excel на лист Дашборд, введите формулу в ячейку D12:

=КУБЗНАЧЕНИЕ("ThisWorkbookDataModel";"[Measures].[Sum of SalePrice]")

В процессе набора Excel помогает вам, отображая доступные поля. Вам лишь нужно следить за синтаксисом, аккуратно вводя кавычки, квадратные скобки, точки и т.п. (рис. 63).

Рис. 63. Подсказки Excel

Представленная выше формула подключается к ThisWorkbookDataModel и извлекает сумму цен продаж из мер (вычисляемые поля также называются мерами). Изменим эту формулу, чтобы можно было получать сумму SalePrice отдельно для каждого региона. Вам нужно задать дополнительные критерии в функции КУБЗНАЧЕНИЕ(). Критерии задают в форме [Таблица].[Поле].[Значение]. Чтобы получить продажи для региона Northern, измените формулу:

=КУБЗНАЧЕНИЕ("ThisWorkbookDataModel";"[Measures].[Sum of SalePrice]";"[States].[RegionName].[Northern]")

Вы увидите значение в ячейке D12: $527 690 (рис. 64).

Рис. 64. Дашборд, шаг 1

Можно еще более детализировать эту формулу, а также «отвязаться» от значений, «зашитых» внутри формулы, а получать значения с помощью ссылок на ячейки. Вместо Northern используйте ссылку на основе операторов &.

=КУБЗНАЧЕНИЕ("ThisWorkbookDataModel";"[Measures].[Sum of SalePrice]";"[States].[RegionName].["&$C12&"]")

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

Добавьте также критерии для получения данных по годам и продуктам. Поскольку это формулы, вы можете вставлять строки и применять любое форматирование. Это добавляет гибкости по сравнению с более ограниченными возможностями сводной таблицы. Можно иметь миллионы строк данных в Power Pivot и построить панель мониторинга на основе всех этих данных без использования сводной таблицы. Но и это еще не всё. Добавьте срез на основе Power Pivot на лист Дашборд (рис. 65).

Рис. 65. Вставка среза модели данных на лист Excel

Находясь на листе Дашборд, перейдите на вкладку Вставка. Нажмите кнопку Срез. В открывшемся окне Существующие подключения перейдите на вкладку Модель данных. Кликните на кнопу Открыть. В окне Вставка среза, найдите таблицу Products, выберите в ней поле Product (рис. 66). Нажмите Ok.

Рис. 66. Выбор среза

На листе Excel появится срез. Подключите его к ячейке. Щелкните правой кнопкой мыши на срезе и выберите Параметры среза. Не изменяйте здесь ничего, но запишите имя среза – Slicer_Product (рис. 67). Нажмите Ok.

Рис. 67. Имя среза

На листе Дашборд в ячейке C9 введите формулу =КУБПОРЭЛЕМЕНТ("ThisWorkbookDataModel";Slicer_Product;1)

Функция КУБПОРЭЛЕМЕНТ() извлекает элемент из набора данных. В нашем случае набор данных – это срез Slicer_Product. Третий аргумент в формуле выше – 1 – говорит о том, что нужно извлечь первое значение. Выберите любой продукт на срезе, и увидите новое значение, отображаемое в ячейке C9 (этот трюк не будет работать, если на срезе выбрано несколько продуктов).

Итоговая формула в ячейке D12 =КУБЗНАЧЕНИЕ("ThisWorkbookDataModel";"[Measures].[Sum of SalePrice]";"[States].[RegionName].["&$C12&"]";"[CalendarTable].[Year].["&D$11&"]";"[Products].["&$C$9&"]")

Протащите формулу вниз и вправо по диапазону D12:T16. Теперь при выборе продукта с помощью среза формула КУБЗНАЧЕНИЕ() считывает связанную ячейку С( и возвращает данные из модели данных для выбранного продукта, года и региона (рис. 68).

Рис. 68. Дашборд, финальный вид

Формулы DAX

В этой заметке о формулах DAX было сказано лишь пару слов. Это было сделано намеренно. DAX – это огромная тема, которая заслуживает отдельного изложения. Я перевел книгу Роб Колли. Формулы DAX для Power Pivot.

Добавление от 01.10.2019. См. также Кен Пульс и Мигель Эскобар. Язык М для Power Query.