Что делать, если нужно быстро собрать данные из большого количества файлов Excel в одну таблицу? Вариантов тут, обычно, несколько:
тупо копипастить вручную (с кучей ошибок в процессе)
писать макрос для сбора данных или заказывать его знакомому VBA-программисту
делегировать задачу подчиненным
На самом деле, начиная с Excel 2013 есть еще один путь - сильно проще и на порядок удобнее - надстройка Power Query. Для Excel 2013 ее можно совершенно бесплатно скачать и подключить, а в Excel 2016 она уже входит по-умолчанию. Написал о том, как использовать ее в подобной ситуации. Основные шаги тут следующие:
импорт одного файла для примера
преобразование процедуры импорта и "причесывания" данных в функцию
импорт всех файлов из папки
добавление созданной пользовательской функции для загрузки данных из каждой книги
Быстро и изящно.
Если у вас пока нет Excel 2013-2016 - все равно гляньте, чтобы оценить красоту решения
Надстройки Power (Query / Pivot / View) для Excel
Надстройки Power (Query / Pivot / View) для Excel это хорошие инструменты уровня Self-Service BI, которые продвинутый аналитик может эффективно использовать в своей работе.8)
Николай, спасибо за отличный пример – использования функции для обработки файла, при загрузки из папки! Буду ждать видео к данной статье!
Если интересно, в качестве альтернативы данную задачу можно решить без функции, на эту тему рекомендую посмотреть видео от коллеги по вашему цеху:
Еще, как правило, есть проблема в Power Query, что путь к файлам/папкам фиксируется в запросе, но это тоже решается – можно создать динамический путь примерно так: = Excel.CurrentWorkbook(){[Name="tPath"]}[Content]{0}[Path]
Если нужно будет кому-то, вот одно из видео на эту тему:
Вообще возможности ExcelЯ c надстройкой Power Query вдохновляют!
Например: долгожданные SQL JOIN-ы таблиц теперь можно делать с помощью Power Query. У меня есть статья на эту тему в блоге:
Обновление надстройки от Января 2017 теперь умеет работать с Екселем из папки, однако...
Ура! Теперь надо обязательно ставить обновление от Января 2017. Вот что пишут:
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:
Excel analyzes the input files from the Folder query and detects the right file format to use (i.e., Text or Excel Workbook). Users can select a specific object from the list (such as a spreadsheet name) to use for data combine. Excel automatically creates the following entities
Теперь вроде как можно радоваться и хлопать в ладоши, т.к. теперь не нужно извращаться с изменением кода вручную.
Однако (на моей работе) при выгрузке файлов из 1С в формате .xlsx, Power Query всё равно не может их проглотить, пока просто не пересохранишь файл в самом Excel (открыть файл, сохранить, закрыть).
Николай, а как добавить столбец, в котором для каждой строки таблицы будет указан полный путь файла-источника? И (или) имя листа в файле-источнике, из которого собственно "тянутся" данные? Спасибо.
Спасибо.
Надстройки Power (Query / Pivot / View) для Excel это хорошие инструменты уровня Self-Service BI, которые продвинутый аналитик может эффективно использовать в своей работе.8)
Николай, спасибо за отличный пример – использования функции для обработки файла, при загрузки из папки! Буду ждать видео к данной статье!
Если интересно, в качестве альтернативы данную задачу можно решить без функции, на эту тему рекомендую посмотреть видео от коллеги по вашему цеху:
Еще, как правило, есть проблема в Power Query, что путь к файлам/папкам фиксируется в запросе, но это тоже решается – можно создать динамический путь примерно так: = Excel.CurrentWorkbook(){[Name="tPath"]}[Content]{0}[Path]
Если нужно будет кому-то, вот одно из видео на эту тему:
Вообще возможности ExcelЯ c надстройкой Power Query вдохновляют!
Например: долгожданные SQL JOIN-ы таблиц теперь можно делать с помощью Power Query. У меня есть статья на эту тему в блоге:
Спасибо!
Ура! Теперь надо обязательно ставить обновление от Января 2017. Вот что пишут:
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:
Excel analyzes the input files from the Folder query and detects the right file format to use (i.e., Text or Excel Workbook).
Users can select a specific object from the list (such as a spreadsheet name) to use for data combine.
Excel automatically creates the following entities
Теперь вроде как можно радоваться и хлопать в ладоши, т.к. теперь не нужно извращаться с изменением кода вручную.
Однако (на моей работе) при выгрузке файлов из 1С в формате .xlsx, Power Query всё равно не может их проглотить, пока просто не пересохранишь файл в самом Excel (открыть файл, сохранить, закрыть).
Николай, подскажите, пожалайста
Чем лучше пользоваться Power Query или Power Pivot? Есть ли какие-то исключительные сильные стороны какой-либо из них в сравнении с другой?
Александр по вопросу Power Query или Power Pivot можно посмотреть
Спасибо.
При попытке создать запрос выдает вот такую ошибку! Подскажите в чем может быть причина?