СУММПРОИЗВ Excel: как рассчитать оценку учебной работы по весам критериев

Сначала согласуйте шкалу и смысл весов

Возьмём условную итоговую работу курса: участник готовит инструкцию для коллеги. Оценим точность действий, полноту условий, проверяемость результата и читаемость. Для каждого критерия заранее описаны уровни от 0 до 4. Если сами уровни ещё не согласованы, начните с критериев оценки учебной работы, а затем переходите к расчёту.

В нашем примере веса равны 40, 30, 20 и 10. Это условные относительные величины, выбранные редакцией для упражнения. Они не являются универсальными рекомендациями для курсов. Команда должна объяснить, почему один критерий влияет на результат сильнее другого, и показать правила ученикам до выполнения задания.

Все баллы относятся к одной шкале. Нельзя без отдельного преобразования смешать оценку от 0 до 4, число найденных ошибок и процент выполненных действий. Получившееся число будет точным арифметически, но его учебный смысл останется неопределённым.

Подготовьте небольшой контрольный набор

В A1:C1 напишите «Критерий», «Балл» и «Вес». В строках со второй по пятую разместите четыре критерия. В B2:B5 внесите 3, 4, 2 и 4; в C2:C5 — 40, 30, 20 и 10. Веса вводите обычными числами: не смешивайте в одном наборе 40 и 30%, поскольку это разные числовые значения.

Сначала посчитайте вручную: 3 × 40 = 120; 4 × 30 = 120; 2 × 20 = 40; 4 × 10 = 40. Сумма произведений равна 320, сумма весов — 100. Деление даёт 3,2 балла по четырёхбалльной шкале. Если нужен процент максимума, 3,2 / 4 = 80%.

Сохраните этот набор как контрольный. Когда таблица станет больше, четыре заранее проверенных значения помогут отличить ошибку формулы от спорного решения проверяющего. Все данные здесь придуманы для расчёта, а не взяты из результатов реальной группы.

Запишите формулу и сравните результат

В отдельной ячейке введите =СУММПРОИЗВ(B2:B5;C2:C5)/СУММ(C2:C5). Для английского имени функции и разделителя-запятой: =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5). Синтаксис имени и разделителя зависит от установки Excel.

Документация Microsoft о SUMPRODUCT описывает сумму попарных произведений соответствующих элементов диапазонов. Их размеры должны совпадать. Деление на сумму весов превращает такую сумму во взвешенное среднее; этот подход также показан в руководстве Microsoft по вычислению среднего.

Сверьте результат с ручными 3,2. Затем сравните с обычным средним четырёх баллов: 3,25. Разница ожидаема, потому что в обычном среднем критерии получают одинаковое влияние. Ни одна из этих формул сама по себе не определяет, какая система оценки подходит вашей учебной задаче.

Проверьте пустые значения и границы

Пустой балл означает отсутствие решения проверяющего, а ноль — принятое решение по описанному уровню. Для рабочего процесса в этом примере выберем правило: пока хотя бы один критерий не оценён, итог считается предварительным и не выдаётся ученику как завершённая оценка.

Это существенно, потому что SUMPRODUCT трактует нечисловые элементы массива как нули. Случайный текст в ячейке или незаполненный балл не должен незаметно превращаться в доказательство невыполнения критерия. Перед расчётом проверьте типы значений, заполненность и границы шкалы. Для организации ввода пригодится проверка данных Excel.

В испытательной копии задайте все баллы равными 4: итог должен быть 4. При четырёх нулях получится 0. Замените веса 40, 30, 20, 10 на 4, 3, 2, 1: результат останется 3,2. Если сумма весов равна нулю, среднее по такой формуле не определено; сначала исправьте набор весов.

Отделите итоговый балл от обязательных требований

Высокий средний результат может скрыть невыполненное обязательное условие. Например, инструкция хорошо оформлена, но не содержит проверки результата действия. Если без этой проверки работа непригодна для использования, заранее определите отдельное требование к приёмке и сообщите его участникам.

Не добавляйте такое условие задним числом после получения неудобного балла. В карточке задания можно заранее разделить два решения: расчёт оценки по критериям и допуск работы к завершению. Тогда куратор объясняет конкретное основание доработки, а не пытается подобрать веса так, чтобы получить желаемый итог.

Проверьте систему на двух контрастных вымышленных работах. У первой сильное оформление и слабая точность, у второй — обратная картина. Сравните не только числа, но и действия, которые вы предложите авторам. Если обратная связь не согласуется с результатом расчёта, пересмотрите правила до следующего потока.

Передайте расчёт с понятным объяснением

Сохраните названия критериев, описания уровней, веса и контрольный пример рядом с формулой. Другой куратор должен восстановить путь от четырёх баллов к итогу без расспросов о скрытых договорённостях. При изменении рубрики укажите, для каких работ действует новая версия.

В материалах курса Umhub объясните ученику, что означает итог 3,2 и какие конкретные признаки требуют улучшения. Таблица может служить отдельным рабочим инструментом проверяющего; наличие формулы не заменяет содержательного комментария к работе.

Расчёт готов к применению, когда ручной и автоматический результаты совпадают, пустые оценки не принимаются за завершённые, а веса и обязательные требования известны до начала выполнения задания.