Создавайте таблицы с разными заголовками из нескольких книг.

Постановка задачи

У нас есть несколько файлов (в нашем примере — 4 штуки, в общем случае — сколько угодно) в одной папке. Отчеты:

Внутри эти файлы выглядят так:

В которой:

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

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

Шаг 1. Подготовка каталога имен столбцов

Первое, что нужно сделать, это подготовить справочник со всеми возможными вариантами названий столбцов и их правильной интерпретацией:

Преобразуем этот список в динамическую «умную» таблицу с помощью кнопки «Форматировать как таблицу» на вкладке Главная (Главная страница — Форматировать как таблицу) или сочетание клавиш Ctrl+T и загрузите его в Power Query с помощью команды Данные – из таблицы/диапазона (Данные — из таблицы/диапазона). В последних версиях Excel он был переименован в С листьями (Из листа).

В окне редактора запросов Power Query мы традиционно удаляем шаг Измененный тип и добавьте вместо него новый шаг, нажав на кнопку f_xв строке формул (если она не видна, то ее можно включить на вкладке Обзор) и введите туда формулу на встроенном языке Power Query M:

=Таблица.ToRows(Источник)

Эта команда преобразует файл, загруженный на предыдущем шаге. Источник ссылочную таблицу в список, состоящий из вложенных списков (List), каждый из которых, в свою очередь, представляет собой пару значений Было-стало из одной строки:

Этот тип данных понадобится нам чуть позже, при массовом переименовании заголовков всех загруженных таблиц.

После завершения преобразования выберите команды На главную — Закрыть и загрузить — Закрыть и загрузить… и тип импорта Просто создайте соединение (Главная страница — Закрыть и загрузить — Закрыть и загрузить в… — Только создать соединение) и вернитесь в Excel.

Шаг 2. Загружаем все из всех файлов как есть

Теперь загрузим содержимое всех наших файлов из папки — пока как есть. Выбор команд Данные – Получить данные – Из файла – Из папки (Данные — Получить данные — Из файла — Из папки) а затем папку, в которой находятся наши исходные книги.

В окне предварительного просмотра нажмите Конвертировать (Преобразование) or Изменить (Редактировать):

А затем развернуть содержимое всех скачанных файлов (Двоичный) кнопка с двойными стрелками в заголовке столбца Содержание:

Power Query на примере первого файла (Восток.xlsx) спросит у нас название листа, который мы хотим взять из каждой книги – выбираем Фото и нажмите ОК:

После этого (по сути) произойдет несколько неочевидных для пользователя событий, последствия которых хорошо видны на левой панели:

1. Power Query возьмет первый файл из папки (он у нас будет Восток.xlsx — посмотреть Пример файла) в качестве примера и импортирует его содержимое, создав запрос Преобразование файла примера. Этот запрос будет состоять из нескольких простых шагов, таких как Источник (доступ к файлу) Навигация (выбор листов) и возможно повышение титров. Этот запрос может загружать данные только из одного конкретного файла. Восток.xlsx.
   
2. На основе этого запроса будет создана связанная с ним функция. Преобразовать файл (обозначается характерным значком f_x), где исходный файл будет уже не константой, а переменным значением — параметром. Таким образом, эта функция может извлекать данные из любой книги, которую мы ей подсовываем в качестве аргумента.
   
3. Функция будет применена по очереди к каждому файлу (двоичному) из столбца Содержание – за это отвечает шаг Вызов пользовательской функции в нашем запросе, который добавляет столбец в список файлов Преобразовать файл с результатами импорта из каждой книги:

   

4. Лишние столбцы удалены.
5. Содержимое вложенных таблиц раскрывается (шаг Расширенный столбец таблицы) — и видим окончательные результаты сбора данных со всех книг:

   
   

Шаг 3. Шлифование

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

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

Вы можете очень легко решить все эти проблемы — просто настройте запрос Convert Sample File. Все корректировки, которые мы вносим в него, автоматически попадут в связанную функцию Convert file, а значит, будут использоваться позже при импорте данных из каждого файла.

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

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

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

Эта функция берет таблицу из предыдущего шага. Повышенные заголовки и переименовывает в нем все столбцы согласно вложенному справочному списку Последние новости. Третий аргумент MissingField.Игнорировать нужен для того, чтобы по тем заголовкам, которые есть в справочнике, но нет в таблице, не возникала ошибка.

Собственно, это все.

Возвращаясь к запросу Отчеты мы увидим совсем другую картину – гораздо приятнее предыдущей:

• Что такое Power Query, Power Pivot, Power BI и зачем они нужны пользователю Excel
  
• Сбор данных со всех файлов в заданной папке
• Сбор данных со всех листов книги в одну таблицу