Створюйте таблиці з різними заголовками з кількох книг

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

У нас кілька файлів (в нашому прикладі – 4 штуки, в загальному випадку – скільки завгодно) в одній папці Звіти:

Всередині ці файли виглядають так:

При цьому:

• Потрібний нам паспорт викликається завжди фотографії, але може бути будь-де в робочій книзі.
  
• За аркушем фотографії Кожна книга може мати інші аркуші.
• Таблиці з даними мають різну кількість рядків і можуть починатися з іншого рядка на аркуші.
• Назви одних і тих же стовпців у різних таблицях можуть відрізнятися (наприклад, Кількість = Кількість = Кількість).
• Стовпці в таблицях можуть бути розташовані в різному порядку.

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

Крок 1. Підготовка довідника імен стовпців

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

Ми перетворюємо цей список на динамічну «розумну» таблицю за допомогою кнопки Форматувати як таблицю на вкладці Головна (Домашня сторінка — форматувати як таблицю) або комбінацію клавіш Ctrl+T і завантажте його в Power Query за допомогою команди Дані – з таблиці/діапазону (Дані — з таблиці/діапазону). В останніх версіях Excel його було перейменовано на З листям (З аркуша).

У вікні редактора запитів Power Query ми традиційно видаляємо крок Змінений тип і додати новий крок замість нього, натиснувши на кнопку f_xу рядку формул (якщо його не видно, його можна ввімкнути на вкладці Розгляд) і введіть туди формулу у вбудованій мові Power Query M:

=Table.ToRows(Джерело)

Ця команда перетворить завантажений на попередньому кроці Source довідкову таблицю в список, що складається з вкладених списків (List), кожен з яких, у свою чергу, є парою значень Було-стало з одного рядка:

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

Після завершення перетворення виберіть команди Головна — Закрийте та завантажте — Закрийте та завантажте в… і вид імпорту Просто створіть зв'язок (Додому — Закрити&Завантажити — Закрити&Завантажити до… — Лише створити з’єднання) і повернутися до Excel.

Крок 2. Завантажуємо все з усіх файлів як є

Тепер давайте завантажимо вміст усіх наших файлів із папки – поки що як є. Вибір команд Дані – Отримати дані – З файлу – З папки (Дані — Отримати дані — З файлу — З папки) а потім папку, де знаходяться наші вихідні книги.

У вікні попереднього перегляду натисніть Конвертувати (Трансформація) or Редагувати (Edit):

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

Power Query на прикладі першого файлу (Восток.xlsx) запитає нас про назву аркуша, який ми хочемо взяти з кожної робочої книги – виберіть фотографії і натисніть OK:

Після цього (фактично) відбудеться кілька неочевидних для користувача подій, наслідки яких добре видно на лівій панелі:

1. Power Query візьме перший файл з папки (він у нас буде Восток.xlsx — побачити Приклад файлу) як приклад і імпортує його вміст, створивши запит Перетворити файл зразка. Цей запит матиме кілька простих кроків, наприклад Source (доступ до файлу) навігація (вибір аркушів) і, можливо, підвищення заголовків. Цей запит може завантажити дані лише з одного конкретного файлу Восток.xlsx.
   
2. На основі цього запиту буде створено пов’язану з ним функцію Перетворити файл (позначається характерним значком f_x), де вихідний файл буде вже не константою, а змінним значенням – параметром. Таким чином, ця функція може витягувати дані з будь-якої книги, які ми вводимо в неї як аргумент.
   
3. Функцію буде застосовано по черзі до кожного файлу (Binary) зі стовпця зміст – за це відповідає крок Виклик спеціальної функції у нашому запиті, який додає стовпець до списку файлів Перетворити файл з результатами імпорту з кожної книги:

   

4. Зайві колонки видаляються.
5. Вміст вкладених таблиць розгортається (крок Розширена колонка таблиці) – і ми бачимо остаточні результати збору даних з усіх книг:

   
   

Крок 3. Шліфування

На попередньому скріншоті добре видно, що пряма збірка «як є» вийшла неякісною:

• Стовпчики виворітні.
• Багато зайвих рядків (порожніх і не тільки).
• Заголовки таблиць не сприймаються як заголовки та змішуються з даними.
  

Ви можете вирішити всі ці проблеми дуже легко – просто налаштуйте запит «Перетворити файл зразка». Усі налаштування, які ми вносимо до нього, автоматично потраплять у пов’язану функцію «Перетворити файл», що означає, що вони використовуватимуться пізніше під час імпорту даних із кожного файлу.

Відкривши заявку Перетворити файл зразка, додайте кроки для фільтрації непотрібних рядків (наприклад, за стовпцями Column2) і піднімаючи заголовки кнопкою Використовуйте перший рядок як заголовки (Використовуйте перший рядок як заголовки). Стіл буде виглядати набагато краще.

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

= Table.RenameColumns(#”Підвищені заголовки”, Headers, MissingField.Ignore)

Ця функція бере таблицю з попереднього кроку Підняті заголовки і перейменовує всі стовпці в ньому відповідно до вкладеного списку пошуку Headlines. Третій аргумент MissingField.Ignore потрібен для того, щоб по тих рубриках, які є в довіднику, але відсутні в таблиці, не виникала помилка.

Власне, це все.

Повертаючись до запиту Звіти ми побачимо зовсім іншу картину – набагато приємнішу за попередню:

• Що таке Power Query, Power Pivot, Power BI і навіщо вони потрібні користувачеві Excel
  
• Збір даних з усіх файлів у певній папці
• Збір даних з усіх аркушів книги в одну таблицю