Как выделить повторяющиеся значения цветом в Excel?
Из этого туториала Вы узнаете, как просматривать дубликаты в Excel. Мы рассмотрим различные методы затенения повторяющихся ячеек, сплошных линий или последовательных повторов с использованием условного форматирования.
Ранее мы исследовали различные способы поиска дубликатов в Excel. Эти формулы, несомненно, очень полезны, но выделение повторяющихся записей определенным цветом может упростить анализ ваших данных.
Самый быстрый способ найти и выделить дубликаты в Excel — использовать условное форматирование. Самым большим преимуществом этого метода является то, что он не только показывает существующие совпадения, но и мгновенно обнаруживает и окрашивает новые повторы, когда вы вводите, редактируете или перезаписываете свои данные.
Позже в этом руководстве вы найдете несколько способов выделить повторяющиеся записи в зависимости от вашего конкретного бизнеса. Эти методы работают во всех версиях Excel 2016, Excel 2013, Excel 2010 и более ранних версиях.
Как подсветить дубликаты, используя встроенный инструмент (с 1-м появлением)
Во-первых, во всех версиях Excel есть шаблон для выделения повторяющихся ячеек. Чтобы использовать его в таблицах, выполните следующие действия:
- Выберите данные, которые хотите проверить. Это может быть столбец, строка или диапазон ячеек.
- На вкладке «Главная» выберите «Условное форматирование»> «Правила выделения ячеек»> «Повторяющиеся значения».
Откроется диалоговое окно со светло-красной заливкой и темно-красным текстом, выбранными по умолчанию. Чтобы применить формат по умолчанию, нажмите ОК.
Помимо красной заливки и форматирования текста, в раскрывающемся списке доступно множество других предопределенных форматов. Чтобы раскрасить повторяющиеся ячейки другим цветом, щелкните «Пользовательский формат» (последний элемент в раскрывающемся списке) и выберите нужный цвет заливки и / или шрифта.
Запрос. Чтобы обозначить уникальные значения цветом, выберите «Уникальный» в поле слева.
И вот что в итоге получилось:
Как видите, повторов было много. Применяя встроенное правило дублирования к двум или более столбцам, Excel не сравнивает содержащиеся в них значения, а просто выбирает все повторяющиеся экземпляры. Для работы со всей таблицей или для сравнения столбцов этот метод вряд ли подойдет вам. Но для поиска в столбце или строке этого достаточно.
При использовании этого инструмента помните о двух вещах:
- Это работает только для одиночных ячеек. Чтобы отмечать повторяющиеся строки, вам нужно создать свои собственные правила.
- Он рисует повторяющиеся клетки, включая их первые появления. Чтобы отметить все, кроме первого вхождения, создайте правило условного форматирования на основе формулы в этом руководстве для поиска дубликатов.
Как выделить повторы без 1-го вхождения
Чтобы отметить второе и все последующие повторяющиеся вхождения, выберите ячейки, которые нужно заполнить, и выполните следующие действия:
- Выберите «Условное форматирование»> «Новое правило»> «Использовать формулу», чтобы определить, какие ячейки нужно форматировать .
- В поле «Значения формата», где эта формула возвращает ИСТИНА, введите:
= СЧЁТЕСЛИ ($ B $ 2: $ B2; $ B2)> 1
Где B2 — верхняя ячейка выбранного диапазона.
- Нажмите кнопку «Форматировать» и выберите желаемый цвет заливки и / или шрифт.
- Наконец, нажмите ОК, чтобы сохранить и применить свое творение.
Если у вас нет большого опыта работы с условным форматированием Excel, вы найдете подробные инструкции о том, как создать правило на основе формулы в следующем руководстве: Как изменить цвет ячейки в зависимости от значения?
В результате повторяющиеся ячейки, за исключением первых экземпляров, будут выделены выбранным вами цветом.
Как показать третий, четвертый и все последующие дубликаты?
Чтобы увидеть повторяющиеся значения, начиная с n-го вхождения, действуйте, как в предыдущем примере. Единственное отличие состоит в том, что вы заменяете> 1 в конце формулы на необходимое число. Например:
Чтобы раскрасить третий и все последующие повторы, примените следующее:
= СЧЁТЕСЛИ ($ B $ 2: $ B2, $ B2)> = 3
Чтобы указать на четвертую и все последующие повторяющиеся записи, используйте выражение:
= СЧЁТЕСЛИ ($ B $ 2: $ B2, $ B2)> = 4
Чтобы выбрать только определенные вхождения, используйте оператор равенства (=).
Например, чтобы изменить фон только 2 экземпляров, вы воспользуетесь следующей формулой:
= СЧЁТЕСЛИ ($ B $ 2: $ B2, $ B2) = 2
Как выделить дубликаты в диапазоне
Если вы хотите проверить наличие повторяющихся данных в области данных, найдя все экземпляры одного и того же элемента, используйте одно из следующих решений.
Выделите дубликаты в таблице, включая 1-е вхождение.
Если первый экземпляр элемента, который появляется в наборе данных более одного раза, считается дубликатом, самый простой способ — использовать встроенное правило Excel для дубликатов, о котором мы говорили выше.
Или создайте собственное условное форматирование:
= СЧЁТЕСЛИ (диапазон; первая_ячейка)> 1
Например, чтобы закодировать совпадения в диапазоне A2: C8, сделайте следующее:
= СЧЁТЕСЛИ ($ A $ 2: $ C $ 8; A2)> 1
Обратите внимание на использование абсолютных ссылок на ячейки для диапазона ($ A $ 2: $ C $ 8) и относительных ссылок на ячейки для верхней ячейки (A2).
Выделите повторы в таблице без 1-го вхождения.
Решение для этого сценария намного сложнее. Неудивительно, что в Excel нет готового решения для этого
