Як порівняти два списки в Excel? (Топ 6 методів)
Нижче наведено 6 різних методів, які використовуються для порівняння двох списків стовпця в Excel для збігів та відмінностей.
- Метод 1: Порівняйте два списки за допомогою оператора рівності
- Спосіб 2: Зіставлення даних за допомогою методу різниці рядків
- Метод 3: Зрівняйте різницю між рядками, використовуючи умову IF
- Метод 4: Збіг даних, навіть якщо є різниця між рядами
- Спосіб 5: Виділіть усі відповідні дані за допомогою умовного форматування
- Метод 6: Техніка часткового узгодження
Тепер обговоримо кожен із методів детально на прикладі -
Ви можете завантажити цей Порівняйте два списки шаблону Excel тут - Порівняйте два списки Excel шаблон# 1 Порівняйте два списки за допомогою оператора рівності
- Крок 1: У наступний стовпець відразу після двох стовпців вставте новий стовпець під назвою “Статус”.
- Крок 2: Тепер помістіть формулу в клітинку C2 як = A2 = B2.
- Крок 3: Ця формула перевіряє, чи дорівнює значення комірки А2 клітинці B2. Якщо обидва значення комірки збігаються, то ми отримаємо TRUE як результат, інакше отримаємо результат як FALSE.
- Крок 4: Тепер перетягніть формулу в клітинку C9 для інших значень, які слід визначити,
Де б ми не мали однакових значень у загальних рядках, ми отримували результат як TRUE або FALSE.
№2 Дані збігу за допомогою методу різниці рядків
Можливо, ви не використовували техніку «Різниця між рядами» на своєму робочому місці, але сьогодні я покажу вам, як використовувати цю техніку для узгодження даних рядком за рядком.
- Крок 1: Щоб виділити невідповідні комірки рядком за рядком, спочатку виберіть усі дані.
- Крок 2: Тепер натисніть комбінаційну клавішу Excel « F5 », щоб відкрити інструмент «Перейти до спеціального».
- Крок 3: Натисніть клавішу F5, щоб відкрити це вікно. Тепер у вікні "Перейти" натисніть вкладку "Спеціальне".
- Крок 4: У наступному вікні "Перейти до спеціального" виберіть опцію "Різниця між рядками". Після вибору “Різниця між рядками” натисніть на “Ok”.
Отримуємо наступний результат.
Як ви можете бачити у наведеному вище вікні, він вибрав клітинки, де є різниця між рядками. Тепер заповніть трохи кольором, щоб виділити значення різниці рядків.
# 3 Збіг різниці рядків, використовуючи умову IF
Як можна не враховувати умову IF, коли ми хочемо збігати дані рядки за рядками. У першому прикладі ми отримали або TRUE, або FALSE як результат, але що, якщо нам потрібен результат різниці замість результатів за замовчуванням або “TRUE, або FALSE”. Припустімо, що вам потрібен результат «Збіг», якщо немає різниці між рядками, і результат має бути «Не збігається», якщо є різниця між рядками.
- Крок 1: Перше відкрите умова IF у клітинці C2.
- Крок 2: Застосуйте логічний тест як A2 = B2.
- Крок 3: Якщо цей логічний тест ІСТИНА, введіть критерії результату. У цьому сценарії критерієм результату є "Відповідність", і якщо рядок не відповідає, тоді нам потрібен результат як "Не відповідає".
- Крок 4: Застосуйте формулу, щоб отримати результат.
- Крок 5: Тепер перетягніть формулу в клітинку C9 для інших значень, які слід визначити,
№4 Дані збігу, навіть якщо є різниця між рядами
Збіг даних щодо методів різниці рядків може не працювати весь час, значення може бути і в інших клітинках, тому нам потрібно використовувати різні технології в цих сценаріях.
Подивіться на дані нижче.
На зображенні вище ми маємо два списки чисел. Нам потрібно порівняти список 2 зі списком 1. Тож давайте використаємо нашу улюблену функцію VLOOKUP.
Отже, якщо дані співпадають, тоді ми отримуємо число, інакше ми отримали значення помилки як # N / A.
Відображення значень помилок зовсім не виглядає добре. Замість того, щоб показувати помилку, давайте замінимо їх словом “Недоступно”. Для цього використовуйте функцію IFERROR в Excel.
# 5 Виділіть усі відповідні дані
Якщо ви не прихильник формул Excel, то не хвилюйтесь, ми все одно зможемо збігати дані і без формули. За допомогою простого умовного форматування в Excel ми можемо фактично виділити всі відповідні дані двох списків.
- Крок 1: Для цього спочатку виберіть дані.
- Крок 2: Тепер перейдіть до умовного форматування та виберіть «Виділити правила комірки >> Повторювані значення».
- Крок 3: Тепер ви побачите вікно форматування «Повторювані значення комірок».
- Крок 4: Ви можете фактично вибрати різні кольори форматування зі спадного списку в Excel. Виберіть перший колір форматування та натисніть кнопку ok.
- Крок 5: Це виділить усі відповідні дані з двох списків.
- Крок 6: Просто у випадку, якщо не потрібно виділяти всі відповідні дані, якщо ви хочете виділити невідповідні дані, тоді у вікні “Повторювані значення” виберіть опцію “Унікальний”.
Це виділить усі невідповідні значення.
# 6 Техніка часткового узгодження
Ми бачили проблему неповних чи однакових даних у двох списках. Наприклад, якщо дані списку 1 містять “ABC Pvt Ltd”, а в списку 2 ми маємо лише “ABC” . У цих випадках усі наші формули та інструменти за замовчуванням взагалі не розпізнаються. У цих випадках нам потрібно використовувати спеціальну зірочку (*), щоб відповідати частковим значенням.
У Списку 1 ми маємо назву компанії та дані про доходи. У Списку 2 у нас є назви компаній, але не точне значення, як у Списку 1. Це складна ситуація, з якою ми всі стикалися на своєму робочому місці.
У таких випадках ми все одно можемо зіставити дані за допомогою спеціальної зірочки (*).
Отримуємо наступний результат.
Тепер перетягніть формулу до комірки E9 для інших значень, які слід визначити,
Знак зірочки (*) був використаний для подання будь-якої кількості символів, тому він буде відповідати повному символу слова "ABC" як "ABC Pvt Ltd".
Що слід пам’ятати
- Використання вищезазначених методів порівняння двох списків у Excel залежить від структури даних.
- Збіг рядків за рядками не найкраще підходить, якщо дані не впорядковані.
- VLOOKUP - це часто використовувана формула для збігу значень.