Как выделить цветом повторяющиеся значения или дублирующиеся строки в Excel?
При работе с большими наборами данных в Excel часто встречаются повторяющиеся записи — будь то отдельные значения, разбросанные по столбцу, или целые строки с одинаковыми данными в нескольких полях. Ручной поиск и выделение таких дубликатов, особенно в сложных таблицах, — не только утомительный, но и ошибкоопасный процесс. Эффективное цветовое выделение повторяющихся значений или строк упрощает проверку данных, помогает при валидации и ускоряет анализ или подготовку отчётов. Ниже представлены практические решения для выделения дублирующихся данных в Excel — от встроенных функций до специализированных инструментов и продвинутой автоматизации.
Раскраска Дублирующиеся значения или строк с помощью Использовать условное форматирование
Раскраска Дублирующиеся значения или строк с помощью Kutools для Excel 
Раскраска Дублирующиеся значения или строк с использованием автоматизации на основе макроса VBA
Раскраска Дублирующиеся значения или строк с помощью Использовать условное форматирование
Функция Excel «Условное форматирование» предлагает простой способ визуально выделять дублирующиеся значения или строки. Это особенно полезно при быстрой проверке и валидации наборов данных, где повторяющаяся информация требует внимания или необходимо обеспечить целостность данных. Однако этот метод применяется преимущественно к видимым данным и лучше всего подходит для разовых задач, а не для постоянного обновления или работы с очень большими динамическими диапазонами.
Раскраска Дублирующиеся значения
1. Выделите диапазон данных, содержащий значения, для которых требуется проверка на дубликаты. Затем перейдите на вкладку Главная, нажмите Условное форматирование, выберите Правила выделения ячеек и укажите Дублирующиеся значения. См. скриншот ниже:
2. В появившемся диалоговом окне выберите цвет выделения из раскрывающегося списка — он будет применён к вашим дублирующимся значениям. Это позволит визуально различать повторяющиеся записи в соответствии с вашими предпочтениями и цветовой схемой. См. скриншот:
3. Нажмите ОК, чтобы применить форматирование. Все дублирующиеся значения — включая первое вхождение — теперь будут выделены выбранным вами цветом, что позволит легко обнаруживать их с первого взгляда.
Практический совет: Если вы хотите выделять дубликаты, но исключить первое вхождение, стандартное условное форматирование не поддерживает такую возможность напрямую. Вам может понадобиться добавить вспомогательный столбец с формулой или воспользоваться более продвинутым решением, как показано в последующих разделах.
Раскраска Дублирующая строка в Выберите диапазон
Если ваша задача требует раскраски Вся строка на основе дубликатов в определённом столбце, выполните следующие действия:
1. Выделите диапазон данных, содержащий строки, которые необходимо оценить. На вкладке Главная выберите Использовать условное форматирование, затем — Создать правило. См. скриншот:
2. В диалоговом окне Создание правила форматирования выберите Использовать формулу для определения форматируемых ячеек. Введите эту формулу в соответствующее поле (при необходимости замените диапазон и ссылку на столбец):
=COUNTIF($D$2:$D$10,$D2)>1
Совет: В этой формуле D2:D10 представляет диапазон целевого столбца для поиска дубликатов, а D2 — начальную ячейку вашего выделения. Измените эти ссылки в соответствии с вашим фактическим диапазоном данных. Например, если ваш набор данных начинается со строки 5, замените D2 на D5 и так далее.
3. Нажмите Формат, чтобы открыть окно Установить формат ячейки. На вкладке Заливка выберите цвет фона для отметки дублирующих строк. Этот цвет должен хорошо контрастировать с исходными данными, чтобы их было легко замечать. См. скриншот:
4. Нажмите ОК в диалоговом окне Установить формат ячейки, затем снова нажмите ОК в диалоговом окне Создание правила форматирования, чтобы завершить настройку. Теперь все строки, в которых указанный столбец содержит дублирующиеся значения, будут выделены — включая первое вхождение каждого дубликата.
Этот метод обеспечивает визуальное представление без изменения исходных данных, что упрощает фильтрацию, сортировку и дальнейшее редактирование. Помните: при изменении диапазона данных или столбцов формулу необходимо адаптировать, а также учтите, что при использовании только условного форматирования существуют ограничения на сложность логики поиска дубликатов.
Однако, поскольку этот метод требует запоминания и ввода формул, он может быть не самым удобным для всех пользователей — особенно при работе с динамическими или часто обновляемыми таблицами. Если вам нужно выделять дубликаты (с исключением или включением первого вхождения) более гибко или раскрашивать дублирующиеся строки, а не только отдельные ячейки, попробуйте одно из продвинутых решений, описанных ниже.
Раскраска Дублирующиеся значения или строк с помощью Kutools для Excel
Kutools для Excel предлагает мощный и удобный инструмент для поиска и выделения цветом не только дублирующихся значений, но и целых строк — с возможностью выбрать собственный цвет и точно настроить, должно ли учитываться первое вхождение.
После бесплатной установкиKutools для Excel выполните следующие действия:
1. Выделите нужный список или диапазон данных, затем нажмите Kutools > Выделить > Выбрать дубликаты/уникальные ячейки. Эта утилита предлагает гибкие возможности выделения и настройки для решения самых разных задач поиска дубликатов. См. скриншот:
2. В диалоговом окне Выбрать дубликаты/уникальные ячейки выберите либо параметр Дубликаты (исключая первое совпадение), либо Дубликаты (включая первое совпадение). Затем установите флажок Заполнить цвет фона и выберите нужный цвет выделения из раскрывающегося списка ниже. Такой подход обеспечивает точный контроль над тем, какие дубликаты выделять, и позволяет сразу видеть результат. См. скриншот:
3. Нажмите ОК. После появления запроса диалоговое окно покажет количество выделенных ячеек. Просто нажмите ОК, чтобы продолжить. См. скриншот:
Указанные вами дублирующиеся значения теперь выделены и окрашены в соответствии с заданными настройками, что позволяет наглядно оценить уникальность данных и легко обнаружить повторяющиеся записи.
Примечание:
Чтобы выделить дублирующиеся строки, просто выделите весь диапазон (все столбцы для строк, которые требуется проверить), откройте Выбрать дубликаты/уникальные ячейки и обязательно установите флажок Выбрать всю строку в дополнение к соответствующему параметру «Критерии оценки». Это гарантирует, что будут выделены все дублирующиеся строки, а не только отдельные дублирующиеся ячейки. См. пример ниже:
Нажмите здесь, чтобы узнать больше о функции «Выбрать дубликаты/уникальные ячейки».
Преимущества этого метода — отсутствие необходимости запоминать формулы, интуитивно понятный интерфейс для выбора параметров и effortless-обработка больших таблиц. Однако для его использования требуется установка Kutools. Если вы регулярно решаете подобные задачи, Kutools существенно ускорит вашу работу, предлагая функциональность, превосходящую встроенные средства Excel.
Цвет Дублирующиеся значения или строк с помощью автоматизации макросом VBA
Для тех, кому требуется более продвинутое управление — например, массовое выделение на нескольких листах, пользовательские критерии окраски или регулярные автоматизированные обновления — макрос VBA может автоматизировать процесс поиска и окрашивания дубликатов. Такой подход особенно подходит пользователям, знакомым с программированием, а также в случаях, когда поиск дубликатов необходимо выполнять часто или когда Использовать условное форматирование не справляется со сложными правилами.
Внимание: Всегда сохраняйте свою книгу перед запуском макросов — действия VBA невозможно отменить с помощью функции «Отменить». Если ваши данные уже содержат цветовое форматирование, макрос может перезаписать его в затронутых областях.
Автоматизируйте окрашивание Дублирующиеся значения в выбранном столбце
1. Нажмите Инструменты разработчика > Visual Basic. В открывшемся окне Microsoft Visual Basic для приложений нажмите Вставка > Модуль, затем скопируйте и вставьте приведённый ниже код в окно модуля:
Sub HighlightDuplicateValues()
Dim Rng As Range
Dim Cell As Range
Dim dict As Object
Dim FirstColor As Long
Dim DupColor As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set dict = CreateObject("Scripting.Dictionary")
Set Rng = Application.InputBox("Select column to check for duplicates", xTitleId, Type:=8)
If Rng Is Nothing Then Exit Sub
FirstColor = vbYellow ' Color for first occurrence, change as needed
DupColor = vbRed ' Color for duplicates, change as needed
' Clear previous coloring in the range
Rng.Interior.ColorIndex = xlNone
For Each Cell In Rng
If Not IsEmpty(Cell.Value) Then
If dict.exists(Cell.Value) Then
Cell.Interior.Color = DupColor
Else
dict.Add Cell.Value, 1
Cell.Interior.Color = FirstColor
End If
End If
Next
End Sub2. Нажмите F5 или кнопку 
, чтобы выполнить код. Когда появится запрос, выберите диапазон ячеек (столбец, в котором нужно искать дубликаты), и нажмите «ОК». Макрос просканирует столбец: первое вхождение будет выделено жёлтым цветом (цвет можно изменить в коде), а все последующие дубликаты — красным (тоже настраивается в коде).
Автоматизируйте окрашивание Дублирующая строка на основе содержимого Вся строка
Этот макрос выделяет Вся строка, которые дублируются во всех столбцах вашего выделения.
1. Откройте редактор VBA, как описано выше ()Инструменты разработчика > Visual Basic > Вставка > Модуль), затем вставьте следующий код:
Sub HighlightDuplicateRows()
Dim Rng As Range
Dim RowArea As Range
Dim dict As Object
Dim i As Long
Dim rowString As String
Dim FirstColor As Long
Dim DupColor As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set dict = CreateObject("Scripting.Dictionary")
Set Rng = Application.InputBox("Select the range including all columns to evaluate for duplicate rows", xTitleId, Type:=8)
If Rng Is Nothing Then Exit Sub
FirstColor = vbYellow ' Change color as needed
DupColor = vbRed ' Change color as needed
' Clear previous coloring
Rng.EntireRow.Interior.ColorIndex = xlNone
For i = 1 To Rng.Rows.Count
rowString = ""
For Each RowArea In Rng.Rows(i).Cells
rowString = rowString & "|" & CStr(RowArea.Value)
Next
If dict.exists(rowString) Then
Rng.Rows(i).Interior.Color = DupColor
Else
dict.Add rowString, 1
Rng.Rows(i).Interior.Color = FirstColor
End If
Next
End Sub2. Нажмите Выполнить или клавишу F5, затем выберите диапазон, охватывающий все столбцы, которые нужно сравнить. Макрос выделит первое вхождение каждой уникальной строки жёлтым цветом (настраивается), а все последующие одинаковые строки — красным.
Пояснения параметров и практические замечания:
- Переменная
FirstColorзадаёт цвет заливки для первых вхождений; переменнаяDupColor— цвет для дублирующихся значений или строк. При необходимости вы можете заменитьvbYellowилиvbRedна другие константы цветов или значения RGB. - Код рассматривает пустые ячейки как уникальные и не окрашивает их. Если ваши данные содержат Пустые ячейки, которые следует обрабатывать иначе, код можно соответствующим образом изменить.
- При работе с очень большими наборами данных время обработки может увеличиться; проявите терпение, если запускаете макрос на тысячах строк.
- Если вы хотите очистить предыдущее форматирование перед повторным запуском макроса, обязательно оставьте строку сброса цвета ()
ColorIndex = xlNone) включённой.
Этот метод VBA отличается высокой гибкостью и поддерживает сложную настройку, пакетную обработку и сценарии автоматизации, недоступные стандартными средствами Excel. Однако он требует включения макросов и базового понимания VBA.
Рекомендации по устранению неполадок и резюме:Если возникают проблемы с окрашиванием, убедитесь, что диапазон выбран правильно, макросы включены и не активны конфликтующие правила условного форматирования. Всегда создавайте резервную копию данных перед первым запуском VBA. Для регулярного и сложного выделения дубликатов рекомендуется сохранять книгу с макросами в качестве шаблона.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек