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