Мастер-класс по объединению таблиц в Excel

Иван Корнев·09.04.2026·⏱5 мин

Чтобы сопоставить данные в Excel, используйте функцию ПРОСМОТРX (для новых версий) или связку ИНДЕКС/ПОИСКПОЗ для гибкого поиска по ключевым столбцам. Для регулярного объединения больших массивов без формул идеально подходит инструмент Power Query. Выбор метода зависит от версии офиса и частоты обновления данных: разовые задачи решаются формулами, регулярные отчеты — запросами.

Когда требуется сопоставление данных

Сопоставление (матчинг) необходимо, когда информация разбросана по разным листам или файлам, но имеет общий идентификатор. Типичные сценарии:

Обогащение отчетов: добавление цен, категорий или контактов к списку заказов по артикулу или ID клиента.
Сверка остатков: поиск расхождений между данными склада и бухгалтерии.
Консолидация: создание единой базы из нескольких источников (например, продажи по регионам).
Поиск дубликатов: выявление повторяющихся записей в списках рассылки или контрагентов.

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

Метод 1: Быстрое сопоставление формулами

Для разовых задач и небольших таблиц (до 50–100 тыс. строк) лучше всего подходят встроенные функции поиска.

Использование ПРОСМОТРX (XLOOKUP)

Доступна в Excel 365, 2021 и веб-версии. Это самый надежный и простой инструмент. Синтаксис: =ПРОСМОТРX(искомое_значение; просматриваемый_массив; возвращаемый_массив)

Пример: Нужно подтянуть цену товара из таблицы «Прайс» (лист Data, столбцы A и B) в таблицу «Заказы» (столбец A содержит артикул). Формула в ячейке с ценой: =ПРОСМОТРX(A2; Data!$A:$A; Data!$B:$B; "Не найдено")

Преимущества:

Не ломается при вставке новых столбцов.
Ищет в любом направлении (слева направо и справа налево).
Встроенная обработка ошибок (последний аргумент).

Классическая связка ИНДЕКС и ПОИСКПОЗ

Универсальный метод для всех версий Excel, включая старые (2010, 2013, 2016). Устойчивее, чем ВПР. Синтаксис: =ИНДЕКС(столбец_с_результатом; ПОИСКПОЗ(ключ; столбец_с_ключами; 0))

Пример: =ИНДЕКС(Data!$B:$B; ПОИСКПОЗ(A2; Data!$A:$A; 0)) Здесь 0 означает точное совпадение. Если забыть этот аргумент, функция может вернуть неверное значение при неточном поиске.

Осторожно с ВПР (VLOOKUP): Эта функция ищет значение только в первом столбце диапазона и возвращает данные справа. При добавлении столбцов в исходную таблицу номер колонки в формуле нужно менять вручную, иначе данные «поедут». Используйте её только если нет альтернатив.

Метод 2: Сложное сопоставление по нескольким ключам

Часто одного идентификатора недостаточно. Например, цена зависит не только от «Артикула», но и от «Региона».

Создание составного ключа

Самый простой способ — создать временный столбец в обеих таблицах, объединив критерии.

В таблице заказов создайте столбец «Ключ»: =A2 & "|" & B2 (Артикул и Регион).
В таблице прайса сделайте то же самое.
Примените обычную функцию ПРОСМОТРX или ВПР по новому уникальному ключу. Разделитель (например, |) нужен, чтобы избежать ситуаций, когда «AB» + «C» случайно совпадет с «A» + «BC».

Массивные формулы (без вспомогательных столбцов)

В Excel 365 можно использовать логику умножения условий внутри ПОИСКПОЗ: =ИНДЕКС(Data!$C:$C; ПОИСКПОЗ(1; (Data!$A:$A=A2) * (Data!$B:$B=B2); 0)) Важно: В старых версиях эту формулу нужно подтверждать комбинацией Ctrl+Shift+Enter.

Метод 3: Автоматизация через Power Query

Если вы сводите отчеты ежедневно или еженедельно, ручное копирование формул неэффективно. Инструмент Power Query (вкладка Данные -> Получить данные) позволяет настроить процесс один раз и обновлять его кнопкой.

Алгоритм действий:

Выделите каждую таблицу и нажмите Из таблицы/диапазона. Они откроются в редакторе Power Query.
Выберите главную таблицу, нажмите Объединить запросы (Merge Queries).
В окне выберите вторую таблицу и кликните мышкой по столбцам-ключам в обоих окнах предпросмотра (они подсветятся).
Выберите тип соединения:
- Левое внешнее (Left Outer): оставить все строки из первой таблицы, подтянув совпадения из второй (аналог ВПР).
- Внутреннее (Inner): оставить только строки, которые есть в обеих таблицах.
Нажмите ОК. Появится новый столбец с названием «Table». Нажмите на значок раскрытия в заголовке столбца и выберите поля, которые нужно добавить.
Нажмите Закрыть и загрузить.

Совет по производительности: Перед объединением в Power Query удалите лишние столбцы и отфильтруйте ненужные строки. Это ускорит обработку в разы, особенно на файлах объемом более 100 МБ.

Частые ошибки и способы их устранения

Даже при правильной формуле данные могут не сопоставиться. Проверьте эти пункты:

Проблема	Причина	Решение
#Н/Д (#N/A)	Значение действительно отсутствует или есть лишний пробел.	Используйте функцию `=СЖПРОБЕЛЫ()` (TRIM) для очистки ключей. Проверьте визуальное сходство (например, "0" и "О").
Неверный результат	Ключи отсортированы, но в ВПР забыли указать `ЛОЖЬ` (0).	Всегда используйте точное совпадение для идентификации.
Число как текст	В одной таблице артикул "123" (число), в другой "123" (текст).	Преобразуйте форматы: выделите столбец -> Данные -> Текст по столбцам -> Готово. Или используйте формулу `=ЗНАЧЕН()` / `=ТЕКСТ()`.
Дубликаты ключей	В справочнике два раза встречается один артикул с разной ценой.	Функция вернет только первое найденное значение. Удалите дубликаты в источнике заранее.

FAQ

В чем разница между ВПР и ПРОСМОТРX? ПРОСМОТРX новее, быстрее, не требует нумерации столбцов, ищет в любую сторону и имеет встроенную защиту от ошибок. ВПР — устаревший, но совместимый со всеми версиями инструмент.

Как сопоставить данные из разных файлов? В формулах просто укажите имя файла в квадратных скобках перед именем листа: =[Отчет_май.xlsx]Лист1!$A:$A. В Power Query это делается через кнопку «Из файла» -> «Из книги», что надежнее для постоянных отчетов.

Можно ли сопоставить данные по части текста? Стандартные функции требуют полного совпадения. Для поиска по части слова используйте подстановочные знаки (*) в ВПР/ПРОСМОТРX (например, "*Москва*") или функции фильтрации в новых версиях Excel.