Об’єднання даних із кількох книг у Excel (за допомогою Power Query)

Power Query може бути дуже корисним, якщо ви хочете об’єднати кілька книг в одну книгу.

Наприклад, припустимо, що у вас є дані про продажі для різних регіонів (Схід, Захід, Північ та Південь). Ви можете об’єднати ці дані з різних книг в один робочий аркуш за допомогою Power Query.

Якщо у вас є ці книги в різних місцях/папках, рекомендується перемістити їх у одну папку (або створити копію та помістити цю копію книги в одну папку).

Отже, для початку, у мене є чотири книги у папці (як показано нижче).

Тепер у цьому підручнику я розглядаю три сценарії, де можна об’єднати дані з різних книг за допомогою Power Query:

• Кожна книга містить дані в таблиці Excel, і всі назви таблиць однакові.
• Кожна книга містить дані з однаковим ім'ям аркуша. Це може бути випадок, коли у всіх робочих книгах є аркуш із назвою «підсумок» або «дані», і ви хочете об’єднати все це.
• Кожна книга містить багато аркушів і таблиць, і ви хочете об’єднати певні таблиці/аркуші. Цей метод також може бути корисним, якщо ви хочете об’єднати таблицю/аркуші, які не мають послідовної назви.

Давайте подивимось, як об’єднати дані з цих робочих книг у кожному конкретному випадку.

Кожна книга містить дані в таблиці Excel з тією ж структурою

Нижченаведена техніка буде працювати, якщо ваші таблиці Excel були структуровані однаково (ті ж назви стовпців).

Кількість рядків у кожній таблиці може змінюватися.

Не хвилюйтесь, якщо деякі таблиці Excel мають додаткові стовпці. Ви можете вибрати одну з таблиць як шаблон (або як "ключ", як називає його Power Query), і Power Query використовуватиме його для об'єднання всіх інших таблиць Excel з ним.

Якщо в інших таблицях є додаткові стовпці, вони будуть проігноровані, а об’єднані лише ті, що зазначені у шаблоні/ключі. Наприклад, якщо обрана вами таблиця шаблонів/ключів має 5 стовпців, а одна з таблиць у іншій книзі містить 2 додаткові стовпці, ці додаткові стовпці будуть проігноровані.

Тепер у мене в папці є чотири робочі книги, які я хочу об’єднати.

Нижче наведено знімок таблиці, яку я маю в одній із робочих книг.

Ось кроки для об’єднання даних із цих книг у єдину книгу (як єдину таблицю).

1. Перейдіть на вкладку Дані.
2. У групі Отримати та перетворити клацніть на спадному меню Новий запит.
3. Наведіть курсор на "З файлу" та натисніть "З папки".
4. У діалоговому вікні «Папка» введіть шлях до папки з файлами або натисніть «Огляд» і знайдіть папку.
5. Натисніть OK.
6. У діалоговому вікні, що відкриється, натисніть кнопку комбінування.
7. Натисніть «Об’єднати та завантажити».
8. У діалоговому вікні «Об’єднати файли» виберіть Таблиця на лівій панелі. Зауважте, що Power Query показує таблицю з першого файлу. Цей файл буде виконувати роль шаблону (або ключа) для об’єднання інших файлів. Power Query тепер шукатиме "Таблицю 1" в інших книгах і поєднуватиме її з цією.
9. Натисніть OK.

Остаточний результат (об’єднані дані) буде завантажено у ваш активний робочий аркуш.

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

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

Кілька речей, які потрібно знати:

• Якщо ви вибрали таблицю Excel як шаблон (на кроці 7), Power Query буде використовувати назви стовпців у цій таблиці для об’єднання даних з інших таблиць. Якщо інші таблиці мають додаткові стовпці, вони будуть проігноровані. Якщо інші таблиці не мають стовпця, який є у вашій таблиці шаблонів, Power Query просто додасть для нього значення «null».
• Стовпці не повинні бути в такому ж порядку, як Power Query використовує заголовки стовпців для зіставлення стовпців.
• Оскільки ви вибрали Таблицю1 як ключ, Power Query шукатиме Таблицю1 у всіх робочих книгах і об’єднає все це. Якщо він не знайде таблицю Excel з такою ж назвою (Таблиця 1 у цьому прикладі), Power Query видасть вам помилку.

Додавання нових файлів до папки

Тепер давайте приділимо хвилину і зрозуміємо, що ми зробили з наведеними вище кроками (які зайняли у нас всього кілька секунд).

Ми об’єднали дані з чотирьох різних робочих книг в одній таблиці за кілька секунд, навіть не відкриваючи жодної з робочих книг.

Але це ще не все.

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

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

Наприклад, у наведеному вище прикладі, якщо я додаю нову книгу - "Середній Захід.xlsx" у папку та оновити запит, він негайно видасть мені новий об’єднаний набір даних.

Ось як оновити запит:

• Клацніть правою кнопкою миші таблицю Excel, завантажену на аркуші, і натисніть «Оновити».
• Клацніть правою кнопкою миші на запиті на панелі «Запит робочої книги» та натисніть «Оновити»
• Перейдіть на вкладку Дані та натисніть Оновити.

Кожна книга містить дані з однаковим ім'ям аркуша

Якщо у вас немає даних у таблиці Excel, але всі назви аркушів (з яких потрібно об’єднати дані) однакові, можна скористатися методом, наведеним у цьому розділі.

Є кілька речей, з якими потрібно бути обережними, коли це лише табличні дані, а не таблиця Excel.

• Назви аркушів повинні бути однаковими. Це допоможе Power Query переглянути ваші робочі книги та об’єднати дані з аркушів, які мають однакову назву в кожній книзі.
• Power Query чутливий до регістру. Це означає, що аркуш з назвою «дані» та «дані» вважаються різними. Подібним чином, стовпці із заголовком "Магазин" та один із "магазином" вважаються різними.
• Хоча важливо мати однакові заголовки стовпців, не важливо мати однаковий порядок. Якщо стовпець 2 у "East.xlsx" - це стовпець 4 у "West.xlsx", Power Query відповідатиме йому правильно, зіставляючи заголовки.

Тепер давайте подивимося, як швидко об’єднати дані з різних робочих книг, де назва аркуша однакова.

У цьому прикладі у мене є папка з чотирма файлами.

У кожній книзі я маю робочий аркуш із назвою «Дані», який містить дані у такому форматі (зверніть увагу, що це не таблиця Excel).

Нижче наведено кроки для об’єднання даних з кількох книг в один аркуш:

1. Перейдіть на вкладку Дані.
2. У групі Отримати та перетворити клацніть на спадному меню Новий запит.
3. Наведіть курсор на "З файлу" та натисніть "З папки".
4. У діалоговому вікні «Папка» введіть шлях до папки з файлами або натисніть «Огляд» і знайдіть папку.
5. Натисніть OK.
6. У діалоговому вікні, що відкриється, натисніть кнопку комбінування.
7. Натисніть «Об’єднати та завантажити».
8. У діалоговому вікні «Об’єднати файли» виберіть «Дані» на лівій панелі. Зауважте, що Power Query показує ім’я аркуша з першого файлу. Цей файл буде виконувати роль ключа/шаблону для об’єднання інших файлів. Power Query перегляне кожну книгу, знайде аркуш з назвою «Дані» та об’єднає все це.
9. Натисніть OK. Тепер Power Query пройде кожну книгу, знайде в ній аркуш з назвою «Дані», а потім об’єднає всі ці набори даних.

Остаточний результат (об’єднані дані) буде завантажено у ваш активний робочий аркуш.

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

Кожна книга містить дані з різними іменами таблиць або іменами аркушів

Іноді ви можете не отримати структуровані та послідовні дані (наприклад, таблиці з однаковою назвою або аркуш з такою самою назвою).

Наприклад, припустимо, що ви отримаєте дані від когось, хто створив ці набори даних, але назвав робочі листи як Східні дані, Західні дані, Північні дані та Південні дані.

Або, можливо, людина створила таблиці Excel, але з різними іменами.

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

1. Перейдіть на вкладку Дані.
2. У групі Отримати та перетворити клацніть на спадному меню Новий запит.
3. Наведіть курсор на "З файлу" та натисніть "З папки".
4. У діалоговому вікні «Папка» введіть шлях до папки з файлами або натисніть «Огляд» і знайдіть папку.
5. Натисніть OK.
6. У діалоговому вікні, що відкриється, натисніть кнопку Редагувати. Відкриється редактор Power Query, де ви побачите деталі всіх файлів у папці.
7. Утримуючи клавішу Control, виберіть стовпці "Вміст" та "Ім'я", клацніть правою кнопкою миші та виберіть "Видалити інші стовпці". Це видалить усі інші стовпці, крім вибраних.
8. На стрічці Редактора запитів натисніть «Додати стовпець», а потім - «Спеціальний стовпець».
9. У діалоговому вікні "Додати користувацьку колонку" назвіть новий стовпець як "Імпорт даних" і скористайтеся такою формулою = Excel.Робоча книга ([ЗМІСТ]). Зверніть увагу, що ця формула чутлива до регістру, і вам потрібно ввести її точно так, як я показав тут.
10. Тепер ви побачите новий стовпець, у якому написана таблиця. Тепер дозвольте мені пояснити, що тут сталося. Ви надали Power Query назви робочих книг, а Power Query вилучив такі об’єкти, як робочі аркуші, таблиці та діапазони імен з кожної книги (яка зараз знаходиться у клітинці Таблиця). Ви можете натиснути на пробіл біля текстової таблиці, і ви побачите інформацію внизу. У цьому випадку, оскільки у нас є лише одна таблиця та один аркуш у кожній книзі, ви можете побачити лише два рядки.
11. Натисніть на значок подвійної стрілки у верхній частині стовпця "Імпорт даних".
12. У вікні даних стовпця зніміть прапорець "Використовувати оригінальний стовпець як префікс", а потім натисніть OK.
13. Тепер ви побачите розгорнуту таблицю, де ви побачите один рядок для кожного об’єкта в таблиці. У цьому випадку для кожної книги об’єкт аркуша та об’єкт таблиці перераховуються окремо.
14. У стовпці Вид відфільтруйте список, щоб відображати лише таблицю.
15. Утримуючи клавішу керування, виберіть стовпець Ім'я та дані. Тепер клацніть правою кнопкою миші та видаліть усі інші стовпці.
16. У стовпці Дані клацніть піктограму подвійної стрілки у верхньому правому куті заголовка даних.
17. У вікні даних стовпця, що відкриється, натисніть кнопку OK. Це об’єднає дані у всіх таблицях і покаже їх у Power Query.
18. Тепер ви можете здійснити будь -яке необхідне перетворення, а потім перейдіть на вкладку «Домашня сторінка» та натисніть «Закрити та завантажити».

Тепер дозвольте мені спробувати швидко пояснити, що ми тут зробили. Оскільки не було узгодженості в іменах аркушів або імен таблиць, ми використовували формулу = Excel.Workbook для отримання всіх об’єктів книг у Power Query. Ці об’єкти можуть включати аркуші, таблиці та іменовані діапазони. Отримавши всі об’єкти з усіх файлів, ми відфільтрували їх, щоб розглядати лише таблиці Excel. Потім ми розширили дані в таблицях і об’єднали все це.

У цьому прикладі ми відфільтрували дані лише для використання таблиць Excel (на кроці 13). Якщо ви хочете об’єднати аркуші, а не таблиці, ви можете фільтрувати аркуші.

Примітка - ця техніка дасть вам комбіновані дані, навіть якщо в назвах стовпців є невідповідність. Наприклад, якщо в East.xlsx у вас є стовпець, написаний неправильно, ви отримаєте 5 стовпців. Power Query заповнить дані стовпцями, якщо знайде їх, а якщо не зможе знайти стовпець, він повідомить значення як "null".

Аналогічно, якщо у вас є якісь додаткові стовпці на будь -якому з аркушів таблиць, вони будуть включені до кінцевого результату.

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