Эксель удаление дубликатов. Удалить дубликаты в Excel

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

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

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

Способ №1

1. Удерживая левую кнопку мышки, обозначьте курсором границы области таблицы (отдельные строки, столбцы), где необходимо убрать повторы. Либо выделите весь проект.

2. В меню редактора перейдите на вкладку «Данные».

3. В блоке «Работа с данными» нажмите кнопку «Удалить дубликаты».

4. В появившейся панели выберите столбцы, в которых нужно убрать одинаковые значения посредством установки меток. Если элементов много, используйте для быстрой настройки удаления опции «Выделить всё» и «Снять выделение». Нажмите «OK».

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

Совет! Повторы сразу после удаления можно восстановить, щёлкнув в левом верхнем углу Excel значок «стрелочку влево».

Способ №2

1. Кликните по редактируемой таблице.

2. На вкладке «Данные», в блоке «Сортировка и фильтр», щёлкните подраздел «Дополнительно».

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

1. В панели «Расширенный фильтр» клацните радиокнопку «Скопировать результат в другое место».

2. Нажмите кнопку, расположенную в правой части поля «Поместить результат в диапазон».

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

4. Кликните окошко «Только уникальные записи» и нажмите «OK».

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

Чтобы отредактировать документ без создания копий:

• в панели «Расширенный фильтр» установите режим обработки «Фильтровать список на месте»;
• кликом мышки включите надстройку «Только уникальные записи»;
• клацните «OK».

Давним пунктом в списке пожеланий к Excel была возможность автоматического удаления дубликатов строк в листе. И вот, начиная с Excel 2007, разработчики Microsoft исполнили это желание.

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

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

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

Следует понимать, что повторяющиеся значения определяются значением, отображаемым в ячейке, а это необязательно значение, хранящееся в ячейке. Предположим, две ячейки содержат одну и ту же дату: одна дата форматируется как 5/15/2010, а другая - как Май 15, 2010. При удалении дубликатов Excel посчитает, что эти строки различные.

Нажмите кнопку OK , и Excel отсеет повторяющиеся строки и выведет сообщение о том, как много дубликатов он удалил (рис. 33.3).

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

Можно ли удалить задвоеные, затроенные и так далее значения в Excel по нескольким столбцам?

Можно, причем очень просто. Для этого есть специальная функция. Предварительно выберите диапазон, где нужно удалять дубликаты. На ленте заходим Данные — Удалить дубликаты (смотрите картинку в начале статьи).

Выбираем первый столбец

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

Очень удобно!

2. Как выделить все дубликаты в Excel?

Уже слышали про ? Да, здесь оно еще как поможет! Выделяете столбец в котором надо пометить дубликаты, выбираете в меню Главное — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения…

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

Нажимаете ОК, если не хотите изменять форматирование. Теперь все данные по выбранным условиям подкрасятся.

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

Кстати, если нужно увидеть уникальные, то в окне слева выберите — уникальные.

3. Уникальные значения при помощи сводных таблиц

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

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

Поиск повторяющихся значений

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

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

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

Поиск уникальных значений

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

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

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

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

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

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

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

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

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

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

Получаем в итоге из начальных 27 записей только 19 без повторений. Этим способом можно удалить дубликаты в Excel 2003, тогда как предыдущий появился только в версии программы 2007.

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

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

Нахождение одинаковых элементов не работает в отчетах сводной таблицы.

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

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

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

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

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

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

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

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

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