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

Дисперсия, среднеквадратичное (стандартное) отклонение, коэффициент вариации в Excel

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

Дисперсия

Дисперсия случайной величины – это один из основных показателей в статистике. Он отражает меру разброса данных вокруг средней арифметической.

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

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

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

То есть дисперсия — это математическое ожидание отклонений от математического ожидания.

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

Дисперсия во выборке

s 2 – выборочная дисперсия, рассчитанная по данным наблюдений,

X – отдельные значения,

– среднее арифметическое по выборке.

Стоит отметить, что у такого расчета дисперсии есть недостаток – она получается смещенной, т.е. ее математическое ожидание не равно истинному значению дисперсии. Подробней об этом здесь. Однако при увеличении объема выборки она все-таки приближается к своему теоретическому аналогу, т.е. является асимптотически не смещенной.

Простыми словами дисперсия – это средний квадрат отклонений. То есть вначале рассчитывается среднее значение, затем берется разница между каждым исходным и средним значением, возводится в квадрат, складывается и затем делится на количество значений в данной совокупности. Разница между отдельным значением и средней отражает меру отклонения. В квадрат возводится для того, чтобы все отклонения стали исключительно положительными числами и чтобы избежать взаимоуничтожения положительных и отрицательных отклонений при их суммировании. Затем, имея квадраты отклонений, просто рассчитываем среднюю арифметическую. Средний – квадрат – отклонений. Отклонения возводятся в квадрат, и считается средняя. Теперь вы знаете, как найти дисперсию.

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

Генеральную и выборочную дисперсии легко рассчитать в Excel. Есть специальные функции: ДИСП.Г и ДИСП.В соответственно.

Функции Excel для расчета дисперсии

В чистом виде дисперсия не используется. Это вспомогательный показатель, который нужен в других расчетах. Например, в проверке статистических гипотез или расчете коэффициентов корреляции. Отсюда неплохо бы знать математические свойства дисперсии.

Свойства дисперсии

Свойство 1. Дисперсия постоянной величины A равна (нулю).

Свойство 2. Если случайную величину умножить на постоянную А, то дисперсия этой случайной величины увеличится в А 2 раз. Другими словами, постоянный множитель можно вынести за знак дисперсии, возведя его в квадрат.

Свойство 3. Если к случайной величине добавить (или отнять) постоянную А, то дисперсия останется неизменной.

Свойство 4. Если случайные величины X и Y независимы, то дисперсия их суммы равна сумме их дисперсий.

Свойство 5. Если случайные величины X и Y независимы, то дисперсия их разницы также равна сумме дисперсий.

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

Если из дисперсии извлечь квадратный корень, получится среднеквадратичное (стандартное) отклонение (сокращенно СКО). Встречается название среднее квадратичное отклонение и сигма (от названия греческой буквы). Общая формула стандартного отклонения в математике следующая:

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

На практике формула стандартного отклонения следующая:

Среднеквадратичное отклонение по генеральной совокупности

Как и с дисперсией, есть и немного другой вариант расчета. Но с ростом выборки разница исчезает.

Расчет cреднеквадратичного (стандартного) отклонения в Excel

Для расчета стандартного отклонения достаточно из дисперсии извлечь квадратный корень. Но в Excel есть и готовые функции: СТАНДОТКЛОН.Г и СТАНДОТКЛОН.В (по генеральной и выборочной совокупности соответственно).

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

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

Коэффициент вариации

Значение стандартного отклонения зависит от масштаба самих данных, что не позволяет сравнивать вариабельность разных выборках. Чтобы устранить влияние масштаба, необходимо рассчитать коэффициент вариации по формуле:

Формула коэффициента вариации

По нему можно сравнивать однородность явлений даже с разным масштабом данных. В статистике принято, что, если значение коэффициента вариации менее 33%, то совокупность считается однородной, если больше 33%, то – неоднородной. В реальности, если коэффициент вариации превышает 33%, то специально ничего делать по этому поводу не нужно. Это информация для общего представления. В общем коэффициент вариации используют для оценки относительного разброса данных в выборке.

Расчет коэффициента вариации в Excel

Расчет коэффициента вариации в Excel также производится делением стандартного отклонения на среднее арифметическое:

Коэффициент вариации обычно выражается в процентах, поэтому ячейке с формулой можно присвоить процентный формат:

Процентный формат

Коэффициент осцилляции

Еще один показатель разброса данных на сегодня – коэффициент осцилляции. Это соотношение размаха вариации (разницы между максимальным и минимальным значением) к средней. Готовой формулы Excel нет, поэтому придется скомпоновать три функции: МАКС, МИН, СРЗНАЧ.

Коэффициент осцилляции в Excel

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

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

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

Источник



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

Оценка S 2 генеральной дисперсии σ 2 любого закона распределения может быть вычислена (при неизвестном математическом ожидании генерального среднего) по формуле:

Читайте также:  Подведены результаты или итоги

Эта оценка является несмещенной и состоятельной, а для нормального распределения — еще и эффективной.

Для нормального закона распределения оценка генерального среднеквадратического отклонения (СКО) S результатов наблюдений определяется:

Оценка является несмещенной, состоятельной и асимптотически эффективной только для нормального закона. В случае представления результатов вариационным рядом следует пользоваться формулой (5.3).

Несмещенная оценка СКО для нормальных распределений так же определяется по формуле:

Значения коэффициента Мкприведены в таблице 1

Таблица 1 — Значения коэффициента Мкв зависимости от количества наблюдений п

СКО случайной погрешности оценки центра распределения (СКО результата измерений) убывает по сравнению с СКО результата наблюдений

в как показано по формуле:

Определение оценок третьего центрального момента μ3 коэффициента асимметрии γa, СКО коэффициента асимметрии σa) проводится по формулам:

Формулы для вычисления начальных ak и центральных μk моментов и соотношения между ними приведены в таблице 2.

Таблица 2 — Перечень формул

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

Рассмотрим на примере последовательность определения оценок центра распределения. Даны результаты 20 измерений перемещения для точек пера лопатки компрессора под действием центробежной силы. Результаты наблюдения и частота их появления указаны в таблице 3.

Таблица 3 — Результаты наблюдений

Требуется определить оценки результата измерения и СКО результатов наблюдений и измерения.

Будем считать, что закон распределения не известен. В этом случае, как отмечалось раньше, за оценку центра распределения экспериментальных данных принимают медиану из ряда пяти оценок центров (расположенных в вариационный ряд).

Определяем оценку центра как:

· среднее арифметическое по формуле (4):

среднее арифметическое 90 %-ной выборки определяем по
формуле (7). Пять процентов выборки в нашем случае 0,05 . n = 0,05 . 20 =1,
т. е. один результат измерения. Отбрасываем по одному измерению с концов
вариационного ряда, т. е. результаты x1 = 23,0 мкм и x20 = 23,7 мкм.

· медиану распределения — по формуле (9). Поскольку n-четное, то

· срединный размах определяем по формуле (15). Для этого вычисляем 25 % и 75 %-ные квантили опытного распределения. Этими квантилями являются точки между 4 и 5, а также между 16 и 17 результатами:

Источник

Стандартное отклонение

Стандартное отклонение (англ. Standard Deviation) — простыми словами это мера того, насколько разбросан набор данных.

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

Стандартное отклонение обозначается буквой σ (греческая буква сигма).

Стандартное отклонение также называется:

  • среднеквадратическое отклонение,
  • среднее квадратическое отклонение,
  • среднеквадратичное отклонение,
  • квадратичное отклонение,
  • стандартный разброс.

Использование и интерпретация величины среднеквадратического отклонения

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

  • в финансах в качестве меры волатильности,
  • в социологии в опросах общественного мнения — оно помогает в расчёте погрешности.

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

День 1 День 2 День 3 День 4
Пред.А 19 21 19 21
Пред.Б 15 26 15 24

В обеих компаниях среднее количество товара составляет 20 единиц:

  • А -> (19 + 21 + 19+ 21) / 4 = 20
  • Б -> (15 + 26 + 15+ 24) / 4 = 20

Однако, глядя на цифры, можно заметить:

  • в компании A количество товара всех четырёх дней очень близко находится к этому среднему значению 20 (колеблется лишь между 19 ед. и 21 ед.),
  • в компании Б существует большая разница со средним количеством товара (колеблется между 15 ед. и 26 ед.).

Если рассчитать стандартное отклонение каждой компании, оно покажет, что

  • стандартное отклонение компании A = 1,
  • стандартное отклонение компании Б ≈ 5.

Стандартное отклонение показывает эту волатильность данных — то, с каким размахом они меняются; т.е. как сильно этот запас товара на складах компаний колеблется (поднимается и опускается).

Расчет среднеквадратичного (стандартного) отклонения

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

Формулы вычисления стандартного отклонения sigma сигма стандартное отклонение формула, среднее квадратичное отклонение формула, среднеквадратическое отклонение формула, среднее квадратическое отклонение формула

Где:
σ — стандартное отклонение,
xi — величина отдельного значения выборки,
μ — среднее арифметическое выборки,
n — размер выборки.
Эта формула применяется, когда анализируются все значения выборки.

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

Где:
S — стандартное отклонение,
n — размер выборки,
xi — величина отдельного значения выборки,
xср — среднее арифметическое выборки.
Эта формула применяется, когда присутствует очень большой размер выборки, поэтому на анализ обычно берётся только её часть.
Единственная разница с предыдущей формулой: “n — 1” вместо “n”, и обозначение «xср» вместо «μ».

Разница между формулами S и σ («n» и «n–1»)

Состоит в том, что мы анализируем — всю выборку или только её часть:

  • только её часть – используется формула S (с «n–1»),
  • полностью все данные – используется формула σ (с «n»).

Как рассчитать стандартное отклонение?

Пример 1 (с σ)

Рассмотрим данные о запасе какого-то товара на складах Предприятия Б.

День 1 День 2 День 3 День 4
Пред.Б 15 26 15 24

Если значений выборки немного (небольшое n, здесь он равен 4) и анализируются все значения, то применяется эта формула:

Формулы вычисления стандартного отклонения sigma сигма стандартное отклонение формула, среднее квадратичное отклонение формула, среднеквадратическое отклонение формула, среднее квадратическое отклонение формула

Применяем эти шаги:

1. Найти среднее арифметическое выборки:

μ = (15 + 26 + 15+ 24) / 4 = 20

2. От каждого значения выборки отнять среднее арифметическое:

x1 — μ = 15 — 20 = -5

x2 — μ = 26 — 20 = 6

x3 — μ = 15 — 20 = -5

x4 — μ = 24 — 20 = 4

3. Каждую полученную разницу возвести в квадрат:

4. Сделать сумму полученных значений:

Σ (xi — μ)² = 25 + 36+ 25+ 16 = 102

5. Поделить на размер выборки (т.е. на n):

(Σ (xi — μ)²)/n = 102 / 4 = 25,5

6. Найти квадратный корень:

√((Σ (xi — μ)²)/n) = √ 25,5 ≈ 5,0498

Пример 2 (с S)

Задача усложняется, когда существуют сотни, тысячи или даже миллионы данных. В этом случае берётся только часть этих данных и анализируется методом выборки.

У Андрея 20 яблонь, но он посчитал яблоки только на 6 из них.

Популяция — это все 20 яблонь, а выборка — 6 яблонь, это деревья, которые Андрей посчитал.

Яблоня 1 Яблоня 2 Яблоня 3 Яблоня 4 Яблоня 5 Яблоня 6
9 2 5 4 12 7

Так как мы используем только выборку в качестве оценки всей популяции, то нужно применить эту формулу:

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

Математически она отличается от предыдущей формулы только тем, что от n нужно будет вычесть 1. Формально нужно будет также вместо μ (среднее арифметическое) написать X ср.

Применяем практически те же шаги:

1. Найти среднее арифметическое выборки:

Xср = (9 + 2 + 5 + 4 + 12 + 7) / 6 = 39 / 6 = 6,5

2. От каждого значения выборки отнять среднее арифметическое:

X1 – Xср = 9 – 6,5 = 2,5

X2 – Xср = 2 – 6,5 = –4,5

X3 – Xср = 5 – 6,5 = –1,5

X4 – Xср = 4 – 6,5 = –2,5

X5 – Xср = 12 – 6,5 = 5,5

X6 – Xср = 7 – 6,5 = 0,5

3. Каждую полученную разницу возвести в квадрат:

(X1 – Xср)² = (2,5)² = 6,25

(X2 – Xср)² = (–4,5)² = 20,25

(X3 – Xср)² = (–1,5)² = 2,25

(X4 – Xср)² = (–2,5)² = 6,25

(X5 – Xср)² = 5,5² = 30,25

(X6 – Xср)² = 0,5² = 0,25

4. Сделать сумму полученных значений:

Σ (Xi – Xср)² = 6,25 + 20,25+ 2,25+ 6,25 + 30,25 + 0,25 = 65,5

5. Поделить на размер выборки, вычитав перед этим 1 (т.е. на n–1):

(Σ (Xi – Xср)²)/(n-1) = 65,5 / (6 – 1) = 13,1

6. Найти квадратный корень:

S = √((Σ (Xi – Xср)²)/(n–1)) = √ 13,1 ≈ 3,6193

Дисперсия и стандартное отклонение

Стандартное отклонение равно квадратному корню из дисперсии (S = √D). То есть, если у вас уже есть стандартное отклонение и нужно рассчитать дисперсию, нужно лишь возвести стандартное отклонение в квадрат (S² = D).

Дисперсия — в статистике это «среднее квадратов отклонений от среднего». Чтобы её вычислить нужно:

  1. Вычесть среднее значение из каждого числа
  2. Возвести каждый результат в квадрат (так получатся квадраты разностей)
  3. Найти среднее значение квадратов разностей.

Ещё расчёт дисперсии можно сделать по этой формуле:

Дисперсия и стандартное отклонение расчёт дисперсии формула

Где:
S² — выборочная дисперсия,
Xi — величина отдельного значения выборки,
Xср (может появляться как X̅) — среднее арифметическое выборки,
n — размер выборки.

Правило трёх сигм

Это правило гласит: вероятность того, что случайная величина отклонится от своего математического ожидания более чем на три стандартных отклонения (на три сигмы), почти равна нулю.

Правило трёх сигм

Глядя на рисунок нормального распределения случайной величины, можно понять, что в пределах:

  • одного среднеквадратического отклонения заключаются 68,26% значений (Xср ± 1σ или μ ± 1σ),
  • двух стандартных отклонений — 95,44% (Xср ± 2σ или μ ± 2σ),
  • трёх стандартных отклонений — 99,72% (Xср ± 3σ или μ ± 3σ).

Это означает, что за пределами остаются лишь 0,28% — это вероятность того, что случайная величина примет значение, которое отклоняется от среднего более чем на 3 сигмы.

Стандартное отклонение в excel

Вычисление стандартного отклонения с «n – 1» в знаменателе (случай выборки из генеральной совокупности):

1. Занесите все данные в документ Excel.

Формулы вычисления стандартного отклонения sigma сигма стандартное отклонение в эксель excel

2. Выберите поле, в котором вы хотите отобразить результат.

3. Введите в этом поле «=СТАНДОТКЛОНА(«

4. Выделите поля, где находятся данные, потом закройте скобки.

Формулы вычисления стандартного отклонения sigma сигма стандартное отклонение в эксель excel

5. Нажмите Ввод (Enter).

Формулы вычисления стандартного отклонения sigma сигма стандартное отклонение в эксель excel

В случае если данные представляют всю генеральную совокупность (n в знаменателе), то нужно использовать функцию СТАНДОТКЛОНПА.

Формулы вычисления стандартного отклонения sigma сигма стандартное отклонение в эксель excel

Формулы вычисления стандартного отклонения sigma сигма стандартное отклонение в эксель excel

Коэффициент вариации

Коэффициент вариации — отношение стандартного отклонения к среднему значению, т.е. Cv = (S/μ) × 100% или V = (σ/X̅) × 100%.

Источник

Ско ряда и ско результата

Инвестируй как Баффет (№ 59) Разбираемся в сути СКО.

Итак, в прошлый раз мы познакомились с двумя из трех характеристик активов в нашем инвестиционном портфеле: средней доходностью и среднеквадратичным отклонением (СКО). Первая цифра показывает сколько мы в среднем будем зарабатывать ежегодно, если будем держать наши активы долго, а вторая цифра показывает, как сильно будет колебаться величина конкретной доходности в тот или иной год от своего среднего значения.

Например, для акций компании American Express, для которой мы произвели расчеты на 20-летнем промежутке времени с 1995 по 2015 годы, мы получили, что в среднем за год акции компании росли на $2,11. При этом СКО составляло $7,01.

Смысл первой величины нам совершенно понятен – акции ежегодно росли в среднем на 2,11 доллара – тут не должно быть вопросов. (Заметим в скобках, что мы считаем сейчас доход только от изменения цены актива. Это не совсем верно. Надо еще прибавлять дивиденды, которые мы ежегодно получаем, но для простоты мы пока про это забудем).

А вот что конкретно означает вторая величина – СКО — пока не очень ясно. Поясним.

Давайте, к средней величине годовой доходности (2,11 доллара) мы сначала прибавим СКО (получится 2,11+7,01=9,12), а потом вычтем СКО (получится 2,11-7,01= -4,9). Так вот, по науке (при некоторых дополнительных условиях, о которых мы пока умолчим, чтоб не усложнять) примерно в двух случаях из трех наш годовой доход будет попадать в интервал между этими цифрами: между $9,12 и —$4,9, давая при этом среднюю доходность около $2,11 в год. И только в одном случае из трех доход будет выбиваться за пределы данного коридора.

Правда, важная информация? Мы заранее можем обоснованно предположить, что чаще всего наша годовая доходность будет находиться в вычисленном коридоре. И только примерно в 1/3 случаев будет выходить за его пределы вверх или вниз. Такой расчет дает там мужество перенести убыточные годы. Например (см. таблицу в пре дыдущем посте), у нас подряд в 2000-м, 2001-м и 2002-м годах были убытки -0,74, -6,51 и -2,51 доллара потерь соответственно. Всего за три эти года мы методично теряли деньги и потеряли всего 9,76 доллара на акцию! Есть отчего начать нервничать. «Спокойно — говорим мы сами себе – так и должно быть: в первом и третьем случае мы находимся в нашем коридоре, а во втором случае немного вышли за его границы, но такое по теории и должно происходить примерно в 1/3 случаев». Подобное рассуждение очень помогает не отчаиваться при наблюдении долговременных убытков.

Давайте проверим теорию. По табличке в предыдущем посте не трудно подсчитать, что в рассчитанную полосу доходности наши фактические доходы попали в 11 случаях из 20. Немного меньше, чем в 2/3 случаев. Если бы было 13 попаданий, то было бы как раз по теории. Почему произошло некоторое отклонение? Потому что цифра 2/3 попаданий справедлива только для очень длинного промежутка времени, для очень большого числа наблюдений. Если бы у нас была серия не из 20 лет, а из 100, то цифра попаданий в указанный коридор была бы значительно ближе к 2/3.

Но инвестирование – это не математика, нам не нужны такие уж точные значения, нас вполне устраивает понимание, в каких границах будет примерно лежать наша конкретная доходность в тот или иной год. И это новое знание о многом нам скажет. Например, нам сейчас стало понятно, что для такой не очень уж высокой средней доходности (2,11 доллара в год на одну акцию или 15,8% от цены ее покупки в 1995 году — от $13,38) терпеть весьма большой разброс по годам: от $9,12 до $4,9, а в 1/3 случаев еще более значительный, кому-то может показаться некомфортным.

Вот если бы доходность была, скажем, 4 доллара в год, а СКО – 2 доллара, то у нас годовые доходы в 2/3 случаев попадали бы в пределы от 2 до 6 долларов. В этом случае мы чувствовали бы себя значительно более комфортно. Можно было бы утверждать, что доход почти никогда не будет отрицательным, и он в среднем будет весьма высоким.

Вот теперь мы точно можем сказать, что понимаем суть этих двух параметров «Средняя доходность» и «СКО».

Но гораздо больше нас волнует другой вопрос: а как же добиться высоких и стабильных показателей доходности? Чтобы средняя доходность была достаточно большой, а СКО достаточно маленькой. Такие способы действительно есть.

Я знаю два пути добиться этого. Первый путь мы в прошлом довольно подробно обсудили. Покупать акции-облигации Уоррена Баффета, которые как раз характеризуются высокой доходностью и высокой стабильностью работы компании, выпустившей эти акции. Купить их по низкой цене и держать долго — вот к чему сводилась наша стратегия. Но очень хорошо комбинировать этот подход в другим, с пассивными портфельными инвестициями, которые мы изучаем прямо сейчас.

Итак, второй путь – грамотно формировать портфель своих инвестиций. Два показателя, характеризующих любой актив в вашем портфеле инвестиций, мы уже разобрали: «Средняя годовая доходность» и «Среднеквадратичное отклонение» (СКО). Нам надо разобраться еще с одним показателем: корреляция доходностей двух активов. Если первые два показателя характеризовали каждый актив в портфеле сам по себе, индивидуально, то показатель «корреляция» относится к паре активов. Если кратко, то корреляция показывает насколько вразнобой колеблются два актива. Разобраться с корреляцией мы попробуем в следующем посте.
_____________
Начало разговора по теме инвестиций здесь
Предыдущий пост по этой тематике здесь
Следующий пост здесь

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

Источник