Сборка таблиц из разных файлов 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 в один
Не подскажете, что можно сделать?
Возможно ли изменить уже записанную функцию? Или надо все заново делать?
Поняла, что удалила некоторые колонки, которые в целом могут понадобиться в будущем для сводной таблицы.
После склейки столбцов с путем и именем в новом столбце для всех книг кроме одной (той с которой на первом этапе делали настройки) вместо "table" пишет ошибку "error"
В запросе "" произошла ошибка. Expression.Error: Ключу не соответствует ни одна строка в таблице.
Сведения:
Key=Record
Table=Table.
В чем может быть причина?
Настроила power query для нескольких файлов. Доступ в папки и файлы открыт для всех сотрудников. Но папки и файлы хранятся не на общем сервере, доступ открыт через dropbox. Соответственно, адрес линка у каждого сотрудника отличается.
C:\Users\имя сотрудника\Dropbox (название компании)\название папки\сам файл
Все прекрасно работает, НО только с моего компьютера. Пока я не внесу обновления, ни один сотрудник не увидит обновления и также не может вносить никакие данные, тк они все равно не попадут в сводный файл, тк линки с других компьютеров не работают. Это и понятно, тк адрес ссылки отличается по имени. К тому же постоянно выскакивает уведомление, что линки не найдены.
Подскажите, как быть, наверное, можно как-то настроить и изменить адрес source, чтобы другие коллеги могли не только видеть последние обновления, но и также работать с файлами и вносить изменения.
Заранее благодарю
Екатерина
Да и объёмы-то несильно большие этих файлов: один 49,9, другой 36,6, а третий 47,4 мега всего... Вот пытаюсь их Повером этим собрать, а он "падает"... Да и таблички там несильно здоровые: 341 столбец на максимум 64300 строк.
Придётся отдельно с каждым файлом возиться
Насколько я понимаю данный подход может работать в одном файле с разными вкладками. Но если вкладки имеют различные названия, как поставить переменную имени вкладки в функцию?
let
Источник = Json.
in
#"Измененный тип"
можно ли как то в полуавтоматическом режиме вводить данные в поле веб запроса, чтобы каждый раз не менять?
Не зависимо от обновления PQ позволяет собрать данные из книги или из папки.
А если в книгах подлежащих консолидации может быть разное количество листов?
Для приведенного примера: Продажи, Продажи2011, Продажи2012 ... и т. д
Запросом к книге собираю все листы имеющиеся в книге (путем незначительной корректировки запроса к одному листу, что описано например в ютубе).
Затем по приведенной методике делаю функцию, которая собирает все листы в книге.
Затем запросом к папке с подстановкой функции собираю все книги в папке.
Результат - сборка всех книг из папки и всех листов из этих книг.
Исходные листы д.б. таблицами, иначе при консолидации диапазонов надо будет как-то удалять лишние строки с заголовками.
Потом из этой таблицы строится Pivot. Может да данном этапе надо проставлять флажек add this data to the data mode?
Подскажите, пожалуйста, возможно ли после загрузки запроса, сделать дополнительный столбец для ручного заполнения, чтобы при обновлении запроса, введенная информация была привязана к своей строке?
Бис!
А ларчик просто открывался. Когда знаешь, тогда всё легко
СПАСИБО!!!
Это полезный материал, но я просил помочь с проблемой=>
В запросе "" произошла ошибка. Expression.Error: Ключу не соответствует ни одна строка в таблице.
Сведения:
Key=Record
Table=Table
Хотя все делал по написанному
Хотите качественной помощи - пришлите мне ваш файл с ошибочным запросом на почту или спросите на форуме (приложив, опять же, файл).
ОК?
Помогите, пожалуйста, понять почему PQ загружает только первые 100 строк из исходного листа+1 строка шапка, а 102 строка - многоточие. Т.е. показывает, что данные есть но больше 100 строк не загружает.
В этом примере получается, есть фиксированное количество столбцов и города по строкам (т.е. наоборот, сделать транспоринирование)
И вопрос № 2, подскажите, пжлст, как получить текстовое название месяца из колонки Дата без меню (ну т.е. имею в виду код).
Спасибо!
Если формулой, то будет =Date.ToText(колонка_с_датой, "MMMM")
В одном файле появилась проблема: при обновлении данных в PQ и в сводных таблицах выскакивает сообщение "Нам не удалось загрузить модель данных. Возможно, модель данных в этой книге повреждена." И больше никаких сведений. При этом само обновление после нажатия кнопки Ок в этом сообщении дальше проходит успешно (?), но, может быть, что-то не так обновляется?
Где нужно покопаться, чтобы найти это самое "повреждение"?
P. S. Хорошо, что делал резервные копии этого файла. При использовании резервной копии такое сообщение не появляется.
При попытке создать пользовательский столбец, выдает ошибку:
"Ошибка выражения :Имя "getData" не распознано. Убедитесь, что оно написано верно"
Формула: =getData([Folder Path]&[Name])
Вопрос снят, я разобрался;)
Подскажите, пожалуйста, как изменить универсальный запрос getData из инструкции, чтобы помимо любой книги из указанной папки, он так же распознавал лист с любым названием из той или иной книги, с целью избежать ошибки, уже озвученной ранее на данном форуме, а именно:
Ее можно наглядно увидеть по
После применения данного шага на этапе формирования запроса "GetData" Error в Пользовательском столбце пропадает.
Вызвана она, скорее всего, в большинстве случаев именно привязкой к названию листа, с которого запрос считывает наши данные.
Подскажите, пожалуйста, какой именно шаг нужно применить.
Моя проблема в том, название листов, с которых нужно собирать данные, отличаются, При этом порядковый номер листа всегда одинаковый.