✅ Об’єднання таблиць у Excel за допомогою Power Query (простий покроковий посібник)

Зміст

Об’єднання таблиць за допомогою Power Query

Завдяки Power Query працювати з даними, розпорошеними по аркушах чи навіть книгах, стало простіше.

Одна з речей, де Power Query може заощадити багато часу, - це коли вам потрібно об’єднати таблиці з різними розмірами та стовпцями на основі відповідного стовпця.

Нижче наведено відео, де я показую, як саме об’єднати таблиці в Excel за допомогою Power Query.

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

Припустимо, у вас є таблиця, як показано нижче:

У цій таблиці є дані, які я хочу використати, але в ній відсутні два важливі стовпці - "Ідентифікатор продукту" та "Регіон", де працює представник з продажу.

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

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

І під об’єднанням я не маю на увазі просту копіювальну пасту.

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

Тепер ви можете покластися на VLOOKUP або INDEX/MATCH для цього.

Або якщо ви фахівець VBA, ви можете написати код для цього.

Але ці варіанти займають багато часу і є складними в порівнянні з Power Query.

У цьому уроці я покажу вам, як об’єднати ці три таблиці Excel в одну.

Щоб ця техніка працювала, вам потрібні сполучні стовпці. Наприклад, у таблицях 1 та 2 загальний стовпець - "Позиція", а у таблиці 1 та 3 - загальний стовпець "Торговий представник". Також зверніть увагу, що в цих сполучних стовпцях не повинно бути повторень.

Примітка: Power Query можна використовувати як надбудову в Excel 2010 та 2013, і це вбудована функція з Excel 2016 і далі. Виходячи з вашої версії, деякі зображення можуть виглядати інакше (знімки зображень, використані в цьому посібнику, є з Excel 2016).

Об’єднання таблиць за допомогою Power Query

Я назвав ці таблиці, як показано нижче:

Таблиця 1 - Sales_Data
Таблиця 2 - Pdt_Id
Таблиця 3 - Регіон

Перейменувати ці таблиці не обов’язково, але краще давати імена, які описують те, про що йдеться у таблиці.

За один раз можна об’єднати лише дві таблиці в Power Query.

Тож спочатку нам доведеться об’єднати Таблицю 1 та Таблицю 2, а потім на наступному кроці об’єднати в неї Таблицю 3.

Об’єднання Таблиці 1 та Таблиці 2

Щоб об’єднати таблиці, спочатку потрібно перетворити ці таблиці в з'єднання в Power Query. Після того, як у вас є з'єднання, ви можете легко їх об'єднати.

Ось кроки для збереження таблиці Excel як з'єднання в Power Query:

Виберіть будь -яку клітинку в таблиці Sales_Data.
Перейдіть на вкладку Дані.
У групі "Отримати та перетворити" натисніть "З таблиці/діапазону". Відкриється редактор запитів.
У редакторі запитів перейдіть на вкладку «Файл».
Натисніть на опцію «Закрити та завантажити».
У діалоговому вікні "Імпорт даних" виберіть "Тільки створити з'єднання".
Натисніть OK.

Вищезазначені кроки створили б зв’язок з іменем Sales_Data (або будь -яким іменем, яке ви дали таблиці Excel).

Повторіть наведені вище кроки для Таблиці 2 та Таблиці 3.

Отже, коли ви закінчите, у вас буде три зв’язки (з іменами Sales_Data, Pdt_Id та Region).

Тепер давайте подивимося, як об’єднати таблицю Sales_Data та Pdt_Id.

Натисніть на вкладку Дані.
У групі Отримати та перетворити дані натисніть Отримати дані.
У спадному меню натисніть «Об’єднати запити».
Натисніть «Об'єднати». Відкриється діалогове вікно Злиття.
У діалоговому вікні Об'єднання виберіть "Дані про продажі" з першого випадаючого меню.
Виберіть "Pdt_Id" у другому спадному меню.
У попередньому перегляді "Дані про продажі" натисніть стовпець "Елемент". Це дозволить вибрати весь стовпець.
У попередньому перегляді "Pdt_Id" натисніть стовпець "Елемент". Це дозволить вибрати весь стовпець.
У спадному меню "Приєднатися до виду" виберіть "Зліва зліва (все від першого, відповідність від другого)".
Натисніть OK.

Наведені вище кроки відкриють редактор запитів і покажуть вам дані з Sales_Data з одним додатковим стовпцем (з Pdt_Id).

Об’єднання таблиць Excel (таблиці 1 та 2)

Тепер процес об’єднання таблиць відбуватиметься у редакторі запитів із такими кроками:

У додатковому стовпці (Pdt_Id) клацніть на подвійну стрілку в заголовку.
У вікні параметрів, що відкриється, зніміть прапорець біля всіх назв стовпців і виберіть лише елемент. Це тому, що у наявній таблиці ми вже маємо стовпець з назвою продукту, і нам потрібен лише ідентифікатор продукту для кожного товару.
Зніміть прапорець "Використовувати оригінальну назву стовпця як префікс".
Натисніть ОК.

Це дасть вам результуючу таблицю, яка містить усі записи з таблиці Sales_Data, і додатковий стовпець, який також містить ідентифікатори продуктів (з таблиці Pdt_Id).

Тепер, якщо ви хочете поєднати лише дві таблиці, ви можете завантажити цей Excel, і ви закінчили.

Але у нас є три таблиці, які потрібно об’єднати, тож є ще над чим працювати.

Вам потрібно зберегти отриману таблицю як з'єднання (щоб ми могли використовувати її для об'єднання з таблицею 3).

Ось кроки, щоб зберегти цю об’єднану таблицю (з даними з таблиці Sales_Data та Pdt_Id) як з'єднання:

Перейдіть на вкладку Файл
Натисніть на опцію «Закрити та завантажити».
У діалоговому вікні "Імпорт даних" виберіть "Тільки створити з'єднання".
Натисніть OK.

Це збереже знову об'єднані дані як з'єднання. Ви можете перейменувати це з'єднання, якщо хочете.

Об’єднання таблиці 3 з результуючою таблицею

Процес злиття третьої таблиці з результуючою таблицею (яку ми отримали шляхом злиття Таблиці 1 та Таблиці 2) абсолютно однаковий.

Нижче описано, як об’єднати ці таблиці.

Натисніть на вкладку Дані.
У групі Отримати та перетворити дані натисніть «Отримати дані».
У спадному меню натисніть «Об’єднати запити».
Натисніть «Об’єднати». Відкриється діалогове вікно Злиття.
У діалоговому вікні "Об'єднати" виберіть "Злиття 1" у першому спадному меню.
У другому спадному меню виберіть "Регіон".
У попередньому перегляді "Злиття1" натисніть стовпець "Представник продажів". Це дозволить вибрати весь стовпець.
У попередньому перегляді регіону натисніть стовпець "Представник продажів". Це дозволить вибрати весь стовпець.
У спадному списку "Приєднатися до виду" виберіть Зліва зовні (все з першого, збігається з другим).
Натисніть OK.

Наведені вище кроки відкриють редактор запитів і покажуть вам дані з Merge1 з одним додатковим стовпцем (регіон).

Тепер процес об’єднання таблиць відбуватиметься у редакторі запитів із такими кроками:

У додатковому стовпці (Регіон) натисніть на подвійну стрілку в заголовку.
У вікні параметрів, що відкриється, зніміть прапорці з усіма назвами стовпців і виберіть лише Регіон.
Зніміть прапорець "Використовувати оригінальну назву стовпця як префікс".
Натисніть ОК.

Наведені вище кроки дадуть вам таблицю, у якій усі три таблиці об’єднані (таблиця Sales_Data з одним стовпцем для Pdt_Id та одним для регіону).

Ось кроки для завантаження цієї таблиці в Excel:

Перейдіть на вкладку Файл.
Натисніть «Закрити та завантажити».
У діалоговому вікні "Імпортувати дані" виберіть опції Таблиця та Нові робочі аркуші.
Натисніть OK.

Це дасть вам отриману об’єднану таблицю на новому робочому аркуші.

Одне з найкращих моментів Power Query полягає в тому, що ви можете легко врахувати будь -які зміни в базових даних (Таблиця 1, 2 і 3), просто оновивши їх.

Наприклад, припустимо, що Лауру переведуть до Азії, і ви отримаєте нові дані за наступний місяць. Тепер вам не доведеться повторювати вищевказані дії знову. Все, що вам потрібно зробити, це оновити таблицю, і вона все зробить за вас заново.

Протягом декількох секунд ви отримаєте нову об’єднану таблицю.

Вам також можуть сподобатися такі підручники Power Query: