Приєднайте дві або більше таблиць у Excel за допомогою Power Query
У цьому підручнику ми розглянемо, як можна об’єднати таблиці в Excel на основі одного або декількох типових стовпців за допомогою майстра Power Query та Merge Tables.
Поєднання даних з декількох таблиць - одне з найскладніших завдань у Excel. Якщо ви вирішите зробити це вручну, ви можете витратити години, лише щоб дізнатися, що ви зіпсували важливу інформацію. Якщо ви досвідчений професіонал Excel, тоді ви можете покластися на формули VLOOKUP та INDEX MATCH. Ви вважаєте, що макрос міг би швидко виконати цю роботу, якби тільки ви знали як. Хороша новина для всіх користувачів Excel - майстер Power Query або Merge Tables може заощадити ваш час. Вибір за вами.
Як об’єднати таблиці за допомогою Excel Power Query
Простіше кажучи, Power Query (також відомий як Отримати та перетворити в Excel 2016 та Excel 2019) - це інструмент для комбінування, очищення та перетворення даних з кількох джерел у потрібний формат, такий як таблиця, зведена таблиця або зведена діаграма.
Крім усього іншого, Power Query може об'єднати 2 таблиці в 1 або об'єднати дані з кілька таблиць шляхом зіставлення даних у стовпцях, на чому зосереджується цей посібник.
Щоб результати відповідали вашим очікуванням, майте на увазі наступні речі:
- Power Query - це вбудована функція в Excel 2016 та Excel 2019, але її також можна завантажити в Excel 2010 та Excel 2013 та використовувати як надбудову. У попередніх версіях деякі вікна можуть виглядати по-різному від зображень у цьому посібнику, зроблених у Excel 2016.
- Щоб таблиці правильно поєднувались, вони повинні мати принаймні один загальний стовпець (він також називається загальним ідентифікатором або стовпцем ключа або унікальним ідентифікатором). Крім того, загальні стовпці повинні містити лише унікальні значення, без повторень.
- Вихідні таблиці можуть бути розміщені на одному аркуші або на різних робочих аркушах.
- На відміну від формул, Power Query не перетягує дані з однієї таблиці в іншу. Він створює нову таблицю, яка поєднує дані вихідних таблиць.
- Отримана таблиця не оновлюється автоматично. Вам слід прямо сказати Excel зробити це. Будь ласка, подивіться, як оновити об’єднану таблицю.
Вихідні дані
Як приклад, об’єднаємо 3 таблиці на основі загальних стовпців Ідентифікатор замовлення та Продавець. Зверніть увагу, що наші таблиці мають різну кількість рядків, і хоча таблиця 1 має дублікати в стовпці Продавець, таблиця 3 містить лише унікальні записи.
Наше завдання - зіставити дані в таблиці 1 з відповідними записами з інших двох таблиць і об'єднати всі дані в нову таблицю, як це:
Перш ніж почати приєднуватися, я порадив би вам дати деякі описові назви для своїх таблиць, щоб вам було легше їх розпізнавати та керувати ними пізніше. Крім того, хоча ми говоримо "таблиці", вам насправді не потрібно створювати таблицю Excel. Ваші "таблиці" можуть бути звичайними діапазонами або іменованими діапазонами, як у цьому прикладі:
- Таблиця 1 має назву Замовлення
- Таблиця 2 має назву Продукти
- Таблиця 3 називається Комісіями
Створюйте з'єднання Power Query
Щоб не захаращувати вашу книгу копіями оригінальних таблиць, ми перетворимо їх у зв’язки, зробимо об’єднання в редакторі Power Query, а потім завантажимо лише отриману таблицю.
Щоб зберегти таблицю як з’єднання в Power Query, ось що ви робите:
- Виберіть свою першу таблицю (Замовлення) або будь-яку комірку в цій таблиці.
- Перейдіть на вкладку Дані> Отримати та перетворити групу та натисніть З таблиці/діапазону.
- У відкритому редакторі Power Query натисніть кнопку Закрити та завантажитистрілка спадного меню (а не саму кнопку!) і виберіть Закрити та завантажити… варіант.
- У діалоговому вікні Імпорт даних виберіть Тільки створити підключення і натисніть OK.
Це створить зв’язок з назвою вашої таблиці/діапазону та відобразить це з’єднання на панелі Запити та підключення, що з’явиться праворуч від вашої книги.
Після закінчення ви побачите всі підключення на панелі:
Об’єднайте два з’єднання в одну таблицю
З наявними зв’язками, давайте подивимося, як можна об’єднати дві таблиці в одну:
- На вкладці Дані в групі Отримати та перетворити дані натисніть кнопку Отримати дані, у спадному списку виберіть Об’єднати запити та натисніть Злиття:
- У діалоговому вікні Злиття виконайте такі дії:
- Виберіть свою першу таблицю (Замовлення) із першого спадного меню.
- Виберіть свою другу таблицю (Продукти) у другому спадному меню.
- В обох попередніх переглядах натисніть на відповідний стовпець (Ідентифікатор замовлення), щоб вибрати його. Вибраний стовпець буде виділений зеленим кольором.
- У розкривному списку Приєднатися до виду залиште параметр за замовчуванням: Зліва зовні (все з першого, відповідне з другого).
- Клацніть OK.
Після завершення вищевказаних кроків редактор Power Query покаже вашу першу таблицю (Замовлення) з одним додатковим стовпцем з іменем, як друга таблиця (Продукти), доданим до кінця. Цей додатковий стовпець ще не має значень, лише слово "Таблиця" у всіх комірках. Але не засмучуйтесь, ви зробили все правильно, і ми збираємось це виправити за мить!
Виберіть стовпці для додавання з другої таблиці
На даний момент у вас є таблиця, схожа на таблицю на скріншоті нижче. Щоб завершити процес об’єднання, виконайте такі дії в редакторі Power Query:
- У доданому стовпці (Товари) натисніть на двосторонню стрілку в заголовку.
- У вікні, що відкриється, зробіть наступне:
- Зберігайте Розгорнути вибраний перемикач.
- Скасуйте вибір усіх стовпців, а потім виберіть з другої таблиці лише ті стовпці, які потрібно скопіювати. У цьому прикладі ми вибираємо лише стовпець Product, оскільки наша перша таблиця вже має ідентифікатор продавця та замовлення.
- Зніміть прапорець Використовуйте оригінальну назву стовпця як префікс поле (якщо ви не хочете, щоб ім'я стовпця було префіксом до імені таблиці, з якої взятий цей стовпець).
- Клацніть OK.