Описать задачу
Формулы Excel › Готовые формулы › Как найти повторы в Excel формулой

Как найти повторы в Excel формулой: пометить дубли

пример проверен пересчётомExcel, Яндекс Таблицы, Р7, LibreOffice

Редакция Формулника · обновлено · примеры проверены пересчётом LibreOffice 26.8 на сервере Формулника · как мы проверяем

Коротко. Пометить повторы: в B2 =ЕСЛИ(СЧЁТЕСЛИ($A$2:A2;A2)>1;"повтор";"") — первое появление остаётся пустым, повторы помечены.

Для русского Excel

=ЕСЛИ(СЧЁТЕСЛИ($A$2:A4;A4)>1;"повтор";"")

Для английского Excel, Google Таблиц и внутри файла .xlsx

=IF(COUNTIF($A$2:A4,A4)>1,"повтор","")
Сделать по моим данным

Поправьте задачу под свою таблицу — Формулник напишет формулу под ваши столбцы и проверит её расчётом.

Пример

AB
1АртикулОтметка
20457
30912
40457повтор

Результат в ячейке B4 посчитан LibreOffice на сервере Формулника — формула из B2, протянутая до B4.

Как найти повторы в Excel формулой — пример в Excel: =ЕСЛИ(СЧЁТЕСЛИ($A$2:A4;A4)>1;"повтор";"")
Лист «Пример» из файла .xlsx, который отдаёт кнопка «Скачать пример»: результат формулы подсвечен

Как вставить

  1. В B2 вставьте формулу.
  2. Протяните вниз.
  3. Отфильтруйте столбец B по слову «повтор» — это строки-дубли.

Почему так

СЧЁТЕСЛИ считает, сколько раз значение встретилось от начала списка до текущей строки. Больше одного — повтор.

Другие случаи

Сколько раз значение встречается во всём списке
=СЧЁТЕСЛИ($A$2:$A$4;A2)
=COUNTIF($A$2:$A$4,A2)
Количество уникальных значений
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A4;A2:A4))
=SUMPRODUCT(1/COUNTIF(A2:A4,A2:A4))

Частые ошибки

«0457» и 457 не считаются повтором

Число и текст — разные значения; приведите столбец к одному типу.

Пробел в конце мешает

«Сидоров » и «Сидоров» — разные; сначала СЖПРОБЕЛЫ.

Функции в этой формуле

СЧЁТЕСЛИУНИКЕСЛИ

Другие готовые формулы

Формула НДС 22 %Процент выполнения планаНарастающий итогФамилия И.О. из полного ФИОРабочие дни между датамиСтажПениСумма прописьюВозраст по дате рожденияПроцент от числа

Повторы в Excel: подсветить, пометить, найти по двум столбцам и сравнить два списка

Быстрее всего подсветить повторы без формул: «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения». Формула со страницы выше нужна, когда дубли надо отфильтровать или посчитать. Повтор по нескольким полям (ФИО и дата рождения, артикул и склад) ищется через СЧЁТЕСЛИМН.

У СЧЁТЕСЛИ есть ловушки: она не различает регистр, понимает * и ? как подстановочные знаки и сравнивает длинные числа-тексты только по первым 15 цифрам.

По шагам

  1. Сделайте копию листа: удаление дублей необратимо, а исходник понадобится для сверки.
  2. Нормализуйте данные: =СЖПРОБЕЛЫ(A2) убирает пробелы по краям, =ПРОПИСН(A2) выравнивает регистр для визуальной проверки.
  3. Подсветите повторы условным форматированием и оцените масштаб.
  4. Пометьте дубли формулой в соседнем столбце, отфильтруйте по пометке.
  5. Решите, что делать: удалить («Данные» → «Удалить дубликаты»), объединить суммы через сводную или оставить первое появление.
  6. Пересчитайте количество строк до и после — разница должна совпасть с числом помеченных.

Повтор по двум столбцам сразу

Однофамильцы — не дубли, а одинаковые ФИО и дата рождения — почти наверняка. ФИО в 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))
Удалить дубли«Данные» → «Удалить дубликаты», отметить нужные столбцы

Частые ошибки

Вопросы и ответы

Как найти одинаковые значения в двух столбцах Excel?

Выделите оба столбца и примените «Повторяющиеся значения» — подсветятся совпадения между ними. Для пометки формулой — СЧЁТЕСЛИ по второму столбцу, как в подсценарии со списками.

Как оставить только уникальные значения?

В Excel 365 — =УНИК(A2:A500). В старых версиях: «Данные» → «Дополнительно» → «Только уникальные записи» с копированием в другое место.

Как посчитать, сколько раз повторяется каждое значение?

Рядом со значением — =СЧЁТЕСЛИ($A$2:$A$500;A2). Удобнее сводная таблица: значение в строки и оно же в значения с операцией «Количество».

Как удалить дубли, но сложить их суммы?

Сводная таблица по ключевому полю с суммой в значениях. Формулник сделает такую чистку по вашему файлу: дубли уйдут на отдельный лист, а не удалятся.

Почему Excel не видит дубли, хотя значения выглядят одинаково?

Пробел в конце, неразрывный пробел или число, сохранённое как текст. Проверьте длину =ДЛСТР(A2) у двух «одинаковых» ячеек.

Проверено редакцией Формулника 17 октября 2026. Как мы проверяем. Источники:
  • Справка Microsoft: «Поиск и удаление повторений»
  • Справка Microsoft: функция СЧЁТЕСЛИ — ограничение на строки длиннее 255 символов