Как в Excel найти повторяющиеся и одинаковые значения
19.11.2020В этом руководстве объясняется, как удалять повторяющиеся значения в Excel. Вы изучите несколько различных методов поиска и удаления дубликатов, избавитесь от дублирующих строк, обнаружите точные повторы и частичные совпадения.
Хотя Microsoft Excel является в первую очередь инструментом для расчетов, его таблицы часто используются в качестве баз данных для отслеживания запасов, составления отчетов о продажах или ведения списков рассылки.
Распространенная проблема, возникающая при увеличении размера базы данных, заключается в том, что в ней появляется много повторов. И даже если ваш огромный файл содержит всего несколько идентичных записей, эти несколько повторов могут вызвать массу проблем. Например, вряд ли порадует отправка нескольких копий одного и того же документа одному человеку или появление одних и тех же данных в отчете несколько раз.
Поэтому, прежде чем использовать базу данных, имеет смысл проверить ее на наличие дублирующих записей, чтобы убедиться, что вы не будете потом тратить время на исправление ошибок.
- Удаление повторяющихся строк вручную
- Удаление дубликатов в «умной таблице».
- Избавьтесь от повторов, скопировав уникальные записи в другое место.
- Как убрать дубликаты строк с помощью формул.
- Формулы для поиска повторяющихся значений в одном столбце
- Формулы для поиска повторяющихся строк.
В нескольких наших недавних статьях мы обсуждали различные способы выявления дубликатов в Excel и выделения неуникальных ячеек или строк (см.ссылки в конце статьи). Однако могут возникнуть ситуации, когда вы захотите в конечном счете устранить дубли в ваших таблицах. И это как раз тема этого руководства.
Метод 1: удаление дублирующихся строк вручную
Смотрите также: “Как убрать колонтитулы в Экселе”
Первый метод максимально прост и предполагает удаление дублированных строк при помощи специального инструмента на ленте вкладки “Данные”.
- Если обрабатываемая таблица содержит шапку, то проверяем пункт “Мои данные содержат заголовки” – он должен быть отмечен галочкой.
Как убрать дубликаты строк с помощью формул.
Еще один способ удалить неуникальные данные — идентифицировать их с помощью формулы, затем отфильтровать, а затем после этого удалить лишнее.
Преимущество этого подхода заключается в универсальности: он позволяет вам:
- находить и удалять повторы в одном столбце,
- находить дубликаты строк на основе значений в нескольких столбиках данных,
- оставлять первые вхождения повторяющихся записей.
Недостатком является то, что вам нужно будет запомнить несколько формул.
В зависимости от вашей задачи используйте одну из следующих формул для обнаружения повторов.
Формулы для поиска повторяющихся значений в одном столбце
Добавляем еще одну колонку, в которой запишем формулу.
Повторы наименований товаров, без учета первого вхождения:
=ЕСЛИ(СЧЁТЕСЛИ($B$2:$B2; $B2)>1; «Дубликат»; «»)
Как видите, когда значение встречается впервые (к примеру, в B4), оно рассматривается как вполне обычное. А вот второе его появление (в B7) уже считается повтором.
Отмечаем все повторы вместе с первым появлением:
=ЕСЛИ(СЧЁТЕСЛИ($B$2:$B$17; $B2)>1; «Дубликат»; «Уникальный»)
Где A2 — первая, а A10 — последняя ячейка диапазона, в котором нужно найти совпадения.
Ну а теперь, чтобы убрать ненужное, устанавливаем фильтр и в столбце H и оставляем только «Дубликат». После чего строки, оставшиеся на экране, просто удаляем.
Вот небольшая пошаговая инструкция.
Метод 2: удаление повторений при помощи “умной таблицы”
Еще один способ удаления повторяющихся строк – использование “умной таблицы“. Давайте рассмотрим алгоритм пошагово.
- ставим курсор на произвольную ячейку таблицы;
Примечание: Из всех описываемых в данной статье методов этот является наиболее гибким и универсальным, позволяя комфортно работать с таблицами различной структуры и объема.
Выделение дубликатов цветом
5860 23.10.2012
— Простите, вы не видели тут моего близнеца?
— Вы уже спрашивали.
Допустим, что у нас имеется длинный список чего-либо и мы предполагаем, что некоторые элементы этого списка повторяются более 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), чтобы собрать ФИО в одну ячейку:
Имея такой столбец мы, фактически, сводим задачу к предыдущему способу.
Если же хочется всё решить без дополнительного столбца, то формула для условного форматирования будет посложнее:
Ссылки по теме
- Сравнение двух диапазонов данных, поиск различий и совпадений
- Извлечение уникальных элементов из диапазона
Николай Рязанов
07.02.2013 22:21:10
А как быть, если идентичные дубликаты нужно сгруппировать? То есть Медведевых выделить одним цветом, а Ермаковых другим? Этот пример хорош тем, что он небольшой и всё видно наглядно, а если в таблице более тысячи строк, то на глаз прикинуть несколько десятков идентичных дубликатов и выделить нужные не так-то просто. Есть ли какое-то решение? Ссылка
Николай Павлов
08.02.2013 01:11:48
Я бы отфильтровал дубликаты по цвету, а потом отсортировал — получите на экране только повторяющиеся элементы сгруппированные «по одинаковости». Родитель Ссылка
Evgeniy Baiburin
02.07.2015 09:41:54
Николай, нашёл дубликаты по способу номер 1 (Excel 2007) из двух колонок. Так получилось, что правая колонка состоит из 2000 ячеек, и мне нужно было найти эти 2000 дубликатов в левой колонке состоящей из 27000 ячеек. Нашёл, обозначил цветом. Теперь нажимаю на фильтр над колонкой из 27000 ячеек (чтобы собрать воедино цветные ячейки) и у меня даже не вылазиет контекстное меню для выбора критериев фильтрации — Excel начинает в прямом смысле слова тупить, пока не нажмёшь клавишу . Вот такая проблема. Родитель Ссылка
Evgeniy Baiburin
02.07.2015 13:33:57
Нашёл ответ сам. Нужно было всего лишь подождать, поскольку столбец длиный он долго собирал в контекстное меню параметры фиольтрации. Спасибо. Всё работает. Родитель Ссылка
Artem Nas
21.06.2020 02:46:48
Та же самая проблема, что у вас на маке. На винде все нормально работает, сразу открывается. Хотя, мак мощнее. В чем может быть проблема? Разве это нормально, что так долго открывается фильтр?)) Если пытаться открыть соседний столбец, где не выделено цветом, то все мгновенно открывается. Родитель Ссылка
Николай Павлов
21.06.2020 11:53:16
Дело не в мощи процессора, а в самом ПО. Excel для Mac традиционно отстает по возможностям от Excel для Windows — так было всегда, к сожалению. Родитель Ссылка
Николай Павлов
18.05.2014 00:49:46
Николай, вот сделал специально для такой задачи макрос. Родитель Ссылка
Николай Рязанов
18.05.2014 12:07:39
Спасибо огромное, Николай! Очень нужный макрос. Экономит кучу времени, которое можно тратить на велопрогулки. Как Вы всё успеваете, даже ума не приложу! И надстройку допилить, и книгу написать, и работа, и дела домашние делать. Книгу жду с нетерпением (шпаргалка с приёмами и горячими клавишами прочно обосновалась на столе в течение нескольких месяцев). Вашим успехам радуюсь от души. Желаю написать многотомный бестселлер, перевести на несколько языков, создать свою корпорацию, вырастить несколько детей, находясь в гармонии с самим собой. Виртуально жму Вам руку и желаю всяческих успехов. Родитель Ссылка
Владимир Ястребов
02.07.2019 14:26:37
Шикарный макрос. Нельзя ли его немного оттюнинговать, чтобы на слишком темной заливке менялся бы цвет надписи(хотя бы)? А то если заливает тёмно-синим, то черный шрифт значения в ячейке не видать совсем. И ваще, хорошо бы заливать не стандартными цветами(они жёсткие — по «глазам бьют» ), а более мягкими из палитры themes/web. Спасибо. Родитель Ссылка
Елена Бондаренко
04.03.2013 12:33:56
Добрый день, подскажите, пожалуйста, возможно ли такое сравнение? знаяения в одном столбце (можно и разделить, т.е. данные в скобках вынести во второй столбец), н-р. Аптека 1 (ООО Фарм) Аптека 1 (ОАО Годовалов) Аптека 2 (ООО Фарм) ООО Фарм нужно оставить только одну строку с ООО Фарм, а строки Аптека 1 (ООО Фарм) и Аптека 2 (ООО Фарм) удалить (выделить…) Ссылка
Николай Павлов
09.03.2013 08:16:56
Елена, посмотрите статью про удаление дубликатов Родитель Ссылка
Александр
16.07.2013 12:34:25
Ссылка
Николай Павлов
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 , адаптируйте для своих значений. Меняет формат каждого следующего повтора, крорме первого. Ссылка
Татьяна Пятибокова
25.12.2013 20:19:03
Добрый вечер! Подскажите, а как выделить цветом ВСЮ СТРОКУ? Есть большая таблица данных, в которой например 8-й столбец содержит названия городов. Нужно выделить цветом всю строку таблицы данных, если это, предположим, Москва или С-Петербург. Заранее спасибо. Ссылка
Николай Павлов
04.01.2014 12:23:16
Выделить всю таблицу, открыть Главная — Условное форматирование — Создать правило — Использовать формулу
и ввести примерно так:
Предполагаю, что города у вас в столбце D и D2 — это первая ячейка с городом в этом столбце после шапки. Не забудьте задать цвет, нажав кнопку
Формат
.
Родитель Ссылка
Татьяна Пятибокова
04.01.2014 15:39:25
Николай! Большое Вам спасибо за помощь с моим вопросом и за замечательный сайт!! Успехов в Новом Году!!! Родитель Ссылка
Дмитрий Глушков
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. Родитель Ссылка
Karina
13.06.2014 15:24:03
Подскажите, пожалуйста, как можно реализовать вот такое действие: «Когда данные при вводе в ячейку ФИО повторяются, в соседней ячейке писалось «Повторно», а уникальные — «Впервые»?????? Спасибо!!! Ссылка
Николай Павлов
14.06.2014 10:31:20
Проверять формулой СЧЁТЕСЛИ не было ли раньше уже введенного значения. Затем при помощи функции ЕСЛИ выводить «повторно» или «впервые» в зависимости от результатов проверки. Точнее сказать не могу не видя вашего файла. Родитель Ссылка
Karina
14.06.2014 14:19:46
Спасибо большое, Николай!!! Я написала через функцию ЕСЛИ, вот какая формула получилась: =ЕСЛИ(СЧЁТЕСЛИ(E$1:E2;E2)>1;»Повторно»;»Впервые»). А я хотела попробовать через макрос, например как у вас тут на сайте приведен пример с автоматической вставкой ДАТЫ в соседнюю ячейку, я хотела так же и тут попробовать, ну ничего не вышло. Лучше же конечно, чтобы всё автоматически работало, а формулу надо постоянно копировать вниз. Родитель Ссылка
Proteje
15.10.2014 04:17:26
Добрый день! Скажите, а возможен поиск не 100%-ых дубликатов, а например 60% (и можно-ли менять этот показатель на 70-80-90% и т.д.)? Проблема в том, что у меня 5 списков наименований (~10.000 каждый), которые создавались пятью разными людьми. Эти 5 человек по разному описывали один и тот-же товар Пример: — молоко — «молоко» — молоко. — молако — малако Мне нужно соединить эти 5 списков в одну базу и найти дубликаты, но так как совпадение в моём случае не 100%, то и выделения тоже не происходит. Есть ли какое-то решение? Ссылка
Михаил Иванов
02.04.2015 13:03:06
Вот здесь описано возможное решение: Нечеткое сравнение строк Родитель Ссылка
Ruslan
07.04.2015 20:57:15
странно, у меня почему-то вариант с использованием функции «сцепить» не работает самое интересное то, что пример я скачал, удаляю правила из ячеек, затем снова добавляю — и не работает. Ссылка
Николай Павлов
09.04.2015 09:11:07
Руслан, посмотрите внимательно на доллары в адресах ячеек в моем примере и у вас — закрепление ссылок должно быть по-другому. Родитель Ссылка
Ruslan
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
Поэтому если изначально исключить дубли в первом столбце, то после этого мы не сможем их отследить во втором. Родитель Ссылка
Urizel777
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 строк,, то у меня выделяются все пока незаполненные ячейки, т.е. он отмечает пустые ячейки. Как их исключить? Ссылка
nov exp
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 подсвечивал только уникальные значения, а не частично похожие. Ссылка
Алекскй Иванов
14.07.2020 20:33:21
Есть решение, пишите в личку Родитель Ссылка
Александр Александров
03.03.2016 09:32:21
Здравствуйте, Николай! Сделал по описанному Вами способу выпадающие списки с накоплением (Фамилия ИО) в расписании занятий (чтобы знать в какое время занят тот или иной человек, на каком занятии, у кого и т.д.). В соседних ячейках время занятий («время от» и «время до»;) Попытался найти способ как выделять цветом одних и тех же людей, занятых в одно и то же время, чтобы случайно не поставить человека на то время, когда он записан на другом занятии, но не смог. Получается, если человек один, но у меня список с накоплением, вот в чем подвох. Когда набирается в списке более одного выделение снимается. Не подскажете, может есть выход? Ссылка
Dmitriy Kim
11.03.2016 07:00:49
странно, у меня не работает способ №3, может дело в том, что я использую умную таблицу, но почему тогда, когда я прописываю правило в окне «Изменение правила форматирования», нажимаю «ок», и окно не закрывается, убираю знак равно-проходит, но формула в ковычках и не работает. Ссылка
Марина
03.03.2017 08:34:00
Здравствуйте! Подскажите пожалуйста, можно ли сделать следующее: Есть книга с расписанием, в ней может быть до 6 листов. Можно ли сделать чтобы выделялись повторяющиеся ячейки на всех листах, для столбца «аудитория» но только для каждой строки отдельно (т.е. выделять если повторяется на любом курсе повтор аудитории например в понедельник 1 пара или вторник 5 пара). Шаблон: https://cloud.mail.ru/public/KGek/jPVjxCfXN Ссылка
Надежда Рябоштанова
30.08.2017 14:47:02
Добрый день! а можно ли вывести дубликаты (Фамилия и имя), если они в одном столбце, но не в отдельных ячейках, а в одной ячейке указано много фамилий сразу и таких ячеек около 40? Скриншот как столбца https://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
Всем привет, Помогите пожалуйста, мне преподаватель на экзамене по математической статистике, задал практическую работу, я все выходные просидел, ничего не выходит, скоро сдавать, а фрилансер который согласился помочь, не отвечает(. У меня есть файл с расписанием института, и бывает такая ситуация что преподаватели встречаются в аудиториях, так как у них одинаковые кабинеты, и в деканате при составлении расписания не могут всё учесть так как оно большое и приходиться преподавателям когда выходит расписание сидеть и просматривать, что очень долго и муторно по каждому пункту. Вот мне и преподаватель задал задачку, что бы сделать что то такое, что бы в строке , если повторяется две разные фамилии, но с одним кабинетом, подсвечивались эти два человека с одним кабинетом красным цветом, что бы в деканате видели, что у этих разных преподов, один кабинет в строке, и видели что их нужно разминуть, назначить им два разных кабинета. После того как им поменять кабинеты, красный цыет должен пропадать, и таким образом красный сигнализирует о том в строке что у этих преподавателей один кабинет которым нужно поменять кабинет чтоб они не встретились. Мой преподаватель думал что это видимо просто, но это очень не просто(. Не хочу долг получить по предмету, вырачайте… Вот файл файл если выручите, можете связь держать по почте Ссылка
Николай Павлов
05.02.2018 09:31:29
Илья, с такими запросами вам на Форум в ветку Работа
Родитель Ссылка
Илья Харенко
05.02.2018 09:39:10
Ок, спасибо! Родитель Ссылка
Станислав
21.03.2018 18:56:21
Подскажите формула для проверки повторяющихся значений в одной ячейки. Проверялось только выделеные числа. После дроби.
Ссылка
Helin ACAR
16.06.2020 16:07:19
Здравствуйте Николай Спасибо, что делитесь этим с нами. Хочу попросить у вас совета или даже помощи. Мы создаем таблицы и в ячейках тексты. В этих текстах бывают повторяющиеся предложения или словосочетания. Нам надо найти и выделить (!!! Не удалять!!!) эти предложения или словосочетания. Ну и потом их мануально изменить. Я не могу составить макрос или правило (кажется макрос будет правильней) для поиска и выделения предложения или скорее лучше будет минимум 3х слов. P.S. я нашла вот это решение https://www.extendoffice.com/documents/word/5450-word-find-duplicate-sentences.html через этот сайт https://otvet.mail.ru/question/217550511 , но этот код выделяет только абзацы, а не предложения в текстах. Помогите, пожалуйста Спасибо заранее Ссылка
Николай Павлов
21.06.2020 11:54:55
С такой задачей лучше на форум в раздел «Работа» — тут нужно специальный макрос писать (не самый простой, ИМХО). Родитель Ссылка
Artem Nas
21.06.2020 02:56:18
Здравствуйте, помогите, пожалуйста, разобраться со следующей проблемой. На макбуке про 2020 года 2.3 ггц 8 гб озу работаю с файлом эксель. У него 20 тысяч строк и 6 столбцов. Выделяю красным дубли в одном из столбцов, далее пытаюсь открыть фильтр и эксель зависает. Только при нажатии esc несколько раз отвисает. При нажатии на фильтр на соседних столбцах, где нет выделения цветом, все срабатывает мгновенно. При этом на более слабом компе на винде такой проблемы нет. Все почти мгновенно работает. В чем может быть проблема? Ссылка
Николай Павлов
21.06.2020 11:54:13
А Office какой версии? Родитель Ссылка
Artem Nas
21.06.2020 12:57:21
2019 последней версии. На других маках, даже более слабых, нет проблем с экселем. Родитель Ссылка
Viktor Vavilov
14.08.2020 13:41:59
Здравствуйте Николай. Огромное спасибо за статью. Не могу разобраться с возникшей проблемой: Решил автоматизировать Способ № 3 (без служебного столбца)
(
с формулойсумм(ч…
и т.д.) В таком виде:
Однако возникла проблема, после применения данного скрипта — выделения повторяющихся ячеек не происходит, при этом правило создаётся. Стоит зайти в редактирование этого правила (а именно в редактирование формулы) и ничего не изменяя — нажать ок/применить форматирование начинает работать. В чём может быть проблема? Заранее спасибо
UPD:
проблема решилась заменой в формуле макроса
СУММ
на
СУММПРОИЗВ
. За подсказку спасибо пользователю sokol92. Ссылка на форум Планета Excel с решением данной проблемы.
Ссылка
Метод 3: использование фильтра
Следующий метод не удаляет повторяющиеся строки физически, но позволяет настроить режим отображения таблицы таким образом, чтобы при просмотре они скрывались.
- как и в предыдущем способе, проверяем адрес диапазон ячеек таблицы;
Поиск и удаление повторений
Смотрите также удаляет повторяющиеся строки содержит тоже значение,нажмите кнопкуНа вкладке щелкните (или нажатьВыделить все, а затем нажмите — или применитьв группе прибавить к дате
есть дубль, у
данные, но иВыделите диапазон ячеек сВ некоторых случаях повторяющиеся на листе Excel.
Главная
клавиши Ctrl +
.
кнопку ОК
условное форматирование на
стиль
дни, т.д., смотрите
ячеек с уникальными работать с ними повторяющимися значениями, который данные могут быть
«Данные»-«Сортировка и фильтр»-«Дополнительно»-«Расширенный фильтр»-«Только 6. А значение
Удаление повторяющихся значений
повторяющиеся значенияв группе
Z на клавиатуре).Чтобы быстро удалить все. — для подтвержденияна вкладке « в статье «Как окрасились.
– посчитать дубли
нужно удалить. полезны, но иногда уникальные записи». Инструмент строки 4 =.
столбцы, нажмите кнопку
Уникальные значения из диапазона
добиться таких результатов,
Главная
посчитать рабочие дниИдея. их подсчет, написатьКак выделить повторяющиеся ячейки
«Нет». перед удалением, обозначитьСовет: они усложняют понимание скрывает повторяющиеся строки
строке 7. ЯчейкиВ списке
щелкните стрелку для
из структуры данных,
Снять выделение
».
support.office.com>
Метод 4: условное форматирование
Смотрите также: “Пример использования функции ВПР в Эксель: пошаговая инструкция”
Условное форматирование – гибкий и мощный инструмент, используемый для решения широкого спектра задач в Excel. В этом примере мы будем использовать его для выбора задвоенных строк, после чего их можно удалить любым удобным способом.
Важно! Этом метод не настолько универсален, как описанные выше, так как выделяет все ячейки с одинаковыми значениями, а не только те, для которых совпадает вся строка целиком. Это видно на предыдущем скриншоте, когда нужные задвоения по названиям регионов были выделены, но вместе с ними отмечены и все ячейки с категориями регионов, потому что значения этих категорий повторяются.
Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)
Выделяем таблицу, в которой необходимо обнаружить повторяющиеся значения. Переходим по вкладке Главная
в группу
Стили,
выбираем
Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения.
В появившемся диалоговом окне Повторяющиеся значения,
необходимо выбрать формат выделения дубликатов. У меня по умолчанию установлено светло-красная заливка и темно-красный цвет текста. Обратите внимание, в данном случае Excel будет сравнивать на уникальность не всю строку таблицы, а лишь ячейку столбца, поэтому если у вас имеются повторяющиеся значения только в одном столбце, Excel отформатирует их тоже. На примере вы можете увидеть, как Excel залил некоторые ячейки третьего столбца с именами, хотя вся строка данной ячейки таблицы уникальна.
Метод 5: формула для удаления повторяющихся строк
Последний метод достаточно сложен, и им мало, кто пользуется, так как здесь предполагается использование сложной формулы, объединяющей в себе несколько простых функций. И чтобы настроить формулу для собственной таблицы с данными, нужен определенный опыт и навыки работы в Эксель.
Формула, позволяющая искать пересечения в пределах конкретного столбца в общем виде выглядит так:
=ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов:адрес_шапки_столбца_дубликатов(абсолютный);адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;адрес_столбца;)>1;0;1);0));»»)
Давайте посмотрим, как с ней работать на примере нашей таблицы:
- ставим курсор в конец строки формул (нужно убедиться, что это, действительно, конец строки, так как в некоторых случаях длинная формула не помещается в пределах одной строки);
Как уже было сказано выше, этот метод сложен и функционально ограничен, так как не предполагает удаления найденных столбцов. Поэтому, при прочих равных условиях, рекомендуется использовать один из ранее описанных методов, более логически понятных и, зачастую, более эффективных.
Поиск повторяющихся значений включая первые вхождения.
Предположим, что у вас в колонке А находится набор каких-то показателей, среди которых, вероятно, есть одинаковые. Это могут быть номера заказов, названия товаров, имена клиентов и прочие данные. Если ваша задача — найти их, то следующая формула для вас:
=СЧЁТЕСЛИ(A:A; A2)>1
Где А2 — первая ячейка из области для поиска.
Просто введите это выражение в любую ячейку и протяните вниз вдоль всей колонки, которую нужно проверить на дубликаты.
Как вы могли заметить на скриншоте выше, формула возвращает ИСТИНА, если имеются совпадения. А для встречающихся только 1 раз значений она показывает ЛОЖЬ.
Подсказка! Если вы ищите повторы в определенной области, а не во всей колонке, обозначьте нужный диапазон и “зафиксируйте” его знаками $. Это значительно ускорит вычисления. Например, если вы ищете в A2:A8, используйте
=СЧЕТЕСЛИ($A$2:$A$8, A2)>1
Если вас путает ИСТИНА и ЛОЖЬ в статусной колонке и вы не хотите держать в уме, что из них означает повторяющееся, а что — уникальное, заверните свою СЧЕТЕСЛИ в функцию ЕСЛИ и укажите любое слово, которое должно соответствовать дубликатам и уникальным:
=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$17; A2)>1;»Дубликат»;»Уникальное»)
Если же вам нужно, чтобы формула указывала только на дубли, замените «Уникальное» на пустоту («»):
=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$17; A2)>1;»Дубликат»;»»)
В этом случае Эксель отметит только неуникальные записи, оставляя пустую ячейку напротив уникальных.
Поиск неуникальных значений без учета первых вхождений
Вы наверняка обратили внимание, что в примерах выше дубликатами обозначаются абсолютно все найденные совпадения. Но зачастую задача заключается в поиске только повторов, оставляя первые вхождения нетронутыми. То есть, когда что-то встречается в первый раз, оно однозначно еще не может быть дубликатом.
Если вам нужно указать только совпадения, давайте немного изменим:
=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2; A2)>1;»Дубликат»;»»)
На скриншоте ниже вы видите эту формулу в деле.
Нетрудно заметить, что она не обозначает первое появление слова, а начинает отсчет со второго.
Чувствительный к регистру поиск дубликатов
Хочу обратить ваше внимание на то, что хоть формулы выше и находят 100%-дубликаты, есть один тонкий момент — они не чувствительны к регистру. Быть может, для вас это не принципиально. Но если в ваших данных абв, Абв и АБВ — это три разных параметра – то этот пример для вас.
Как вы могли уже догадаться, выражения, использованные нами ранее, с такой задачей не справятся. Здесь нужно выполнить более тонкий поиск, с чем нам поможет следующая функция массива:
{=ЕСЛИ(СУММ((—СОВПАД($A$2:$A$17;A2)))<=1;»»;»Дубликат»)}
Не забывайте, что формулы массива вводятся комбиинацией Ctrl + Shift + Enter.
Если вернуться к содержанию, то здесь используется функция СОВПАД для сравнения целевой ячейки со всеми остальными ячейками с выбранной области. Результат возвращается в виде ИСТИНА (совпадение) или ЛОЖЬ (не совпадение), которые затем преобразуются в массив из 1 и 0 при помощи оператора (—).
После этого, функция СУММ складывает эти числа. И если полученный результат больше 1, функция ЕСЛИ сообщает о найденном дубликате.
Если вы взглянете на следующий скриншот, вы убедитесь, что поиск действительно учитывает регистр при обнаружении дубликатов:
Смородина и арбуз, которые встречаются дважды, не отмечены в нашем поиске, так как регистр первых букв у них отличается.
Использование сводных таблиц для определения повторяющихся значений
Воспользуемся уже знакомой нам таблицей с тремя столбцами и добавим четвертый, под названием Счетчик,
и заполним его единицами (1). Выделяем всю таблицу и переходим по вкладке
Вставка
в группу
Таблицы,
щелкаем по кнопке
Сводная таблица.
Создаем сводную таблицу. В поле Название строк
помещаем три первых столбца, в поле
Значения
помещаем столбец со счетчиком. В созданной сводной таблице, записи со значением больше единицы будут дубликатами, само значение будет означать количество повторяющихся значений. Для большей наглядности, можно отсортировать таблицу по столбцу
Счетчик
, чтобы сгруппировать дубликаты.
Поиск дублей в Excel – это одна из самых распространенных задач для любого офисного сотрудника. Для ее решения существует несколько разных способов. Но как быстро как найти дубликаты в Excel и выделить их цветом? Для ответа на этот часто задаваемый вопрос рассмотрим конкретный пример.
Выделяем цветом дубликаты в таблице
Первым способом я покажу вам, каким образом можно найти дубликаты и выделить их цветом. Это может вам потребоваться, для сравнения каких-либо данных без их удаления. В моем примере это будут одинаковые имена и фамилии людей.
Открывает вкладку «Главная», в разделе «Стили» выбираем «Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения».
Открылось окно, в котором есть два пункта: что выделить – уникальные или повторяющиеся значения, и, как их выделить – в какую цветовую гамму. И, конечно же, кнопка «ОК».
Чтобы поиск был осуществлен не по всей таблице, предварительно выделите один или несколько столбцов.
Посмотрите на мой результат. Правда такой способ имеет существенный недостаток: нет выборки, выделяет все, что встречается более одного раза.
Способ №1
1. Удерживая левую кнопку мышки, обозначьте курсором границы области таблицы (отдельные строки, столбцы), где необходимо убрать повторы. Либо выделите весь проект.
2. В меню редактора перейдите на вкладку «Данные».
3. В блоке «Работа с данными» нажмите кнопку «Удалить дубликаты».
4. В появившейся панели выберите столбцы, в которых нужно убрать одинаковые значения посредством установки меток. Если элементов много, используйте для быстрой настройки удаления опции «Выделить всё» и «Снять выделение». Нажмите «OK».
5. По завершении очистки строк и столбцов таблицы от дублей появится сообщение о том, сколько осталось уникальных значений.
Совет! Повторы сразу после удаления можно восстановить, щёлкнув в левом верхнем углу Excel значок «стрелочку влево».
Использование расширенного фильтра для удаления дубликатов
Выберите любую ячейку в таблице, перейдите по вкладке Данные
в группу
Сортировка и фильтр
, щелкните по кнопке
Дополнительно.
В появившемся диалоговом окне Расширенный фильтр,
необходимо установить переключатель в положение
скопировать результат в другое место,
в поле
Исходный диапазон
указать диапазон, в котором находится таблица, в поле
Поместить результат в диапазон
указать верхнюю левую ячейку будущей отфильтрованной таблицы и установить маркер
Только уникальные значения.
Щелкаем
ОК.
На месте, указанном для размещения результатов работы расширенного фильтра, будет создана еще одна таблица, но уже с отфильтрованными, по уникальным значениям, данными.