Поиск повторяющихся значений в Excel

Microsoft Excel достаточно богат на функции анализа диапазонов данных, ранее мы рассматривали каким образом можно сравнить два файла Excel, как можно использовать условное форматирование для поиска уникальных записей в двух диапазонах данных, а также визуализацию статистический информации с добавлением функции ЗНАК (SIGN).

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

Первая часть способа.

Рассмотрим пример нахождения повторяющихся значений.

диапазон с данными в Excel

Для нахождения повторяющихся значений необходимо выполнить следующий пошаговый алгоритм действий:

  1. Выделить исходный диапазон (A1:E8)
  2. Выполнить команду: вкладка Главная / группа Стили / Условное форматирование / Создать правило

  1. В диалоговом окне выбрать: "Использовать формулу для определения форматируемых ячеек", при этом диалоговое окно немного изменит свой вид, далее следует ввести следующую формулу:
    =СЧЁТЕСЛИ($A$1:$E$8;A1)>1

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

  1. После нажатия кнопки "ОК", можно сразу наблюдать результат выполнения операции.

Введенная формула сравнивает значение каждой отдельно взятой ячейки с ячейками из диапазона и, если ячейка не уникальна, то к ней применяется форматирование, в нашем случае, выполняется заливка ячейки оранжевым цветом.

Вторая часть способа.

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

  1. Основная идея нахождения неуникальных, или, наоборот, уникальных строк, заключается в том, чтобы из всех строк диапазона сделать одну путем конкатенации (соединения), а потом искать неуникальные значений в новом диапазоне. Соединить строки можно, кстати, тоже не одним способом, например, отлично подойдет знак конкатенации "&", равно как и функция СПЕПИТЬ (CONCATENATE).

Соединение ячеек в Excel функцией СЦЕПИТЬ

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

В окне построений правил MS Excel, разработчики уже предусмотрели наиболее частые сценарии использования этого инструмента, поэтому можно не вписывать формулу, а выбрать пункт "Форматировать только уникальные или повторяющиеся значения"

Создание правила условного форматирования в Excel

  1. После нажатия "ОК", результат не заставит себя долго ждать.

Результат поиска не уникальных строк

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