Как показывает практика, если в файле 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
Power Pivot отлично работает со сводными таблицами. Используя модель данных, пользователи могут создавать отчеты с помощью сводных таблиц и диаграмм. Чтобы создать сводную таблицу, выберите меню Главная → Сводная таблица.
В версиях Excel после 2013 года это можно сделать прямо из Excel в меню Вставка → Сводная таблица, выбрав там «Использовать модель данных этой книги». После этого откроется стандартное меню, уже знакомое вам по работе с обычными сводными таблицами.
Вид этого окна может немного отличаться в зависимости от версии Excel.
Так как данные в Power Pivot связаны, можно создать одну сводную таблицу на основе нескольких таблиц. Например, добавить в область строк и столбцов значения из справочников, а в область значений – меры, записанные с помощью DAX-формул.
Подключения к источникам данных и вычисления настраиваются в отчете один раз. Если данные в источниках поменяются, то для повторного выполнения вычислений достаточно нажать на кнопку Обновить все на вкладке Данные.