Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel

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

1.     Удаление повторяющихся значений в Excel (2007+)

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

удалить-дубликаты excel

Если в каждом столбце таблицы имеется заголовок, установить маркер Мои данные содержат заголовки. Также проставляем маркеры напротив тех столбцов, в которых требуется произвести поиск дубликатов.

удалить-дубликаты excel

Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.

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

2.     Использование расширенного фильтра для удаления дубликатов

Выберите любую ячейку в таблице, перейдите по вкладке Данные в группу Сортировка и фильтр, щелкните по кнопке Дополнительно.

расширенный фильтр excel

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

расширенный фильтр excel

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

расширенный фильтр excel

3.     Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)

Выделяем таблицу, в которой необходимо обнаружить повторяющиеся значения. Переходим по вкладке Главная в группу Стили, выбираем Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения.

повторяющиеся значения excel

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

повторяющиеся значения excel

4.     Использование сводных таблиц для определения повторяющихся значений

Воспользуемся уже знакомой нам таблицей с тремя столбцами и добавим четвертый, под названием Счетчик, и заполним его единицами (1). Выделяем всю таблицу и переходим по вкладке Вставка в группу Таблицы, щелкаем по кнопке Сводная таблица.

сводная таблица excel

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

сводная таблица excel


69 комментариев

  1. Pingback: Как сравнить два столбца в Excel — методы сравнения данных Excel « Blog — organiser

  2. Отличная статья. Спасибо. Всегда испытывал много трудностей с Экселем. Многие задачи занимали по много времени. Советы описанные в статье помогут сократить трудозатраты, что очень классно.

  3. Мне нужно отформатировать повторяющиеся значения но не по полным значениям а по первым 10 элементам например есть CZE21055A2 Зырянов и есть CZE21055A2 Иванов так вот мне надо чтобы форматирование произошло по первым 10 элементам и выделило цветом повторяющиеся
    CZE21055A2 Зырянов
    CZE21055A2 Иванов в итоге должно показаться что это повторяющиеся значения цветом

  4. помогите плиз как выделить одинаковые гиперссылки? Гиперссылки могут называться по разному, но и ссылки разные. задача найти одинаковые ссылки в гиперссылках.

  5. А если усложнить задание. Из первой таблицы найти парные ячейки и перенести результаты в указанное место. Попытался объяснить это в файле. Помогите пожалуйста. В реале таблица большая и выбрать вручную это очень тяжело.

    Файл:  1100.xlsx

  6. Здравствуйте!Подскажите пожалуйста,как после выделения дубликатов цветом,убрать эту функцию?Т.е я проверила,и нужно вернуть исходный вид

  7. Доброе время суток!Подскажите, как с помощью формулы можно удалить повторы, если значений названий столбцов аж 30. А строк больше 30 000…
    с помощью сводной таблицы, как я понимаю, нереально все 30 000 проставить 1, чтоб потом проверить.
    Помогите найти рещение….Как отфильтровать эту базу данных на повторы?

  8. стоит ЕХЕЛЬ 2013.
    2 способ (где используют расширенный фильтр) не получается???.
    В Вашем примере обратил внимание на то что база предварительно отфильтрована по алфавиту может в этом всё дело???

  9. Pingback: Какими бывают аналитические системы — small big data

  10. Спасибо, очень полезная статья. Пожалуйста, позвольте спросить.

    Как можно находить повторяющиеся слова, учитывая окончания слов? То есть слова в разных словоформах.

    Пример: Алексей Синицын — Алексея Синицина (Алексеем, Алексея) и т.д.

    Можно ли в Excel распознавать слова, учитывая словоформы?

  11. Добрый день . Есть большой лист Excel. На нем есть отдельные ячейки с цифрами через запятую от 1 до 99. Каждая ячейка содержит 10 цифр в порядке возрастания. Выглядят ячейки так: 44,48,54,59,60,61,64,73,79,97; 23,32,35,38,41,56,62,63,65,84; и т.д. Н некоторые ячейки из них с повторяющимися цифрами например: 54,59,61,73,78,81,85,87,93,98; 48,54,59,60,64,68,72,77,85,92; 23,35,41,56,60,67,73,83,94,99

    4-5 цифр повторяются, остальные разные. Ячейки в которых совпадают все 10 цифр можно автоматически выделить с помощью условного форматирования.
    А вот как сделать так, чтобы подобным образом автоматически выделялись цветом ячейки в которых совпадают 4 цифры и более?
    За раннее спасибо!

  12. Здравствуйте.
    Есть Ячейки с номерами телефонов (допустим 8908323224) и есть ячейки с номерами телефонов + с комментариями к ним (допустим 8908323224 не дозвонился.). Как мне находить ячейки с повторяющимися номерами телефонов в таком случае?
    Ведь поиск ведется по абсолютно идентичным ячейкам, а у меня не везде есть комментарии.

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

  14. Toonily is on the mission to generate real readers on its website by providing high-quality manga to all ages. This website is dedicated to generating Korean translated comics and toons for the global audience. It is a safe place and secure for the customers to use. Toonily

  15. The Uwell Caliburn X 20w Pod Kit is a great vape kit for anyone looking for a small and portable device. It is listed at BestVapeMart It’s also a great choice for beginners, as it’s easy to use and maintain. The Uwell Caliburn X 20w Pod Kit produces great flavor and vapor, and it’s available in a variety of colors. I would highly recommend this vape kit to anyone looking for a new device.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *