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