Как найти и удалить повторяющиеся ячейки в Excel

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

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

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

Как выделить повторяющиеся ячейки в Excel

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

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

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

Чтобы применить встроенное правило для дубликатов, выполните следующие действия:

1. Выберите диапазон, в котором вы хотите найти повторяющиеся ячейки.
2. На Дом вкладка, в Стили группа, нажмите Условное форматирование > Правила выделения ячеек > Повторяющиеся значения…
   
3. в Повторяющиеся значения всплывающем диалоговом окне выберите форматирование для повторяющихся ячеек (по умолчанию — светло-красная заливка и темно-красный текст). Excel сразу покажет вам предварительный просмотр выбранного формата, и если он вас устраивает, нажмите ХОРОШО.
   

Советы:

• Чтобы применить собственное форматирование к дубликатам, нажмите «Пользовательский формат…» (последний пункт в раскрывающемся списке), а затем выберите нужный формат. Шрифт, Граница а также Наполнять опции.
• Чтобы выделить уникальные ячейки, выберите «Уникальные» в левом поле.

Пример 2. Выделите повторяющиеся ячейки, кроме первых вхождений

Чтобы пометить повторяющиеся значения, кроме 1-го экземпляра, встроенное правило не поможет, и вам нужно будет настроить собственное правило с формулой. Формула довольно сложная и требует добавления пустого столбца слева от вашего набора данных (столбец A в этом примере).

Чтобы создать правило, необходимо выполнить следующие шаги:

1. Выберите целевой диапазон.
2. На Дом вкладка, в Стили группа, нажмите Условное форматирование > Новое правило > Используйте формулу, чтобы определить, какие ячейки нужно отформатировать..
3. в Форматировать значения, где эта формула верна введите следующую формулу:

   =ЕСЛИ(СТОЛБЦЫ($B2:B2)>1, СЧЁТЕСЛИ(A$2:$B$7,B2),0) + СЧЁТЕСЛИ(B$2:B2,B2)>1

   Где B2 — первая ячейка в первом столбце, B7 — последняя ячейка в первом столбце, а A2 — ячейка в пустом столбце, соответствующая первой строке в выбранном диапазоне. Подробное объяснение формулы приведено в отдельном руководстве.

4. Нажмите на Формат… кнопку и выберите нужные параметры форматирования.
5. Нажмите ХОРОШО чтобы сохранить правило.
   

В этом руководстве можно найти гораздо больше вариантов использования и примеров: Как выделить дубликаты в Excel.

Как найти повторяющиеся ячейки в Excel с помощью формул

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

Чтобы найти дубликаты, включая 1-е вхождение, используется общая формула:

ЕСЛИ(СЧЁТЕСЛИ(диапазон, клетка)>1, «Дубликат», «»)

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

ЕСЛИ(СЧЁТЕСЛИ(расширяющийся_диапазон, клетка)>1, «Дубликат», «»)

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

Чтобы найти повторяющиеся ячейки, включая первые экземпляры, вы сравниваете целевую ячейку (A2) со всеми другими ячейками в диапазоне $A$2:$A$10 (обратите внимание, что мы блокируем диапазон абсолютными ссылками), и если несколько ячеек содержат одинаковые значение найдено, пометьте целевую ячейку как «Дубликат».

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$10, A2)>1, «Дубликат», «»)

Эта формула помещается в ячейку B2, а затем вы копируете ее в столько ячеек, сколько элементов в списке.

Чтобы получить повторяющиеся ячейки без первых экземпляров, вы сравниваете целевую ячейку (A2) только с указанными выше ячейками, а не с каждой другой ячейкой в ​​диапазоне. Для этого создайте ссылку на расширяющийся диапазон, например $A$2:$A2.

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2, $A2)>1, «Дубликат», «»)

При копировании в ячейки ниже ссылка на диапазон расширяется на 1. Таким образом, формула в ячейке B2 сравнивает значение в ячейке A2 только с самой этой ячейкой. В ячейке B3 диапазон расширяется до $A$2:$A3, поэтому значение в ячейке A3 также сравнивается с ячейкой выше и так далее.

Советы:

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

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

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

Как вы, наверное, знаете, все современные версии Excel снабжены Удалить дубликат инструмент, который работает со следующими оговорками:

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

Чтобы удалить повторяющиеся записи, вам нужно сделать следующее:

1. Выберите набор данных, который вы хотите дедупликации.
2. На Данные вкладка, в Инструменты данных щелкните Удалить дубликаты.
3. в Удалить дубликаты диалоговом окне, выберите столбцы для проверки на дубликаты и щелкните ХОРОШО.

В приведенном ниже примере мы хотим проверить первые четыре столбца на наличие дубликатов, поэтому мы выбираем их. Комментарии столбец не очень важен и поэтому не выбран.

На основе значений в выбранных столбцах Excel нашел и удалил 2 повторяющиеся записи (для Каден а также Итан). Первые экземпляры этих записей сохраняются.

Советы:

• Перед запуском инструмента имеет смысл сделать копию рабочего листа, чтобы не потерять информацию, если что-то пойдет не так.
• Прежде чем пытаться устранить дубликаты, удалите все фильтры, контуры или промежуточные итоги из ваших данных.
• Чтобы удалить дубликаты в отдельных ячейках (как в наборе данных чисел Randon из самого первого примера), используйте команду Дублирующиеся ячейки инструмент, обсуждаемый в следующем примере.

Другие варианты использования описаны в разделе Как удалить повторяющиеся строки в Excel.

Универсальный инструмент для поиска и удаления повторяющихся ячеек в Excel.

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

Чтобы облегчить жизнь пользователям Ultimate Suite, мы создали специальный инструмент для простой обработки повторяющихся ячеек. Что именно он может сделать? Почти все, что вы можете придумать 🙂

• Найдите повторяющиеся ячейки (с первыми вхождениями или без них) или уникальные ячейки.
• Найдите ячейки с одинаковыми значениями, формулами, фоном или цветом шрифта.
• Поиск повторяющихся ячеек с учетом регистра текста (поиск с учетом регистра) и игнорирование пробелов.
• Очистите повторяющиеся ячейки (содержимое, форматы или все).
• Закрасьте повторяющиеся ячейки.
• Выберите повторяющиеся ячейки.

Позвольте представить вам наше недавнее дополнение к Способности Удаление дубликатов набор инструментов — надстройка «Найти повторяющиеся ячейки».

Чтобы найти дубликаты ячеек на листе, выполните следующие действия:

1. Выберите свои данные.
2. На Данные об аблебитах вкладка, нажмите Удаление дубликатов > Найти Дублирующиеся ячейки.
3. Выберите, следует ли искать повторяющиеся или уникальные ячейки.
   
4. Укажите, следует ли сравнивать значения, формулы или форматирование, и при необходимости выберите дополнительные параметры. На скриншоте ниже показаны настройки по умолчанию:
   
5. Наконец, решите, что делать с найденными дубликатами: очистить, выделить или просто выделить, и нажмите «Готово».
   

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

Помните, что громоздкая формула для условного форматирования для достижения того же эффекта? 😉

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

Чтобы найти дубликаты в 2 столбцах или 2 разных таблицах, запустите инструмент «Сравнить две таблицы».

Хорошей новостью является то, что все эти инструменты включены в Ultimate Suite, и вы можете попробовать любой из них в своих рабочих листах прямо сейчас — ссылка для скачивания находится прямо ниже.

Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Доступные загрузки

Поиск повторяющихся ячеек — примеры (файл .xlsx)
Ultimate Suite — пробная версия (файл .exe)

Вас также могут заинтересовать