Удалить дубликаты в одном столбце Excel проще всего штатным инструментом: выделите столбец и нажмите Данные → Удалить дубликаты — останется первое вхождение каждого значения. Чтобы получить список без повторов в соседнем столбце и не трогать исходные данные, в Excel 365 и 2021 есть формула =УНИК(A2:A8), а отметить повторы в любой версии можно формулой =СЧЁТЕСЛИ($A$2:A2;A2)>1. Удалить дубли без смещения соседних столбцов, неявные дубли и повторы с разными словоформами помогут процедуры !SEMTools.
Одна из наиболее популярных операций в Excel - удаление дублей. Но каждый вкладывает в это понятие что-то своё. Поэтому способов удалить повторяющиеся значения в Excel довольно много.
Почему так? Рассмотрим под микроскопом механику процесса удаления дубликатов:
- В любом удалении дублей есть объект (собственно, что мы считаем дублем). И это может быть:
- ячейка
- значение ячейки (это другое!)
- строка целиком
- Дублем кто-то считает все повторы кроме первого, а кто-то - все повторяющиеся значения, включая первое (технически ведь и оно является дубликатом остальных).
- Проблема неявных дублей, заключается она в том, какие допущения возможны при сравнении.
- Учитывать ли лишние пробелы в ячейках?
- удалять ли пунктуацию?
- что делать, если в ячейках те же слова, но в разном порядке?
- учитывать ли склонение, падеж, число и другие словоформы тех же слов?
Комбинации различных вариантов понимания процесса и порождают уйму способов удалить дубли. Постараюсь детально рассмотреть каждый из них и дать ответы на все вопросы.
Как удалить дубликаты в одном столбце Excel
Если столбец один, процедуру легко произвести с помощью штатного инструмента удаления дубликатов. Инструмент довольно удобен, когда нужно просто удалить дубликаты и не беспокоят остальные данные на листе:
- Выделите столбец или диапазон с данными.
- На вкладке «Данные» нажмите «Удалить дубликаты».
- Если рядом есть другие данные, Excel предложит расширить выделенный диапазон. Расширение удалит строки целиком; чтобы обработать только выделенный столбец, выберите вариант без расширения.
- В окне отметьте столбцы для сравнения и галочку «Мои данные содержат заголовки», если первая строка — заголовок. Нажмите «ОК».
- Excel сообщит, сколько повторяющихся значений удалено и сколько уникальных осталось.
Но проблема этого инструмента в том, что он удаляет либо ячейки, либо строки целиком! Если же столбцов с данными несколько, это приведет к тому, что оставшиеся ячейки сместятся относительно других столбцов. Если это ваш случай, смотрите статью о том, как удалить дубликаты без смещения остальных значений. Иными словами, как удалить лишь дублирующиеся значения ячеек. А не сами дублирующиеся ячейки.
Удалить повторы формулой УНИК
Инструмент «Удалить дубликаты» меняет исходные данные, и при добавлении новых строк его приходится запускать заново. Формула УНИК (Excel 365 и 2021) строит список без повторов в соседнем столбце и обновляется сама. Результат «разливается» вниз на столько строк, сколько уникальных значений нашлось. В старых версиях повторы можно отметить формулой с СЧЁТЕСЛИ и затем отфильтровать.
| A | B | C | |
|---|---|---|---|
| 1 | Город | Повтор? | Без дублей |
| 2 | Москва | =ЕСЛИ(СЧЁТЕСЛИ($A$2:A2;A2)>1;"повтор";"") | Москва=УНИК(A2:A8) |
| 3 | Казань | =ЕСЛИ(СЧЁТЕСЛИ($A$2:A3;A3)>1;"повтор";"") | Казань |
| 4 | Москва | повтор=ЕСЛИ(СЧЁТЕСЛИ($A$2:A4;A4)>1;"повтор";"") | Сочи |
| 5 | Сочи | =ЕСЛИ(СЧЁТЕСЛИ($A$2:A5;A5)>1;"повтор";"") | Омск |
| 6 | Казань | повтор=ЕСЛИ(СЧЁТЕСЛИ($A$2:A6;A6)>1;"повтор";"") | |
| 7 | Москва | повтор=ЕСЛИ(СЧЁТЕСЛИ($A$2:A7;A7)>1;"повтор";"") | |
| 8 | Омск | =ЕСЛИ(СЧЁТЕСЛИ($A$2:A8;A8)>1;"повтор";"") |
Формула в C2 сама заполнила C3:C5 — это «динамический массив». СЧЁТЕСЛИ с растущим диапазоном $A$2:A2 считает вхождения только до текущей строки, поэтому первое вхождение не помечается, а повторы — помечаются.
В английском Excel: =UNIQUE(A2:A8)
Чтобы оставить только значения, которые встречаются ровно один раз (удалить все повторяющиеся, включая первое вхождение), укажите третий аргумент УНИК: =УНИК(A2:A8;;ИСТИНА) — в примере останутся только «Сочи» и «Омск». В старых версиях то же покажет условие =СЧЁТЕСЛИ($A$2:$A$8;A2)=1.
Расширенный фильтр: только уникальные записи
Способ для любой версии Excel, который не меняет исходный столбец: Данные → Сортировка и фильтр → Дополнительно, выберите «Скопировать результат в другое место», укажите ячейку для результата и поставьте галочку «Только уникальные записи».
Удалить дубли ячеек другого столбца
Чтобы сравнить два столбца и найти между ними дублирующиеся значения, есть пара штатных возможностей в Excel. Как ими пользоваться для удаления повторов, описано в соответствующих разделах:
- Функция ВПР
- Условное форматирование
В своей надстройке я решил максимально упростить данный процесс. Смотрите, как это делать с её помощью:
Проблема с неявными дублями
Зачастую данные в ячейках бывают практически неразличимы визуально, или могут являться эквивалентными с точки зрения человека, но компьютер штука точная, и даже лишний пробел для него разница в 1 символ, а значит ячейки не равны. При этом регистр он по умолчанию все же игнорирует :)
Когда значения ячеек отличаются лишь пунктуацией или порядком слов, они называются неявными дублями. Смотрите статью о том, как найти и удалить неявные дубли в Excel.
Дубли с разными словоформами: лемматизация
Когда значения ячеек в целом состоят из тех же слов, но слова отличаются числом, падежом, временем и т.д., иными словами, слова различаются их словоформами, можно прибегнуть к такому процессу, как лемматизация текста.
Соответствующий инструмент-лемматизатор доступен в моей надстройке !SEMTools и позволяет вернуть всем словам одну и ту же для всех начальную форму. Что, в свою очередь, позволит найти уже по ним повторяющиеся значения и удалить их. Смотрите соответствующую статью: Лемматизация в Excel.
Почему дубликаты не удаляются
- Значения выглядят одинаково, но отличаются пробелами. «Москва» и «Москва » для Excel разные. Сначала удалите лишние пробелы, в том числе неразрывные.
- Одинаковые даты или числа не считаются дублями. Инструмент сравнивает значения так, как они отображаются: дата в формате «08.03.2026» и «8 мар 2026» для него разная. Приведите столбец к одному формату.
- Число и число, сохранённое как текст. «15» текстом и 15 числом — разные значения для формул. Преобразуйте текст в числа.
- Удалились строки целиком и сбились соседние столбцы. При удалении было выбрано «расширить выделенный диапазон». Отмените действие (Ctrl+Z) и повторите без расширения или удалите дубли без смещения.
- УНИК возвращает #ПЕРЕНОС!. Ячейки, куда «разливается» результат, не пустые. Очистите место под список.
- Формула УНИК даёт #ИМЯ?. Функции нет в Excel 2019 и раньше. Используйте инструмент «Удалить дубликаты» или расширенный фильтр.
Удаление дубликатов в Excel — частые вопросы
Как удалить дубликаты в одном столбце Excel?
Выделите столбец и нажмите «Данные → Удалить дубликаты». Если рядом есть данные, выберите вариант без расширения выделенного диапазона, чтобы обработать только этот столбец.
Как удалить повторяющиеся значения, не меняя исходные данные?
Формулой =УНИК(A2:A8) в Excel 365 и 2021 или расширенным фильтром с галочкой «Только уникальные записи» в любой версии.
Как удалить все повторяющиеся значения, включая первое?
Формулой =УНИК(A2:A8;;ИСТИНА) — останутся только значения, которые встречаются один раз.
Учитывает ли Excel регистр при удалении дубликатов?
Нет: «москва» и «Москва» считаются дублями. А вот лишний пробел или другой формат даты делает значения разными.
Удалить дубли в столбце без смещения соседних данных, найти неявные дубли с разным порядком слов или пунктуацией и повторы с разными словоформами можно в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как удалить дубликаты в Excel
| Задача | Способ |
|---|---|
| Удалить повторы в столбце на месте | Данные → Удалить дубликаты |
| Список без повторов рядом, с автообновлением | =УНИК(A2:A8) (Excel 365, 2021) |
| Только значения, встречающиеся один раз | =УНИК(A2:A8;;ИСТИНА) |
| Отметить повторы в любой версии | =СЧЁТЕСЛИ($A$2:A2;A2)>1 |
| Удалить дубли без смещения, неявные дубли | Процедуры !SEMTools |
Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: найти дубликаты в Excel и удалить неявные дубли.