Как заменить ВПР / VLOOKUP и ПРОСМОТРX (XLOOKUP) с помощью модели данных Excel
Если вы устали от медленных и громоздких формул ВПР / VLOOKUP или ПРОСМОТРX (XLOOKUP) для объединения данных, эта статья покажет вам мощную альтернативу — модель данных Excel и Power Pivot. Этот метод позволяет обойтись без множества формул поиска в сводном отчёте и открывает доступ к расширенным возможностям анализа, таким как подсчет уникальных значений.
Инструкция рассчитана на настольный Excel для Windows с поддержкой модели данных и Power Pivot; доступность надстройки зависит от выпуска и лицензии. Эти шаги нельзя напрямую повторить в Mac или веб-версии. ПРОСМОТРX отсутствует в бессрочных Excel 2016/2019; модель данных решает задачу связанного анализа, а не заполняет ячейки листа результатами поиска.
Представьте, что у вас есть основной набор данных с информацией о продуктах, датах, клиентах и продажах, но в нем отсутствует важный столбец, например, сектор клиента.
Для связи используется отдельная справочная таблица, сопоставляющая клиента с его сектором. Вместо того чтобы писать формулы, выполните следующие шаги.
- Преобразуйте таблицы в форматированные. Выделите диапазоны основной и справочной таблиц и на вкладке Главная выберите Форматировать как таблицу. На вкладке Работа с таблицами дайте им осмысленные имена, например,
ДанныеиСектора.
На левой стороне ленты 'Работа с таблицами' присвойте справочной таблице имя, например, Sectors. - Активируйте Power Pivot. Если на ленте нет вкладки Power Pivot, перейдите в Файл → Параметры → Надстройки. В разделе Управление выберите Надстройки COM и нажмите Перейти.... Установите флажок Microsoft Power Pivot для Excel. Также в Параметрах Excel → Настройка ленты убедитесь, что вкладка отображается.
- Добавьте таблицы в модель данных. Выделите ячейку в таблице
Данные. На вкладке Power Pivot выберите Добавить в модель данных. Повторите это для таблицыСектора. - Создайте связь между таблицами. На вкладке Данные выберите Связи; в некоторых версиях команда находится в группе или меню инструментов модели данных.
На вкладке 'Данные' в ленте, в группе 'Инструменты для работы с данными'. Откройте выпадающее меню 'Модель данных' и выберите 'Связи'. В диалоговом окне нажмите Создать.... Выберите таблицу «Данные» и внешний столбец «Клиент», затем связанную таблицу «Сектора» и её первичный столбец «Клиент». В справочнике каждый ключ должен встречаться один раз, а типы ключевых столбцов должны быть совместимыми. Удалите лишние пробелы и проверьте, что у всех клиентов из продаж есть соответствие. Если имена не уникальны, используйте идентификатор клиента.
Диалоговое окно 'Создать связь': столбец 'Клиент' таблицы 'Данные' связан со столбцом 'Клиент' таблицы 'Сектора'. - Создайте сводную таблицу из модели данных. Перейдите на чистый лист. На вкладке Вставка выберите Сводная таблица → Из модели данных.
- Настройте отчет. В области Поля сводной таблицы вы увидите обе таблицы. Перетащите поле
Секториз таблицыСекторав область СТРОКИ, а полеВыручкаиз таблицыДанные— в область ЗНАЧЕНИЯ. Созданная связь позволит группировать выручку по сектору без физического добавления столбца в таблицу продаж.
Бонус: Подсчет уникальных значений
Стандартная агрегация «Количество» считает непустые значения, включая повторы. Для прямого подсчёта различных клиентов нужна сводная на модели данных. Но что, если вам нужно узнать количество уникальных клиентов в каждом секторе?
- Перетащите поле
Клиентиз таблицыДанныев область ЗНАЧЕНИЯ. По умолчанию будет отображаться Количество клиентов. - Дважды щелкните по этому заголовку в сводной таблице. Откроется диалоговое окно Параметры поля значений.
- Выберите вкладку с функциями агрегирования («Операция» / Summarize Values By), а не «Дополнительные вычисления» / Show Values As. Прокрутите список вниз. Благодаря использованию модели данных, в самом низу появится опция Количество различных (Distinct Count).
Выберите этот вариант, и сводная таблица начнет показывать именно количество уникальных клиентов в каждом секторе — задача, которая раньше была крайне сложной для обычных сводных таблиц.
Использование модели данных Excel и Power Pivot — это мощный шаг вперед в аналитике. Этот метод не только избавляет от необходимости в сложных формулах поиска, но и предоставляет более глубокие и гибкие возможности для анализа данных, такие как подсчет уникальных значений, что было практически недоступно в классическом режиме.