KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

Как Отметить дубликаты в Excel

АвторАманда ЛиДата изменения

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

Использование формулы СЧЁТЕСЛИ в Excel для пометки только второго и последующих дубликатов в вспомогательном столбце

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

Отметить дубликаты (включая первые вхождения)

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

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

Excel вернёт значение Duplicate для значений, встречающихся в столбце более одного раза, и Unique для значений, встречающихся только один раз.

Использование формулы СЧЁТЕСЛИ в Excel для пометки дубликатов и уникальных значений во вспомогательном столбце

Примечание:

Если вы не хотите отображать Unique для недублирующихся значений, замените "Unique" на "" в формуле.

Преимущества

  • Простая настройка
  • Хорошо подходит для проверки дубликатов в одном столбце
  • Автоматически обновляется при изменении данных

Маркировка только второго и последующих дубликатов

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

  1. Предположим, что значения находятся в столбце A, начиная с ячейки A2.
  2. Во вспомогательном столбце введите приведённую ниже формулу:
    =IF(COUNTIF($A$2:A2,A2)>,1,"Duplicate","")
  3. Нажмите клавишу Enter, а затем скопируйте формулу вниз.

При таком подходе первое вхождение остаётся пустым, а все последующие — включая второе — помечаются как Duplicate.

Использование формулы СЧЁТЕСЛИ в Excel для пометки только второго и последующих дубликатов в вспомогательном столбце

Примечание:

Это удобно, когда вы хотите оставить первую совпадающую запись в качестве основной и пометить только дополнительные дублирующие записи.

Преимущества

  • Первое вхождение остаётся без отметки
  • Полезно для проверки повторяющихся записей
  • После маркировки легко фильтровать

Проверка дубликатов с учётом регистра

Функция COUNTIF в Excel не учитывает регистр, поэтому значения, такие как Apple и APPLE, рассматриваются как дубликаты. Если вам нужно различать значения по регистру букв, используйте формулу с функцией EXACT.

  1. Предположим, что ваши значения находятся в диапазоне A2:A12.
  2. Во вспомогательном столбце введите любую из следующих формул:
    • Определение дубликатов с учётом регистра включаяпервые вхождения:
      =IF(SUMPRODUCT(--EXACT($A$2:$A$12,A2))>,1,"Duplicate","")
    • Определение дубликатов с учётом регистра исключаяпервые вхождения:
      =IF(SUMPRODUCT(--EXACT($A$2:A2,A2))>,1,"Duplicate","")
  3. Нажмите клавишу Enter, затем введите формулу ниже.

Эти формулы проверяют, встречается ли значение более одного раза с учётом точного совпадения регистра букв. На снимке экрана я применил первую формулу, поэтому все совпадения — включая первое вхождение — помечены как дубликаты.

Использование функции СОВПАД в Excel для пометки дубликатов с учётом регистра

Примечание:

Если вы хотите отображать Unique для недублирующихся значений, замените "" на "Unique" в формуле.

Преимущества

  • Различает значения по регистру букв
  • Полезно для кодов, идентификаторов и текстовых строк, где важен регистр

Отметить дубликаты на основе нескольких столбцов

Иногда дубликат определяется не одним столбцом. Например, запись считается дубликатом только тогда, когда одновременно совпадают Product и Region или когда повторяется вся строка. В таких случаях необходимо проверять несколько столбцов вместе.

Используйте формулу для выявления Дублирующая строка

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

  1. Предположим, что проверка на дубликаты выполняется по столбцам A, B и C, начиная со строки 2.
  2. Во вспомогательном столбце введите любую из следующих формул:
    • Определение Дублирующая строка включаяпервые вхождения:
      =IF(COUNTIFS($A:$A,A2,$B:$B,B2,$C:$C,C2)>,1,"Duplicate","")
    • Определение Дублирующая строка исключаяпервые вхождения:
      =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2)>,1,"Duplicate","")
  3. Нажмите клавишу Enter, а затем перетащите формулу вниз.

Excel определит строки, в которых комбинация значений из столбцов A, B и C встречается более одного раза. На снимке экрана я применил вторую формулу, поэтому первое вхождение каждой уникальной комбинации остаётся без пометки, а все последующие — помечаются как Duplicate.

Лист Excel с выделенными повторяющимися строками с помощью формулы СЧЁТЕСЛИМН, где только повторяющиеся записи помечены как «Дубликат», а первое вхождение оставлено без отметки
Совет:Чтобы проверить дубликаты в четырёх или более столбцах, просто расширьте формулу, добавив дополнительные пары диапазонов и условий, например $D:$D;D2,$E:$E;E2и так далее.

Преимущества

  • Хорошо работает для Дублирующая строка, основанных на нескольких полях
  • Автоматически обновляется при изменении данных
  • Гибко и легко расширяемо на дополнительные столбцы

Недостатки

  • Требуется вспомогательный столбец
  • Может быть сложнее для восприятия при большом количестве столбцов

Отметить дубликаты по нескольким столбцам с помощью Kutools для Excel

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

Kutools для Excelпредлагает более 300 расширенных функций для упрощения сложных задач, повышая креативность и эффективность.Интегрировано с возможностями ИИ, Kutools автоматизирует задачи с высокой точностью, делая управление данными простым и удобным.Подробная информация о Kutools для Excel…         Бесплатная пробная версия…
  1. Выберите диапазон данных, в котором нужно проверить наличие дубликатов.
  2. Щёлкните Kutools > Find > Найти дубликаты.
  3. В диалоговом окне Найти дубликаты отметьте столбцы, по которым следует определять дублирующиеся записи.
  4. В разделе Тип действия выберите Отметить дубликаты.
  5. Подтвердите, содержит ли ваша таблица заголовки, затем нажмите кнопку OK.
    Диалоговое окно Kutools «Найти дубликаты» с выбранным параметром «Определить дубликаты»

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

Лист с метками дубликатов, добавленными с помощью Kutools for Excel, и диалоговым окном подтверждения после определения повторяющихся строк

Примечание:

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

Преимущества

  • Не требует написания формул
  • Можно Отметить дубликаты на основе одного или нескольких столбцов
  • Удобно для больших или сложных таблиц

Недостатки

  • Требуется Kutools для Excel
  • Поддерживает только исключение первого вхождения

Kutools для Excel — более 300 незаменимых инструментов для Excel, которые делают работу быстрее, проще и эффективнее.Скачать сейчас!

Часто задаваемые вопросы

Что означают знаки доллара в формулах Excel?

Знаки доллара делают ссылку на ячейку абсолютной. Например, $A$2 фиксирует и столбец, и строку, поэтому при копировании формулы вниз ссылка остаётся неизменной. Это особенно удобно, когда вы хотите, чтобы определённая часть диапазона в формуле не менялась.

Когда при отметке дубликатов следует включать или исключать первое вхождение?

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

Почему Excel выдаёт предупреждение «Формула пропускает смежные ячейки»?

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

Можно ли помечать дубликаты сразу по нескольким столбцам без использования формул?

Да. С помощью Kutools для Excel вы можете легко найти дублирующиеся строки по выбранным столбцам и автоматически пометить их прямо на листе — без необходимости вручную создавать формулы.

Может ли Kutools для Excel находить и выделять дубликаты в списке?

Да. Kutools может находить и выделять дубликаты как в одном столбце — по вашему выбору, так и в нескольких столбцах, рассматривая выбранные условия фильтрации совместно как одну запись.

Заключение

Использование таких формул, как COUNTIF и COUNTIFS, даёт гибкий способ отметить дубликаты, не изменяя исходные данные. Для более сложных задач, например при проверке с учётом регистра, комбинирование функций, таких как EXACT, обеспечивает лучший контроль.

Если вам нужен более быстрый способ без формул, Kutools для Excel предлагает простой метод автоматической пометки дубликатов.

Надеемся, что этот учебник оказался для вас полезным! Хотите освоить ещё больше советов по Excel и готовых практических решений? Пожалуйста, нажмите здесь, чтобы ознакомиться со всей нашей коллекцией учебных материалов по Excel.