Условное форматирование строк или ячеек, если два столбца равны в Excel

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

• Условное форматирование строк или ячеек, если два столбца равны
• Выделение и выбор ячеек, если два столбца равны с помощью Kutools для Excel
• Выделение ячеек, где два столбца равны с помощью кода VBA
• Использование вспомогательного столбца с формулой для отметки совпадений и фильтрации пар равных значений

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

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

1. Выберите первый список данных, который вы хотите сравнить со вторым. Например, выберите диапазон A2:A7. Затем, на ленте нажмите Главная > Условное форматирование > Новое правило.

2. В диалоговом окне Нового правила форматирования выберите Использовать формулу для определения ячеек, которые нужно форматировать из списка Тип правила. В текстовом поле Форматировать значения, где эта формула верна, введите =$A2=$B2.

Совет: Здесь A2 и B2 — это начальные ячейки для ваших двух столбцов. Убедитесь, что ваша формула использует правильные ссылки на ячейки для вашего диапазона. Например, если ваши данные начинаются с третьей строки, соответственно отрегулируйте формулу.

3. Нажмите кнопку Формат в диалоговом окне Нового правила форматирования Выберите цвет фона на вкладке Заливка диалогового окна Формат ячеек чтобы визуально отметить совпадающие ячейки.

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

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

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

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

1. Выберите первый список, затем, удерживая клавишу Ctrl выберите второй список. Далее выберите Kutools > Сравнить ячейки чтобы открыть диалоговое окно Сравнить ячейки .

2. В окне Сравнить ячейки отметьте параметр Одинаковые ячейки Также отметьте Цвет фона заливки или Цвет шрифта заливки и выберите предпочитаемый цвет выделения.

3. Нажмите ОК. Ячейки в обоих столбцах, которые равны, теперь будут выбраны и выделены из первого списка. Это позволяет вам сразу обрабатывать, копировать или анализировать только эти ячейки.

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

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

1. Нажмите Разработчик > Visual Basic. В новом окне Microsoft Visual Basic for Applications выберите Вставка > Модуль. Введите следующий код в окно модуля:

Sub HighlightMatchingSelection()
    Dim rng       As Range
    Dim cell1     As Range, cell2 As Range
    Dim i         As Long
    On Error Resume Next
    Set rng = Application.InputBox( _
        Prompt:="Please select the two-column range to compare:", _
        Title:="Kutools for Excel", Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    If rng.Columns.Count <> 2 Then
        MsgBox "You must select exactly two adjacent columns.", vbExclamation
        Exit Sub
    End If
    Application.ScreenUpdating = False
    For i = 1 To rng.Rows.Count
        Set cell1 = rng.Cells(i, 1)
        Set cell2 = rng.Cells(i, 2)
        If CStr(cell1.Value) = CStr(cell2.Value) Then
            cell1.Interior.Color = vbYellow
            cell2.Interior.Color = vbYellow
        Else
            cell1.Interior.ColorIndex = xlColorIndexNone
            cell2.Interior.ColorIndex = xlColorIndexNone
        End If
    Next i
    
    Application.ScreenUpdating = True
    
    MsgBox "Highlighting complete for " & rng.Rows.Count & " rows.", vbInformation
End Sub 

2Чтобы запустить макрос, нажмите кнопку на панели инструментов редактора VBA или нажмите F5В появившемся диалоговом окне выберите два столбца, затем нажмите ОК .

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

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

1. В новом вспомогательном столбце (например, столбец C), введите следующую формулу в ячейку C2:

=A2=B2

2. Нажмите Enter. Результат покажет TRUE, если значения в столбцах A и B равны в этой строке, или FALSE, если нет.

3. Скопируйте формулу вниз по вспомогательному столбцу до конца ваших данных, перетаскивая маркер автозаполнения или дважды щелкнув его.

4. После того как все результаты сгенерированы, вы можете использовать функцию Фильтр Excel (Данные > Фильтр), чтобы отобразить только те строки, где вспомогательный столбец равен TRUE (т.е., где два столбца совпадают). Вы также можете сортировать, удалять несоответствующие строки или проводить дальнейший анализ по необходимости.

---

Лучшие инструменты для повышения производительности Office

Kutools для Excel решает большинство ваших проблем и увеличивает вашу продуктивность на 80%

• Супер строка формул (легкое редактирование нескольких строк текста и формул); Режим чтения (удобное чтение и редактирование большого количества ячеек); Вставка в отфильтрованный диапазон...
• Объединение ячеек/строк/столбцов с сохранением данных; Разделение содержимого ячеек; Объединение дублирующихся строк с подсчетом суммы/среднего значения... Предотвращение дублирования ячеек; Сравнение диапазонов...
• Выбор дублирующихся или уникальных строк; Выбор пустых строк (все ячейки пустые); Супер поиск и нечеткий поиск во многих книгах; Случайный выбор...
• Точное копирование нескольких ячеек без изменения ссылок на формулы; Автоматическое создание ссылок на несколько листов; Вставка маркеров, флажков и многое другое...
• Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Шифрование ячеек с паролем; Создание списка рассылки и отправка электронных писем...
• Извлечение текста, добавление текста, удаление по позиции, удаление пробелов; Создание и печать статистики страниц; Преобразование между содержимым ячеек и комментариями...
• Супер фильтр (сохранение и применение схем фильтрации к другим листам); Расширенная сортировка по месяцу/неделе/дню, частоте и другим параметрам; Специальный фильтр по жирному шрифту, курсиву...
• Объединение книг и листов; Объединение таблиц на основе ключевых столбцов; Разделение данных на несколько листов; Пакетное преобразование xls, xlsx и PDF...
• Группировка сводной таблицы по номеру недели, дню недели и другим параметрам... Отображение разблокированных, заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы/имена...

Читать далее... Полные функции 30-дневная бесплатная пробная версия... Приобрести... 

---

Office Tab - добавляет интерфейс с вкладками в Office и значительно упрощает вашу работу

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
• Открывайте и создавайте несколько документов в новых вкладках одного окна, а не в новых окнах.
• Увеличивает вашу продуктивность на 50% и экономит сотни кликов мышью каждый день!

Читать далее... Полные функции 30-дневная бесплатная пробная версия... Приобрести...