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

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

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

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

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

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

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

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

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

Итак, вам необходимо:

1. Зажав левую кнопку мыши, выделить необходимый диапазон ячеек, в пределах которого будет поиск и автоматическое удаление повторяющихся строк.
2. Перейти во вкладку "Данные".
3. Нажать по инструменту "Удалить дубликаты", что находится в группе "Работа с данными".
4. В появившемся окне поставить галочку рядом с "Мои данные содержат заголовки".
5. Установить галочки рядом с названием тех столбцов, в которых будет проводиться поиск. Возьмите во внимание, что если установлены все галочки, то дубликатом будут считаться строки, которые полностью повторяются во всех столбцах. Таким образом, если вы хотите удалить дубликаты с одного столбца, то необходимо оставить лишь одну галочку рядом с его наименованием.
6. Нажать "ОК".

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

Способ 2: использование "умной таблицы"

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

Для удаления повторов в Excel, вам нужно сделать следующее:

1. Как и в предыдущем способе, сначала выделить диапазон ячеек, где необходимо удалить дубликаты.
2. Нажать кнопку "Форматировать как таблицу", что располагается на вкладке "Главная" в группе инструментов "Стили".
3. Выбрать из выпадающего меню понравившейся стиль (любой).
4. В появившемся окне необходимо подтвердить указанный ранее диапазон ячеек, а если он не совпадает, то переназначить. Также установите галочку рядом с "Таблица с заголовками», если она такова, по итогу нажмите кнопку "ОК".
5. "Умная таблица" создана, но это еще не все. Теперь вам необходимо выделить любую ячейку таблицы, чтобы во вкладках появился пункт "Конструктор" и непосредственно перейти в эту вкладку.
6. В ленте инструментов нужно нажать кнопку "Удалить дубликаты".

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

Заключение

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

В данной статье мы разберем, как удалить дубликаты в Excel 2007, Excel 2010, Excel 2013 и Excel 2016. Вы узнаете несколько разных способов как найти и удалить дубликаты в Excel с или без первых вхождений, удалить дубликаты строк , как удалить дубликаты в столбце , как обнаружить абсолютные дубликаты и частичные совпадения. В статье рассмотрены следующие пункты:

Удалить дубликаты строк в Excel с помощью функции «Удалить дубликаты»

Если вы используете последними версиями Excel 2007, Excel 2010, Excel 2013 или Excel 2016, у вас есть преимущество, потому что эти версии содержат встроенную функцию для поиска и удаления дубликатов - функцию Удалить дубликаты .

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

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

1. Для начала выберите диапазон, в котором вы хотите удалить дубликаты . Чтобы выбрать всю таблицу, нажмите Ctrl+A .
2. Далее перейдите на вкладку «ДАННЫЕ » --> группа «Работа с данными » и нажмите кнопку «Удалить дубликаты ».

Удалить дубликаты в Excel - Функция Удалить дубликаты в Excel

1. Откроется диалоговое окно «Удалить дубликаты ». Выберите столбцы для проверки дубликатов и нажмите «ОК ».

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

Удалить дубликаты в Excel - Выбор столбца(ов), который вы хотите проверить на наличие дубликатов

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

Удалить дубликаты в Excel - Сообщение о том, сколько было удалено дубликатов

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

Удалить дубликаты, скопировав уникальные записи в другое место

Другой способ удалить дубликаты в Excel - это разделение уникальных значений и копирование их на другой лист или в выбранный диапазон на текущем листе. Разберем этот способ.

1. Выберите диапазон или всю таблицу, которую вы хотите удалить дубликаты .
2. Перейдите во вкладку «ДАННЫЕ » --> группа «Сортировка и фильтр » и нажмите кнопку «Дополнительно ».

Удалить дубликаты в Excel - Использование дополнительного фильтра для удаления дубликатов

1. В диалоговом окне «Расширенный фильтр » выполните следующие действия:

• Выберите пункт «скопировать результат в другое место ».
• Проверьте, отображается ли правильный диапазон в Исходном диапазоне . Это должен быть диапазон, выбранный на шаге 1.
• В поле Поместить результат в диапазон введите диапазон, в котором вы хотите скопировать уникальные значения (на самом деле достаточно выбрать верхнюю левую ячейку диапазона назначения).
• Выберите Только уникальные записи

Удалить дубликаты в Excel - Фильтр дубликатов

1. Наконец, нажмите «ОК ». Excel удалит дубликаты и скопирует уникальные значения в новое указанное место:

Удалить дубликаты в Excel - Уникальные записи, скопированные из другого места

Таким образом вы получаете новые данные, на основе указанных, но с удаленными дубликатами .

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

Удалить дубликаты строк в Excel с помощью формул и фильтра

Еще один способ удалить дубликаты в Excel - это определить их с помощью формулы, отфильтровать и удалить дубликаты строк .

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

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

Формулы для поиска дубликатов в 1 столбце

• Дубликаты за исключением 1-го вхождения:

ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2; $A2)>1; "Дубликат"; "")

• Дубликаты с 1-го вхождения:

ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$10; $A2)>1; "Дубликат"; "Уникальное")

Где ячейка A2 является первой, а A10 - последней ячейкой диапазона для поиска дубликатов.

Формулы для поиска дубликатов строк

• Дубликаты строк, кроме 1-го вхождения. То есть, если в результате есть две или более одинаковых строки, то первая из них будет отмечена, как уникальная, а все последующие как дубликаты:

ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A2; $A2; $B$2:$B2; $B2; $C$2:$C2; $C2)>1;"Дубликат строки"; "Уникальное")

• Дубликаты строк с 1-ым вхождением. В данном случае, если в результате поиска есть две или более одинаковых строк, то все они будут отмечены как дубликаты:

ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A$10; $A2; $B$2:$B$10; $B2; $C$2:$C$10; $C2)>1; "Дубликат строки"; "Уникальное")

Где A, B и C - столбцы, подлежащие проверке на дубликаты.

Например, так вы можете идентифицировать дубликаты строк , за исключением 1-го вхождения:

Удалить дубликаты в Excel - Формула для идентификации дубликатов строк за исключением первых случаев

1. Выберите любую ячейку в своей таблице и примените автоматический фильтр, нажав кнопку «Фильтр » на вкладке «ДАННЫЕ », или «Сортировка и фильтр » --> «Фильтр » на вкладке «ГЛАВНАЯ ».

Удалить дубликаты в Excel - Применение фильтра к ячейкам (Вкладка ДАННЫЕ)

1. Отфильтруйте дубликаты строк , щелкнув стрелку в заголовке столбца «Дубликаты », а затем установите флажок «Дубликат строки ».

Удалить дубликаты в Excel - Фильтр дубликатов строки

1. И, наконец, удалите дубликаты строк . Чтобы сделать это, выберите отфильтрованные строки, переместив указатель мыши на номера строк, щелкните по ним правой кнопкой мыши и выберите «Удалить строку » в контекстном меню. В данном случае для удаления дубликатов не стоит пользоваться клавишей «Delete » на клавиатуре, потому что нам необходимо удалить целые строки, а не только содержимое ячеек:

Удалить дубликаты в Excel - Фильтрация и удаление дубликатов строк

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

Задачу удаления дубликатов или повторяющихся значений в Excel можно решать различными способами. В Excel 2007 и выше удалить дубликаты можно стандартными средствами, в Excel 2003 такие средства отсутствуют, но задача решается при помощи VBA (Visual Basic for Application).

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

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

Option Explicit Sub Udalenie_Dublikatov_Znachenij() "макрос удаляет значения ячеек, если находит дубликаты Dim iCount As Long, i As Long, j As Long, k As Long Dim Str1 As String, Str2 As String k = 1 iCount = Selection.Cells.Count For i = k To iCount Str1 = CStr(Selection.Cells(i).Value) If Str1 <> <> j And Str1 = Str2 Then Selection.Cells(j).ClearContents Next j End If Next i End Sub

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

Option Explicit Sub Udalenie_Dublikatov_Yacheek() "макрос удаляет ячейки, если находит дубликаты Dim iCount As Long, i As Long, j As Long, k As Long Dim Str1 As String, Str2 As String Dim Group As Range k = 1 iCount = Selection.Cells.Count For i = k To iCount Str1 = CStr(Selection.Cells(i).Value) If Str1 <> "" Then For j = i To iCount Str2 = CStr(Selection.Cells(j).Value) If i <> j And Str1 = Str2 Then If Group Is Nothing Then _ Set Group = Selection.Cells(j) Else Set Group = Union(Group, Selection.Cells(j)) End If Next j End If Next i On Error Resume Next Group.Delete Shift:=xlUp End Sub

Для того чтобы ячейки удалялись со сдвигом влево, необходимо в предпоследней строке вместо xlUp написать xlToLeft.

Удаление дубликатов в Excel 2007/2010/2013

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

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

Если запустить макрорекордер и записать действие, закрепленное за кнопкой "Удалить дубликаты", получится макрос, программный код которого приведен ниже. Этот макрос удаляет дубликаты в диапазоне A1:A20.

Sub Udalenie_Dublikatov() " макрос удаляет дубликаты (повторяющиеся значения) в диапазоне A1:A20 активного рабочего листа ActiveSheet.Range("$A$1:$A$20").RemoveDuplicates Columns:=1, Header:=xlNo End Sub

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

Дубликаты данных в 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

Представим, что у нас есть данные продаж как в таблице ниже:

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

• Выделите данные таблицы;
• Перейдите на Панели инструментов во вкладку “Данные” – > раздел “Работа с данными” -> “Удалить дубликаты”:

• В диалоговом окне “Удалить дубликаты” поставьте флажок напротив пункта “Мои данные содержат заголовки” если в выделенном вами диапазоне есть заголовки. Для того, чтобы удалить дубликаты во всех столбцах кроме даты, оставьте поле с датой пустым:

• Нажмите “ОК”

После этого система удалит строки с данными, в которых дублируются значения в колонках “Имя”, “Регион”, “Продажи”.

Как удалить дублирующие строки с данными в Эксель

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

• Выделите данные таблицы;
• Перейдите на Панели инструментов во вкладку “Данные” – > раздел “Работа с данными” -> “Удалить дубликаты”:

• В диалоговом окне “Удалить дубликаты” поставьте флажок напротив пункта “Мои данные содержат заголовки” если в выделенном вами диапазоне есть заголовки. Для того чтобы система сравнила внутри таблицы строки, важно убедиться что отмечены все столбцы в диалоговом окне:

• Нажмите “ОК”

Используйте вышеупомянутые методы для очистки данных и избавления от дубликатов.