Сборка таблиц из разных файлов Excel с помощью Power Query
Постановка задачи
Давайте разберем красивое решение для одной из весьма стандартных ситуаций, с которой рано или поздно сталкивается большинство пользователей Excel: нужно быстро и автоматически собрать данные из большого количества файлов в одну итоговую таблицу.
Предположим, что у нас есть вот такая папка, в которой содержится несколько файлов с данными из филиалов-городов:
Количество файлов роли не играет и может меняться в будущем. В каждом файле есть лист с именем Продажи, где расположена таблица с данными:
Количество строк (заказов) в таблицах, само-собой, разное, но набор столбцов везде стандартный.
Задача: собрать данные из всех файлов в одну книгу с последующим автоматическим обновлением при добавлении-удалении файлов-городов или строк в таблицах. По итоговой консолидированной таблице затем можно будет строить любые отчеты, сводные таблицы, фильтровать-сортировать данные и т.д. Главное - суметь собрать.
Подбираем оружие
Для решения нам потребуется последняя версия Excel 2016 (в нее нужный функционал уже встроен по умолчанию) или предыдущие версии Excel 2010-2013 с установленной бесплатной надстройкой Power Query от Microsoft (скачать ее можно здесь). Power Query - это супергибкий и супермощный инструмент для загрузки в Excel данных из внешнего мира с последующей их зачисткой и обработкой. Power Query поддерживает практически все существующие источники данных - от текстовых файлов до SQL и даже Facebook :)
Если у вас нет Excel 2013 или 2016, то дальше можно не читать (шучу). В более древних версиях Excel подобную задачу можно реализовать только программированием макроса на Visual Basic (что весьма непросто для начинающих) или монотонным ручным копированием (что долго и порождает ошибки).
Шаг 1. Импортируем один файл как образец
Для начала давайте импортируем данные из одной книги в качестве примера, чтобы Excel "подхватил идею". Для этого создайте новую пустую книгу и...
- если у вас Excel 2016, то откройте вкладку Данные и выберите Создать запрос - Из файла - Из книги (Data - New Query- From file - From Excel)
- если у вас Excel 2010-2013 с установленной надстройкой Power Query, то откройте вкладку Power Query и выберите на ней Из файла - Из книги (From file - From Excel)
Если нажать в правом нижнем углу этого окна кнопку Загрузить (Load), то таблица будет сразу импортирована на лист в исходном виде. Для одиночного файла - это хорошо, но нам нужно загрузить много таких файлов, поэтому мы пойдем немного другим путем и жмем кнопку Правка (Edit). После этого должен в отдельном окне отобразиться редактор запросов Power Query с нашими данными из книги:
Это очень мощный инструмент, позволяющий "допилить" таблицу под нужный нам вид. Даже поверхностное описание всех его функций заняло бы под сотню страниц, но, если совсем кратко, то с помощью этого окна можно:
- отфильтровывать ненужные данные, пустые строки, строки с ошибками
- сортировать данные по одному или нескольким столбцам
- избавляться от повторов
- делить слипшийся текст по столбцам (по разделителям, количеству символов и т.д.)
- приводить текст в порядок (удалять лишние пробелы, исправлять регистр и т.д.)
- всячески преобразовывать типы данных (превращать числа как текст в нормальные числа и наоборот)
- транспонировать (поворачивать) таблицы и разворачивать двумерные кросс-таблицы в плоские
- добавлять к таблице дополнительные столбцы и использовать в них формулы и функции на встроенном в Power Query языке М.
- ...
Для примера, давайте добавим к нашей таблице столбец с текстовым названием месяца, чтобы потом проще было строить отчеты сводных таблиц. Для этого щелкните правой кнопкой мыши по заголовку столбца Дата и выберите команду Дублировать столбец (Duplicate Column), а затем щелкните правой кнопкой мыши по заголовку появившегося столбца-дубликата и выберите команды Преобразование - Месяц - Название месяца:
Должен образоваться новый столбец с текстовыми названиями месяца для каждой строки. Дважды щелкнув по заголовку столбца, его можно переименовать из Копия Дата в более удобное Месяц, например.
Если в каких-то столбцах программа не совсем корректно распознала тип данных, то ей можно помочь, щелкнув по значку формата в левой части каждого столбца:
Исключить строки с ошибками или пустые строки, а также ненужных менеджеров или заказчиков можно с помощью простого фильтра:
Причем все выполненные преобразования фиксируются в правой панели, где их всегда можно откатить (крестик) или изменить их параметры (шестеренка):
Легко и изящно, не правда ли?
Шаг 2. Преобразуем наш запрос в функцию
Чтобы впоследствии повторить все сделанные преобразования данных для каждой импортируемой книги, нужно преобразовать наш созданный запрос в функцию, которая затем будет применяться, по очереди, ко всем нашим файлам. Сделать это, на самом деле, очень просто.
В редакторе запросов перейдите на вкладку Просмотр и нажмите кнопку Расширенный редактор (View - Advanced Editor). Должно открыться окно, где все наши предыдущие действия будут записаны в виде кода на языке М. Обратите внимание, что в коде жестко прописан путь к файлу, который мы импортировали для примера:
Теперь аккуратно вносим пару правок:
Смысл их прост: первая строка (filepath)=> превращает нашу процедуру в функцию с аргументом filepath, а ниже мы меняем фиксированный путь на значение этой переменной.
Все. Жмем на Готово и должны увидеть вот это:
Не пугайтесь, что пропали данные - на самом деле все ОК, все так и должно выглядеть :) Мы успешно создали нашу пользовательскую функцию, где запомнился весь алгоритм импорта и обработки данных без привязки к конкретному файлу. Осталось дать ей более понятное имя (например getData) на панели справа в поле Имя и можно жать Главная - Закрыть и загрузить (Home - Close and Load). Обратите внимание, что в коде жестко прописан путь к файлу, который мы импортировали для примера.. Вы вернетесь в основное окно Microsoft Excel, но справа должна появиться панель с созданным подключением к нашей функции:
Шаг 3. Собираем все файлы
Все самое сложное - позади, осталась приятная и легкая часть. Идем на вкладку Данные - Создать запрос - Из файла - Из папки (Data - New Query - From file - From folder) или, если у вас Excel 2010-2013, аналогично на вкладку Power Query. В появившемся окне указываем папку, где лежат все наши исходные файлы-города и жмем ОК. Следующим шагом должно открыться окно, где будут перечислены все найденные в этой папке (и ее подпапках) файлы Excel и детализация по каждому из них:
Жмем Изменить (Edit) и опять попадаем в знакомое окно редактора запросов.
Теперь нужно добавить к нашей таблице еще один столбец с нашей созданной функцией, которая "вытянет" данные из каждого файла. Для этого идем на вкладку Добавить столбец - Пользовательский столбец (Add Column - Add Custom Column) и в появившемся окне вводим нашу функцию getData, указав для ее в качестве аргумента полный путь к каждому файлу:
После нажатия на ОК созданный столбец должен добавиться к нашей таблице справа.
Теперь удалим все ненужные столбцы (как в Excel, с помощью правой кнопки мыши - Удалить), оставив только добавленный столбец и столбец с именем файла, т.к. это имя (а точнее - город) будет полезно иметь в итоговых данных для каждой строки.
А теперь "вау-момент" - щелкнем мышью по значку со своенным стрелками в правом верхнем углу добавленного столбца с нашей функцией:
... снимаем флажок Использовать исходное имя столбца как префикс (Use original column name as prefix)и жмем ОК. И наша функция подгрузит и обработает данные из каждого файла, следуя записанному алгоритму и собрав все в общую таблицу:
Для полной красоты можно еще убрать расширения .xlsx из первого столбца с именами файлов - стандартной заменой на "ничего" (правой кнопкой мыши по заголовку столбца - Заменить) и переименовать этот столбец в Город. А также подправить формат данных в столбце с датой.
Все! Жмем на Главной - Закрыть и загрузить (Home - Close & Load). Все собранные запросом данные по всем городам будут выгружены на текущий лист Excel в формате "умной таблицы":
Созданное подключение и нашу функцию сборки не нужно никак отдельно сохранять - они сохраняются вместе с текущим файлом обычным образом.
В будущем, при любых изменениях в папке (добавлении-удалении городов) или в файлах (изменение количества строк) достаточно будет щелкнуть правой кнопкой мыши прямо по таблице или по запросу в правой панели и выбрать команду Обновить (Refresh) - Power Query "пересоберет" все данные заново за несколько секунд.
P.S.
Поправка. После январских обновлений 2017 года Power Query научился собирать Excel'евские книги сам, т.е. не нужно больше делать отдельную функцию - это происходит автоматически. Таким образом второй шаг из этой статьи уже не нужен и весь процесс становится заметно проще:
- Выбрать Создать запрос - Из файла - Из папки - Выбрать папку - ОК
- После появления списка файлов нажать Изменить
- В окне редактора запросов развернуть двойной стрелкой столбец Binary и выбрать имя листа, который нужно взять из каждого файла
И все! Песня!
Ссылки по теме
- Редизайн кросс-таблицы в плоскую, подходящую для построения сводных таблиц
- Построение анимированной пузырьковой диаграммы в Power View
- Макрос для сборки листов из разных файлов Excel в один
Собираю, как у вас данные из кучи файлов. Спасибо все работает. Помогите, пожалуйста, нужен двойной фильтр. Но данные не могу предоставить из-за конфиденциальности. Допустим у меня есть колонка тип с цифрами от 1 до 13 и колонка со множеством пользователей. В собираемой таблице в типах 4 и 9 нужно удалить данные пользователей с Null.
У меня удаляется фильтр и остальные типы вообще исчезают. Что я делаю не так?
Спасибо
Огромное спасибо за ваши статьи.
Подскажите, пожалуйста, как решить небольшую проблему.
Не могу никак собрать данные, хотя на другом компьютере все получалось без проблем. При загрузке запроса выдаётся сообщение:
"Сбой инициализации источника данных.
Проверьте сервер или обратитесь к администратору данных. Убедитесь, что внешняя база данных доступна, и вновь повторите операцию. При повторении сообщения создайте новый источник для подключения к базе данных."
Все собираемые файлы хранятся на рабочем столе в папке с уникальным названием.
Спасибо за полезные уроки!
У меня проблема - на шаге внесения изменений "Расширенного редактора" выскакивает надпись "Ожидался токен Eof ".
Что это может означать?
Все делаю по инструкции.
помогите разобраться.
у меня есть книга, в которой140 листов. на каждом таблица из 6-7 столбцов. 5 столбцов одинаковые, а 1-2 столбца могут присутствовать или отсутствовать на отдельных листах (в средине таблицы).
мне нужно объединить данные из этих листов в один массив, но когда я делаю это с помощью PowerQuery - то столбцы с одинаковым названием не выстраиваются друг под другом.
Я очень вас благодарю за ваш труд.
Пример отличный.
Но возникает вопрос: не могу решить.... Меня множество файлов во множестве папок. И у них у всех первые три строки в виде шапки имеют объединение ячеек. Это вызывает ошибку в редакторе запросов при проведении 1го иди 3го шага
подскажите как обойти сию трудность или как во множестве файлов Excel поснимать объединение ячеек внутри файлов, не открывая их.
Благодарю.
когда подгружаю в PQ или текстовый файл(из примера в книге) или "загрузка данных через Word" , то таблицы полностью в PQ не подгружаются , всегда пару строк или столбцов остаются за бортом( я уже и обновила excel (у меня2019). какие действия можно предпринять?
Огорчение. Как быть?
Спасибо!
Николай, спасибо Вам большое за полезное видео. Всё делал как Вы рассказывали и всё получилось. Но столкнулся с такой проблемой. При обновлении общего файла куда сливаются данные из 70 других файлов, сбивается настройка ширины столбцов и из-за этого очень долго происходит само обновление. (около 20-30 минут. Размер файла 90000 строк на 90 столбцов).
Различные комбинации галочек в свойствах подключения не дают никакого результата, максимум что удалось добиться - чтобы не сбивал фильтрацию при обновлении.
Кто-то сталкивался с подобными проблемами? Есть ли какое-то решение или идеи что нажать чтобы всё работало?)
Заранее спасибо за обратную связь!)
Можно ли с помощью power query объединить несколько книг, состоящих их нескольких листов, так чтоб в новой книге каждый лист суммировался отдельно.
Собираете данные из всех книг в одну таблицу, добавляя колонку с именем листа, а потом по этой колонке выполняете в Power Query группировку (Преобразование - Группировать), чтобы просуммировать данные по каждому листу.
Как-то так
Подскажите, почему Power Query ругается, когда пытаюсь сделать запрос на загрузку файл формата XLSB из папки?
Заранее спасибо.
А можно сделать с помощью PQ все тоже самое, но в обратную сторону?
Т.е. у меня есть таблица, я её обрабатываю в PQ, а в итоге нужно "раздербанить" эту таблицу на множество частей (например по критерию "город") в виде отдельных файлов Excel?
Без VBA и PLEX силами PQ это реально сделать?
Спасибо.
После обновления Экселя пропала кнопка "Получить данные из файла":(
Подскажите пжл, как теперь ее найти и вытащить?
Есть подключение к Аксес, Интернету, тексту, другим источникам. А подключение к другой книге excel отсутствует..
- Выбрать Создать запрос - Из файла - Из папки - Выбрать папку - ОК
- После появления списка файлов нажать Изменить
- В окне редактора запросов развернуть двойной стрелкой столбец Binary и выбрать имя листа, который нужно взять из каждого файла
Плюс небольшой момент. Если нужны все столбцы, в качестве примера после открытия Binary нужно взять заранее выбранный файл в котором есть все встречающиеся столбцы, ну или можно образец сделать со всеми названиями столбцов, которые могут быть.