Как найти повторы в Excel формулой: пометить дубли
Для русского Excel
Для английского Excel, Google Таблиц и внутри файла .xlsx
Пример
| A | B | |
|---|---|---|
| 1 | Артикул | Отметка |
| 2 | 0457 | |
| 3 | 0912 | |
| 4 | 0457 | повтор |
Результат в ячейке B4 посчитан LibreOffice на сервере Формулника — формула из B2, протянутая до B4.

Как вставить
- В B2 вставьте формулу.
- Протяните вниз.
- Отфильтруйте столбец B по слову «повтор» — это строки-дубли.
Почему так
СЧЁТЕСЛИ считает, сколько раз значение встретилось от начала списка до текущей строки. Больше одного — повтор.
Другие случаи
Частые ошибки
Число и текст — разные значения; приведите столбец к одному типу.
«Сидоров » и «Сидоров» — разные; сначала СЖПРОБЕЛЫ.
Функции в этой формуле
Другие готовые формулы
Повторы в Excel: подсветить, пометить, найти по двум столбцам и сравнить два списка
Быстрее всего подсветить повторы без формул: «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения». Формула со страницы выше нужна, когда дубли надо отфильтровать или посчитать. Повтор по нескольким полям (ФИО и дата рождения, артикул и склад) ищется через СЧЁТЕСЛИМН.
У СЧЁТЕСЛИ есть ловушки: она не различает регистр, понимает * и ? как подстановочные знаки и сравнивает длинные числа-тексты только по первым 15 цифрам.
По шагам
- Сделайте копию листа: удаление дублей необратимо, а исходник понадобится для сверки.
- Нормализуйте данные:
=СЖПРОБЕЛЫ(A2)убирает пробелы по краям,=ПРОПИСН(A2)выравнивает регистр для визуальной проверки. - Подсветите повторы условным форматированием и оцените масштаб.
- Пометьте дубли формулой в соседнем столбце, отфильтруйте по пометке.
- Решите, что делать: удалить («Данные» → «Удалить дубликаты»), объединить суммы через сводную или оставить первое появление.
- Пересчитайте количество строк до и после — разница должна совпасть с числом помеченных.
Повтор по двум столбцам сразу
Однофамильцы — не дубли, а одинаковые ФИО и дата рождения — почти наверняка. ФИО в A, дата в B. Формула ниже возвращает ИСТИНА, если такая пара встречается больше одного раза; её же можно вставить в правило условного форматирования для подсветки строки целиком.
=СЧЁТЕСЛИМН($A$2:$A$500;A2;$B$2:$B$500;B2)>1Сравнить два списка: кто есть в обоих
Список клиентов на листе «Январь», второй — на «Февраль». На листе «Февраль» рядом с каждым именем: =ЕСЛИ(СЧЁТЕСЛИ(Январь!$A:$A;A2)>0;"был в январе";"новый"). Отфильтруйте «новый» — это пришедшие в феврале клиенты. Поменяйте листы местами — получите ушедших.
=ЕСЛИ(СЧЁТЕСЛИ(Январь!$A:$A;A2)>0;"был в январе";"новый")Длинные номера, штрихкоды и тексты длиннее 255 символов
Номера карт, штрихкоды и номера договоров по 16–20 цифр СЧЁТЕСЛИ сравнивает как числа с точностью 15 знаков — разные номера могут посчитаться одинаковыми. Та же функция ошибается на строках длиннее 255 символов. Для таких данных сравнивайте напрямую через СУММПРОИЗВ: она проверяет ячейки как есть, без преобразований.
=СУММПРОИЗВ(--($A$2:$A$500=A2))>1Способы найти дубли
| Задача | Как сделать |
|---|---|
| Просто увидеть | Условное форматирование → «Повторяющиеся значения» |
| Пометить все вхождения, включая первое | =СЧЁТЕСЛИ($A$2:$A$500;A2)>1 |
| Список без повторов, Excel 365 | =УНИК(A2:A500) |
| Сколько значений повторяется | =СУММПРОИЗВ(--(СЧЁТЕСЛИ(A2:A500;A2:A500)>1)) |
| Удалить дубли | «Данные» → «Удалить дубликаты», отметить нужные столбцы |
Частые ошибки
- «ООО Ромашка*» считается повтором всех названий на «ООО Ромашка» — Звёздочка в искомом значении — подстановочный знак. Экранируйте:
=СЧЁТЕСЛИ($A$2:$A$500;ПОДСТАВИТЬ(A2;"*";"~*"))или сравнивайте черезСУММПРОИЗВ. - «ИВАНОВ» и «Иванов» помечены как повтор, а нужно различать —
СЧЁТЕСЛИрегистр не учитывает. Для точного сравнения —=СУММПРОИЗВ(--СОВПАД($A$2:$A$500;A2))>1. - «Удалить дубликаты» по одному столбцу удалило разные заказы одного клиента — В окне удаления отметьте все столбцы, которые вместе определяют дубль, и работайте на копии листа.
- Формула на всём столбце
$A:$Aтормозит книгу — На 50 000 строкСЧЁТЕСЛИпо полным столбцам пересчитывается долго. Ограничьте диапазон или используйте умную таблицу.
Вопросы и ответы
Как найти одинаковые значения в двух столбцах Excel?
Выделите оба столбца и примените «Повторяющиеся значения» — подсветятся совпадения между ними. Для пометки формулой — СЧЁТЕСЛИ по второму столбцу, как в подсценарии со списками.
Как оставить только уникальные значения?
В Excel 365 — =УНИК(A2:A500). В старых версиях: «Данные» → «Дополнительно» → «Только уникальные записи» с копированием в другое место.
Как посчитать, сколько раз повторяется каждое значение?
Рядом со значением — =СЧЁТЕСЛИ($A$2:$A$500;A2). Удобнее сводная таблица: значение в строки и оно же в значения с операцией «Количество».
Как удалить дубли, но сложить их суммы?
Сводная таблица по ключевому полю с суммой в значениях. Формулник сделает такую чистку по вашему файлу: дубли уйдут на отдельный лист, а не удалятся.
Почему Excel не видит дубли, хотя значения выглядят одинаково?
Пробел в конце, неразрывный пробел или число, сохранённое как текст. Проверьте длину =ДЛСТР(A2) у двух «одинаковых» ячеек.
- Справка Microsoft: «Поиск и удаление повторений»
- Справка Microsoft: функция СЧЁТЕСЛИ — ограничение на строки длиннее 255 символов