зміст
Постановка проблеми
В якості вихідних даних маємо файл Excel, де на одному з аркушів міститься кілька таблиць з даними про продажі наступного вигляду:
Зауважте, що:
- Таблиці різних розмірів і з різними наборами продуктів і регіонів у рядках і стовпцях без будь-якого сортування.
- Між таблицями можна вставляти порожні рядки.
- Кількість столів може бути будь-якою.
Два важливих припущення. Передбачається, що:
- Над кожною таблицею в першому стовпчику вказано ім'я керівника, продажі якого ілюструє таблиця (Іванов, Петров, Сидоров і т.д.)
- Назви товарів і регіонів у всіх таблицях пишуться однаково – з точністю до регістру.
Кінцева мета — зібрати дані з усіх таблиць в одну плоску нормалізовану таблицю, зручну для подальшого аналізу та побудови зведення, тобто в таку:
Крок 1. Підключіться до файлу
Давайте створимо новий порожній файл Excel і виберемо його на вкладці дані Command Отримати дані – З файлу – З Книги (Дані — З файлу — З книги). Вказуємо місце розташування вихідного файлу з даними про продажі, а потім у вікні навігатора вибираємо потрібний нам лист і натискаємо на кнопку Перетворення даних (Перетворення даних):
В результаті всі дані з нього повинні бути завантажені в редактор Power Query:
Крок 2. Приберіть сміття
Видалити автоматично згенеровані кроки модифікований тип (Змінений тип) и Підняті заголовки (Розширені заголовки) і позбутися порожніх рядків і рядків із підсумками за допомогою фільтра нулю и РАЗОМ першим стовпцем. В результаті отримуємо наступну картину:
Крок 3. Додавання менеджерів
Щоб потім зрозуміти, де чиї продажі, необхідно додати в нашу таблицю стовпець, де в кожному рядку буде відповідне прізвище. Для цього:
1. Додамо допоміжний стовпець з номерами рядків за допомогою команди Додати стовпець – стовпець індексу – від 0 (Додати стовпець — стовпець індексу — від 0).
2. Додайте стовпець із формулою за допомогою команди Додавання стовпця – спеціальний стовпець (Додати стовпець — спеціальний стовпець) і введіть туди таку конструкцію:
Логіка цієї формули проста – якщо значення наступної комірки в першому стовпці «Продукт», то це означає, що ми натрапили на початок нової таблиці, тому виводимо значення попередньої комірки з ім'я керівника. В іншому випадку ми нічого не відображаємо, тобто нуль.
Щоб отримати батьківську клітинку з прізвищем, спочатку звернемося до таблиці з попереднього кроку #"Індекс додано", а потім вказуємо назву потрібного нам стовпця [Колонка 1] у квадратних дужках і номер комірки в цьому стовпці у фігурних дужках. Номер комірки буде на одиницю менше поточного, який беремо зі стовпця індекс, відповідно.
3. Залишилося заповнити порожні клітинки нулю імена з вищих комірок за допомогою команди Трансформація – Заливка – Вниз (Трансформація — Заливка — Вниз) і видаліть непотрібний стовпець з індексами та рядки з прізвищами в першому стовпці. В результаті отримуємо:
Крок 4. Групування в окремі таблиці за керівниками
Наступним кроком є групування рядків для кожного менеджера в окремі таблиці. Для цього на вкладці «Трансформація» скористайтеся командою «Групувати за» (Transform – Group By) і у вікні, що відкриється, виберіть стовпець «Диспетчер» і операцію «Усі рядки» (All rows), щоб просто зібрати дані без застосування будь-якої функції агрегування. них (сума, середнє та ін.). П.):
В результаті ми отримуємо окремі таблиці для кожного керівника:
Крок 5: Перетворення вкладених таблиць
Тепер наведемо таблиці, які лежать в кожній комірці отриманого стовпця Усі дані в пристойній формі.
Спочатку видаліть стовпець, який більше не потрібен, у кожній таблиці менеджер. Використовуємо знову Спеціальний стовпець таб Перетворення (Перетворення — спеціальний стовпець) та наступна формула:
Потім за допомогою іншого обчислюваного стовпця ми піднімаємо перший рядок у кожній таблиці до заголовків:
І, нарешті, виконуємо головне перетворення – розгортаємо кожну таблицю за допомогою М-функції Table.UnpivotOtherColumns:
Назви регіонів із шапки підуть у новий стовпець і ми отримаємо вужчу, але водночас довшу нормалізовану таблицю. Порожні клітинки с нулю ігноруються.
Позбувшись непотрібних проміжних колонок, маємо:
Крок 6 Розгорніть вкладені таблиці
Залишається розгорнути всі нормалізовані вкладені таблиці в єдиний список за допомогою кнопки з подвійними стрілками в заголовку стовпця:
… і ми нарешті отримуємо те, чого хотіли:
Ви можете експортувати отриману таблицю назад до Excel за допомогою команди Головна — Закрийте та завантажте — Закрийте та завантажте в… (Додому — Закрити&Завантажити — Закрити&Завантажити до…).
- Створюйте таблиці з різними заголовками з кількох книг
- Збір даних з усіх файлів у певній папці
- Збір даних з усіх аркушів книги в одну таблицю