Проверка в ексель повторяющихся ячеек

Выделение дубликатов цветом

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

Хотелось бы видеть эти повторы явно, т.е. подсветить дублирующие ячейки цветом, например так: 

Способ 1. Если у вас Excel 2007 или новее

В последних версиях Excel начиная с 2007 года функция подсветки проверка в ексель повторяющихся ячеек является стандартной.

Выделяем все ячейки с данными и на вкладке Главная (Home) жмем кнопку Условное форматирование (Conditional Formatting), затем выбираем Правила выделения ячеек - Повторяющиеся значения (Highlight Cell Rules - Duplicate Values):

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

Способ 2.

Если у вас Excel 2003 и старше

В более древних версиях Excel придется чуточку сложнее. Выделяем весь список (в нашем примере - диапазон А2:A10), и идем в меню Формат - Условное форматирование (Format - Conditional Formatting). Выбираем из выпадающего списка вариант условия Формула (Formula) и вводим такую проверку:

=СЧЁТЕСЛИ($A:$A;A2)>1

в английском Excel это будет соответственно =COUNTIF($A:$A;A2)>1

Эта простая функция ищет сколько раз содержимое текущей ячейки встречается в столбце А.

проверка в ексель повторяющихся ячеек

Если это количество повторений больше 1, т.е. у элемента есть дубликаты, то срабатывает заливка ячейки. Для выбора цвета выделения в окне Условное форматирование нажмите кнопку Формат. (Format) и перейдите на вкладку Вид (Pattern).

Способ 3.

Если много столбцов

Усложним задачу. Допустим, нам нужно искать и подсвечивать повторы не по одному столбцу, а по нескольким. Например, имеется вот такая таблица с ФИО в трех колонках:

Задача все та же проверка в ексель повторяющихся ячеек подсветить совпадающие ФИО, имея ввиду совпадение сразу по всем трем столбцам - имени, фамилии и отчества одновременно.

Самым простым решением будет добавить дополнительный служебный столбец (его потом можно скрыть) с текстовой функцией СЦЕПИТЬ (CONCATENATE), проверка в ексель повторяющихся ячеек собрать ФИО в одну ячейку:

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

Для выделения совпадающих ФИО теперь можно выделить все три столбца проверка в ексель повторяющихся ячеек данными и создать новое правило форматирования, аналогичное Способу 2. А именно:

  • в Excel 2003 и старше - выбрать в меню Формат - Условное форматирование - Формула (Format - Conditional Formatting - Formula)
  • в Excel 2007 и новее - нажать на вкладке Главная (Home)кнопку Условное форматирование - Создать правило (Conditional Formatting - New Rule) и выбрать тип правила Использовать формулу для опеределения форматируемых ячеек (Use a formula to determine which cell to format)

Затем ввести формулу проверки количества совпадений и задать цвет с помощью кнопки Формат (Format) - все, как в Способе 2:

Ссылки по теме

Источник: https://www.planetaexcel.ru/techniques/9/69/

проверка в ексель повторяющихся ячеек