Сборка таблиц из разных файлов 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 в один
Важное замечание - для 2010 эта надстройка тоже работает! Установочный файл тот жеще, что и для 2013
Подскажите пожалуйста, возможно ли подобным образом собирать данные не только из отдельных файлов, но и из папок. Объясню. Коллеги разносят файлы по папкам 01 Январь, 02 Февраль, и т.д. При этом важно чтобы данные обновлялись
Спасибо.
Спасибо.
Николай, спасибо за отличный пример – использования функции для обработки файла, при загрузки из папки! Буду ждать видео к данной статье!
Если интересно, в качестве альтернативы данную задачу можно решить без функции, на эту тему рекомендую посмотреть видео от коллеги по вашему цеху:
Еще, как правило, есть проблема в Power Query, что путь к файлам/папкам фиксируется в запросе, но это тоже решается – можно создать динамический путь примерно так: = Excel.CurrentWorkbook(){[Name="tPath"]}[Content]{0}[Path]
Если нужно будет кому-то, вот одно из видео на эту тему:
Вообще возможности ExcelЯ c надстройкой Power Query вдохновляют!
Например: долгожданные SQL JOIN-ы таблиц теперь можно делать с помощью Power Query. В ближайшее время планирую сделать статью и видео на эту тему и выложить себе в блог:
Спасибо!
А так все очень круто запросы действиетльно очень полезные.
подскажите, пожалуйста, а как в шапке отобразить значки типа данных ('ABC', календарик, 'ABC 123') ?
1. Николай, у вас в тексте написано: "первая строка (filepath)=> превращает нашу процедуру в функцию с аргументом filepath..", а на видео вместо filepath указано filename.
2. Exel 2016 и тоже не показывает как у вас в шапке значки типа данных ('ABC', календарик, 'ABC 123')
3. а если такую функцию внедрить в Plex, на подобие плюшки в Plex "Сборка листов"?
[DataFormat.Eror] Не удалось выполнить синтаксический анализ входных данных, представленных в качестве значения даты.
подскажите пожалуйста как устранить данную проблему.
Enhanced “Combine Binaries” experience when importing from any folder
Before this release, users could combine Text or CSV files only. The combine would not work for any other supported file formats (such as Excel Workbooks, JSON files, etc.), and it would not account for transformations required on each file before combining them into a single table (such as removing the first row with header values).
With this release, we enhanced the “Combine Binaries” experience when importing from any folder so that:
Теперь вроде как можно радоваться и хлопать в ладоши, т.к. теперь не нужно извращаться с изменением кода вручную.
Однако (на моей работе) при выгрузке файлов из 1С в формате .xlsx, Power Query всё равно не может их проглотить, пока просто не пересохранишь файл в самом Excel (открыть файл, сохранить, закрыть).
Спасибо, Василий! You made my day!
как теперь лучше решать эту задачу?
А можно в студию версию сборки Excel 2016 после установки январского обновления?
1.Сборка листов
2.Собрать на итоговый лист
Остается только через фильтр удалить заголовки...
- будет собирать дольше
- не умеет обновлять собранные данные (нужно будет пересобирать)
- не умеет запоминать цепочку действий по причесыванию данных
Так что Power Query в этом вопросе однозначно круче.Зато PLEX умеет разбирать одну таблицу по разным листам, а PQ - пока нет
Подскажите а как быть когда при добавлении в папку нового файла с данными, при обновлении запроса Query задваиват данные
Случайно не подскажете, как с помощью vba кода потом удалить все запросы из полученной книги?
Макрорекордер не чирикает...
Вот такой код:
Private Sub DeleteQuery() Dim wSh As Worksheet Dim lo As ListObject On Error Resume Next ActiveWorkbook.Queries("Test").Delete For Each wSh In ActiveWorkbook.Worksheets For Each lo In wSh.ListObjects If lo.Name = "Test" Then Application.DisplayAlerts = False wSh.Delete 'wSh.ListObjects("Test").Delete End If Next lo Next wSh On Error GoTo 0 End SubВо всех файлах дали одинаковое наименование листу для выгрузки. Что-то все ранво не так.
заранее огромное спасибо за помощь
Есть какой-то способ обращения к листу не по его названию, а по номеру? Как в VBA.
Имя листа можно передавать вторым параметром
(filepath, shname) =>
Предварительно в этой же таблице с путями, отдельной колонкой можно завести имена листов.
Подскажите, пожалуйста, как это сделать (я имею в виду отдельной колонкой завести имена листов).
В Query нет функции "номер листа".
Дело в том, в моей тысяче файлов порядковый номер листов, с которых нужно собирать данные, всегда одинаковый, но имена листов различаются.
Уже скорее всего не актуально, но мало ли:)
Спасибо.
2. можно ли настроить автоматическое обновление сборной таблицы? или каждый раз при добавлении файлов нужно обновлять вручную?
2. Насколько я знаю, да - надо руками обновлять.
-пользовательский столбец
-вызвать настаиваемую функцию
-условный столбец
-столбец индекса
мне нужна функция поиска, нигде из этих столбцов я не могу ее описать
вычисляемого столбца не нашел
Спасибо.
Спасибо!
Спасибо большое!!!
У меня Excel 2016
Я работаю со штрихкодами (12цифр), для удобства я ставлю формат Дополнительный(табельный номер), отчёты мне приходят ежедневно и хочу собрать их в один файл с помощью Power Query.
И возникает проблема.
Первый столбец – вводятся данные с помощью сканера штрихкодов и они переносятся корректно.
Второй Столбец образуется из первого по формуле «ВПР» и «Еслиошибка» (появляется значение «продан»).
Вот с ним и возникает проблема, данные переносятся не корректно, а именно округляются :
122040897832 - 122040897830
118060007467 - 118060007470
155050192073 - 155050192070
И так далее…
И вопрос №2,
Power Query – делает свою «умную таблицу», а я хотел бы что бы он переносил данные в мою «умную таблицу» где уже прописаны формулы в дополнительных столбцах и идёт сразу же обработка этих данных.
Помогите пожалуйста!!!
2. Или писать ваши формулы рядом с умной таблицей PQ или вытаскивать данные из таблицы-результата в вашу функциями типа ВПР (VLOOKUP).
сразу оговорюсь, что таблица сложная много сгруппированных строк и столбцов в шапке, также есть формулы сумм в некоторых столбцах. И количество строк порядка 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? Если это конечно не секрет?