Несколько дней назад прислали министерское письмо по мониторингу учебников. Сейчас школы во всю занимаются формированием заказа на учебники на новый учебный год. По предлагаемому же сейчас к заполнению мониторингу учебников школы должны в таблицах Excel на каждом из 11 листов соответствующих классам заполнить от 150 до 230 ячеек данными, которые ещё им нужно собрать и самим предварительно проанализировать и проверить.
Я решил несколько помочь в этой работе. Присланные файлы для мониторинга учебников я собрал в одной папке на диске С. Кроме этого я постарался определить, где находятся во всех файлах школы района, на каком месте, в какой строке. Оказалось, что строчки пляшут на 1-2 по разным файлам и листам. Названия школ вроде бы одинаковые. Предлагаемая программа с автозапуском макросов по Sub Auto_Open вызывает на экран форму со считанными в Listbox значениями школ и классов. Я опробовал и вариант с загрузкой учебников, но его реализация оказалась затруднена по причине безобразного заполнения названий учебников с применением механизма заполнения обычно или часто применяемых некоторыми в Word. Я так никогда не заполняю шапки ни в Word, ни тем более в Excel.
При выборе пользователем школы и класса происходит открытие одного из файлов одной из 3-х форм мониторинга, а файл выборки закрывается. Выбранная строка в данном классе может на 1-2 отличаться по описанной выше причине. От поиска по названию школы я пока отказался.
Программа настроена на школы нашего РУО. В исходном файле выборки можно изменить список школ. Для остановки выполнения программы можно запретить выполнение макросов или нажать Ctrl_Break (Вторая клавиша обычно соответствует кнопке Пауза). Для запуска программы надо соответственно разрешить макросы в Excel.
Скачайте архив и просто разархивируйте его на диск С. Файлы мониторинга в нём взяты из присланного письма.
Если предполагать в дальнейшем организацию автоматического сбора, то организовать его по номеру строки будет или невозможно, или надо для каждого класса предусматривать корректировку номера строки. Лучше пойти по пути приблизительного поиска указанной строки +-2-3 вверх или вниз с одновременной проверкой на заполненность строки во всех ячейках найденной строки. Но организовать сборку автоматом данным от школ будет более или менее возможно. При этом в присланных файлах должна стоять спереди школа из мониторинга, а впереди ещё и номер этой школы в виде 567_МБОУ….
Ссылка на архив 10 Мб. Запускаемый файл с макросами Выбор файла и школы для ввода данных в мониторинг по учебникам.xls.
Папка – !!!!!Форма Мониторинг Учебников 1-11 класс. Если вы не хотите использовать предлагаемый макрос, то воспользуйтесь возможностью Excel перехода на открытом активном листе в данную ячейку по клавише F5, а далее вводите адрес ячейки перехода, например b570.
04.03.2019
Сегодня я внёс изменения в файл Выбор файла и школы для ввода данных в мониторинг по учебникам.xls, добавив возможность формирования файлов на отсылку из выбранных Вами.
Для создания файлов на отсылку выберите точно так же Вашу школу и класс. Дополнительно надо выделить CheckBox и выбрать кнопку команды формирования под ним. Сколько файлов и классов Вы выберете, столько их и сформируется во вновь создаваемой папке Отослать. Имя каждой школы будет кратким.
05.03.2019
Сегодня проверил возможность на 5 школах собрать сведения по 5-9 классам в файл сбора данных. На всё было затрачено 57 сек., т.е 1 минута на 5 школ. Следовательно я смогу собрать мониторинг учебников 29 школ района где-то за 6 минут. С учетом 1-4 классов и 9-10 классов на весь сбор данных у меня уйдет около получаса. Время обработки практически не зависит от количества обрабатываемых листов, т.к. основное время уходит на открытие и закрытие заполненного файла школы.
12.03.2019
Проверил работу новой версии программы на 6 школах. При этом я обрабатываю за один раз в каждой школе все 3 таблицы по 11 классам. Выборка осуществляется в один файл Excel с разбивкой по классам в соответствии с местом конкретной школы в присланном списке школ. В файл сбора кроме листов 11 классов добавлен лист Школы со списком школ из присланного файла и номером строки школы в этом файле. Кроме этого есть соответствующий столбец с кратким именем школы. На втором добавленном листе Настройка находится список присылаемых файлов, а также список классов. Школы присылают свои файлы мониторинга учебников с именами, у которых впереди краткое имя школы. Программа по краткому имени школы определяет место школы в основном файле и просто переносит копированием нужную строку в файл сбора.
Я отказался от определения имен присланных файлов программным путем для ускорения отладки, а просто копирую имена присланных файлов в колону 5 файла сбора листа настройки. Для этого у меня есть надстройка Поиск файлов.По номеру классов в имени присылаемого файла определяется список классов для переноса данных.
Последний прогон показал, что на 18 обработанных файлов 6 школ мне понадобилось 192 секунды. На одну школу это составляет около полминуты, а на все 29 школ около 15 минут. Если выполнять консолидацию данных по диапазону для 4-го файла Мониторинг потребности, то на одну школу уходит около также 35 секунд.
Если же применить настройки приложения,
‘выключаем некоторые параметры для увеличения скорости обработки файла
With Application
.ScreenUpdating = False ‘отключение обновление экрана
.Calculation = xlCalculationManual ‘отключение пересчёт формул вручную
.EnableEvents = False ‘отключение событий
.DisplayAlerts = False ‘отключение предупреждающих сообщений
End With
то скорость консолидации возрастает в сотню раз, а скорость копирования строк только в 2 раза. Почему? Консолидацию я провожу через массивы, а копирование данных в строке обычными командами Excel – Copy и Paste.
Если применить также копирование данных через массивы, то скорость обработки несколько сократится конечно, но не намного. Всё дело в размерах файлов, числе строк и столбцов для переноса. Файлы мониторинга учебников очень большие. Они долго открываются и закрываются.
15.03.2019
В настоящее время данные практически со всех школ собраны по всем 11 классам в одном файле. Часть школ нули не проставляли, что вообще-то для работы в Excel правильно. Сегодня с утра я написал и проверил макрос переноса собранных мною данных по всем школам в 3 отдельных присланных файла для заполнения с одновременным заполнением нулями пустых ячеек и переименованием 3-х файлов для отсылки. Перенос данных на 3 отдельных файла с разбивкой по классам программно у меня заняло всего 35 секунд. Теперь осталось провести консолидацию данных по 4-му файлу потребности, что вообще-то элементарно. Останется лишь проверить, чтобы не здеть формулы в листе результата, т.к при консолидации всем листом, а не диапазонами формулы уничтожаются. Поэтому просто останется копировать диапазонами до формул.
Для желающих посмотреть как происходит вызов различных книг с активизацией нужных листов выкладываю макрос, написанный час назад сегодня. Этот макрос можно использовать как некоторый шаблон для переноса аналогичных данных. Я постарался вставит ряд комментариев и сделать понятными названия переменных.