Чтобы узнать среднее арифметическое нескольких ячеек, не потребуется больших усилий. Достаточно использовать формулу =СРЗНАЧ. Но что если вес одного из значений больше, чем остальных? Например, в большом количестве школ тесты или задания могут иметь разную ценность. Чтобы в такой ситуации совершить правильные расчеты, необходимо вычислить взвешенное среднее.
Несмотря на отсутствие этой функции Excel, есть другая функция, которая делает большую часть действий за пользователя: =СУМПРОИЗВ.
Эта формула очень простая, и даже тот, кто ее раньше не применял, сможет действовать, подобно профессионалу. Этот метод определения взвешенного среднего может использоваться во всех версиях Excel и даже в Google таблицах.
Настройка таблицы
Чтобы вычислить взвешенное среднее, требуется хотя бы две колонки. В первой (то есть, столбец B в описанном нами примере) написано по всем заданиям или тестам. Во второй колонке (C в нашем случае) описаны весовые коэффициенты. Их величина напрямую влияет на оценку по конкретному заданию на общий результат теста.
Что такое вес? На первый взгляд, это процент от общей суммы. Но вообще все веса корректируют выставленный преподавателем балл на определенную величину вверх. Очень хорошо, что описываемая нами формула корректирует оценки вне зависимости от того, какие веса добавляются сверху.
Ввод формулы
Теперь, после того, как наша таблица была настроена, мы вставляем функцию в ячейку B10 (но можно использовать какую-угодно пустую ячейку). Как и с любой формулой, предварительно нужно написать знак =.
Для начала нужно ввести функцию =СУМПРОИЗВ и открыть скобку:
=СУМПРОИЗВ(
После этого необходимо добавить параметры в функцию. Сама она может иметь любое количество аргументов, но как правило используется два. В описываемом нами случае в качестве первого параметра будет диапазон значений B2:B9, то есть, ячейки, которые используются для указания баллов по заданиям и тестам.
=СУМПРОИЗВ(B2:B9
Вторым параметром служит диапазон C2;C9, покрывающий весовые значения. Для разделения всех аргументов нужно использовать запятую. Ну и наконец, после ввода значений необходимо поставить вторую скобку.
=СУМПРОИЗВ(B2:B9, C2:C9)
Далее добавляется вторая часть формулы, на которую разделить значение возвращенное функцией =СУМПРОИЗВ. Эта часть являет собой сумму всех значений. Позже мы разберем, почему это так важно.
Итак, чтобы ввести знак деления, необходимо написать знак /, а после этого ввести функцию =СУММ. Нам достаточно в качестве параметра функции прописать совокупность весов (C2:C9). Важно закрыть скобку после этого.
=СУММПРОИЗВ(B2:B9, C2:C9)/СУММ(C2:C9)
И это все! После нажатия на клавишу «Ввод» на клавиатуре, Excel автоматически определит взвешенное среднее. В описанном нами случае, окончательный балл – это 83.6.
Принцип работы формулы
Теперь давайте детально посмотрим на каждую часть этой формулы, чтобы увидеть, как она функционирует. Начнем с формулы СУММПРОИЗВ, которая умножает (то есть, находит производное) каждый балл на весовой коэффициент, после чего складывает все результаты. Проще говоря, она складывает все результаты умножения, которое проводилось перед этим. Так, для первого задания она умножает 85 на 5, а в случае с результатами теста — 83 на 25.
Если вы удивляетесь, почему необходимо умножать значения на первом месте, думайте следующим образом: баллы за задачи с наибольшим весом увеличиваются в большее число раз. Так, второе задание считается 5 раз, а заключительный экзамен – 45 раз. Поэтому финальный экзамен способен сильнее менять итоговый балл
Если сравнивать со стандартной функцией СРЗНАЧ, то последняя будет каждую позицию вводить в выборку только один раз, не обращая внимание на ее значимость.
Если вы хотите узнать, какие вычисления Excel выполняет на самом деле, то вычисления делаются следующие:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Хорошо, что не нужно писать такую длинную формулу, где можно запутаться с теми или иными элементами, потому что функция СУММПРОИЗВ делает это автоматически.
Если эту функцию выполнять саму по себе, то эта функция даст нам огромнейшее значение – 10450. И это далеко от среднего значения всех входящих значений, скорректированных на весовой коэффициент. И чтобы понять, какое же значение среднее, необходимо разделить формулу на сумму всех весов. Таким образом, получится значение 83,6.
Вторая часть формулы очень полезна, поскольку она позволяет программе автоматически корректировать результат. Также важно помнить, что весовые коэффициенты должны быть больше или равно 100%. Это может быть единица, 1,5, 15, 10,100. Просто при увеличении одного или нескольких весов происходит автоматическое деление второй части формулы на большее число. Поэтому ответ будет все равно правильный, какие бы изменения не вносились в таблицу. Более того, можно веса уменьшить, и все равно все будет работать правильно. Скажите, классно?
