Расчет средневзвешенного значения в Excel: 2 примера

Всем привет! Сегодня мы будем разбирать более сложную тему чем обычно, хотя я попробовал разобрать её как можно подробнее и понятнее на простом примере. Мы рассмотрим формулу средневзвешенного значения в Excel:

=СУММПРОИЗВ(Массив_значений;Массив_веса_значений)/СУММ(Массив_веса_значений)

Обычно средневзвешенное значение используют для более точного определения какого-то среднестатистического значения по выбранным весам. Не будем вдаваться в подробности определения и лучше все рассмотрим на конкретных примерах, чтобы вам было понятно.

Пример 1: Оценки

Представим себе ситуацию, что нам нужно выбрать 2-х лучших студентов по оценкам для поступления на наш факультет по программированию. У нас есть таблица с именами и оценками.

В 11 строке у нас расположено среднеарифметическое по всем оценкам. Формула в Excel:

=СРЗНАЧ(B2:B10)

Вроде сразу же на ум приходит Петр, у которого средний балл 4,3, но есть нюанс – у него по Алгебре и Информатике (двум профильным предметам нашего факультета) невысокие оценки.

Что же нам делать в таком случае? Для этого мы будем высчитывать средневзвешенное значение по весу каждого предмета. Для начала нам нужно в табличку добавить вес предмета.

Я на глаз добавил вес для факультета программирования. У нас Информатика и Алгебра имеют самый высокий вес в 9 баллов. Физ-ра, Биология и Пение – всего 1.

Средневзвешенное значение по предметам, например, для Дениса будет выглядеть так:

=( 5 х 9 + 4 х 8 + 5 х 3 + 4 х 6 + 5 х 4 + 4 х 1+ 3 х 1 + 3 х 1 + 4 х 9) / (9 + 8 + 3 + 6 + 4 + 1 + 1 + 1 + 9)

То есть нам нужно умножить каждое значение между двумя столбцами:

Оценки х Вес оценок

Потом все произведения сложить и поделить на сумму всех весов. Таким образом получается рассчитать средневзвешенное значение. Давайте посмотрим, как это реализовать в Excel. И посмотрим на конечную формулу (для Дениса):

=СУММПРОИЗВ(B2:B10;$H$2:$H$10)/СУММ($H$2:$H$10)

Рассмотрим подробно первую функцию:

СУММАПРОИЗВ(Массив_1;Массив_2;Массив_3;…)

Она как раз и делает перемножение по столбцам, а потом делает сумму этих произведений. Ну и дальше достаточно поделить это все ну сумму всех весов:

СУММ($H$2:$H$10)

Обратите внимание, что мы используем знак доллара ($), чтобы закрепить адреса ячеек веса, так как мы должны использовать его для всех остальных участников, чтобы маркер автозаполнения все сделал правильно при копировании в соседние столбцы по горизонтали.

Пример 2: Товары

Рассмотрим еще один пример для закрепления расчета средневзвешенного значения в Excel. Пример классический, но не такой интуитивно понятный, как прошлый. Представим себе, что у нас есть таблица товаров с ценой и количеством. Товары продавались в разное время по разной цене. И продано было разное количество этих товаров. Например, апельсины продавались по 200, 300, и 250 в количестве 125, 23 и 143 соответственно.

Как вариант можно посчитать среднее арифметическое по цене:

=СРЗНАЧ(B2:B4)

Но для бизнеса данное среднее имеет большую погрешность, ведь мы продавали по определенным ценам разное количество товара. Поэтому в данном примере стоит использовать формулу средневзвешенного.

Что мы будем использовать в качестве веса для цены товара? Обычно используется количество проданного. То есть чем больше продано, тем больше вес будет иметь эта цена. В итоге для апельсинов расчет будет такой:

(200 х 125 + 300 х 23 + 250 х 143) / (125 + 23 + 143)

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

Для апельсинов расчет будет таким:

=СУММПРОИЗВ(B2:B4;C2:C4) / СУММ(C2:C4)

Мы используем те же самые функции СУММПРОИЗВ и СУММ. Рассматривать их подробно не будем, я думаю вы с ними уже разобрались. В целом наш урок подошел к концу. Пишите свои дополнения в комментариях. До новых встреч на портале WiFiGiD.RU.

Видео