Поиск дубликатов в Excel осложняется множеством факторов. Это могут быть лишние пробелы, знаки препинания, перестановки слов. К тому же, часто хочется ограничиться только поиском дублей без их удаления. Если же вы спокойны и готовы избавиться от дублей в 1 шаг, не проверяя их, вам в раздел «удалить дубликаты«.

Иногда дубли хочется найти внутри колонки, другой раз интересует поиск значений, повторяющихся в другой колонке.

Ситуаций множество, но ключевых моментов всего три:
1. Какие ячейки мы готовы считать дубликатами — все кроме первого или включая его?
2. Считаем ли дублями строки, отличающиеся только пробелами до, после слов или лишними пробелами между словами?
3. Где мы будем искать дубли — внутри текущего диапазона, или производим сравнение с другим диапазоном?

Итак, обо всем по порядку

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

Зачастую под словом «найти» рядовой пользователь Excel подразумевает «найти и выделить цветом», когда дело заходит о повторяющихся значениях.

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

Процедура находится на вкладке «Главная»:

Условное форматирование - выделение повторяющихся значений на панели Excel
Вызов процедуры условного форматирования для подсветки повторяющихся значений

Процедура интуитивно понятна:

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

Важно понимать, что процедура находит дубликаты внутри всего диапазона и поэтому может не быть применима для сравнения двух столбцов. Достаточно иметь дубликаты внутри одного столбца — и процедура подсветит их оба, хотя во втором их не будет:

Неочевидный результат работы условного форматирования с дублями внутри диапазона
Условное форматирование меняет цвет дубликатов внутри столбца

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

Как избежать подобной ситуации, если хочется найти именно дубли в другом столбце? Простейшее решение — удалить дубли внутри каждого столбца перед применением условного форматирования.

Но есть и другие решения — о них дальше.

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

Использование собственной формулы для проверки дубликатов в списке или диапазоне имеет ряд преимуществ, единственная задача — составление такой формулы. Но её я возьму на себя.

Внутри диапазона

Чтобы проверить, есть ли в диапазоне повторяющиеся значения, можно использовать такую формулу массива:

=СУММПРОИЗВ(СЧЁТЕСЛИ(диапазон;тот-же-диапазон)-1)>0

Так выглядит на практике применение формулы:

Формула возвращает ИСТИНА, если в адресованном диапазоне появляется дубликат

В чем же преимущество такой формулы, ведь она полностью дублирует опцию условного форматирования? — Спросите вы.

А дело все в том, что формулу несложно видоизменить и улучшить.

Например, можно улучшить эффективность формулы, добавив в нее функцию СЖПРОБЕЛЫ — это позволит находить дубликаты, отличающиеся незаметными лишними пробелами:

=СУММПРОИЗВ(--(СЖПРОБЕЛЫ(ячейка)=СЖПРОБЕЛЫ(диапазон)))>1

Эта формула слегка отличается, так как проверяет встречаемость в диапазоне значения одной ячейки.

Если внести ее как правило отбора условного форматирования, она позволит выявлять неявные дубли. Ниже наглядная демонстрация:

демонстрация использования формулы для поиска дубликатов с неучетом пробелов
Применение формулы для условного форматирования и поиска неявных дубликатов

Обратите внимание на пару моментов этой демонстрации:

  • диапазон закреплен ($A$1:$B$4), а искомая ячейка — нет (A1).
  • именно это позволяет условному форматированию находить все дубликаты в диапазоне

Выделить цветом совпадения из другого столбца/диапазона

Поиск дубликатов с !SEMTools

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

Поэтому я пришел к тому, чтобы сделать процесс удаления дубликатов максимально быстрым и удобным — сделал для этого отдельные процедуры в своей надстройке. Далее покажу, как они работают.

Найти дубли ячеек внутри диапазона, кроме первого

Найти дубли кроме первого

Найти в диапазоне дубли ячеек, включая первый

Найти дубли без учета лишних пробелов

Если мы считаем дубликатами фразы, отличающиеся количеством пробелов между словами или после — наша задача сначала избавиться от лишних пробелов, и далее — произвести тот же поиск пробелов.

Для первой операции есть отдельный макрос — удалить лишние пробелы

Как найти дубли ячеек, не учитывая лишние пробелы

Смотрите также:

Удалить дубли без смещения строк

Удалить неявные дубли

Найти повторяющиеся слова в Excel

Удалить повторяющиеся слова внутри ячеек