Power Pivot в Excel: как связать таблицы продаж со справочником товаров

Задача типовая: продажи лежат в одной таблице, справочник товаров — в другой, и ради итогов по категориям приходится тянуть подстановку на тысячи строк. Справка Microsoft описывает другой путь: в Power Pivot связи создают в представлении диаграммы или в окне «Создание связей», а расчёты пишут формулами DAX^[1]. Таблицы остаются раздельными, а сводная берёт поля из обеих.

Порядок освоения простой. Сначала поймите задачу — какие таблицы нужно свести и по какому ключу. Затем выберите инструмент: модель данных вместо формул подстановки. Освойте основы — вкладку, связи и меры, — попробуйте связать таблицы в тренажёре и примените схему к своим выгрузкам. Главная ловушка — связь «на глаз»: Excel проверяет совпадение типов столбцов, но не наличие соответствующих кодов^[2].

Ключевые факты: Power Pivot в Excel

• Power Pivot — надстройка Excel для анализа данных и сложных моделей, куда можно импортировать миллионы строк из разных источников^[1].
• В 32-разрядной среде Excel выделяет 2 ГБ виртуального адресного пространства, и модель данных занимает из них до 500–700 МБ^[3].
• Между двумя таблицами модели активной может быть только одна связь^[2].
• Явная мера отличается от неявной тем, что работает в любой сводной книги, а не только в своей^[4].
• Справка Microsoft открывает окно надстройки кнопкой Manage в группе Data Model вкладки Power Pivot^[5].

Что такое Power Pivot в Excel и что умеет модель данных

Power Pivot в Excel объединяет несколько таблиц в модель данных и считает итоги по ним. Справка Microsoft называет Power Pivot надстройкой для эффективного анализа данных и создания сложных моделей^[1]. Модель живёт внутри книги: таблицы не склеиваются в одну, а соединяются связями.

Таблица показывает, что умеет лист Excel и что добавляет Power Pivot: импорт с фильтром, связи, DAX, KPI, иерархии и перспективы^[1]. Для новичка важнее первые три строки — остальное понадобится в большом отчёте.

Модель данных отличается от обычной сводной тем, что держит несколько таблиц и связи между ними, поэтому поля категории и суммы попадают в один отчёт без вспомогательных столбцов. Английская справка добавляет, что надстройка — часть современного моделирования данных в Excel, а для подготовки таблиц обычно берут Power Query^[5].

Разобравшись с назначением, переходим к окну надстройки — с него начинается любая модель.

Как открыть окно Power Pivot и вернуть пропавшую вкладку

Окно Power Pivot открывают кнопкой Manage на вкладке Power Pivot в группе Data Model^[5]. Так путь назван в английской справке: после включения надстройки на ленте появляется вкладка Power Pivot^[5]. Блок «Применяется к» на странице Power Pivot перечисляет Excel для Microsoft 365, Excel 2024, Excel 2021, Excel 2019 и Excel 2016^[1] — Excel для Mac и Excel в Интернете в нём нет.

Вкладка иногда исчезает. Если Excel закрылся аварийно при открытом окне Power Pivot, лента с надстройкой может пропасть^[6]. Справка предлагает вернуть её так:

1. Откройте File → Options → Add-Ins^[6].
2. В списке Manage выберите Disabled Items и нажмите Go.
3. Выделите Microsoft Office Power Pivot и нажмите Enable.

Пошаговое первое включение справка выносит в отдельную статью «Start the Power Pivot add-in for Excel», и её блок «Применяется к» называет Excel для Microsoft 365, Excel 2024 и Excel 2021^[6].

Таблицу с листа в модель добавляют без окна: оформите диапазон командой «Форматировать как таблицу» и на вкладке Power Pivot выберите «Добавить в модель данных»^[7]. Получится связанная таблица: её правки автоматически синхронизируются с моделью, а в режиме «Вручную» данные обновляются только командами «Обновить все» или «Обновить выбранное»^[7]. Имя таблице дайте до добавления: справка о связанных таблицах отдельно оговаривает имена таблиц как исключение, и после переименования ссылки в модели и отчётах придётся проверить^[7].

Дальше вся работа идёт в окне Power Pivot в Excel: там видны таблицы модели, область вычислений под каждой таблицей и переключатель видов. Лист книги при этом не меняется — модель хранится отдельно от ячеек.

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

Пример модели: продажи и справочник товаров со связью по коду

Модель данных подставляет категорию из справочника к каждой продаже через связь по коду. Возьмём две таблицы: журнал продаж рыболовного магазина и справочник «Товары».

1. Оформите обе таблицы как таблицы Excel и добавьте каждую в модель данных.
2. В окне Power Pivot переключитесь в режим схемы.
3. Щёлкните таблицу продаж правой кнопкой, выберите «Создание связи», укажите столбец «Код», в поле «Связанная таблица подстановки» — «Товары» и её столбец «Код», затем нажмите «Создать»^[2].
4. Постройте сводную по модели: категории из справочника, суммы из продаж.

Справка расходится в названиях: одна страница называет вид «Режим схемы», другая — «представление диаграммы», а английская версия — Diagram view^[2]. Ориентируйтесь на значок вида внизу справа окна Power Pivot^[5].

Код R-07 в справочнике отсутствует, но связь всё равно создастся: Excel проверяет совпадение типов данных, но не наличие соответствующих данных в столбцах^[2]. Сверяйте коды до построения отчёта. Между двумя таблицами бывает несколько связей, но активной может быть только одна^[2].

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

Именно поэтому после связи главное — правильный расчёт: мера или вычисляемый столбец.

Меры DAX или вычисляемые столбцы: что выбрать для расчёта

Мера в Power Pivot считает итог для ячеек сводной, а вычисляемый столбец хранит значения. Справка определяет меры как вычисления, используемые при анализе данных^[4], а вычисляемый столбец — как производные данные в таблице модели^[8].

Явную меру пишут формулой в области вычислений окна Power Pivot; справка приводит пример =SUM('FactResellerSales'[SalesAmount])*1.06^[4]. Для модели из примера мера выручки выглядит так:

=SUM('Продажи'[Сумма])

Вычисляемый столбец задают формулой, которая применяется ко всему столбцу сразу^[8], например = [Сумма] - [Скидка] для таблицы со скидками. Неявная мера появляется при перетаскивании поля в «Значения» и работает только в той сводной, где создана^[4]; явную меру можно взять в любую сводную книги.

Имена в модели требуют аккуратности. Новые столбцы по умолчанию называются CalculatedColumn1, CalculatedColumn2 и так далее, а справка советует не давать столбцу имя, которое уже занято мерой^[8]. После переименования столбца обновите формулы, которые на него ссылаются, — сами они не поправятся^[8].

Остаётся понять, когда модель данных избыточна.

Когда Power Pivot не подойдёт

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

• Если таблица одна, обычная сводная по списку продаж построится быстрее и без модели.
• Когда коллеги открывают книгу в Excel для Mac или в браузере, учтите: этих версий нет в блоке «Применяется к» справки о Power Pivot^[1].
• При 32-разрядном Excel модель данных может достигать лишь 500–700 МБ адресного пространства^[3] — для больших выгрузок нужна 64-разрядная версия без жёстких ограничений размера^[3].
• Если категория нужна прямо в строке продажи, чтобы её видели без сводной, хватит ВПР по коду товара из справочника.
• Если связь, на которую опираются формулы, придётся удалить или изменить, эти формулы станут недопустимыми^[8] — сначала перепишите меры, потом меняйте схему.

Итог простой: Power Pivot не подойдёт, если таблица одна и обычной сводной хватает, если коллеги работают в Excel для Mac или в браузере и если вашей версии нет в справке. В остальных случаях Power Pivot в Excel экономит вспомогательные столбцы и формулы подстановки: справочник хранится один раз, а сводная собирает поля из всех связанных таблиц.

Google Таблицы строят сводную через Insert → Pivot table, и каждому столбцу там нужен заголовок^[9]; модели данных и связей между таблицами эта справка Google не описывает. Соседние темы собраны среди всех уроков тренажёра по моделям данных.