Расчет ско в excel

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

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

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

​СТАНДОТКЛОН​ сделать простым выделением​

Вычисление коэффициента вариации

​(/)​ коэффициент вариации.​. Аргументы полностью идентичны​ все нужные данные​или​

​ разделена, в зависимости​Одним из основных статистических​ активы предприятия В​Обычно показатель выражается в​Коэффициент вариации позволяет сравнить​ разброса двух случайных​ двойных кавычках, например​Описание (результат)​ значения и текстовые​Функция СТАНДОТКЛОНА предполагает, что​

Шаг 1: расчет стандартного отклонения

​и​ данного диапазона. Вместо​на клавиатуре. Далее​Выделяем ячейку, в которую​ тем, что и​ введены, жмем на​«Полный алфавитный перечень»​​ от того, по​​ показателей последовательности чисел​ рискованнее. Риск выше​ процентах. Поэтому для​ риск инвестирования и​ величин с разными​ «Возраст» или «Урожай»​Результат​ представления чисел в​​ аргументы являются только​​СРЗНАЧ​​ оператора​​ выделяем ячейку, в​

​ будет выводиться результат.​ у операторов группы​

​ кнопку​
​. Выбираем наименование​
​ генеральной совокупности происходит​

​ является коэффициент вариации.​ в 1,7 раза.​ ячеек с результатами​ доходность двух и​ единицами измерения относительно​ в приведенном ниже​=СТАНДОТКЛОНА(A3:A12)​ ссылку как часть​ выборкой из генеральной​​эта задача очень​​СТАНДОТКЛОН.В​ которой располагается среднее​ Прежде всего, нужно​СТАНДОТКЛОН​

​«OK»​​«СТАНДОТКЛОН.Г»​​ вычисление или по​ Для его нахождения​ Как сопоставить акции​ установлен процентный формат.​​ более портфелей активов.​​ ожидаемого значения. В​​ примере базы данных,​​Стандартное отклонение предела прочности​​ вычисления, используйте функцию​​ совокупности. Если данные​​ упрощается. Таким образом,​​, если пользователь считает​ арифметическое заданного числового​ учесть, что коэффициент​. То есть, в​В предварительно выделенной ячейке​или​​ выборке, на два​​ производятся довольно сложные​

​ с разной ожидаемой​Значение коэффициента для компании​ Причем последние могут​ итоге можно получить​ или как число​ для всех инструментов​ СТАНДОТКЛОН.​ представляют всю генеральную​ в Excel её​ нужным, можно применять​​ ряда. Для того,​​ вариации является процентным​ их качестве могут​ отображается итог расчета​«СТАНДОТКЛОН.В»​ отдельных варианта:​ расчеты. Инструменты Microsoft​ доходностью и различным​ А – 33%,​ существенно отличаться. То​​ сопоставимые результаты. Показатель​​ (без кавычек) ,​ (27,46391572)​Функция СТАНДОТКЛОНА вычисляется по​ совокупность, то стандартное​​ может выполнить даже​

​ выбранного вида стандартного​​, в зависимости от​СТАНДОТКЛОН.Г​

Шаг 2: расчет среднего арифметического

​ Excel позволяют значительно​ уровнем риска?​ что свидетельствует об​ есть показатель увязывает​ наглядно иллюстрирует однородность​ задающее положение столбца​27,46391572​​ следующей формуле:​​ отклонение следует вычислять​ человек, который не​

​СТАНДОТКЛОН.Г​ и вывести значение,​ с этим следует​ числовые величины, так​​ отклонения.​​ того, по генеральной​

​и​ облегчить их для​​Для сопоставления активов двух​​ относительной однородности ряда.​ риск и доходность.​​ временного ряда.​​ в списке: 1​

​Юрик​​где x — выборочное среднее​​ с помощью функции​ имеет высокого уровня​.​​ щёлкаем по кнопке​​ поменять формат ячейки​ и ссылки. Устанавливаем​Урок:​ совокупности или по​СТАНДОТКЛОН.В​ пользователя.​​ компаний рассчитан коэффициент​​ Формула расчета коэффициента​ Позволяет оценить отношение​Коэффициент вариации используется также​ — для первого​: СТАНДОТКЛОН (число1; число2;. )​ СРЗНАЧ(значение1,значение2,…), а n —​ СТАНДОТКЛОНПА.​ знаний связанных со​После этого, чтобы рассчитать​Enter​​ на соответствующий. Это​​ курсор в поле​

​ между среднеквадратическим отклонением​​ инвесторами при портфельном​ поля, 2 —​

Шаг 3: нахождение коэффициента вариации

​Число1, число2. — от​ размер выборки.​Стандартное отклонение вычисляется с​ статистическими закономерностями.​ значение и показать​

​на клавиатуре.​ можно сделать после​«Число1»​ в Excel​ расчет. Жмем на​Синтаксис данных функций выглядит​ Excel​ для предприятия В​Сравните: для компании В​ и ожидаемой доходностью​ анализе в качестве​ для второго поля​​ 1 до 30​​Скопируйте образец данных из​ использованием «n-1» метода.​Автор: Максим Тютюшев​​ результат на экране​​Как видим, результат расчета​ её выделения, находясь​​. Так же, как​​Среднее арифметическое является отношением​ кнопку​ соответствующим образом:​

​Этот показатель представляет собой​ – 50%, для​ коэффициент вариации составил​ в относительном выражении.​ количественного показателя риска,​ и так далее.​​ числовых аргументов, соответствующих​​ следующей таблицы и​Допускаются следующие аргументы: числа;​В этой статье описаны​ монитора, щелкаем по​ выведен на экран.​​ во вкладке​​ и в предыдущем​ общей суммы всех​«OK»​= СТАНДОТКЛОН(Число1;Число2;…)​ отношение стандартного отклонения​ предприятия А –​ 50%: ряд не​ Соответственно, сопоставить полученные​​ связанного с вложением​​Критерий. Это диапазон​

​ имена, массивы или​ синтаксис формулы и​ кнопке​Таким образом мы произвели​«Главная»​ случае, выделяем на​ значений числового ряда​.​= СТАНДОТКЛОН.Г(Число1;Число2;…)​ к среднему арифметическому.​

​ 33%. Риск инвестирования​ является однородным, данные​ результаты.​ средств в определенные​ ячеек, содержащий задаваемые​ совокупности. Вместо аргументов,​

​ ячейку A1 нового​

​ ссылки, содержащие числа;​​ использование функции​​Enter​ вычисление коэффициента вариации,​. Кликаем по полю​ листе нужную нам​ к их количеству.​Открывается окно аргументов данной​= СТАНДОТКЛОН.В(Число1;Число2;…)​​ Полученный результат выражается​​ в ценные бумаги​ значительно разбросаны относительно​При принятии инвестиционного решения​​ активы. Особенно эффективен​​ условия. В качестве​

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

​ в ситуации, когда​ аргумента критерия можно​ запятой, можно также​ отобразить результаты формул,​ логические значения, такие​в Microsoft Excel.​Существует условное разграничение. Считается,​ в которых уже​ в блоке инструментов​ того, как их​ показателя тоже существует​ иметь от 1​​ стандартное отклонение, выделяем​​В Экселе не существует​​ в 1,54 раза​​​ момент: когда ожидаемая​ у активов разная​ использовать любой диапазон,​ использовать массив или​ выделите их и​ как ИСТИНА и​Оценивает стандартное отклонение по​

​ что если показатель​

СТАНДОТКЛОНА (функция СТАНДОТКЛОНА)

​ были рассчитаны стандартное​«Число»​ координаты были занесены​​ отдельная функция –​​ до 255 полей,​

Описание

​ любую свободную ячейку​ отдельно функции для​ (50% / 33%).​Прежде чем включить в​ доходность актива близка​ доходность и различный​

Синтаксис

​ который содержит по​

​ ссылку на массив.​ нажмите клавишу F2,​

​ ЛОЖЬ, в ссылке.​​ выборке. Стандартное отклонение​ коэффициента вариации менее​ отклонение и среднее​. Из раскрывшегося списка​ в поле окна​СРЗНАЧ​ в которых могут​ на листе, которая​ вычисления этого показателя,​ Это означает, что​

Замечания

​ инвестиционный портфель дополнительный​ к 0, коэффициент​ уровень риска. К​ крайней мере один​И ещё одна​ а затем —​Аргументы, содержащие значение ИСТИНА,​ — это мера​ 33%, то совокупность​

​ арифметическое. Но можно​ вариантов выбираем​

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

​ вариации может получиться​ примеру, у одного​ заголовок столбца и​ функция.​ клавишу ВВОД. При​ интерпретируются как 1.​

​ того, насколько широко​ чисел однородная. В​ поступить и несколько​«Процентный»​ кнопку​ на конкретном примере.​ числа, так и​

​ того, чтобы выводить​ для расчета стандартного​ имеют лучшее соотношение​ должен обосновать свое​

​ большим. Причем показатель​ актива высокая ожидаемая​ по крайней мере​ДСТАНДОТКЛ (база_данных; поле;​ необходимости измените ширину​ Аргументы, содержащие текст​

​ разбросаны точки данных​ обратном случае её​

​ по-иному, не рассчитывая​. После этих действий​«OK»​

Пример

​Выделяем на листе ячейку​ ссылки на ячейки​ в неё результаты​ отклонения и среднего​ риск / доходность.​ решение. Один из​ значительно меняется при​ доходность, а у​ одну ячейку под​ критерий)​ столбцов, чтобы видеть​ или значение ЛОЖЬ,​ относительно их среднего.​

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

Определение

Среднеквадратическое отклонение (англ. Standard Deviation, SD) является показателем, который используется в теории вероятности и математической статистике для оценки степени рассеивания случайной величины относительно ее математического ожидания. В инвестировании стандартное отклонение доходности ценных бумаг или портфеля используется для оценки меры риска. Чем выше степень рассеивания доходности ценной бумаги относительно ожидаемого доходности (математическое ожидание доходности), тем выше риск инвестирования, и наоборот.

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

Формула

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

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

Где X_i – значение случайной величины X при i-ом исходе; M(X) математическое ожидание случайной величины X; p_i – вероятность i-го исхода; N – количество возможных исходов.

При этом математическое ожидание случайной величины рассчитывается по формуле:

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

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

Где X_i – i-ое значение случайной величины X; X – среднеарифметическое генеральной совокупности; N – объем генеральной совокупности.

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

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

Где X_i – i-ое значение случайной величины X; X – среднеарифметическое выборки; N – объем выборки.

Примеры расчета

Пример 1

Портфельный менеджер должен оценить риски инвестирования в акции двух компаний А и Б. При этом он рассматривает 5 сценариев развития событий, информация по которым представлена в таблице.

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

Шаг 1. Рассчитаем математическое ожидание доходности для каждой из акций.

M(А) = -5%×0,02+6%×0,25+15%×0,40+24%×0,30+34%×0,03 = 15,62%

M(Б) = -18%×0,02+2%×0,25+16%×0,40+27%×0,30+36%×0,03 = 22,14%

Шаг 2. Подставим полученные данные в первую формулу.

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

Пример 2

Аналитик располагает данными о доходности двух ценных бумаг за последние 5 лет, которые представлены в таблице.

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

Шаг 1. Рассчитаем ожидаемую доходность для каждой ценной бумаги как среднеарифметическое выборки.

X _А = (7 + 15 + 2 – 5 + 6) ÷ 5 = 5%

X _Б = (3 – 2 + 12 + 4 +8) ÷ 5 = 5%

Шаг 2. Рассчитаем стандартное отклонение доходности для каждой из ценных бумаг по формуле для выборки из генеральной совокупности данных.

Следует отметить, что обе ценные бумаги имеют равную ожидаемую доходность 5%. При этом стандартное отклонение доходности у ценной бумаги Б ниже, что при прочих равных делает ее более привлекательным объектом инвестирования в следствие лучшего профиля риск-доходность.

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

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

Для выборки воспользуйтесь функцией «СТАНДОТКЛОН.В»:

1. В диапазоне ячеек B1:F1 введены значения случайной величины X.
2. Выберите выходную ячейку B2.
3. В командной строке нажмите кнопку fx, во всплывшем окне «Вставка функции» выберите Категорию «Полный алфавитный перечень» и выберите функцию «СТАНДОТКЛОН.В».
4. В поле «Число1» выберите диапазон ячеек B1:F1, поле «Число2» оставьте пустым и нажмите кнопку «OK».

Для генеральной совокупности используется функция «СТАНДОТКЛОН.Г»:

1. В диапазоне ячеек B1:F1 введены значения случайной величины X.
2. Выберите выходную ячейку B2.
3. В командной строке нажмите кнопку fx, во всплывшем окне «Вставка функции» выберите Категорию «Полный алфавитный перечень» и выберите функцию «СТАНДОТКЛОН.Г».
4. В поле «Число1» выберите диапазон ячеек B1:F1, поле «Число2» оставьте пустым и нажмите кнопку «OK».

Интерпретация

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

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

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

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

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

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

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

Для этого временного ряда i=1, n=10 , ,

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

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

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

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

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

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

= СРЗНАЧ(ссылка на диапазон) = 100/10=10

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

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

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

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

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

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

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

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

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

=16+0+9+4+16+16+4+9+0+16=90

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

= 90/(10-1)=10

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Добрый день!

В статье я решил рассмотреть, как работает стандартное отклонение в Excel с помощью функции СТАНДОТКЛОН. Я просто очень давно не описывал и не комментировал статистические функции, а еще просто потому что это очень полезная функция для тех, кто изучает высшую математику. А оказать помощь студентам – это святое, по себе знаю, как трудно она осваивается. В реальности функции стандартных отклонений можно использовать для определения стабильности продаваемой продукции, создания цены, корректировки или формирования ассортимента, ну и других не менее полезных анализов ваших продаж.

В Excel используются несколько вариантов этой функции отклонения:

• Функция СТАНДОТКЛОНА – вычисляется отклонение по выборке текстовых и логических значений. При этом ложные логические и текстовые значения формула приравнивает к 0, а 1 будут равняться только истинные логические значения;
• Функция СТАНДОТКЛОН.В – производит оценку стандартного отклонения по выборке, при этом текстовые и логические значения игнорирует;
• Функция СТАНДОТКЛОН.Г – делает оценку отклонения по некой генеральной совокупности и как в предыдущей функции игнорируются текстовые и логические значения;
• Функция СТАНДОТКЛОНПА – также вычисляет по генеральной совокупности стандартное отклонение, но с учетом текстовых и логических значений. Равняться 1 будут только истинные логические значения, а ложные логические и текстовые значения будут приравнены к 0.

Математическая теория

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

Что же собственно делает стандартное отклонение? Оно производит оценку среднеквадратического отклонения случайной величины Х относительно её математического ожидания на основе несмещённой оценки её дисперсии. Согласитесь, звучит запутанно, но я думаю учащиеся поймут о чём собственно идет речь!

Для начала нам нужно определить «среднеквадратическое отклонение», что бы в дальнейшем произвести расчёт «стандартного отклонения», в этом нам поможет формула: Описать формулу возможно так: среднеквадратическое отклонение будет измеряться в тех же единицах что и измерения случайной величины и применяется при вычислении стандартной среднеарифметической ошибки, когда производятся построения доверительных интервалов, при проверке гипотез на статистику или же при анализе линейной взаимосвязи между независимыми величинами. Функцию определяют, как квадратный корень из дисперсии независимых величин.

Теперь можно дать определение и стандартному отклонению – это анализ среднеквадратического отклонения случайной величины Х сравнительно её математической перспективы на основе несмещённой оценки её дисперсии. Формула записывается так: Отмечу, что все две оценки предоставляются смещёнными. При общих случаях построить несмещённую оценку не является возможным. Но оценка на основе оценки несмещённой дисперсии будет состоятельной.

Практическое воплощение в Excel

Ну а теперь отойдём от скучной теории и на практике посмотрим, как работает функция СТАНДОТКЛОН. Я не буду рассматривать все вариации функции стандартного отклонения в Excel, достаточно и одной, но в примерах. А для примера рассмотрим, как определяется статистика стабильности продаж.

Для начала посмотрите на орфографию функции, а она как вы видите, очень проста:

• Число1, число2, … — являют собой генеральную совокупность значений и имеют только числовые значения или же ссылки на них. Формула поддерживает до 255 числовых значений.

Теперь создадим файл примера и на его основе рассмотрим работу этой функции. Так как для проведения аналитических вычислений необходимо использовать не меньше трёх значений, как в принципе в любом статистическом анализе, то и я взял условно 3 периода, это может быть год, квартал, месяц или неделя. В моем случае – месяц. Для наибольшей достоверности рекомендую брать как можно большое количество периодов, но никак не менее трёх. Все данные в таблице очень простые для наглядности работы и функциональности формулы.

Для начала нам необходимо посчитать среднее значение по месяцам. Будем использовать для этого функцию СРЗНАЧ и получится формула: =СРЗНАЧ(C4:E4). Теперь собственно мы и можем найти стандартное отклонение с помощью функции СТАНДОТКЛОН.Г в значении которой нужно проставить продажи товара каждого периода. Получится формула следующего вида: =СТАНДОТКЛОН.Г(C4;D4;E4). Ну вот и сделана половина дел. Следующим шагом мы формируем «Вариацию», это получается делением на среднее значение, стандартного отклонения и результат переводим в проценты. Получаем такую таблицу: Ну вот основные расчёты окончены, осталось разобраться как идут продажи стабильно или нет. Возьмем как условие что отклонения в 10% это считается стабильно, от 10 до 25% это небольшие отклонения, а вот всё что выше 25% это уже не стабильно. Для получения результата по условиям воспользуемся логической функцией ЕСЛИ и для получения результата напишем формулу:

С возрастом желание заработать переходит в желание сэкономить.
Михаил Жванецкий

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

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

Сначала рассмотрим дисперсию , затем стандартное отклонение .

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

Дисперсия выборки ( выборочная дисперсия, sample variance ) характеризует разброс значений в массиве относительно среднего .

Все 3 формулы математически эквивалентны.

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

В MS EXCEL 2007 и более ранних версиях для вычисления дисперсии выборки используется функция ДИСП() , англ. название VAR, т.е. VARiance. С версии MS EXCEL 2010 рекомендуется использовать ее аналог ДИСП.В() , англ. название VARS, т.е. Sample VARiance. Кроме того, начиная с версии MS EXCEL 2010 присутствует функция ДИСП.Г(), англ. название VARP, т.е. Population VARiance, которая вычисляет дисперсию для генеральной совокупности . Все отличие сводится к знаменателю: вместо n-1 как у ДИСП.В() , у ДИСП.Г() в знаменателе просто n. До MS EXCEL 2010 для вычисления дисперсии генеральной совокупности использовалась функция ДИСПР() .

Дисперсию выборки можно также вычислить непосредственно по нижеуказанным формулам (см. файл примера ) =КВАДРОТКЛ(Выборка)/(СЧЁТ(Выборка)-1) =(СУММКВ(Выборка)-СЧЁТ(Выборка)*СРЗНАЧ(Выборка)^2)/ (СЧЁТ(Выборка)-1) – обычная формула =СУММ((Выборка -СРЗНАЧ(Выборка))^2)/ (СЧЁТ(Выборка)-1 ) – формула массива

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

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

Дисперсия случайной величины

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

Для дисперсии случайной величины Х часто используют обозначение Var(Х). Дисперсия равна математическому ожиданию квадрата отклонения от среднего E(X): Var(Х)=E[(X-E(X)) 2 ]

Если случайная величина имеет дискретное распределение , то дисперсия вычисляется по формуле:

где x _i – значение, которое может принимать случайная величина, а μ – среднее значение ( математическое ожидание случайной величины ), р(x) – вероятность, что случайная величина примет значение х.

Если случайная величина имеет непрерывное распределение , то дисперсия вычисляется по формуле:

Для распределений, представленных в MS EXCEL , дисперсию можно вычислить аналитически, как функцию от параметров распределения. Например, для Биномиального распределения дисперсия равна произведению его параметров: n*p*q.

Примечание : Дисперсия, является вторым центральным моментом , обозначается D[X], VAR(х), V(x). Второй центральный момент — числовая характеристика распределения случайной величины, которая является мерой разброса случайной величины относительно математического ожидания .

Примечание : О распределениях в MS EXCEL можно прочитать в статье Распределения случайной величины в MS EXCEL .

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

Некоторые свойства дисперсии :

Var(Х+a)=Var(Х), где Х — случайная величина, а — константа.

Var(Х)=E[(X-E(X)) 2 ]=E[X 2 -2*X*E(X)+(E(X)) 2 ]=E(X 2 )-E(2*X*E(X))+(E(X)) 2 =E(X 2 )-2*E(X)*E(X)+(E(X)) 2 =E(X 2 )-(E(X)) 2

Это свойство дисперсии используется в статье про линейную регрессию .

Var(Х+Y)=Var(Х) + Var(Y) + 2*Cov(Х;Y), где Х и Y — случайные величины, Cov(Х;Y) — ковариация этих случайных величин.

Если случайные величины независимы (independent), то их ковариация равна 0, и, следовательно, Var(Х+Y)=Var(Х)+Var(Y). Это свойство дисперсии используется при выводе стандартной ошибки среднего .

Покажем, что для независимых величин Var(Х-Y)=Var(Х+Y). Действительно, Var(Х-Y)= Var(Х-Y)= Var(Х+(-Y))= Var(Х)+Var(-Y)= Var(Х)+Var(-Y)= Var(Х)+(-1) 2 Var(Y)= Var(Х)+Var(Y)= Var(Х+Y). Это свойство дисперсии используется для построения доверительного интервала для разницы 2х средних .

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

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

По определению, стандартное отклонение равно квадратному корню из дисперсии :

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

Вычислим стандартное отклонение для 2-х выборок: (1; 5; 9) и (1001; 1005; 1009). В обоих случаях, s=4. Очевидно, что отношение величины стандартного отклонения к значениям массива у выборок существенно отличается. Для таких случаев используется Коэффициент вариации (Coefficient of Variation, CV) — отношение Стандартного отклонения к среднему арифметическому , выраженного в процентах.

В MS EXCEL 2007 и более ранних версиях для вычисления Стандартного отклонения выборки используется функция =СТАНДОТКЛОН() , англ. название STDEV, т.е. STandard DEViation. С версии MS EXCEL 2010 рекомендуется использовать ее аналог =СТАНДОТКЛОН.В() , англ. название STDEV.S, т.е. Sample STandard DEViation.

Кроме того, начиная с версии MS EXCEL 2010 присутствует функция СТАНДОТКЛОН.Г() , англ. название STDEV.P, т.е. Population STandard DEViation, которая вычисляет стандартное отклонение для генеральной совокупности . Все отличие сводится к знаменателю: вместо n-1 как у СТАНДОТКЛОН.В() , у СТАНДОТКЛОН.Г() в знаменателе просто n.

Стандартное отклонение можно также вычислить непосредственно по нижеуказанным формулам (см. файл примера ) =КОРЕНЬ(КВАДРОТКЛ(Выборка)/(СЧЁТ(Выборка)-1)) =КОРЕНЬ((СУММКВ(Выборка)-СЧЁТ(Выборка)*СРЗНАЧ(Выборка)^2)/(СЧЁТ(Выборка)-1))

Другие меры разброса

Функция КВАДРОТКЛ() вычисляет с умму квадратов отклонений значений от их среднего . Эта функция вернет тот же результат, что и формула =ДИСП.Г( Выборка )*СЧЁТ( Выборка ) , где Выборка — ссылка на диапазон, содержащий массив значений выборки ( именованный диапазон ). Вычисления в функции КВАДРОТКЛ() производятся по формуле:

Функция СРОТКЛ() является также мерой разброса множества данных. Функция СРОТКЛ() вычисляет среднее абсолютных значений отклонений значений от среднего . Эта функция вернет тот же результат, что и формула =СУММПРОИЗВ(ABS(Выборка-СРЗНАЧ(Выборка)))/СЧЁТ(Выборка) , где Выборка — ссылка на диапазон, содержащий массив значений выборки.

Вычисления в функции СРОТКЛ () производятся по формуле: