Выявление повторяющихся значений в двух столбцах 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 for ExcelАвтора Выберите одинаковые и разные ячейки функция-идеальный выбор. Он может идентифицировать повторяющиеся или уникальные значения, выделяя и выбирая нужные ячейки.

Нажмите Кутулс > Выберите > Выберите одинаковые и разные ячейки, В Выберите одинаковые и разные ячейки диалоговом окне выполните следующие действия:

1. Выберите исходные данные и сравниваемые данные в Найдите значения в и XNUMX% людей считают долгое ожидание – самой неприятной частью взаимодействия с компаниями ( ящики отдельно;
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. Press 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 клавишу, а затем перетащите маркер заполнения вниз, чтобы получить все результаты. Он вернет TRUE, если значения в одной строке столбцов A и B идентичны, и FALSE, если это не так. Смотрите скриншот:

=A2=B2

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

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

Пожалуйста, введите или скопируйте формулу ниже, нажмите Enter клавишу, а затем перетащите маркер заполнения вниз, чтобы получить все результаты. Он вернет Match, если значения одинаковы, и No Match, если они разные. Смотрите скриншот:

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

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

Если вам нужно сравнение с учетом регистра, ТОЧНАЯ функция это путь.

Пожалуйста, введите или скопируйте следующую формулу, нажмите Enter клавишу, а затем перетащите маркер заполнения вниз, чтобы получить все результаты. Он вернет Match, если значения точно совпадают, и No Match, если они различны. Смотрите скриншот:

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

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

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

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

1. Выберите данные из двух столбцов в Найдите значения в и XNUMX% людей считают долгое ожидание – самой неприятной частью взаимодействия с компаниями ( ящики отдельно;
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)

Результат:

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

Статьи по теме: