Удалить дубли эксель

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

Сегодня поговорим о том, как удалить дублирующие записи (дубликаты) в Excel.

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

Простой способ. Кнопка «Удалить дубликаты».

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

Продвинутый способ. Сводная таблица.

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

1. Добавляем столбцу заголовок.
2. Выделяем интересующий нас диапазон.
3. На вкладке «Вставка» нажимаем кнопку «Сводная таблица».
4. В открывшемся диалоговом окне нажимаем «Ок». Должен создаться новый лист со сводной таблицей.
5. Переносим заголовок интересующего нас столбца в строки.
6. Вы получили перечень уникальных записей по выбранному столбцу.

Сегодня мы узнали как удалить дубли в Excel, либо как составить список уникальных записей из массива данных при помощи кнопки «Удалить дубликаты» или Сводной таблицы.

Если у Вас появились вопросы — задавайте их в комментариях к этой статье.

Поиск и удаление дубликатов в Microsoft Excel

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

Поиск и удаление

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

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

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

1. Выделяем весь табличный диапазон. Переходим во вкладку «Данные». Жмем на кнопку «Удалить дубликаты». Она располагается на ленте в блоке инструментов «Работа с данными».

Открывается окно удаление дубликатов. Если у вас таблица с шапкой (а в подавляющем большинстве всегда так и есть), то около параметра «Мои данные содержат заголовки» должна стоять галочка. В основном поле окна расположен список столбцов, по которым будет проводиться проверка. Строка будет считаться дублем только в случае, если данные всех столбцов, выделенных галочкой, совпадут. То есть, если вы снимете галочку с названия какого-то столбца, то тем самым расширяете вероятность признания записи повторной. После того, как все требуемые настройки произведены, жмем на кнопку «OK».

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

Способ 2: удаление дубликатов в «умной таблице»

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

Выделяем весь табличный диапазон.

Находясь во вкладке «Главная» жмем на кнопку «Форматировать как таблицу», расположенную на ленте в блоке инструментов «Стили». В появившемся списке выбираем любой понравившийся стиль.

Затем открывается небольшое окошко, в котором нужно подтвердить выбранный диапазон для формирования «умной таблицы». Если вы выделили все правильно, то можно подтверждать, если допустили ошибку, то в этом окне следует исправить. Важно также обратить внимание на то, чтобы около параметра «Таблица с заголовками» стояла галочка. Если её нет, то следует поставить. После того, как все настройки завершены, жмите на кнопку «OK». «Умная таблица» создана.

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

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

Способ 3: применение сортировки

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

Выделяем таблицу. Переходим во вкладку «Данные». Жмем на кнопку «Фильтр», расположенную в блоке настроек «Сортировка и фильтр».

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

Открывается окно расширенного фильтра. Устанавливаем в нем галочку напротив параметра «Только уникальные записи». Все остальные настройки оставляем по умолчанию. После этого кликаем по кнопке «OK».

После этого, повторяющиеся записи будут скрыты. Но их показ можно в любой момент включить повторным нажатием на кнопку «Фильтр».

Способ 4: условное форматирование

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

Выделяем область таблицы. Находясь во вкладке «Главная», жмем на кнопку «Условное форматирование», расположенную в блоке настроек «Стили». В появившемся меню последовательно переходим по пунктам «Правила выделения» и «Повторяющиеся значения…».

Открывается окно настройки форматирования. Первый параметр в нём оставляем без изменения – «Повторяющиеся». А вот в параметре выделения можно, как оставить настройки по умолчанию, так и выбрать любой подходящий для вас цвет, после этого жмем на кнопку «OK».

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

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

Способ 5: применение формулы

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

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

Создаем отдельный столбец, куда будут выводиться дубликаты.

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

Выделяем весь столбец для дубликатов, кроме шапки. Устанавливаем курсор в конец строки формул. Нажимаем на клавиатуре кнопку F2. Затем набираем комбинацию клавиш Ctrl+Shift+Enter. Это обусловлено особенностями применения формул к массивам.

После этих действий в столбце «Дубликаты» отобразятся повторяющиеся значения.

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Удаление дубликатов в Excel с помощью таблиц

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

Как удалить дубликаты в Excel

Ниже на рисунке изображена таблица с дублирующими значениями. Строка 3 содержит тоже значение, что и строка 6. А значение строки 4 = строке 7. Ячейки с числами в дублирующихся строках имеют одинаковые значения и разные форматы. У них отличается количество разрядов после запятой. Выполним 2 разные операции для удаления дубликатов.

Устранение дубликатов на основе значений колонки с текстом:

1. Создайте умную таблицу (CTRL+T) с повторяющимися значениями как на рисунке:
2. Щелкните по таблице и выберите инструмент «Работа с таблицами»-«Конструктор»-«Удалить дубликаты» в разделе инструментов «Сервис».
3. В появившемся окне «Удалить дубликаты», следует отключить проверку по 4-му столбцу «Цена».

Строки 6 и 7 распознаны как дублирующие и удалены из таблицы. Если в пункте 2 не отключить проверку по столбцу ни одна строка не будет удалена, так как для Excel все числа в колонке «Цена» считаются разными.

Альтернативные способы удаления дубликатов

В Excel существуют и другие средства для работы с дублированными значениями. Например:

1. «Данные»-«Удалить дубликаты» — Инструмент удаляет повторяющиеся строки на листе Excel.
2. «Данные»-«Сортировка и фильтр»-«Дополнительно»-«Расширенный фильтр»-«Только уникальные записи». Инструмент скрывает повторяющиеся строки листа.
3. «Главная»-«Условное форматирование»-«Правила выделения ячеек»-«Повторяющиеся значения». Инструмент подсвечивает дублирующие данные цветом.
4. Формула: =СЧЁТЕСЛИ($B:$B;B2)>1. Формула ищет одинаковые значения чисел количества в столбце B. Формулу следует протянуть маркером (скопировать) вдоль всех значений. Так же следует проконтролировать, чтобы был одинаковый формат отображения всех исходных чисел в столбце A.
5. Формула в массиве: 1;0;1);0));»»)’ class=’formula’>. Формула ищет одинаковые наименования в диапазоне A2:A13 и выводит их в отдельный список столбца B (формулу нужно скопировать в диапазон B2:B13). Обратите внимание, что формула отображается в фигурных скобках <>, а значит она выполняется в массиве. Поэтому ее нужно вводить комбинацией горячих клавиш CTRL+SHIFT+Enter.

Каждый инструмент обладает своими преимуществами и недостатками. Но эффективнее всех использовать для удаления дубликатов – таблицу (как описано выше). Там весь процесс происходит поэтапно с максимальным контролем данных. Это дает возможность получить качественный результат.

Поиск и удаление повторений

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

Выберите ячейки, которые нужно проверить на наличие повторений.

Примечание: В Excel не поддерживается выделение повторяющихся значений в области «Значения» отчета сводной таблицы.

На вкладке Главная выберите Условное форматирование > Правила выделения ячеек > Повторяющиеся значения.

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

Удаление повторяющихся значений

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

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

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

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

Например, на данном листе в столбце «Январь» содержатся сведения о ценах, которые нужно сохранить.

Поэтому флажок Январь в поле Удаление дубликатов нужно снять.