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

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

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

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

Якщо ви віддаєте перевагу читанню тексту перед переглядом відео, нижче наводяться письмові інструкції.

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

таблиць

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

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

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

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

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

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

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

Але ці варіанти трудомісткі та складні в порівнянні з Power Query.

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

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

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

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

  1. Таблиця 1 - Дані_продажу
  2. Таблиця 2 - Pdt_Id
  3. Таблиця 3 - Регіон

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

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

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

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

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

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

  1. Виберіть будь-яку комірку в таблиці Sales_Data.
  2. Клацніть на вкладку Дані.
  3. У групі «Отримати та перетворити» натисніть «З таблиці/діапазону». Це відкриє редактор запитів.
  4. У редакторі запитів натисніть вкладку «Файл».
  5. Клацніть на опцію «Закрити та завантажити до».
  6. У діалоговому вікні «Імпорт даних» виберіть «Тільки створити підключення».
  7. Клацніть OK.

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

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

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

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

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

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

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

  1. У додатковому стовпці (Pdt_Id) натисніть на подвійну стрілку в заголовку.
  2. У вікні параметрів, що відкриється, зніміть прапорці з усіх назв стовпців і виберіть лише пункт. Це пов’язано з тим, що ми вже маємо стовпець із назвою товару в існуючій таблиці, і нам потрібен лише ідентифікатор товару для кожного товару.
  3. Зніміть прапорець біля опції «Використовувати оригінальну назву стовпця як префікс».
  4. Клацніть Ok.

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

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

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

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

Ось кроки для збереження цієї об’єднаної таблиці (з даними з таблиць Sales_Data та Pdt_Id) як з’єднання:

  1. Клацніть на вкладку Файл
  2. Клацніть на опцію «Закрити та завантажити до».
  3. У діалоговому вікні «Імпорт даних» виберіть «Тільки створити підключення».
  4. Клацніть OK.

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

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

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

Ось кроки для об’єднання цих таблиць:

  1. Клацніть на вкладку Дані.
  2. У групі Отримати та перетворити дані натисніть кнопку «Отримати дані».
  3. У спадному меню натисніть «Об’єднати запити.
  4. Натисніть «Об’єднати». Після цього відкриється діалогове вікно Злиття.
  5. У діалоговому вікні «Об’єднання» з першого спадного меню виберіть «Об’єднати1».
  6. Виберіть "Регіон" у другому спадному меню.
  7. У попередньому перегляді ‘Merge1’ натисніть на стовпець ‘Rep. При цьому буде виділено весь стовпець.
  8. У попередньому перегляді регіону натисніть на стовпець «Торговий представник». При цьому буде виділено весь стовпець.
  9. У спадному меню «Приєднатися до виду» виберіть Зліва зовні (все з першого, відповідне з другого).
  10. Клацніть OK.

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

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

  1. У додатковому стовпці (Регіон) натисніть на подвійну стрілку в заголовку.
  2. У вікні параметрів, що відкриється, зніміть галочки з усіх назв стовпців і виберіть лише регіон.
  3. Зніміть прапорець біля опції «Використовувати оригінальну назву стовпця як префікс».
  4. Клацніть Ok.

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

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

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

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

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

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

За кілька секунд у вас з’явиться нова об’єднана таблиця.

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