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

Как показано на скриншоте ниже, есть два столбца - Столбец1 и Столбец2, каждая ячейка в столбце содержит числа, разделенные запятыми. Чтобы сравнить числа, разделенные запятыми, в Столбце1 с содержимым ячеек в той же строке Столбца2 и вернуть все повторяющиеся или уникальные значения, что вы можете сделать?

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

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

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

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

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

Возврат повторяющихся значений

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

=LET(x, TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),y,UNIQUE(x),z,UNIQUE(x,,1), TEXTJOIN(", ",TRUE,IF(ISERROR(MATCH(y,z,0)),y, "")))

Возврат уникальных значений

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

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

=TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),,1))

Примечания:

Раскройте магию Excel с Kutools AI

• Умное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это посредством простых команд.
• Пользовательские формулы: Создавайте индивидуальные формулы для оптимизации ваших рабочих процессов.
• Кодирование VBA: Пишите и внедряйте код VBA без особых усилий.
• Интерпретация формул: Легко разбирайтесь в сложных формулах.
• Перевод текста: Преодолейте языковые барьеры в ваших таблицах.

Улучшите возможности Excel с помощью инструментов на базе ИИ. Скачать сейчас и испытайте беспрецедентную эффективность!

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

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

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

1. В открывшейся книге нажмите клавиши Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.

2. В окне Microsoft Visual Basic for Applications нажмите Вставка > Модуль и скопируйте следующий код VBA в окно Модуль (Код).

Код VBA: Сравнение значений, разделенных запятыми, в двух ячейках и возврат повторяющихся/уникальных значений

Private Function COMPARE(Rng1, Rng2 As Range, Op As Boolean)
'Updated by Extendoffice 20221019
    Dim R1Arr As Variant
    Dim R2Arr As Variant
    Dim Ans1 As String
    Dim Ans2 As String
    Dim Separator As String
    Dim d1 As New Dictionary
    Dim d2 As New Dictionary
    Dim d3 As New Dictionary
    Application.Volatile

    Separator = ", "
    
    R1Arr = Split(Rng1.Value, Separator)
    R2Arr = Split(Rng2.Value, Separator)
    
    Ans1 = ""
    Ans2 = ""
    
    For Each ch In R2Arr
        If Not d2.Exists(ch) Then
            d2.Add ch, "1"
        End If
    Next
    
    If Op Then
        For Each ch In R1Arr
            If d2.Exists(ch) Then
                If Not d3.Exists(ch) Then
                    d3.Add ch, "1"
                    Ans1 = Ans1 & ch & Separator
                End If
            End If
        Next
        If Ans1 <> "" Then
            Ans1 = Mid(Ans1, 1, Len(Ans1) - Len(Separator))
        End If
        COMPARE = Ans1
    Else
        For Each ch In R1Arr
            If Not d1.Exists(ch) Then
                d1.Add ch, "1"
            End If
        Next
        
        For Each ch In R1Arr
            If Not d2.Exists(ch) Then
                If Not d3.Exists(ch) Then
                    d3.Add ch, "1"
                    Ans2 = Ans2 & ch & Separator
                End If
            End If
        Next
        For Each ch In R2Arr
            If Not d1.Exists(ch) Then
                If Not d3.Exists(ch) Then
                    d3.Add ch, "1"
                    Ans2 = Ans2 & ch & Separator
                End If
            End If
        Next
        If Ans2 <> "" Then
            Ans2 = Mid(Ans2, 1, Len(Ans2) - Len(Separator))
        End If
        COMPARE = Ans2
    End If

End Function

3. После вставки кода в окно Модуль (Код) перейдите в меню Сервис > Ссылки, чтобы открыть окно Ссылки – VBAProject, отметьте поле Microsoft Scripting Runtime и нажмите кнопку ОК.

4. Нажмите клавиши Alt + Q, чтобы закрыть окно Microsoft Visual Basic for Applications.

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

Возврат повторяющегося значения

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

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

=COMPARE(A2,B2,TRUE)

Возврат уникальных значений

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

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

=COMPARE(A2,B2,FALSE)