Как найти повторяющиеся значения в Microsoft Excel: 3 метода

Чтобы найти повторяющиеся значения в Microsoft Excel, используйте условное форматирование для их выделения, формулу COUNTIF для их обозначения во вспомогательном столбце или функцию «Удалить дубликаты» для удаления повторяющихся записей. Начинайте с выделения или формулы, когда вам нужно просмотреть совпадения; используйте удаление только после того, как вы определите, что считается дубликатом, и создадите копию исходных данных.

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

Выберите правильный метод

• Выделение ячеек: быстрый просмотр повторяющихся значений без изменения данных.
• Используйте функцию COUNTIF: создайте многоразовую метку «Дублировать» или «Повторить» рядом с каждым значением.
• Удаление дубликатов: удаление строк на основе выбранного столбца или комбинации столбцов.

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

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

1. Выберите диапазон, который хотите проверить.

   

2. На вкладке «Главная» выберите «Условное форматирование» > «Правила выделения ячеек» > «Повторяющиеся значения» .

   

3. В диалоговом окне оставьте выбранным пункт «Дублировать» , выберите формат и нажмите «ОК» .

   

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

Чтобы выделить уникальные значения, выберите «Уникальные» в том же диалоговом окне. Встроенное правило не может форматировать повторяющиеся значения в области «Значения» сводной таблицы; вместо этого используйте обычный диапазон или проверку на основе формулы. См. инструкции Microsoft по поиску и удалению дубликатов .

2. Отметьте совпадения с помощью формулы COUNTIF.

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

=IF(A2="","",IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate",""))

Замените ячейку A100 на последнюю строку в вашем списке. Знаки доллара сохраняют диапазон поиска неизменным по мере заполнения формулы, в то время как ячейка A2 меняется на A3, A4 и так далее. Внешний оператор IF оставляет пустые строки пустыми. Поскольку формула подсчитывает весь диапазон, она помечает все вхождения повторяющегося значения, включая его первое появление.

1. Выделив ячейку B2, перетащите маркер заполнения вниз по строкам, которые вы хотите проверить, или скопируйте и вставьте формулу в остальную часть столбца B.

   

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

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

Чтобы создать дубликат целой строки на основе трех полей в столбцах A, B и C, поместите его во вспомогательный столбец и заполните его снизу вверх:

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2)>1

Эта функция возвращает TRUE для каждой строки, в которой комбинация A/B/C встречается более одного раза. Добавляйте или удаляйте пары столбцов, чтобы соответствовать вашей таблице. Функция COUNTIF обрабатывает один критерий; COUNTIFS поддерживает несколько критериев. В справочнике Microsoft по функции COUNTIF также отмечается, что сопоставление текста не чувствительно к регистру и что лишние пробелы или непечатаемые символы могут повлиять на результаты.

3. Удалите повторяющиеся строки с помощью функции «Удалить дубликаты».

Используйте этот параметр только тогда, когда будете готовы удалить записи. Сначала скопируйте исходный диапазон или лист. Выделите всю таблицу, чтобы ее столбцы оставались вместе, затем выберите Данные > Удалить дубликаты .

Если таблица содержит заголовки столбцов, установите флажок « Мои данные содержат заголовки ». В разделе «Столбцы» выберите поля, которые определяют дубликат, затем нажмите «ОК» .

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

Excel сохраняет первое совпадение и удаляет последующие строки, соответствующие выбранному ключу. Удаление затрагивает выбранный диапазон или таблицу, включая столбцы, которые не были отмечены в диалоговом окне. Проверьте количество удаленных строк и сразу же используйте Ctrl+Z, если результат не соответствует вашим ожиданиям. Microsoft объясняет это поведение в своем руководстве по фильтрации уникальных значений и удалению дубликатов .

Если Excel пропустил дубликат

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