Как сравнить два столбца в Excel на совпадения и различия: 6 методов с формулами

Какой метод подойдёт: сравнение шести способов

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

Способ 1. Визуальная маркировка через условное форматирование

Условное форматирование (conditional formatting) — встроенный инструмент, который окрашивает ячейки по заданному правилу. Формулы не нужны, результат виден мгновенно. Главное ограничение: данные только помечаются цветом, не извлекаются.

Как выделить повторяющиеся значения цветом

Выделите диапазон обоих столбцов — например, H6:I15.

Перейдите: «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения».

В диалоге выберите цвет заливки (по умолчанию — светло-красный) и нажмите ОК.

Результат: ячейки с одинаковыми значениями в обоих столбцах окрасятся. Остальные не изменятся.

Как пометить уникальные значения — различия между списками

Чтобы найти различия, а не совпадения, — путь тот же. В диалоге «Повторяющиеся значения» раскройте первый выпадающий список и смените «повторяющиеся» на «уникальные». Excel подсветит значения, присутствующие только в одном из столбцов. Удобно при сверке списков сотрудников, товаров или идентификаторов.

Способ 2. Функция ВПР — найти совпадение и вывести значение

ВПР (VLOOKUP — вертикальный просмотр) ищет значение из одного столбца в диапазоне другого и возвращает результат в отдельную ячейку. Метод подходит для сравнения двух столбцов в Excel, когда нужно не просто пометить совпадение, а вывести его для дальнейшей работы.

Если хотите освоить Excel и аналитику данных системно — в рамках нацпроекта «Кадры» действует программа «Excel. Системный аналитик: моделирование данных в табличных процессорах». Обучение с нуля, бесплатно. Подробнее в каталоге программ обучения.

Синтаксис ВПР и формула для копирования

Формула: =ВПР(H6;$I$6:$I$15;1;0)

Разбор четырёх аргументов:

• H6 — что ищем (значение из первого столбца);
• $I$6:$I$15 — где ищем (диапазон второго столбца, закреплён знаком $);
• 1 — вернуть значение из первого столбца диапазона;
• 0 — точное совпадение, без приближения.

Введите формулу в первую ячейку третьего столбца и протяните вниз до конца диапазона. Где совпадение найдено — появится значение, где нет — ошибка #Н/Д.

Альтернатива для сложных таблиц: =ИНДЕКС(B:B;ПОИСКПОЗ(A2;C:C;0)). Формула ИНДЕКС+ПОИСКПОЗ работает в любом направлении и не ломается при добавлении столбцов левее диапазона поиска — в отличие от ВПР, которая ищет только слева направо.

Что означает ошибка #Н/Д и как её скрыть

#Н/Д — штатный результат, не баг: значение просто не найдено в другом столбце. Чтобы вместо ошибки показывалась пустая ячейка, оберните ВПР в ЕОШИБКА:

=ЕСЛИ(ЕОШИБКА(ВПР(H6;$I$6:$I$15;1;0));»»;ВПР(H6;$I$6:$I$15;1;0))

ЕОШИБКА перехватывает ошибку и подставляет пустую строку вместо красного #Н/Д.

Способ 3. ЕСЛИ и ПОИСКПОЗ — вывести совпадения без ошибок

Комбинация ЕСЛИ + ЕОШИБКА + ПОИСКПОЗ позволяет сравнить два столбца в Excel и получить чистый список: совпадения отображаются, несовпадения скрыты пустой строкой — без #Н/Д в ячейках.

Основная формула: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(H6;$I$6:$I$14;0));»»;H6)

Как работает: ПОИСКПОЗ ищет позицию значения из H6 в диапазоне $I$6:$I$14. Если элемент не найден, возникает ошибка — ЕОШИБКА преобразует её в TRUE. ЕСЛИ при TRUE выводит пустую строку, при FALSE — исходное значение H6.

Для построчного сравнения, когда значения стоят напротив друг друга в одной строке: =ЕСЛИ(И(H6=I6);»Совпадают»;»»)

Способ 4. СЧЕТЕСЛИ — сравнить несколько колонок и расставить метки

СЧЕТЕСЛИ (COUNTIF — подсчёт по условию) считает ячейки, соответствующие критерию. В связке с ЕСЛИ расставляет текстовые метки — и единственная из рассмотренных функций сравнивает сразу три столбца в одной формуле.

Для двух столбцов: =ЕСЛИ(СЧЕТЕСЛИ($H6:$I6;$H6)=2;»Совпадают»;»»)

Для трёх столбцов: =ЕСЛИ(СЧЕТЕСЛИ($H6:$J6;$H6)=3;»Совпадают»;»»)

Тонкость: замените 3 на 2 в трёхстолбцовой формуле — Excel пометит строки, где значение совпало хотя бы в двух из трёх колонок.

Способ 5. Функция СОВПАД — точное сравнение с учётом регистра

ВПР и СЧЕТЕСЛИ не различают «А» и «а» — для них это одно и то же. СОВПАД (EXACT — точное совпадение) регистрозависима: «Иванов» и «ИВАНОВ» она считает разными значениями. Метод нужен при работе с кодами, артикулами или данными, где регистр принципиален.

Формула: =ИЛИ(СОВПАД(I6;$H$6:$H$19))

Вводить обязательно через Ctrl+Shift+Enter — Excel автоматически добавит фигурные скобки {}, признак массивной формулы (array formula). Результат: ИСТИНА при наличии точного совпадения, ЛОЖЬ при его отсутствии.

Способ 6. Надстройка Inquire — сравнить целые книги Excel

Inquire — встроенная надстройка (COM-модуль) для Excel 2013 и новее. Она сравнивает не отдельные столбцы, а два файла XLS/XLSX целиком и выдаёт цветовую карту различий.

Как включить: Файл → Параметры → Надстройки → COM-надстройки → отметить Inquire → ОК.

Как использовать: откройте оба файла → вкладка «Inquire» → Compare Files → выберите два открытых документа → Compare. Зелёный цвет маркирует различия, ячейки без выделения — совпадения.

Нюансы сравнения текстовых данных: регистр и пробелы

При поиске одинаковых значений в Excel в разных столбцах текстовые данные создают скрытые проблемы. Первая — разный регистр: ВПР и СЧЕТЕСЛИ считают «Иванов» и «ИВАНОВ» одинаковыми, СОВПАД — нет. Вторая — лишние пробелы: «Иванов » с концевым пробелом и «Иванов» — разные строки для всех функций без исключения.

Решение: создать вспомогательный столбец с нормализованными данными.

• Удалить лишние пробелы: =СЖПРОБЕЛЫ(A2)
• Привести к нижнему регистру и убрать пробелы: =СЖПРОБЕЛЫ(СТРОЧН(A2))

Сравнивайте нормализованные столбцы — все функции дадут точный результат.

Для таблиц от 10 000 строк формулы пересчитываются при каждом изменении и замедляют файл. В этом случае оптимально подходит Power Query (встроенный инструмент для обработки данных, Excel 2016+): Данные → Получить данные → объединить через Merge Queries (слияние запросов).

Часто задаваемые вопросы

Как быстро сравнить два столбца без формул?

Условное форматирование: выделите оба столбца → «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения» → выбрать цвет → ОК. Дубликаты окрасятся мгновенно. Для поиска различий — в том же диалоге смените «повторяющиеся» на «уникальные». Метод работает с любым объёмом данных, формулы не нужны.

Что делать, если ВПР показывает #Н/Д?

#Н/Д — штатный результат: значение не найдено в другом столбце. Чтобы показывалась пустая ячейка, оберните ВПР в ЕОШИБКА: =ЕСЛИ(ЕОШИБКА(ВПР(H6;$I$6:$I$15;1;0));»»;ВПР(H6;$I$6:$I$15;1;0)). Функция ЕОШИБКА перехватывает ошибку и подставляет пустую строку вместо красного #Н/Д.

Как сравнить столбцы с текстом, где важен регистр букв?

ВПР и СЧЕТЕСЛИ регистр игнорируют — «А» и «а» для них равны. Для регистрозависимого сравнения используйте СОВПАД: =ИЛИ(СОВПАД(I6;$H$6:$H$19)) — вводить через Ctrl+Shift+Enter, появятся {}. Возвращает ИСТИНА при точном совпадении с учётом заглавных букв. Подходит для кодов, артикулов, паролей.

Можно ли сравнить сразу три и более столбца?

СЧЕТЕСЛИ справится: =ЕСЛИ(СЧЕТЕСЛИ($H6:$J6;$H6)=3;»Совпадают»;»»). Число в конце равно количеству сравниваемых колонок. Замените 3 на 2 — Excel пометит строки, где значение совпало хотя бы в двух из трёх столбцов.

Чем ИНДЕКС+ПОИСКПОЗ лучше ВПР?

ВПР ищет только слева направо и ломается при добавлении столбцов левее диапазона поиска. ИНДЕКС+ПОИСКПОЗ (=ИНДЕКС(B:B;ПОИСКПОЗ(A2;C:C;0))) работает в любом направлении без привязки к расположению столбцов. Рекомендуется при сложной структуре таблицы или частых изменениях её макета.

Как выделить уникальные значения, а не дубликаты?

Тот же инструмент — условное форматирование. В диалоге «Повторяющиеся значения» раскройте первый список и смените «повторяющиеся» на «уникальные». Уникальные значения — те, что присутствуют только в одном из двух столбцов. Метод подходит для поиска различий между списками заказов, кадровыми реестрами, каталогами товаров.

Как убрать лишние пробелы перед сравнением столбцов?

Создайте вспомогательный столбец: =СЖПРОБЕЛЫ(A2) — удаляет ведущие, завершающие и задвоенные пробелы. Для одновременного удаления пробелов и приведения к нижнему регистру: =СЖПРОБЕЛЫ(СТРОЧН(A2)). Сравнивайте нормализованные данные — все функции дадут точный результат.

Какой метод выбрать для таблицы с 10 000+ строк?

ВПР и СЧЕТЕСЛИ пересчитываются при каждом изменении и заметно замедляют файл на больших данных. Для таблиц от 10 000 строк оптимально подходит Power Query (Excel 2016+): Данные → Получить данные → объединить через Merge Queries (слияние запросов), тип соединения «Левое антисоединение» — для поиска несовпадений. Пересчёт происходит только при ручном обновлении запроса.