Когда вы работаете с большими объемами данных, легко потерять их из виду и увидеть, как они многократно появляются в таблице. Некоторые дубликаты размещены намеренно, а другие - ошибочными. В любом случае, вы можете захотеть, чтобы эти дубликаты автоматически выделялись для вас.
Найти повторяющиеся ячейки в небольшой электронной таблице легко, но при работе с большими и сложными наборами данных может быть довольно сложно сделать это вручную. К счастью, в Excel есть встроенные инструменты, позволяющие выделять повторяющиеся значения. В этом руководстве мы покажем вам, как выделить повторяющиеся данные с помощью функции условного форматирования в Excel.
Выделите дубликаты с условным форматированием в Excel. Как правило, вы можете захотеть найти дубликаты в Excel, потому что чаще всего дубликаты появляются по ошибке и должны быть удалены, или дубликаты важны для анализа и должны быть выделены в Excel.
Есть два метода поиска повторяющихся значений с условным форматированием в Excel. Они есть:
- Выделите дубликаты с помощью правила повторяющегося значения
- Выделите дубликаты с помощью настраиваемой формулы Excel (СЧЁТЕСЛИ и СЧЁТЕСЛИ)
Выделите дубликаты с помощью правила дублирования значений
Предположим, у нас есть этот набор данных:
Сначала выберите диапазон ячеек, содержащий повторяющиеся значения. Затем перейдите на вкладку «Главная» и нажмите «Условное форматирование» в разделе «Стили» на ленте. В раскрывающемся списке наведите курсор на первый вариант «Highlight Cell Rules», и он снова отобразит список правил во всплывающем окне. Выберите здесь вариант «Повторяющиеся значения».
После того, как вы нажмете на «Повторяющиеся значения», появится диалоговое окно «Повторяющиеся значения». Здесь вы можете выбрать тип форматирования для повторяющихся значений. Вы можете выбрать один из цветов, чтобы заполнить только ячейки, только для шрифта, в качестве границы или в произвольном формате, если хотите. Затем нажмите «ОК», чтобы закрыть диалоговое окно.
В нашем примере мы выбираем «Зеленая заливка темно-зеленым текстом».
После выбора типа форматирования будут выделены все повторяющиеся значения в выбранном диапазоне, как показано ниже.
Выделять Дубликаты Uпеть СЧЁТЕСЛИ Формула
Другой способ выделить повторяющиеся значения - использовать условное форматирование с простой формулой СЧЁТЕСЛИ в одном или нескольких столбцах.
Выберите диапазон данных, в котором вы хотите выделить дубликаты. Затем перейдите на вкладку «Главная» и выберите «Условное форматирование». В раскрывающемся списке выберите «Новое правило».
Откроется диалоговое окно «Новое правило форматирования».
В диалоговом окне «Новое правило форматирования» выберите параметр «Использовать формулу для определения ячеек для форматирования» в поле со списком «Выбор типа правила», затем введите следующую формулу СЧЁТЕСЛИ для подсчета дубликатов.
= СЧЁТЕСЛИ ($ A $ 1: $ C $ 11; A1)> 1
Затем нажмите кнопку «Форматировать», чтобы перейти к диалоговому окну «Форматирование ячеек». В диалоговом окне «Формат ячеек» вы можете выбрать цвет заливки из цветовой палитры для выделения ячеек, а затем нажать «ОК». Здесь мы выбираем синий цвет заливки для форматирования дубликатов.
Затем снова нажмите «ОК», чтобы закрыть диалоговое окно. Формула выделит все значения ячеек, которые появляются более одного раза.
Всегда вводите формулу для левой верхней ячейки в выбранном диапазоне (A1: C11). Excel автоматически копирует формулу в другие ячейки.
Вы также можете определять правила, как хотите. Например, если вы хотите найти значения, которые появляются в таблице только два раза, введите вместо этого эту формулу (в диалоговом окне «Новое правило форматирования»):
= СЧЁТЕСЛИ ($ A $ 1: $ C $ 11; A1) = 2Результат:
Иногда вы можете захотеть увидеть только дубликаты и отфильтровать уникальные значения. Для этого выберите диапазон, перейдите на вкладку «Главная», нажмите кнопку «Сортировка и фильтр» в правом верхнем углу Excel и выберите параметр «Фильтр».
В первой ячейке каждого столбца появится раскрывающееся меню, в котором вы можете определить критерий фильтрации. Щелкните раскрывающийся список в первой ячейке столбца и выберите «Фильтр по цвету». Затем выберите синий цвет.
Теперь вы увидите только выделенные ячейки и можете делать с ними все, что захотите.
Найдите и выделите повторяющиеся строки в Excel с помощью формулы СЧЁТЕСЛИМН
Если вы хотите найти и выделить повторяющиеся строки в Excel, используйте СЧЁТЕСЛИ вместо СЧЁТЕСЛИ.
Выберите диапазон, перейдите на вкладку «Главная» и нажмите «Условное форматирование» в группе «Стили». В раскрывающемся списке выберите «Новое правило».
В диалоговом окне «Новое правило форматирования» выберите параметр «Использовать формулу для определения ячеек для форматирования» в поле со списком «Выбор типа правила», а затем введите следующую формулу СЧЁТЕСЛИМН:
= COUNTIFS ($ A $ 1: $ A $ 20, $ A1, $ B $ 1: $ B $ 20, $ B1, $ C $ 1: $ C $ 20, $ C1)> 1В приведенной выше формуле диапазон A1: A20 относится к столбцу A, B1: B20 относится к столбцу B, а C1: C20 относится к столбцу C. Формула подсчитывает количество строк на основе нескольких критериев (A1, B2 и C1). .
Затем нажмите кнопку «Форматировать», чтобы выбрать стиль форматирования, и нажмите «ОК».
Теперь Excel выделяет только повторяющиеся строки, как показано ниже.
Вот и все.