В настоящее время в Excel появилось такое мощное средство для сбора и обработки данных как Power Query.
Хорошие описания использования этого средства, как пример, есть у Николая Павлова. Во всяком случае первое знакомство с возможностями Power Query я получил из его видео. Теперь появилась и книга Николая. Есть и другие ресурсы и книги с примерами использования Power Query и его возможностями. На этом можно было бы и закончить. Но недавно я зарегистрировался на одном из сайтов и нашёл в среди обсуждаемых ранее тем тему по сбору данных. Вопросы о том, как это сделать и примеры кода для считывания файлов из папки на диске. (Я всегда любил название – директория от команды dir).
Далее я расскажу о своей работе по сбору данных, которые я проводил в течение лет десяти непрерывно. Не скажу, что я владею VBA в совершенстве. Я видел коды программ, в которых было очень тяжело разобраться с использованием в них тонкостей VBA и всех возможностей. Даже некоторые примеры из книги Джона Уокенбаха бывали тяжелы для разбора. До сих пор у меня есть большие трудности в понимании использования классов в VBA. Может поэтому я решил писать программы попроще для себя и понятнее.
Моя работа состояла в сборе отчетов от школ и детских садов и составлении сводных отчетов по различных видам деятельности или консолидации данных по всему району. Получаемых файлов могло быть до 100 по каждому отчету.
Во- первых. Сразу был запрет на документы Ворд. Хотя часть пользователей и руководство района и области продолжали присылать исходные формы в Word.
Во – вторых. Я готовил все формы в Excel и рассылал их адресатам, требуя присылать без изменения формы и с определенной структурой в имени файла, Если этого не выдерживалось, то приходилось самому изменять присланное под нужный вид. Со временем все что-то поняли, и ошибок стало минимум.
Как правило, все требуемые формы для отчета были одностраничными, Но встречались и многостраничные отчеты, как пример формы по статистике.
Если имеем одностраничный файл, то существует два возможных вида или способа сбора данных,
Первый. Сбор данных из каждого присланного файла на один общий лист. При этом обязательно наличие поля в файле с именем организации, приславшей данные.
Второй. Сбор данных из каждого присланного файла в один файл с множеством листов от каждой организации.
В первом случае, как правило, потом можно было использовать механизм сводных таблиц для проведения аналитики и получения отчетов, а во втором случае – консолидацию данных.
Если же присылаемые файлы были многостраничными, то, как правило, проводилась сразу консолидация данных через использование массивов по каждому листу из всех организаций с формированием многостраничного же файла Excel с уже просуммированными значениями.
Для облегчения работы все присылаемые файлы в Outlook сначала собирались в одну папку Outlook, а затем макросом считывались и сбрасывались на диск в нужную папку, где и формировался потом отчет. Так как работа по сбору данных была однотипной, то требовались минимальные изменения в стандартной, написанной мной программе сбора данных.
Такие разработки и примеры чтения файлов из нужной папки в Интернет всегда можно найти.
Больше всего мне нравился такой способ сбора данных.
- Определяются все имена файлов для последующей обработки.
- Первый файл считывается и на основе его формируется файл шаблона. Файл шаблона можно и просто скопировать и вставить в нужный лист. По шаблону ищется отдельно шапка и определяется число строк в ней. А затем определяется число строк в теле самого отчета.
- Пункт 2 можно заменить простой формой, указав строку начала и конца шапки, начала и конца данных в файле отчета или считывать строки до конца данных на листе. Здесь есть опасность получить пустые строки, т.к. Excel запоминает номер строки конца вводимых ранее данных. Тогда требованием к присылаемым данным является отсутствие пустых строк. Или же наличие у Вас программы удаления пустых строк в файле. В этом случае надо обязательно читать данные на листе не с начала данных, а с конечной строки данных на листе.
- Шапка копируется или первый считанный файл на лист сбора, а затее вниз добавляются данные из всех других файлов.
Если же собираются многостраничные файлы, то здесь фантазии могут быть разнообразными, как и возможности сбора данных и получения отчетов. В разделах Использование макросов и Обучение уже была информация на эту тему ранее.