Як зіставити дані в Excel? Покроковий посібник (із прикладами)

Різні методи зіставлення даних у Excel

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

  • Спосіб No1 - Використання функції Vlookup
  • Спосіб No2 - Використання функції покажчика + відповідності
  • Спосіб №3 - Створіть власне значення пошуку

А тепер давайте детально обговоримо кожен із методів

Ви можете завантажити цей шаблон відповідності даних Excel тут - Шаблон відповідності даних Excel

# 1 - Збіг даних за допомогою функції VLOOKUP

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

Для прикладу зверніться до таблиці нижче.

У нас тут є дві таблиці даних, перша - це дані 1 , а друга - дані 2.

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

Дані 1 - Таблиця

Дані 2 - Таблиця

Я застосував функцію SUM для обох стовпців Сума продажу в таблиці. На самому початковому кроці ми отримали різницю у значеннях. Таблиця даних 1 відображає загальний обсяг продажів 2 166 214, а таблиця даних 2 - загальний обсяг продажів 2 102 214 .

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

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

Нам потрібні дані з другого стовпця, і діапазон пошуку - FALSE, тобто Точний збіг.

Результат наведено нижче:

У наступній комірці віднімають вихідне значення із значенням надходження.

Після вирахування ми отримуємо результат як нуль.

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

У клітинках G6 та G12 ми отримали відмінності.

У Даних 1 ми маємо 12104 для дати 04 березня 2019 року, а у Даних 2 ми маємо 15104 за ту саму дату, тому є різниця 3000.

Аналогічним чином, на дату 18 березня 2019 р. У Даних 1 ми маємо 19351, а в Даних 2 - 10351, отже, різниця становить 9000.

# 2 - Збіг даних за допомогою функції INDEX + MATCH

Для тих самих даних ми можемо використовувати функцію INDEX + MATCH. Ми можемо використовувати це як альтернативу функції VLOOKUP.

Функція INDEX, що використовується для отримання значення з вибраного стовпця на основі наданого номера рядка. Щоб вказати номер рядка, нам потрібно використовувати функцію MATCH на основі значення LOOKUP.

Відкрийте функцію INDEX у комірці F3.

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

Щоб отримати номер рядка, відкрийте функцію MATCH як наступний аргумент.

Виберіть значення пошуку як комірку D3.

Далі виберіть масив підстановки як стовпець "Дата продажу" в Дані 1.

У типі відповідності виберіть “0 - Точний збіг”.

Закрийте дві дужки та натисніть клавішу Enter, щоб отримати результат.

Це також дає той самий результат, що лише VLOOKUP. Оскільки ми використовували ті самі дані, отримали цифри, як вони є

# 3 - Створіть власне значення пошуку

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

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

Ми отримали багато варіантів. Нехай розглядають кожен випадок окремо.

У комірці I5 ми отримали дисперсію 8300. Давайте подивимось на основну таблицю.

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

У цьому випадку нашим пошуковим значенням є дата, тобто 20 березня 2019 року. У вищевказаній комірці для Північної зони на ту ж дату ми маємо значення 20404, тому VLOOKUP повернув це значення також для Східної зони.

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

Дані 1 - Таблиця

Дані 2 - Таблиця

Зараз ми створили унікальне значення для кожної зони із комбінованим значенням Зони, Дати продажу та Суми продажу.

Використовуючи ці унікальні значення, можна застосувати функцію VLOOKUP.

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

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