Складання таблиць із різних файлів Excel за допомогою Power Query

Постановка проблеми

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

Припустимо, у нас є така папка, яка містить кілька файлів з даними з міст-філій:

Кількість файлів не має значення та може змінитися в майбутньому. Кожен файл має аркуш з назвою Salesде знаходиться таблиця даних:

Кількість рядків (порядків) у таблицях, звичайно, різна, але набір стовпців скрізь стандартний.

Завдання: зібрати дані з усіх файлів в одну книгу з подальшим автоматичним оновленням при додаванні або видаленні файлів міст або рядків у таблицях. За підсумковою зведеною таблицею потім можна буде будувати будь-які звіти, зведені таблиці, фільтрувати-сортувати дані і т. д. Головне вміти збирати.

Підбираємо зброю

Для рішення нам знадобиться остання версія Excel 2016 (потрібний функціонал вже вбудований в нього за замовчуванням) або попередні версії Excel 2010-2013 з встановленою безкоштовною надбудовою PowerQuery від Microsoft (завантажити тут). Power Query — це надзвичайно гнучкий і надпотужний інструмент для завантаження даних у Excel із зовнішнього світу, а потім видалення й обробки. Power Query підтримує майже всі існуючі джерела даних – від текстових файлів до SQL і навіть Facebook 🙂

Якщо у вас немає Excel 2013 або 2016, то ви можете не читати далі (жартую). У старих версіях Excel таке завдання можна виконати лише за допомогою програмування макросу у Visual Basic (що дуже складно для початківців) або за допомогою монотонного копіювання вручну (що займає багато часу та породжує помилки).

Крок 1. Імпортуйте один файл як зразок

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

• якщо у вас Excel 2016, то відкрийте вкладку дані , А потім Створити запит – З файлу – З книги (Дані — Новий запит — З файлу — З Excel)
• якщо у вас Excel 2010-2013 із встановленою надбудовою Power Query, відкрийте вкладку PowerQuery і виберіть на ньому З файлу – З книги (З файлу — З Excel)

Потім у вікні, що відкриється, заходимо в нашу папку зі звітами і вибираємо будь-який з міських файлів (не важливо який, адже всі вони типові). Через пару секунд має з'явитися вікно навігатора, де зліва потрібно вибрати потрібний нам лист (Продажі), а справа відобразиться його вміст:

Якщо натиснути на кнопку в нижньому правому куті цього вікна Завантажити (Завантажити), тоді таблиця буде негайно імпортована на аркуш у вихідному вигляді. Для одного файлу це добре, але нам потрібно завантажити багато таких файлів, тому ми підемо трохи інакше і натиснемо кнопку Корекція (Edit). Після цього редактор запитів Power Query повинен відобразитися в окремому вікні з нашими даними з книги:

Це дуже потужний інструмент, який дозволяє «довести» таблицю до потрібного нам виду. Навіть поверхневий опис усіх його функцій зайняв би близько сотні сторінок, але, якщо дуже коротко, за допомогою цього вікна ви можете:

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

Наприклад, додамо в нашу таблицю стовпець з текстовою назвою місяця, щоб потім було легше будувати звіти зведеної таблиці. Для цього клацніть правою кнопкою миші на заголовку стовпця датаі виберіть команду Копія стовпця (Дубльований стовпець), а потім клацніть правою кнопкою миші заголовок стовпця-дубліката, який з’явиться, і виберіть команди Перетворення – Місяць – Назва місяця:

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

Якщо в деяких стовпцях програма не зовсім правильно розпізнала тип даних, то ви можете їй допомогти, натиснувши на іконку форматування зліва від кожного стовпця:

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

Крім того, всі зроблені трансформації фіксуються в правій панелі, де їх завжди можна відкотити (хрест) або змінити їх параметри (шестерня):

Легкий і елегантний, чи не так?

Крок 2. Перетворимо наш запит у функцію

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

У редакторі запитів перейдіть на вкладку «Перегляд» і натисніть кнопку Розширений редактор (Перегляд — розширений редактор). Має відкритися вікно, де будуть записані всі наші попередні дії у вигляді коду мовою М. Зверніть увагу, що шлях до файлу, який ми імпортували для прикладу, жорстко закодовано в коді:

Тепер давайте внесемо пару коригувань:

Їх значення просте: перший рядок (шлях до файлу)=> перетворює нашу процедуру на функцію з аргументом шлях до файлу, а нижче змінюємо фіксований шлях на значення цієї змінної. 

все. Натисніть на обробка і повинен побачити це:

Не бійтеся, що дані зникли – насправді все добре, все має виглядати так 🙂 Ми успішно створили свою власну функцію, де запам’ятовується весь алгоритм імпорту та обробки даних без прив’язки до конкретного файлу . Залишається дати йому більш зрозумілу назву (наприклад getData) на панелі праворуч у полі Ім'я і можна жати Головна — закрити та завантажити (Додому — закрити та завантажити). Зверніть увагу, що шлях до файлу, який ми імпортували для прикладу, жорстко закодований у коді. Ви повернетеся в головне вікно Microsoft Excel, але праворуч повинна з'явитися панель зі створеним підключенням до нашої функції:

Крок 3. Збираємо всі файли

Все найскладніше позаду, залишилося приємне і легке. Перейдіть на вкладку Дані – Створити запит – З файлу – З папки (Дані — Новий запит — З файлу — З папки) або, якщо у вас Excel 2010-2013, аналогічно вкладці PowerQuery. У вікні, що з’явиться, вкажіть папку, де знаходяться всі наші вихідні файли міста, і натисніть OK. Наступний крок має відкрити вікно, у якому будуть перераховані всі файли Excel, знайдені в цій папці (і її вкладених папках), і деталі для кожного з них:

Натисніть Редагувати (Edit) і знову потрапляємо в знайоме вікно редактора запитів.

Тепер нам потрібно додати ще один стовпець до нашої таблиці зі створеною функцією, яка буде «витягувати» дані з кожного файлу. Для цього перейдіть на вкладку Додати стовпець – спеціальний стовпець (Додати стовпець — Додати спеціальний стовпець) і у вікні, що з’явиться, вводимо нашу функцію getData, вказавши для нього як аргумент повний шлях до кожного файлу:

Натиснувши на OK створений стовпець слід додати до нашої таблиці праворуч.

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

А тепер «вау момент» – клацніть на іконку з власними стрілками у верхньому правому куті доданої колонки з нашою функцією:

… зніміть прапорець Використовуйте оригінальну назву стовпця як префікс (Використовуйте оригінальну назву стовпця як префікс)і натисніть кнопку OK. І наша функція завантажить і обробить дані з кожного файлу, дотримуючись записаного алгоритму і збираючи все в загальну таблицю:

Для повної краси ви також можете видалити розширення .xlsx із першого стовпця з іменами файлів – стандартною заміною на «нічого» (клацніть правою кнопкою миші на заголовку стовпця – Замінити) і перейменуйте цей стовпець на Місто. А також виправте формат даних у стовпці з датою.

все! Натисніть на Головна – закрити та завантажити (Додому — Закрити та завантажити). Усі дані, зібрані запитом для всіх міст, будуть завантажені на поточний аркуш Excel у форматі «розумної таблиці»:

Створене з’єднання і нашу функцію збірки жодним чином не потрібно зберігати окремо – вони зберігаються разом з поточним файлом звичайним способом.

Надалі при будь-яких змінах в папці (додавання або видалення міст) або у файлах (зміна кількості рядків) достатньо буде клацнути правою кнопкою миші безпосередньо на таблиці або на запиті в правій панелі та вибрати команда Update & Save (Оновити) – Power Query знову «перебудує» всі дані за кілька секунд.

PS

Поправка. Після оновлень у січні 2017 року Power Query навчився самостійно збирати робочі книги Excel, тобто більше не потрібно створювати окрему функцію – це відбувається автоматично. Таким чином, другий крок з цієї статті більше не потрібен, і весь процес стає помітно простіше:

1. Вибирати Створити запит – З файлу – З папки – Вибрати папку – OK
2. Після появи списку файлів натисніть Редагувати
3. У вікні «Редактор запитів» розгорніть стовпець «Двійковий» за допомогою подвійної стрілки та виберіть ім’я аркуша, яке буде взято з кожного файлу.

І це все! пісня!

• Переробка перехресної таблиці в плоску, придатну для створення зведених таблиць
• Створення анімованої бульбашкової діаграми в Power View
• Макрос для збирання аркушів з різних файлів Excel в один