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

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

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite - автоматический расчет прогноза в Excel.
  • 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Зарегистрируйтесь и скачайте решения

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

 

Комментарии   

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

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

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

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

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

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

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

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

Спасибо!

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

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

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