Сбор данных из файлов excel в заданной папке

Обновлено: 07.07.2024

Представим ситуацию: отдел маркетинга уже начал готовиться к новому году (кстати, 1 сентября, уже пора!), придумал концепцию поздравления, три варианта подарка и запросил у всех сотрудников список партнеров с указанием, кому какой подарок дарить.

Наша же задача, собрать эти данные, внести в одну общую таблицу, в одинаковом виде, чтобы с данными потом было удобно работать. Как лучше всего это сделать? Рассмотрим несколько вариантов:

Онлайн таблица
Рассылка шаблона и сбор ответных данных с помощью Query (Получить данные)
Формы
Автоматизация (сервис Flow)

Вариант первый - онлайн таблица.

Создаем шаблон для ввода данных на листе, оформляем его, как таблицу (Ctrl+T) - это значительно упростит ввод и обработку данных. Если делаете, как в примере выше, нужно отметить галочкой, что в таблице уже есть заголовки (My table has headers).

Теперь данные в таблицу добавляются легко - просто нужно начать писать на следующей, после окончания таблице строчке, и они автоматом добавятся в таблицу. Кстати, после ввода данных в первую ячейку вместо Enter можно нажать Tab, и курсор переместится в следующую ячейку.

Добавление данных - вместо Enter можно нажать Tab. Листайте вправо --> Добавление данных: в таблицу добавилась новая строка.

Если сотрудников и партнеров немного, то можно так и оставить, и добавить остальных вручную. Если их больше 10-20, это уже займет достаточно много времени, и лучше пусть сотрудники сами заполняют.

Чтобы не было путаницы в вставке данных, можно сделать выпадающие списки и проверку данных. Сделаем выпадающий список на виды подарков, важность подарка, (как сделать выпадающий список я написал здесь: раз , два , три ). Также пометим строки, в которых не вся информация указана красным: выбираем всю таблицу (это можно сделать, поместив курсор в левый верхний угол, так, чтобы он поменял свою форму на стрелку вправо-вниз и нажав левую кнопку мыши - см. галерею ниже), затем создать новое правило условного форматирования: если хотя бы одно поле пустое, то пометить применить формат "красная заливка".

Добавление условного форматирования. Листайте вправо --> Добавление условного форматирования. Листайте вправо -->

Если автоформатирование применить ко всей таблице, то при расширении таблицы на новые строки к ним также будет применено условное форматирование.

Осталось дело за малым - выложить таблицу на сервер SharePoint или SharePoint Online или OneDrive или OneDrive Business и предоставить доступ на редактирования всем сотрудникам.

После дедлайна таблицу можно "суммировать" с помощью сводной таблицы и предоставить отделу маркетинга готовые данные.

Плюсы : просто, быстро, все сотрудники будут заносить данные самостоятельно.

Минусы : нельзя разграничить права, сотрудники могут случайно или намеренно изменить/удалить внесенные ранее данные.

Рассылка шаблона и сбор данных обратно по почте.

На примере файлов бюджетов покажу как можно собирать данные со всех этих файлов в одну итоговую таблицу и просуммировать все присланные данные по статьям из каждой таблицы.

Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?

Модель агрегации файлов.zip (53,5 KiB, 1 118 скачиваний)

Видеоинструкция:

Для ведения бюджета применяется таблица такого вида:

Сама таблица преобразована заранее в так называемую "умную" таблицу: выделяем таблицу -вкладка Вставка (Insert) и выбрать Таблица (Table) :

Для каждого филиала отдельный файл только с одним этим листом. После заполнения филиалы присылают эти файлы в головной офис, где их необходимо объединить в одну такую же таблицу, но суммировать данные по каждой статье и каждому месяцу, чтобы получить единый файл бюджета с суммированием по каждой статье от всех филиалов.
Все действия будут производиться при помощи Power Query и лишь в самом конце на лист будет выгружена итоговая таблица, которую потом надо будет только обновлять(пара кликов мыши), если данные изменятся или будут присланы файла от за другие месяцы или от других филиалов. Никаких макросов использовать не надо.

Перейдем к реализации.
Создаем новую пустую книгу, переходим на вкладку Данные(или Power Query) -Получить данные -Из файла -Из папки:

В появившемся окне указываем путь к папке, в которую были помещены файлы бюджетов, присланные филиалами

Нажимаем Ок.
Появится окно, в котором будет список всех файлов в выбранной папке. Нажимаем Изменить и попадем в редактор запросов Power Query. Здесь пошагово мы и будем делать все преобразования отчетов для их объединения и приведения к нужному виду.
Для начала удалим лишние столбцы, оставив только два столбца: Content и Name :

Для этого выделяем лишние столбцы с зажатой клавишей Shift и нажимаем Delete(или правая кнопка мыши -Удалить столбцы).
Теперь надо получить таблицы из файлов. Для этого переходим на вкладку Добавить столбец -Пользовательский столбец. В появившемся окне даем имя новому столбцу(у меня это Данные), а в поле формулы вписываем такую функцию:
=Excel.Workbook([Content])

Нажимаем Ок.
В отчет будет добавлен новый столбец. Необходимо его «развернуть» - получить все данные из каждого файла. Для этого нажимаем на этом столбце значок в виде двух разнонаправленных стрелок, снимаем галочку «Использовать исходное имя столбца как префикс» и нажимаем Ок:

Будет добавлено еще два столбца, из которых аналогичным образом разворачиваем столбец Data(нажатием на значок в виде двух разнонаправленных стрелок). Там будут наименования вроде Column1, Column2 и т.д. – это нормально, выгружаем все как есть. Получится такая картина:

Теперь столбцы Content , Name и Name.1 можно удалить (в столбце Name записано имя файла, поэтому если оно нужно – можно оставить на время отладки запроса. Но впоследствии данные будут объединены и просуммированы и оно все равно будет лишним).
Т.к. у нас реальные данные в таблицах начинаются не с первой строки и имеется шапка – необходимо убрать все лишние строки, чтобы исключить ошибки при дальнейшем суммировании данных. Для этого сначала в Column2 раскрываем меню фильтра и убираем галочки со значений NULL :

А в Column1 в фильтре убираем галочку с пункта «Статьи». Теперь первой строкой данных у нас идут названия месяцев. Делаем их заголовками: вкладка Преобразование -Таблица -Использовать первую строку в качестве заголовков:

Т.к. первый столбец теперь будет иметь не совсем понятное имя вроде Column1 - имеет смысл переименовать его в «Статьи».
Далее выделяем все столбцы месяцев и столбец Итого -вкладка Преобразование -группа Любой столбец -раскрываем список Тип данных и выбираем Десятичное число:

Теперь надо объединить все одинаковые строки статей и просуммировать данные по ним за каждый месяц. Выделяем столбец Статьи вкладка Преобразование -Таблица -Группировать по:

В появившемся окне сразу выбираем режим Дополнительно и указываем параметры группировки:

Группировка – оставляем поле Статьи . Ниже создаем 13 столбцов группировки – по одному на каждый месяц и один для Итого. Для каждого столбца указываем имя(лучше такое же как и имя исходного столбца – название месяца, т.к. именно они будут использоваться в итоговой таблице), Операция – Сумма .
Останется перейти на вкладку Главная -Закрыть и загрузить. Готовая таблица будет выгружена на новый лист текущей книги.
Теперь, если в папку будут помещены другие файлы или имеющиеся будут заменены другими и результирующую таблицу бюджета потребуется обновить – все, что необходимо будет сделать, это на созданной PowerQuery таблице в любой ячейке щелкнуть правой кнопкой мыши и выбрать Обновить:

Все файлы в папке будут просмотрены, преобразованы и просуммированы.

Некоторым пользователям приложения Microsoft Excel приходится решать задачу сбора данных из разных рабочих книг. Кому-то для формирования месячных, квартальных или годовых отчетов, а кому-то для составления реестров или баланса. При этом многократно повторяются такие операции как открытие файла, выделение определенного диапазона, копирование значений выделенного диапазона, закрытие файла, вставка скопированных значений на отдельный итоговый лист. Такие действия пользователей можно и нужно автоматизировать, ускоряя консолидацию данных и делая свою работу более эффективной.

Сбор данных из различных книг Excel на один лист в два этапа

В более ранних публикациях рассматривались инструменты, предназначенные для решения других задач, но при помощи которых можно собрать данные из разных файлов Excel на итоговый лист в два этапа.

Далее речь пойдет о том, как проделать все тоже самое, но в одно действие.

Сбор данных из нескольких рабочих книг Excel на отдельный лист

Для быстрого объединения данных, расположенных в разных рабочих книгах можно использовать надстройку для Excel.

Надстройка позволяет копировать заданные пользователем диапазоны данных на всех заданных листах всех выбранных рабочих книг и вставлять скопированные данные на новый лист активной рабочей книги. При работе с этой надстройкой пользователь имеет возможность:

1) Выбирать нужные рабочие книги для последующей обработки;

2) Осуществлять выбор листов по именам, по номерам, по заданному значению в ячейках выделенного диапазона;

3) Задавать диапазоны ячеек для копирования;

а) Используемые диапазоны - это диапазоны, начинающиеся с первой используемой ячейки и заканчивающиеся последней используемой ячейкой;

б) Предварительно выделенные диапазоны на каждом листе - диапазоны, выделенные пользователем самостоятельно, исходя из его потребностей по консолидации данных;

в) Одноименные диапазоны - диапазоны с одинаковым адресом на каждом листе, указанным на активном рабочем листе;

г) Выборочные диапазоны - диапазоны ячеек, начинающиеся с выбранной ячейки и заканчивающиеся:

- концом листа (последней используемой ячейкой);

- последней заполненной ячейкой в заданном столбце;

- последней заполненной ячейкой в заданной строке;

д) Именованные диапазоны.

4) При вставке данных на итоговый лист заменять формулы результатами их вычислений (при отсутствии объединенных ячеек);

5) При сборе данных на итоговый лист вставлять связи, при этом изменения значений в исходных диапазонах ячеек будут отображаться на итоговом листе;

6) Подставлять имена листов перед вставляемыми на итоговый лист диапазонами;

7) Подставлять имена рабочих книг перед вставляемыми на итоговый лист диапазонами;

8) Выбирать вертикальное либо горизонтальное размещение данных на листе с итогами;

9) Сохранять все настройки диалогового окна для последующих сеансов работы.

*В зависимости от выбранной опции диапазоны будут располагаться один ниже другого (вертикальное расположение), либо один правее другого (горизонтальное расположение).

После установки надстройки останется лишь запустить программу нажатием одной кнопки из главного меню Excel или с ленты, в зависимости от версии Excel и в диалоговом окне выбрать необходимые параметры для сбора информации.

В случае, если итоговый лист содержит ненужные пустые строки, можно провести их автоматическое удаление при помощи надстройки для выборочного удаления строк.

Этот макрос предназначен для сбора (загрузки) информации из файлов Excel, расположенных в одной папке.

Для работы этого макроса, помимо него самого, вам понадобится добавить в свой файл:

для получения списка файлов в папке для вывода диалогового окна выбора папки с запоминанием выбранной папки для отображения процесса обработки файлов (модуль класса и форму)

Если при тестировании макроса у вас возникает ошибка, что не найдена та или иная функция,
— проверьте, все ли необходимые компоненты (которые перечислены выше) вы добавили в свой файл.

Этот макрос я публикую прежде всего для себя (поскольку использую этот код чуть ли ни в каждой третьей своей программе),
поэтому я не буду помогать вам в настройке этого макроса, если у вас он вдруг не заработает.

Макрос при запуске выдает диалоговое окно для выбора папки, в которой расположены обрабатываемые файлы,
после чего открывает каждый из файлов, считывает из него данные, помещает их в текущую книгу (из которой запущен макрос),
и закрывает обработанный файл без сохранения изменений.

После того, как очередной файл обработан, он перемещается во вторую папку («архив»).

On Error Resume Next : Err.Clear

' запрашиваем пути к папкам с файлами

InvoiceFolder$ = GetFolder(1, , "Выберите папку с файлами заявок (из Outlook)" )

If InvoiceFolder$ = "" Then MsgBox "Не задана папка с заявками" , vbCritical, "Обработка заявок невозможна" : Exit Sub

ArchieveFolder$ = GetFolder(2, , "Выберите папку, куда будут помещаться обработанные файлы заявок" )

If ArchieveFolder$ = "" Then MsgBox "Не задана папка для архива заявок" , vbCritical, "Обработка заявок невозможна" : Exit Sub

Dim coll As Collection

' загружаем список файлов по маске имени файла

Set coll = FilenamesCollection(InvoiceFolder$, "Заявка №*от*.xls*" , 1)

If coll.Count = 0 Then

MsgBox "Не найдено ни одной заявки для обработки в папке" & vbNewLine & InvoiceFolder$, _

vbExclamation, "Нет необработанных заявок"

Dim pi As New ProgressIndicator: pi.Show "Обработка заявок" , , 2

pi.StartNewAction , , , , , coll.Count ' отображаем прогресс-бар

Dim WB As Workbook, sh As Worksheet, ra As Range

Application.ScreenUpdating = False ' отключаем обновление экрана (чтобы процесс открытия файлов не был виден)

' перебираем все найденные в папке файлы

For Each Filename In coll

' обновляем информацию на прогресс-баре

pi.SubAction "Обрабатывается заявка $index из $count" , "Файл заявки: " & Dir(Filename), "$time"

pi.Log "Файл: " & Dir(Filename)

' открываем очередной файл в режиме «только чтение»

Set WB = Nothing : Set WB = Workbooks. Open (Filename, False , True )

If WB Is Nothing Then ' не удалось открыть файл

pi.Log vbTab & "ОШИБКА при загрузке файла. Файл не обработан."

Else ' файл успешно открыт

Set sh = WB.Worksheets(1) ' будем брать данные с первого листа

' берем диапазон ячеек с ячейки B1 до последней заполненной в столбце B

Set ra = sh.Range(sh.Range( "b1" ), sh.Range( "b" & sh.Rows.Count). End (xlUp))

' ==== переносим данные в наш файл (shb - кодовое имя листа, куда помещаем данные)

shb.Range( "a" & shb.Rows.Count). End (xlUp).Offset(1).Resize(, ra.Rows.Count).Value = _

' ==== конец обработки данных из очередного файла

WB. Close False : DoEvents ' закрываем обработанный файл без сохранения изменений

pi.Log vbTab & "Файл успешно обработан."

' перемещаем обработанный файл из папки InvoiceFolder$ в папку ArchieveFolder$

Name Filename As ArchieveFolder$ & Dir(Filename, vbNormal)

' закрываем прогресс-бар, включаем обновление экрана

pi.Hide: DoEvents: Application.ScreenUpdating = True

MsgBox "Обработка заявок завершена" , vbInformation

Во вложении - файл со всеми необходимыми макросами для сбора данных из других файлов Excel

Используйте Power Query, чтобы объединить несколько файлов с одной схемой, храняной в одной папке, в одну таблицу. Например, каждый месяц необходимо объединить бюджетные книги из нескольких отделов, где столбцы одинаковы, но количество строк и значений в каждой книге различается. После ее настроив, вы можете применить дополнительные преобразования, как к любому импортируемму источнику данных, а затем обновить данные, чтобы увидеть результаты за каждый месяц.

Примечание. В этой теме показано, как объединять файлы из папки. Вы также можете объединять файлы, хранимые в SharePoint, azure BLOB-служба хранилища и Azure Data Lake служба хранилища. Процесс аналогичный.

Убедитесь, что все файлы, которые вы хотите объединить, содержатся в выделенной папке без лишних файлов. В противном случае все файлы в папке и все вложенные папки, которые вы выбрали, будут включены в данные для совместной обработки.

У каждого файла должна быть та же схема, что и для согласованных столбцов, типов данных и количества столбцов. Столбцы не должны быть в том же порядке, что и имена столбцов.

По возможности старайтесь не использовать несвязанные объекты данных для источников данных, которые могут иметь несколько объектов данных, например JSON-файл, Excel книгу или базу данных Access.

Каждый из этих файлов имеет простой шаблон, в каждом из которых есть только одна таблица данных.

Выберите Данные > Получить данные > из файла > из папки. Появится диалоговое окно Обзор.

Найдите папку с файлами, которые вы хотите объединить.

Список файлов в папке появится в диалоговом <путь к> папке. Убедитесь, что в списке указаны все нужные файлы.

Выберите одну из команд в нижней части диалогового окна, например Объединить> Объединить & Загрузить. В разделе Обо всех этих командах обсуждаются дополнительные команды.

Если выбрать команду Объединить, появится диалоговое окно Объединение файлов. Чтобы изменить параметры файла, выберите каждый файл в поле Образец файла, задав нужные параметры Источник файла,Делитер и Обнаружение типов данных. Кроме того, в нижней части диалогового окна можно выбрать или отобрать диалоговое окно Пропускать файлы с ошибками.

Power Query автоматически создает запросы для консолидации данных из каждого файла на каждом из них. Шаги запроса и созданные столбцы зависят от того, какую команду вы выбрали. Дополнительные сведения см. в разделе Сведения обо всех этих запросах.

Выберите Данные > Получить данные > из файла > из папки. Появится диалоговое окно Обзор.

Найдите папку с файлами, которые вы хотите объединить.

Список файлов в папке появится в диалоговом <путь к> папке. Убедитесь, что в списке указаны все нужные файлы.

Выберите одну из команд в нижней части диалогового окна, например Объединить> Объединить & Transform. В разделе Обо всех этих командах обсуждаются дополнительные команды.

Появится редактор Power Query.

Столбец Значение является структурированным столбцом списка. Выберите значок Развернуть , а затем выберите Развернуть до новых строк.

Столбец Value (Значение) теперь является структурированным столбцом Record (Запись). Выберите значок развернуть. Появится диалоговое окно с drop-down.

Вы можете выбрать все столбцы, содержащие значения данных. На лентевыберите Главная , стрелку рядом с клавишей Remove Columns, а затем выберите Удалить другие столбцы.

Выберите Главная> закрыть & загрузить.

Каждый из этих источников данных может иметь несколько объектов для импорта. Книга Excel может иметь несколько книг, Excel таблиц или именовых диапазонов. База данных Access может иметь несколько таблиц и запросов.

Выберите Данные > Получить данные > из файла > из папки. Появится диалоговое окно Обзор.

Найдите папку с файлами, которые вы хотите объединить.

Список файлов в папке появится в диалоговом <путь к> папке. Убедитесь, что в списке указаны все нужные файлы.

В диалоговом окне Объединение файлов:

В поле Образец файла выберите файл, который будет использоваться в качестве примера данных для создания запросов. Нельзя выбрать объект или выбрать только один объект. Однако выбрать несколько из них нельзя.

Если объектов много, используйте поле Поиск, чтобы найти объект, или параметры отображения, а также кнопку Обновить для фильтрации списка.

В нижней части диалогового окна можно выбрать или отобирать для файлов с ошибками поле Пропускать файлы с ошибками.

Power Query автоматически создает запрос для консолидации данных из каждого файла на каждом из них. Шаги запроса и созданные столбцы зависят от того, какую команду вы выбрали. Дополнительные сведения см. в разделе Сведения обо всех этих запросах.

Для большей гибкости вы можете явным образом объединить файлы в редакторе Power Query с помощью команды Объединить файлы. Предположим, что в исходных папках есть как типы файлов, так и вложенные папки, и вы хотите подцелить определенные файлы с тем же типом файлов и схемой, но не с другими. Это может повысить производительность и упростить преобразования.

Выберите data > Get Data > Data > From File > From Folder. Появится диалоговое окно Обзор.

Найдите папку с файлами, которые вы хотите объединить, и выберите открыть.

Список всех файлов в папке и вложенных папках появится в диалоговом<путь>папки. Убедитесь, что в списке указаны все нужные файлы.

Внизу выберите преобразовать данные. Откроется редактор Power Query со всеми файлами в папке и во вложенных папках.

Чтобы выбрать нужные файлы, отфильтруем столбцы, например Расширение или Путь к папке.

Чтобы объединить файлы в одну таблицу, выберите столбец Содержимое, содержащий каждый двоичный (обычно первый столбец), а затем выберите Главная > Объединить файлы. Появится диалоговое окно Объединение файлов.

Power Query анализирует пример файла (по умолчанию первый файл в списке), чтобы использовать правильный соединительщик и определить совпадающие столбцы.

Чтобы использовать другой файл для примера, выберите его в списке Образец файла.

При желании внизу выберите пропустить файлы с ошибкой s,чтобы исключить эти файлы из результата.

Существует несколько команд, которые можно выбрать, и каждая из них имеет разные цели.

Объединение и преобразование данных Чтобы объединить все файлы с запросом, а затем запустить редактор Power Query, выберите объединить> объединить и преобразовать данные.

Объединение и загрузка Чтобы отобразить диалоговое окно Образец файла, создайте запрос, а затем загрузите на таблицу, выберите объединить> Объединить и загрузить.

Объединение и загрузка в Чтобы отобразить диалоговое окно Образец файла, создайте запрос, а затем в диалоговом окне Импорт выберите объединить> Объединить и загрузить в.

Нагрузки Чтобы создать запрос с одним шагом, а затем загрузить на таблицу, выберите загрузить > загрузить.

Загрузить в Чтобы создать запрос одним шагом и отобразить диалоговое окно Импорт, выберите загрузить > Загрузить в.

Преобразование данныхЧтобы создать запрос с одним шагом и запустить редактор Power Query, выберите Преобразовать данные.

Тем не менее при объединения файлов в области Запросы в группе "Запросы-справки" создается несколько вспомогательных запросов.

Power Query создает запрос "Образец файла" на основе примера запроса.

Запрос функции Transform File (Файл преобразования) использует запрос Parameter1 для указания каждого файла (двоичного) в качестве входного в запрос "Образец файла". Этот запрос также создает столбец Содержимое, содержащий содержимое файла, и автоматически расширяет его, чтобы добавить данные столбца в результаты. Запросы "Преобразовать файл" и "Образец файла" связаны, поэтому изменения в запросе "Образец файла" отражаются в запросе "Преобразовать файл".

Запрос, содержащий окончательные результаты, находится в группе "Другие запросы". По умолчанию он называется папкой, из которого вы импортировали файлы.

Для дальнейшего изучения щелкните каждый запрос правой кнопкой мыши и выберите Изменить, чтобы изучить каждый шаг запроса и увидеть, как работают запросы на этапе.

Чтобы начать процесс объединения нескольких файлов, сначала поместите их все в одну папку.

Примечание: Поддерживаются файлы Excel и Access, текстовые файлы, а также файлы в форматах CSV, JSON и XML.

На вкладке Power Query выберите из файла > из папки.

Убедившись в том, что все нужные файлы присутствуют в списке, нажмите в столбце Содержимое кнопку Объединить двоичные данные.

Будет выполнен анализ каждого файла и определен правильный формат, например текстовый, Excel или JSON. В этом примере отображается список листов из первой книги Excel. Выберите нужный лист и нажмите кнопку ОК.

При преобразовании разных файлов автоматически создается запрос для консолидации данных из каждого файла и предварительного просмотра. Если результат вас устраивает, нажмите кнопку Закрыть & загрузить.

Когда процесс объединения двоичных файлов завершится, данные листов из списка будут консолидированы на одном листе.

Если исходные файлы данных изменятся, вы всегда сможете обновить импортируемые данные. Щелкните в любом месте диапазона данных, а затем перейдите в инструменты запросов > обновить. Кроме того, вы можете легко применить дополнительные шаги преобразования или извлечения, изменяя автоматически созданный запрос-образец, не беспокоясь об изменении или создании дополнительных шагов запроса функций. любые изменения запроса-образец автоматически создаются в связанном запросе функции.

Известные проблемы

Читайте также: