Печать
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. Надеемся, полученные знания пригодятся вам в работе.

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


{simplepopup} {/simplepopup}

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

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

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

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

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

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

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

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

Comments  

 
+3 #9 Герман 28.04.2015 15:03
Спасибо :lol:
Quote
 
 
+7 #8 Михаил 30.11.2014 12:17
Спасибо за разъяснение!
С Вашей помощью нашёл:
СТАНДОТКЛОН.Г = КОРЕНЬ(ДИСПР) для генеральной и
СТАНДОТКЛОН = КОРЕНЬ(ДИСП) для выборки
Quote
 
 
+4 #7 Алексей Батурин 19.09.2014 20:39
Цитирую Максим:
Только вы рассчитали не среднеквадратич ное отклонение, а стандартное отклонение.

Максим, добрый вечер. среднеквадратич ное отклонение и стандартное отклонение это синонимы. Хорошо что вы обратили на это внимание. Спасибо!
Возможно вы встретили расчет среднеквадратич еского отклонения с делением на n,
а у нас с делением на n-1

На n мы делим, если рассматривается вся генеральная совокупность данных.

На n-1, если анализируется не весь массив имеющихся данных, а определенная выборка.

Уменьшение степеней свободы (n) на 1 вызвано тем, что при проведении анализа на основе выборки происходит смешенная оценка ожидаемого значения случайной величины, что выражается в ее недооценке. Устранение этого момента достигается за счет снижения количества степеней свободы на 1.
;-)

Будут вопросы, пишите!
Quote
 
 
+2 #6 Максим 19.09.2014 16:21
Только вы рассчитали не среднеквадратич ное отклонение, а стандартное отклонение.
Quote
 
 
+5 #5 Kai 28.08.2014 20:10
Спасибо за помощь! Все просто и понятно!
Quote
 
 
+2 #4 Алексей Батурин 28.08.2014 12:29
Корректировку внес, спасибо!
Quote
 
 
+6 #3 Алексей Батурин 28.08.2014 12:21
ALEN, спасибо за ваш отзыв!
Цель статьи это научиться разбирать по частям математические формулы, а не показать простой способ расчета среднеквадратич еского отклонения или дисперсии в Excel.
О том как рассчитать среднеквадратич еское отклонение или дисперсию использовать стандартные формулы Excel в интернете написано много.

Но люди сталкиваются с тем, что читая книгу или статью и найдя там формулу, не могу её перенести в элементарные Excel функции (мой опыт).

Да в начале статьи цель сформулирована не четко, изменю,

Спасибо!

Хорошего вам дня!
Quote
 
 
+7 #2 ALEN 28.08.2014 08:54
Странно, а почему статья называется "Разбираем формулы среднеквадратич еского отклонения и дисперсии в Excel"?
В Excel есть встроенные формулы (ДИСП, СТАНДОТКЛОНП и т.п) в разных вариациях, почему-то о них как раз и не упомянуто, хотя это следовало сделать в первую очередь.
А то, что Вы разобрали - обычная математика, к Excel прямого отношения не имеющая.

Вывод - статья "высосана из пальца".
Quote
 
 
+8 #1 Eлена 19.04.2014 17:52
Спасибо - очень доступная и полезная информация.
Quote
 

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


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