Постановка задачі
Давайте розберемо гарне рішення для однієї з досить стандартних ситуацій, з якою рано чи пізно стикається більшість користувачів Excel: потрібно швидко і автоматично зібрати дані з великої кількості файлів в одну підсумкову таблицю.
Припустимо, що у нас є ось така папка, в якій міститься кілька файлів з даними з філій-міст:
Кількість файлів ролі не грає і може змінюватися в майбутньому. В кожному файлі є лист з ім'ям Продажі. де розташована таблиця з даними:
Кількість рядків (замовлень) в таблицях, само-собою, різний, але набір стовпців всюди стандартний.
Завдання: зібрати дані з усіх файлів в одну книгу з подальшим автоматичним оновленням при додаванні-видаленні файлів-міст або рядків в таблицях. За підсумкової консолідованої таблиці потім можна буде будувати будь-які звіти, зведені таблиці, фільтрувати-сортувати дані і т.д. Головне - зуміти зібрати.
підбираємо зброю
Крок 1. Імпортуємо один файл як зразок
Для початку давайте імпортуємо дані з однієї книги в якості прикладу, щоб Excel "підхопив ідею". Для цього створіть нову порожню книгу і.
Це дуже потужний інструмент, що дозволяє "допив" таблицю під потрібний нам вигляд. Навіть поверхневий опис всіх його функцій зайняло б під сотню сторінок, але, якщо зовсім коротко, то за допомогою цього вікна можна:
- фільтрувати непотрібні дані, порожні рядки, рядки з помилками
- сортувати дані по одному або кількох стовпців
- позбавлятися від повторів
- ділити злиплий текст по стовпцях (по розділювачам, кількості символів і т.д.)
- приводити текст в порядок (видаляти зайві прогалини, виправляти регістр і т.д.)
- всіляко перетворювати типи даних (перетворювати числа як текст в нормальні числа і навпаки)
- транспонувати (повертати) таблиці і розгортати двовимірні крос-таблиці в плоскі
- додавати до таблиці додаткові стовпці і використовувати в них формули і функції на вбудованому в Power Query мовою М.
- .
Для прикладу, давайте додамо до нашої таблиці стовпець з текстовим назвою місяця, щоб потім простіше було будувати звіти зведених таблиць. Для цього клацніть правою кнопкою миші по заголовку стовпця Дата і виберіть команду Дублювати стовпець (Duplicate Column). а потім клацніть правою кнопкою миші по заголовку з'явився стовпчика-дубліката та виберіть команди Перетворення - Місяць - Назва місяця.
Повинен утворитися новий стовпець з текстовими назвами місяці для кожного рядка. Двічі клацнувши по заголовку стовпця, його можна перейменувати з Копія Дата в більш зручне Місяць. наприклад.
Якщо в якихось шпальтах програма не зовсім коректно розпізнала тип даних, то їй можна допомогти, клацнувши по значку формату в лівій частині кожного стовпця:
Виключити рядки з помилками або порожні рядки, а також непотрібних менеджерів або замовників можна за допомогою простого фільтра:
Причому всі виконані перетворення фіксуються в правій панелі, де їх завжди можна відкотити (хрестик) або змінити їх параметри (шестерня):
Легко і витончено, чи не так?
Крок 2. Перетворимо наш запит в функцію
Щоб згодом повторити все зроблені перетворення даних для кожної імпортованої книги, потрібно перетворити наш створений запит в функцію, яка потім буде застосовуватися, по черзі, до всіх наших файлів. Зробити це, насправді, дуже просто.
Тепер акуратно вносимо пару правок:
Сенс їх простий: перший рядок (filepath) => перетворює нашу процедуру в функцію з аргументом filepath. а нижче ми міняємо фіксований шлях на значення цієї змінної.
Усе. Тиснемо на Готово і повинні побачити ось це:
Не лякайтеся, що пропали дані - насправді все ОК, все так і має виглядати :) Ми успішно створили нашу призначену для користувача функцію, де запам'ятався весь алгоритм імпорту та обробки даних без прив'язки до конкретного файлу. Залишилося дати їй більш зрозуміле ім'я (наприклад getData) на панелі праворуч у поле Ім'я і можна тиснути Головна - Закрити і завантажити (Home - Close and Load). Зверніть увагу, що в коді жорстко прописаний шлях до файлу, який ми імпортували для прикладу. Ви повернетеся в основне вікно Microsoft Excel, але справа повинна з'явитися панель з створеним підключенням до нашої функції:
Крок 3. Збираємо всі файли
Тепер потрібно додати до нашої таблиці ще один стовпець з нашої створеної функцією, яка "витягне" дані з кожного файлу. Для цього йдемо на вкладку Додати стовпець - Призначений для користувача стовпець (Add Column - Add Custom Column) і у вікні вводимо нашу функцію getData. вказавши для її як аргумент повний шлях до кожного файлу:
Після натискання на ОК створений стовпець повинен додатися до нашої таблиці справа.
Тепер видалимо всі непотрібні стовпці (як в Excel, за допомогою правої кнопки миші - Видалити), залишивши тільки доданий стовпець і стовпець з ім'ям файлу, тому що це ім'я (а точніше - місто) буде корисно мати в підсумкових даних для кожного рядка.
А тепер "вау-який момент" - щелкнем мишею по значку зі своенним стрілками в правому верхньому куті доданого стовпчика з нашої функцією:
знімаємо прапорець Використовувати вихідне ім'я стовпця як префікс (Use original column name as prefix) і тиснемо ОК. І наша функція довантажити і обробить дані з кожного файлу, слідуючи записаному алгоритму і зібравши всі в загальну таблицю:
Для повної краси можна ще прибрати розширення .xlsx з першого стовпчика з іменами файлів - стандартної заміною на "нічого" (правою кнопкою миші по заголовку стовпця - Замінити) і перейменувати цей стовпець в Місто. А також підправити формат даних в стовпці з датою.
Усе! Тиснемо на Головній - Закрити і завантажити (Home - Close Load). Всі зібрані запитом дані по всіх містах будуть вивантажені на поточний лист Excel в форматі "розумної таблиці":
Створене підключення і нашу функцію збірки не потрібно ніяк окремо зберігати - вони зберігаються разом з поточним файлом звичайним чином.
В майбутньому, при будь-яких змінах в папці (додаванні-видаленні міст) або в файлах (зміна кількості рядків) досить буде натиснути правою кнопкою миші прямо по таблиці або за запитом в правій панелі і вибрати команду Оновити (Refresh) - Power Query "пересоберет" всі дані заново за кілька секунд.
Посилання по темі
Enhanced "Combine Binaries" experience when importing from any folder
Before this release, users could combine Text or CSV files only. The combine would not work for any other supported file formats (such as Excel Workbooks, JSON files, etc.), and it would not account for transformations required on each file before combining them into a single table (such as removing the first row with header values).
Тепер ніби як можна радіти і плескати в долоні, тому що тепер не потрібно перекручуватися зі зміною коду вручну.
Однак (на моїй роботі) при вивантаженні файлів з 1С в форматі .xlsx, Power Query все одно не може їх проковтнути, поки просто не перезберегти файл в самому Excel (відкрити файл, зберегти, закрити).
Твою дивізію! Тільки що поставив оновлення і спробував: всі файли завантажилися на раз безпосередньо без функції - в два клацання. Офігєть. Вони роблять апдейти швидше, ніж я пишу статті
Спасибі, Василю! You made my day!