Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Модель данных и DAX

Как заменить VLOOKUP и XLOOKUP с помощью модели данных Excel

forest_paw 06.11.2025 337

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

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

Инструкция рассчитана на настольный Excel для Windows с поддержкой модели данных и Power Pivot; доступность надстройки зависит от выпуска и лицензии. Эти шаги нельзя напрямую повторить в Mac или веб-версии. ПРОСМОТРX отсутствует в бессрочных Excel 2016/2019; модель данных решает задачу связанного анализа, а не заполняет ячейки листа результатами поиска.

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

Столбцы с данными: Продукт, Дата, Клиент, Количество, Выручка и Прибыль.
Столбцы с данными: Продукт, Дата, Клиент, Количество, Выручка и Прибыль.

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

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

    В диалоговом окне нажмите Создать.... Выберите таблицу «Данные» и внешний столбец «Клиент», затем связанную таблицу «Сектора» и её первичный столбец «Клиент». В справочнике каждый ключ должен встречаться один раз, а типы ключевых столбцов должны быть совместимыми. Удалите лишние пробелы и проверьте, что у всех клиентов из продаж есть соответствие. Если имена не уникальны, используйте идентификатор клиента.

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

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

Стандартная агрегация «Количество» считает непустые значения, включая повторы. Для прямого подсчёта различных клиентов нужна сводная на модели данных. Но что, если вам нужно узнать количество уникальных клиентов в каждом секторе?

Этот отчет, показывающий 'Количество клиентов' по секторам, на самом деле отображает количество заказов в каждом секторе.
Этот отчет, показывающий 'Количество клиентов' по секторам, на самом деле отображает количество заказов в каждом секторе.
  1. Перетащите поле Клиент из таблицы Данные в область ЗНАЧЕНИЯ. По умолчанию будет отображаться Количество клиентов.
  2. Дважды щелкните по этому заголовку в сводной таблице. Откроется диалоговое окно Параметры поля значений.
  3. Выберите вкладку с функциями агрегирования («Операция» / Summarize Values By), а не «Дополнительные вычисления» / Show Values As. Прокрутите список вниз. Благодаря использованию модели данных, в самом низу появится опция Количество различных (Distinct Count).
Дважды щелкните заголовок 'Количество клиентов'. В списке функций агрегирования прокрутите до самого низа, и появится скрытая дополнительная опция: 'Количество различных'.
Дважды щелкните заголовок 'Количество клиентов'. В списке функций агрегирования прокрутите до самого низа, и появится скрытая дополнительная опция: 'Количество различных'.

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

Теперь отчет корректно показывает количество уникальных клиентов в каждом секторе.
Теперь отчет корректно показывает количество уникальных клиентов в каждом секторе.

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

Поделиться:

Мы используем cookies для улучшения работы сайта. Продолжая использовать сайт, вы соглашаетесь с политикой конфиденциальности.