Как удалить повторяющиеся значения в таблице Excel

Метод 1: удаление дублирующихся строк вручную

Смотрите также: “Как убрать колонтитулы в Экселе”

Первый метод максимально прост и предполагает удаление дублированных строк при помощи специального инструмента на ленте вкладки “Данные”.

1. Полностью выделяем все ячейки таблицы с данными, воспользовавшись, например, зажатой левой кнопкой мыши.
2. Во вкладке “Данные” в разделе инструментов “Работа с данными” находим кнопку “Удалить дубликаты” и кликаем на нее.

3. Переходим к настройкам параметров удаления дубликатов:
   Если обрабатываемая таблица содержит шапку, то проверяем пункт “Мои данные содержат заголовки” – он должен быть отмечен галочкой.
4. Ниже, в основном окне, перечислены названия столбцов, по которым будет осуществляться поиск дубликатов. Система считает совпадением ситуацию, в которой в строках повторяются значения всех выбранных в настройке столбцов. Если убрать часть столбцов из сравнения, повышается вероятность увеличения количества похожих строк.
5. Тщательно все проверяем и нажимаем ОК.
6. Далее программа Эксель в автоматическом режиме найдет и удалит все дублированные строки.
7. По окончании процедуры на экране появится соответствующее сообщение с информацией о количестве найденных и удаленных дубликатов, а также о количестве оставшихся уникальных строк. Для закрытия окна и завершения работы данной функции нажимаем кнопку OK.

Работа с цветом

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

• После открытия вкладки «Главная»,
• зайдя в раздел под названием «Стили»,
• следует выбрать «Условное форматирование»,
• затем – «Правила обозначения ячеек»,
• и, наконец, «Повторяемые значения».

Итак, возникло окошко, которое содержит несколько пунктов: что именно выделять – уникальные либо повторяющиеся данные, а также, каким образом их выделять – в какой именно оттенок окрашивать. И, естественно, кнопка подтверждения.

Чтобы поиск осуществлялся не по всей составленной табличке, заранее следует выделить один либо пару столбцов.

Взгляните на результаты. Единственное, подобный метод обладает существенным минусом: отсутствует выборка, выделяется абсолютно все, встречающееся более, чем единожды.

Минутку внимания, возможно вам будет интересно узнать как сделать скриншот экрана на компьютере или .

Метод 2: удаление повторений при помощи “умной таблицы”

Еще один способ удаления повторяющихся строк – использование “умной таблицы“. Давайте рассмотрим алгоритм пошагово.

1. Для начала, нам нужно выделить всю таблицу, как в первом шаге предыдущего раздела.
2. Во вкладке “Главная” находим кнопку “Форматировать как таблицу” (раздел инструментов “Стили“). Кликаем на стрелку вниз справа от названия кнопки и выбираем понравившуюся цветовую схему таблицы.
3. После выбора стиля откроется окно настроек, в котором указывается диапазон для создания “умной таблицы“. Так как ячейки были выделены заранее, то следует просто убедиться, что в окошке указаны верные данные. Если это не так, то вносим исправления, проверяем, чтобы пункт “Таблица с заголовками” был отмечен галочкой и нажимаем ОК. На этом процесс создания “умной таблицы” завершен.
4. Далее приступаем к основной задаче – нахождению задвоенных строк в таблице. Для этого:
   ставим курсор на произвольную ячейку таблицы;
5. переключаемся во вкладку “Конструктор” (если после создания “умной таблицы” переход не был осуществлен автоматически);
6. в разделе “Инструменты” жмем кнопку “Удалить дубликаты“.
7. Следующие шаги полностью совпадают с описанными в методе выше действиями по удалению дублированных строк.

Примечание: Из всех описываемых в данной статье методов этот является наиболее гибким и универсальным, позволяя комфортно работать с таблицами различной структуры и объема.

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

7774 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

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

Татьяна Пятибокова

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: использование фильтра

Следующий метод не удаляет повторяющиеся строки физически, но позволяет настроить режим отображения таблицы таким образом, чтобы при просмотре они скрывались.

1. Как обычно, выделяем все ячейки таблицы.
2. Во вкладке “Данные” в разделе инструментов “Сортировка и фильтр” ищем кнопку “Фильтр” (иконка напоминает воронку) и кликаем на нее.
3. После этого в строке с названиями столбцов таблицы появятся значки перевернутых треугольников (это значит, что фильтр включен). Чтобы перейти к расширенным настройкам, жмем кнопку “Дополнительно“, расположенную справа от кнопки “Фильтр“.
4. В появившемся окне с расширенными настройками:
   как и в предыдущем способе, проверяем адрес диапазон ячеек таблицы;
5. отмечаем галочкой пункт “Только уникальные записи“;
6. жмем ОК.
7. После этого все задвоенные данные перестанут отображаться в таблицей. Чтобы вернуться в стандартный режим, достаточно снова нажать на кнопку “Фильтр” во вкладке “Данные”.

Метод 4: условное форматирование

Смотрите также: “Пример использования функции ВПР в Эксель: пошаговая инструкция”

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

1. Выделяем все ячейки нашей таблицы.
2. Во вкладке “Главная” кликаем по кнопке “Условное форматирование“, которая находится в разделе инструментов “Стили“.
3. Откроется перечень, в котором выбираем группу “Правила выделения ячеек“, а внутри нее – пункт “Повторяющиеся значения“.
4. Окно настроек форматирования оставляем без изменений. Единственный его параметр, который можно поменять в соответствии с собственными цветовыми предпочтениями – это используемая для заливки выделяемых строк цветовая схема. По готовности нажимаем кнопку ОК.
5. Теперь все повторяющиеся ячейки в таблице “подсвечены”, и с ними можно работать – редактировать содержимое или удалить строки целиком любым удобным способом.

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

Как найти повторяющиеся значения в Excel?

Допустим мы занимаемся регистрацией заказов, поступающих на фирму через факс и e-mail. Может сложиться такая ситуация, что один и тот же заказ поступил двумя каналами входящей информации. Если зарегистрировать дважды один и тот же заказ, могут возникнуть определенные проблемы для фирмы. Ниже рассмотрим решение средствами условного форматирования.

Чтобы избежать дублированных заказов, можно использовать условное форматирование, которое поможет быстро найти одинаковые значения в столбце Excel.

Пример дневного журнала заказов на товары:

Чтобы проверить содержит ли журнал заказов возможные дубликаты, будем анализировать по наименованиям клиентов – столбец B:

1. Выделите диапазон B2:B9 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. Вберете «Использовать формулу для определения форматируемых ячеек».
3. Чтобы найти повторяющиеся значения в столбце Excel, в поле ввода введите формулу: =СЧЁТЕСЛИ($B$2:$B$9; B2)>1.
4. Нажмите на кнопку «Формат» и выберите желаемую заливку ячеек, чтобы выделить дубликаты цветом. Например, зеленый. И нажмите ОК на всех открытых окнах.

Как видно на рисунке с условным форматированием нам удалось легко и быстро реализовать поиск дубликатов в Excel и обнаружить повторяющиеся данные ячеек для таблицы журнала заказов.

Пример функции СЧЁТЕСЛИ и выделение повторяющихся значений

Принцип действия формулы для поиска дубликатов условным форматированием – прост. Формула содержит функцию =СЧЁТЕСЛИ(). Эту функцию так же можно использовать при поиске одинаковых значений в диапазоне ячеек. В функции первым аргументом указан просматриваемый диапазон данных. Во втором аргументе мы указываем что мы ищем. Первый аргумент у нас имеет абсолютные ссылки, так как он должен быть неизменным. А второй аргумент наоборот, должен меняться на адрес каждой ячейки просматриваемого диапазона, потому имеет относительную ссылку.

Самые быстрые и простые способы: найти дубликаты в ячейках.

После функции идет оператор сравнения количества найденных значений в диапазоне с числом 1. То есть если больше чем одно значение, значит формула возвращает значение ИСТЕНА и к текущей ячейке применяется условное форматирование.

Пожалуй, каждый, кто работает с данными в Excel сталкивается с вопросом как сравнить два столбца в Excel на совпадения и различия. Существует несколько способов как это сделать. Давайте рассмотрим подробней каждый из них.

Метод 5: формула для удаления повторяющихся строк

Последний метод достаточно сложен, и им мало, кто пользуется, так как здесь предполагается использование сложной формулы, объединяющей в себе несколько простых функций. И чтобы настроить формулу для собственной таблицы с данными, нужен определенный опыт и навыки работы в Эксель.

Формула, позволяющая искать пересечения в пределах конкретного столбца в общем виде выглядит так:

=ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов:адрес_шапки_столбца_дубликатов(абсолютный);адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;адрес_столбца;)>1;0;1);0));»»)

Давайте посмотрим, как с ней работать на примере нашей таблицы:

1. Добавляем в конце таблицы новый столбец, специально предназначенный для отображения повторяющихся значений (дубликаты).
2. В верхнюю ячейку нового столбца (не считая шапки) вводим формулу, которая для данного конкретного примера будет иметь вид ниже, и жмем Enter: =ЕСЛИОШИБКА(ИНДЕКС(A2:A90;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E1:$E$1;A2:A90)+ЕСЛИ(СЧЁТЕСЛИ(A2:A90;A2:A90)>1;0;1);0));»»).
3. Выделяем до конца новый столбец для задвоенных данных, шапку при этом не трогаем. Далее действуем строго по инструкции:
   ставим курсор в конец строки формул (нужно убедиться, что это, действительно, конец строки, так как в некоторых случаях длинная формула не помещается в пределах одной строки);
4. жмем служебную клавишу F2 на клавиатуре;
5. затем нажимаем сочетание клавиш Ctrl+SHIFT+Enter.
6. Эти действия позволяют корректно заполнить формулой, содержащей ссылки на массивы, все ячейки столбца. Проверяем результат.

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