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

Порівняйте два стовпці, щоб знайти повторювані значення

• Виділіть дублікати у двох стовпцях за допомогою умовного форматування
• Виберіть і виділіть дублікати у двох стовпцях за допомогою потужного інструменту – Kutools
• Знайдіть і витягніть дублікати у двох стовпцях із формулами
• Виберіть дублікати у двох стовпцях із кодом VBA

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

• Порівняйте два стовпці в одному рядку з формулами
• Виберіть і виділіть збіги в одному рядку за допомогою корисного інструменту - Kutools
• Порівняйте два стовпці та виділіть збіги в одному рядку

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

Порівняйте два стовпці, щоб знайти повторювані значення

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

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

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

Крок 1: Виберіть дані в стовпці, з якого потрібно виділити дублікати

У цьому прикладі я виберу A2:A10, див. знімок екрана:

Крок 2. Застосуйте функцію умовного форматування

1. Натисніть Головна > Умовне форматування > Нове правило, див. скріншот:
   
2. У Нове правило форматування діалоговому вікні, виконайте такі дії:
   • 2.1 Виберіть За допомогою формули визначте, які клітинки потрібно форматувати від Виберіть тип правила вікно списку;
   • 2.2 Введіть наступну формулу в Форматувати значення, де ця формула відповідає дійсності текстове вікно;

     =COUNTIF($B$2:$B$10, A2)>0

   • примітки: У наведеній вище формулі, B2: B10 представляє список даних, з якими ви хочете порівняти, A2 це перша комірка стовпця, з якої ви хочете виділити дублікати. Ця формула перевіряє, чи знайдено значення в клітинці A2 десь у стовпці B. Змініть посилання на клітинки відповідно до ваших даних.
   • 2.3 Потім натисніть сформований кнопки.
   • 
3. У вискочив Формат ячеек у діалоговому вікні вкажіть один колір, яким потрібно виділити повторювані елементи. І натисніть OK.
   
4. Коли він повертається Нове правило форматування діалогове вікно натисніть кнопку OK кнопки.

Результат:

Тепер значення, які повторюються в обох стовпцях A і B, тепер виділено в стовпці A, як показано на наступному знімку екрана:

Виберіть і виділіть дублікати у двох стовпцях за допомогою потужного інструменту – Kutools

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

Натисніть Кутулс > Select > Виберіть однакові та різні клітини, В Виберіть однакові та різні клітини діалоговому вікні, виконайте такі дії:

1. Виберіть вихідні дані та порівняйте дані в Знайти значення в та За оцінками коробки окремо;
2. Select Кожен ряд під На основі розділ;
3. Вибирати Ті самі значення від знайти розділ;
4. Укажіть колір фону для виділення повторюваних значень під Обробка результатів розділ;
5. Нарешті клацніть OK кнопки.
   

Результат:

Тепер значення, які є дублікатами в обох стовпцях A і B, виділені та виділені в стовпці A, готові для копіювання та вставлення в будь-які потрібні клітинки. Перегляньте скріншот:

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

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

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

=IF(ISERROR(MATCH(A2,$B$2:$B$10,0)),"",A2)

примітки: У наведеній вище формулі, A2 є першою коміркою стовпця, де потрібно знайти дублікати; B2: B10 представляє список даних, з якими ви хочете порівняти.

Результат:

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

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

Цей розділ допоможе вам створити код VBA, який визначає та вибирає повторювані значення між двома стовпцями.

Крок 1. Відкрийте редактор модуля VBA та скопіюйте код

1. прес Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.
2. У вікні, що відкрилося, натисніть Insert > Модулі щоб створити новий порожній модуль.
3. Потім скопіюйте та вставте наведений нижче код у порожній модуль.
   Код VBA: знайдіть і виберіть повторювані значення між двома стовпцями

   Sub Compare()
   'Update by Extendoffice
   Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range
   xTitleId = "KutoolsforExcel"
   On Error Resume Next
   Set Range1 = Application.Selection
   Set Range1 = Application.InputBox("Range1 :", xTitleId, Range1.Address, Type:=8)
   Set Range2 = Application.InputBox("Range2:", xTitleId, Type:=8)
   Application.ScreenUpdating = False
   For Each Rng1 In Range1
       xValue = Rng1.Value
       For Each Rng2 In Range2
           If xValue = Rng2.Value Then
               If outRng Is Nothing Then
                   Set outRng = Rng1
               Else
                   Set outRng = Application.Union(outRng, Rng1)
               End If
           End If
       Next
   Next
   outRng.Select
   Application.ScreenUpdating = True
   End Sub
   

Крок 2. Виконайте цей код VBA

1. Після вставки цього коду, натисніть F5 ключ для запуску цього коду. У першому вікні підказки виберіть список даних, з якого потрібно вибрати дублікати. А потім натисніть OK.
   
2. У другому вікні підказки виберіть список даних, з яким потрібно порівняти, а потім натисніть OK, див. скріншот:
   

Результат:

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

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

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

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

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

• Використання оператора дорівнює (=): найпростіший спосіб порівняння двох клітинок
• Функція IF: щоб зробити ваше порівняння більш інформативним
• ТОЧНА функція: порівняння з урахуванням регістру

Використання оператора дорівнює (=):

Введіть або скопіюйте наступну формулу, натисніть Що натомість? Створіть віртуальну версію себе у а потім перетягніть маркер заповнення вниз, щоб отримати всі результати. Він поверне TRUE, якщо значення в тому самому рядку стовпців A і B ідентичні, і FALSE, якщо вони не є. Перегляньте скріншот:

=A2=B2

Функція IF:

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

Введіть або скопіюйте наведену нижче формулу, натисніть Що натомість? Створіть віртуальну версію себе у а потім перетягніть маркер заповнення вниз, щоб отримати всі результати. Він поверне відповідність, якщо значення однакові, і відсутність відповідності, якщо вони різні. Перегляньте скріншот:

=IF(A2=B2, "Match", "No Match")

ТОЧНА функція:

Якщо вам потрібно порівняння з урахуванням регістру, то ТОЧНА функція це шлях.

Введіть або скопіюйте наступну формулу, натисніть Що натомість? Створіть віртуальну версію себе у а потім перетягніть маркер заповнення вниз, щоб отримати всі результати. Він поверне відповідність, якщо значення точно збігаються, і відсутність відповідності, якщо вони відрізняються. Перегляньте скріншот:

=IF(EXACT(A2,B2), "Match", "No match")   

Виберіть і виділіть збіги в одному рядку за допомогою корисного інструменту - Kutools

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

Натисніть Кутулс > Порівняйте клітини, В Порівняйте клітини діалоговому вікні, виконайте такі дії:

1. Виберіть дані з двох стовпців у Знайти значення в та За оцінками коробки окремо;
2. Select Ті ж клітини під знайти розділ;
3. Укажіть колір тла для виділення збігів під Обробка результатів розділ;
4. Нарешті клацніть OK кнопки.
   

Результат:

Тепер збіги в одному рядку виділені та виділені в стовпці A, що робить їх доступними для копіювання та вставлення в будь-які потрібні клітинки. Перегляньте скріншот:

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

Порівняння двох стовпців і виділення збігів в одному рядку можна ефективно виконати за допомогою умовного форматування в Excel. Ось посібник із визначення та виділення збігів рядків:

Крок 1: виберіть діапазон даних

Виберіть діапазон даних, для якого потрібно виділити збіги рядків.

Крок 2. Застосуйте функцію умовного форматування

1. Натисніть Головна > Умовне форматування > Нове правило, в Нове правило форматування діалоговому вікні, виконайте такі дії:
   • 2.1 Виберіть За допомогою формули визначте, які клітинки потрібно форматувати від Виберіть тип правила вікно списку;
   • 2.2 Введіть наведену нижче формулу в Форматувати значення, де ця формула відповідає дійсності текстове вікно;

     =$B2=$A2

   • 2.3 Потім натисніть сформований кнопки.
   • 
2. У вискочив Формат ячеек у діалоговому вікні вкажіть один колір, яким потрібно виділити повторювані елементи. І натисніть OK.
   
3. Коли він повертається Нове правило форматування діалогове вікно натисніть кнопку OK кнопки.

Результат:

Тепер відповідні значення в одному рядку відразу підсвічуються, дивіться знімок екрана:

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

Коли ви маєте справу з двома наборами даних у Excel і вам потрібно знайти загальні елементи з одного списку в іншому, формули пошуку є вашим основним рішенням для отримання цих збігів.

У Excel, якщо у вас є список фруктів у стовпці A та показники їхніх продажів у стовпці B, тепер ви хочете зіставити їх із вибором фруктів у стовпці D, щоб знайти їхні відповідні продажі. Як ви можете повернути відносні значення зі стовпця B в Excel?

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

• Всі версії Excel:

  =VLOOKUP(D2, $A$2:$B$6, 2, FALSE)

• Excel 365 і Excel 2021:

  =XLOOKUP(D2, $A$2:$A$6, $B$2:$B$6)

Результат:

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

Статті по темі: