В 2019 году все ждали появления результатов ВПР, но их сначала как и сами работы перенесли, а затем на сайте появилось что-то новое и непонятное, рассчитанное на обработку и анализ школами. Нормальных результатов ждали и после майских праздников, но их так и не оказалось. Самое главное, что школы так и не могут выяснить есть ли у них несоответствие или нет. Подготовка к обработке ВПР велась мной заранее, но в основном пришлось доводить макросы 2018 года, которые могут и не пригодиться сейчас. Ниже материалы и 2018 и 2019 года.
Начальный вариант от середины апреля 2018
Нашим методистам постоянно приходится проводить анализ выполнения проверочных работ по различным предметам. Таких работ становится всё больше. Какие выявляются проблемы у школ?
Самой неприятной является проблема несоответствия оценок по проверочным работам и оценками в журналах. Стандартно где-то 60 % оценок коррелируют с оценками в журналах, а плюс, минус 20% завышены или занижены. Отклонение может быть и выше. Начинаются проверки школ по этому поводу. Но я не об этом. А о самом подсчете баллов по заданиям и критериям.
Все оценки по школам по выполнению заданий можно взять на сайте, но их сначала придётся разнести по столбцам и, возможно, чуть преобразовать. В прилагаемом примере на 2-х листах по английскому языку и истории баллы по заданиям по школам уже разнесены. При анализе необходимо найти сумму баллов по каждому заданию, которых обычно бывает 15 или 20, в соответствии с критерием. Стандартно это можно сделать при помощи функции =СУММЕСЛИ. Эта функция имеет три параметра: диапазон критериев, критерий, диапазон выборки или поиска. Для меня нет трудностей в использовании данной функции. Осторожным надо быть лишь с типом ссылок, чтобы одну единственную формулу скопировать вправо по заданиям и вниз по критериям. Для других построение данной формулы может оказаться и проблематичным. Для такого случая я и написал вчера небольшую программу, которая запрашивает два диапазона: данных и критериев. На форме есть и скрытый элемент ListBox1 для выявления уникальных критериев. Программа помещает результаты всегда ниже последней не пустой строки. Программа обрабатывает всегда активный лист.
Ссылка на файл Excel с макросами.
11.02.2019. Проверил работу этого макроса. Он работает Надо выделить колонки C:T , содержащие оценки, а затем любые 7 ячеек столбца B, содержащего критерии. После этого в конце файла появятся 7 строк с итогами ниже последней заполненной строки.
Я постарался отдельные действия реализовать в отдельных макросах для удобства понимания работы программы.
P.S. Так как программе безразлично какие данные суммируются, то её можно применять для подсчета, например, распределения учащихся по полу или классу, суммы баллов по предмету и т.д. Специально для этого я часто в таблицах завожу специальное поле Количество и заполняю его единицами. Такая методика хороша и для применения в сводных таблицах. Так как диапазон критериев может быть любой, то используя программу, можно всегда получить уникальные записи, содержащие данный критерий, как в сводных таблицах.Возник ещё один неприятный момент. Исходные данные на сайте VPR.STATGRAD имеют совсем другую структуру данных. Чтобы воспользоваться этой версии программы сначала надо обработать исходные данные и подсчитать количество учащихся по каждому заданию, получившим N,0,1,2 и т.д баллов. При этом надо использовать функцию =СчетЕсли.
Второй вариант для программы анализа ВПР
Мне предоставили первоначальный материал с сайта Статграда, в котором критерии уже разбиты по колонкам. Анализ структуры строк на листах Excel довольно утомительный и многовариантен. Поэтому, чтобы упростить решение задачи, я пошёл по такому пути.
- В исходном файле Excel школы обычно отделены пустой строкой. Учитывая возможные ошибки, я решил сначала убрать все пустые строки, а потом после каждой школы вставить по одной строке.
- Данные исходного листа потом копируются на лист Количество участников, в котором и ведётся дальнейшая обработка. Она включает в себя раскраску нужных ячеек, поиск и вывод после каждой школы найденных критериев после их поиска и вставки нужного количества строк, нумерацию строк по школам и обозначение строки критерия в первой колонке, начальной строке и колонке поиска критериев.
- Все переменные для каждого модуля не связаны с предыдущими модулями для упрощения решения задачи. Поиск критериев и школ ведётся на каждом листе отдельно программно, а не через передачу параметров. Данные 1 листа копируются на лист Количество, а данные листа Количество после обработки копируются на лист Анализ. Макросы не вызывают друг друга, но это удобно для отладки каждого модуля. Школы для анализа обезличены. Некоторые проверки работоспособности макросов проведены, но могут быть, возможно, и ошибки.
- В файле несколько листов и макросов. Вызывать надо последовательно 3 первых макроса. Окончательный результат находится на листе Анализ. Сам анализ можно усовершенствовать, добавить какие-то вычисления, сверку оценок, нахождение процентов. Можно выйти и на построение графиков
Третий, но возможно , что и не последний вариант
Мне постепенно предоставляют больше информации по анализу ВПР, но добиться окончательно, что же всё таки желательно получить на выходе, какие ещё получить диаграммы или проверки, бывает затруднительно. Будем считать, что этот вариант более полный. Какие изменения внесены? Добавлены дополнительные листы. Добавлены макросы автоматического вызова макросов (automat), замены номеров школ их наименованиями, удаление модулей. Есть ограничения на число запусков, но Вы всегда можете это обойти или брать новый оригинал. Усложнять защиту я не стал. Алгоритм работы остался тот же. Первоначально удаляются на всякий случай пустые строки, ведётся анализ на школы и перенумеровка строк по каждому учреждению. Потом ищутся уникальные символы оценки заданий типа N, 0,1,2 и т.д. и подсчёт их числа. С учетом этого после каждой школы добавляется нужное количество пустых строк и проставляются найденные критерии оценки заданий. Осуществляется подсчет их количества по каждой школе и перенос их количества на нужный лист. Формулы не формируются, хотя я это и сделаю потом. Всё лишь считается. На последнем этапе ведётся анализ оценок и их расхождение с оценками по журналу. Появилась ещё одна проблема. Статград иногда не размещает оценки по журналу, поэтому на последнем этапе возможна ошибка. Начальная строка и столбец размещения оценок в файле можно изменить.
Вариант от 14 мая
Немного доработал программу Анализа ВПР.
- В данных со Статграда могут быть лишние школы, например из-за реорганизации в районе. Желательно, поэтому, при обработке оставить лишь школы из списка.
- В исходных данных колонка Отм. по журналу может быть, а может и отсутствовать. С этой целью проверяется строка 1 листа Исходные данные и запрашивается вставка столбца, если он отсутствует. В конце концов при анализе расхождений оценок по журналу и ВПР Вы можете проставить свои отметки позже, если их нет в данных Статграда. Но расчет отклонений программно всё равно проводится с выводом на отдельный лист.
- При подсчёте итогов по критериям дополнительно также создаётся формула СуммаЕсли на листе Анализ. Даже, если она будет по какой-то причине и не верна, то всё равно, её наличие позволит затем подправить формулу под нужный вариант. Это сделано с учётом проблем для некоторых пользователей создавать нужные формулы без ошибок, используя абсолютную и относительную адресацию ячеек и диапазонов.
- Ограничения остались те же. А промежуточные варианты я убрал, оставив только первый и последний.
- 21 мая я обработал 6 файлов с сайта СтатГрад – 3 из них 5 класс, а остальные 4 класс. В последнем случае колонка отметок по журналу была во всех трёх случаях. По 5 классу отметки были лишь в одном классе. Времени затратил от силы час, т.к. перед обработкой надо было перевести файлы в формат XLS из формата CSV (Я до сих пор пользуюсь офисом 2003). Кроме этого надо было список оценок разложить по столбцам и убрать один из символов, определяющих текстовой вид. В варианте ниже я нашёл пока лишь одну ошибку, связанную с переименованием школ. Последняя школа не изменяется. Ошибка простейшая и у себя я уже её поправил. Если есть пожелания, то жду примеры, пожелания и вопросы, если они есть на один из основных моих адресов coozr1@yandex.ru.
Вариант программы от 14 мая 2018 года.
22.05.2018
При преобразовании данных с сайта Статград версией Excel 2003 надо сначала данные считывать в формате CSV, указывая в качестве разделителя точку с запятой. Тогда обработка проходит верно и не остаётся нечитаемых символов. Сохраняем данные в формате XLS.
Затем в полученном файле столбец с оценками раскидываем по столбцам справа с заменой, используя Данные-Текст по столбцам. Остаётся заменить символ признака принадлежности к текстовой информации (удалит его), используя команду замены. Этот символ можно скопировать, а затем вставить. Но у этого символа есть числовой десятичный код равный 96. Его достаточно набрать и на клавиатуре при нажатой клавише Alt.
Эти предварительные действия также можно автоматизировать, используя команды VBA выбора файла из папки. Это просто ускорит всю работу.
Для того, чтобы не оставлять макросы после проведения анализа, надо просто выполнить макрос удаления. Он на всякий случай даёт 2 предупреждения.
2018.05.25
Для ускорения работы по Анализу данных ВПР имеет смысл в основную программу встроить модуль чтения данных из подготовленного файла формата XLS на лист Исходные данные основной программы. Проблема может возникнуть, если в качестве исходника использовать непосредственно файл текстового формата CSV, имеющийся в архивных файлах Статграда по каждому из предметов. Различные форумы утверждают о появлении всевозможных ошибок при программном переводе файлов из формата CSV в формат XLS. Я в этом убедился сам, поэтому и принял решение о том, что файлы из формата CSV в XLS надо преобразовывать непосредственно средствамиExcel, а следовательно, вручную.
А в программе по Анализу ВПР желательно на листе Анализ вставить в конце и итоговые строки для проверки правильности данных по столбцам.
2018.05.30
В последний вариант программы добавлен макрос копирования данных из подготовленного для анализа файла в формате Excel с сайта Статград. От работы непосредственно с файлом в формате CSV я отказался.
И дополнительный файл. В результате проводимых мониторингов на областном или федеральном уровне нам присылают, или мы получаем с сайта некоторый файл Excel с данными по присланным на мониторинг файлами отдельных школ. Часто методами обычной автофильтрации отобрать свой район бывает невозможно. ЭТО МОЖНО СДЕЛАТЬ ЛИШЬ ЧЕРЕЗ ПОСЛЕДОВАТЕЛЬНЫЙ ПОИСК, используя некоторые сокращения поисковых слов или выражений. Так случилось и несколько дней назад. Для целей такого отбора я написал эту программу (макрос). Лист Результат будет содержать результат поиска. Он самоочищается каждый раз при запуске макроса. Лист Поиск содержит выбранные поисковые слова, а Поиск1 все поисковые слова в виде списка. Колонку поиска можно отобрать. Во время поиска запускается автофильтрация. Все данные на каждом листе расположены с первой строки. Сами данные со второй строки.
Сегодня, 11 февраля 2019 года я подправил этот вариант и вывел сообщения в статусной строке. Иначе казалось, что программа долго работает или зависла. На самом деле, действительно, обработка ведётся не быстро. В Питоне это делается за секунды, а в Excel уже та же обработка ведётся минуты, но результат уже, правда, может быть легко отформатирован.
Обработка ВПР в 2019 году будет предоставляться на другом сайте и пока неизвестен формат предоставляемых данных. Возможно, что всё останется и по старому с новыми возможностями для пользователей.
Можно макросы запускать постепенно, но лучше запустить макрос Автомат. Он последовательно вызовет все макросы. Там есть и порядок вызова. Кто хочет посмотреть код программы, то он 123. При переходе с одного активного листа на другой я чаще всего использую код из 3-х строчек:
If ActiveSheet.name<>”Имя листа” Then
Sheets(“Имя листа”).Activate
End If ‘В этом случае наверняка не ошибёшься с листом с которым работаешь
11.02.2019
17.06.2019
На днях мне предоставили 3 файла ВПР по 3-м различным предметам и классам и сказали, что на сайте можно получить анализ по оценкам, но только нет сравнения отметок по ВПР и отм.по журналу. Хорошо бы получить отклонения или разночтения.
Я предложил это сделать просто.
- После столбца Отм. по журналу вставляем 4-е пустых столбца. Три из которых мы потом заполним. 4-ый нужно оставить пустым, чтобы разорвать таблицу по колонке.
- В шапку вводим три имени столбца: – Разница положительна, равна нулю, отрицательна
- В каждый новый столбец вводим простую формулу вида =Если(L2-M2)>0;1;0) и соответственно =0 и меньше 0.
- Копируем эти формулы вниз до конца файла
- Создаем итоги, подсчитывающие суммы по этим 3-м столбцам при смене школы – ОУ
- При нажатии на 2 в итогах мы видим все 3 отклонения по каждой школе и анализируем расхождения в оценке по ВПР и журналу.
Для сравнения оценок по ВПР и журналу я предлагаю несколько макросов в программе подсчёта числа отклонений в оценках.
Скопируйте свои данные на рабочий лист файла с макросами. Первый макрос ищет колонку Выполнение и вставляет 4 пустых колонки, а затем заполняет три из них значениями разницы в оценках. Второй макрос создаёт итоги по школам, а третий макрос возвращает всё к первоначальному виду.
Успехов Вам.