Анализ контрольной работы и ВПР в Excel
Вставьте ведомость — получите успеваемость, качество знаний, средний балл, СОУ, выполнение по заданиям и список, кого повторить; книгу Excel с формулами и диаграммами и справку Word для завуча.
Для ВПР добавьте столбец «Отметка по журналу».
или файл .xlsx:
По предметам
Вопросы
Куда уходят фамилии учеников?
Никуда. При вставке ведомости фамилии заменяются номерами прямо в браузере — на сервер уходят только номера и баллы. Загруженный файл разбирается в памяти, сразу обезличивается и не сохраняется; книга и справка возвращаются вам и у нас не остаются. Нейросети в анализе не участвуют.
Как считаются показатели?
Успеваемость — доля работ без «2»; качество знаний — доля «4» и «5»; средний балл — среднее отметок; СОУ — степень обученности по В. П. Симонову (вес «5» — 100 %, «4» — 64 %, «3» — 36 %, «2» — 16 %). Все формулы видны в книге Excel, веса можно поменять на листе «Параметры».
Что нужно для ВПР?
Столбец «Отметка по журналу» (или «За четверть»): Формулник посчитает, сколько учеников подтвердили, понизили и повысили отметку, — как в форме анализа ВПР.
Какой должна быть ведомость?
Строка заголовков: ФИО, баллы по заданиям («1», «2»… или «Задание 1»), по желанию «Отметка» и строка «Макс» с максимальными баллами. Без отметки она выставится по шкале перевода баллов.
Как сделать анализ контрольной работы в Excel: формулы показателей и справка
Анализ контрольной в Excel держится на пяти числах: успеваемость (доля работ на «3», «4» и «5»), качество знаний (доля «4» и «5»), средний балл, СОУ по Симонову и процент выполнения каждого задания. Если отметки лежат в столбце H3:H27, качество знаний — =СЧЁТЕСЛИ(H3:H27;">=4")/СЧЁТ(H3:H27) в процентном формате.
Пример: писали 25 человек, «5» — 5, «4» — 9, «3» — 8, «2» — 3. Успеваемость 22/25 = 88 %, качество 14/25 = 56 %, средний балл 91/25 = 3,64, СОУ (5·100 + 9·64 + 8·36 + 3·16)/25 = 56,5 %.
По шагам
- Сделайте лист «Ведомость»: в строке 1 — номера заданий, в строке 2 — максимальный балл за каждое, с строки 3 — ученики. Фамилии можно заменить номерами: для анализа они не нужны.
- В столбце суммы посчитайте первичный балл ученика
=СУММ(C3:F3), рядом выставьте отметку по шкале перевода: пороги «5», «4», «3» держите в отдельных ячейкахL1:L3, а не внутри формулы. - Отметку получите формулой
=ЕСЛИ(G3>=$L$1;5;ЕСЛИ(G3>=$L$2;4;ЕСЛИ(G3>=$L$3;3;2)))и протяните вниз. - Под ведомостью посчитайте количество каждой отметки через
=СЧЁТЕСЛИ(H3:H27;5)и так далее — из этих четырёх чисел собираются все остальные показатели. - Для каждого задания найдите процент выполнения: средний балл по заданию, делённый на максимум, —
=СРЗНАЧ(C3:C27)/C$2. - Отметьте, кого подтянуть: в ячейке ученика по заданию
=ЕСЛИ(C3/C$2<0,5;"повторить";"")— меньше половины баллов за задание значит, что тему стоит повторить. - Постройте две диаграммы: круговую по отметкам и гистограмму по процентам выполнения заданий. Обе вставляются в справку как есть.
Если это ВПР и нужно сравнить с отметкой по журналу
Добавьте столбец I с отметкой за четверть. Подтвердили отметку — =СУММПРОИЗВ((H3:H27=I3:I27)*(H3:H27<>"")), понизили — та же формула со знаком <, повысили — со знаком >. Доли считаются делением на число писавших. Эти три строки есть в форме анализа ВПР, которую просят завучи.
Если кто-то отсутствовал
Оставьте у отсутствующего ячейку отметки пустой или впишите «н». СЧЁТ и СЧЁТЕСЛИ с числовым условием пропускают текст и пустоты, поэтому показатели посчитаются только по писавшим. Число отсутствующих — =СЧЁТЕСЛИ(H3:H27;"н").
Если завуч просит справку, а не таблицу
Обычная структура справки: цель и дата работы, класс, по списку и писали, таблица отметок с успеваемостью, качеством, СОУ и средним баллом, задания с самым низким процентом выполнения и типичные ошибки, выводы и что делать дальше (повторение тем, работа с конкретными учениками). Цифры в справку берите из книги, а не пересчитывайте вручную. Формулник по вставленной ведомости собирает и книгу с формулами, и справку Word — таблица и диаграммы в ней уже заполнены.
Показатели анализа и формулы
| Показатель | Как считается | Формула (отметки в H3:H27) |
|---|---|---|
| Успеваемость | («5» + «4» + «3») / писали | =СЧЁТЕСЛИ(H3:H27;">=3")/СЧЁТ(H3:H27) |
| Качество знаний | («5» + «4») / писали | =СЧЁТЕСЛИ(H3:H27;">=4")/СЧЁТ(H3:H27) |
| Средний балл | сумма отметок / писали | =СРЗНАЧ(H3:H27) |
| СОУ | (n5·100 + n4·64 + n3·36 + n2·16) / писали, % | =(СЧЁТЕСЛИ(H3:H27;5)*100+СЧЁТЕСЛИ(H3:H27;4)*64+СЧЁТЕСЛИ(H3:H27;3)*36+СЧЁТЕСЛИ(H3:H27;2)*16)/СЧЁТ(H3:H27) |
| Выполнение задания | средний балл за задание / максимум | =СРЗНАЧ(C3:C27)/C$2 |
Частые ошибки
- Отсутствующим ставят 0 или «2» — так падают успеваемость и средний балл. Пустая ячейка или «н» — и формулы считают только тех, кто писал.
- Качество знаний делят на число учеников по списку — делить нужно на число писавших — иначе показатель занижен на долю отсутствующих.
- Пороги перевода вписаны прямо в формулу отметки — при смене шкалы приходится править каждую строку. Держите пороги в ячейках и ссылайтесь на них со знаками
$. - Процент выполнения задания считают по числу решивших — для задания на 3 балла это теряет частичные решения. Делите средний балл на максимум — так видно и тех, кто решил наполовину.
Вопросы и ответы
Как посчитать качество знаний и успеваемость в процентах?
Качество — число «4» и «5», делённое на число писавших, успеваемость — число всех положительных отметок, делённое на то же число. Ячейке задайте процентный формат, умножать на 100 не нужно.
Что такое СОУ и откуда веса 100, 64, 36, 16?
Степень обученности учащихся по методике В. П. Симонова. Каждой отметке соответствует уровень обученности, вес — его доля в процентах; в полной шкале у «1» вес 7 %. СОУ 64 % — это уровень класса, где все написали на «4»; ориентир для своего класса школа задаёт сама.
Где взять шаблон анализа контрольной работы по математике в Excel?
Шаблон одинаков для любого предмета: меняются только число заданий, максимальные баллы и шкала перевода. Для математики обычно добавляют разбивку заданий на алгебру и геометрию — это две суммы по своим столбцам.
Как показать в анализе, какие задания провалены?
Отсортируйте строку процентов выполнения по возрастанию или включите условное форматирование: ниже 50 % — красным. В справку обычно выписывают 2–3 самых слабых задания с темой каждого.
Можно ли сделать анализ ВПР в том же файле?
Да, достаточно столбца с отметкой по журналу: из него считаются подтвердившие, понизившие и повысившие отметку.
- Справка Microsoft: функция СЧЁТЕСЛИ
- Симонов В. П. Педагогическая диагностика (методика расчёта степени обученности учащихся)
- Рособрнадзор: всероссийские проверочные работы