Сбор данных из файлов excel в заданной папке
Некоторым пользователям приложения Microsoft Excel приходится решать задачу сбора данных из разных рабочих книг. Кому-то для формирования месячных, квартальных или годовых отчетов, а кому-то для составления реестров или баланса. При этом многократно повторяются такие операции как открытие файла, выделение определенного диапазона, копирование значений выделенного диапазона, закрытие файла, вставка скопированных значений на отдельный итоговый лист. Такие действия пользователей можно и нужно автоматизировать, ускоряя консолидацию данных и делая свою работу более эффективной.
Сбор данных из различных книг Excel на один лист в два этапа
В более ранних публикациях рассматривались инструменты, предназначенные для решения других задач, но при помощи которых можно собрать данные из разных файлов Excel на итоговый лист в два этапа.
Далее речь пойдет о том, как проделать все тоже самое, но в одно действие.
Сбор данных из нескольких рабочих книг Excel на отдельный лист
Для быстрого объединения данных, расположенных в разных рабочих книгах можно использовать надстройку для Excel.
Надстройка позволяет копировать заданные пользователем диапазоны данных на всех заданных листах всех выбранных рабочих книг и вставлять скопированные данные на новый лист активной рабочей книги. При работе с этой надстройкой пользователь имеет возможность:
1) Выбирать нужные рабочие книги для последующей обработки;
2) Осуществлять выбор листов по именам, по номерам, по заданному значению в ячейках выделенного диапазона;
3) Задавать диапазоны ячеек для копирования;
а) Используемые диапазоны - это диапазоны, начинающиеся с первой используемой ячейки и заканчивающиеся последней используемой ячейкой;
б) Предварительно выделенные диапазоны на каждом листе - диапазоны, выделенные пользователем самостоятельно, исходя из его потребностей по консолидации данных;
в) Одноименные диапазоны - диапазоны с одинаковым адресом на каждом листе, указанным на активном рабочем листе;
г) Выборочные диапазоны - диапазоны ячеек, начинающиеся с выбранной ячейки и заканчивающиеся:
- концом листа (последней используемой ячейкой);
- последней заполненной ячейкой в заданном столбце;
- последней заполненной ячейкой в заданной строке;
д) Именованные диапазоны.
4) При вставке данных на итоговый лист заменять формулы результатами их вычислений (при отсутствии объединенных ячеек);
5) При сборе данных на итоговый лист вставлять связи, при этом изменения значений в исходных диапазонах ячеек будут отображаться на итоговом листе;
6) Подставлять имена листов перед вставляемыми на итоговый лист диапазонами;
7) Подставлять имена рабочих книг перед вставляемыми на итоговый лист диапазонами;
8) Выбирать вертикальное либо горизонтальное размещение данных на листе с итогами;
9) Сохранять все настройки диалогового окна для последующих сеансов работы.
*В зависимости от выбранной опции диапазоны будут располагаться один ниже другого (вертикальное расположение), либо один правее другого (горизонтальное расположение).
После установки надстройки останется лишь запустить программу нажатием одной кнопки из главного меню Excel или с ленты, в зависимости от версии Excel и в диалоговом окне выбрать необходимые параметры для сбора информации.
В случае, если итоговый лист содержит ненужные пустые строки, можно провести их автоматическое удаление при помощи надстройки для выборочного удаления строк.
Этот макрос предназначен для сбора (загрузки) информации из файлов Excel, расположенных в одной папке.
Для работы этого макроса, помимо него самого, вам понадобится добавить в свой файл:
- функцию FilenamesCollection для получения списка файлов в папке
- функцию GetFolder для вывода диалогового окна выбора папки с запоминанием выбранной папки
- прогресс-бар для отображения процесса обработки файлов (модуль класса и форму)
Если при тестировании макроса у вас возникает ошибка, что не найдена та или иная функция,
— проверьте, все ли необходимые компоненты (которые перечислены выше) вы добавили в свой файл.
Этот макрос я публикую прежде всего для себя (поскольку использую этот код чуть ли ни в каждой третьей своей программе),
поэтому я не буду помогать вам в настройке этого макроса, если у вас он вдруг не заработает.
Макрос при запуске выдает диалоговое окно для выбора папки, в которой расположены обрабатываемые файлы,
после чего открывает каждый из файлов, считывает из него данные, помещает их в текущую книгу (из которой запущен макрос),
и закрывает обработанный файл без сохранения изменений.
После того, как очередной файл обработан, он перемещается во вторую папку («архив»).
Во вложении - файл со всеми необходимыми макросами для сбора данных из других файлов Excel
Комментарии
Проблемма решилась самостоятельно, нужно было всего лишь переоткрыть файл для применения изменений формата.
Оформляйте заказ на сайте, - сделаем.
Уважаемы гуру VBA помогите новичку не провалить задание.
Дело в том, что с разных отделов ( около 40) мне шлют заполненную таблицу Excel, универсальную для всех.
Разница лишь в цифрах, которые туда заносит каждый отдел исходя из своих показателей. Мне нужно создать сводную, одну таблицу, которая бы суммировала значения всех отделов. Неизменными остаются -шапка таблицы и столбец с названием показателей. Помогите создать макрос.
Спасибо, очень помог макрос после небольшой доработки!
Доброго времени суток!
Скачал Ваш файл с макросами. Как теперь добавить необходимую мне информацию из книг, откуда идет сбор информации, в эту книгу?
Спасибо за ответ.
Иван, оформляйте заказ, прикрепляйте к нему примеры файлов, и подробно описывайте, что и как должно работать.
Добрый день. Вот такая есть у меня задачка, в Excel я чайник. Помогите решить ее. Суть в следующем. Есть некое количество файлов Excel в папке, каждый день разное (от 100 до нескольких тысяч), нужен вывод названия каждого файла в общую таблица, строка названия файла в графе с подсчетом строк в каждом фале, исключая шапку, общие число файлов.И желательно интеграция в word. Как прикрепить таблицы для примера?
нарисовал в коде вот так:
'If (sh.Range("c45") = 1) Then
'shd.Range("f" & shd.Rows.Count).End(xlUp).Offset(1).Resize(1, 1).Value = Array(sh.Range("e45"))
'End If 'Направления использования - командировочные
'If (sh.Range("c47") = 1) Then
'shd.Range("f" & shd.Rows.Count).End(xlUp).Offset(1).Resize(1, 1).Value = Array(sh.Range("e47"))
'End If 'Направления использования - хозрасходы
'If (sh.Range("c49") = 1) Then
'shd.Range("f" & shd.Rows.Count).End(xlUp).Offset(1).Resize(1, 1).Value = Array(sh.Range("e49"))
'End If 'Направления использования - внесение средств
'If (sh.Range("c51") = 1) Then
'shd.Range("f" & shd.Rows.Count).End(xlUp).Offset(1).Resize(1, 1).Value = Array(sh.Range("e51"))
'End If 'Направления использования - Иное
Добрый день. Стоит задача собирать данные из нескольких файлов с одинаковой структурой в один, имеющий аналогичную структуру. Имена файлов имеют одинаковое правило - 2016_07_ОП_*наименование*, то есть год, месяц, ОП-название формы, далее идет наименование отдела. Собирать данные нужно с конкретного листа и вставлять в файл на лист, с таким же наименованием (именовать лист нужно автоматически). Так же в сборочный файл нужно добавить две колонки - Год и Месяц. Брать данные для их заполнения нужно из имен файлов, то есть в данном случае всем скопированным строкам нужно поставить год - 2016 и месяц - 07.
Есть вот такой макрос, найденный на этом форуме (Извините за неудобства. Вставляю со всеми отступами, но они почему-то пропадают):
Сбор данных разных файлов, рассортированных по трем папкам
Доброго времени суток! Очень прошу всех помочь существует одна довольно заковыристая задачка.
Сбор информации с разных файлов
Кто может помочь в написании не сложного (а для меня очень сложного :) ) макроса. Суть в.
Сбор данных из разных книг
Добрый день! Помогите решить вопрос: Есть папка с 100+ файлами, там примерно одни и те же данные.
Из постановки задачи непонятно с какого именно конкретного листа брать данные, его имя или позиция.
Непонятно что Вы имеете ввиду под словом вставлять - заменять полностью старые данные или добавлять снизу к старым данным.
Укажите номера колонок для добавления года и месяца .
Из постановки задачи непонятно с какого именно конкретного листа брать данные, его имя или позиция - имя листа "Бюджет расходов и платежей"
Непонятно что Вы имеете ввиду под словом вставлять - заменять полностью старые данные или добавлять снизу к старым данным - может не до конца корректно изъясняюсь. попробую пояснить: взяли первый файл из папки, в нем взяли лист "Бюджет расходов и платежей", с него перенесли все данные в сводный файл, далее взяли следующий файл, аналогичный лист, данные добавляем под перенесенными ранее в сводный файл.
Укажите номера колонок для добавления года и месяца - год 12, месяц 13.
Так как не видно образца, то просто добавляя данные я добавлю и лишние шапки таблицы в середину данных.
По идее макрос, который я привел выше, берет только один раз шапку, а дальше только данные. Или это не так?
Вложил форму. Я удалил из нее лишние листы, оставил только тот, из которого нужно копировать.
В сводном нужно создать лист с таким же именем и переносить в него.
Решение
Огромное спасибо. Все работает.
Добавлено через 19 часов 51 минуту
Добрый день. С исполнителями оказалось все сложнее)
Задача немного изменилась.
1) Нужно чтобы собирались данные в открытый файл, а не чтобы он создавался. С этим я вроде бы справился, исключив эту строку "Workbooks.Add"
2) Помимо этой нужна еще следующая модификация макроса: файл, в который собираются данные имеет следующее имя (например) 2016_07_HR.xlsx Необходимо, чтобы этот файл обращался к папке с файлами и собирал из них только те строки, в которых в колонке А стоит HR.
3) Нужен обратный макрос, который разбивает сборочный файл на многие по следующим признакам:
- имя файлов содержит часть имени файла, который разбивается в части года и месяца, то есть 2016_07_
- разбивка идет по колонке B. То есть данные с общем значением в колонке B заносятся в отдельный файл.
- Остальная часть имени файла - значение колонки B. То есть если, к примеру, там стоит Екатеринбург, то создается файл 2016_07_Екатеринбург.xlsx и в него переносятся все строки из сборочного, со значением Екатеринбург.
- В итоге получается столько файлов, сколько уникальных значений в колонке B. Структура в них аналогична сборочному.
- Пользователь открывает файл, который нужно разбить, макрос будет в нем и выбирает папку в которой нужно создать файлы.
На примере файлов бюджетов покажу как можно собирать данные со всех этих файлов в одну итоговую таблицу и просуммировать все присланные данные по статьям из каждой таблицы.
Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?
Модель агрегации файлов.zip (53,5 KiB, 1 206 скачиваний)
Видеоинструкция:
Для ведения бюджета применяется таблица такого вида:
Сама таблица преобразована заранее в так называемую "умную" таблицу: выделяем таблицу -вкладка Вставка (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 таблице в любой ячейке щелкнуть правой кнопкой мыши и выбрать Обновить:
Все файлы в папке будут просмотрены, преобразованы и просуммированы.
Power Query умеет подключаться к разным источникам. Далее рассмотрим, как получить данные из книги Excel.
Таблицы Excel
Лучше всего данные хранить в таблице Excel, это самый удобный и распространенный источник для Power Query. На ленте даже есть специальная кнопка.
Чтобы загрузить таблицу в редактор Power Query, достаточно выделить любую ее ячейку и нажать Данные → Получить и преобразовать данные → Из таблицы/диапазона.
Примечание. В вашей версии Excel расположение кнопок и их названия могут отличаться.
Если то же самое проделать с обычным диапазоном, то Excel вначале преобразует диапазон в таблицу Excel, а потом запустит Power Query.
Запросу присваивается имя таблицы Excel, которая является источником данных. Поэтому желательно сразу дать таблице говорящее название. Не обязательно, конечно, но желательно. В противном случае рекомендуется переименовать сам запрос, чтобы затем его можно было легко найти среди других запросов книги.
Данные находятся в Power Query. Новые значения, внесенные в исходную таблицу, автоматически попадут в запрос после его обновления. Далее в редакторе Power Query делают обработку данных и выгружают либо в виде таблицы Excel, либо оставляют в памяти Excel в виде подключения.
Именованный диапазон Excel
Источником для Power Query может быть не только таблица Excel. Например, вы получили красивый отформатированный отчет и не хотите вносить в него изменения. Тогда нужно использовать именованный диапазон. Самый простой способ создать именованный диапазон – это выделить область на листе и ввести название в поле Имя.
Либо выполнить команду Формулы → Определенные имена → Присвоить имя. В Excel будет создан новый объект, к которому можно обращаться, например, в формулах. Диапазон виден в Диспетчере имен.
Здесь перечислены все именованные диапазоны, формулы и таблицы. Среди них есть и только что созданный Отчет.
Теперь можно стать на любую ячейку внутри именованного диапазона (или выбрать его из выпадающего списка в поле Имя) и вызвать ту же команду: Данные → Получить и преобразовать данные → Из таблицы/диапазона. Произойдет загрузка данных в Power Query.
Такой способ позволяет «не портить» исходные данные. Но у него есть и очевидный недостаток: новые строки, которые выйдут за пределы именованного диапазона, не попадут в запрос.
Динамический именованный диапазон Excel
Решить данную проблему можно, создав динамический именованный диапазон. Это такой диапазон, который задается формулой и автоматически расширяется до последней заполненной ячейки.
Внести статичное имя в поле Имя на этот раз не получится. Поэтому заходим в Формулы → Определенные имена → Задать имя (или нажимаем Создать в Диспетчере имен), указываем название будущего динамического диапазона ДинамОтчет и внизу вместо ссылки записываем формулу:
Ко всем ссылкам этой формулы Excel еще автоматически добавит название листа.
Смысл формулы следующий. Верхняя левая ячейка диапазона фиксируется ($A$2), а правая нижняя определяется формулой, которая возвращает адрес последней заполненной строки в столбце B.
Но не все так просто. Excel видит это имя лишь как формулу, а не диапазон. Как же его увидит Power Query? Делаем ход конем.
Создаем пустой запрос Power Query Данные → Получить и преобразовать данные → Получить данные → Из других источников → Пустой запрос. Открывается пустой запрос, где в строке формул нужно ввести:
После ввода формулы (нажатием Enter) Power Query обратится к текущей книге и выведет все объекты, среди которых есть и наш динамический диапазон ДинамОтчет.
Название запроса не подхватывается, поэтому придется изменить самостоятельно.
Чтобы извлечь содержимое объекта, в этой же строке правой кнопкой мыши кликаем по Table, далее выбираем Детализация.
Power Query разворачивает таблицу и даже делает некоторые шаги обработки: повышает заголовки и задает нужный формат для столбцов.
Теперь в запрос будут попадать новые строки, несмотря на то, что исходные данные не являются таблицей Excel.
Вот такие приемы импорта данных в Power Query из книги Excel. Самый распространенный из них – это импорт из таблицы Excel. Тем не менее, в случае необходимости можно прибегнуть к альтернативам, создав именованный или динамический именованный диапазон.
Консолидация данных из разных таблиц Excel
Одна из насущных задач, с которыми сталкиваются пользователи, – консолидация данных. Под консолидацией понимается объединение нескольких таблиц в одну. До появления Power Query это была довольно трудоемкая операция, особенно, если процесс требовал автоматизации. Хотя в эксель есть специальная команда Данные → Работа с данными → Консолидация, пользоваться ей не удобно. Мне, по крайней мере. Появление Power Query в корне изменило представление о том, как нужно объединять таблицы.
Рассмотрим пример. В некоторый файл каждый месяц вносится отчет о продажах в формате таблицы Excel. Каждая таблица при этом имеет соответствующее название: Январь_2018, Февраль_2018 и т.д. Необходимо объединить все таблицы книги в одну. Как бы скопировать и вставить одну под другой, создав при этом дополнительный столбец, указывающий, к какой таблице принадлежит конкретная строка. Задача не одноразовая, а с заделом на будущее, поэтому нужно предусмотреть появление в этом файле новых таблиц.
Процесс начинается с запуска пустого запроса: Данные → Получить и преобразовать данные → Создать запрос → Из других источников → Пустой запрос
Затем в строке формул вводим знакомую команду
Power Query показывает все таблицы в текущей книге.
Их нужно развернуть кнопкой с двумя стрелками в названии поля Content (на скриншоте ниже выделено красным кружком).
Если есть лишние столбцы, то их можно не выводить, сняв соответствующую галку. Также лучше убрать галку напротив опции Использовать исходное имя столбца как префикс. Нажимаем Ok.
Все таблицы находятся на одном листе, а рядом колонка с названием источника, откуда взята каждая строка.
Данные загружены. Можно приступать к их обработке. Ограничимся преобразованием названий таблиц в настоящую дату, чтобы затем использовать для сведения данных по месяцам.
Визуально мы наблюдаем и месяц, и год. Но Power Query такое название воспринимает, как текст. Поэтому делаем следующее.
Удалим нижнее подчеркивание. Правой кнопкой мыши по названию столбца Name → Замена значений.
В следующем окне настроек указываем, что меняем _ на пусто, то есть в нижнем поле ничего не указываем.
Подчеркивание удаляется из названия.
Поиск и замена здесь работает так же, как и в обычном Excel.
Далее запускаем команду Преобразование → Столбец «Дата и время» → Дата → Выполнить анализ.
Power Query распознает дату и меняет формат колонки. Мы также переименовываем столбец на Период.
Полученную таблицу можно использовать для анализа данных. Выгрузим ее на лист Excel.
Главная → Закрыть и загрузить.
Но что-то пошло не так. Во-первых, внизу таблицы пустая строка; во-вторых, при выгрузке произошла одна ошибка. Обновим запрос (справа от названия запроса значок обновления).
Что-то еще больше пошло не так. Даты исчезли, снизу таблицы добавились новые строки, а количество ошибок уже 19. Спокойствие, только спокойствие! Дело вот в чем.
Помните, на первом шаге мы получили все таблицы из файла? Так ведь и выгруженная таблица – это тоже таблица! Получается, Power Query взял 3 исходных таблицы, обработал, выгрузил на лист Excel и на следующем круге видит уже 4 таблицы!
При повторном обновлении запрос захватывает их все, а т.к. таблица выхода имеет другую структуру, то возникают ошибки.
Короче, из запроса нужно исключить таблицу, которая получается на выходе (Запрос1). Есть разные подходы, самый простой – это добавить шаг фильтрации. Выделяем в правой панели первый шаг Источник, открываем фильтр в колонке с названиями, снимаем галку с таблицы Запрос1 → Ok.
Снова выгружаем таблицу в Excel и на этот раз все в порядке.
Сделаем с помощью сводной таблицы маленький отчет по месяцам.
Прошло время, и в файл добавили новую таблицу с продажами за апрель.
Требуется обновить сводный отчет. Представьте на минуту, как это происходит в обычном Эксель: таблица копируется в самый низ общего источника, продлевается колонка с датой, изменяется диапазон для сводной таблицы, обновляется весь отчет.
А вот, как это выглядит при использовании Power Query.
Достаточно два раза нажать кнопку Обновить все (первый раз – для обновления запроса, второй – для сводной таблицы).
На добавление в отчет новых данных вместе с их обработкой потребовалось несколько секунд.
Вот за это мы так любим Power Query.
Читайте также: