Перейти к основному содержанию

Сравните два столбца, чтобы найти дубликаты в Excel (Полное руководство)

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

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

Сравнить два столбца на наличие совпадений построчно

Сравните два столбца и извлеките соответствующие данные.


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

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

Советы:
  • Дублирующееся правило в Условное форматирование is нечувствительный к регистру. Таким образом, Apple и Apple будут помечены как дубликаты.
  • Если вы хотите выделить дубликаты из столбца B, вам просто нужно сначала выбрать столбец B, а затем применить следующую формулу к Условное форматирование:
    =COUNTIF($A$2:$A$10, B2)>0

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

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

Внимание: Если вы хотите использовать это Выберите одинаковые и разные ячейки особенность, пожалуйста скачайте и установите Kutools для Excel первый.

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

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

Результат:

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

Советы:
  • Эта особенность поддерживает чувствительность к регистру сравнение во время тиканья Деликатный случай флажок в Выберите одинаковые и разные ячейки диалог;
  • Если вы хотите выбрать дубликаты из столбца B, вам просто нужно поменять местами два выбранных столбца в Найдите значения в и По коробки Выберите одинаковые и разные ячейки диалоговое окно;
  • Чтобы применить эту функцию, пожалуйста, скачайте и установите Kutools для Excel первый.

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

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

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

=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, что позволяет вам закрасить ячейки цветом или скопировать и вставить их по мере необходимости.

Советы:
  • Этот код VBA с учетом регистра;
  • Если вы хотите выбрать дубликаты из столбца B, вам просто нужно поменять местами два выбранных столбца при выборе диапазона данных.

Сравнить два столбца на наличие совпадений построчно

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

Внимание: Если вы хотите использовать это Сравнить ячейки особенность, пожалуйста скачайте и установите Kutools для Excel первый.

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

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

Результат:

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

Советы:
  • Эта особенность поддерживает чувствительность к регистру сравнение, если вы проверите Деликатный случай вариант в Сравнить ячейки диалоговое окно;
  • Если вы хотите выбрать совпадения из столбца B, вам просто нужно поменять местами два выбранных столбца в Найдите значения в и По коробки Сравнить ячейки диалоговое окно;
  • Чтобы применить эту функцию, пожалуйста, скачайте и установите Kutools для Excel первый.

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

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

Шаг 1. Выберите диапазон данных.

Выберите диапазон данных, совпадения строк которого вы хотите выделить.

Шаг 2. Примените функцию условного форматирования.

  1. Нажмите Главная > Условное форматирование > Новое правило. В Новое правило форматирования диалоговом окне выполните следующие действия:
    • 2.1 Выбрать Используйте формулу, чтобы определить, какие ячейки следует форматировать. из Выберите тип правила список;
    • 2.2 Введите приведенную ниже формулу в поле Формат значений, где эта формула истинна текстовое окно;
      =$B2=$A2
    • 2.3 Затем нажмите Формат .
  2. В выдвинутом Формат ячеек диалоговом окне укажите один цвет, которым хотите выделить повторяющиеся элементы. И нажмите OK.
  3. Когда он возвращает Новое правило форматирования диалоговое окно, нажмите OK .

Результат:

Теперь совпавшие значения в одной строке сразу выделяются, см. снимок экрана:

Советы:
  • Формула условного форматирования: нечувствительный к регистру.
  • Если вы хотите выделить ячейки с разными значениями, примените следующую формулу:
    =$B2<>$A2

Сравните два столбца и извлеките соответствующие данные.

Если вы имеете дело с двумя наборами данных в 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, см. снимок экрана:

Советы:
  • Если в приведенных выше формулах есть фрукты в столбце D, которым нет совпадения в столбце A, они вернут ошибку. Чтобы облегчить понимание этих ошибок, вы можете обернуть формулу функцией ЕСЛИОШИБКА:
    • Все версии Excel:
      =IFERROR(VLOOKUP(D2,$A$2:$B$10,2,FALSE), "No match found")
    • Excel 365 и Excel 2021:
      =IFERROR(XLOOKUP(D2, $A$2:$A$10, $B$2:$B$10),"No match found")
  • Для тех, кто интересуется расширенными формулами поиска, Kutools for Excel предоставляет впечатляющий набор расширенных формул поиска, которые поднимают традиционную функцию ВПР на новую высоту, предоставляя вам беспрецедентную точность и эффективность в задачах управления данными.

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

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

  • Найдите и выделите повторяющиеся строки в диапазоне
  • Иногда в вашем диапазоне данных на листе могут быть повторяющиеся записи, и теперь вы хотите найти или выделить повторяющиеся строки в диапазоне, как показано на следующих снимках экрана. Конечно, вы можете найти их одну за другой, проверяя строки. Но это не лучший выбор, если рядов сотни. Здесь я расскажу о некоторых полезных способах решения этой задачи.
  • Выделяйте повторяющиеся значения разными цветами
  • В Excel мы можем легко выделить повторяющиеся значения в столбце одним цветом с помощью условного форматирования, но иногда нам нужно выделить повторяющиеся значения разными цветами, чтобы быстро и легко распознать дубликаты, как показано на следующем снимке экрана. Как бы вы могли решить эту задачу в Excel?
  • Поиск, выделение, фильтрация, подсчет, удаление дубликатов в Excel
  • В Excel повторяющиеся данные возникают время от времени, когда мы записываем данные вручную, копируем данные из других источников или по другим причинам. Иногда дубликаты необходимы и полезны. Однако иногда повторяющиеся значения приводят к ошибкам или недопониманию. В этой статье будут представлены методы быстрого выявления, выделения, фильтрации, подсчета, удаления дубликатов по формулам, правилам условного форматирования, сторонним надстройкам и т. Д. В Excel.
  • Удалите дубликаты и замените пустыми ячейками
  • Обычно, когда вы применяете команду «Удалить дубликаты» в Excel, она удаляет все повторяющиеся строки. Но иногда вы хотите, чтобы пустые ячейки заменили повторяющиеся значения, в этой ситуации команда «Удалить дубликаты» не будет работать. Эта статья поможет вам удалить дубликаты и заменить их пустыми ячейками в Excel.
Comments (48)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I'm trying to count duplicates from two columns (Column A is the name Column B is the address) how could I do it?
This comment was minimized by the moderator on the site
Hi, Committed, if you want to count total number of duplicates in two columns, firstly, use a formula to count the duplicates in first column:=COUNTIF(A2:A7, A2), A2:A7 is the range of the first column, A2 is the first data except header of the first column. Then use the same formula (change reference) to count the duplicates in second column. Finaly, use SUM function to get the total number of duplicates in two columns.Here is a tutorial which list almost all scenarios about comparing columns, if you are interested in this, you can visit:https://www.extendoffice.com/documents/excel/6392-excel-compare-two-columns.html</div>;
This comment was minimized by the moderator on the site
BUT I WANT DUPLICATE VALUE BEFORE ANY SPACING, IN COLUMN B WE FOUND DUPLICATE VALUES THAT'S GREAT BUT THERE IS SPACE I WANT THOSE VALUES WITHOUT SPACES, HOW CAN I?
This comment was minimized by the moderator on the site
Hi, just remove the spaces by using the Go to special function to find the space cell, then remove them by clicking Delete key after finding the duplicate values.
This comment was minimized by the moderator on the site
Здравствуйте. Макрос приведенный здесь выделяет дубликаты ячеек, но при попытке редактирования какой-либо ячейки выделение снимается сразу со всех дубликатов, как сделать что бы этого не происходило?
This comment was minimized by the moderator on the site
so lovely yeah!
This comment was minimized by the moderator on the site
I LOVE THIS SITE!
This comment was minimized by the moderator on the site
Thank You!
This comment was minimized by the moderator on the site
This shit don't work... just getting

outRng.Select object variable or with block variable not set??
This comment was minimized by the moderator on the site
great demo guys,keep rocking
This comment was minimized by the moderator on the site
Thank You Very Much Guys
This comment was minimized by the moderator on the site
that was really great.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations