Сравнение двух столбцов и Поиск повторяющихся данных в Excel (полное руководство)

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

Сравнение двух столбцов для поиска Дублирующиеся значения

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

Сравнение двух столбцов построчно на совпадения

• Сравнение двух столбцов в одной строке с помощью формул
• Выбор и выделение совпадений в одной строке с помощью удобного инструмента — Kutools
• Сравнение двух столбцов и выделение совпадений в одной строке

Сравнение двух столбцов и извлечение совпадающих данных

Сравнение двух столбцов для поиска Дублирующиеся значения

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

Выделение дубликатов в двух столбцах с помощью Использовать условное форматирование

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

Шаг 1: выделите данные в столбце, в котором необходимо выделить дубликаты

В этом примере я выбираю диапазон A2:A10 — см. скриншот:

Шаг 2: примените функцию Использовать условное форматирование

1. Нажмите «Главная» > «Использовать условное форматирование» > «Создать правило» (см. скриншот).
   
2. В диалоговом окне «Создание правила форматирования» выполните следующие действия:
   • 2,1 Выберите «Использовать формулу для определения форматируемых ячеек» в списке «Выберите тип правила»;
   • 2,2 Введите следующую формулу в поле «Форматировать значения, для которых формула принимает значение ИСТИНА»;

     =COUNTIF($B$2:$B$10, A2)>0

   • Примечание. В приведённой выше формуле «B2:B10» — это диапазон данных, с которым выполняется сравнение, а «A2» — первая ячейка столбца, в котором нужно выделить повторяющиеся значения. Формула проверяет, встречается ли значение из ячейки A2 где-либо в столбце B. Не забудьте адаптировать ссылки на ячейки под свои данные.
   • 2,3 Затем нажмите кнопку «Формат».
   • 
3. В открывшемся диалоговом окне «Формат ячеек» выберите цвет для выделения повторяющихся значений и нажмите «OK».
   
4. Вернувшись в диалоговое окно «Создание правила форматирования», нажмите кнопку «OK».

Результат:

Теперь значения, являющиеся дубликатами в обоих столбцах A и B, выделены в столбце A, как показано на скриншоте:

Выбрать и выделить дубликаты в двух столбцах с помощью мощного инструмента — Kutools

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

Нажмите «Kutools» > «Выделить» > «Выбрать одинаковые/разные ячейки». В открывшемся диалоговом окне «Выбрать одинаковые/разные ячейки» выполните следующие действия:

1. Выберите данные для поиска и данные для сравнения в полях «Искать значения в» и «Сравнивать с» соответственно;
2. Выберите «Каждая строка» в разделе «На основе»;
3. Выберите «Одинаковые значения» в разделе «Найти»;
4. Укажите Цвет фона для выделения Дублирующиеся значения в разделе «Обработка выбранных результатов»;
5. Наконец, нажмите кнопку «OK».
   

Результат:

Теперь значения, дублирующиеся в обоих столбцах — A и B, — выделены и выбраны в столбце A, готовые к копированию в любые нужные ячейки. См. скриншот:

Поиск и извлечение дубликатов в двух столбцах с помощью формулы

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

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

=IF(ISERROR(MATCH(A2,$B$2:$B$10,0)),"",A2)

Примечание. В приведённой выше формуле «A2» — это первая ячейка столбца, в котором нужно найти дубликаты, а «B2:B10» — список данных для сравнения.

Результат:

Как видите, если данные из столбца A присутствуют в столбце B, отображается соответствующее значение; в противном случае ячейки остаются пустыми.

Выбрать дубликаты в двух столбцах с помощью кода VBA

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

Шаг 1: Откройте редактор модуля VBA и скопируйте код

1. Нажмите клавиши «Alt + F11», чтобы открыть окно Microsoft Visual Basic для приложений.
2. В открывшемся окне выберите «Вставка» > «Модуль», чтобы создать новый пустой модуль.
3. Затем скопируйте и вставьте приведённый ниже код в пустой модуль.
   Код VBA: поиск и выделение Дублирующиеся значения между двумя столбцами

   Sub Compare()
   'Update by Extendoffice
   Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range
   xTitleId = "KutoolsforExcel"
   On Error Resume Next
   Set Range1 = Application.Selection
   Set Range1 = Application.InputBox("Range1 :", xTitleId, Range1.Address, Type:=8)
   Set Range2 = Application.InputBox("Range2:", xTitleId, Type:=8)
   Application.ScreenUpdating = False
   For Each Rng1 In Range1
       xValue = Rng1.Value
       For Each Rng2 In Range2
           If xValue = Rng2.Value Then
               If outRng Is Nothing Then
                   Set outRng = Rng1
               Else
                   Set outRng = Application.Union(outRng, Rng1)
               End If
           End If
       Next
   Next
   outRng.Select
   Application.ScreenUpdating = True
   End Sub
   

Шаг 2: Выполните этот код VBA

1. После вставки кода нажмите клавишу «F5», чтобы запустить его. В первом диалоговом окне выберите список данных, из которого нужно выбрать дубликаты, и нажмите «OK».
   
2. Во втором диалоговом окне выберите список данных для сравнения и нажмите «OK» (см. скриншот).
   

Результат:

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

Сравнение двух столбцов построчно на совпадения

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

Сравнение двух столбцов в одной строке с помощью формул

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

• «Оператор равенства (=)»: самый простой способ сравнить две ячейки
• «Функция ЕСЛИ»: делает сравнение более информативным
• «Функция СОВПАД»: сравнение с учётом регистра

Использование оператора равенства (=):

Введите или скопируйте приведённую ниже формулу, нажмите клавишу «Enter», а затем протяните маркер заполнения вниз, чтобы получить все результаты. Формула вернёт значение ИСТИНА, если значения в строке столбцов A и B совпадают, и ЛОЖЬ — если не совпадают. См. снимок экрана:

=A2=B2

Функция ЕСЛИ:

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

Введите или скопируйте приведённую ниже формулу, нажмите клавишу «Enter», а затем протяните маркер заполнения вниз, чтобы получить все результаты. Формула вернёт «Совпадение», если значения совпадают, и «Нет совпадения» — если они различаются. См. снимок экрана:

=IF(A2=B2, "Match", "No Match")

Функция EXACT:

Если нужно сравнение с учётом регистра, используйте функцию «ТОЧНО».

Введите или скопируйте приведённую ниже формулу, нажмите клавишу «Enter», а затем протяните маркер заполнения вниз, чтобы получить все результаты. Формула вернёт «Совпадение», если значения полностью совпадают, и «Нет совпадения» — если они различаются. См. снимок экрана:

=IF(EXACT(A2,B2), "Match", "No match")   

Выбор и выделение совпадений в одной строке с помощью удобного инструмента — Kutools

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

Нажмите «Kutools» > «Сравнить ячейки». В открывшемся диалоговом окне «Сравнить ячейки» выполните следующие действия:

1. Выберите данные из двух столбцов в полях «Искать значения в» и «Сравнивать с» соответственно;
2. Выберите «Одинаковые ячейки» в разделе «Найти»;
3. Укажите Цвет фона для выделения совпадений в разделе «Обработка выбранных результатов»;
4. Наконец, нажмите кнопку «OK».
   

Результат:

Теперь совпадения в одной строке выделены и подсвечены в столбце A, поэтому их можно легко скопировать и вставить в любые нужные ячейки. См. снимок экрана:

Сравнение двух столбцов и выделение совпадений в одной строке

Эффективно сравнить два столбца и выделить совпадения в одной строке можно с помощью условного форматирования в Excel. Ниже — пошаговое руководство по поиску и подсветке совпадений по строкам:

Шаг 1: Выделите Диапазон данных

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

Шаг 2: Примените функцию Использовать условное форматирование

1. Нажмите «Главная» > «Условное форматирование» > «Создать правило». В открывшемся диалоговом окне «Создание правила форматирования» выполните следующие действия:
   • 2,1 Выберите «Использовать формулу для определения форматируемых ячеек» в списке «Выберите тип правила»;
   • 2,2 Введите приведённую ниже формулу в поле «Форматировать значения, для которых формула принимает значение ИСТИНА»;

     =$B2=$A2

   • 2,3 Затем нажмите кнопку «Формат».
   • 
2. В открывшемся диалоговом окне «Формат ячеек» выберите цвет для выделения совпадающих элементов и нажмите «OK».
   
3. Вернувшись в диалоговое окно «Создание правила форматирования», нажмите кнопку «OK».

Результат:

Теперь совпадающие значения в одной строке подсвечиваются сразу же. См. снимок экрана:

Сравнение двух столбцов и извлечение совпадающих данных

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

Допустим, в столбце A у вас указан список фруктов, а в столбце B — соответствующие объёмы продаж. Теперь вы хотите сопоставить эти данные с выборкой фруктов из столбца D и найти для них соответствующие объёмы продаж. Как в Excel получить связанные с ними значения из столбца B?

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

• Все версии Excel:

  =VLOOKUP(D2, $A$2:$B$6, 2, FALSE)

• Excel 365 и Excel 2021:

  =XLOOKUP(D2, $A$2:$A$6, $B$2:$B$6)

Результат:

Все соответствующие значения отобразятся при наличии совпадения; в противном случае вернётся ошибка #Н/Д. См. снимок экрана:

Связанные статьи: