Собираем данные из статистических форм с помощью Power Query

Как собрать нужные цифры из громоздких таблиц в отдельную, где с ними будет удобно работать — инструкция для Microsoft Office 365

Дата
22 мар. 2021
Собираем данные из статистических форм с помощью Power Query

Когда я готовила материал про аварийные школы, мне нужно было обработать данные из большого количества Excel-таблиц. Я изучала школы по всей России, и на каждый регион по каждому году приходилась своя отдельная таблица с несколькими вкладками. Работать вручную с таким количеством файлов долго и нудно. Собрать нужные цифры из громоздких таблиц в отдельную, где с ними будет удобно работать, можно с помощью программирования, но я расскажу про другой способ — с помощью Power Query.

Power Query — это технология подключения к данным, с помощью которой можно обнаруживать, подключать, объединять и уточнять данные из различных источников для последующего анализа. Функции Power Query доступны в классической версии Microsoft Excel из пакета Office 365 — с ним и будем работать.

Пошаговая инструкция

Открываем Excel. Нажимаем «Данные» → «Получить данные» → «Из книги». Выбираем один файл, по которому будем создавать шаблон:

Открывается окно редактора, выбираем нужный раздел формы, нажимаем «Преобразовать данные»:

В редакторе можно удалять верхние и нижние строки, ненужные столбцы. Редактируем лист, как нам нужно, так, чтобы остались только необходимые данные. На вкладке «Главная» нажимаем «Расширенный редактор»:

Меняем формулу, добавляя filepath вместо названия файла и в начало: (filepath)=>

Придумываем название функции, нажимаем «Закрыть и загрузить»:

Теперь будем собирать данные из всех файлов по созданному шаблону. В Excel опять идем в «Данные», но теперь «Из папки»:

Выбираем папку, где лежат статформы, нажимаем «Преобразовать данные». В редакторе Power Query добавляем настраиваемый столбец:

Задаем название столбца и вводим формулу =название_формулы([Folder Path]&[Name])

Нажимаем «ОК». Появляется новый столбец. Если все сделали правильно, в каждом строке будет написано Table. Удаляем все ненужные столбцы (как в Excel, с помощью правой кнопки мыши → «Удалить»).

Оставляем только добавленный столбец и столбец с именем файла, так как это имя будет полезно иметь в итоговых данных для каждой строки. Щелкаем мышью по значку со сдвоенными стрелками в правом верхнем углу добавленного столбца с нашей функцией. Снимаем флажок «Использовать исходное имя столбца как префикс», жмем «ОК»:

Щелкаем мышью по значку со сдвоенными стрелками в правом верхнем углу добавленного столбца с нашей функцией
Снимаем флажок «Использовать исходное имя столбца как префикс», жмем «ОК»

Мы получили таблицу со всеми регионами и нужными нам данными.