Быстрый поиск общих значений в списках Excel

Иван Корнев·13.04.2026·⏱4 мин

Чтобы сравнить два столбца в Excel и выделить совпадающие значения, используйте Условное форматирование с формулой =СЧЁТЕСЛИ(). Это самый быстрый способ визуально найти дубликаты без создания дополнительных колонок. Выделите первый диапазон данных, выберите «Условное форматирование» → «Создать правило» → «Использовать формулу» и введите =СЧЁТЕСЛИ($B:$B; A1)>0. Примените цвет заливки. Повторите процедуру для второго столбца, изменив формулу на =СЧЁТЕСЛИ($A:$A; B1)>0.

Если вам нужен не просто визуальный маркер, а список совпадений или точная пометка «Да/Нет», используйте функции ВПР, ПОИСКПОЗ или динамические массивы в новых версиях Excel. Ниже рассмотрены все рабочие методы от простого к сложному.

Краткий итог: Для быстрой подсветки используйте условное форматирование с функцией СЧЁТЕСЛИ. Для получения списка уникальных совпадений в отдельной ячейке (Excel 365) примените формулу =УНИК(ФИЛЬТР(...)).

Метод 1: Визуальное выделение цветом (Условное форматирование)

Этот метод идеален, когда нужно быстро увидеть пересечения двух списков прямо в таблице. Он не меняет данные, а только меняет их отображение.

Пошаговая инструкция:

Выделите диапазон первого столбца (например, A2:A100).
На вкладке Главная нажмите Условное форматирование → Создать правило.
Выберите тип правила: Использовать формулу для определения форматируемых ячеек.
В поле ввода формулы напишите:

    =СЧЁТЕСЛИ($B:$B; A2)>0
    ```
    *Где `$B:$B` — это столбец, с которым сравниваем, а `A2` — первая ячейка выделенного диапазона.*
5.  Нажмите кнопку **Формат**, выберите вкладку **Заливка** и укажите цвет (например, светло-зеленый).
6.  Нажмите **ОК**. Все значения из столбца A, которые есть в столбце B, окрасятся.

Чтобы подсветить совпадения и во втором столбце, повторите те же шаги для диапазона B2:B100, но используйте формулу =СЧЁТЕСЛИ($A:$A; B2)>0. Можно выбрать другой цвет, чтобы видеть направление совпадения.

Метод 2: Создание вспомогательного столбца с пометками

Если нужно отфильтровать строки с совпадениями или экспортировать результат, лучше создать отдельный столбец с текстовыми метками «Найдено» / «Нет».

Формула для проверки наличия значения

Вставьте эту формулу в ячейку C2 (рядом с первой строкой данных) и протяните вниз:

=ЕСЛИ(СЧЁТЕСЛИ($B:$B; A2)>0; "Совпадает"; "Уникально")

Как это работает:

СЧЁТЕСЛИ($B:$B; A2) считает, сколько раз значение из A2 встречается в столбце B.
Если счет больше 0, функция ЕСЛИ возвращает текст «Совпадает».

После заполнения столбца вы можете включить Фильтр (Ctrl+Shift+L) и отобрать только строки со статусом «Совпадает».

Метод 3: Получение списка совпадений одной формулой (Excel 365 / 2021)

В современных версиях Excel можно вывести список всех общих значений в одну ячейку без протягивания формул. Это экономит место и выглядит аккуратно.

Используйте комбинацию функций ФИЛЬТР и УНИК:

=УНИК(ФИЛЬТР(A2:A100; СЧЁТЕСЛИ(B2:B100; A2:A100)>0))

Результат: Формула автоматически создаст вертикальный список только тех значений из столбца A, которые присутствуют в столбце B, удалив при этом внутренние дубликаты.

Важно про пробелы и регистр. Функции СЧЁТЕСЛИ и ВПР не чувствительны к регистру («Текст» и «текст» считаются одинаковыми), но они чувствительны к лишним пробелам. Значение "Иван " (с пробелом в конце) не совпадет с "Иван". Перед сравнением очистите данные формулой =СЖПРОБЕЛЫ() или используйте инструмент «Найти и заменить» (удалить все пробелы).

Сравнение подходов: какой метод выбрать?

Задача	Рекомендуемый метод	Преимущества
Быстро найти глазами общие записи	Условное форматирование	Не требует лишних столбцов, наглядно
Отфильтровать или удалить дубликаты	Вспомогательный столбец + Фильтр	Позволяет манипулировать строками данных
Создать отчетный список пересечений	Формула массива (ФИЛЬТР + УНИК)	Автоматическое обновление, компактность
Сравнение с учетом регистра	Формула `СУММПРОИЗВ` + `ТОЧНО`	Высокая точность (редко требуется)

Частые ошибки при сравнении

Относительные ссылки вместо абсолютных.
- Ошибка: Использование формулы =СЧЁТЕСЛИ(B:B; A1) внутри условного форматирования без фиксации диапазона поиска, если логика сбивается при копировании.
- Решение: Всегда фиксируйте столбец поиска знаком доллара: $B:$B.
Сравнение разных типов данных.
- Ошибка: Число 123 в одном столбце и текст "123" в другом не совпадут.
- Решение: Приведите типы к единому виду через «Текст по столбцам» или умножение на 1.
Скрытые символы.
- Ошибка: Данные скопированы из веба или 1С и содержат неразрывные пробелы.
- Решение: Используйте функцию ПЕЧСИМВ() для удаления непечатаемых символов перед сравнением.

FAQ

Можно ли сравнить три столбца одновременно? Да. В условном форматировании используйте формулу =И(СЧЁТЕСЛИ($B:$B; A1)>0; СЧЁТЕСЛИ($C:$C; A1)>0). Ячейка окрасится, только если значение есть и во втором, и в третьем столбце.

Как найти значения, которые есть в столбце А, но НЕТ в столбце В? Измените логику формулы на «равно нулю». Для условного форматирования: =СЧЁТЕСЛИ($B:$B; A1)=0. Для текстовой пометки: =ЕСЛИ(СЧЁТЕСЛИ($B:$B; A1)=0; "Нет в списке В"; "").

Работает ли это в старых версиях Excel (2010, 2013)? Да, методы с СЧЁТЕСЛИ и условным форматированием работают во всех версиях. Формула с ФИЛЬТР и УНИК доступна только в Excel 365 и версии 2021+.