Выделение дубликатов цветом
- Простите, вы не видели тут моего близнеца?
- Вы уже спрашивали.
Допустим, что у нас имеется длинный список чего-либо и мы предполагаем, что некоторые элементы этого списка повторяются более 1 раза. Хотелось бы видеть эти повторы явно, т.е. подсветить дубликаты цветом. Сделать это в Excel можно несколькими разными способами.
Способ 1. Повторяющиеся ячейки
Выделяем все ячейки с данными и на вкладке Главная (Home) жмем кнопку Условное форматирование (Conditional Formatting), затем выбираем Правила выделения ячеек - Повторяющиеся значения (Highlight Cell Rules - Duplicate Values):
В появившемся затем окне можно задать желаемое форматирование (заливку, цвет шрифта и т.д.)
Способ 2. Выделение всей строки
Если хочется выделить цветом не одиночные ячейки, а сразу строки целиком, то придется создавать правило условного форматирования с формулой. Для этого выделяем все данные в таблице и выбираем Главная - Условное форматирование - Создать правило - Использовать формулу для выделения форматируемых ячеек (Home - Conditional formatting - Create rule - Use a formula to determine which cells to format), а затем вводим формулу:
=СЧЁТЕСЛИ($A$2:$A$20;$A2)>1
=COUNTIF($A$2:$A$20;$A2)>1
где
- $A$2:$A$20 - столбец в данных, в котором мы проверяем уникальность
- $A2 - ссылка на первую ячейку столбца
Способ 3. Нет ключевого столбца
Усложним задачу. Допустим, нам нужно искать и подсвечивать повторы не по одному столбцу, а по нескольким. Например, имеется вот такая таблица с ФИО в трех колонках:
Задача все та же - подсветить совпадающие ФИО, имея ввиду совпадение сразу по всем трем столбцам - имени, фамилии и отчества одновременно.
Самым простым решением будет, конечно, добавить дополнительный служебный столбец (его потом можно скрыть) с текстовой функцией СЦЕПИТЬ (CONCATENATE), чтобы собрать ФИО в одну ячейку:
Имея такой столбец мы, фактически, сводим задачу к предыдущему способу.
Если же хочется всё решить без дополнительного столбца, то формула для условного форматирования будет посложнее:
Ссылки по теме
- Сравнение двух диапазонов данных, поиск различий и совпадений
- Извлечение уникальных элементов из диапазона
Как Вы всё успеваете, даже ума не приложу! И надстройку допилить, и книгу написать, и работа, и дела домашние делать.
Книгу жду с нетерпением (шпаргалка с приёмами и горячими клавишами прочно обосновалась на столе в течение нескольких месяцев).
Вашим успехам радуюсь от души.
Виртуально жму Вам руку и желаю всяческих успехов.
Нельзя ли его немного оттюнинговать, чтобы на слишком темной заливке менялся бы цвет надписи(хотя бы)?
А то если заливает тёмно-синим, то черный шрифт значения в ячейке не видать совсем.
И ваще, хорошо бы заливать не стандартными цветами(они жёсткие - по "глазам бьют" ), а более мягкими из палитры themes/web.
Спасибо.
знаяения в одном столбце (можно и разделить, т.е. данные в скобках вынести во второй столбец), н-р.
Аптека 1 (ООО Фарм)
Аптека 1 (ОАО Годовалов)
Аптека 2 (ООО Фарм)
ООО Фарм
нужно оставить только одну строку с ООО Фарм, а строки Аптека 1 (ООО Фарм)
и Аптека 2 (ООО Фарм) удалить (выделить...)
Подскажите, а как выделить цветом ВСЮ СТРОКУ?
Есть большая таблица данных, в которой например 8-й столбец содержит названия городов. Нужно выделить цветом всю строку таблицы данных, если это, предположим, Москва или С-Петербург.
Заранее спасибо.
Предполагаю, что города у вас в столбце D и D2 - это первая ячейка с городом в этом столбце после шапки.
Не забудьте задать цвет, нажав кнопку Формат.
Большое Вам спасибо за помощь с моим вопросом и за замечательный сайт!!
Успехов в Новом Году!!!
У меня есть, к примеру 30 столбцов с e-mail адресами
Мне нужно отфильтровать те e-mail'ы, которые встречаются во всех 30 столбцах.
Т.е. если e-mail есть только в одном или двух или 29 столбцах - он не подходит. А если он есть в 30 - это то что нужно.
Спасибо!
Спасибо!!!
А я хотела попробовать через макрос, например как у вас тут на сайте приведен пример с автоматической вставкой ДАТЫ в соседнюю ячейку, я хотела так же и тут попробовать, ну ничего не вышло. Лучше же конечно, чтобы всё автоматически работало, а формулу надо постоянно копировать вниз.
Проблема в том, что у меня 5 списков наименований (~10.000 каждый), которые создавались пятью разными людьми. Эти 5 человек по разному описывали один и тот-же товар
Пример:
- молоко
- "молоко"
- молоко.
- молако
- малако
Мне нужно соединить эти 5 списков в одну базу и найти дубликаты, но так как совпадение в моём случае не 100%, то и выделения тоже не происходит. Есть ли какое-то решение?
самое интересное то, что пример я скачал, удаляю правила из ячеек, затем снова добавляю - и не работает.
Задача из той же серии: есть 2 столбца со значениями. Мне необходимо выделить значения, которые повторяются в двух столбцах, при этом в первом столбце также есть одинаковые значения, соответственно их выделять не нужно.
Разобрался (помог поиск по форуму): =ЕСЛИ(СЧЁТЕСЛИ(A$5:A$16;A5)>1;СЧЁТЕСЛИ(A$5:A5;A5);1)
Спасибо за сайт
У меня стоит 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.
Но ведь это неправильное поведение! Можно ли как-то решить эту проблему?
Простой пример: в ячейку А1 заношу любое числовое значение, например, 402. В ячейку А2 заношу простую формулу, призванную преобразить число в текст, который далее больше нельзя будет использовать при вычислениях: =ТЕКСТ(A1;"0" ) Получаем результат 402, сдвинутый к левому краю ячейки, что вроде бы должно нас убедить, что это теперь текст. Далее в ячейку А3 ввожу формулу: =A2+2 и в результате, вуаля, получаем 407!
Как я уже говорила, такая политика в форматировании появилась пару лет назад и сразу во всех вариантах Офиса. До тех пор текст это был именно текст, как бы он не выглядел. Отсюда и проблемы. Правильно ли я понимаю, что в настоящее время решить эту ситуацию никак нельзя?
Может кто сталкивался и знает, что с этим делать.
Есть таблица, в одном столбце действует правило выделение дубликатов. Когда в ячейке этого столбца вводишь число, которое точно в этом столбце есть, то ячейка выделяется, т.е. все работает, как и должно.
Но если копируешь строку из этой таблицы, и вставляешь ниже (чтобы не заполнять заново другие ячейки), то правило работать перестает.
Вот так правило выглядит до вставки новой строки -
А вот как правило начинает выглядеть после вставки строки -
Я так понимаю, что почему-то правило начинает ограничиваться новой строкой, но почему? И если правило применяется до 940 строки, а новая строка как раз имеет этот номер, то почему оно в ней не меняет цвет, хоть там и дубль?
Если просто вставить пустую строку, то все ок, правило не меняется.
Подскажите пожалуйста, такой момент.
В таблице 60 000 строк текстовых фраз, среди которых есть дубликаты. В соседнем столбце через формулу СЧЁТЕСЛИ вывожу сколько раз каждая фраза встречается. Для первой строки проблем никаких нет, формула цифру выдаёт. А вот когда я растягиваю формулу до конца таблицы, то вначале таблицы она ещё срабатывает, то есть выдаёт верную цифру, а в дальнейшем (начиная с середины таблицы и до конца) показывает одну и туже цифру. То есть складывается ощущение, что ексель не справляется с расчетом и выдаёт некую цифру. Что самое интересное, если по этим одинаковым цифрам ещё раз растянуть формулу, то цифры обновятся на корректные.
Для информации: вычисление стоит автоматически, ошибок в формуле нет (перепроверил эти моменты по 100 раз).
Спасибо.
Юрий.
спасибо за мануалы.
есть такой вопрос.
есть столбец с ссылками и столбец с словом YES и NO
фильтрую по слову YES. и теперь здесь нужно найти дубликаты ссылок и дать им значение NO. т.е. только копиям дать значение NO.
прошу помочь с этой проблемой. в файле 30 листов по 2500 строк. по одному нереально каждую проверять.
Уперся тоже в эту проблему, поиск дубликатов работает нормально, но только до 15 знака, после 15 знака СЧЁТЕСЛИ "видит" нули и воспринимается значение как совпадение, а это ни есть "истина". Использую формулу =СЧЁТЕСЛИМН($B$5:$B$2723;$B5;$E$5:$E$2723;$E5)>1 в условном форматировании, т.е. поиск ведется по двум условиям (хотя это не так важно сейчас). И, таки да, формат ячейки текстовый, потому как значения могут начинаться с нуля, Excel 2016.
Есть ли решение этой проблемы? На просторах инета предложений с таким случаем не нашел.
У решение вроде есть, но он не отвечает. Да и не правильно это, решение должно быть доступно для всех
Попробуйте конструкцию:
СЧЁТЕСЛИ($A$1:$A$15;"*"&A1)
У меня работает как формула условного форматирования, подсвечивает дубликаты на ICC сим-карт (20 цифр как текст).
Работает пока всё отлично и ошибок пока не заметил! Думал уже решения не найду. Антон, преогромнейшее спасибо за подсказку!
Я надеюсь, что этот приём пригодится не одному мне. Всех форумчан с Новым Годом!!!
Сделал по описанному Вами способу выпадающие списки с накоплением (Фамилия ИО) в расписании занятий (чтобы знать в какое время занят тот или иной человек, на каком занятии, у кого и т.д.). В соседних ячейках время занятий ("время от" и "время до";) Попытался найти способ как выделять цветом одних и тех же людей, занятых в одно и то же время, чтобы случайно не поставить человека на то время, когда он записан на другом занятии, но не смог. Получается, если человек один, но у меня список с накоплением, вот в чем подвох. Когда набирается в списке более одного выделение снимается. Не подскажете, может есть выход?
прописываю правило в окне "Изменение правила форматирования", нажимаю "ок", и окно не закрывается, убираю знак равно-проходит, но формула в ковычках и не работает.
Подскажите пожалуйста, можно ли сделать следующее:
Есть книга с расписанием, в ней может быть до 6 листов. Можно ли сделать чтобы выделялись повторяющиеся ячейки на всех листах, для столбца "аудитория" но только для каждой строки отдельно (т.е. выделять если повторяется на любом курсе повтор аудитории например в понедельник 1 пара или вторник 5 пара).
Шаблон:
а можно ли вывести дубликаты (Фамилия и имя), если они в одном столбце, но не в отдельных ячейках, а в одной ячейке указано много фамилий сразу и таких ячеек около 40?
Скриншот как столбца
скажите, пожалуйста, а есть ли возможность сделать так. Например, идут значения в столбце (номера лотов), и чтобы при последующем введении лота Excel как нибудь бы сообщал, что такой лот уже существует. А если бы еще и отсылал на строку с таким номером , то вообще сказка.))
Условное форматирование в принципе работает, то есть при введении существующего лота, excel сразу же закрашивает его цветом.
Но хотелось бы узнать есть ли еще какие то способы?
Не могу понять почему знак $ перед буквой ячейки выделяет цветом все три ячейки ФИО, а если без $ то только ячейки в первом столбце?
Нашел:)
Помогите пожалуйста, мне преподаватель на экзамене по математической статистике, задал практическую работу, я все выходные просидел, ничего не выходит, скоро сдавать, а фрилансер который согласился помочь, не отвечает(.
У меня есть файл с расписанием института, и бывает такая ситуация что преподаватели встречаются в аудиториях, так как у них одинаковые кабинеты, и в деканате при составлении расписания не могут всё учесть так как оно большое и приходиться преподавателям когда выходит расписание сидеть и просматривать, что очень долго и муторно по каждому пункту. Вот мне и преподаватель задал задачку, что бы сделать что то такое, что бы в строке , если повторяется две разные фамилии, но с одним кабинетом, подсвечивались эти два человека с одним кабинетом красным цветом, что бы в деканате видели, что у этих разных преподов, один кабинет в строке, и видели что их нужно разминуть, назначить им два разных кабинета. После того как им поменять кабинеты, красный цыет должен пропадать, и таким образом красный сигнализирует о том в строке что у этих преподавателей один кабинет которым нужно поменять кабинет чтоб они не встретились.
Мой преподаватель думал что это видимо просто, но это очень не просто(. Не хочу долг получить по предмету, вырачайте...
Вот файл
если выручите, можете связь держать по почте harenko.ilya@gmail.com
Спасибо, что делитесь этим с нами.
Хочу попросить у вас совета или даже помощи.
Мы создаем таблицы и в ячейках тексты. В этих текстах бывают повторяющиеся предложения или словосочетания. Нам надо найти и выделить (!!! Не удалять!!!) эти предложения или словосочетания. Ну и потом их мануально изменить.
Я не могу составить макрос или правило (кажется макрос будет правильней) для поиска и выделения предложения или скорее лучше будет минимум 3х слов.
P.S. я нашла вот это решение через этот сайт , но этот код выделяет только абзацы, а не предложения в текстах.
Помогите, пожалуйста
Спасибо заранее
В чем может быть проблема?
Огромное спасибо за статью.
Не могу разобраться с возникшей проблемой:
Решил автоматизировать Способ № 3 (без служебного столбца) (с формулой сумм(ч... и т.д.)
В таком виде:
Range("A2:C30").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=СУММ(Ч($A2&$B2&$C2=$A$2:$A$30&$B$2:$B$30&$C$2:$C$30))>1" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority Selection.FormatConditions(1).Interior.Color = 49407 Selection.FormatConditions(1).StopIfTrue = FalseСтоит зайти в редактирование этого правила (а именно в редактирование формулы) и ничего не изменяя - нажать ок/применить форматирование начинает работать.
В чём может быть проблема?
Заранее спасибо
UPD: проблема решилась заменой в формуле макроса СУММ на СУММПРОИЗВ.
За подсказку спасибо .
на форум Планета Excel с решением данной проблемы.
Подскажите, пожалуйста.
Есть нн-ое количество дубликатов. Как можно добавить символ к одному из дублей? Если повторяющихся три - то символы для каждого из дублей не должны повторяться. Для первого дубля - можно оставить как есть.
Дубли в итоге должны перестать быть дублями.
Заранее спасибо за помощь.
Подскажите пожалуйста, стоит задача из 2х столбцов диапазона ячеек ВЫДЕЛЯТЬ ЦВЕТОМ только уникальные четные значения и только уникальные нечетные значения.
В списке присутствуют дублирующие значения в колонке B.
И есть колонка А, которая ведет счет всех значений таким образом , что если есть дубли, то они нумеруются повторно. То есть , если есть дубли в колонке В, то номер в колонке А соответствует колонке В , т.е. повторяется, например:
1 29.07
1 29.07
2 01.08
2 01.08
и тд.
- Формула для счета повторений такая:
=ЕСЛИ(G2="";""; ЕСЛИ(СЧЁТЕСЛИ($G$2:G2;G2)=1;МАКС($F$1:F1 )+1;F1))- Есть формула, которая ВЫДЕЛЯЕТ УНИКАЛЬНЫЕ значения из этого списка:
=ПОИСКПОЗ($F2;$F$2:$F;0)=СТРОКА(F3)-СТРОКА($F$2)- И есть формула, которая ВЫДЕЛЯЕТ ЧЁТНЫЕ И НЕЧЕТНЫЕ значения в этих строках:
=И($F1<>"";ОСТАТ($F1;2)<>1; ) ;=И($F1<>"";ОСТАТ($F1;2)<>0; ) .
Все формулы присутствуют, но как теперь их совместить так, что бы ВЫДЕЛЯЛИСЬ только четные, но уникальные значения и только нечетные, но уникальные значения.
Помогите совместить или же подскажите более простой, но верный способ , как это сделать?
P.S. Данную задачу мне необходимо сделать в гугл таблице.
Помогите найти решение.
Есть 3 столбца с названиями клиентов. Все клиенты повторяются, но сортировка разная.
Как сделать так, что бы если я встал в ячейку Иванов в столбце 3, подсветились ячейки Иванов в столбцах 1 и 2?
Есть ли в excel возможность выделить одинаковые числа с противоположными знаками?