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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рекомендации по сценарию: Данный метод идеально подходит для перекрёстной проверки списков участников, артикулов товаров или любых других случаев, когда требуется анализ частоты значений в двух исходных диапазонах.

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

Демонстрация: поиск уникальных/Дублирующиеся значения значений в двух столбцах 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. Нажмите кнопку Формат, выберите цвет выделения (например, жёлтый) и нажмите ОК.

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

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

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

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

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

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

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