Импорт данных из папки с несколькими файлами (Power Query)
Используйте Power Query для объединения нескольких файлов с одной схемой, хранящейся в одной папке, в одну таблицу. Например, каждый месяц требуется объединять бюджетные книги из нескольких отделов, где столбцы одинаковы, но количество строк и значений в каждой книге отличается. После настройки можно применить дополнительные преобразования, как и к любому отдельному импортированному источнику данных, а затем обновить данные , чтобы просмотреть результаты за каждый месяц.
Примечание. В этом разделе показано, как объединить файлы из папки. Вы также можете объединять файлы, хранящиеся в SharePoint, Хранилище BLOB-объектов Azure и Azure Data Lake Storage. Процесс аналогичен.
Подготовка
- Убедитесь, что все файлы, которые требуется объединить, содержатся в выделенной папке без лишних файлов. В противном случае все файлы в папке и все выбираемые вложенные папки включаются в объединенные данные.
- Каждый файл должен иметь одинаковую схему с согласованными заголовками столбцов, типами данных и количеством столбцов. Столбцы не должны находиться в том же порядке, в котором сопоставление выполняется по именам столбцов.
- По возможности избегайте несвязанных объектов данных для источников данных, которые могут содержать несколько объектов данных, таких как JSON-файл, книга Excel или база данных Access.
Импорт из текстовых, CSV-файлов или XML-файлов
Каждый из этих файлов соответствует простой схеме: в каждом файле содержится только одна таблица данных.
- Выберите Данные >получить > данныхиз файла >из папки. Откроется диалоговое окно Обзор .
- Найдите папку, содержащую файлы, которые требуется объединить.
- Список файлов в папке появится в диалоговом окне Путь к папке <>. Убедитесь, что перечислены все нужные файлы.
Result (Результат)
Power Query автоматически создает запросы для консолидации данных из каждого файла на листе. Созданные шаги запроса и столбцы зависят от выбранной команды. Дополнительные сведения см. в разделе Обо всех этих запросах.
Импорт из JSON
- Выберите Данные >получить > данныхиз файла >из папки. Откроется диалоговое окно Обзор.
- Найдите папку, содержащую файлы, которые требуется объединить.
- Список файлов в папке появится в диалоговом окне Путь к папке <>. Убедитесь, что перечислены все нужные файлы.
- Выберите одну из команд в нижней части диалогового окна, например Объединить >Объединить & Преобразование. В разделе Обо всех этих командах рассматриваются дополнительные команды.
Появится Редактор Power Query.
Столбец Значение — это структурированный столбец List . Щелкните значок Развернуть
, а затем выберите Развернуть до новых строк.
Столбец Значение теперь является структурированным столбцом Запись . Щелкните значок Развернуть
. Откроется раскрывающееся диалоговое окно.
Result (Результат)
Power Query автоматически создает запросы для консолидации данных из каждого файла на листе. Созданные шаги запроса и столбцы зависят от выбранной команды. Дополнительные сведения см. в разделе Обо всех этих запросах.
Импорт из Excel или Access
Каждый из этих источников данных может иметь несколько объектов для импорта. Книга Excel может содержать несколько листов, таблиц Excel или именованных диапазонов. База данных Access может содержать несколько таблиц и запросов.
- Выберите Данные >получить > данныхиз файла >из папки. Откроется диалоговое окно Обзор.
- Найдите папку, содержащую файлы, которые требуется объединить.
- Список файлов в папке появится в диалоговом окне Путь к папке <>. Убедитесь, что перечислены все нужные файлы.
- Выберите одну из команд в нижней части диалогового окна, например Объединить >Объединить & Загрузить. В разделе Обо всех этих командах рассматриваются дополнительные команды.
- В диалоговом окне Объединение файлов выполните следующие действия:
- В поле Пример файла выберите файл, который будет использоваться в качестве примера данных, используемых для создания запросов. Вы не можете выбрать объект или выбрать только один объект. Но вы не можете выбрать более одного.
- Если у вас много объектов, используйте поле Поиск , чтобы найти объект, или параметры отображения вместе с кнопкой Обновить , чтобы отфильтровать список.
- Установите или снимите флажок Пропускать файлы с ошибками в нижней части диалогового окна.
- Нажмите кнопку ОК.
Result (Результат)
Power Query автоматически создает запрос для объединения данных из каждого файла в лист. Созданные шаги запроса и столбцы зависят от выбранной команды. Дополнительные сведения см. в разделе Обо всех этих запросах.
Использование команды «Объединить файлы»
Для большей гибкости можно явно объединить файлы в Редактор Power Query с помощью команды Объединить файлы. Предположим, исходная папка содержит сочетание типов файлов и вложенных папок, и вы хотите выбрать конкретные файлы с тем же типом файлов и схемой, но не с другими. Это может повысить производительность и упростить преобразования.
- Выберите Данные >Получить данные >из файла >из папки. Откроется диалоговое окно Обзор .
- Найдите папку, содержащую файлы, которые требуется объединить, и нажмите кнопку Открыть.
- Список всех файлов в папке и вложенных папках появится в диалоговом окне . Убедитесь, что перечислены все нужные файлы.
- Выберите Преобразовать данные в нижней части экрана. Откроется Редактор Power Query, в котором отображаются все файлы в папке и все вложенные папки.
- Чтобы выбрать нужные файлы, отфильтруйте столбцы, например Расширение или Путь к папке.
- Чтобы объединить файлы в одну таблицу, выберите столбец Содержимое , содержащий каждый двоичный файл (обычно первый столбец), а затем выберите Главная >Объединить файлы. Откроется диалоговое окно Объединение файлов .
- Power Query анализирует пример файла, по умолчанию первый файл в списке, чтобы использовать правильный соединитель и определить соответствующие столбцы.
Result (Результат)
Power Query автоматически создает запросы для консолидации данных из каждого файла в лист. Созданные шаги запроса и столбцы зависят от выбранной команды. Дополнительные сведения см. в разделе Обо всех этих запросах.
Обо всех этих командах
Существует несколько команд, которые можно выбрать, и каждая из них имеет разные цели.
- Объединение и преобразование данных Чтобы объединить все файлы с запросом, а затем запустить Редактор Power Query, выберите Объединить >Объединить и преобразовать данные.
- Объединение и загрузка Чтобы открыть диалоговое окно Пример файла, создайте запрос, а затем загрузите на лист, выберите Объединить >Объединить и Загрузить.
- Объединение и загрузка в Чтобы открыть диалоговое окно Пример файла, создайте запрос, а затем откройте диалоговое окно Импорт , выберите Объединить >Объединить и Загрузить в.
- Нагрузки Чтобы создать запрос с одним шагом, а затем загрузить на лист, выберите Загрузить >Загрузить.
- Загрузка в Чтобы создать запрос с одним шагом, а затем открыть диалоговое окно Импорт , выберите Загрузить >Загрузить в.
- Преобразование данныхЧтобы создать запрос с одним шагом, а затем запустить Редактор Power Query, выберите Преобразовать данные.
Обо всех этих запросах
Однако вы объединяете файлы, несколько вспомогательных запросов создаются в области Запросы в группе «Вспомогательные запросы».
- Power Query создает запрос «Образец файла» на основе примера запроса.
- Запрос функции Transform File использует запрос Parameter1, чтобы указать каждый файл (или двоичный файл) в качестве входных данных для запроса Sample File. Этот запрос также создает столбец Содержимое , содержащий содержимое файла, и автоматически расширяет структурированный столбец Запись , чтобы добавить данные столбца в результаты. Запросы «Файл преобразования» и «Пример файла» связаны, поэтому изменения в запросе «Пример файла» отражаются в запросе «Файл преобразования».
- Запрос, содержащий окончательные результаты, находится в группе «Другие запросы». По умолчанию он называется в честь папки, из которую вы импортировали файлы.
Для дальнейшего изучения щелкните правой кнопкой мыши каждый запрос и выберите Изменить , чтобы изучить каждый шаг запроса и посмотреть, как запросы работают согласованно.
Чтобы начать процесс объединения нескольких файлов, сначала поместите их все в одну папку.
Примечание: Поддерживаются файлы Excel и Access, текстовые файлы, а также файлы в форматах CSV, JSON и XML.
- Перейдите на вкладку Power Query, а затем выберите Пункт Из файла >Из папки.
Нажмите кнопку Обзор, чтобы перейти к нужной папке. Появится список файлов в папке. Нажмите кнопку Изменить, чтобы открыть редактор запросов. В этом примере мы загрузим четыре книги Excel.
Убедившись в том, что все нужные файлы присутствуют в списке, нажмите в столбце Содержимое кнопку Объединить двоичные данные.
Будет выполнен анализ каждого файла и определен правильный формат, например текстовый, Excel или JSON. В этом примере отображается список листов из первой книги Excel. Выберите нужный лист и нажмите кнопку ОК.
Затем процесс преобразования объединенных двоичных файлов автоматически создаст запрос для консолидации данных из каждого файла и отображения предварительного просмотра. Если результат вас устраивает, нажмите кнопку Закрыть & загрузить.
Известные проблемы
При загрузке процедуры объединения двоичных файлов в Excel может появиться такое сообщение об ошибке:
Если вы проигнорируете ошибки и нажмете кнопку ОК, то процесс объединения двоичных файлов будет выполнен и правильно загрузит данные, несмотря на это сообщение. Мы работаем над устранением этой ошибки.
Как собрать данные из разных файлов excel в один
Объединение таблиц из нескольких файлов
Для того, чтобы объединить и просуммировать данные из таблиц, расположенных в отдельных файлах, можно воспользоваться консолидацией данных. Для этого:
-
В каждом файле задаем имя диапазонов для наших таблиц. Для этого выделяем всю таблицу и в строке адресов пишем уникальное имя диапазона (Продажи2012) без пробелов (или с нижним подчеркиванием). Выполняем это для всех наших таблиц.
В отдельном файле, в котором будем выполнять объединение, выбрав ячейку, переходим на вкладку «Данные» и нажимаем на кнопку «Консолидация»
В строке «ССЫЛКА» сдвигаемся вправо мышью на конец ссылки, выделяя последние элементы (применять стрелки нельзя) и нажимаем на кнопку выбора диапазона.
В окне после «!» дописываем имя диапазона, из которого будут браться данные так, как присвоили в исходном файле (Продажи2012)
Повторяем для всех остальных файлов те же самые действия и нажимаем «ОК». Поставив все галки ниже в окне, в том числе «Создавать связи с исходными данными» — консолидированная таблица будет зависеть от параметров в исходных данных.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
Собирайте данные с нескольких листов на один сводный лист за секунды
Предположим, у вас есть несколько отчётов о продажах по разным регионам. Если вы хотите выполнить вычисления и построить графики ко всему объёму данных, вам нужно собрать все данные на один сводный лист. Переключение между несколькими книгами, бесконечное копирование сотен строк или написание VBA может занять много времени.
С инструментом «Объединить листы», вы сможете собрать данные на один сводный лист в считанные секунды:
Собрать данные листов из разных книг на одном листе
Собрать данные листов с одинаковым именем и объединить по имени вкладки
Объединить данные листов с идентичной структурой под одним заголовком
Сохранить форматирование в сводном листе результата
Перед началом работы добавьте «Объединить листы» в Excel
«Объединить листы» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.
Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.
Как собрать данные с нескольких листов на один в один за 3 шага
С надстройкой «Объединить листы» вы можете собрать и объединить данные нескольких листов — и из разных книг — в один главный лист всего за 3 шага:
Отметьте листы, которые нужно объединить. Дерево данных отображает все листы во всех открытых книгах.
Нажмите кнопку Объединить Готово! Все данные скопированы на один главный сводный лист.
Как объединить данные нескольких листов на одном сводном листе
Предположим, у вас есть ряд листов, и каждый из них содержит отчёт о продажах по конкретной категории продуктов. Надстройка поможет вам скопировать все эти отдельные отчёты и объединить данные в один сводный лист.
Нажмите кнопку Объединить листы Выберите Объединить данные нескольких листов на одном листе .
Отметьте флажком Таблицы с заголовками , если это так.
Совет: так, данные будут объединены под единым заголовком. Это удобно, если структура листов, которые вы объединяете, единообразна, напр., если отчёты созданы по одному шаблону. Если заголовки не совпадают, каждый диапазон данных будет добавлен со своим заголовком.
Выберите листы для объединения, установив соответствующие флажки в дереве данных.
Нажмите кнопку Объединить Готово, все данные из выбранных листов собраны на одном главном листе в новой книге.
Внимание: не забудьте сохранить эту сводную книгу на свой компьютер.
Как объединить данные из листов c одинаковым именем на одном сводном листе
Предположим, у вас есть ряд книг, и каждая из них содержит отчёт о региональных продажах. Каждый отчёт разбит на листы с данными по конкретным продуктам — таким образом, вкладки в региональных отчётах имеют одинаковые названия. Надстройка поможет вам скопировать данные по всем отчётам в один сводный лист.
Нажмите кнопку Объединить листы Выберите Объединить данные листов с одинаковым именем на одном листе .
Отметьте флажком Таблицы с заголовками если это так.
Совет: так, данные будут объединены под единым заголовком. Это удобно, если структура листов, которые вы объединяете, единообразна, напр., если отчёты созданы по одному шаблону. Если заголовки не совпадают, каждый диапазон данных будет добавлен со своим заголовком.
Выберите листы для объединения, установив соответствующие флажки в дереве данных.
Совет: вместо поочерёдного объединения листов с одинаковым называнием по группам (одна именная группа за другой), вы можете выбрать их все сразу. Надстройка автоматически соберет данные по одинаковым названиям вкладок и вынесет их на соответствующие отдельные листы сводной книги.
Нажмите кнопку Объединить Готово, все данные выбранных листов с одинаковым именем собраны в новой сводной книге.
Внимание: не забудьте сохранить эту сводную книгу на свой компьютер.
Каким образом данные копируются на сводный лист
Объединение данных по сути означает извлечение и копирование данных из нескольких исходных листов на новый лист.
Данные копируются полностью — весь диапазон до последней использованной ячейки на исходном листе.
Скопированные диапазоны добавляются последовательно, один диапазон под последней строкой предыдущего диапазона.
Надстройка XLTools «Объединить листы» сохраняет форматирование ячеек и таблиц, ссылки на ячейки, функции и формулы, объединённые ячейки, т.д.
Исходные данные не подвергаются изменениям.
Как объединить несколько листов в одну книгу
Вы можете объединить несколько листов в одну книгу с помощью надстройки XLTools Органайзер книг. Она помогает копировать и управлять множеством листов одновременно.
Как объединить данные из разных файлов MS Excel в одну таблицу
Иногда может возникнуть необходимость в объединении данных из разных файлов в одну таблицу. Если их много, а информация размещена на разных листах, то обычное копирование займет много времени.
Решить данную задачу поможет Power Query – встроенный в MS Excel инструмент обработки данных.
Рассмотрим пример объединения файлов с общей «шапкой» и данными на разных листах:
- Импортируем файлы («Данные» → «Создать запрос» → «Из файла» → «Из папки») и указываем путь:
2. Power Query покажет все файлы, размещенные по указанному адресу:
3. Нажимаем «изменить», переходим в редактор запросов и фильтруем нужные файлы:
4. Удаляем все столбцы кроме «Content»:
5. Добавляем новый столбец и вводим формулу с учетом регистра:
6. Раскрываем столбец «Пользовательская», снимаем флажок «Использовать исходное имя…».
Если есть предупреждение о незавершенном списке, то жмем «загрузить ещё»:
7. Удаляем столбцы кроме «Data», раскрываем его. Если есть предупреждение, то жмем «загрузить ещё»:
8. Power Query отразит сводные данные:
9. Ставим первую строку как заголовок: «Главная» → «Использовать первую строку…»:
10. Скрываем фильтром лишние заголовки и выгружаем данные на новый/существующий лист:
Готово! Информация со всех листов всех файлов собрана в общую таблицу при существенно меньших трудозатратах по сравнению с ручным копированием.
Таким образом, Power Query позволяет решать широкий спектр задач без использования сложных запросов и формул, что делает его универсальным инструментом для обработки данных и повышения эффективности работы.