Як порівняти значення, розділені комами, у двох клітинках і повернути повторювані чи унікальні значення в Excel?
Як показано на знімку екрана нижче, є два стовпці – Стовпець1 і Стовпець2, кожна клітинка в стовпці містить числа, розділені комами. Що ви можете зробити, щоб порівняти числа, розділені комами, у стовпці 1 із вмістом клітинок у тому самому рядку стовпця 2 і повернути всі повторювані чи унікальні значення?
Цей посібник пропонує два методи, які допоможуть вам виконати це завдання.
Порівнюйте значення, розділені комами, у двох клітинках і повертайте повторювані або унікальні значення за допомогою формул
У цьому розділі наведено дві формули, які допоможуть порівняти значення, розділені комами, у двох клітинках і повернути повторювані або унікальні значення між ними.
примітки: наведені нижче формули працюють лише в Excel для 365. Якщо ви використовуєте інші версії Excel, спробуйте скористатися наведеним нижче методом VBA.
Візьміть наведені вище два стовпці як приклад, щоб порівняти числа, розділені комами, у стовпці 1 із числами, розділеними комами, у тому самому рядку стовпця 2 і повернути повторювані або унікальні значення, виконайте наведені нижче дії.
Повернути повторювані значення
1. Виберіть комірку, щоб вивести повторювані числа між двома вказаними комірками з числами, розділеними комами, у цьому випадку я вибираю комірку D2, потім вводжу наведену нижче формулу та натискаю кнопку Що натомість? Створіть віртуальну версію себе у ключ. Виберіть клітинку формули та перетягніть її Ручка автозаповнення вниз, щоб отримати повторювані числа між клітинками в інших рядках.
=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, потім введіть формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ. Виберіть клітинку формули та перетягніть її Ручка автозаповнення вниз, щоб отримати унікальні числа між клітинками в інших рядках.
=TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),,1))
примітки:
Порівняйте два стовпці зі значеннями, розділеними комами, і поверніть повторювані або унікальні значення за допомогою VBA
Функція, визначена користувачем, наведена в цьому розділі, допомагає порівнювати значення, розділені комами, у двох указаних клітинках і повертати повторювані значення або унікальні значення між ними. Будь ласка, зробіть наступне.
Візьміть той самий приклад, що й вище, щоб порівняти числа, розділені комами, у стовпці 1 із числами, розділеними комами, у тому самому рядку стовпця 2 і повернути повторювані чи унікальні значення, будь ласка, спробуйте функцію, визначену користувачем у цьому розділі.
1. У робочій книзі, що відкривається, натисніть інший + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.
2 В Microsoft Visual Basic для додатків вікна, натисніть Insert > Модуліі скопіюйте наступний код 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. Після вставлення коду в Модуль (Код) вікно, перейдіть до клацання Tools > посилання відкрити Посилання - VBAProject вікно, перевірте Виконання сценаріїв Microsoft поле, а потім натисніть кнопку OK кнопки.
4 Натисніть кнопку інший + Q клавіші, щоб закрити Microsoft Visual Basic для додатків вікна.
5. Тепер вам потрібно застосувати дві функції окремо, щоб повернути повторювані та унікальні значення з двох клітинок значень, розділених комами.
Повернути повторюване значення
Виберіть клітинку для виведення повторюваних чисел, у цьому прикладі я вибираю клітинку D2, потім введіть формулу нижче та натисніть кнопку Що натомість? Створіть віртуальну версію себе у щоб отримати повторювані числа між комірками A2 і B2.
Виберіть клітинку формули та перетягніть її маркер автозаповнення вниз, щоб отримати повторювані числа між клітинками в інших рядках.
=COMPARE(A2,B2,TRUE)
Повернути унікальні значення
Виберіть клітинку для виведення унікальних чисел, у цьому прикладі я вибираю клітинку E2, потім введіть формулу нижче та натисніть клавішу Що натомість? Створіть віртуальну версію себе у щоб отримати унікальні числа між комірками A2 і B2.
Виберіть клітинку формули та перетягніть її маркер автозаповнення вниз, щоб отримати унікальні числа між клітинками в інших рядках.
=COMPARE(A2,B2,FALSE)
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!