Порівняйте два стовпці рядок за рядком

Нижче наведено набір даних (діапазон B2:C8), де мені потрібно перевірити, чи імена в стовпці B збігаються з іменами в стовпці C у тому самому рядку.

У цій частині наведено два приклади для пояснення того, як порівнювати два стовпці рядок за рядком

• Приклад 1. Порівняйте клітинки в одному рядку
• Приклад 2. Порівняйте клітинки в одному рядку з урахуванням регістру

Приклад 1. Порівняйте клітинки в одному рядку

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

=B2=C2

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

Або ви можете відобразити певні тексти для показу збігів або невідповідностей за допомогою функції IF, як це:

=IF(B2=C2,"Match","No match")

Результат може виглядати так:

Приклад 2. Порівняйте клітинки в одному рядку з урахуванням регістру

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

IF(EXACT(B2,C2), "Match", "Mismatch")

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

У наведеній вище формулі ви можете змінити тексти «Збіг» і «Невідповідність» на власний опис.

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

Іноді вам може знадобитися порівняти більше ніж два стовпці в одному рядку, наприклад набір даних (діапазон B2:D7), як показано на знімку екрана нижче. У цьому розділі наведено різні методи порівняння кількох стовпців.

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

• Приклад 1. Порівняйте кілька стовпців і знайдіть збіги в усіх клітинках одного рядка
• Приклад 2. Порівняйте кілька стовпців і знайдіть відповідність у будь-яких двох клітинках в одному рядку

Приклад 1. Порівняйте кілька стовпців і знайдіть збіги в усіх клітинках одного рядка

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

=IF(COUNTIF($B2:$D2, $B2)=3, "Full match", "Not")

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

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

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

=IF(COUNTIF($B2:$D2,$B2)>=2,"Match","No match")

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

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

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

Є два приклади для порівняння та виділення збігів і відмінностей

• Приклад 1. Порівняйте два стовпці та виділіть повні збіги в усіх клітинках одного рядка або будь-яких двох клітинках одного рядка
• Приклад 2: Порівняйте два стовпці та виділіть відмінності в одному рядку

Приклад 1. Порівняйте два стовпці та виділіть повні збіги в усіх клітинках одного рядка або будь-яких двох клітинках одного рядка

Для виділення збігів у всіх клітинках або будь-яких двох клітинках в одному рядку вам може допомогти функція умовного форматування.

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

2 В Нове правило форматування Діалог

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

   =COUNTIF($B2:$D2, $B2)=3

3. Натисніть сформований.

3 В Формат ячеек діалоговому вікні, а потім виберіть один колір заливки або інше форматування комірки, щоб виділяти рядки. Натисніть OK > OK щоб закрити діалоги.

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

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

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

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

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

Тепер відмінності рядків вибрано.

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

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

У цій частині набір даних (діапазон B2:C8) показаний нижче, і ви хочете знайти всі значення, які одночасно містяться в стовпцях B і C, або знайти значення лише в стовпці B.

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

• Приклад 1. Порівняйте два стовпці в клітинках і відобразіть результат порівняння в іншому стовпці
• Приклад 2. Порівняйте два стовпці в клітинках і виберіть або виділіть повторювані чи унікальні дані за допомогою зручного інструменту
• Приклад 3. Порівняйте два стовпці в клітинках і виділіть повторювані або унікальні дані
• Приклад 4: Порівняйте два стовпці в клітинках і перерахуйте точні копії в іншому стовпці

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

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

=IF(COUNTIF($C$2:$C$8, $B2)=0, "No in C", "Yes in C")

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

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

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

після безкоштовне встановлення Kutools для Excel, Натисніть Кутулс > Select > Виберіть однакові та різні клітини. Потім у діалоговому вікні «Вибір однакових і різних клітинок» виконайте наведені нижче дії.

1. У Знайти значення в та За оцінками розділи, виберіть дві колонки окремо те, що використовується для порівняння.
2. Вибирати Кожен ряд варіант.
3. Вибирати Ті самі значення or Різні цінності як вам потрібно.
4. Укажіть, чи потрібно забарвлювати вибрані значення, і натисніть OK.

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

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

Приклад 3. Порівняйте два стовпці в клітинках і виділіть повторювані або унікальні дані

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

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

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

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

Приклад 4: Порівняйте два стовпці в клітинках і перерахуйте точні копії в іншому стовпці

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

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

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

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

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

Sub ExtendOffice_FindMatches()
'UpdatebyKutools
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 щоб запустити код, з’являться два діалогові вікна, у яких ви зможете вибрати два стовпці окремо. Потім натисніть OK > OK.

Збіги були автоматично перераховані в правій колонці з двох колонок.

Порівняйте два списки та витягніть відповідні дані

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

• Приклад 1. Порівняйте два стовпці та витягніть точні відповідні дані
• Приклад 2. Порівняйте два стовпці та витягніть часткові відповідні дані

Приклад 1. Порівняйте два стовпці та витягніть точні відповідні дані

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

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

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

=VLOOKUP(E2,$B$2:$C$8,2,0)

Or

=INDEX($B$2:$C$8,MATCH(E2,$B$2:$B$8,0),2)

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

Приклад 2. Порівняйте два стовпці та витягніть часткові відповідні дані

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

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

=VLOOKUP("*"&E2&"*",$B$2:$C$8,2,0)

Or

=INDEX($B$2:$C$8,MATCH("*"&E2&"*",$B$2:$B$8,0),2)

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

Порівняйте два стовпці та знайдіть відсутні точки даних

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

• Приклад 1. Порівняйте два стовпці та знайдіть відсутні точки даних
• Приклад 2: знайдіть відсутні точки даних і перелічіть їх в іншому стовпці (за допомогою зручного інструменту)
• Приклад 3: Порівняйте два стовпці та перелічіть відсутні дані нижче

Приклад 1. Порівняйте два стовпці та знайдіть відсутні точки даних

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

=ISERROR(VLOOKUP(B2,$C$2:$C$10,1,0))

Or

=NOT(ISNUMBER(MATCH(B2,$C$2:$C$10,0)))

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

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

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

Після встановлення Kutools для Excel, Натисніть Кутулс > Select > Виберіть однакові та різні клітини. Потім у діалоговому вікні «Вибір однакових і різних клітинок» виконайте наведені нижче дії.

1. У Знайдіть значення виберіть довший стовпець, що містить повний список.
2. У За оцінками розділ, виберіть коротший стовпець, в якому відсутні деякі дані.
3. Вибирати Кожен ряд варіант.
4. Вибирати Різні цінності варіант Натисніть OK.

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

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

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

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

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

=INDEX($B$2:$B$10,MATCH(TRUE,ISNA(MATCH($B$2:$B$10,$C$2:C6,0)),0))

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

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

Порівняйте два стовпці з підстановкою

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

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

=SUM(COUNTIF(B2,"*" & $D$2:$D$3 & "*"))

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

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

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

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

• Приклад 1: Порівняйте два стовпці, якщо більше або менше
• Приклад 2: порівняйте два стовпці, якщо більше або менше, ніж формат

Приклад 1: Порівняйте два стовпці, якщо більше або менше

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

=IF(B2>C2,"Yes","No")

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

Приклад 2: порівняйте два стовпці, якщо більше або менше, ніж формат

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

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

2 В Нове правило форматування діалог, виберіть За допомогою формули визначте, які клітинки потрібно форматувати в Виберіть тип правила розділ, потім введіть формулу

 =$B2>$C2

в текстове поле Відформатуйте значення, у яких ця формула є істинною.

=$B2<$C2.

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

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

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

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

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

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B8,C2:C8,0))))

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

Щоб отримати додаткові методи підрахунку збігів і відмінностей, відвідайте цю сторінку: Підрахуйте всі збіги / дублікати між двома стовпцями в Excel

Порівняйте два діапазони

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

• Приклад 1. Порівняйте два діапазони по клітинках
• Приклад 2. Порівняйте два діапазони, якщо дані в однаковому порядку

Приклад 1. Порівняйте два діапазони по клітинках

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

Після безкоштовного встановлення Kutools для Excel, Натисніть Кутулс > Select > Виберіть однакові та різні клітини. Потім у спливаючому діалоговому вікні «Вибір однакових і різних клітинок» виконайте наведені нижче дії.

1. У Знайти значення в розділу, виберіть діапазон, який ви хочете дізнатись, збіги чи відмінності після порівняння двох діапазонів.
2. У За оцінками розділ, виберіть інший діапазон, що використовується для порівняння діапазону.
3. In На основі розділ, вибрати Одиночна комірка.
4. У знайти розділ, виберіть тип комірок, які потрібно виділити або виділити.
5. У Обробка результатів у розділі ви можете виділити комірки кольором фону заливки або кольором шрифту, якщо вам не потрібно виділяти, не встановлюйте прапорці. Натисніть OK.

Відкриється діалогове вікно з нагадуванням про те, скільки комірок/рядків вибрано, натисніть OK закрити його.

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

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

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

У цьому випадку, Порівняйте клітини of Kutools для Excel може допомогти вам.

Після безкоштовного встановлення Kutools для Excel, Натисніть Кутулс > Порівняйте клітини. Потім у діалоговому вікні «Порівняти клітинки» встановіть наступне:

1. Виберіть два діапазони в Знайти значення в та За оцінками коробки окремо.
2. Виберіть тип комірки, який потрібно виділити в знайти .
3. Виберіть вид мелірування в Обробка результатів розділ. Натисніть OK.

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

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