Сравните два столбца и найдите дубликаты в 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 for Applications».
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")

ТОЧН Функция:

Если вам нужно сравнение с учетом регистра, функция «ТОЧН» — это то, что вам нужно.

Пожалуйста, введите или скопируйте следующую формулу, нажмите клавишу «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 и вам нужно найти общие элементы из одного списка в другом, формулы поиска станут вашим решением для извлечения этих совпадений.

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

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

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

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

• Excel 365 и Excel 2021:

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

Результат:

Все соответствующие значения будут отображены, если совпадение найдено, в противном случае возвращается ошибка #Н/Д, см. скриншот:

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