Как убрать дубликаты в Excel: 4 способа от простого к сложному
Дубликаты в таблице - классика. Скопировал данные из двух источников, выгрузил из CRM, собрал опрос - и вот в списке один и тот же человек или товар встречается дважды, а то и пять раз.
Хорошая новость: Excel умеет убирать повторы несколькими способами. Какой выбрать - зависит от задачи.
Способ 1. Кнопка "Удалить дубликаты" - быстро и просто
Это встроенный инструмент Excel. Подходит в большинстве случаев и занимает буквально 10 секунд.
Как сделать:
- Кликните на любую ячейку внутри таблицы
- Перейдите на вкладку Данные в верхнем меню
- Нажмите кнопку Удалить дубликаты
- Откроется окно с галочками по столбцам. Здесь нужно решить: по каким столбцам считать строку дублирующейся
- Нажмите ОК
Excel покажет сообщение - сколько дубликатов удалено и сколько уникальных строк осталось.
Важный момент про галочки столбцов. Если отметить все столбцы - Excel удалит только полностью одинаковые строки. Если отметить только один столбец, например "Email" - удалит все строки где email повторяется, даже если имена разные. Выбирайте под свою задачу.
Минус способа: отменить нельзя после закрытия файла. Перед удалением сделайте копию листа - просто кликните правой кнопкой на ярлык листа внизу и выберите "Переместить или скопировать".
Способ 2. Условное форматирование - сначала посмотреть, потом решить
Иногда дубликаты нужно не сразу удалять, а сначала проверить - вдруг это не ошибка, а нормальные данные. Условное форматирование покрасит повторяющиеся ячейки, и вы сами решите что с ними делать.
Как сделать:
- Выделите столбец или диапазон, в котором ищете дубликаты
- Вкладка Главная - Условное форматирование - Правила выделения ячеек - Повторяющиеся значения
- Выберите цвет заливки и нажмите ОК
Все дубликаты подсветятся. Теперь можно отсортировать таблицу по цвету: вкладка Данные - Сортировка - сортировать по цвету ячейки. Все дубликаты окажутся рядом - легко просмотреть и удалить вручную.
Когда это удобно: когда данные важные и вы хотите сначала глазами проверить что удаляется.
Способ 3. Формула СЧЁТЕСЛИ - найти дубликаты без удаления
Этот способ не удаляет, а помечает дубликаты в отдельном столбце. Полезно когда нужно оставить первое вхождение и удалить только повторы.
Как сделать:
Добавьте пустой столбец рядом с таблицей. Назовите его "Дубликат". В первой строке данных введите формулу:
=СЧЁТЕСЛИ($A$2:$A2;A2)>1
Здесь $A$2:$A2 - диапазон от первой строки до текущей, A2 - проверяемая ячейка.
Протяните формулу вниз на все строки. Там где написано ИСТИНА - это дубликат (уже встречался выше). Где ЛОЖЬ - первое уникальное вхождение.
Дальше: отфильтруйте по значению ИСТИНА и удалите эти строки.
Плюс способа: первое вхождение остаётся, удаляются только повторные. Способ 1 удаляет непредсказуемо какое из повторений оставить.
Способ 4. Power Query - для больших таблиц и регулярной задачи
Если вы регулярно получаете данные с дубликатами - например, каждую неделю выгружаете отчёт - Power Query сэкономит кучу времени. Настраиваете один раз, потом просто обновляете.
Как сделать:
- Кликните на любую ячейку таблицы
- Вкладка Данные - Получить данные - Из таблицы/диапазона
- Откроется редактор Power Query
- Вкладка Главная - Удалить строки - Удалить дубликаты
- Нажмите Закрыть и загрузить
Excel создаст новый лист с очищенной таблицей. Исходные данные останутся нетронутыми.
В следующий раз когда придут новые данные - просто вставьте их на исходный лист и нажмите Обновить на листе с результатом. Power Query всё сделает сам.
Power Query есть в Excel 2016 и новее. В старых версиях его нужно устанавливать как надстройку.
Перед тем как начать.
Все способы ниже протестированы в Excel 2016, 2019, 2021 и Microsoft 365. В Excel 2013 и старше Power Query недоступен - там только способы 1-3.
Перед удалением дубликатов обязательно сделайте резервную копию листа: правая кнопка мыши на ярлыке листа внизу - "Переместить или скопировать" - поставьте галочку "Создать копию".
