Порівняйте два стовпці щодо збігів та відмінностей у Excel

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

Цей підручник охоплює

1. Порівняйте два стовпці рядок за рядком 1.1 Порівняйте клітинки в одному рядку для точного збігу 1.2 Порівняйте клітинки в одному рядку для точного збігу чи регістру (за формулою IF) 1.3 Порівняйте комірки в одному рядку та виділіть дані, що відповідають або не узгоджуються (за допомогою умовного форматування) 1.4 Порівняйте два стовпці рядок за рядком та виділіть значення невідповідності (за допомогою VBA)

2． Порівняйте два стовпці в клітинках і виберіть або виділіть повторювані або унікальні дані 2.1 Порівняйте дві клітинки клітинок за клітинками та покажіть результат порівняння в іншому стовпці (використовуючи формулу) 2.2 Порівняння та виділення повторюваних або унікальних даних (за допомогою умовного форматування) 2.3 Порівняння та вибір або виділення повторюваних або унікальних даних (за допомогою зручного інструменту) 2.4 Порівняйте два стовпці та перелічіть точні дублікати в іншому стовпці (використовуючи код VBA) 2.5 Порівняйте два стовпці та виділіть дублікати (за допомогою коду VBA)

3． Порівняйте кілька стовпців в одному рядку 3.1 Знайти повні збіги у всіх клітинках одного рядка (використовуючи формулу IF) 3.2 Знайти відповідне в будь-яких двох клітинках одного рядка (за формулою IF) 3.3 Знайти повні збіги у всіх клітинках одного рядка та виділити (за допомогою умовного форматування) 3.4 Порівняйте кілька стовпців та виділіть відмінності між рядками

4. Порівняйте два стовпці та знайдіть відсутні точки даних 4.1 Порівняйте та знайдіть відсутні точки даних (за допомогою формули VLOOKUP або MATCH) 4.2 Порівняйте два стовпці та перелічіть відсутні дані нижче (за формулою INDEX) 4.3 Порівняйте та знайдіть відсутні точки даних і перелічіть їх в інший стовпець (за допомогою зручного інструменту) 4.4 Порівняйте два стовпці та перелічіть відсутні дані нижче (за допомогою VBA)

5． Порівняйте дві колонки (дати), якщо вони більші або менші 5.1 Порівняйте два стовпці, якщо вони більші або менші (за формулою) 5.2 Порівняйте два стовпці, якщо вони більше або менше форматування (за умови умовного форматування)

6． Порівняйте два стовпці та поверніть значення в іншому стовпці 6.1 Витягніть точні дані відповідності (за допомогою формули або зручного інструменту) 6.2 Витягніть дані часткового збігу (за формулою)

7. Порівняйте дві колонки та підрахуйте збіги чи різницю 7.1 Порівняйте два стовпці та підрахуйте збіги (за допомогою формули SUMPRODUCT) 7.2 Порівняйте два стовпці та підрахуйте збіги або відмінності (за допомогою зручного інструменту)

8. Порівняйте дві колонки з підстановкою

9. Видаліть відмінності або збіги

10. Порівняйте два стовпці і обчисліть процентну зміну між ними

11. Порівняйте два діапазони 11.1 Порівняйте два діапазони за клітинками 11.2 Порівняйте два діапазони, якщо дані в одному порядку

примітки

У цьому посібнику він містить деякі приклади даних для кращого пояснення методів порівняння двох стовпців у різних випадках. Залежно від вашого набору даних, можливо, вам доведеться змінити або відкоригувати деякий вміст (посилання), однак основні принципи залишаться незмінними. Або завантажте зразки кожного випадку, якщо ви хочете лише перевірити, чи працюють методи чи ні.

---

Нижче наведено набір даних, де мені потрібно перевірити в одному рядку, чи імена в стовпці A однакові з іменами в стовпці B чи ні.

Клацніть, щоб завантажити зразок файлу

1.1 Порівняйте клітинки в одному рядку для точного збігу

---

Як правило, якщо ви хочете порівняти два стовпці рядком за рядком для точного збігу, ви можете використовувати формулу нижче:

прес вводити клавішу та перетягніть маркер заповнення до комірки D8. Якщо формула повертає TRUE, значення двох стовпців абсолютно однакові, якщо повертає FALSE, вони різні.

1.2 Порівняйте клітинки в одному рядку для точного збігу чи регістру (за формулою IF)

---

Якщо ви хочете порівняти два стовпці рядок за рядком для чутливих до регістру або отримати більше опису, наприклад Match, Mismatch, ви можете використовувати функцію IF.

Порівнюючи клітинки в одному рядку для точного збігу

Якщо ви хочете використати тексти «Відповідність» та «Невідповідність» для опису результатів порівняння, використовуйте формулу нижче:

прес вводити , щоб отримати перший результат, а потім перетягніть маркер автоматичного заповнення до комірки D8.

Порівняння клітинок у тому самому рядку для збігу регістру, що не чутливий

Якщо ви хочете порівняти клітинки з урахуванням регістру, ви можете використати формулу нижче:

прес вводити , щоб отримати перший результат, а потім перетягніть маркер автоматичного заповнення до комірки E8.

Зауваження

У наведених вище формулах ви можете змінити тексти “Match” та “Mathatch” на власний опис.

1.3 Порівняйте комірки в одному рядку та виділіть дані, що відповідають або не узгоджуються (за допомогою умовного форматування)

---

Якщо ви хочете виділити відповідність або різні значення, Умовне форматування ця функція може вам допомогти.

1. Виберіть два стовпці, які використовуються для порівняння з (B2: C8, за винятком заголовків стовпців), а потім натисніть Головна > Умовне форматування > Нове правило.

2. У вискакуванні Нове правило форматування натисніть, щоб вибрати За допомогою формули визначте, які клітинки потрібно форматувати в Виберіть тип правила розділ, а потім введіть = $ B2 = $ C2 в текстове поле Форматувати значення, де ця формула відповідає дійсності.

3. Тепер натисніть сформований щоб відобразити Формат ячеек діалогове вікно, потім під Заповнювати на вкладці виберіть один колір, який вам потрібен, щоб виділити збіги.

Або ви можете змінити розмір шрифту, розмір шрифту, межі комірок або формат цифр, щоб вирівняти збіги, як це потрібно на інших вкладках.

4. клацання OK > OK щоб закрити діалогові вікна, тоді клітинки в тому самому рядку будуть виділені, якщо вони однакові.

Якщо ви хочете виділити значення невідповідності, ви можете використовувати це в = $ B2 <> $ C2 в Форматувати значення, де ця формула відповідає дійсності текстове поле в Змінити правило форматування діалог

Тоді відмінності двох стовпців в одному рядку будуть виділені вказаним кольором.

1.4 Порівняйте два стовпці рядок за рядком та виділіть значення невідповідності (за допомогою VBA)

---

Якщо ви хочете порівняти два стовпці рядок за рядком із кодом VBA, цей посібник вас задовольнить.

1. Увімкніть аркуш, що містить два стовпці, що використовуються для порівняння, натисніть Alt + F11 клавіші, щоб увімкнути Microsoft Visual Basic для додатків вікна.

2. У діалоговому вікні, що з’являється, натисніть Insert > Модулі.

3. Потім скопіюйте та вставте нижче макрос у новий сценарій модуля.

VBA: Порівняйте два стовпці рядок за рядком та виділіть відмінності

Sub ExtendOffice_HighlightColumnDifferences()
'UpdatebyKutools20201016
Dim xRg As Range

Dim xWs As Worksheet

Dim xFI As Integer

On Error Resume Next

SRg:

Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)

If xRg Is Nothing Then Exit Sub

 

If xRg.Columns.Count <> 2 Then

    MsgBox "Please select two columns"

    GoTo SRg

End If

 

Set xWs = xRg.Worksheet

For xFI = 1 To xRg.Rows.Count

    If Not StrComp(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2), vbBinaryCompare) = 0 Then

        xWs.Range(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2)).Interior.ColorIndex = 7 'you can change the color index as you need.

End If

Next xFI

End Sub

4. натисніть F5 клавішу для запуску коду, тоді з'явиться діалогове вікно для вибору двох стовпців.

5. клацання OK. Потім відмінності двох стовпців були виділені кольором тла.

Зауваження

Ви можете змінити колір виділення відповідно до власних потреб, змінивши індекс кольору в коді, посилання на індекс кольору:

---

У цій частині набір даних показаний, як показано нижче, і ви хочете знайти всі значення, що знаходяться як у стовпці B, так і в стовпці C одночасно, або знайти значення лише у стовпці B.

Клацніть, щоб завантажити зразок файлу

2.1 Порівняйте дві клітинки клітинок за клітинками та покажіть результати порівняння в іншій колонці (за формулою)

---

Тут ви можете використовувати формулу, яка поєднується з функцією IF та COUNTIF, щоб порівняти два стовпці та знайти значення, які є у стовпці B, але не в стовпці C.

прес вводити клавішу та перетягніть маркер автозаповнення до комірки D8.

Зауваження

1. Ця формула порівнює два стовпці без урахування регістру.

2. Ви можете змінити опис "Ні в С" та "Так в С" на інші.

3. Якщо ви хочете порівняти цілі два стовпці, змініть фіксований діапазон $ C $ 2: $ C $ 8 на $ C: $ C.

2.2 Порівняння та виділення повторюваних або унікальних даних (за допомогою умовного форматування)

---

повне г, повне г,, показали, від, номер, XNUMX Умовне форматування функція в Excel потужна, тут ви можете використовувати її для порівняння двох клітинок стовпців за клітинками, а потім виділити відмінності або збіги, як вам потрібно.

Виділіть усі дублікати або унікальні значення у двох стовпцях

1. Виберіть два стовпці, з якими ви будете порівнювати, і натисніть Головна > Умовне форматування > Виділіть правила клітин > Повторювані значення.

2. У вискакуванні Повторювані значення діалоговому вікні, виберіть потрібний формат виділення зі спадного списку значень за допомогою.

3. клацання OK. Потім були виділені дублікати у двох стовпцях.

Зауваження

Якщо ви хочете виділити унікальні значення (відмінності) у двох стовпцях, також клацніть Головна > Умовне форматування > Виділіть правила клітин > Повторювані значення щоб відобразити Повторювані значення діалогове вікно, змініть дублювати до Унікальний у розкривному списку ліворуч, потім виберіть інший формат зі значень зі спадним списком, натисніть OK.

Унікальні значення будуть виділені.

Знайдіть і виділіть значення у стовпці B, якщо це також у стовпці C

Якщо ви хочете виділити значення у стовпці B, які також є у стовпці C, Cумовне форматування функція також може зробити вам послугу.

1. Виберіть стовпець B2: B8, натисніть Головна > Умовне форматування > Нове правило.

2 В Нове правило форматування діалогове вікно, виберіть За допомогою формули визначте, які клітинки потрібно форматувати від Виберіть тип правила розділ, а потім введіть = COUNTIF ($ C $ 2: $ C $ 8, $ B2)> 0 в текстове поле Форматувати значення, де ця формула відповідає дійсності.

3. клацання сформований йти до Формат ячеек діалогове вікно, під Заповнювати виберіть один колір, щоб виділити збіги.

Ви можете використовувати інші формати для виділення значень на вкладках Шрифт, Число, Межа.

4. клацання OK > OK. Тоді значення у стовпці B, які також існують у стовпці C, були виділені вказаним кольором.

Якщо ви хочете виділити значення лише у стовпці B, але не в стовпці C, повторіть вищевказані кроки, але змініть формулу на кроці 2 на = COUNTIF ($ C $ 2: $ C $ 8, $ B2) = 0 в Нове правило форматування діалог

Потім виберіть інший колір для форматування значень.

Зауваження

Тут умовне форматування порівнює два стовпці без чутливих регістрів.

2.3 Порівняння та вибір або виділення повторюваних або унікальних даних (за допомогою зручного інструменту)

---

Іноді, порівнявши дві колонки, ви можете вжити інші дії щодо збігів чи різниці, наприклад, виділення, видалення, копіювання тощо. У цьому випадку зручний інструмент - Виберіть однакові та різні клітини of Kutools для Excel може безпосередньо вибрати збіги або різницю для кращого виконання наступної операції, а також може безпосередньо виділити значення.

після безкоштовна установка Kutools для Excel, будь-ласка, виконайте наведені нижче дії:

1. клацання Кутулс > вибрати > Виберіть однакові та різні клітини.

2. Потім у Виберіть однакові та різні клітини діалогове вікно, будь-ласка, зробіть, як показано нижче:

1) У Знайдіть значення в і Відповідно до розділи, виберіть два стовпці, з якими потрібно порівняти;

2) Виберіть Кожен ряд варіант;

3) Виберіть Ті самі значення or Різні цінності як вам потрібно;

4) Якщо ви хочете виділити значення, виберіть потрібний колір.

3. клацання Ok, спливає діалогове вікно, щоб нагадати вам кількість знайдених значень, натисніть OK щоб закрити діалогове вікно. І в той же час були вибрані значення, тепер ви можете видалити, скопіювати або виконати інші операції.

Якщо ви встановите прапорець Заливка кольором і Колір шрифту заливки прапорці, результат відображається так:

Зауваження

Якщо ви хочете порівняти з урахуванням регістру, перевірте Чутливий до справи варіант.

Цей інструмент підтримує порівняння двох стовпців на різних робочих аркушах.

2.4 Порівняйте два стовпці та перелічіть точні дублікати в іншому стовпці (використовуючи код VBA)

---

Якщо ви хочете перерахувати відповідні значення в іншому стовпці після порівняння двох стовпців по клітинках, тут вам може допомогти наведений нижче код макросу.

1. Увімкніть аркуш, для якого потрібно порівняти два стовпці, а потім натисніть Alt + F11 клавіші для відображення Microsoft Visual Basic для додатків вікна.

2. клацання Insert > Модулі в Microsoft Visual Basic для додатків вікна.

3. Потім скопіюйте та вставте наведений нижче код у новий порожній сценарій модуля.

VBA: Перерахуйте дублікати поруч із стовпцем після порівняння двох стовпців

Sub ExtendOffice_FindMatches()
'UpdatebyKutools20201019
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range

Dim xIntSR, xIntER, xIntSC, xIntEC As Integer

On Error Resume Next

SRg:

Set xRgC1 = Application.InputBox("Select first column:", "Kutools for Excel", , , , , , 8)

If xRgC1 Is Nothing Then Exit Sub

If xRgC1.Columns.Count <> 1 Then

    MsgBox "Please select single column"

    GoTo SRg

End If

SsRg:

Set xRgC2 = Application.InputBox("Select the second column:", "Kutools for Excel", , , , , , 8)

If xRgC2 Is Nothing Then Exit Sub

If xRgC2.Columns.Count <> 1 Then

    MsgBox "Please select single column"

    GoTo SsRg

End If

Set xWs = xRg.Worksheet

 

For Each xRgF1 In xRgC1

    For Each xRgF2 In xRgC2

        If xRgF1.Value = xRgF2.Value Then xRgF2.Offset(0, 1) = xRgF1.Value

    Next xRgF2

Next xRgF1

End Sub

4. прес F5 для запуску коду, є два діалогових вікна, що вискакують одне за іншим, щоб ви могли вибрати два стовпці окремо.

примітки: Будь ласка, спочатку виберіть лівий стовпець, потім виберіть правий стовпець у другому діалоговому вікні, інакше дублікати замінять вихідні дані у другому стовпці.

5. клацання OK > OK, тоді збіги були автоматично перераховані у правій колонці двох стовпців.

Зауваження

Код VBA порівнює два стовпці з урахуванням регістру.

2.5 Порівняйте два стовпці та виділіть дублікати (за допомогою коду VBA)

---

Якщо ви хочете порівняти два стовпці клітинку за коміркою, а потім виділити збіги, ви можете спробувати код нижче.

1. Увімкніть аркуш, для якого потрібно порівняти два стовпці, а потім натисніть Alt + F11 клавіші для відображення Microsoft Visual Basic для додатків вікна.

2. клацання Insert > Модулі в Microsoft Visual Basic для додатків вікна.

3. Скопіюйте код макросу нижче та вставте їх у нове порожнє місце Модулі сценарій

VBA: Порівняйте два стовпці та виділіть дублікати

Sub ExtendOffice_CompareTwoRanges()
'UpdatebyKutools20201019

Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range

SRg:

Set xRgC1 = Application.InputBox("Select the column you want compare according to", "Kutools for Excel", , , , , , 8)

If xRgC1 Is Nothing Then Exit Sub

If xRgC1.Columns.Count <> 1 Then

    MsgBox "Please select a single column"

    GoTo SRg

End If

SsRg:

Set xRgC2 = Application.InputBox("Select the column you want to highlight duplicates in:", "Kutools for Excel", , , , , , 8)

If xRgC2 Is Nothing Then Exit Sub

If xRgC2.Columns.Count <> 1 Then

    MsgBox "Please select a single column"

    GoTo SsRg

End If

 

    For Each xRgF1 In xRgC1

        For Each xRgF2 In xRgC2

            If xRgF1.Value = xRgF2.Value Then

               xRgF2.Interior.ColorIndex = 38 '(you can change the color index as you need)

            End If

        Next

    Next

End Sub

4. прес F5 клавіша для запуску коду. У першому діалоговому вікні, що вискакує, виберіть стовпець, за яким потрібно порівняти повторювані значення.

5. клацання OK. У другому діалоговому вікні виберіть стовпець, у якому потрібно виділити повторювані значення.

6. клацання OK. Тепер дублікати у другому стовпці були виділені кольором тла після порівняння з першим стовпцем.

Зауваження

1. Код порівнює стовпці з урахуванням регістру.

2. Ви можете змінити колір виділення відповідно до власних потреб, змінивши індекс кольору в коді, посилання на індекс кольору:

---

Іноді вам може знадобитися порівняти більше двох стовпців в одному рядку, наприклад, набір даних, як показано нижче. Тут, у цьому розділі, він перелічує різні методи порівняння кількох стовпців.

Клацніть, щоб завантажити зразок файлу

3.1 Знайти повні збіги у всіх клітинках одного рядка (використовуючи формулу IF)

---

Щоб знайти повні збіги між стовпцями в одному рядку, нижче допоможе формула IF.

 

Якщо комірки в одному рядку збігаються між собою, відображається “Повна відповідність” або “Ні”.

прес вводити , щоб отримати перший результат порівняння, а потім перетягніть маркер автоматичного заповнення до комірки E7.

Зауваження

1. Формула порівнює стовпці без урахування регістру.

2. Якщо вам потрібно порівняти більше або дорівнювати трьом стовпцям, ви можете використати формулу нижче:

 

У формулі 3 - це кількість стовпців, ви можете змінити її відповідно до своїх потреб.

3.2 Знайти відповідне в будь-яких двох клітинках одного рядка (за формулою IF)

---

Іноді ви хочете дізнатись, чи відповідають будь-які два стовпці в одному рядку, ви можете використовувати формулу IF.

 

У цій формулі вам потрібно порівняти будь-яку пару клітинок того самого рядка. “Збіг” означає, що є відповідні дві клітинки, якщо немає відповідних комірок, формула відображає “Не”, ви можете змінювати тексти, як вам потрібно.

прес вводити і перетягніть маркер заповнення до комірки E7.

Зауваження

1. Ця формула не підтримує нечутливий регістр.

2. Якщо для порівняння потрібно багато стовпців, порівняння будь-якої пари клітинок у формулі може бути занадто довгим. У цьому випадку ви можете використати формулу нижче, яка поєднує функції IF та COUNTIF.

 

3.3 Знайти повні збіги у всіх клітинках одного рядка та виділити (за допомогою умовного форматування)

---

Якщо ви хочете виділити рядки, які всі клітинки збігаються між собою, ви можете використовувати Умовне форматування функція в Excel.

1. Виберіть діапазон, який ви використовуєте, і натисніть Головна > Умовне форматування > Нове правило.

2 В Нове правило форматування діалогове вікно, виберіть За допомогою формули визначте, які клітинки потрібно форматувати від Виберіть тип правила розділу, тоді ви можете використовувати одну з наведених нижче формул у Форматувати значення, де ця формула відповідає дійсності текстове вікно.

Or

 

примітки: Якщо кількість стовпців більше трьох, припустимо, 5, формули слід змінити на:

Or

3. клацання сформований , щоб перейти до діалогового вікна "Форматувати комірки", а потім виберіть один колір заливки або інше форматування комірок, щоб перевершити рядки.

4. клацання OK > OK, тепер будуть виділені лише рядки, в яких всі клітинки збігаються.

Зауваження

Наведені вище формули не підтримують регістр.

3.4 Порівняйте кілька стовпців та виділіть відмінності між рядками

---

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

1. Виберіть діапазон, для якого потрібно виділити різницю рядків, і натисніть Головна > Знайти та вибрати > Перейти до спеціального.

2. У вискакуванні Перейти до спеціального діалогове вікно, перевірте Відмінності рядків варіант.

3. клацання OK. Тепер вибрано відмінності між рядками.

4. Тепер тримайте клітинки виділеними, клацніть Головна > Колір заливки щоб вибрати один колір зі спадного меню.

Зауваження

Цей метод порівнює клітини без урахування регістру.

---

Припустимо, що є два стовпці, стовпець B довший, а стовпець C коротший, як показано нижче. Як порівняти зі стовпцем B, як дізнатись відсутні дані у стовпці C?

Клацніть, щоб завантажити зразок файлу

4.1 Порівняйте та знайдіть відсутні точки даних (за допомогою формули VLOOKUP або MATCH)

---

Якщо вам потрібно лише визначити, яких даних не вистачає після порівняння двох стовпців, ви можете скористатися однією з наведених нижче формул:

Or

 

прес вводити клавішу, а потім перетягніть маркер автоматичного заповнення над комірку D10. Тепер, якщо дані знаходяться як у стовпці B, так і в стовпці C, формула повертає FALSE, якщо дані знаходяться лише у стовпці B, але в стовпці C відсутні, формула повертає TRUE.

Зауваження

Вище двох формул порівнюють дані без урахування регістру.

4.2 Порівняйте два стовпці та перелічіть відсутні дані нижче (за формулою INDEX)

---

Якщо після порівняння двох стовпців ви хочете вказати відсутні дані під коротшим стовпцем, вам може допомогти формула масиву INDEX.

У нижченаведеній комірці коротшого стовпця, припускаючи клітинку C7, введіть нижче формулу:

 

прес Shift + Ctrl + Enter , щоб отримати перші відсутні дані, а потім перетягніть маркер автоматичного заповнення вниз, доки не поверне значення помилки # N / A.

Тоді ви можете видалити значення помилки, і всі відсутні дані були перераховані нижче коротшого стовпця.

Зауваження

Ця формула порівнює клітини без урахування регістру.

4.3 Порівняйте та знайдіть відсутні точки даних і перелічіть їх в інший стовпець (за допомогою зручного інструменту)

---

Якщо ви хочете виконати деякі подальші дії щодо відсутніх даних після порівняння двох стовпців, наприклад, перерахування відсутніх даних в іншому стовпці або доповнення відсутніх даних нижче коротшого стовпця, ви можете спробувати зручний інструмент-Виберіть однакові та різні клітини of Kutools для Excel.

1. клацання Кутулс > вибрати > Виберіть однакові та різні клітини.

2 В Виберіть однакові та різні клітини діалогове вікно, виконайте вказані нижче дії

1) В Знайти значення в виберіть довший стовпець, що містить повний список.
In Відповідно до розділ, виберіть коротший стовпець, в якому відсутні деякі дані.

2) Виберіть Кожен ряд варіант.

3) Виберіть Різні цінності варіант.

3. клацання Ok, спливає діалогове вікно, щоб нагадати вам кількість відсутніх даних, натисніть OK щоб закрити його. Тоді вибрані відсутні дані.

Тепер ти можеш натиснути Ctrl + C клавіші, щоб скопіювати вибрані відсутні дані та вставити їх, натиснувши Ctrl + V клавіші під коротшим стовпцем або інший новий стовпець, як вам потрібно.

Зауваження

Позначаючи Не враховує регістр параметр у діалоговому вікні «Вибрати однакові та різні клітинки» порівнює два стовпці з урахуванням регістру.

4.4 Порівняйте два стовпці та перелічіть відсутні дані нижче (за допомогою VBA)

---

Ось макрокод, який може заповнити відсутні дані під двома стовпцями.

1. Відкрийте аркуш, який ви використовуєте, натисніть Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. клацання Insert > Модулі створити новий модуль для розміщення нижче коду VBA в.

VBA: Порівняйте два стовпці та заповніть відсутні дані

Sub ExtendOffice_PullUniques()
'UpdatebyKutools20201021

Dim xRg, xRgC1, xRgC2, xFRg1, xFRg2 As Range

Dim xIntR, xIntSR, xIntER, xIntSC, xIntEC As Integer

Dim xWs As Worksheet

On Error Resume Next

SRg:

Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)

If xRg Is Nothing Then Exit Sub

If xRg.Columns.Count <> 2 Then

    MsgBox "Please select two columns as a range"

    GoTo SRg

End If

Set xWs = xRg.Worksheet

 

xIntSC = xRg.Column

xIntEC = xRg.Columns.Count + xIntSC - 1

xIntSR = xRg.Row

xIntER = xRg.Rows.Count + xIntSR - 1

 

Set xRg = xRg.Columns

Set xRgC1 = xWs.Range(xWs.Cells(xIntSR, xIntSC), xWs.Cells(xIntER, xIntSC))

Set xRgC2 = xWs.Range(xWs.Cells(xIntSR, xIntEC), xWs.Cells(xIntER, xIntEC))

xIntR = 1

For Each xFRg In xRgC1

    If WorksheetFunction.CountIf(xRgC2, xFRg.Value) = 0 Then

        xWs.Cells(xIntER, xIntEC).Offset(xIntR) = xFRg

        xIntR = xIntR + 1

    End If

Next

xIntR = 1

For Each xFRg In xRgC2

    If WorksheetFunction.CountIf(xRgC1, xFRg) = 0 Then

        xWs.Cells(xIntER, xIntSC).Offset(xIntR) = xFRg

        xIntR = xIntR + 1

    End If

Next

End Sub

3. Потім натисніть F5 для запуску коду, вискакує діалогове вікно для вибору двох стовпців, що порівнюють.

4. клацання OK. Тепер відсутні дані вказані під двома стовпцями.

Зауваження

Код порівнює клітинки без чутливих до регістру.

---

Якщо є два стовпці дат, як показано на знімку екрана, можливо, ви захочете порівняти, яка дата пізніше в тому ж рядку.

Клацніть, щоб завантажити зразок файлу

5.1 Порівняйте два стовпці, якщо вони більші або менші (за формулою)

---

За допомогою простої формули можна швидко визначити, чи дата 1 перевищує дату 2 у кожному рядку.

 

прес вводити , щоб отримати перший порівняний результат, а потім перетягніть маркер автоматичного заповнення до комірки C6, щоб отримати всі результати.

Зауваження

1. В Excel дати зберігаються як числові ряди, насправді вони є числами. Тому ви застосовуєте формулу для безпосереднього порівняння дат.

2. Якщо ви хочете порівняти, якщо дата 1 передує даті 2 у кожному рядку, змініть символ > до < у формулі.

5.2 Порівняйте два стовпці, якщо вони більше або менше форматування (за умови умовного форматування)

---

Якщо ви хочете виділити клітинки у стовпці Дата 1, якщо вони перевищують Дату 2, ви можете використовувати Умовне форматування функція в Excel.

1. Виберіть дати у стовпці B (Date1), а потім натисніть Головна > Умовне форматування > Нове правило.

2 В Нове правило форматування діалог, виберіть За допомогою формули визначте, які клітинки потрібно форматувати в Виберіть тип правила розділ, потім введіть формулу = $ B2> $ C2 в текстове поле Форматувати значення, де ця формула відповідає дійсності.

Якщо ви хочете виділити комірки в стовпці B, які є меншими, ніж у стовпці C, використовуйте формулу = $ B2 <$ C2.

3. клацання сформований кнопка, щоб відкрити Формат ячеек діалоговому вікні, а потім виберіть потрібний тип формату.

4. клацання OK > OK. Потім були виділені клітинки у стовпці Date1, які перевищують клітинки у стовпці Date2.

---

Наприклад, є дві таблиці, тепер потрібно порівняти стовпець B і стовпець E, потім знайти відносні ціни зі стовпця C і повернути їх у стовпець F.

Клацніть, щоб завантажити зразок файлу

6.1 Витягніть точні дані відповідності (за допомогою формули або зручного інструменту)

---

Тут він представляє деякі корисні формули та інструмент для вирішення цієї роботи.

Метод формули

У комірці F2 (комірці, куди потрібно розмістити повернене значення), використовуйте одну з наведених нижче формул:

Or

 

прес вводити ключ, і перше значення знайдено. Потім перетягніть маркер автоматичного заповнення до комірки F6, усі значення були витягнуті.

Зауваження

1. Формули не підтримують регістр.

2. Число 2 у формулі означає, що ви знайдете відповідні значення у другому стовпці масиву таблиці.

3. Якщо формули не можуть знайти відносне значення, воно повертає значення помилки # N / A.

Зручний метод інструменту

Якщо вас плутають з формулами, ви можете спробувати зручний інструмент - Помічник формули of Kutools для Excel, який містить декілька формул для вирішення більшості завдань у програмі Excel. За допомогою нього потрібно лише вибрати діапазон, але не потрібно пам’ятати, як використовуються формули.

після безкоштовна установка Kutools для Excel, будь-ласка, виконайте наведені нижче дії:

1. Виділіть клітинку F2 (клітинку, в яку потрібно розмістити повернене значення), і натисніть Кутулс > Помічник формули > Пошук та довідкові матеріали > Шукайте значення у списку.

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

Чайові: Не забудьте змінити абсолютне посилання в Пошук_значення поле відносно, або ви можете знайти лише перше значення.

3. клацання Ok, було знайдено перше значення, а потім перетягніть маркер автоматичного заповнення до комірки F6.

6.2 Витягніть дані часткового збігу (за формулою)

---

Якщо між двома порівняними стовпцями є незначна різниця, як показано на знімку екрана, вищезазначені методи не можуть працювати.

Будь ласка, виберіть одну з наведених нижче формул для вирішення цієї роботи:

Or

 

прес вводити клавішу, а потім перетягніть маркер автоматичного заповнення до комірки F5, всі значення знайдено.

Зауваження

1. Формули не підтримують регістр.

2. Число 2 у формулі означає, що ви знайдете значення повернення у другому стовпці масиву таблиці.

3. Якщо формули не можуть знайти відносне значення, воно повертає значення помилки # N / A.

4. * у формулі - узагальнюючий знак, який використовується для позначення будь-якого символу або рядків.

---

Нижче набір даних - приклад порівняння та підрахунку збігів чи різниці.

Клацніть, щоб завантажити зразок файлу

7.1 Порівняйте два стовпці та підрахуйте збіги (за допомогою формули SUMPRODUCT)

---

Формула SUMPRODUCT дозволяє швидко підрахувати збіги у дві колонки.

 

прес вводити ключ, щоб отримати результат.

Зауваження

Формула підраховує клітини, не враховуючи регістр.

7.2 Порівняйте два стовпці та підрахуйте збіги або відмінності (за допомогою зручного інструменту)

---

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

після безкоштовна установка Kutools для Excel, будь-ласка, виконайте наведені нижче дії:

1. клацання Кутулс > вибрати > Виберіть однакові та різні клітини.

2 В Виберіть Одинакові та різницькі клітини , виберіть два діапазони стовпців у Знайти значення в і Відповідно до розділи окремо, потім виберіть Кожен ряд, і вибрати Ті самі значення or Різні цінності варіант, як вам потрібно.

3. клацання Ok. Вискакує діалогове вікно, щоб повідомити, скільки вибраних або різних клітинок вибрано.

Відповідні клітинки

Різні клітини

---

Припустимо, ось список даних у стовпці B, і ви хочете порахувати клітинки, які містять "Apple" або "Candy" у стовпці D, як показано нижче:

Клацніть, щоб завантажити зразок файлу

Щоб підрахувати, чи містить клітинка одне або кілька значень, для вирішення цієї проблеми можна скористатися формулою із символами підстановки.

 

прес Shift + Ctrl + Enter , щоб отримати першу перевірку, а потім перетягніть маркер автозаповнення до комірки F8.

Тепер, якщо відповідна клітинка містить одне або більше значень у стовпці D, результат відображає число більше 0, якщо воно не містить жодного значення в стовпці D, повертає 0.

Якщо ви хочете підрахувати загальну кількість комірок, що містять значення у стовпці D, скористайтеся формулою внизу комірки F8:

Зауваження

1. Також ви можете використовувати формулу для підрахунку, якщо комірка містить значення в іншому стовпці

Цю формулу потрібно лише натиснути вводити , а потім перетягніть маркер автоматичного заповнення.

2. У формулах * є символом підстановки, який вказує будь-який символ або рядок.

---

Якщо ви хочете видалити відмінності або збіги після порівняння двох стовпців, як показано нижче:

Клацніть, щоб завантажити зразок файлу

Ви можете використовувати формулу, щоб знайти різницю або спочатку збіги:

прес вводити клавішу та перетягніть маркер автоматичного заповнення до комірки D8.

Потім застосуйте фільтр функцію в перевершувати щоб відфільтрувати відмінності або значення відповідності.

Виберіть стовпець формули, а потім клацніть дані > фільтр.

Потім у стовпці C з'являється кнопка фільтра, клацніть на кнопці фільтра, щоб розгорнути спадне меню, виберіть Різниця or матч як вам потрібно. Потім клацніть OK закінчувати.

Зараз відфільтровано лише відмінності або збіги. Ви можете вибрати їх і натиснути видаляти щоб видалити їх.

Тепер натисніть дані > фільтр ще раз, щоб очистити фільтр.

Видаліть стовпець формули, якщо він вам більше не потрібен.

---

Є дві колонки, одна містить вихідні ціни, а інша - ціни продажу. Тепер ця частина вводить формулу для порівняння цих двох стовпців, а потім обчислює процентну зміну між двома стовпцями.

Клацніть, щоб завантажити зразок файлу

Ви можете використовувати формулу нижче, щоб визначити процентну зміну між двома цінами в одному рядку.

 

прес вводити клавішу, щоб отримати число, а потім перетягніть маркер автоматичного заповнення до комірки D7.

Потім відформатуйте результат формули у відсотках. Виберіть результати, натисніть Головна та перейдіть до Номер групу, яку потрібно натиснути Процентний стиль.

Результати формули були відформатовані у відсотках.

---

Тепер ви знаєте, як порівняти два стовпці, прочитавши наведені вище методи. Однак у деяких випадках вам може знадобитися порівняти два діапазони (дві серії з кількома стовпцями). Ви можете використовувати вищезазначені методи (формули або умовне форматування), щоб порівняти їх стовпчик за стовпцем, але тут представлений зручний інструмент - Kutools для Excel може швидко вирішити цю роботу в різних випадках за допомогою безкоштовних формул.

Клацніть, щоб завантажити зразок файлу

11.1 Порівняйте два діапазони за клітинками

---

Ось два діапазони, які потрібно порівняти за клітинками, ви можете використовувати Виберіть однакові та різні клітини утиліта Kutools для Excel для обробки.

після безкоштовна установка Kutools для Excel, будь-ласка, виконайте наведені нижче дії:

1. клацання Кутулс > вибрати > Виберіть однакові та різні клітини.

2. У вискакуванні Виберіть однакові та різні клітини діалогове вікно, виконайте вказані нижче дії

1) У Знайти значення в розділу, виберіть діапазон, який ви хочете дізнатись, збіги чи відмінності після порівняння двох діапазонів.

2) У Відповідно до розділ, виберіть інший діапазон, що використовується для порівняння діапазону.

3) В На основі розділ, вибрати Одиночна комірка.

4) Потім у знайти розділ, виберіть тип комірок, які потрібно виділити або виділити.

5) У Обробка результатів розділу, ви можете виділити комірки кольором фона заливки або кольором шрифту, якщо виділення не потрібно, не встановлюйте прапорці.

3. клацання Ok. Вискакує діалогове вікно і нагадує, скільки клітинок / рядків було вибрано, натисніть OK щоб закрити його. Тепер комірки, які відрізняються від тих, що знаходяться в іншому діапазоні, були виділені та виділені.

Виділення тих самих значень

Зауваження

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

11.2 Порівняйте два діапазони, якщо дані в одному порядку

---

Якщо ви хочете порівняти два діапазони за рядками, то Функція порівняння клітин Kutools для Excel може вам допомогти.

після безкоштовна установка Kutools для Excel, будь-ласка, виконайте наведені нижче дії:

Припустимо, діапазон F2: H7 є моделлю, тепер ви хочете знайти, чи дані в діапазоні B2: D7 знаходяться в правильному порядку відповідно до діапазону F2: H7.

1. клацання Кутулс > Порівняйте клітини.

2 В Порівняйте клітини діалогове вікно, встановлене, як показано нижче:

1) Виберіть два діапазони в Знайти значення в і Відповідно до коробки окремо.

2) Виберіть тип комірки, який потрібно виділити в знайти розділ.

3) Виберіть тип підсвічування в Обробка результатів розділ.

3. клацання Ok. Вискакує діалогове вікно і нагадує, скільки клітинок було вибрано, клацніть OK щоб закрити його. Тепер комірки, які відрізняються від тих, що знаходяться в іншому діапазоні, були виділені та виділені.

---

Вас також може зацікавити

Порівняйте дати, більші за інші дати в Excel Надає формули та зручний інструмент для порівняння двох дат та виявлення, чи є дати більшими за інші. Порівняйте два аркуші поруч У цій статті це допоможе швидко порівняти два аркуші, щоб знайти збіги та відмінності. Vlookup для порівняння двох списків на окремих робочих аркушах Тут ми введемо функцію VLOOKUP для порівняння двох списків на різних двох аркушах. Знайти, виділити, відфільтрувати, порахувати, видалити дублікати в Excel Це довгий та детальний підручник, який надає безліч різних методів пошуку, виділення, фільтрування, підрахунку та видалення дублікатів.

---

---