Поиск дубликатов в Excel: выделение, подсчёт и список

By Mirko Peters · 2026-11-12

Поиск дубликатов в Excel с помощью условного форматирования, формул COUNTIF, UNIQUE, Power Query и проверок между листами, а также как найти похожие совпадения перед удалением.

Automatically translated with AI from the original post. Read the English original

Чтобы найти дубликаты в Excel, ничего не удаляя, выделите данные и выберите Главная > Условное форматирование > Правила выделения ячеек > Повторяющиеся значения. Excel окрасит каждое значение, которое встречается более одного раза. Если нужны числа вместо цветов, добавьте вспомогательный столбец с формулой COUNTIF. Эта статья для пользователей Excel, которым достались запутанные списки и которым нужно увидеть проблему до её исправления. Вы узнаете о методе выделения, формулах подсчёта и пометки, способе вывести только повторяющиеся значения, варианте с Power Query, проверках между листами и плане работы с похожими совпадениями.

Ключевые выводы

• Самый быстрый способ найти дубликаты в Excel: «Главная», «Условное форматирование», «Правила выделения ячеек», «Повторяющиеся значения». Повторяющиеся значения окрашиваются, и ничего не удаляется.
• Вспомогательный столбец с COUNTIF показывает, how часто встречается каждое значение, а с помощью COUNTIFS можно проверить сразу несколько столбцов.
• COUNTIF не учитывает регистр букв и считает символы ? и * подстановочными знаками, поэтому результат подсчёта может отличаться от ожидаемого.
• Чтобы сравнить два листа, подсчитайте каждое значение по второму списку с помощью COUNTIF, и откройте обе книги, если второй список находится в другом файле.
• Похожие совпадения, например Jon и John, не будут считаться дубликатами. Сначала уберите лишние пробелы и приведите регистр к единому виду, затем проверьте остальное вручную или с помощью надстройки Fuzzy Lookup.

Сначала поиск, потом удаление. Если вы уже знаете, какие строки нужно удалить, в нашем сопутствующем руководстве «как удалить дубликаты в Excel» описан этап удаления. Здесь же цель — сначала посмотреть в Excel, чтобы вы сами решили, что считать дубликатом и что оставить.

Выделение дубликатов с помощью условного форматирования

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

1. Выделите ячейки, столбец или диапазон, который хотите проверить. Обычно достаточно одного столбца.
2. На вкладке Главная откройте Условное форматирование.
3. Выберите Правила выделения ячеек, затем Повторяющиеся значения.
4. Выберите формат, например светло-красную заливку, и нажмите ОК.

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

Есть и второй способ, который даёт больше контроля. Откройте Условное форматирование > Управление правилами, создайте Создать правило и выберите Форматировать только уникальные или повторяющиеся значения. В списке Форматировать все можно выбрать уникальные или повторяющиеся, а также задать формат числа, шрифт, границу или заливку. Это удобно, когда нужно окрасить значения, встречающиеся только один раз, и не трогать повторяющиеся.

Два ограничения, о которых стоит знать. Microsoft пишет, что Excel не может выделять дубликаты в области «Значения» сводной таблицы. А если выбран один столбец, правило проверяет только его. Строка, которая повторяется только при совпадении нескольких столбцов, не будет подсвечена. Для такого случая нужна формула.

Подсчёт и пометка дубликатов с помощью COUNTIF

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

Пометка всех повторов

Допустим, значения находятся в диапазоне A2:A100. В ячейку B2 введите:

=COUNTIF($A$2:$A$100,A2)

Протяните формулу вниз. Результат 1 означает, что значение уникально. Число 2 означает, что оно встречается дважды, и так далее. Каждая копия повторяющегося значения получает одно и то же число, включая первую. Чтобы превратить счётчик в ИСТИНА или ЛОЖЬ, добавьте сравнение: =COUNTIF($A$2:$A$100,A2)>1.

Пометка только последующих копий

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

=COUNTIF(A$2:A2,A2)>1

Диапазон A$2:A2 расширяется при копировании формулы вниз. При первом появлении значения результат будет ЛОЖЬ, при каждом последующем — ИСТИНА. Так же работает команда «Удалить дубликаты» в Excel, которая сохраняет первое вхождение.

Проверка нескольких столбцов с помощью COUNTIFS

Если запись считается дубликатом только тогда, когда совпадают, например, имя и фамилия, используйте COUNTIFS с парой «диапазон и условие» для каждого столбца:

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)

Результат больше 1 означает, что комбинация встречается более одного раза. Для большего числа столбцов добавьте еще пары. Не пытайтесь построить ту же проверку, объединяя диапазоны внутри COUNTIF, как в COUNTIF($A$2:$A$100&$B$2:$B$100,A2&B2). COUNTIF ожидает в качестве первого аргумента настоящий диапазон, поэтому такая формула работает не так, как кажется. Правильное решение — COUNTIFS.

Что в COUNTIF удивляет многих

На странице Microsoft о COUNTIF перечислены особенности, которые важны при поиске дубликатов:

• Сопоставление текста не учитывает регистр букв. «apples» и «APPLES» считаются одним и тем же значением. Excel рассматривает их здесь как дубликаты.
• Символы ? и * работают как подстановочные знаки. Если в данных есть такие символы, например в кодах товаров, COUNTIF может найти больше совпадений, чем вы ожидаете. Чтобы искать такой символ буквально, поставьте перед ним знак тильды (~).
• COUNTIF возвращает неверные результаты для строк длиннее 255 символов.
• Пробелы в начале или в конце строки, смешанные прямые и typographic-кавычки, а также непечатаемые символы могут исказить подсчёт. Microsoft предлагает сначала очистить данные с помощью функций TRIM и CLEAN.

Именно этот последний пункт стоит за большинством жалоб вида «Excel не видит мои дубликаты». Две ячейки могут выглядеть одинаково, но отличаться конечным пробелом. Сначала очистите данные, затем считайте.

Фильтрация и проверка

Когда вспомогательный столбец готов, отфильтруйте его по ИСТИНА или по значениям больше 1 и просмотрите строки. Можно отсортировать по вспомогательному столбцу, чтобы сгруппировать все повторяющиеся строки вместе. Перед удалением посмотрите выборку. Нередко «дубликат» оказывается двумя реальными записями с одинаковым именем.

Вывод только повторяющихся значений

Иногда нужен короткий список повторяющихся значений, а не пометка рядом с каждой строкой.

В Excel для Microsoft 365, 2024 и 2021 эту задачу может решить функция UNIQUE. Согласно странице Microsoft о UNIQUE, =UNIQUE(array,[by_col],[exactly_once]) возвращает уникальные значения из диапазона, а если exactly_once равно ИСТИНА, возвращаются только значения, встречающиеся один раз. Так, =UNIQUE(A2:A100) выводит каждое значение один раз, а =UNIQUE(A2:A100,,TRUE) выводит значения, которые вообще не повторяются. Чтобы вывести только повторяющиеся значения, объедините UNIQUE с FILTER и COUNTIF:

=UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1))

Эта формула оставляет строки, у которых счётчик больше 1, и выводит каждое такое значение один раз. Для работы нужна версия Excel с динамическими массивами. Прежде чем доверять ей большой список, проверьте её на нескольких строках.

В более старых версиях используйте вспомогательный столбец. Введите =IF(COUNTIF($A$2:$A$100,A2)>1,A2,"") и протяните формулу вниз. Столбец покажет каждое повторяющееся значение и оставит пустые ячейки для остальных. Отфильтруйте пустые. Каждое повторяющееся значение будет показано столько раз, сколько оно встречается, что подходит для проверки.

С расширенным фильтром можно скопировать в новое место только уникальные записи. Перейдите на вкладку Данные > Дополнительно, выберите Скопировать результат в другое место, укажите ячейку в поле Поместить результат в диапазон и установите флажок Только уникальные записи. Сравнение этого списка с исходным покажет, что повторялось.

Используйте Power Query, чтобы оставить только дубликаты

Если данных много или они поступают каждую неделю, поручите поиск дубликатов Power Query. В документации Microsoft по Power Query описана команда Сохранить дубликаты, противоположная команде Удалить дубликаты. Она фильтрует таблицу до строк, значения которых в выбранных столбцах встречаются более одного раза. В отличие от команды Удалить дубликаты, она возвращает все совпадающие строки, а не по одной на каждое значение.

В статье Microsoft о сохранении и удалении повторяющихся строк приведены шаги для варианта удаления. Начало у обоих вариантов одинаковое:

1. Выберите ячейку в данных и перейдите в раздел Запрос > Изменить либо загрузите данные командой Данные > Из таблицы/диапазона.
2. Щелкните заголовок столбца, чтобы выбрать его. Чтобы выбрать несколько столбцов, удерживайте Shift или Ctrl.
3. Примените команду. В шагах Microsoft для удаления используется путь Главная > Удалить строки > Удалить дубликаты. Команда Сохранить дубликаты находится рядом с другими командами для строк на вкладке «Главная». Проверьте результат в предварительном просмотре.

Microsoft также отмечает, что Power Query учитывает регистр, поэтому «Apple» и «apple» там являются разными значениями, в отличие от COUNTIF. Это реальное различие между двумя способами. Если нужна повторяемая еженедельная проверка, выберите одно поведение и придерживайтесь его, например, предварительно переведя столбец в верхний регистр.

Поиск дубликатов между двумя листами или списками

Типичная задача: проверить, есть ли список с одного листа на другом, например новый список клиентов по сравнению со старым или заказы этого месяца по сравнению с заказами прошлого месяца.

1. Разместите два списка на отдельных листах, например Лист1 и Лист2, со значениями в столбце A.
2. На Лист1 в ячейку B2 введите =COUNTIF(Лист2!A:A;A2)>0.
3. Протяните формулу вниз. ИСТИНА означает, что значение также есть на Лист2.
4. Отфильтруйте по ИСТИНА, чтобы увидеть совпадения, или по ЛОЖЬ, чтобы увидеть новые значения.

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

Если второй список находится в другой книге, Microsoft предупреждает, что формула, ссылающаяся на закрытую книгу, может вернуть ошибку #ЗНАЧ!. Держите обе книги открытыми во время работы или перенесите второй список в первую книгу с помощью Power Query. После проверки учитывайте, что результат зависит от данных на этот момент. Если любой из списков изменится, флаги тоже изменятся.

Похожие совпадения: Jon Smith и John Smith

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

Первый этап: устраните ложные различия. Используйте СЖПРОБЕЛЫ, чтобы убрать лишние пробелы, и ПЕЧСИМV, чтобы удалить непечатаемые символы, и строьте проверку на очищенных значениях. во вспомогательном столбце =СЖПРОБЕЛЫ(A2) покажет очищенный текст. Считайте дубликаты по этому столбцу, а не по исходному. Поскольку COUNTIF не учитывает регистр, менять его здесь не нужно. В Power Query, где регистр важен, переведите столбец в верхний или нижний регистр.

Шаг второй: посмотрите, что осталось. Отсортируйте очищенный список по алфавиту и просмотрите соседние строки. Почти совпадающие записи, например «Jon Smith» и «John Smith», окажутся рядом, и человек сможет их увидеть. Для больших списков Microsoft Research опубликовала надстройку Fuzzy Lookup add-in for Excel. Она умеет находить нечёткие дубликаты строк в одной таблице или сопоставлять похожие строки в двух таблицах и присваивает каждому совпадению оценку сходства. Прежде чем планировать работу с ней, проверьте требования: на странице загрузки указаны Windows и Excel 2007, 2010, 2013 и 2016, а также релиз 2020 года. Она может не устанавливаться на актуальной конфигурации Microsoft 365, и её нет для Mac.

Что бы вы ни использовали, проверяйте результаты нечёткого поиска вручную. Два человека с похожими именами не обязательно являются одним и тем же человеком.

Распространённые ошибки

• Проверка только одного столбца, когда дубликат определяется по двум. Одна лишь повторяющаяся фамилия не делает запись дубликатом.
• Доверие результатам COUNTIF на данных с лишними пробелами или непечатаемыми символами. Сначала очистите данные с помощью TRIM и CLEAN.
• Забыть, что COUNTIF не учитывает регистр, тогда как Power Query его учитывает, и получить от них разные результаты.
• Использовать COUNTIF($A$2:$A$100,A2)>1 и удалять все строки с TRUE. Так помечается и первая копия, и вы удалите все экземпляры. Используйте расширяющийся диапазон, чтобы пометить только последующие.
• Искать по всем столбцам строки длиннее 255 символов, где COUNTIF даёт неверные результаты.
• Удалять сразу после выделения. Сначала скопируйте лист, поскольку Microsoft сообщает, что удалённые дубликаты удаляются безвозвратно.

Связанные темы для изучения

• Удалите найденные дубликаты. Команда «Удалить дубликаты» на вкладке «Данные» оставляет первое совпадение и удаляет остальные. Сначала отсортируйте данные, чтобы контролировать, какая строка останется.
• Оставляйте лучшую запись, а не первую. Отсортируйте по дате или значению, чтобы нужная строка оказалась первой, а затем пометьте последующие копии с помощью расширяющегося COUNTIF.
• Сравнивайте списки в разных файлах. Power Query может объединить несколько книг в одну таблицу, чтобы вы могли проверить их вместе.
• Предотвращайте появление новых дубликатов. Проверка данных с пользовательской формулой может блокировать значение, которое уже существует. Попробуйте на тестовом листе.
• Ведите журнал аудита. Оставьте вспомогательный столбец в копии листа, чтобы позже можно было показать, что было помечено и почему.

Следующий шаг

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