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

Допустим, что у нас имеется длинный список чего-либо (например, товаров), и мы предполагаем, что некоторые элементы этого списка повторяются более 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

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

 


Не задан ID пользователя.

07.02.2013 22:21:10
А как быть, если идентичные дубликаты нужно сгруппировать? То есть Медведевых выделить одним цветом, а Ермаковых другим? Этот пример хорош тем, что он небольшой и всё видно наглядно, а если в таблице более тысячи строк, то на глаз прикинуть несколько десятков идентичных дубликатов и выделить нужные не так-то просто. Есть ли какое-то решение?
08.02.2013 01:11:48
Я бы отфильтровал дубликаты по цвету, а потом отсортировал - получите на экране только повторяющиеся элементы сгруппированные "по одинаковости".
Не задан ID пользователя.

02.07.2015 09:41:54
Николай, нашёл дубликаты по способу номер 1 (Excel 2007) из двух колонок. Так получилось, что правая колонка состоит из 2000 ячеек, и мне нужно было найти эти 2000 дубликатов в левой колонке состоящей из 27000 ячеек. Нашёл, обозначил цветом. Теперь нажимаю на фильтр над колонкой из 27000 ячеек (чтобы собрать воедино цветные ячейки) и у меня даже не вылазиет контекстное меню для выбора критериев фильтрации - Excel начинает в прямом смысле слова тупить, пока не нажмёшь клавишу <Esc>. Вот такая проблема.
Не задан ID пользователя.

02.07.2015 13:33:57
Нашёл ответ сам. Нужно было всего лишь подождать, поскольку столбец длиный он долго собирал в контекстное меню параметры фиольтрации. Спасибо. Всё работает. :)
18.05.2014 00:49:46
Николай, вот сделал специально для такой задачи макрос. :)
Не задан ID пользователя.

18.05.2014 12:07:39
Спасибо огромное, Николай! Очень нужный макрос. Экономит кучу времени, которое можно тратить на велопрогулки. :)
Как Вы всё успеваете, даже ума не приложу! И надстройку допилить, и книгу написать, и работа, и дела домашние делать. ;)
Книгу жду с нетерпением (шпаргалка с приёмами и горячими клавишами прочно обосновалась на столе в течение нескольких месяцев). :)

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

Виртуально жму Вам руку и желаю всяческих успехов. ;)
Не задан ID пользователя.

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

нужно оставить только одну строку с ООО Фарм, а строки Аптека 1 (ООО Фарм)
и Аптека 2 (ООО Фарм) удалить (выделить...)
09.03.2013 08:16:56
Елена, посмотрите статью про удаление дубликатов
Не задан ID пользователя.

16.07.2013 12:34:25
  1. Добрый день! Подскажите, а если мне нужно найденные одинаковые дубликаты просуммировать в месте и  в отдельный столбец уже вывести результаты без дубликатов. Как это можно правильно реализовать?
09.10.2013 09:36:20
Александр, вам нужны сводные таблицы
Не задан ID пользователя.

02.11.2013 14:39:37
Добрый день! Можете подсказать как выделить цветом дубликаты, даные которые находятся на нескольких страницах.
Не задан ID пользователя.

15.12.2013 16:28:31
Доброго времени суток! :) Вам, Есть надобность выделить белым цветом шрифт у каждого следующеего дубликата в ячейках столбца. Т.е. первое вхождение оставить ка есть, а во всех последующих повторах шрифт белый. При условии повторы считать только подряд до нового не равного предыдущуму значению. Потом, в столбце может быть встречено такое же вхождение как и первое.
Не задан ID пользователя.

24.12.2013 18:23:12
Решение найдено! В условном форматировании, на основе формулы создать правило и ввести формулу =СЧЁТЕСЛИ($AH$7:$AH7;AH7)>1 , адаптируйте для своих значений. Меняет формат каждого следующего повтора, крорме первого.
Не задан ID пользователя.

25.12.2013 20:19:03
Добрый вечер!
Подскажите, а как выделить цветом ВСЮ СТРОКУ?
Есть большая таблица данных, в которой например 8-й столбец содержит названия городов. Нужно выделить  цветом всю строку таблицы данных, если это, предположим, Москва или С-Петербург.
Заранее спасибо.
04.01.2014 12:23:16
Выделить всю таблицу, открыть Главная - Условное форматирование - Создать правило - Использовать формулу и ввести примерно так:

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

Предполагаю, что города у вас в столбце D и D2 - это первая ячейка с городом в этом столбце после шапки.
Не забудьте задать цвет, нажав кнопку Формат.
Не задан ID пользователя.

04.01.2014 15:39:25
Николай!
Большое Вам спасибо за помощь с моим вопросом и за замечательный сайт!!
Успехов в Новом Году!!!
Не задан ID пользователя.

25.02.2014 08:59:45
Подскажите пожалуйста , почему могут не выделяться дубликаты при условном форматировании? Значения ячеек абсолютно одинаковые?
26.02.2014 07:01:27
А они точно дубликаты? Пробелов или русская-эс-вместо-английская-си там точно нет?
Не задан ID пользователя.

26.02.2014 14:41:44
да Николай , даже бывает копируешь содержимое ячейки , а строка всё равно не выделяется.
Не задан ID пользователя.

10.08.2015 01:00:51
Здравствуйте. удалось ди вам найти решение данной проблемы? то же самое дубликаты не выделяются, мне кажется  если они созданы в разных программах. Пыталась объединить два файла в один копированием, все равно не выделяются.
Не задан ID пользователя.

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.
Не задан ID пользователя.

13.06.2014 15:24:03
Подскажите, пожалуйста, как можно реализовать вот такое действие: "Когда данные при вводе в ячейку ФИО повторяются, в соседней ячейке писалось "Повторно", а уникальные - "Впервые"??????
Спасибо!!!
14.06.2014 10:31:20
Проверять формулой СЧЁТЕСЛИ не было ли раньше уже введенного значения. Затем при помощи функции ЕСЛИ выводить "повторно" или "впервые" в зависимости от результатов проверки. Точнее сказать не могу не видя вашего файла.
Не задан ID пользователя.

14.06.2014 14:19:46
Спасибо большое, Николай!!! Я написала через функцию ЕСЛИ, вот какая формула получилась: =ЕСЛИ(СЧЁТЕСЛИ(E$1:E2;E2)>1;"Повторно";"Впервые").
А я хотела попробовать через макрос, например как у вас тут на сайте приведен пример с автоматической вставкой ДАТЫ в соседнюю ячейку, я хотела так же и тут попробовать, ну ничего не вышло. Лучше же конечно, чтобы всё автоматически работало, а формулу надо постоянно копировать вниз.
Не задан ID пользователя.

15.10.2014 04:17:26
Добрый день! Скажите, а возможен поиск не 100%-ых дубликатов, а например 60% (и можно-ли менять этот показатель на 70-80-90% и т.д.)?

Проблема в том, что у меня 5 списков наименований (~10.000 каждый), которые создавались пятью разными людьми. Эти 5 человек по разному описывали один и тот-же товар
Пример:
- молоко
- "молоко"
- молоко.
- молако
- малако
Мне нужно соединить эти 5 списков в одну базу и найти дубликаты, но так как совпадение в моём случае не 100%, то и выделения тоже не происходит. Есть ли какое-то решение?
Не задан ID пользователя.

02.04.2015 13:03:06
Вот здесь описано возможное решение: Нечеткое сравнение строк
Не задан ID пользователя.

07.04.2015 20:57:15
странно, у меня почему-то вариант с использованием функции "сцепить" не работает



самое интересное то, что пример я скачал, удаляю правила из ячеек, затем снова добавляю - и не работает.
09.04.2015 09:11:07
Руслан, посмотрите внимательно на доллары в адресах ячеек в моем примере и у вас - закрепление ссылок должно быть по-другому.
Не задан ID пользователя.

12.04.2015 10:50:34
Да, спасибо, разобрался
Не задан ID пользователя.

20.05.2015 14:13:31
Добрый день!
Задача из той же серии: есть 2 столбца со значениями. Мне необходимо выделить значения, которые повторяются в двух столбцах, при этом в первом столбце также есть одинаковые значения, соответственно их выделять не нужно.
20.05.2015 14:48:03
А если сначала удалить дубликаты в первом столбце, а потом применить способ из статьи?
Не задан ID пользователя.

20.05.2015 14:57:19
Дело в том что некоторые дубликаты в первом столбце могут попадаться и во втором. А задача состоит именно в том, чтобы найти повторения в двух столбцах(значений в каждом столбце по несколько тысяч).
Не задан ID пользователя.

20.05.2015 14:59:55
Поэтому если изначально исключить дубли в первом столбце, то после этого мы не сможем их отследить во втором.
10.06.2015 13:59:38
А есть возможность подсветка дублирующихся ячеек через формулу?*
11.06.2015 09:20:48
В смысле "через формулу"? Как формула может изменять цвет ячеек?
Не задан ID пользователя.

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)
Спасибо за сайт
Не задан ID пользователя.

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), либо чисел в текстовый формат.
Не задан ID пользователя.

14.10.2015 11:08:11
Николай, как я и говорила ранее, я отформатировала ячейки в диапазоне как текст. То есть я преобразовала эти псевдочисла в текстовой формат, разве нет? Что я еще должна была сделать, что бы Excel воспринимал значения 1-02, 6-15, 1-01 и проч. именно как текст?
Не задан ID пользователя.

15.10.2015 10:33:41
Ответа не дождалась, но, думаю, проблема всё-таки в некорретной работе текстового форматирования. Некорректно она работает и в формульной части. Так в справке к функции "Текст" имеем следующее: " Функция ТЕКСТ преобразует число в форматированный текст, и результат больше не может быть использован в вычислениях в качестве числа.".
Простой пример: в ячейку А1 заношу любое числовое значение, например, 402. В ячейку А2 заношу простую формулу, призванную преобразить число в текст, который далее больше нельзя будет использовать при вычислениях: =ТЕКСТ(A1;"0" ) Получаем результат 402, сдвинутый к левому краю ячейки, что вроде бы должно нас убедить, что это теперь текст. Далее в ячейку А3 ввожу формулу: =A2+2 и в результате, вуаля, получаем 407!
Как я уже говорила, такая политика в форматировании появилась пару лет назад и сразу во всех вариантах Офиса. До тех пор текст это был именно текст, как бы он не выглядел. Отсюда и проблемы. Правильно ли я понимаю, что в настоящее время решить эту ситуацию никак нельзя?
Не задан ID пользователя.

09.11.2015 16:23:07
День добрый!
Может кто сталкивался и знает, что с этим делать.
Есть таблица, в одном столбце действует правило выделение дубликатов. Когда в ячейке этого столбца вводишь число, которое точно в этом столбце есть, то ячейка выделяется, т.е. все работает, как и должно.
Но если копируешь строку из этой таблицы, и вставляешь ниже (чтобы не заполнять заново другие ячейки), то правило работать перестает.
Вот так правило выглядит до вставки новой строки -


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



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

12.11.2015 05:51:59
Николай, здравствуйте.

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

Спасибо.


Юрий.  
Не задан ID пользователя.

07.12.2015 09:47:09
Приём у меня отлично работает, но если я для оператора протягиваю на, например, 2000 строк,, то у меня выделяются все пока незаполненные ячейки, т.е. он отмечает пустые ячейки. Как их исключить?
Не задан ID пользователя.

05.01.2016 19:17:04
А есть ли возможность в колонке выделить дубликаты чисел с противоположным знаком?
Не задан ID пользователя.

08.01.2016 13:57:36
Добрый день!
спасибо за мануалы.
есть такой вопрос.
есть столбец с ссылками и столбец с словом YES и NO
фильтрую по слову YES. и теперь здесь нужно найти дубликаты ссылок и дать им значение NO. т.е. только копиям дать значение NO.
прошу помочь с этой проблемой. в файле 30 листов по 2500 строк. по одному нереально каждую проверять.
Не задан ID пользователя.

24.01.2016 15:50:24
Давно ищу ответ на вопрос: как подсветить повторяющиеся данные в списках данных, имеющих одинаковое начало, но разное окончание. Стандартная функция работает в данном случае некорректно. Вот, например, значения 1025500277315000046 и 1025500277315000127 и т.д. (см. скриншот). Начинаются одинаково и excel их высвечивает как одинаковые. В данном случае это реестровые номера контрактов с сайта госзакупок. И мне нужно чтобы excel подсвечивал только уникальные значения, а не частично похожие.

Не задан ID пользователя.

03.03.2016 09:32:21
Здравствуйте, Николай!
Сделал по описанному Вами способу выпадающие списки с накоплением (Фамилия ИО) в расписании занятий (чтобы знать в какое время занят тот или иной человек, на каком занятии, у кого и т.д.). В соседних ячейках время занятий ("время от" и "время до";) Попытался найти способ как выделять цветом одних и тех же людей, занятых в одно и то же время, чтобы случайно не поставить человека на то время, когда он записан на другом занятии, но не смог. Получается, если человек один, но у меня список с накоплением, вот в чем подвох. Когда набирается в списке более одного выделение снимается. Не подскажете, может есть выход?
Не задан ID пользователя.

11.03.2016 07:00:49
странно, у меня не работает способ №3, может дело в том, что я использую умную таблицу, но почему тогда, когда я
прописываю правило в окне "Изменение правила форматирования", нажимаю "ок", и окно не закрывается, убираю знак равно-проходит, но формула в ковычках и не работает.
Не задан ID пользователя.

03.03.2017 08:34:00
Здравствуйте!
Подскажите пожалуйста, можно ли сделать следующее:
Есть книга с расписанием, в ней может быть до 6 листов. Можно ли сделать чтобы выделялись повторяющиеся ячейки на всех листах, для столбца "аудитория" но только для каждой строки отдельно (т.е. выделять если повторяется на любом курсе повтор аудитории например в понедельник 1 пара или вторник 5 пара).
Шаблон:
https://cloud.mail.ru/public/KGek/jPVjxCfXN