Сборка разноформатных таблиц с одного листа в Power Query
Постановка задачи
В качестве входных данных имеем Excel-файл, где на одном из листов расположены друг под другом несколько таблиц с данными по продажам следующего вида:
Обратите внимание, что:
- Таблицы разного размера и с разными наборами товаров и регионов по строчкам-столбцам без какой-либо сортировки.
- Между таблицами могут быть вставлены пустые строки.
- Количество таблиц может быть любым.
- Над каждой таблицей в первом столбце есть имя менеджера, чьи продажи иллюстрирует таблица (Иванов, Петров, Сидоров и т.д.)
- Названия товаров и регионов во всех таблицах написаны одинаково - с точностью до регистра.
Конечная цель - собрать данные из всех таблиц в одну плоскую нормированную таблицу, удобную для последующего анализа и построения сводной, т.е. вот в такую:
Шаг 1. Подключаемся к файлу
Создадим новый пустой файл Excel и в нём выберем на вкладке Данные команду Получить данные - Из файла - Из книги (Data - From file - From workbook). Укажем местоположение исходного файла с данными по продажам и затем в окне навигатора выберем нужный нам лист и нажмём на кнопку Преобразовать данные (Transform Data):
Шаг 2. Убираем мусор
Удалим автоматически созданные шаги Измененный тип (Changed Type) и Повышенные заголовки (Promoted Headers) и избавимся от пустых строк и строк с итогами с помощью фильтра null и ИТОГО по первому столбцу. В итоге получим следующую картинку:
Шаг 3. Добавляем менеджеров
Чтобы понимать впоследствии где чьи продажи, необходимо добавить к нашей таблице столбец, где в каждой строке будет соответствующая фамилия. Для этого:
1. Добавим вспомогательный столбец с нумерацией строк с помощью команды Добавление столбца - Столбец индекса - От 0 (Add column - Index column - From 0).
2. Добавим столбец с формулой командой Добавление столбца - Настраиваемый столбец (Add column - Custom column) и введём туда следующую конструкцию:
Логика этой формулы проста - если в первом столбце значение очередной ячейки равно "Товар", то это значит, что мы наткнулись на начало новой таблицы, поэтому выводим значение предыдущей ячейки с именем менеджера. В противном случае - не выводим ничего, т.е. null.
Чтобы взять вышестоящую ячейку с фамилией, мы сначала ссылаемся на таблицу с предыдущего шага #"Добавлен индекс", а затем указываем имя нужного нам столбца [Column1] в квадратных скобках и номер ячейки в этом столбце в фигурных. Номер ячейки будет на единицу меньше, чем текущий, который мы берём из столбца Индекс, соответственно.
3. Останется заполнить пустые ячейки с null именами из вышестоящих ячеек командой Преобразование - Заполнить - Вниз (Transform - Fill - Down) и удалить ненужный более столбец с индексами и строки с фамилиями в первом столбце. В итоге получим:
Шаг 4. Группируем в отдельные таблицы по менеджерам
Следующим шагом сгруппируем строки по каждому менеджеру в отдельные таблицы. Для этого на вкладке Преобразование используем команду Группировать по (Transform - Group By) и в открывшемся окне выберем столбец Менеджер и операцию Все строки (All rows), чтобы просто собрать данные, не применяя к ним никакой агрегирующей функции (суммы, среднего и т.п.):
В итоге получим отдельные таблицы для каждого менеджера:
Шаг 5. Преобразуем вложенные таблицы
Теперь приведём таблицы, лежащие в каждой ячейке получившегося столбца ВсеДанные в приличный вид.
Сначала удалим в каждой таблице ненужный более столбец Менеджер. Опять используем Настраиваемый столбец на вкладке Преобразование (Transform - Custom column) и следующую формулу:
Затем ещё одним вычисляемым столбцом поднимаем в каждой таблице первую строку в заголовки:
И наконец выполняем главное преобразование - отмену свёртывания каждой таблицы с помощью М-функции Table.UnpivotOtherColumns:
Названия регионов из шапки уйдут в новый столбец и мы получим на выходе более узкую, но при этом более длинную таблицу нормализованного вида. Пустые ячейки с null при этом игнорируются.
Избавившись от ненужных промежуточных столбцов, имеем:
Шаг 6. Разворачиваем вложенные таблицы
Останется развернуть все нормализованные вложенные таблицы в единый список, используя кнопку с двойными стрелками в шапке столбца:
... и мы, наконец, получим то, что требовалось:
Выгрузить результирующую табличку обратно в Excel можно с помощью команды Главная - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close&Load - Close&Load to...).
Ссылки по теме
- Сборка таблиц с разными шапками из нескольких книг
- Сбор данных из всех файлов заданной папки
- Сборка данных со всех листов книги в одну таблицу
У меня все получилось )))
Большое спасибо за Ваши праведные труды по образовыванию глубинного темного населения типа меня.
Низкий поклон и большой респект ВАМ!
Опять же у меня есть пожелание на будущее.
Мое субъективное мнение - усложнять примеры надо крайне осторожно дабы "не сорвать резьбу".
Анпивот вложенных таблиц лучше было бы дать отдельным коротким примером так как в решении данной задачи он не был нужен но утяжелил ролик для понимания такого чайника как я (((.
Я большой сторонник копотких понятных примеров так как по моему мнению именно на таких примерах проще всего изучить предмет.
Разумеется, это исключительно мое субъективное.
Еще раз большое спасибо!!!