Как найти средневзвешенное значение в excel
Перейти к содержимому

Как найти средневзвешенное значение в excel

  • автор:

Средневзвешенное значение в Excel

Существует несколько средних величин: среднее арифметическое, среднее геометрическое, среднее гармоническое, средневзвешенное и так далее.

Как показывает практика, большинство использует в своих расчетах только два понятия — это среднее арифметическое и средневзвешенное

Со средним арифметическим знаком каждый школьник и рассчитать его в Excel при помощи формулы СРЗНАЧ, я думаю, сможет каждый

Средневзвешенное — это показатель, который учитывает «вес» каждого числа или значения, используемого в вычислениях. Считается, что показатель средневзвешенного более точный и правильный в определенных расчетах

Для примера я взял банальный пример по данным о заработной платы сотрудников и их численности по должностям

Как видим, у нас 5 должностей, фонд заработной платы на человека и численность по каждой должности. Заодно я рассчитал среднеарифместическое значение по заработной плате (СРЗНАЧ)

Если считать математически, то формула средневзвешенного будет выглядеть как (Количество1*Заработная плата1+. +Количество5*Заработная плата5)/(Количество1+. +Количество5)

В больших массивах данных прописывать такое очень трудоемко, и здесь нам поможет Excel.

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

В примере наглядно видно, как сильно различаются показатели, и как сильно это может повлиять на дальнейшие расчеты.

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

Взвешенное среднее значение

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

Поиск взвешеного среднего

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

  • Например, отправка 10 карандашей составляет 20 цент в случае. Но вторая посылка из 40 досье стоит 30 цент на случай, так как карандаши очень востребованы. Если вы усредили стоимость каждой посылки этим способом (0,20+0,30)/2 = 0,25, результат будет не точным. При математических вычислениях не учитываются случаи, когда продано на 30 цент на 30 цент по сравнению с 20 центами. Чтобы получить правильное среднее значение, воспользуйтесь этой формулой, чтобы получить результат (28 цент на отправленные): =СУММПРОИПР(A2:A3;B2:B3)/СУММ(B2:B3) Формула работает путем деления общей стоимости двух заказов на общее количество заказаний.

Как использовать формулу средневзвешенного ЕСЛИ в Excel

Как использовать формулу средневзвешенного ЕСЛИ в 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 ) 

На следующем снимке экрана показано, как использовать эту формулу на практике:

формула средневзвешенной ЕСЛИ в Excel

Средневзвешенное значение экзаменационных баллов студента А равно 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:

Как найти средневзвешенное значение в excel

MARCHBANNER2017

Средневзвешенные значения

Для того, чтобы рассчитать средневзвешенные значения по каким-либо параметрам, необходимо воспользоваться конструкцией из 2-х основных функций в EXCEL.

1

В примере ведется расчет среднего значения удельных затрат по логистике на одну товарную позицию. Для этого:

1. Вводится функция СУММПРОИЗВ, в аргументах которой вводятся 2 диапазона — B4:B6 с логистическими затратами на одну позицию в регионах и C4:C6 с указанием долей отгрузок по регионам.

4

2

2. Далее в строке формул после готовой формулы СУММПРОИЗВ(B4:B6;C4:C6) вводим «/» и делим на сумму «СУММ» самих долей с диапазоном суммирования C4:C6.

3

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

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *