Как удалить дубликаты в Excel. Как в 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
Ниже на рисунке изображена таблица с дублирующими значениями. Строка 3 содержит тоже значение, что и строка 6. А значение строки 4 = строке 7. Ячейки с числами в дублирующихся строках имеют одинаковые значения и разные форматы. У них отличается количество разрядов после запятой. Выполним 2 разные операции для удаления дубликатов.
Устранение дубликатов на основе значений колонки с текстом:
Строки 6 и 7 распознаны как дублирующие и удалены из таблицы. Если в пункте 2 не отключить проверку по столбцу ни одна строка не будет удалена, так как для Excel все числа в колонке «Цена» считаются разными.
Альтернативные способы удаления дубликатов
В Excel существуют и другие средства для работы с дублированными значениями. Например:
Каждый инструмент обладает своими преимуществами и недостатками. Но эффективнее всех использовать для удаления дубликатов – таблицу (как описано выше). Там весь процесс происходит поэтапно с максимальным контролем данных. Это дает возможность получить качественный результат.
Когда работаешь с огромным объемом данных в программе "Эксель", то ненароком очень просто можно совершить ошибку и вписать одни и те же данные повторно. Так возникают дубликаты. Это, конечно же, повышает объем всей суммарной информации, а в некоторых случаях сбивает формулы, которые должны подводить итоги и рассчитывать различные переменные. Вручную найти дубликаты можно, но зачем, если есть несколько способов, как в Excel удалить повторяющиеся строки автоматически.
Способ 1: стандартное удаление дубликатов
Самым простым способом, как строки, является использование соответствующего инструмента, располагающегося в ленте.
Итак, вам необходимо:
- Зажав левую кнопку мыши, выделить необходимый диапазон ячеек, в пределах которого будет поиск и автоматическое удаление повторяющихся строк.
- Перейти во вкладку "Данные".
- Нажать по инструменту "Удалить дубликаты", что находится в группе "Работа с данными".
- В появившемся окне поставить галочку рядом с "Мои данные содержат заголовки".
- Установить галочки рядом с названием тех столбцов, в которых будет проводиться поиск. Возьмите во внимание, что если установлены все галочки, то дубликатом будут считаться строки, которые полностью повторяются во всех столбцах. Таким образом, если вы хотите удалить дубликаты с одного столбца, то необходимо оставить лишь одну галочку рядом с его наименованием.
- Нажать "ОК".
Как только вы нажмете на кнопку, начнется поиск дубликатов выделенной области, а затем и их удаление. По итогу появится окно, в котором будет продемонстрирован отчет. Теперь вы знаете, как в Excel удалить повторяющиеся строки, но лишь первый способ, на очереди второй.
Способ 2: использование "умной таблицы"
Дубликаты можно удалить подобным способом, который имеет место быть в этой статье. На сей раз, в рассказе, как в Excel удалить повторяющиеся строки, будет использоваться "умная таблица".
Для удаления повторов в Excel, вам нужно сделать следующее:
- Как и в предыдущем способе, сначала выделить диапазон ячеек, где необходимо удалить дубликаты.
- Нажать кнопку "Форматировать как таблицу", что располагается на вкладке "Главная" в группе инструментов "Стили".
- Выбрать из выпадающего меню понравившейся стиль (любой).
- В появившемся окне необходимо подтвердить указанный ранее диапазон ячеек, а если он не совпадает, то переназначить. Также установите галочку рядом с "Таблица с заголовками», если она такова, по итогу нажмите кнопку "ОК".
- "Умная таблица" создана, но это еще не все. Теперь вам необходимо выделить любую ячейку таблицы, чтобы во вкладках появился пункт "Конструктор" и непосредственно перейти в эту вкладку.
- В ленте инструментов нужно нажать кнопку "Удалить дубликаты".
После этого появится окно удаления повторяющихся строк. Оно будет аналогично тому, что было представлено в первом способе, поэтому все последующие действия проводите в соответствии с первой инструкцией.
Заключение
Вот мы и разобрали два способа, как в Excel удалить строки с повторяющимися значениями. Как можно заметить, в этом нет ничего сложного, а пользуясь инструкцией, так и вовсе провернуть данную операцию можно за несколько секунд. Пример был приведен в 2016-й версии программы, однако удалить повторяющиеся строки в Excel 2010 и других версиях можно точно также.
Дубликаты данных в Excel могут приводить к множеству проблем при работе с данными. Не важно, импортируете ли вы данные из какой-либо базы данных, получаете их от коллеги или друзей. Чем больше данных в вашем файле, тем сложней найти и удалить дубликаты в Excel.
В этой статье мы подробно рассмотрим эффективные практики по поиску и удалению дубликатов.
Поиск и выделение дубликатов цветом в Excel
Дубликаты в таблицах могу встречаться в разных формах. Это могут быть повторяющиеся значения в одной колонке и в нескольких, а также в одной или нескольких строках.
Поиск и выделение дубликатов цветом в одном столбце в Эксель
Самый простой способ найти и выделить цветом дубликаты в Excel, это использовать условное форматирование.
Как это сделать:
- Выделим область с данными, в которой нам нужно найти дубликаты:
- На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”:
- Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены дублирующие значения. Нажмите кнопку “ОК”:
- После этого, в выделенной колонке, будут подсвечены цветом дубликаты:
Подсказка : не забудьте проверить данные вашей таблицы на наличие лишних пробелов. Для этого лучше использовать функцию TRIM (СЖПРОБЕЛЫ).
Поиск и выделение дубликатов цветом в нескольких столбцах в Эксель
Если вам нужно вычислить дубликаты в нескольких столбцах, то процесс по их вычислению такой же как в описанном выше примере. Единственное отличие, что для этого вам нужно выделить уже не одну колонку, а несколько:
- Выделите колонки с данными, в которых нужно найти дубликаты;
- На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
- После этого в выделенной колонке будут подсвечены цветом дубликаты:
Поиск и выделение цветом дубликатов строк в Excel
Поиск дубликатов повторяющихся ячеек и целых строк с данными это разные понятия. Обратите внимание на две таблицы ниже:
В таблицах выше размещены одинаковые данные. Их отличие в том, что на примере слева мы искали дубликаты ячеек, а справа мы нашли целые повторяющие строчки с данными.
Рассмотрим как найти дубликаты строк:
- Справа от таблицы с данными создадим вспомогательный столбец, в котором напротив каждой строки с данными проставим формулу, объединяющую все значения строки таблицы в одну ячейку:
=A2&B2&C2&D2
Во вспомогательной колонке вы увидите объединенные данные таблицы:
Теперь, для определения повторяющихся строк в таблице сделайте следующие шаги:
- Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
- На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
- После этого в выделенной колонке будут подсвечены дублирующиеся строки:
На примере выше, мы выделили строки в созданной вспомогательной колонке.
Но что, если нам нужно выделить цветом строки не во вспомогательном столбце, а сами строки в таблице с данными?
Для этого давайте сделаем следующее:
- Также как и в примере выше создадим вспомогательный столбец, в каждой строке которого проставим следующую формулу:
=A2&B2&C2&D2
Таким образом, мы получим в одной ячейке собранные данные всей строки таблицы:
- Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15 ;
- Затем, на вкладке “Главная” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:
- В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:
=СЧЁТЕСЛИ($E$2:$E$15;$E2)>1
- Не забудьте задать формат найденных дублированных строк.
Эта формула проверяет диапазон данных во вспомогательной колонке и при наличии повторяющихся строк выделяет их цветом в таблице:
Как убрать дубликаты в Excel
Выше мы изучили как найти дубликаты и как их выделить цветом. Ниже вы узнаете как их удалить.
Как удалить дубликаты в одном столбце Эксель
Если ваши данные размещены в одном столбце и вы хотите удалить все дубликаты, то проделайте следующие действия:
- Выделите данные;
- Перейдите на Панели инструментов во вкладку “Данные” – > раздел “Работа с данными” -> “Удалить дубликаты”:
- В диалоговом окне “Удалить дубликаты” поставьте флажок напротив пункта “Мои данные содержат заголовки”, если в выделенном вами диапазоне столбца есть заголовок. Также, убедитесь, что в меню “Колонны” выделен необходимый вам столбец:
- Нажмите “ОК”
После этого система удалит все дубликаты в столбце, оставив только уникальные значения.
Совет. Обязательно делайте резервную копию ваших данных перед любыми операциями с удалением дубликатов. Можно также проводить удаление дубликатов на отдельных листах, во избежание случайного удаления данных.
Как удалить дубликаты в нескольких столбцах в Excel
Представим, что у нас есть данные продаж как в таблице ниже:
Желтым цветом выделены строки, в которых имена, регион и сумма продаж одинаковы, но даты разные. Скорее всего, это связано с ошибкой ввода дынных в таблице. Если нам требуется удалить дубликаты данных таблицы в отдельных колонках, то проделайте следующие действия:
- Выделите данные таблицы;
- Перейдите на Панели инструментов во вкладку “Данные” – > раздел “Работа с данными” -> “Удалить дубликаты”:
- В диалоговом окне “Удалить дубликаты” поставьте флажок напротив пункта “Мои данные содержат заголовки” если в выделенном вами диапазоне есть заголовки. Для того, чтобы удалить дубликаты во всех столбцах кроме даты, оставьте поле с датой пустым:
- Нажмите “ОК”
После этого система удалит строки с данными, в которых дублируются значения в колонках “Имя”, “Регион”, “Продажи”.
Как удалить дублирующие строки с данными в Эксель
Если вам нужно удалить целые строки дублирующиеся в таблице, проделайте следующие шаги:
- Выделите данные таблицы;
- Перейдите на Панели инструментов во вкладку “Данные” – > раздел “Работа с данными” -> “Удалить дубликаты”:
- В диалоговом окне “Удалить дубликаты” поставьте флажок напротив пункта “Мои данные содержат заголовки” если в выделенном вами диапазоне есть заголовки. Для того чтобы система сравнила внутри таблицы строки, важно убедиться что отмечены все столбцы в диалоговом окне:
- Нажмите “ОК”
Используйте вышеупомянутые методы для очистки данных и избавления от дубликатов.