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

Быстрый способ: штатное правило

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

Выделить только повторы, а не все вхождения

Штатное правило красит и первое, и все последующие появления значения одинаково — не видно, где значение встретилось впервые, а где повторилось. Чтобы оставить первое вхождение без подсветки и выделить только реальные повторы, создайте своё правило («Условное форматирование» → «Создать правило» → «Использовать формулу для определения форматируемых ячеек») с формулой:

=СЧЁТЕСЛИ($A$2:A2;A2)>1

Диапазон в формуле начинается с первой строки данных и не закреплён с нижнего края — при движении вниз по строкам он «растёт», и функция считает, сколько раз значение из текущей строки уже встречалось выше, включая саму строку. Для первого появления результат всегда 1 (не больше 1 — подсветки нет), для второго и далее — 2, 3 и так далее (условие выполняется).

Совпадения между двумя столбцами

Если нужно подсветить в столбце A те значения, которые есть и в столбце B (например, клиенты из старого и нового списка), выделите столбец A и создайте правило с формулой:

=СЧЁТЕСЛИ($B$2:$B$100;A2)>0

Здесь диапазон второго столбца закреплён полностью (не растёт), а ссылка на ячейку первого столбца — относительная, чтобы формула проверяла свою строку при копировании вниз по диапазону. Это подходит, чтобы быстро увидеть пересечения между двумя столбцами; если нужно построчно сравнить именно две таблицы целиком и найти все различия, а не только совпадения, — смотрите статью «Как сравнить два столбца или две таблицы в Excel».

С учётом регистра

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

=СУММПРОИЗВ(--ТОЧН(A2;$A$2:$A$100))>1

ТОЧН сравнивает текст с учётом регистра и возвращает массив ИСТИНА/ЛОЖЬ по всему диапазону, СУММПРОИЗВ складывает результат в одно число — если совпадений (включая саму ячейку) больше одного, значит есть точный регистрозависимый повтор.

Повтор по нескольким столбцам сразу

Когда дубликатом считается не одно значение, а сочетание (например, одинаковые ФИО и дата вместе, а не по отдельности), выделите таблицу и создайте правило с формулой СЧЁТЕСЛИМН — она проверяет несколько условий одновременно:

=СЧЁТЕСЛИМН($A$2:$A$100;A2;$B$2:$B$100;B2)>1

Формула считает, сколько строк в диапазоне одновременно совпадают и по столбцу A, и по столбцу B — подсветятся только строки, где повторяется именно сочетание значений, а не каждый столбец по отдельности.

Разные цвета для разных групп повторов

Готового правила «своя группа — свой цвет» в условном форматировании нет: все правила красят подходящие ячейки в один и тот же цвет. Приблизительно разделить группы цветом помогает вспомогательный столбец с формулой =ПОИСКПОЗ(A2;$A$2:$A$100;0) — она возвращает номер строки первого появления значения, то есть у всех повторов одной группы будет одно и то же число. Если затем применить к этому столбцу «Цветовую шкалу» из условного форматирования, близкие числа получат похожие оттенки — это грубая группировка, а не чёткое разделение на цвета, но для небольшого числа групп разницу обычно видно.

Частые проблемы

• Визуально одинаковые значения не подсвечиваются. Частая причина — лишние пробелы в начале, конце или внутри текста; такие строки формально различаются. Перед проверкой на повторы стоит очистить текст функцией СЖПРОБЕЛЫ.
• Числа не считаются повторами, хотя выглядят одинаково. Одно число может быть сохранено как текст, другое — как число: визуально оба выглядят как «100», но для Excel это разные типы данных. Проверить тип можно функцией ЕЧИСЛО или просто по выравниванию в ячейке (числа по умолчанию прижаты вправо, текст — влево).
• После копирования правила на другие ячейки формула считает неправильно. Обычно дело в абсолютных и относительных ссылках — если диапазон, который должен быть закреплён (со знаком $), скопировался как относительный, при протягивании он «съезжает» и сравнивает не те ячейки. Проверьте адреса в самой формуле правила через «Управление правилами».
• Одна ячейка подсвечивается не тем цветом, что ожидалось. Если на диапазон наложено сразу несколько правил условного форматирования, применяется то, что выше в списке «Диспетчера правил», а срабатывание следующих можно ограничить галочкой «Остановить, если истина» — проверьте порядок правил и эту галочку в «Управление правилами».

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

Что дальше

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