Печать
PDF

Разбираем формулы среднеквадратического отклонения и дисперсии в Excel

Автор: Алексей Батурин.

среднеквадратическое отклонение и дисперсия в Excel

Цель данной статьи показать, как математические формулы, с которыми вы можете столкнуться в книгах и статьях, разложить на элементарные функции в Excel. 

В данной статье мы разберем формулы  среднеквадратического отклонения и дисперсии и рассчитаем их в Excel.

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

Рассматривая формулы моделей прогнозирования, мы встретимся со следующими показателями:

среднее в Excel

Например, у нас есть временной ряд - продажи по неделям в шт.

Неделя

1

2

3

4

5

6

7

8

9

10

Отгрузка, шт

6

10

7

12

6

14

8

13

10

14

Сморите пример расчета здесь: среднеквадратическое отклонние и дисперсия

Для этого временного ряда i=1, n=10среднее квадратическое отклонениедисперсия в Excel

Рассмотрим формулу среднего значения:

оценка среднеквадратического отклонения

Неделя

1

2

3

4

5

6

7

8

9

10

Отгрузка, шт

6

10

7

12

6

14

8

13

10

14

Для нашего временного ряда определим среднее значение дисперсия +и среднеквадратическое отклонение

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

Формула расчета среднеквадратического отклонение для выборки следующая:

среднеквадратическое отклонение формула

Разложим формулу на составные части и рассчитаем среднеквадратическое отклонение в Excel на примере нашего временного ряда.

среднеквадратическое отклонение случайной величины

1. Рассчитаем среднее значение для этого воспользуемся формулой Excel =СРЗНАЧ(B11:K11)

расчет среднего в Excel=СРЗНАЧ(ссылка на диапазон) = 100/10=10

среднеквадратическое отклонение формула +в excel


2. Определим отклонение каждого значения ряда относительно среднего отклонение относительно среднего

среднее квадратическое отклонение

для первой недели = 6-10=-4

для второй недели = 10-10=0

для третей = 7-1=-3 и т.д.


3. Для каждого значения ряда определим квадрат разницы отклонения значений ряда относительно среднего  квадрат разницы отклонений

для первой недели = (-4)^2=16

для второй недели = 0^2=0

для третей = (-3)^2=9 и т.д.


4. Рассчитаем сумму квадратов отклонений значений относительно среднего сумма квадратов отклонений относительного среднего с помощью формулы =СУММ(ссылка на диапазон (ссылка на диапазон с сумма квадратов отклонений)

сумма квадратов отклонений в Excel дисперсия

как рассчитать в excel среднеквадратическое отлонение  =16+0+9+4+16+16+4+9+0+16=90


5. дисперсия +и среднее квадратическое отклонениедля этого  сумму квадратов отклонений значений относительно среднего разделим на количество значений минус единица (Сумма((Xi-Xср)^2))/(n-1)

среднее квадратическое отклонение пример расчета в Excel

средняя квадратическое отклонение пример расчета в Excel =90/(10-1)=10


6. Среднеквадратическое отклонение равно среднее квадратическое отклонение формула = корень(10)=3,2

среднее квадратическое отклонение в Excel

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

 Скачать файл с примером


Рассмотрим еще один показатель, который в будущем нам понадобятся - дисперсия.

Как рассчитать дисперсию в Excel?

Дисперсия - квадрат среднеквадратического отклонения и отражает разброс данных относительно среднего.

Рассчитаем дисперсию:  дисперсия

дисперсия расчет в Excel

Скачать файл с примером 


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

Точных вам прогнозов!


Зарегистрируйтесь и протестируйте Forecast4AC PROскачать программу для прогнозирования Forecast4AC PRO

график модель прогноза + трендграфик сезенностьграфик прогноз + границы прогноза + трендграфик прогноз к предыдущим периодам

Forcast4AC Pro 4ddabd6bbee26 250x250Подписка "Прогноз с точностью 90% и выше!"

Присоединяясь к нам Вы получаете:

  • Более 10 рекомендаций по прогнозированию продаж!
  • Практические примеры!
  • Ссылку на Forecast4AC PRO - 40 дней бесплатно!
  • + советы по работе с программой!

Forecast4AC PRO - прогноз одним нажатием клавиши!

Статья полезная? Поделитесь с друзьями

Ваши комментарии и вопросы:

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


Защитный код
Обновить