Как найти уникальные/повторяющиеся значения между двумя столбцами в Excel?

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

Например, чтобы определить, какие значения в столбце A отсутствуют в столбце C (то есть уникальны для столбца A), можно использовать функции VLOOKUP и ISNA следующим образом:

Введите эту формулу в ячейку B2:

=IF(ISNA(VLOOKUP(A2,$C$2:$C$13,1,FALSE)),"Yes","")

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

Если формула возвращает «Да» в столбце B, соответствующее значение в столбце A не встречается в столбце C — это означает, что оно уникально для столбца A. Если ячейка пустая, это значит, что значение присутствует в обоих столбцах. Этот метод прост и работает безупречно как для списков с дубликатами, так и без них.

Примечания:

• В формуле A2 относится к значению в столбце A, которое нужно проверить, а $C$2:$C$13 определяет диапазон, с которым вы хотите сравнить данные в столбце C. Настройте эти диапазоны в зависимости от ваших реальных данных.
• Если вы хотите найти значения, уникальные для столбца C (то есть те, которые встречаются в столбце C, но не в столбце A), введите следующую формулу в пустую ячейку рядом со столбцом C, например, D2, и перетащите её вниз:

  =IF(ISNA(VLOOKUP(C2,$A$2:$A$15,1,FALSE)),"Yes","")

• Убедитесь, что ваши диапазоны включают все соответствующие данные, и используйте абсолютные ссылки (с символами $) для диапазонов сравнения, если планируете копировать формулу на несколько строк.

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

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

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

1. Щелкните Kutools > Выбрать > Выбрать одинаковые и разные ячейки.

2. В диалоговом окне Выбрать одинаковые и разные ячейки выполните следующие шаги:

• (1) Укажите значения первого столбца, которые вы хотите проверить, в поле Найти значения в;
• (2) Установите столбец для сравнения в поле В соответствии с;
• (3) В разделе На основе выберите опцию По строке;
• (4) В разделе Найти выберите Разные значения, чтобы найти уникальные элементы;
• (5) При желании включите Заливку цветом фона и выберите цвет выделения для результатов;
• (6) Нажмите OK, чтобы выполнить сравнение.

Примечание: (1) Если ваши столбцы содержат заголовки, отметьте опцию Данные содержат заголовки. (2) Чтобы найти повторяющиеся значения, просто выберите То же значение в диалоговом окне сравнения.

Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас

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

Если вам нужно найти значения, уникальные для столбца C (но не в столбце A), просто поменяйте местами назначения диапазонов для Диапазона A и Диапазона B и повторите вышеуказанный процесс.

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

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

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

1. Выберите пустую ячейку рядом с первым столбцом, например B2. Затем щелкните Kutools > Помощник формул > Подсчет количества появлений слова.

2. В диалоговом окне Помощник формул введите абсолютный диапазон столбца сравнения (например, $C$2:$C$13) в поле Текст и выберите ячейку из первого столбца (например, A2) в поле Слово . Нажмите OK.

3. Используйте маркер заполнения, чтобы перетащить результат формулы на остальные ячейки в столбце B. Теперь вы увидите, сколько раз каждое значение из столбца A появляется в столбце C.

Результат 0 означает, что значение уникально для столбца A, тогда как результат 1 или больше указывает, что значение существует в обоих столбцах, и число указывает количество появлений.

Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас

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

Совет по сценарию: Этот метод идеален для проверки списков участников, SKU продуктов или любого случая, где требуется анализ частоты в двух источниках данных.

Напоминание об ошибке: Если диапазоны не соответствуют фактическим данным или если есть несоответствие в типах данных (числа как текст и т.д.), обязательно стандартизируйте свои списки перед использованием формулы.

Демонстрация: найти уникальные/повторяющиеся значения между двумя столбцами в Excel

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

Этот подход особенно подходит для пользователей, знакомых с Макросами, которые хотят минимизировать ручные действия или применить решение на нескольких листах или книгах.

1. Откройте свой файл Excel. Затем в ленте Excel перейдите к Разработчик > Visual Basic, чтобы открыть редактор VBA. В редакторе щелкните Вставить > Модуль и вставьте следующий код в окно модуля:

Пример: Выделить повторяющиеся значения между двумя выбранными столбцами.

Sub HighlightDuplicatesBetweenColumns()
    Dim rngA As Range, rngB As Range
    Dim cell As Range
    Dim ws As Worksheet
    Dim xTitleId As String
    xTitleId = "KutoolsforExcel"

    Set ws = ActiveSheet

    ' Ask user to select the first range
    Set rngA = Application.InputBox("Select the first range (e.g., Column A):", xTitleId, , , , , , 8)
    If rngA Is Nothing Then Exit Sub

    ' Ask user to select the second range
    Set rngB = Application.InputBox("Select the second range (e.g., Column C):", xTitleId, , , , , , 8)
    If rngB Is Nothing Then Exit Sub

    ' Highlight duplicates in both ranges
    For Each cell In rngA
        If cell.Value <> "" And WorksheetFunction.CountIf(rngB, cell.Value) > 0 Then
            cell.Interior.Color = RGB(255, 255, 0) ' Yellow
        End If
    Next cell

    For Each cell In rngB
        If cell.Value <> "" And WorksheetFunction.CountIf(rngA, cell.Value) > 0 Then
            cell.Interior.Color = RGB(255, 255, 0) ' Yellow
        End If
    Next cell

    MsgBox "Duplicate values have been highlighted in yellow.", vbInformation, xTitleId
End Sub

2. После вставки кода закройте редактор. Вернитесь в Excel, нажмите Alt + F8, выберите HighlightDuplicatesBetweenColumns из списка макросов и нажмите Выполнить. Когда вас попросят, выберите два целевых диапазона.

💡 Чтобы выделить уникальные значения вместо этого: Измените условие сравнения с > 0 на = 0 в обоих операторах If и при желании выберите другой цвет (например, зеленый).

Советы: Всегда создавайте резервную копию рабочего листа перед запуском макроса, так как изменения форматирования ячеек нельзя отменить с помощью Ctrl+Z. Вы также можете настроить цветовые коды (например, RGB(255,255,0)) в соответствии с вашими предпочтениями.

Устранение неполадок: Если вы получили ошибку, убедитесь:

• Вкладка Разработчик включена в вашем Excel.
• Ваша настройка безопасности макросов позволяет выполнять макросы.
• Выбранные диапазоны действительны и содержат сопоставимые значения.

Преимущества: Полностью автоматизирован, работает с файлами разных размеров.
Недостатки: Требуется разрешение на использование макросов и базовое знание VBA.

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

Выделение дубликатов между двумя столбцами:

1. Выберите диапазон в первом столбце, который вы хотите отформатировать (например, A2:A15).
2. Перейдите к Главная > Условное форматирование > Новое правило.
3. Выберите Использовать формулу для определения форматируемых ячеек.
4. Введите следующую формулу:
   

   =COUNTIF($C$2:$C$13, A2)>0

5. Нажмите Формат, выберите цвет выделения (например, желтый) и нажмите OK.

Это выделит ячейки в столбце A, которые также встречаются в столбце C.

Выделение уникальных значений в одном столбце:

1. Выберите тот же диапазон (например, A2:A15).
2. Повторите шаги выше, чтобы создать новое правило.
3. Введите формулу:
   

   =COUNTIF($C$2:$C$13, A2)=0

4. Выберите другой цвет, чтобы отличать уникальные значения.

Примените ту же логику к столбцу C, если необходимо, изменив ссылки в формуле (например, выделите C2:C13 на основе A2:A15).

✔ Преимущества: Мгновенная визуальная обратная связь; нет необходимости в дополнительных столбцах; динамически обновляется при изменении данных.
⚠ Меры предосторожности: Слишком много правил форматирования на больших наборах данных может замедлить производительность. Всегда дважды проверяйте ссылки на диапазоны, особенно когда столбцы не смежны.