Как сравнить значения, разделённые запятыми, в двух ячейках Excel и получить дублирующиеся или уникальные значения?
Как показано на снимке экрана ниже, в Столбце1 и Столбце2 каждая ячейка содержит числа, разделённые запятыми. Как сравнить такие числа в Столбце1 с содержимым соответствующей ячейки в Столбце2 и получить все дублирующиеся или уникальные значения?
В этом руководстве описаны два метода, которые помогут вам легко справиться с этой задачей.
Сравнение значений, разделённых запятыми, в двух ячейках и возврат дублирующихся или уникальных значений с помощью формул
В этом разделе представлены две формулы для сравнения значений, разделённых запятыми, в двух ячейках и получения дублирующихся или уникальных значений между ними.
Примечание: приведённые ниже формулы работают только в Excel для Microsoft 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 легко и без усилий.
- Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
- Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.
Сравнение двух столбцов со значениями, разделёнными запятыми, и возврат дублирующихся или уникальных значений с помощью 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 Function3. После вставки кода в окно Модуль (Код), перейдите по меню Сервис > Ссылки, чтобы открыть окно Ссылки – 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)
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек