Взвешенное среднее значение
Обычно при расчете среднего у всех чисел одинаковый уровень значимости. числа сделяются, а затем делятся на их количество. При значении взвешего среднего одному или несколько чисел дается более вес.
Поиск взвешеного среднего
Используйте функции СУММПРОИВ И СУММ для поиска взвешеного среднего, которое зависит от весовых значений.
- Например, отправка 10 карандашей составляет 20 цент в случае. Но вторая посылка из 40 досье стоит 30 цент на случай, так как карандаши очень востребованы. Если вы усредили стоимость каждой посылки этим способом (0,20+0,30)/2 = 0,25, результат будет не точным. При математических вычислениях не учитываются случаи, когда продано на 30 цент на 30 цент по сравнению с 20 центами. Чтобы получить правильное среднее значение, воспользуйтесь этой формулой, чтобы получить результат (28 цент на отправленные): =СУММПРОИПР(A2:A3;B2:B3)/СУММ(B2:B3) Формула работает путем деления общей стоимости двух заказов на общее количество заказаний.
Как в Excel рассчитать средневзвешенное значение
Функция Excel СРЗНАЧ вычисляет среднее значение набора данных. Часто пользователям нужно рассчитать средневзвешенное. Вы можете потратить целый день на поиски, но не найдете функцию, позволяющую сделать такой расчет. Можно, однако, рассчитывать средневзвешенные, создав формулу, которая использует функции СУММПРОИЗВ и СУММ.
На рис. 96.1 показан обычный лист, который содержит цены на бензин в течение 30 дней. Например, цена составляла $2,48 первые пять дней. Затем она снизилась до $2,41 в течение двух дней. Далее цена еще снизилась до $2,39 в течение трех дней и т. д.
Рис. 96.1. Формула в ячейке В16 вычисляет средневзвешенную цену на бензин
Ячейка В15 содержит формулу, которая использует функцию СРЗНАЧ: =СРЗНАЧ(B4:B13) . Если вы проанализируете ее, то увидите, что формула не возвращает точный результат. Скорее, цены должны быть взвешены по фактору количества дней, в течение которых каждая цена была в силе. Другими словами, средневзвешенное является соответствующим типом расчета. Следующая формула, указанная в ячейке В16 , делает то, что нужно: =СУММПРОИЗВ(B4:B13;C4:C13)/СУММ(C4:C13) .
Эта формула умножает каждую цену на соответствующее количество дней, а затем складывает все, что получилось. Результат делится на количество дней. Вы можете легко переделать эту формулу для других видов расчета средневзвешенного.
Как использовать формулу средневзвешенного ЕСЛИ в Excel
Вы можете использовать следующий синтаксис в Excel, чтобы применить формулу средневзвешенного ЕСЛИ:
=SUMPRODUCT(--( A2:A7 ="A"), B2:B7 , C2:C7 )/SUMIF( A2:A7 , "A", C2:C7 ) Эта формула вычисляет средневзвешенное значение значений в диапазоне B2:B7 , используя C2:C7 в качестве весов, только для ячеек, где A2:A7 равны «A».
В следующем примере показано, как использовать эту формулу на практике.
Пример: формула средневзвешенного ЕСЛИ в Excel
Во-первых, давайте введем следующие данные, которые показывают баллы двух студентов (Студент A и Студент B) на трех разных экзаменах:
Далее мы будем использовать следующую формулу для расчета средневзвешенного балла экзамена только для студента А:
=SUMPRODUCT(--( A2:A7 ="A"), B2:B7 , C2:C7 )/SUMIF( A2:A7 , "A", C2:C7 ) На следующем снимке экрана показано, как использовать эту формулу на практике:
Средневзвешенное значение экзаменационных баллов студента А равно 78 .
Мы можем убедиться, что это правильно, вручную вычислив средневзвешенный экзаменационный балл студента А.
Напомним, что мы используем следующую формулу для средневзвешенного значения:
Средневзвешенное значение = Σw i X i / Σw i
Мы можем подставить значения из нашего набора данных в эту формулу, чтобы рассчитать средневзвешенный балл экзамена для студента А:
- Средневзвешенное значение для студента A = Σw i X i / Σw i
- Средневзвешенное значение для студента А = (2*60 + 5*90 + 70*3) / (2+5+3)
- Средневзвешенное значение для студента A = 78
Это соответствует значению, которое мы рассчитали по формуле в Excel.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Как в экселе посчитать средневзвешенное значение
Средневзвешенные значения
Для того, чтобы рассчитать средневзвешенные значения по каким-либо параметрам, необходимо воспользоваться конструкцией из 2-х основных функций в EXCEL.
В примере ведется расчет среднего значения удельных затрат по логистике на одну товарную позицию. Для этого:
1. Вводится функция СУММПРОИЗВ, в аргументах которой вводятся 2 диапазона — B4:B6 с логистическими затратами на одну позицию в регионах и C4:C6 с указанием долей отгрузок по регионам.
2. Далее в строке формул после готовой формулы СУММПРОИЗВ(B4:B6;C4:C6) вводим «/» и делим на сумму «СУММ» самих долей с диапазоном суммирования C4:C6.
Полученное значение будет средневзвешенным, и его можно будет использовать для включения в себестоимость.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже: