Как сравнить значения, разделенные запятыми, в двух ячейках и вернуть повторяющиеся или уникальные значения в Excel?
Как показано на скриншоте ниже, есть два столбца — Column1 и Column2, каждая ячейка в столбце содержит числа, разделенные запятыми. Что можно сделать, чтобы сравнить числа, разделенные запятыми, в столбце 1 с содержимым ячейки в той же строке столбца 2 и вернуть все повторяющиеся или уникальные значения?
В этом руководстве представлены два метода, которые помогут вам выполнить эту задачу.
Сравнивайте значения, разделенные запятыми, в двух ячейках и возвращайте повторяющиеся или уникальные значения с помощью формул.
В этом разделе представлены две формулы, помогающие сравнить значения, разделенные запятыми, в двух ячейках и вернуть повторяющиеся или уникальные значения между ними.
Внимание: Следующие формулы работают только в Эксель для 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))
Заметки:
Сравните два столбца со значениями, разделенными запятыми, и верните повторяющиеся или уникальные значения с помощью VBA
Пользовательская функция, представленная в этом разделе, помогает сравнивать значения, разделенные запятыми, в двух указанных ячейках и возвращать повторяющиеся значения или уникальные значения между ними. Пожалуйста, сделайте следующее.
Возьмите тот же пример, что и выше, чтобы сравнить числа, разделенные запятыми, в столбце 1 с числами, разделенными запятыми, в той же строке столбца 2 и вернуть повторяющиеся или уникальные значения, попробуйте пользовательскую функцию в этом разделе.
1. В открывшейся книге нажмите кнопку другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модулии скопируйте следующий код 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 Function3. После вставки кода в Модуль (код) окно, нажмите кнопку Инструменты > Рекомендации для открытия Ссылки - VBAProject окно, проверьте Среда выполнения сценариев Microsoft и нажмите OK .
4. нажмите другой + Q ключи, чтобы закрыть Microsoft Visual Basic для приложений окно.
5. Теперь вам нужно применить две функции отдельно, чтобы вернуть повторяющиеся и уникальные значения из двух ячеек значений, разделенных запятыми.
Вернуть повторяющееся значение
Выберите ячейку для вывода повторяющихся чисел, в этом примере я выбираю ячейку D2, затем ввожу приведенную ниже формулу и нажимаю клавишу Enter чтобы получить повторяющиеся числа между ячейками A2 и B2.
Выберите ячейку формулы и перетащите ее маркер автозаполнения вниз, чтобы получить повторяющиеся числа между ячейками в других строках.
=COMPARE(A2,B2,TRUE)
Возвращать уникальные значения
Выберите ячейку для вывода уникальных чисел, в этом примере я выбираю ячейку E2, затем ввожу приведенную ниже формулу и нажимаю клавишу Enter ключ, чтобы получить уникальные числа между ячейками A2 и B2.
Выберите ячейку формулы и перетащите ее маркер автозаполнения вниз, чтобы получить уникальные числа между ячейками в других строках.
=COMPARE(A2,B2,FALSE)
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
