Как удалить дубликаты в Excel и «Google Таблицах»: инструкция со скриншотами

Если в таблице с сотней или тысячей строк есть повторяющиеся данные, главное — не начинать искать и удалять их вручную. В Excel и «Google Таблицах» есть инструменты, которые позволяют сделать это автоматически за несколько секунд.

В инструкции объясняем:

• как найти и выделить дубликаты в Excel;
• как удалить дубликаты в Excel;
• как скрыть дубликаты в Excel, не удаляя их;
• как удалить дубликаты в «Google Таблицах»;
• что делать, если таблицы не помещаются в Excel или «Google Таблицы», а удалить дубликаты надо;
• как узнать больше о работе в Excel.

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

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

Разберём на примере, как это сделать.

Допустим, у нас есть список сотрудников компании. Его собирали из нескольких документов, поэтому фамилии могут дублироваться.

Чтобы найти дублирующиеся фамилии, воспользуемся опцией условного форматирования.

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

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

Нажимаем «ОК».

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

Если нужно убрать форматирование, нажмите «Условное форматирование» → «Удалить правила» → «Удалить правила из выделенных ячеек».

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

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

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

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

Удаляем дубликаты из одного столбца

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

В появившемся окне нужно выбрать, какие столбцы таблицы Excel будет проверять. В нашем случае у таблицы столбец один, поэтому жмём «ОК», ничего не изменяя.

Excel покажет, сколько повторяющихся значений он нашёл и удалил, сколько уникальных значений осталось. Изучаем и жмём «ОК».

Готово — в таблице остались только уникальные значения, а ячейки с красной заливкой пропали.

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

Удаляем дубликаты сразу по нескольким столбцам

В некоторых случаях найти дубликаты по одному столбцу бывает недостаточно.

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

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

Выберем любую ячейку таблицы, перейдём во вкладку «Данные» и нажмём кнопку «Удалить дубликаты». В появившемся меню выберем названия столбцов, которые нужно учесть при поиске дубликатов. Для нашего примера выберем все столбцы.

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

Готово — алгоритм проанализировал все столбцы и удалил строки, в которых дублировались одновременно все значения.

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

Снова вызовем окно удаления дубликатов и уберём галочку с заголовка «Цвет». Алгоритм проведёт новый анализ, в котором не будет учитывать этот параметр. То есть, если два автомобиля совпадают по всем параметрам, кроме цвета, Excel всё равно посчитает их одинаковыми и удалит.

В результате из таблицы пропало ещё две строки.

Как скрыть дубликаты в Excel, не удаляя их

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

Выделим любую ячейку таблицы, перейдём во вкладку «Данные» и нажмём кнопку «Фильтр».

Дальше нажимаем кнопку «Дополнительно». В появившемся окне проверяем, что в строке «Диапазон списка» был выбран диапазон нашей таблицы. Затем ставим галочку напротив строки «Только уникальные записи» и жмём «ОК».

Готово. Excel проанализировал дубликаты по всем столбцам таблицы и скрыл их, не удаляя, — это видно по нумерации строк.

Чтобы просмотреть скрытые строки, наведите курсор мыши на жирную полоску между их номерами — например, на полоску между строками 9 и 12; зажмите курсор и потяните его вниз. Скрытые строки будут появляться по одной.

Чтобы полностью отменить фильтрацию, кликните в меню по кнопке «Фильтр» повторно.

Как удалить дубликаты в «Google Таблицах»

Принцип такой же, как в Excel. Выбираем любую ячейку внутри таблицы, которую нужно очистить от дубликатов. Затем вызываем меню «Данные» и нажимаем «Очистка данных» → «Удалить повторы».

В появившемся окне выбираем столбцы, которые нужно проанализировать на наличие дубликатов, и нажимаем «Удалить повторы».

Так же, как в Excel, алгоритм «Google Таблиц» стёр только те строки, значения в которых совпали одновременно во всех выбранных столбцах.

Что делать, если данные не помещаются в Excel- или Google-таблицу, а удалить дубликаты надо? Это происходит, например, когда информацию собирают из разных источников, а количество строк в итоговой таблице больше, чем могут вместить Excel или «Google Таблицы».

В этом случае можно использовать надстройку Power Query, встроенную в Excel. С помощью неё можно выгружать таблицы из источников разных форматов, приводить их к удобному виду — в том числе удалять дубликаты — и импортировать дальше. При этом в Power Query нет ограничений по объёму данных, с которыми он может работать без потери производительности.

Подробнее о том, как работать с надстройкой Power Query, мы рассказывали в этой инструкции. Изучите её, если работаете с большими базами данных.