Удаление дубликатов в Excel

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

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

  1. Поиск уникальных значений/Поиск дубликатов.
  2. Получение уникальных значений.
  3. Получение значений, где есть дублирующие записи.

Метод первый - для Excel 2007  и выше.

Слава богу, для обладателей версии Excel 2007  и выше можно ни о чем не думать. Начиная с этой версии, появилось штатное средство - "Удалить дубликаты" на вкладке Данные. Удаление дубликатов в Excel

Пользоваться им просто:

  • Встаете на вашу таблицу с дубликатами, нажимаете "Удалить дубликаты".
  • Появляется окно, где вам нужно выделить те столбцы, по которым у вас могут быть идти совпадения. Удаление дубликатов в ExcelНапример, у меня список депутатов Госдумы и некоторые из них повторяются. Я могу выбрать все три столбца Фамилия, Имя и Отчество, а могу просто поискать однофамильцев, тогда я оставлю только первый столбец.
  • Результатом будет вот такое окноУдаление дубликатов в Excel

Плюс этого метода - скорость и простота. Надо получить только уникальные значения - решения в два клика, получите и распишитесь.

Минус в том, что если мы более глубоко работаем над таблицей, то мы не сможем понять, какие данные были дублированы (а соответственно, не определим причину дубляжа), а это порой не менее важнее получения нормального списка.

Метод второй - расширенный фильтр.

Этот метод доступен уже и для версии Excel 2003, ниже не проверял, но насколько помню эта версия не сильно прогрессировала по сравнению с 2000й версией.

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

  • Встаем на таблицу. Нажимаем Дополнительно в группе Сортировка и Фильтр на вкладке ДанныеУдаление дубликатов в Excel
  • Появляется окошко, где нам надо выбрать диапазон (который не надо выбирать, если ваша таблица не имеет разрывов и вам нужно удалить дубликаты не по отдельным столбцам) и отметить галочкой пункт "Только уникальные записи". Правда, надо еще определиться, где вы хотите видеть список без дубликатов - на отдельном листе или пусть список отфильтруется на месте. Во втором случае дубликаты не удалятся, а просто скроются, так что можно будет еще с ними поработать. Результат копирования результата в другое место практически аналогичен выше описанному методу с использованием инструмента Удалить дубликаты.Удаление дубликатов в Excel
  • После этого нажимаете "Ок" и ваши данные отфильтруются или скопируются, в зависимости от того, что вы выбрали. Я буду рассматривать случай фильтрацииПоиск уникальных значение в Excelкак видите, данные отфильтрованы - это можно видеть по синим номерам строк и нарушенной нумерации (после 19 идет 21, 20я строка скрыта).
  • Теперь уникальные значения можно выделить цветом или забить в отдельном столбце какой-то признак (я ставлю единицу, так проще потом анализировать).Поиск уникальных значений в Excel
  • Теперь выбираем команду "Очистить" и у вас сразу видно, где дубликаты - они или не отмечены никаким цветом либо у них пустые поля. Теперь можно с помощью автофильтра спокойно получить список дубликатов или уникальных значений.

Плюс этого метода в том, что мы имеем выбор - удалять или не удалять дубликаты, а значит, можем работать и анализировать данные.

И в конце еще один метод (шутка). Просто сделайте сводную таблицу и нужные столбцы закиньте в поле строк. Повторы там не появятся :)

6-минутное видео по статье:

Эксель Практик

«Глаза боятся, а руки делают»

 P.S. Понравилась статья? Подпишитесь на рассылку в правой части страницы (Бесплатный курс "Топ-10 инструментов Excel") и будьте в курсе новых событий.

 

 

 

 

 

/strong

Удаление дубликатов в Excel: 13 комментариев

  1. Дмитрий

    Добрый день! Подскажите, возможно ли удалить повторяющиеся слова в одной ячейке?

  2. admin Автор записи

    Привет. Штатными средствами не получится. Так-то возможно, но это гемор, величина которого зависит от задачи — что за данные, какие инструменты реально задействовать.
    Как бы поступил я: разбил бы ячейку на слова, использовав инструмент «Текст по столбцам», потом с помощью функции СЧЁТЕСЛИ я бы отобрал неповторяющиеся значения и одновременно с помощью функции «СЦЕПИТЬ» объединил бы все слова. Надеюсь, ответил.

  3. Анна

    Отличная статья! Подскажите — при методе «Удалить дубликаты» какие ячейки удаляются? Первая остается? а все последующие (дубликаты) — удаляются?? Очень важно знать это! Спасибо за ответ!!

  4. admin Автор записи

    Спасибо :)
    Первый остается, остальные удаляются, поэтому важно смотреть на порядок сортировки ваших данных.

  5. irina

    я не волшебник, я только учусь, в том числе и благодаря Вашим урокам. меня мучает вопрос — почему в данном примере для выделения уникальных записей лучше проставить для них отдельный признак «1″, а не выделить их цветом.

  6. admin Автор записи

    Цвет — это только визуальное выделение. Если же стоит единица, её можно использовать в формулах. Смотрите наперед, ведь в Экселе самое лучшее применение — использование его для многократных операций.

  7. Николай

    Возможно ли вернуть удаленные дубликаты? Получилось так, что удалил дубликаты, чтобы посчитать сумму, а потом случайно сохранил(((

  8. admin Автор записи

    Посмотрите в версиях — Управление версиями. В Excel 2013 — Файл/Управление версиями. Там сохраняются версии вашего файла, возможно получится вернуть.

  9. Александр

    Как удалить дубликаты (повторяющиеся) email адреса.
    ПРИМЕР-
    ( info@ajax-maritime.com, cv@gms.od.ua, asap@te.net.ua, ua-csc-ode-man@bc-shipmanagement.com, bernard.angelvy@bourbon-online.com, vimar_crew@farlep.net vimar@te.net.ua, vitamaritime@email.com.ua, vmc@inet.ua, office@volans.com.ua, info@ajax-maritime.com, cv@gms.od.ua, info@globalship.com.ua, cv@gms.od.ua,goldenport@comstar.net.ua, crew@gmarine.com.ua, danukr@danaos.od.ua )
    Некоторые из них повторяются по несколько раз, как в примере я выделил разным цветом , вот мне нужно чтоб не было дубликатов слов, чтоб все слова были без дубликатов, т.е в одном экземпляре.

    ВОТ ТАК ( info@ajax-maritime.com, asap@te.net.ua, ua-csc-ode-man@bc-shipmanagement.com, bernard.angelvy@bourbon-online.com, vimar_crew@farlep.net vimar@te.net.ua, vitamaritime@email.com.ua, vmc@inet.ua, office@volans.com.ua, info@globalship.com.ua, cv@gms.od.ua, goldenport@comstar.net.ua, crew@gmarine.com.ua, danukr@danaos.od.ua )

    На данный момент список с адресами находится в Worde и Блокноте.

    СПАСИБО,

  10. admin Автор записи

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

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

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

Можно использовать следующие HTML-теги и атрибуты: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>