Импорт данных из папки с несколькими файлами (Power Query)
Используйте Power Query, чтобы объединить несколько файлов с одной схемой, храняной в одной папке, в одну таблицу. Например, каждый месяц необходимо объединить бюджетные книги из нескольких отделов, где столбцы одинаковы, но количество строк и значений в каждой книге различается. После ее настроив, вы можете применить дополнительные преобразования, как к любому импортируемму источнику данных, а затем обновить данные, чтобы увидеть результаты за каждый месяц.
Примечание. В этой теме показано, как объединять файлы из папки. Вы также можете объединять файлы, хранимые в SharePoint, azure BLOB-служба хранилища и Azure Data Lake служба хранилища. Процесс аналогичный.
Подготовка
- Убедитесь, что все файлы, которые вы хотите объединить, содержатся в выделенной папке без лишних файлов. В противном случае все файлы в папке и все вложенные папки, которые вы выбрали, будут включены в данные для совместной обработки.
- У каждого файла должна быть та же схема, что и для согласованных столбцов, типов данных и количества столбцов. Столбцы не должны быть в том же порядке, что и имена столбцов.
- По возможности старайтесь не использовать несвязанные объекты данных для источников данных, которые могут иметь несколько объектов данных, например JSON-файл, Excel книгу или базу данных Access.
Импорт из текстовых, CSV- или XML-файлов
Каждый из этих файлов имеет простой шаблон, в каждом из которых есть только одна таблица данных.
- Выберите Данные >Получить данные > из файла >из папки. Появится диалоговое окно Обзор.
- Найдите папку с файлами, которые вы хотите объединить.
- Список файлов в папке появится в диалоговом папке. Убедитесь, что в списке указаны все нужные файлы.
Power Query автоматически создает запросы для консолидации данных из каждого файла на каждом из них. Шаги запроса и созданные столбцы зависят от того, какую команду вы выбрали. Дополнительные сведения см. в разделе Сведения обо всех этих запросах.
Импорт из JSON
- Выберите Данные >Получить данные > из файла >из папки. Появится диалоговое окно Обзор.
- Найдите папку с файлами, которые вы хотите объединить.
- Список файлов в папке появится в диалоговом папке. Убедитесь, что в списке указаны все нужные файлы.
- Выберите одну из команд в нижней части диалогового окна, например Объединить>Объединить & Transform. В разделе Обо всех этих командах обсуждаются дополнительные команды.
Появится редактор Power Query.
Столбец Значение является структурированным столбцом списка. Выберите значок Развернуть, а затем выберите Развернуть до новых строк.
Столбец Value (Значение) теперь является структурированным столбцом Record (Запись). Выберите значок развернуть. Появится диалоговое окно с drop-down.
Power Query автоматически создает запросы для консолидации данных из каждого файла на каждом из них. Шаги запроса и созданные столбцы зависят от того, какую команду вы выбрали. Дополнительные сведения см. в разделе Сведения обо всех этих запросах.
Импорт из Excel или Access
Каждый из этих источников данных может иметь несколько объектов для импорта. Книга Excel может иметь несколько книг, Excel таблиц или именовых диапазонов. База данных Access может иметь несколько таблиц и запросов.
- Выберите Данные >Получить данные > из файла >из папки. Появится диалоговое окно Обзор.
- Найдите папку с файлами, которые вы хотите объединить.
- Список файлов в папке появится в диалоговом папке. Убедитесь, что в списке указаны все нужные файлы.
- Выберите одну из команд в нижней части диалогового окна, например Объединить> Объединить & Загрузить. В разделе Обо всех этих командах обсуждаются дополнительные команды.
- В диалоговом окне Объединение файлов:
- В поле Образец файла выберите файл, который будет использоваться в качестве примера данных для создания запросов. Нельзя выбрать объект или выбрать только один объект. Однако выбрать несколько из них нельзя.
- Если объектов много, используйте поле Поиск, чтобы найти объект, или параметры отображения, а также кнопку Обновить для фильтрации списка.
- В нижней части диалогового окна можно выбрать или отобирать для файлов с ошибками поле Пропускать файлы с ошибками.
- Нажмите кнопку ОК.
Power Query автоматически создает запрос для консолидации данных из каждого файла на каждом из них. Шаги запроса и созданные столбцы зависят от того, какую команду вы выбрали. Дополнительные сведения см. в разделе Сведения обо всех этих запросах.
Использование команды «Объединить файлы»
Для большей гибкости вы можете явным образом объединить файлы в редакторе Power Query с помощью команды Объединить файлы. Предположим, что в исходных папках есть как типы файлов, так и вложенные папки, и вы хотите подцелить определенные файлы с тем же типом файлов и схемой, но не с другими. Это может повысить производительность и упростить преобразования.
- Выберите data >Get Data > Data > From File >From Folder. Появится диалоговое окно Обзор.
- Найдите папку с файлами, которые вы хотите объединить, и выберите открыть.
- Список всех файлов в папке и вложенных папках появится в диалоговомпапки. Убедитесь, что в списке указаны все нужные файлы.
- Внизу выберите преобразовать данные. Откроется редактор Power Query со всеми файлами в папке и во вложенных папках.
- Чтобы выбрать нужные файлы, отфильтруем столбцы, например Расширение или Путь к папке.
- Чтобы объединить файлы в одну таблицу, выберите столбец Содержимое, содержащий каждый двоичный (обычно первый столбец), а затем выберите Главная >Объединить файлы. Появится диалоговое окно Объединение файлов.
- Power Query анализирует пример файла (по умолчанию первый файл в списке), чтобы использовать правильный соединительщик и определить совпадающие столбцы.
Power Query автоматически создает запросы для консолидации данных из каждого файла на каждом из них. Шаги запроса и созданные столбцы зависят от того, какую команду вы выбрали. Дополнительные сведения см. в разделе Сведения обо всех этих запросах.
Обо всех этих командах
Существует несколько команд, которые можно выбрать, и каждая из них имеет разные цели.
- Объединение и преобразование данных Чтобы объединить все файлы с запросом, а затем запустить редактор Power Query, выберите объединить>объединить и преобразовать данные.
- Объединение и загрузка Чтобы отобразить диалоговое окно Образец файла, создайте запрос, а затем загрузите на таблицу, выберите объединить>Объединить и загрузить.
- Объединение и загрузка в Чтобы отобразить диалоговое окно Образец файла, создайте запрос, а затем в диалоговом окне Импорт выберите объединить> Объединить и загрузить в.
- Нагрузки Чтобы создать запрос с одним шагом, а затем загрузить на таблицу, выберите загрузить >загрузить.
- Загрузить в Чтобы создать запрос одним шагом и отобразить диалоговое окно Импорт, выберите загрузить >Загрузить в.
- Преобразование данныхЧтобы создать запрос с одним шагом и запустить редактор Power Query, выберите Преобразовать данные.
Обо всех этих запросах
Тем не менее при объединения файлов в области Запросы в группе «Запросы-справки» создается несколько вспомогательных запросов.
- Power Query создает запрос «Образец файла» на основе примера запроса.
- Запрос функции Transform File (Файл преобразования) использует запрос Parameter1 для указания каждого файла (двоичного) в качестве входного в запрос «Образец файла». Этот запрос также создает столбец Содержимое, содержащий содержимое файла, и автоматически расширяет его, чтобы добавить данные столбца в результаты. Запросы «Преобразовать файл» и «Образец файла» связаны, поэтому изменения в запросе «Образец файла» отражаются в запросе «Преобразовать файл».
- Запрос, содержащий окончательные результаты, находится в группе «Другие запросы». По умолчанию он называется папкой, из которого вы импортировали файлы.
Для дальнейшего изучения щелкните каждый запрос правой кнопкой мыши и выберите Изменить, чтобы изучить каждый шаг запроса и увидеть, как работают запросы на этапе.
Чтобы начать процесс объединения нескольких файлов, сначала поместите их все в одну папку.
Примечание: Поддерживаются файлы Excel и Access, текстовые файлы, а также файлы в форматах CSV, JSON и XML.
- На вкладке Power Query выберите из файла > из папки.
- Нажмите кнопку Обзор, чтобы перейти к нужной папке. Появится список файлов в папке. Нажмите кнопку Изменить, чтобы открыть редактор запросов. В этом примере мы загрузим четыре книги Excel.
- Убедившись в том, что все нужные файлы присутствуют в списке, нажмите в столбце Содержимое кнопку Объединить двоичные данные.
- Будет выполнен анализ каждого файла и определен правильный формат, например текстовый, Excel или JSON. В этом примере отображается список листов из первой книги Excel. Выберите нужный лист и нажмите кнопку ОК.
- При преобразовании разных файлов автоматически создается запрос для консолидации данных из каждого файла и предварительного просмотра. Если результат вас устраивает, нажмите кнопку Закрыть & загрузить.
Когда процесс объединения двоичных файлов завершится, данные листов из списка будут консолидированы на одном листе. - Если исходные файлы данных изменятся, вы всегда сможете обновить импортируемые данные. Щелкните в любом месте диапазона данных, а затем перейдите в инструменты запросов >обновить. Кроме того, вы можете легко применить дополнительные шаги преобразования или извлечения, изменяя автоматически созданный запрос-образец, не беспокоясь об изменении или создании дополнительных шагов запроса функций. любые изменения запроса-образец автоматически создаются в связанном запросе функции.
Известные проблемы
При загрузке процедуры объединения двоичных файлов в Excel может появиться такое сообщение об ошибке:
Если вы проигнорируете ошибки и нажмете кнопку ОК, то процесс объединения двоичных файлов будет выполнен и правильно загрузит данные, несмотря на это сообщение. Мы работаем над устранением этой ошибки.
Консолидация таблиц из нескольких файлов Excel
В больших компаниях, с несколькими отделами или подразделениями, часто встречается потребность в создании общего реестра на основе данных от каждого отдела. Не всегда этот реестр можно вести совместно, поэтому приходится его собирать по кусочкам. Можно автоматизировать этот процесс с помощью подобного решения.
Описание задачи
- Необходимо объединить таблицы, содержащиеся в большом количестве Excel файлов. При этом все они хранятся в разных подкаталогах рабочей папки.
- После создания консолидированного файла со всеми таблицами, необходимо обработать данные и разбить единую таблицу на несколько, в зависимости от заданного критерия или (как в нашем случае) на основе столбцов, в которых проставляются знаки «+».
Описание программы
Программа представляет собой файл Excel, содержащее меню. Тут пользователь задает папку в которой расположены подпапки с файлами. Для удобства, выбор папки также реализован по клику на иконку.
После выбора папки останется только нажать на кнопку «Консолидация данных». Программа пройдет по всем каталогам и подкаталогам в указанной папке, найдет все файлы Excel и создаст сводную таблицу содержащую все данные из найденных файлов.
Помимо самой консолидации в программе реализована функция сортировки и группировки данных. Здесь стоит отметить, что конкретная обработка данных зависит от потребностей компании. Это может быть как проверка условий из других источников, так и всевозможные дополнительные вычисления.
В данном случае после консолидации данных была выполнена разбивка сводной таблицы на несколько подтаблиц в зависимости от наличия отметки «+». Все подтаблицы сохранялись в итоговый файл на отдельных листах Excel.
Как свести таблицы в excel из разных файлов
Объединение таблиц из нескольких файлов
Для того, чтобы объединить и просуммировать данные из таблиц, расположенных в отдельных файлах, можно воспользоваться консолидацией данных. Для этого:
-
В каждом файле задаем имя диапазонов для наших таблиц. Для этого выделяем всю таблицу и в строке адресов пишем уникальное имя диапазона (Продажи2012) без пробелов (или с нижним подчеркиванием). Выполняем это для всех наших таблиц.
В отдельном файле, в котором будем выполнять объединение, выбрав ячейку, переходим на вкладку «Данные» и нажимаем на кнопку «Консолидация»
В строке «ССЫЛКА» сдвигаемся вправо мышью на конец ссылки, выделяя последние элементы (применять стрелки нельзя) и нажимаем на кнопку выбора диапазона.
В окне после «!» дописываем имя диапазона, из которого будут браться данные так, как присвоили в исходном файле (Продажи2012)
Повторяем для всех остальных файлов те же самые действия и нажимаем «ОК». Поставив все галки ниже в окне, в том числе «Создавать связи с исходными данными» — консолидированная таблица будет зависеть от параметров в исходных данных.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
Как свести таблицы в excel из разных файлов
Типичная задача — имеем несколько однотипных таблиц на разных листах рабочей книги. Хотим, чтобы при внесении данных в любую из этих таблиц — данные добавлялись в одну общую таблицу, расположенную на отдельном листе.
Инструкция
Устанавливаем себе надстройку ЁXCEL . Читаем справку.
Приступаем к решению. Переходим во вкладку ЁXCEL главного меню, нажимаем кнопку «Таблицы», в выпавшем списке выбираем команду «Объединить таблицы»:
В открывшемся диалоговом окне выделяем листы с таблицами, которые необходимо объединить и нажимаем «ОК»:
Программа сформирует запрос — объединит таблицы и выведет информационное сообщение:
Переходим на итоговый лист (тот где хотим вывести общую таблицу), устанавливаем курсор в ячейку «A1«. Переходим в главном меню во вкладку «Данные» в разделе «Получение внешних данных» нажимаем кнопку «Существующие подключения»:
В открывшемся диалоговом окне выбираем «Подключения в этой книге» — «Запрос из Excel Files» и нажимаем «Открыть»:
В открывшемся диалоговом окне устанавливаем переключатели в положения «Таблица» и «Имеющийся лист», нажимаем «ОК»:
В активном листе будет создана таблица, которая будет объединять таблицы, расположенные на указанных нами листах:
Теперь все таблицы связаны. Добавьте новые строчки в любую из объединенных таблиц. Перейдите во вкладку «Данные» и нажмите кнопку «Обновить все»:
В итоговой таблице появятся строчки, добавленные в выбранный вами лист.
Чтобы обновление итоговой таблицы происходило автоматически вставьте в модуль каждого листа (кроме Итогового) следующий код (Как вставлять макросы?):
Private Sub Worksheet_Change(ByVal Target As Range) ActiveWorkbook.RefreshAll End Sub
Видео-пример
Важно:
- Количество столбцов во всех таблицах должно быть одинаково;
- Кроме таблиц на листах не должно быть никакой информации;
- Если вы переместили файл в другую папку или отправили файл коллеге по электронной почте — необходимо заново связать таблицы (в запросе прописывается абсолютный путь к файлу).
Чтобы запрос работал не зависимо от того в какой папке лежит файл вставьте в модуль «ЭтаКнига» следующий код:
Private Sub Workbook_Open() Dim q As String On Error Resume Next q = Application.ThisWorkbook.Path & "\" & Application.ThisWorkbook.Name 'Определяем текущий путь к файлу With ActiveWorkbook.Connections("Запрос из Excel Files").ODBCConnection 'Имя запроса .Connection = "ODBC;DSN=Excel Files;DBQ=" & q & _ ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" 'Меняем строку подключения End With End Sub
Данный макрос при открытии книги будет определять текущий путь к файлу и менять путь к файлу в запросе.
| Файл | Описание | Размер файла: | Скачивания |
|---|---|---|---|
| Пример | 21 Кб | 2906 |
Чтобы оценить всю прелесть — выньте файл из архива и при загрузке файла включите макросы.
Возможные ошибки при использовании этого метода:
- В таблицах одинаковое количество столбцов, но при попытке использовать запрос получаем сообщение: «В таблицах или запросах, выбранных в запросе на объединение, не совпадает число столбцов.» Причина: в одном или нескольких листах с таблицами есть пустые столбцы (в них раньше были данные) и MS Excel считает их столбцами таблиц. Решение: Выделить и удалить все пустые столбцы на листах с таблицами.
- Все столбцы удалили, но ошибка осталась. Причина: файл сохранен на сетевом диске или открыт из почтовой программы. Решение: сохраните файл у себя на локальном компьютере, выполните запрос, вставьте в него макрос меняющий путь в запросе и выложите обратно в сеть (если нужно).
- В полученном запросе в некоторых ячейках пропадают данные. Причина: в ваших таблицах встречаются столбцы, которые одновременно содержатся и числовые и текстовые значения. MS Excel считает, что эти столбцы должны содержать только числа и не выводит текст. Решение:преобразуйте все числовые значения в этих столбцах во всех таблицах в текстовые. Обновите запрос — текстовые данные появятся.
Добавить комментарий
Комментарии
# Евгений 18.07.2023 10:13
Добрый день. помогите сформировать одну таблицу из множеста разных книг(таблицы одинаковые). В каждой книге несколько вкладок с одинаковыми названиями.
# Иркэ 24.06.2023 20:49
Доброго времени. Есть две таблицы с данными-1: ФИО, место госпитализации, 2: ФИО ,группа инвалидности. Не все ФИО в 1 и 2 таблицах не однозначны. Как объединить таблицы так,чтобы они объединились по ФИО, место госпитализации, группа инвалидности?
Благодарю
# Виктория Р. 20.04.2023 18:26
Здравствуйте, есть одна общая таблица на весь коллектив, когда один из работников вносит в нее данные другой работник не может вывести в неё данные. Как можно решить эту проблему?