Как рассчитать средневзвешенное значение в Excel

Средневзвешенное значение - это такое, которое учитывает важность или вес каждого значения. В этой статье будет показано, как использовать функции СУММПРОИЗВ и СУММ в Excel по отдельности и как объединить их для расчета средневзвешенного значения.

Что такое средневзвешенное значение?

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

В следующем примере будет показано, как использовать функции СУММПРОИЗВ и СУММ в Excel для расчета средневзвешенного значения.

Давайте посмотрим на пример

В нашем примере давайте посмотрим на результаты викторины и экзаменов учащегося. Есть шесть викторин, каждая на 5% от общей оценки, два экзамена на 20% от общей оценки и один заключительный экзамен на 30% от общей оценки. Итоговая оценка ученика будет средневзвешенной, и мы будем использовать функции СУММПРОИЗВ и СУММ для ее расчета.

Как вы можете видеть в нашей таблице ниже, мы уже присвоили относительные веса каждому тесту и экзамену в столбце D.

Шаг первый: рассчитайте СУММАРНЫЙ ПРОДУКТ

Сначала давайте посмотрим, как работает функция СУММПРОИЗВ. Начните с выбора ячейки, в которой вы хотите отобразить результат (в нашем примере это ячейка D13). Затем перейдите в меню «Формулы», выберите раскрывающийся список «Математика и триггеры», прокрутите вниз и нажмите функцию «СУММПОЛУЧИТЬ».

Появится окно «Аргументы функции».

В поле «Array1» выберите баллы учащегося. Здесь мы выбираем все ячейки с фактическими оценками в столбце C.

Затем используйте поле «Array2», чтобы выбрать веса тестов и экзаменов. Для нас они находятся в столбце D.

По завершении нажмите «ОК».

Функция СУММПРОИЗВ умножит каждую оценку на соответствующий вес, а затем вернет сумму всех этих продуктов.

Шаг второй: вычислите сумму

Теперь посмотрим, как работает функция СУММ. Выберите ячейку, в которой должны отображаться результаты (в нашем примере это ячейка D14). Затем перейдите в меню «Формулы», выберите раскрывающийся список «Математика и триггеры», прокрутите вниз и нажмите функцию «СУММ».

Появится окно «Аргументы функции».

В поле «Число1» выберите все веса.

Нажмите «ОК».

Функция СУММ сложит все значения вместе.

Шаг третий: объедините СУММПРОИЗВ и СУММ для вычисления средневзвешенного значения

Теперь мы можем объединить две функции, чтобы определить итоговую оценку учащегося на основе их оценок и веса каждой оценки. Выберите ячейку, в которую должно помещаться средневзвешенное значение (для нас это ячейка D15), а затем введите следующую формулу на панели функций.

= СУММПРОИЗВ (C3: C11, D3: D11) / СУММ (D3: D11)

После ввода формулы нажмите «Enter», чтобы просмотреть средневзвешенное значение.

И вот оно. Это довольно простой пример, но он хорошо показывает, как работают средневзвешенные значения.