Перейти до основного матеріалу

Як порівняти значення, розділені комами, у двох клітинках і повернути повторювані чи унікальні значення в 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))

примітки:

1) Наведені вище дві формули можна застосувати лише в Excel для 365. Якщо ви використовуєте іншу версію Excel, ніж Excel для 365, спробуйте такий метод VBA.
2) Комірки для порівняння мають бути поруч одна з одною в одному рядку чи стовпці.

Порівняйте два стовпці зі значеннями, розділеними комами, і поверніть повторювані або унікальні значення за допомогою 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)

Найкращі інструменти продуктивності офісу

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями    Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул    Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок    Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу.  Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...

Опис


Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations