Сборка таблиц из разных файлов 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 в один
сразу оговорюсь, что таблица сложная много сгруппированных строк и столбцов в шапке, также есть формулы сумм в некоторых столбцах. И количество строк порядка 1500 шт.
Если есть время помогите или направьте в нужном направлении. Есть файл банковских операций до 01.05. и файлы обновлений. Слияние на один лист, к примеру "Альфа", исходного файла и обновлений из папки я настроил. Теперь пробую сделать так чтобы информация на листе "Альфа" сохранялась даже при удалении выписок, т.е. чтобы конечный лист помнил свое текущее состояние и обновлялся только в случая поступления новых выписок. Power Query такое конечно позволяет делать результат мне выгружает в новый лист, так как выгрузка в текущую таблицу данных запрещена.
Спасибо!
Подскажите, как можно сделать слияние, если во всех файлах отличается имя листа (каждый раз генерится новое имя) а структура данных абсолютно одинакова. Я так понимаю нужно делать 2 запроса и превращать их в функцию: 1-й для файла, 2-й для имени книги и подставлять их в итоговый большой запрос?
Но идея в том, что надо сначала собрать сами файлы в Power Query, а потом применить к ним функцию =Excel.Workbook(), чтобы вытащить из каждого весь список листов (независимо от их имен).
А потом уже разворачиваем содержимое листов и работает с данными.
Как-то так, если на пальцах.
На примере кода из статьи, на втором шаге:
Продажи_Sheet = Источник{[Item="Продажи",Kind="Sheet"]}[Data],Продажи_Sheet = Источник{0}[Data],В итоге, перебирая файлы из папки, Ексель не ищет лист и таблицу на нем по названию, а просто забирает первый лист и первую таблицу на нем. Если вместо 0 поставить 1, то соответственно второй и так далее.
Если собирали с помощью функции "из папки", то после создания, перейдите в редактор ПоверКвери, на шаг "Преобразовать файл" и в коде исправьте на:
Лист1_Sheet = Источник{0}[Data],Подскажите что неправильно сделала?
Excel 2013
Здесь много вариантов применения PQ. Все очень подробно.
Прошу помощи...при добавлении пользовательского столбца, прописав формулу, не активируется кнопка "Ок" и указывается что "Определение формулы не завершено". Отчеты скачал из данного примера и пробовал другие.
Пробовал и формулу "=Excel.Workbook([Content])" тоже самое... не активируется "ок" и "определение формулы не завершено".
Почему? Что не так?(((((
Excel 2013 и 2010...
Например названия месяцев на русском и английском
Я бы сделала дубликат столбца, и заменила бы Январь на January, Февраль на February и т.д. итого 12 раз
Спасибо за статью, у меня все получилось.
Единственное - я бы хотела чтобы Power Query вставил данные начиная с определенной строки, например, начиная с 7-ой строки. Как это реализовать? Подскажите, пожалуйста!
Дальше будет окно, где можно выбрать на какой лист выгрузить и начиная с какой конкретно ячейки.
Я добавляю папку. В нем файлы и в них много листов. Как указать конкретный лист? Не осилил, прошу Ваш подсказать
Как можно обойти ошибку доступа к файлам EXCEL, при обновлении запроса, если один или несколько файлов открыты и на них нет группового доступа, а также закрывать файла нельзя. Может быть можно дописать в функцию команду как для обработки run-time ошибок в VB?.
Спасибо за статью. Николай, подскажите, а с группировкой строк в исходных файлах совсем ничего не поделать? У меня выгрузки из базы данных сгруппированы по строкам и файлов достаточно много. Приходится заходить в каждый и разгруппировывать..
1) А если файлы в разных папках можно как нибудь их собрать одним запросом используя относительный путь к папке типа - = Excel.CurrentWorkbook(){[Name="_001_path_spiski"]}[Content]{0}[ Path]
? Или только каждую таблицу вытаскивать на отдельный лист а потом их все обьеденять?
2)
В готовую таблицу на основе запроса power query нужно добавить новые столбцы которые должны забиваться вручную!!!!
Важно в исходной таблице из которой брал данные запрос power query этих столбцов нет и быть не должно.
Осуществимо ли это?
1) Если по файлам >1млн строк, то загрузить их все можно в PowerPivot только? Можно ли раскидать по листам Excel не фильтруя-разделяя на 2 и более запроса/-ов внутри PQ.
2) Можно ли через PQ получать количество строк по каждому из файлов? Есть список файлов, напротив каждого получить количество строк по содержимому.
есть куча однотипных файлов Эксель, в них есть слово итого и есть сумма. Нужно посчитать общую сумму всех этих "итого"
есть ещё поле дата (которая везде указана в разных форматах) вот очень желательно чтобы сумма считала за конкретный месяц. Я прочёл описание по Power Query, но не смог его установить. Установщик выдает следующее сообщение:
"Для Microsoft Power Query для Excel (x64) требуется 64-разрядная версия Microsoft Excel 2010 с пакетом обновления 1 (SP1) или Microsoft Excel 2013. Сначала установите 64-разрядную версию Microsoft Excel 2010 с пакетом обновления 1 (SP 1) или Microsoft Excel 2013."
У меня следующие ТТХ:
Windows 10 PRO
Exel 2016 в списке надстроек я не нашёл Power Query.
Буду благодарен за ответ!
1. Собирая данные из отдельных Книг в одной папке на сетевом диске - будет-ли PQ работать, если один или несколько исходных файлов открыты на удалённых компьютерах, при условии, что после каждого внесения изменения будут сохранять внесенную информацию.
2. Корректно ли работает PQ во всём диапазоне EXCEL? При каждом обновлении информации PQ проверяет все таблицы или в ней есть алгоритм, который не рассматривает старые значения, а вносит лишь новые?
Спасибо!
У меня в одном столбце данные в виде дат и текста, при сборке таблиц из нескольких файлов PQ уделяет данные из этого столбца. Подскажите как это исправить.
Николай, подскажите пожалуйста, а как можно победить ограничение в 1 000 строк в фильтре столбца в PQ? Если это конечно не секрет?
Не подскажете, что можно сделать?
Возможно ли изменить уже записанную функцию? Или надо все заново делать?
Поняла, что удалила некоторые колонки, которые в целом могут понадобиться в будущем для сводной таблицы.
После склейки столбцов с путем и именем в новом столбце для всех книг кроме одной (той с которой на первом этапе делали настройки) вместо "table" пишет ошибку "error"
В запросе "" произошла ошибка. Expression.Error: Ключу не соответствует ни одна строка в таблице.
Сведения:
Key=Record
Table=Table.
В чем может быть причина?