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