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

Средневзвешенное значение - это значение, которое учитывает важность или вес каждого значения. В этой статье вы узнаете, как использовать функции SUMPRODUCT и SUM в Excel по отдельности и как их объединить для вычисления средневзвешенного значения..

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

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

В следующем примере будет показано, как использовать функции SUMPRODUCT и SUM в Excel для вычисления средневзвешенного значения..

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

Для нашего примера, давайте посмотрим на экзамен студента и результаты экзаменов. Существует шесть тестов, каждый из которых оценивается в 5% от общей оценки, два экзамена, каждый из которых оценивается в 20% от общей оценки, и один итоговый экзамен, оцениваемый в 30% от общей оценки. Итоговая оценка учащегося будет средневзвешенной, и мы будем использовать функции SUMPRODUCT и SUM для ее вычисления..

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

Шаг первый: Рассчитать SUMPRODUCT

Сначала давайте посмотрим, как работает функция SUMPRODUCT. Начните с выбора ячейки, в которой вы хотите отобразить результат (в нашем примере это ячейка D13). Затем перейдите в меню «Формулы», выберите выпадающий список «Math & Trig», прокрутите вниз и нажмите на функцию «SUMPRODUCT»..

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

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

Затем с помощью поля «Массив2» выберите вес тестов и экзаменов. Для нас это в колонке D.

Нажмите «ОК», когда вы закончите.

Функция SUMPRODUCT умножает каждый результат на соответствующий вес, а затем возвращает сумму всех этих продуктов..

Шаг второй: рассчитать сумму

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

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

Для поля «Номер1» выберите все веса.

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

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

Шаг третий: объедините SUMPRODUCT и SUM для расчета средневзвешенного

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

= SUMPRODUCT (С3: С11, D3: D11) / СУММ (D3: D11)

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

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