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

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

colored_duplicates0.png

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

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

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

colored_duplicates1.png

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

Способ 2. Если у вас Excel 2003 и старше

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

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

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

colored_duplicates2.gif

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

Способ 3. Если много столбцов

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

colored_duplicates4.png

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

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

colored_duplicates3.png

Имея такой столбец мы, фактически, сводим задачу к предыдущему способу. Для выделения совпадающих ФИО теперь можно выделить все три столбца с данными и создать новое правило форматирования, аналогичное Способу 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:

colored_duplicates5.png

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

 


07.02.2013 22:21:10
А как быть, если идентичные дубликаты нужно сгруппировать? То есть Медведевых выделить одним цветом, а Ермаковых другим? Этот пример хорош тем, что он небольшой и всё видно наглядно, а если в таблице более тысячи строк, то на глаз прикинуть несколько десятков идентичных дубликатов и выделить нужные не так-то просто. Есть ли какое-то решение?
08.02.2013 01:11:48
Я бы отфильтровал дубликаты по цвету, а потом отсортировал - получите на экране только повторяющиеся элементы сгруппированные "по одинаковости".
02.07.2015 09:41:54
Николай, нашёл дубликаты по способу номер 1 (Excel 2007) из двух колонок. Так получилось, что правая колонка состоит из 2000 ячеек, и мне нужно было найти эти 2000 дубликатов в левой колонке состоящей из 27000 ячеек. Нашёл, обозначил цветом. Теперь нажимаю на фильтр над колонкой из 27000 ячеек (чтобы собрать воедино цветные ячейки) и у меня даже не вылазиет контекстное меню для выбора критериев фильтрации - Excel начинает в прямом смысле слова тупить, пока не нажмёшь клавишу <Esc>. Вот такая проблема.
02.07.2015 13:33:57
Нашёл ответ сам. Нужно было всего лишь подождать, поскольку столбец длиный он долго собирал в контекстное меню параметры фиольтрации. Спасибо. Всё работает. :)
18.05.2014 00:49:46
Николай, вот сделал специально для такой задачи макрос. :)
18.05.2014 12:07:39
Спасибо огромное, Николай! Очень нужный макрос. Экономит кучу времени, которое можно тратить на велопрогулки. :)
Как Вы всё успеваете, даже ума не приложу! И надстройку допилить, и книгу написать, и работа, и дела домашние делать. ;)
Книгу жду с нетерпением (шпаргалка с приёмами и горячими клавишами прочно обосновалась на столе в течение нескольких месяцев). :)

Вашим успехам радуюсь от души. :) Желаю написать многотомный бестселлер, перевести на несколько языков, создать свою корпорацию, вырастить несколько детей, находясь в гармонии с самим собой. :D

Виртуально жму Вам руку и желаю всяческих успехов. ;)
04.03.2013 12:33:56
Добрый день, подскажите, пожалуйста, возможно ли такое сравнение?
знаяения в одном столбце (можно и разделить, т.е. данные в скобках вынести во второй столбец),  н-р.
Аптека 1 (ООО Фарм)
Аптека 1 (ОАО Годовалов)
Аптека 2 (ООО Фарм)
ООО Фарм

нужно оставить только одну строку с ООО Фарм, а строки Аптека 1 (ООО Фарм)
и Аптека 2 (ООО Фарм) удалить (выделить...)
09.03.2013 08:16:56
Елена, посмотрите статью про удаление дубликатов
16.07.2013 12:34:25
  1. Добрый день! Подскажите, а если мне нужно найденные одинаковые дубликаты просуммировать в месте и  в отдельный столбец уже вывести результаты без дубликатов. Как это можно правильно реализовать?
09.10.2013 09:36:20
Александр, вам нужны сводные таблицы
02.11.2013 14:39:37
Добрый день! Можете подсказать как выделить цветом дубликаты, даные которые находятся на нескольких страницах.
15.12.2013 16:28:31
Доброго времени суток! :) Вам, Есть надобность выделить белым цветом шрифт у каждого следующеего дубликата в ячейках столбца. Т.е. первое вхождение оставить ка есть, а во всех последующих повторах шрифт белый. При условии повторы считать только подряд до нового не равного предыдущуму значению. Потом, в столбце может быть встречено такое же вхождение как и первое.
24.12.2013 18:23:12
Решение найдено! В условном форматировании, на основе формулы создать правило и ввести формулу =СЧЁТЕСЛИ($AH$7:$AH7;AH7)>1 , адаптируйте для своих значений. Меняет формат каждого следующего повтора, крорме первого.
Добрый вечер!
Подскажите, а как выделить цветом ВСЮ СТРОКУ?
Есть большая таблица данных, в которой например 8-й столбец содержит названия городов. Нужно выделить  цветом всю строку таблицы данных, если это, предположим, Москва или С-Петербург.
Заранее спасибо.
04.01.2014 12:23:16
Выделить всю таблицу, открыть Главная - Условное форматирование - Создать правило - Использовать формулу и ввести примерно так:

=$D2="Москва"

Предполагаю, что города у вас в столбце D и D2 - это первая ячейка с городом в этом столбце после шапки.
Не забудьте задать цвет, нажав кнопку Формат.
Николай!
Большое Вам спасибо за помощь с моим вопросом и за замечательный сайт!!
Успехов в Новом Году!!!
25.02.2014 08:59:45
Подскажите пожалуйста , почему могут не выделяться дубликаты при условном форматировании? Значения ячеек абсолютно одинаковые?
26.02.2014 07:01:27
А они точно дубликаты? Пробелов или русская-эс-вместо-английская-си там точно нет?
26.02.2014 14:41:44
да Николай , даже бывает копируешь содержимое ячейки , а строка всё равно не выделяется.
10.08.2015 01:00:51
Здравствуйте. удалось ди вам найти решение данной проблемы? то же самое дубликаты не выделяются, мне кажется  если они созданы в разных программах. Пыталась объединить два файла в один копированием, все равно не выделяются.
05.05.2014 14:59:12
Подскажите, пожалуйста, каким образом можно реализовать следующую выборку.
У меня есть, к примеру 30 столбцов с e-mail адресами
Мне нужно отфильтровать те e-mail'ы, которые встречаются во всех 30 столбцах.
Т.е. если e-mail есть только в одном или двух или 29 столбцах - он не подходит. А если он есть в 30 - это то что нужно.
Спасибо!
14.06.2014 10:34:41
С ходу вариант: сделать рядом с таблицей справа еще одну таблицу из 30 столбцов, где в каждом столбце проверять с помощью функции СЧЁТЕСЛИ наличие в нем определенного email (будет либо 0, либо 1).  Затем суммировать все единицы еще в одном столбце. Там где сумма =30 - ваш email.
13.06.2014 15:24:03
Подскажите, пожалуйста, как можно реализовать вот такое действие: "Когда данные при вводе в ячейку ФИО повторяются, в соседней ячейке писалось "Повторно", а уникальные - "Впервые"??????
Спасибо!!!
14.06.2014 10:31:20
Проверять формулой СЧЁТЕСЛИ не было ли раньше уже введенного значения. Затем при помощи функции ЕСЛИ выводить "повторно" или "впервые" в зависимости от результатов проверки. Точнее сказать не могу не видя вашего файла.
14.06.2014 14:19:46
Спасибо большое, Николай!!! Я написала через функцию ЕСЛИ, вот какая формула получилась: =ЕСЛИ(СЧЁТЕСЛИ(E$1:E2;E2)>1;"Повторно";"Впервые").
А я хотела попробовать через макрос, например как у вас тут на сайте приведен пример с автоматической вставкой ДАТЫ в соседнюю ячейку, я хотела так же и тут попробовать, ну ничего не вышло. Лучше же конечно, чтобы всё автоматически работало, а формулу надо постоянно копировать вниз.
15.10.2014 04:17:26
Добрый день! Скажите, а возможен поиск не 100%-ых дубликатов, а например 60% (и можно-ли менять этот показатель на 70-80-90% и т.д.)?

Проблема в том, что у меня 5 списков наименований (~10.000 каждый), которые создавались пятью разными людьми. Эти 5 человек по разному описывали один и тот-же товар
Пример:
- молоко
- "молоко"
- молоко.
- молако
- малако
Мне нужно соединить эти 5 списков в одну базу и найти дубликаты, но так как совпадение в моём случае не 100%, то и выделения тоже не происходит. Есть ли какое-то решение?
02.04.2015 13:03:06
Вот здесь описано возможное решение: Нечеткое сравнение строк
07.04.2015 20:57:15
странно, у меня почему-то вариант с использованием функции "сцепить" не работает



самое интересное то, что пример я скачал, удаляю правила из ячеек, затем снова добавляю - и не работает.
09.04.2015 09:11:07
Руслан, посмотрите внимательно на доллары в адресах ячеек в моем примере и у вас - закрепление ссылок должно быть по-другому.
12.04.2015 10:50:34
Да, спасибо, разобрался
20.05.2015 14:13:31
Добрый день!
Задача из той же серии: есть 2 столбца со значениями. Мне необходимо выделить значения, которые повторяются в двух столбцах, при этом в первом столбце также есть одинаковые значения, соответственно их выделять не нужно.
20.05.2015 14:48:03
А если сначала удалить дубликаты в первом столбце, а потом применить способ из статьи?
20.05.2015 14:57:19
Дело в том что некоторые дубликаты в первом столбце могут попадаться и во втором. А задача состоит именно в том, чтобы найти повторения в двух столбцах(значений в каждом столбце по несколько тысяч).
20.05.2015 14:59:55
Поэтому если изначально исключить дубли в первом столбце, то после этого мы не сможем их отследить во втором.
10.06.2015 13:59:38
А есть возможность подсветка дублирующихся ячеек через формулу?*
11.06.2015 09:20:48
В смысле "через формулу"? Как формула может изменять цвет ячеек?
10.08.2015 14:24:41
Добрый день! Подскажите как установить счетчик повтора значения в ячейке. Например в формуле =ЕСЛИ(СЧЁТЕСЛИ(E$1:E2;E2)>1;"Повторно";"Впервые";) вместо "повторно" указывалось количество повторов (для втрого раза - 2, для третьего повтора -3 и т.д., а вместо "Впервые" - 1).

Разобрался (помог поиск по форуму): =ЕСЛИ(СЧЁТЕСЛИ(A$5:A$16;A5)>1;СЧЁТЕСЛИ(A$5:A5;A5);1)
Спасибо за сайт
14.10.2015 09:08:43
Здравствуйте!
У меня стоит MS Office 2007. Пару лет назад при очередном обновлении Excel возникла проблема с правильным отображением дубликатов некоторых текстовых значений. С тех пор проблема не исчезла и наблюдается во всех последующих версиях Excel (проверяла), хотя до злополучного обновления всё работало нормально. Причем проблема эта наблюдается как в условном форматировании ячеек при попытке выделить повторяющиеся значения, так и в формулах, где идет проверка на совпадение значений. А теперь суть:
Привожу для наглядности пример. Ячейки А1:Е1 В отформатированы как текстовые, кроме того с условным форматированием повторяющихся значений. Четыре ячейки выделены как повторяющиеся. Excel воспринимает как одинаковые пары значений 1-02, 2-15 и 1-01, 1-01. Кроме того приведена формула на поиск соответствия в этом диапазоне текстовому значению 1-15. Формула выдает значение "Истина" (то есть подходящих ячеек в диапазоне как минимум должно быть две), при этом такого значения в диапазоне нет вообще! Причины такого поведения я раскрыла. Они наглядно отражены в таблице (колонки G:H). Колонка G отформатирована как текст, колонка H отформатирована как числа. Значения же в них вводились одинаковые, но в колонке H эти значения преобразовывались в числа, которые для значений 1-01 и 1-15 оказались одинаковыми, что тут же отразилось в поведении условного форматирования. Вот почему и формула выдает Истину, принимая значения 1-01 в строке равной значению 1-15.
Но ведь это неправильное поведение! Можно ли как-то решить эту проблему?
14.10.2015 09:54:55
Юлия, "числа-как-текст" и "числа-как-числа" - это две большие разницы в Excel (у них разные внутренние коды). Чтобы корректно их сравнивать, нужно либо преобразование псевдочисел в числа (это можно сделать, например, с помощью макроса в PLEX), либо чисел в текстовый формат.
14.10.2015 11:08:11
Николай, как я и говорила ранее, я отформатировала ячейки в диапазоне как текст. То есть я преобразовала эти псевдочисла в текстовой формат, разве нет? Что я еще должна была сделать, что бы Excel воспринимал значения 1-02, 6-15, 1-01 и проч. именно как текст?
15.10.2015 10:33:41
Ответа не дождалась, но, думаю, проблема всё-таки в некорретной работе текстового форматирования. Некорректно она работает и в формульной части. Так в справке к функции "Текст" имеем следующее: " Функция ТЕКСТ преобразует число в форматированный текст, и результат больше не может быть использован в вычислениях в качестве числа.".
Простой пример: в ячейку А1 заношу любое числовое значение, например, 402. В ячейку А2 заношу простую формулу, призванную преобразить число в текст, который далее больше нельзя будет использовать при вычислениях: =ТЕКСТ(A1;"0" ) Получаем результат 402, сдвинутый к левому краю ячейки, что вроде бы должно нас убедить, что это теперь текст. Далее в ячейку А3 ввожу формулу: =A2+2 и в результате, вуаля, получаем 407!
Как я уже говорила, такая политика в форматировании появилась пару лет назад и сразу во всех вариантах Офиса. До тех пор текст это был именно текст, как бы он не выглядел. Отсюда и проблемы. Правильно ли я понимаю, что в настоящее время решить эту ситуацию никак нельзя?
09.11.2015 16:23:07
День добрый!
Может кто сталкивался и знает, что с этим делать.
Есть таблица, в одном столбце действует правило выделение дубликатов. Когда в ячейке этого столбца вводишь число, которое точно в этом столбце есть, то ячейка выделяется, т.е. все работает, как и должно.
Но если копируешь строку из этой таблицы, и вставляешь ниже (чтобы не заполнять заново другие ячейки), то правило работать перестает.
Вот так правило выглядит до вставки новой строки -


А вот как правило начинает выглядеть после вставки строки -



Я так понимаю, что почему-то правило начинает ограничиваться новой строкой, но почему? И если правило применяется до 940 строки, а новая строка как раз имеет этот номер, то почему оно в ней не меняет цвет, хоть там и дубль?
Если просто вставить пустую строку, то все ок, правило не меняется.  
12.11.2015 05:51:59
Николай, здравствуйте.

Подскажите пожалуйста, такой момент.
В таблице 60 000 строк текстовых фраз, среди которых есть дубликаты. В соседнем столбце через формулу СЧЁТЕСЛИ вывожу сколько раз каждая фраза встречается. Для первой строки проблем никаких нет, формула цифру выдаёт. А вот когда я растягиваю формулу до конца таблицы, то вначале таблицы она ещё срабатывает, то есть выдаёт верную цифру, а в дальнейшем (начиная с середины таблицы и до конца) показывает одну и туже цифру. То есть складывается ощущение, что ексель не справляется с расчетом и выдаёт некую цифру. Что самое интересное, если по этим одинаковым цифрам ещё раз растянуть формулу, то цифры обновятся на корректные.
Для информации: вычисление стоит автоматически, ошибок в формуле нет (перепроверил эти моменты по 100 раз).

Спасибо.


Юрий.  
07.12.2015 09:47:09
Приём у меня отлично работает, но если я для оператора протягиваю на, например, 2000 строк,, то у меня выделяются все пока незаполненные ячейки, т.е. он отмечает пустые ячейки. Как их исключить?
05.01.2016 19:17:04
А есть ли возможность в колонке выделить дубликаты чисел с противоположным знаком?
08.01.2016 13:57:36
Добрый день!
спасибо за мануалы.
есть такой вопрос.
есть столбец с ссылками и столбец с словом YES и NO
фильтрую по слову YES. и теперь здесь нужно найти дубликаты ссылок и дать им значение NO. т.е. только копиям дать значение NO.
прошу помочь с этой проблемой. в файле 30 листов по 2500 строк. по одному нереально каждую проверять.
24.01.2016 15:50:24
Давно ищу ответ на вопрос: как подсветить повторяющиеся данные в списках данных, имеющих одинаковое начало, но разное окончание. Стандартная функция работает в данном случае некорректно. Вот, например, значения 1025500277315000046 и 1025500277315000127 и т.д. (см. скриншот). Начинаются одинаково и excel их высвечивает как одинаковые. В данном случае это реестровые номера контрактов с сайта госзакупок. И мне нужно чтобы excel подсвечивал только уникальные значения, а не частично похожие.

Здравствуйте, Николай!
Сделал по описанному Вами способу выпадающие списки с накоплением (Фамилия ИО) в расписании занятий (чтобы знать в какое время занят тот или иной человек, на каком занятии, у кого и т.д.). В соседних ячейках время занятий ("время от" и "время до";) Попытался найти способ как выделять цветом одних и тех же людей, занятых в одно и то же время, чтобы случайно не поставить человека на то время, когда он записан на другом занятии, но не смог. Получается, если человек один, но у меня список с накоплением, вот в чем подвох. Когда набирается в списке более одного выделение снимается. Не подскажете, может есть выход?
11.03.2016 07:00:49
странно, у меня не работает способ №3, может дело в том, что я использую умную таблицу, но почему тогда, когда я
прописываю правило в окне "Изменение правила форматирования", нажимаю "ок", и окно не закрывается, убираю знак равно-проходит, но формула в ковычках и не работает.
03.03.2017 08:34:00
Здравствуйте!
Подскажите пожалуйста, можно ли сделать следующее:
Есть книга с расписанием, в ней может быть до 6 листов. Можно ли сделать чтобы выделялись повторяющиеся ячейки на всех листах, для столбца "аудитория" но только для каждой строки отдельно (т.е. выделять если повторяется на любом курсе повтор аудитории например в понедельник 1 пара или вторник 5 пара).
Шаблон:
https://cloud.mail.ru/public/KGek/jPVjxCfXN
Добрый день!
а можно ли вывести дубликаты (Фамилия и имя), если они в одном столбце, но не в отдельных ячейках, а в одной ячейке указано много фамилий сразу и таких ячеек около 40?
Скриншот как столбца http://prntscr.com/gew5wl
31.08.2017 12:30:18
Ох, ужас какой :) А на выходе вам нужно что получить? Список всех людей без повторов? Или понимать, кто участвует больше чем в одной конференции?
03.10.2017 13:06:34
Специально зарегистрировалась на вашем сайте, уж очень полезный.
скажите, пожалуйста, а есть ли возможность сделать так. Например, идут значения в столбце (номера лотов), и чтобы при последующем введении лота Excel как нибудь бы сообщал, что такой лот уже существует. А если бы еще и отсылал на строку с таким номером , то вообще сказка.))

Условное форматирование в принципе работает, то есть при введении существующего лота, excel сразу же закрашивает его цветом.
Но хотелось бы узнать есть ли еще какие то способы?
05.10.2017 21:36:31
Ну, можно, например, проверять функцией ЕСЛИ встречались ли уже ранее такие значения (подсчитывать повторы с помощью СЧЁТЕСЛИ) и выводить сообщение.
19.11.2017 21:09:50
Возможно ли  данный код переделать только для цвета шрифта, чтобы не перегружать цветами рабочий лист?
23.01.2018 08:41:06
Добрый день!

Не могу понять почему знак $ перед буквой ячейки выделяет цветом все три ячейки ФИО, а если без $ то только ячейки в первом столбце?

Нашел:)
05.02.2018 00:18:47
Всем привет,
Помогите пожалуйста, мне преподаватель на экзамене по математической статистике, задал практическую работу, я все выходные просидел, ничего не выходит, скоро сдавать, а фрилансер который согласился помочь, не отвечает(.
У меня есть файл с расписанием института, и бывает такая ситуация что преподаватели встречаются в аудиториях, так как у них одинаковые кабинеты, и в деканате при составлении расписания не могут всё учесть так как оно большое и приходиться преподавателям когда выходит расписание сидеть и просматривать, что очень долго и муторно по каждому пункту. Вот мне и преподаватель задал задачку, что бы сделать что то такое, что бы в строке , если повторяется две разные фамилии, но с одним кабинетом, подсвечивались эти два человека с одним кабинетом красным цветом, что бы в деканате видели, что у этих разных преподов, один кабинет в строке, и видели что их нужно разминуть, назначить им два разных кабинета. После того как им поменять кабинеты, красный цыет должен пропадать, и таким образом красный сигнализирует о том в строке что у этих преподавателей один кабинет которым нужно поменять кабинет чтоб они не встретились.

Мой преподаватель думал что это видимо просто, но это очень не просто(. Не хочу долг получить по предмету, вырачайте...

Вот файл

файл

если выручите, можете связь держать по почте harenko.ilya@gmail.com
05.02.2018 09:31:29
Илья, с такими запросами вам на Форум в ветку Работа
05.02.2018 09:39:10
Ок, спасибо!