Как в Excel удалить дубликаты данных. Удалить дубликаты в Excel

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

Вариант 1: Команда Удалить дубликаты в Excel

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

Перейдите на вкладку Date (Данные), а затем нажмите команду Remove Duplicates (Удалить дубликаты), как показано ниже.

Появится небольшое диалоговое окно Remove Duplicates (Удалить дубликаты). Можно заметить, что выделение первой строки снимается автоматически. Причиной тому является флажок, установленный в пункте My data has headers (Мои данные содержат заголовки).

В нашем примере нет заголовков, поскольку таблица начинается с 1-й строки. Поэтому снимем флажок. Сделав это, Вы заметите, что вся таблица снова выделена, а раздел Columns (Колонны) изменится с dulpicate на Column A , B и С .

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

Вариант 2: Расширенный фильтр

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

Затем перейдите на вкладку Data (Данные), в группе команд Sort & Filter (Сортировка и фильтр) нажмите команду Advanced (Дополнительно), как показано ниже. Если Вы используете Excel 2003, вызовите раскрывающееся меню Data (Данные), выберите Filters (Фильтры), а затем Advanced Filters (Расширенные фильтры).

Теперь необходимо установить флажок Unique records only (Только уникальные записи).

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

Вариант 3: Замена

Этот метод удобно применять, когда необходимо найти повторяющиеся строки в небольших таблицах. Мы будем использовать инструмент Find and Replace (Поиск и замена), который встроен во все продукты Microsoft Office. Для начала необходимо открыть таблицу Excel, с которой планируется работать.

Открыв таблицу, выберите ячейку, содержимое которой требуется найти и заменить, и скопируйте ее. Для этого выделите нужную ячейку и нажмите сочетание клавиш Ctrl+C .

Скопировав слово, которое необходимо найти, воспользуйтесь сочетанием Ctrl+H , чтобы вызвать диалоговое окно Find and Replace (Поиск и замена). Вставьте скопированное слово с поле Найти , нажав Ctrl+V .

Нажмите кнопку Options (Параметры), чтобы открыть дополнительный список опций. Установите флажок у пункта Match entire cell contents (Ячейка целиком). Это необходимо сделать, поскольку в некоторых ячейках искомые слова находятся вместе с другими словами. Если не выбрать эту опцию, можно непреднамеренно удалить ячейки, которые требуется оставить. Убедитесь, что все остальные настройки соответствуют указанным на рисунке ниже.

Теперь необходимо ввести значение в поле Replace with (Заменить на). В данном примере мы воспользуемся цифрой 1 . Введя нужное значение, нажмите Replace All (Заменить все).

Можно заметить, что все значения duplicate в ячейках таблицы, будут заменены на 1 . Мы использовали значение 1 , так как оно небольшое и выделяется в тексте. Теперь Вы можете визуально определить строки, которые имеют повторяющиеся значения.

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

Определив строки с повторяющимся содержимым, поочередно выделите их, удерживая клавишу Ctrl .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Удаление повторяющихся значений в Excel (2007+)

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

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

Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.

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

2. Использование расширенного фильтра для удаления дубликатов

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

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

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

3. Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)

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

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

4. Использование сводных таблиц для определения повторяющихся значений

Воспользуемся уже знакомой нам таблицей с тремя столбцами и добавим четвертый, под названием Счетчик, и заполним его единицами (1). Выделяем всю таблицу и переходим по вкладке Вставка в группу Таблицы, щелкаем по кнопке Сводная таблица.

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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