В данной статье мы разберем, как удалить дубликаты в Excel 2007, Excel 2010, Excel 2013 и Excel 2016. Вы узнаете несколько разных способов как найти и удалить дубликаты в Excel с или без первых вхождений, удалить дубликаты строк , как удалить дубликаты в столбце , как обнаружить абсолютные дубликаты и частичные совпадения. В статье рассмотрены следующие пункты:
Удалить дубликаты строк в Excel с помощью функции «Удалить дубликаты»
Если вы используете последними версиями Excel 2007, Excel 2010, Excel 2013 или Excel 2016, у вас есть преимущество, потому что эти версии содержат встроенную функцию для поиска и удаления дубликатов - функцию Удалить дубликаты .
Эта функция позволяет находить и удалять абсолютные дубликаты (ячейки или целые строки), а также частично соответствующие записи (строки, которые имеют одинаковые значения в указанном столбце или столбцах). Разберем на примере, как пошагово использовать функцию Удалить дубликаты в Excel .
Примечание . Поскольку функция Удалить дубликаты навсегда удаляет идентичные записи, рекомендуется создать копию исходных данных перед удалением повторяющихся строк.
- Для начала выберите диапазон, в котором вы хотите удалить дубликаты . Чтобы выбрать всю таблицу, нажмите Ctrl+A .
- Далее перейдите на вкладку «ДАННЫЕ » --> группа «Работа с данными » и нажмите кнопку «Удалить дубликаты ».
Удалить дубликаты в Excel - Функция Удалить дубликаты в Excel
- Откроется диалоговое окно «Удалить дубликаты ». Выберите столбцы для проверки дубликатов и нажмите «ОК ».
- Чтобы удалить дубликаты строк , имеющие полностью одинаковые значения во всех столбцах, оставьте флажки рядом со всеми столбцами, как показано на изображении ниже.
- Чтобы удалить частичные дубликаты на основе одного или нескольких ключевых столбцов, выберите только соответствующие столбцы. Если в вашей таблице много столбцов, лучше сперва нажать кнопку «Снять выделение », а затем выбрать столбцы, которые вы хотите проверить на предмет дубликатов.
- Если в вашей таблице нет заголовков , уберите флаг с поля «Мои данные содержат заголовки » в правом верхнем углу диалогового окна, которое обычно выбирается по умолчанию.
Удалить дубликаты в Excel - Выбор столбца(ов), который вы хотите проверить на наличие дубликатов
Готово! Все дубликаты строк в выбранном диапазоне удалены, и отображается сообщение, указывающее, сколько было удалено дубликатов записей и сколько уникальных значений осталось.
Удалить дубликаты в Excel - Сообщение о том, сколько было удалено дубликатов
Функция Удалить дубликаты в Excel удаляет 2-ой и все последующие дубликаты экземпляров, оставляя все уникальные строки и первые экземпляры одинаковых записей. Если вы хотите удалить дубликаты строк , включая первые вхождения, т.е. если вы ходите удалить все дублирующие ячейки. Или в другом случае, если есть два или более дубликата строк, и первый из них вы хотите оставить, а все последующие дубликаты удалить , то используйте одно из следующих решений описанных в .
Удалить дубликаты, скопировав уникальные записи в другое место
Другой способ удалить дубликаты в Excel - это разделение уникальных значений и копирование их на другой лист или в выбранный диапазон на текущем листе. Разберем этот способ.
- Выберите диапазон или всю таблицу, которую вы хотите удалить дубликаты .
- Перейдите во вкладку «ДАННЫЕ » --> группа «Сортировка и фильтр » и нажмите кнопку «Дополнительно ».
Удалить дубликаты в Excel - Использование дополнительного фильтра для удаления дубликатов
- В диалоговом окне «Расширенный фильтр » выполните следующие действия:
- Выберите пункт «скопировать результат в другое место ».
- Проверьте, отображается ли правильный диапазон в Исходном диапазоне . Это должен быть диапазон, выбранный на шаге 1.
- В поле Поместить результат в диапазон введите диапазон, в котором вы хотите скопировать уникальные значения (на самом деле достаточно выбрать верхнюю левую ячейку диапазона назначения).
- Выберите Только уникальные записи
Удалить дубликаты в Excel - Фильтр дубликатов
- Наконец, нажмите «ОК ». Excel удалит дубликаты и скопирует уникальные значения в новое указанное место:
Удалить дубликаты в Excel - Уникальные записи, скопированные из другого места
Таким образом вы получаете новые данные, на основе указанных, но с удаленными дубликатами .
Обратите внимание, что расширенный фильтр позволяет копировать отфильтрованные значения в другое место только на активном листе.
Удалить дубликаты строк в Excel с помощью формул и фильтра
Еще один способ удалить дубликаты в Excel - это определить их с помощью формулы, отфильтровать и удалить дубликаты строк .
Преимуществом этого подхода является универсальность - он позволяет найти и удалить дубликаты в столбце или дублировать строки на основе значений в нескольких столбцах. Недостатком является то, что вам нужно будет запомнить несколько повторяющихся формул.
- В зависимости от вашей задачи используйте одну из следующих формул для поиска дубликатов.
Формулы для поиска дубликатов в 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 - Формула для идентификации дубликатов строк за исключением первых случаев
- Выберите любую ячейку в своей таблице и примените автоматический фильтр, нажав кнопку «Фильтр » на вкладке «ДАННЫЕ », или «Сортировка и фильтр » --> «Фильтр » на вкладке «ГЛАВНАЯ ».
Удалить дубликаты в Excel - Применение фильтра к ячейкам (Вкладка ДАННЫЕ)
- Отфильтруйте дубликаты строк , щелкнув стрелку в заголовке столбца «Дубликаты », а затем установите флажок «Дубликат строки ».
Удалить дубликаты в Excel - Фильтр дубликатов строки
- И, наконец, удалите дубликаты строк . Чтобы сделать это, выберите отфильтрованные строки, переместив указатель мыши на номера строк, щелкните по ним правой кнопкой мыши и выберите «Удалить строку » в контекстном меню. В данном случае для удаления дубликатов не стоит пользоваться клавишей «Delete » на клавиатуре, потому что нам необходимо удалить целые строки, а не только содержимое ячеек:
Удалить дубликаты в Excel - Фильтрация и удаление дубликатов строк
Ну, теперь вы узнали несколько способов, как удалить дубликаты в Excel . И можете пользоваться одним из них в зависимости от вашей ситуации.
В сегодняшних Excel файлах дубликаты встречаются повсеместно. К примеру, когда вы создаете составную таблицу из других таблиц, вы можете обнаружить в ней повторяющиеся значения, или в файле с общим доступом внесли одинаковые данные два разных пользователя, что привело к задвоению и т.д. Дубликаты могут возникнуть в одном столбце, в нескольких столбцах или даже во всем листе. В Microsoft Excel реализовано несколько инструментов поиска, выделения и, при необходимости, удаления повторяющихся значений. Ниже описаны основные методики определения дубликатов в Excel.
1. Удаление повторяющихся значений в Excel (2007+)
Предположим, у вас имеется таблица, состоящая из трех столбцов, в которой присутствуют одинаковые записи и вам необходимо избавится от них. Выделяем область таблицы, в которой хотите удалить повторяющиеся значения. Вы можете выделить один или несколько столбцов, или всю таблицу целиком. Переходим по вкладке Данные в группу Работа с данными, щелкаем по кнопке Удалить дубликаты.
Если в каждом столбце таблицы имеется заголовок, установить маркер Мои данные содержат заголовки. Также проставляем маркеры напротив тех столбцов, в которых требуется произвести поиск дубликатов.
Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.
Данная функция предназначена для удаления записей, которые полностью дублируют строки в таблице. Если вы выделили не все столбцы для определения дубликатов, строки с повторяющимися значениями также будут удалены.
2. Использование расширенного фильтра для удаления дубликатов
Выберите любую ячейку в таблице, перейдите по вкладке Данные в группу Сортировка и фильтр, щелкните по кнопке Дополнительно.
В появившемся диалоговом окне Расширенный фильтр, необходимо установить переключатель в положение скопировать результат в другое место, в поле Исходный диапазон указать диапазон, в котором находится таблица, в поле Поместить результат в диапазон указать верхнюю левую ячейку будущей отфильтрованной таблицы и установить маркер Только уникальные значения. Щелкаем ОК.
На месте, указанном для размещения результатов работы расширенного фильтра, будет создана еще одна таблица, но уже с отфильтрованными, по уникальным значениям, данными.
3. Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)
Выделяем таблицу, в которой необходимо обнаружить повторяющиеся значения. Переходим по вкладкеГлавная в группу Стили, выбираем Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения.
В появившемся диалоговом окне Повторяющиеся значения, необходимо выбрать формат выделения дубликатов. У меня по умолчанию установлено светло-красная заливка и темно-красный цвет текста. Обратите внимание, в данном случае Excel будет сравнивать на уникальность не всю строку таблицы, а лишь ячейку столбца, поэтому если у вас имеются повторяющиеся значения только в одном столбце, Excel отформатирует их тоже. На примере вы можете увидеть, как Excel залил некоторые ячейки третьего столбца с именами, хотя вся строка данной ячейки таблицы уникальна.
4. Использование сводных таблиц для определения повторяющихся значений
Воспользуемся уже знакомой нам таблицей с тремя столбцами и добавим четвертый, под названиемСчетчик, и заполним его единицами (1). Выделяем всю таблицу и переходим по вкладке Вставка в группу Таблицы, щелкаем по кнопке Сводная таблица.
Создаем сводную таблицу. В поле Название строк помещаем три первых столбца, в поле Значенияпомещаем столбец со счетчиком. В созданной сводной таблице, записи со значением больше единицы будут дубликатами, само значение будет означать количество повторяющихся значений. Для большей наглядности, можно отсортировать таблицу по столбцу Счетчик, чтобы сгруппировать дубликаты.
Всем, кто работает с электронными таблицами, хоть раз приходилось сталкиваться с задачей удаления дубликатов. Excel для этого предоставляет свои штатные средства, их мы и рассмотрим в этой статье. Сразу скажу, что освещать буду методы простые и безотказные, углубляться в формулы я буду в другой статье - это более тонкая работа.
Итак, давайте сначал определимся, что нам нужно. Например, у нас есть таблица данных, где есть совпадения, причем, возможно, по нескольким столбцам. Какие есть задачи:
- Поиск уникальных значений/Поиск дубликатов.
- Получение уникальных значений.
- Получение значений, где есть дублирующие записи.
Метод первый - для Excel 2007 и выше.
Слава богу, для обладателей версии Excel 2007 и выше можно ни о чем не думать. Начиная с этой версии, появилось штатное средство - "Удалить дубликаты " на вкладке Данные.
Пользоваться им просто:
Плюс этого метода - скорость и простота. Надо получить только уникальные значения - решения в два клика, получите и распишитесь.
Минус в том, что если мы более глубоко работаем над таблицей, то мы не сможем понять, какие данные были дублированы (а соответственно, не определим причину дубляжа), а это порой не менее важнее получения нормального списка.
Метод второй - расширенный фильтр.
Этот метод доступен уже и для версии Excel 2003, ниже не проверял, но насколько помню эта версия не сильно прогрессировала по сравнению с 2000й версией.
Этот метод заставит чуть больше повозиться, но и информации можно из него выдоить соответственно. Используется . Итак:
Плюс этого метода в том, что мы имеем выбор - удалять или не удалять дубликаты, а значит, можем работать и анализировать данные.
Когда возникает задача оптимизации базы данных или меняется ее структура, иногда появляется попутная задача организации уже накопленных данных. Хорошо, если таблица уже при разработке приведена в нормальную форму, и вся система организована так, что она не копит лишней дублирующей информации. Если же это не так, то при доработке такой системы хочется избавиться от всех избыточных данных и сделать все наиболее качественно.
В этой статье рассмотрим задачу удаления дублирующих строк в таблице БД. Сразу же отмечу, что речь идет о необходимости удалить именно повторяющиеся строки. Например, записи в таблице заказов с полями "код заказа", "код товара", "код покупателя", "дата заказа" могут различаться только кодом заказа, так как все же один покупатель в один день может заказать один и тот же товар несколько раз. А главный показатель здесь, что все правильно – наличие ключевого поля.
Если же мы видим таблицу, изобилующую повторяющимися полями, без явной необходимости каждой записи, то это именно то, что должно быть исправлено.
Пример явно избыточной таблицы:
Теперь рассмотрим, как можно решить эту проблему. Здесь можно применить несколько методов.
1. Можно написать функцию для сравнения и перебора всех данных. Это долго, да и писать код для одноразового использования не всегда хочется.
2. Другое решение – создать запрос на выборку с группировкой данных, так чтобы получить только уникальные строки:
SELECT country_id, city_name
FROM mytable
GROUP BY country_id, city_name
Получаем следующую выборку:
Затем, полученный набор данных записываем в другую таблицу.
3. В указанных решениях применяется дополнительный программный код или дополнительные таблицы. Однако, было бы удобней сделать все, используя только запросы SQL без дополнительных таблиц. И вот пример такого решения:
DELETE a.* FROM mytable a,
(SELECT
FROM mytable b
) c
WHERE
a.country_id = c.country_id
AND a.city_name = c.city_name
AND a.id > c.mid
После выполнения такого запроса в таблице останутся только уникальные записи:
Теперь разберемся подробнее, как все это работает. При запросе на удаление, необходимо задать условие, которое укажет какие данные нужно удалить, а какие оставить. Нам необходимо удалить все не уникальные записи. Т.е. если существует несколько одинаковых записей (одинаковые они, если у них равны значения country_id и city_name), то нужно взять одну из строк, запомнить ее код и удалить все записи с такими же значениями country_id и city_name, но другим кодом (id).
Строка SQL запроса:
DELETE a.* FROM mytable a,
указывает, что удаление будет производиться из таблицы mytable.
Затем запрос на выборку формирует вспомогательную таблицу, где мы группируем записи так, чтобы все записи были уникальными:
(SELECT
b.country_id, b.city_name, MIN(b.id) mid
FROM mytable b
GROUP BY b.country_id, b.city_name
) c
MIN(b.id) mid – формирует столбец mid (сокращение min id), в который вносятся минимальное значение id, в каждой подгруппе.
В результате получается таблица, содержащая уникальные записи и id первой строки для каждой группы дублирующих записей.
Теперь мы имеем две таблицы. Одну общую, содержащую все записи. Из нее будут удаляться лишние строки. Вторая содержит информацию о строках, которые нужно сохранить.
Остается только сформировать условие, где указывается: удалить нужно все строки, где совпадают поля country_id и city_name, а id совпадать не будет. В данном случае выбирается минимальное значение id, поэтому удаляются все записи, id которых больше чем выбранный во временную таблицу.
Стоит еще отметить, что описанную операцию можно выполнить при наличии в таблице ключевого поля. Если вдруг встретилась таблица без уникального идентификатора, то просто добавляем его:
ALTER TABLE ` mytable` ADD `id` INT(11) NOT NULL AUTO_INCREMENT , ADD PRIMARY KEY (`id`)
Выполнив такой запрос, получим дополнительный столбец, заполненный уникальными числовыми значениями для каждой строки таблицы.
Выполняем все необходимые действия. После того, как операция по очистке таблицы от дубликатов записей выполнена, это поле можно так же удалить.
Поиск и удаление вручную повторяющихся значений в таблице Excel, в особенности в больших документах, - весьма рискованное и нецелесообразное занятие. Не исключено, что вы, проверяя зрительно ячейки, можете пропустить дубли. И потом, тратится колоссальное количество времени на удаление каждой ячейки.
В этой статье мы рассмотрим, как можно удалить дубликаты в Excel, используя различные автоматизированные штатные функции.
Совет! Для удобства ознакомления со способами удаления повторяющихся строк, столбцов, значений ячеек, откройте в редакторе черновой шаблон или копию проекта, над которым работаете, чтобы по ошибке в ходе редактирования не потерять ценные данные и не нарушить формат оригинала.
Способ №1
1. Удерживая левую кнопку мышки, обозначьте курсором границы области таблицы (отдельные строки, столбцы), где необходимо убрать повторы. Либо выделите весь проект.
2. В меню редактора перейдите на вкладку «Данные».
3. В блоке «Работа с данными» нажмите кнопку «Удалить дубликаты».
4. В появившейся панели выберите столбцы, в которых нужно убрать одинаковые значения посредством установки меток. Если элементов много, используйте для быстрой настройки удаления опции «Выделить всё» и «Снять выделение». Нажмите «OK».
5. По завершении очистки строк и столбцов таблицы от дублей появится сообщение о том, сколько осталось уникальных значений.
Совет! Повторы сразу после удаления можно восстановить, щёлкнув в левом верхнем углу Excel значок «стрелочку влево».
Способ №2
1. Кликните по редактируемой таблице.
2. На вкладке «Данные», в блоке «Сортировка и фильтр», щёлкните подраздел «Дополнительно».
Если необходимо создать новую таблицу, содержащую только уникальные ячейки исходника:
1. В панели «Расширенный фильтр» клацните радиокнопку «Скопировать результат в другое место».
2. Нажмите кнопку, расположенную в правой части поля «Поместить результат в диапазон».
3. Клацните на свободном рабочем пространстве Excel, куда необходимо будет поместить отфильтрованную таблицу. После щелчка в поле появится код ячейки. Закройте его и перейдите в опции фильтра.
4. Кликните окошко «Только уникальные записи» и нажмите «OK».
5. После выполнения фильтрации в указанном месте появится версия исходной таблицы без повторов.
Чтобы отредактировать документ без создания копий:
- в панели «Расширенный фильтр» установите режим обработки «Фильтровать список на месте»;
- кликом мышки включите надстройку «Только уникальные записи»;
- клацните «OK».
