Power Pivot — обзор и обучение
Power Pivot — это технология моделирования данных, которая позволяет создавать модели данных, устанавливать отношения и добавлять вычисления. С помощью Power Pivot вы можете работать с большими наборами данных, создавать развернутые отношения и сложные (или простые) вычисления — и все это в знакомой высокопроизводительной среде Excel.
Power Pivot — одно из трех средств анализа данных, доступных в Excel:
Ресурсы по Power Pivot
Приведенные ниже ссылки и сведения помогут вам освоиться с Power Pivot (в том числе узнать, как включить Power Query в Excel и как начать работу с Power Pivot), а также найти учебники и подключиться к сообществу.
Как получить Power Pivot?
Power Pivot можно использовать в качестве надстройки для Excel, которую можно включить, выполнив несколько простых действий. Базовая технология моделирования Power Pivot используется также в конструкторе Power BI Designer, который является частью службы Power BI, предлагаемой корпорацией Майкрософт.
Начало работы с Power Pivot
Когда надстройка Power Pivot включена, на ленте появляется вкладка Power Pivot, которая показана на следующем изображении.
На вкладке ленты Power Pivot в разделе Модель данных выберите управление.
После выбора элемента Управление появляется окно Power Pivot, в котором вы можете просматривать модель данных и управлять ею, добавлять вычисления, устанавливать отношения и видеть элементы своей модели данных Power Pivot. Модель данных — это коллекция таблиц или других данных, между которыми зачастую установлены отношения. На рисунке ниже показано окно Power Pivot с таблицей.
В окне Power Pivot также можно устанавливать и графически выражать отношения между данными, включенными в модель. Щелкнув значок представления схемы в правом нижнем углу окна Power Pivot, вы увидите имеющиеся отношения в модели данных Power Pivot. На приведенном ниже изображении показано окно Power Pivot в представлении схемы.
Краткое руководство по использованию Power Pivot вы найдете в следующей статье:
В дополнение к этому руководству по следующей ссылке вы найдете исчерпывающую подборку ссылок, ресурсов и дополнительных сведений о Power Pivot:
В последующих разделах перечислены дополнительные ресурсы и руководства, в которых подробнее рассказывается о том, как использовать Power Pivot, в том числе в сочетании с Power Query и Power View, для самостоятельного выполнения комплексных, интуитивно понятных задач бизнес-аналитики в Excel.
Учебники по PowerPivot
Посмотрите на Power Pivot в действии — это поможет вам понять принципы работы и ознакомиться с полезными вариантами использования, демонстрирующими возможности Power Pivot. Начать работу вам помогут следующие руководства:
- Создание модели данных в Excel (начинается с базовой модели данных, которая затем настраивается с помощью Power Pivot)
- Импорт данных в Excel и создание модели данных (первый из шести конечных рядов учебников)
- Оптимизация модели данных для отчетов Power View
- Краткое руководство. Обучение основам DAX за 30 минут
Дополнительные сведения о Power Pivot
Power Pivot проста в использовании и быстро работает. Эта надстройка позволяет создавать многофункциональные и сложные вычисления, индикаторы и формулы. Приведенные ниже ссылки помогут вам определиться со многими задачами, которые можно выполнять с помощью Power Pivot.
- Создание модели данных с эффективным использованием памяти с помощью Excel 2013 и надстройки Power Pivot
- Общие сведения о вычислениях в PowerPivot
- Выражения анализа данных (DAX) в PowerPivot
- Иерархии в PowerPivot
- Агрегатные функции в PowerPivot
- Power Pivot: мощные средства анализа и моделирования данных в Excel (сравнение моделей данных)
- Оптимизатор размера книги (скачивание)
По следующей ссылке вы найдете исчерпывающий набор ссылок и полезных сведений (еще раз):
Ссылки на форумы и связанные темы
Множество разных людей используют Power Query, и они с удовольствием делятся тем, что узнали. С помощью указанных ниже ресурсов вы можете пообщаться с другими пользователями в сообществе Power Query.
- Форум Power Pivot.
- Выражения анализа данных (DAX) в PowerPivot
Начало работы с Power Pivot в Microsoft Excel
Надстройка PowerPivot добавляет расширенные функции моделирования данных в Microsoft Excel. Указанные ниже ресурсы помогут вам узнать, как с помощью PowerPivot лучше понять данные.
Начало работы
- Дополнительные сведения о средствах анализа данных в Excel
- Power Pivot: мощные средства анализа и моделирования данных в Excel
- Запуск надстройки Power Pivot
- Сочетания клавиш в Excel
Учебники по моделированию данных и виртуализации
- Учебник. Импорт данных в Excel и создание модели данных
- Учебник. Расширение связей модели данных с использованием Excel, Power Pivot и DAX
Понимание модели данных PowerPivot
- Создание модели данных в Excel
- Создание модели данных с эффективным использованием памяти с Excel и надстройки Power Pivot
- Назначение вычисляемых столбцов и полей
- Совместимость версий моделей данных PowerPivot в Excel 2010 и Excel 2013
- Обновление моделей данных PowerPivot в Excel 2013
- Спецификации и ограничения модели данных
- Типы данных в моделях данных
- Закрепление столбцов
Добавление данных
- Получение данных с помощью надстройки PowerPivot
- Получение данных из служб Analysis Services
- Импорт данных из отчета Reporting Services
- Внесение изменений в существующий источник данных в PowerPivot
Совет: Power Query для Excel — это новая надстройка, позволяющая импортировать информацию из различных источников в книги и модели данных Excel. Подробнее об этом можно узнать в справке по Microsoft Power Query для Excel.
Работа со связями
- Создание связи между таблицами в Excel
- Создание связей в представлении схемы PowerPivot
- Удаление связей между таблицами в модели данных
- Устранение неполадок в связях между таблицами
- Работа со связями в сводных таблицах
Работа с иерархиями
Работа с перспективами
Работа с вычислениями и DAX
- Общие сведения о вычислениях в PowerPivot
- Назначение вычисляемых столбцов и полей
- Вычисляемые столбцы в PowerPivot
- Создание вычисляемого столбца в PowerPivot
- Создание вычисляемого поля в PowerPivot
- Краткое руководство: обучение основам DAX за 30 минут
- Контекст в формулах DAX
- Фильтрация данных в формулах DAX
- Операторы DAX
- Справочник по функции DAX
Совет: Вики-сайт центра ресурсов DAX на сайте TechNet содержит большое количество статей, видео и примеров от экспертов по бизнес-бизнесу.
Работа со значениями даты и времени
- Даты в PowerPivot
- Создание таблиц дат в PowerPivot для Excel
- Логика операций со временем в PowerPivot для Excel
- Фильтрация дат в отчете сводной таблицы или сводной диаграммы
Ошибки и сообщения
- Ошибка PowerPivot: «Превышен максимальный объем памяти или размер файла»
- Ошибка PowerPivot: «Не удалось выполнить инициализацию источника данных»
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Power Pivot. Все уроки
Решаем практические задачи с помощью Power Pivot, закрепляем навыки и знания, полученные в Базовом курсе.
Номер урока Урок Описание 1 Power Pivot Практический №1. Значение показателя на конец месяца (ENDOFMONTH, CALCULATE) В этом уроке вы узнаете как находить последнее значение показателя на конец месяца. С подобным приходится сталкивать часто, особенно когда речь о финансовых показателях, например, состояние кредитного портфеля. 2 Power Pivot Практический №2. Нарастающий итог, Анализ клиентской базы (CALCULATE, ALLEXCEPT, ALL, FILTER) В этом уроке мы научимся считать нарастающий итог на примере анализа роста клиентской базы. Задача прикладная и интересная. 3 Power Pivot Практический №3. Анализ лояльности клиентов В этом уроке мы проанализируем нашу клиентскую базу. 4 Power Pivot Практический №4. Анализ лояльности клиентов 2 Проанализируем структуру продаж. Разобьем клиентов на группы в зависимости от года первой сделки. 5 Power Pivot Практический №5. Анализ лояльности клиентов 3 — сколько прошло до второго заказа Посчитаем количество клиентов, которые сделали второй заказ через 0, 1, 2, 3 и т. д. квартала. 6 Power Pivot Практический №6. Сравнение всех категорий с выбранной Научимся сравнивать продажи выбранной категории с остальными. 7 Power Pivot Практический №7. Динамический фильтр Топ N (HASONEVALUE, RANKX, ALL, IF) В этом уроке вы узнаете как создать динамический фильтр Топ N, чтобы отображать в сводной таблице только несколько лучших значений. 8 Power Pivot Практический №8. Функция EARLIER, ABC анализ Выполним ABC категоризацию в Power Pivot. Разное
Полезные уроки по Power Pivot, которые не вошли ни в один курс.
Номер урока Урок Описание 1 Power Pivot Разное №1. Быстрая документация отчета (PP Utilities) Как быстро получить список всех мер с их формулами и описаниями; Как быстро получить список источников и их взаимосвязи. 2 Power Pivot Разное №2. Невозможно создать диаграмму этого типа В этом уроке вы узнаете как обойти ошибку «Невозможно создать диаграмму этого типа», которая возникает при попытке создать определенные диаграммы на основе данных из сводной таблицы. Надстройка для Excel — Power Pivot, или жизнь после 1 048 576 строк
Как показывает практика, если в файле Excel больше 50 тысяч строк, да еще формулы типа ВПР, он падает и умирает. Потом восстает, как зомби, чтобы выпить нашу кровь и нервы. Ведет он себя тоже как зомби — еле двигается и «ни черта» не соображает.
Что же делать? Ответ простой: начать работать с надстройкой для Excel — Power Pivot. Этот инструмент создан для работы с данными. Он может легко обрабатывать миллионы строк!
Надстройка Power Pivot в Excel
Power Pivot – это надстройка Excel, с помощью которой можно работать с данными в несколько миллионов строк, объединять таблицы в модель данных и создавать аналитические вычисления.
В «обычном» Excel пользователи ограничены количеством строк в таблице – не более размера листа в 1 048 тысяч строк, но в Power Pivot такого ограничения нет. Надстройка может подключаться к данным из внешних источников и работать с большими объемами информации в миллионы строк.
Открыть надстройку Power Pivot можно, нажав на вкладке меню Power Pivot кнопку Управление. Эта вкладка выглядит одинаково во всех версиях Excel.
Если такой вкладки у вас меню нет, проверьте, та ли у вас версия Excel . Так как Power Pivot представляет собой надстройку COM, то перед первым применением вам может потребоваться добавить её в меню (как это сделать, читайте в предыдущей статье ).
Хорошая новость: начиная c версий после 2019 года компания Microsoft анонсировала включение Power Pivot во все версии Excel.
Работа с данными в Power Pivot
Как правило, разработка отчетов в Power Pivot происходит в следующем порядке:
- Подключение к внешним источникам данных. При загрузке в Power Pivot данные сжимаются в несколько раз с помощью специальных механизмов оптимизации.
- Объединение таблиц в модель данных с помощью создания связей между ними.
- Аналитические вычисления с помощью DAX-формул.
- Построение сводных таблиц и диаграмм на основе модели данных.
Подключения к источникам, связи и вычисления настраиваются в отчете один раз. При изменении исходных данных отчеты можно обновить в меню Данные → Обновить все. Давайте разберем подробнее, как это работает.
Добавление данных в Power Pivot
Чтобы начать работать с Power Pivot, перейдите на вкладку меню Power Pivot → нажмите Управление. Добавить данные в открывшейся надстройке можно несколькими способами:
- С помощью встроенных инструментов импорта.
- Добавить данные из Power Query.
- Также таблицу с данными можно просто скопировать и вставить в Power Pivot из буфера обмена в меню Главная → Вставить.
Способ 1. Подключение к данным с помощью встроенных инструментов импорта.
В Power Pivot есть свои инструменты для импорта внешних данных, которые можно найти на вкладке Главная → кнопки Из базы данных, Из службы данных, Из других источников.
С помощью встроенных инструментов настраивается подключение к 15 видам источников данных.
Увидеть весь список можно в окне «Мастер импорта таблиц», которое открывается в меню Главная → Из других источников.
Настроим подключение к данным на примере файла Excel. Укажите путь к файлу, поставьте галочку «Использовать первую строку в качестве заголовков столбцов», выберите таблицы, жмем «Готово». У вас в окне включится счетчик импорта строк — работает довольно быстро. В результате импорта в окне Power Pivot появятся вкладки с таблицами.
Способ 2. Добавить данные из Power Query.
Загрузка данных с помощью инструментов Power Pivot делается легко, но Power Query лучше подходит для импорта и значительно расширяет возможности аналитики. В нем намного больше доступных источников и возможностей для обработки таблиц произвольного вида.
Чтобы настроить подключение с помощью Power Query, вам нужно создать запрос к источнику данных. Список ранее созданных запросов находится на вкладке «Запросы и подключения». Нажмите на запрос правой кнопкой мышки и выберите Загрузить в… В открывшемся окне доступных вариантов импорта поставьте галочку «Добавить эти данные в модель данных». Задать настройки импорта также можно в самом редакторе Power Query.
К сожалению, в Excel 2010 Power Pivot почти невозможно «подружить» с Power Query и этот новый функционал в старом Excel сильно ограничен.
Интерфейс Power Pivot
Разберем подробнее интерфейс Power Pivot.
В окне Power Pivot есть:
- Лента редактора для вкладок меню Главная, Конструктор, Дополнительно.
- Строка формул на языке DAX.
- Область данных и вычисляемых столбцов.
- Добавление нового вычисляемого столбца.
- Область вычислений, в которой можно писать меры.
- Меню, которое появляется при нажатии правой кнопкой мышки.
- Ярлычки с названиями таблиц данных для переключения между ними (как между листами в «обычном» Excel).
Модель данных и связи
Чтобы перейти к настройке связей между таблицами, выберите в меню Главная → Представление диаграммы (вернутся обратно к просмотру таблиц можно, нажав Представление данных).
Модель данных в Power Pivot – это набор таблиц, объединенных связями.
Графически связь таблиц обозначается линией между ними, как в примере на рисунке. Чтобы создать связь, выделите мышкой поле в одной таблице и «перетащите» его на соответствующее ему поле в области другой таблицы.
Power Pivot поддерживает типы связей «один к одному», «один ко многим».
- Понять, какой именно вид связи задан между таблицами, можно с помощью значков на концах линий: на стороне «один» стоит символ единица — «1», а на стороне «многие» — звездочка «*». Если между таблицами задана связь «один к одному», то на концах линии будут единички «1».
- Поля, которые используются для создания связей, называются ключами связи. В таблицах, которые находятся на стороне «один» (конец линии с единичкой «1») в ключевых столбцах должны содержаться только уникальные значения. В таблицах на стороне «многие» со звездочкой «*» в ключевых столбцах те же значения, но они могут повторяться много раз.
- Стрелка на линии связи обозначает направление фильтрации. Так, на рисунке выше справочники Товары и Города фильтруют таблицы ДанныеФакт и ДанныеПлан.
Если выделить мышкой линию связи в модели данных, то можно увидеть, с помощью каких полей задана связь. Выделенные линии можно удалять. Или, щелкнув по ним дважды, менять связи в открывшемся окне. Также управление связями доступно в окне, которое открывается в меню Конструктор → Управление связями.
Вычисления в Power Pivot
Формулы Power Pivot пишут на языке DAX (Data Analysis Expressions, выражения для анализа данных). DAX-формулы позволяют, по аналогии с формулами Excel, выполнять вычисления и/или настраивать произвольную фильтрацию и представление данных в таблицах.
Язык DAX впервые появился в 2010 году вместе с надстройкой Power Pivot. В этом языке сотни функций, с помощью которых можно создавать аналитические расчеты. Кроме Power Pivot в Excel, DAX-формулы также доступны в Power BI и Analysis Services. То есть эти формулы вам точно пригодятся.
Вычисления с помощью DAX-формул создаются в виде:
- вычисляемых столбцов, как в обычных таблицах Excel.
- мер, которые пишут в области вычислений под таблицей.
Вычисляемые столбцы представляют собой столбцы в таблицах данных, созданные с помощью формул. Чтобы добавить такой столбец, щелкните мышкой дважды по столбцу слева «Добавление столбца», введите название вычисления, а затем знак «=» и формулу в строке формул.
Вычисляемый столбец похож на любой другой столбец в таблице. Также с помощью таких столбцов можно создавать связи в модели. Вычисляемые столбцы в Excel рассчитываются в момент загрузки (обновления) данных, а значения в самом столбце сохраняются, увеличивая размер файла. То есть столбцы – это статические вычисления.
Как и вычисляемые столбцы, меры тоже создаются с помощью DAX-формул. В отличие от столбцов, меры – это динамические вычисления, результат которых зависит от контекста – его можно увидеть в отчете, где мы задаем в разрезе каких полей, фильтров и др. вычислить меру. Меры записываются под таблицей в области вычислений.
Меры в Power Pivot можно превратить в KPI – ключевые показатели эффективности. Для этого выделите меру и нажмите на кнопку Создать KPI в меню Главная. Кроме мер, созданных пользователями, в Excel также есть неявные меры. Они создаются автоматически при формировании сводной таблицы, когда пользователь помещает данные в область значений. Чтобы посмотреть, есть ли у вас в Power Pivot неявные меры, выберите на вкладке Главная → Показать скрытые.
Подробнее о DAX-формулах:
- Основные формулы Power Pivot
- ТОП-20 DAX формул для Power Pivot и Power BI