Как заменить ВПР (VLOOKUP) и ПРОСМОТРХ (XLOOKUP) с помощью модели данных Excel

Если вы устали от медленных и громоздких формул ВПР (VLOOKUP) или ПРОСМОТРХ (XLOOKUP) для объединения данных, эта статья покажет вам мощную альтернативу — модель данных Excel и СТЕПЕНЬ (Power) Pivot. Этот метод не только ускоряет работу, но и открывает доступ к расширенным возможностям анализа, таким как подсчет уникальных значений.

Представьте, что у вас есть основной набор данных с информацией о продуктах, датах, клиентах и продажах, но в нем отсутствует важный столбец, например, сектор клиента.

Для связи используется отдельная справочная таблица, сопоставляющая клиента с его сектором. Вместо того чтобы писать формулы, выполните следующие шаги.

1. Преобразуйте таблицы в форматированные. Выделите диапазоны основной и справочной таблиц и на вкладке Главная выберите Форматировать как таблицу. На вкладке Работа с таблицами дайте им осмысленные имена, например, Данные и Сектора.



2. Активируйте СТЕПЕНЬ (Power) Pivot. Если на ленте нет вкладки СТЕПЕНЬ (Power) Pivot, перейдите в Файл → Параметры → Надстройки. В разделе Управление выберите Надстройки COM и нажмите Перейти.... Установите флажок Microsoft СТЕПЕНЬ (Power) Pivot для Excel. Также в Параметрах Excel → Настройка ленты убедитесь, что вкладка отображается.
3. Добавьте таблицы в модель данных. Выделите ячейку в таблице Данные. На вкладке СТЕПЕНЬ (Power) Pivot выберите Добавить в модель данных. Повторите это для таблицы Сектора.
4. Создайте связь между таблицами. На вкладке Данные в группе Инструменты для работы с данными откройте выпадающий список Модель данных и выберите Связи.



В диалоговом окне нажмите Создать.... Укажите общее поле для связи — в нашем случае это Клиент.



5. Создайте сводную таблицу из модели данных. Перейдите на чистый лист. На вкладке Вставка выберите Сводная таблица → Из модели данных.
6. Настройте отчет. В области Поля сводной таблицы вы увидите обе таблицы. Перетащите поле Сектор из таблицы Сектора в область СТРОКИ, а поле Выручка из таблицы Данные — в область ЗНАЧЕНИЯ. Данные будут объединены автоматически.

Бонус: Подсчет уникальных значений

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

1. Перетащите поле Клиент из таблицы Данные в область ЗНАЧЕНИЯ. По умолчанию будет отображаться Количество клиентов.
2. Дважды щелкните по этому заголовку в сводной таблице. Откроется диалоговое окно Параметры поля значений.
3. Перейдите на вкладку Способы вычисления. Прокрутите список вниз. Благодаря использованию модели данных, в самом низу появится опция Количество различных (Distinct СЧЁТ (Count)).

Выберите этот вариант, и сводная таблица начнет показывать именно количество уникальных клиентов в каждом секторе — задача, которая раньше была крайне сложной для обычных сводных таблиц.

Использование модели данных Excel и СТЕПЕНЬ (Power) Pivot — это мощный шаг вперед в аналитике. Этот метод не только избавляет от необходимости в сложных формулах поиска, но и предоставляет более глубокие и гибкие возможности для анализа данных, такие как подсчет уникальных значений, что было практически недоступно в классическом режиме.