Трюк №10. Поиск повторяющихся данных при помощи условного форматирования

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

Возьмите, например, таблицу данных в диапазоне $А$1:$Н$100, Щелкните левую верхнюю ячейку, А1, и, удерживая кнопку мыши, перетащите указатель вниз на Н100. Важно, чтобы при выделении активной ячейкой была А1, поэтому перетаскивание указателя от Н100 до А1 не будет идентично описанному действию. Выберите команду Формат → Условное форматирование (Format → Conditional Formatting) и в диалоговом окне Условное форматирование (Conditional Formatting) в левом верхнем раскрывающемся списке выберите пункт Формула (Formula Is). В поле справа введите следующий код: =COUNTIF($A$1:$H$100;A1)>1, и в русской версии Excel =СЧЁТЕСЛИ($А$1;$Н$100;А1)>1.

Перейдите на вкладку Format (Формат) (в Mac OS X нужно щелкнуть кнопку Format), затем на вкладку Вид (Patterns) и выберите цвет, которым будут выделяться повторяющиеся данные. Щелкните на кнопке ОК, чтобы вернуться в диалоговое окно Условное форматирование (Conditional Formatting) и еще раз щелкните на кнопке ОК, чтобы применить форматирование. Теперь ячейки, содержащие повторяющиеся данные, должны быть выделены выбранным цветом, что упрощает поиск, удаление, перемещение или другое изменение подобных данных.

Необходимо помнить, что при выделении ячеек активной должна быть ячейка А1, так как адрес ячейки — это относительная, а не абсолютная ссылка, так же, как и ваша таблица данных, $А$1:$Н$100. Используя условное форматирование подобным образом, вы даете Excel знать, какую ячейку необходимо использовать как условие для функции СЧЁТЕСЛИ (COUNTIF). То есть формула условного форматирования в ячейке А1 выглядит как =COUNTIF($A$1:$H$100;A1)>1, в русской версии Excel =СЧЁТЕСЛИ($А$1:$Н$100;А1>1 тогда как в ячейке А2 она такая =COUNTIF($A$1:$H$100;A2)>1, в русской версии Excel =СЧЁТЕСЛИ($А$1:$Н$100;А2)>1, а в ячейке A3 =COUNTIF($A$1:$H$100;A3)>1, в русской версии Excel =СЧЁТЕСЛИ($А$1:$Н$100;АЗ)>1 и так далее.

Если необходимо увидеть данные, повторяющиеся несколько раз, можно использовать условное форматирование с тремя различными условиями и разными цветовыми кодами для упрощения визуальной идентификации. Для этого выберите ячейку А1 (это ячейка в левом верхнем углу таблицы) и протащите указатель к ячейке Н100. И снова важно, чтобы при выделении активной была ячейка А1. Теперь выберите команду Формат → Условное форматирование (Format → Conditional Formatting) и в левом текстовом поле условий вместо пункта Значение (Cell Value Is) выберите пункт Формула (Formula Is). В поле справа от поля Формула (Formula Is) введите следующий код: =COUNTIF($A$1:$H$100;A1)>3, в русской версии Excel =СЧЁТЕСЛИ($А$1:$Н$100;А1)>3.

Щелкните кнопку Формат (Format), перейдите на вкладку Вид (Patterns) и выберите цвет, который будет идентифицировать данные, встречающиеся более трех раз. Щелкните на кнопке ОК, затем на кнопке А также (Add). В группе Условие 2 (Condition 2) выберите пункт Формула (Formula Is) и введите следующую формулу: =COUNTIF(SA$l:$H$100;A1)-3, в русской версии Excel =СЧЁТЕСЛИ($А$1:$Н$100;А1)-3.
[stextbox id=»warning»]Чтобы не вводить формулу заново, выделите ее в поле Условие 1 (Condition 1), нажмите сочетание клавиш Ctrl/Apple+C, чтобы скопировать ее, щелкните поле Формула (Formula) в группе Условие 2 (Condition 2), нажмите сочетание клавиш Ctrl/Apple+V, чтобы вставить ее, и измените условие «>3» на «=3».[/stextbox]
Щелкните на кнопке Формат (Format), а затем перейдите на вкладку Вид (Patterns) и выберите цвет, который будет идентифицировать данные, появляющиеся три раза. Щелкните на кнопке ОК, затем на кнопке А также (Add). В группе Условие 3 (Condition 3) выберите пункт Формула (Formula Is) и введите следующую формулу: =COUNTIF($A$l:$H$100;A1)-2, в русской версии Excel =СЧЁТЕСЛИ($А$1:$Н$100;А1)-2. Наконец, щелкните кнопку Формат (Format) и перейдите на вкладку Вид (Patterns). Выберите цвет для данных, появляющихся два раза. Щелкните на кнопке ОК. Теперь цвета ячеек будут различаться в зависимости от того, сколько раз данные появились в таблице.

И снова необходимо помнить, что при выделении ячеек активной должна быть ячейка А1, так как адрес ячейки — это относительная, а не абсолютная ссылка, так же, как и ваша таблица данных, $А$1:$Н$100. Используя условное форматирование подобным образом, вы даете Excel знать, какую ячейку необходимо использовать как условие для функции СЧЁТЕСЛИ (COUNTIF).