Сравнение двух таблиц в Excel¶

Сравнение двух таблиц в Excel — это набор приёмов и средств электронных таблиц Microsoft Excel, предназначенных для сопоставления значений двух диапазонов данных и выявления совпадающих, различающихся или отсутствующих записей. Операция применяется при сверке баз данных, проверке дубликатов, сопоставлении прайс-листов, сверке бухгалтерских отчётов и других задачах, где необходимо определить, какие строки или ячейки в двух списках совпадают, а какие нет.

¶Основные методы сравнения

В Excel реализовано несколько принципиально разных подходов к сравнению таблиц:

• Формульный — с использованием функций ВПР (ВЕРР), ИНДЕКС/ПОИСКПОЗ, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, ЕСЛИ, СУММПРОИЗВ, а также новых функций поиска (ПОИСК, СОВПАД, ПОИСКЗНАЧ).
• Условное форматирование — визуальное выделение совпадающих или различных ячеек без изменения данных.
• Инструмент «Удалить дубликаты» и Power Query — для массовой обработки и объединения таблиц.
• Power Pivot и DAX — для сложных сопоставлений на больших объёмах данных.

¶Формульные методы

¶Функция ВПР

Классический способ — функция ВПР (в англоязычной версии — VLOOKUP). Она ищет значение из первой таблицы во второй и возвращает соответствующее значение из нужного столбца. Если ячейка возвращает ошибку #Н/Д, совпадения нет.

Пример: =ВПР(A2; Лист2!$A:$B; 2; ЛОЖЬ) — ищет значение из ячейки A2 первого листа в первом столбце диапазона листа 2 и возвращает значение второго столбца. Четвёртый аргумент ЛОЖЬ обеспечивает поиск точного совпадения.

¶СЧЁТЕСЛИ и СЧЁТЕСЛИМН

Для проверки наличия значения в другой таблице проще использовать СЧЁТЕСЛИ: =СЧЁТЕСЛИ(Лист2!$A:$A; A2). Если результат больше нуля — значение найдено. Для проверки совпадения по нескольким столбцам одновременно применяется СЧЁТЕСЛИМН с несколькими парами «диапазон — критерий».

¶ИНДЕКС и ПОИСКПОЗ

Комбинация ИНДЕКС/ПОИСКПОЗ (в англоязычной версии — INDEX/MATCH) является более гибкой альтернативой ВПР: ПОИСКПОЗ находит номер строки совпадения, а ИНДЕКС возвращает значение из нужного столбца. В отличие от ВПР, эта связка позволяет искать значения слева от ключевого столбца.

¶ЕСЛИ и СУММПРОИЗВ

Для прямого сравнения двух ячеек используется =ЕСЛИ(A2=B2; "Совпадает"; "Различается"). Для сравнения двух массивов целиком применяется СУММПРОИЗВ, которая позволяет получить количество различий между диапазонами: =СУММПРОИЗВ((Лист1!A1:A100<>Лист2!A1:A100)*1).

¶Новые функции поиска (Excel 365 / Excel 2021+)

В актуальных версиях Excel появились динамические массивные функции:

• ПОИСК (в англоязычной версии — XLOOKUP) — ищет значение и возвращает совпадение, имеет встроенный обработчик отсутствия результата.
• СОВПАД (XMATCH) — возвращает позицию значения в массиве.
• ПОИСКЗНАЧ (FILTER) — фильтрует данные по условию, что удобно для выделения только совпадающих или только различающихся строк.

¶Условное форматирование

Для визуального сравнения двух таблиц используется условное форматирование:

1. Выделить диапазон первой таблицы.
2. На вкладке «Главная» выбрать «Условное форматирование» → «Создать правило» → «Использовать формулу».
3. Ввести формулу вида =СЧЁТЕСЛИ(Лист2!$A:$A; A1)=0 и задать формат для ячеек без совпадений (например, красную заливку).

Аналогичным образом можно выделить совпадающие ячейки, поменяв условие на >0. Для выделения различий между двумя диапазонами одной таблицы используется встроенная группа правил «Выделить ячейки правила» → «Значения, не равные друг другу».

¶Power Query

Для регулярных сверок больших таблиц применяется Power Query (вкладка «Данные» → «Получить данные» → «Объединить»). Инструмент позволяет выбрать тип объединения:

После объединения можно добавить вычисляемый столбец, определяющий, найдено ли совпадение, и отфильтровать результат.

¶Удаление дубликатов

Встроенная команда «Удалить дубликаты» (вкладка «Данные») работает внутри одной таблицы. Для поиска дубликатов между двумя таблицами их предварительно объединяют в один диапазон (например, скопировав второй столбец под первый) и затем запускают команду, указав ключевые столбцы.

¶Особенности и типичные ошибки

• Разные типы данных: число 1 и текст «1» не будут считаться совпадающими при прямом сравнении.
• Лишние пробелы в начале или конце ячейки — устраняются функцией СЖПРОБЕЛ.
• Разные кодировки или невидимые символы — требуют очистки через СЖПРОБЕЛ и ПОДСТРОКА.
• При использовании ВПР с ЛОЖЬ порядок столбцов обязателен: искомый ключ должен быть первым столбцом диапазона.
• На очень больших таблицах (сотни тысяч строк) массивные формулы и СУММПРОИЗВ могут существенно замедлять работу — в таких случаях предпочтительнее Power Query.

Источники:

• Microsoft Docs — документация по функциям Excel
• «Excel 2019. Полный курс профессионала», Д. Макферсон
• «Большая книга Excel», Дж. Уокенбах
• Справка Microsoft Excel (поддержка Microsoft)