Среднее и стандартное отклонение в excel. Расчет среднего квадратичного отклонения в Microsoft Excel

Андрей Липов

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

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

Пример

Возьмем два ряда чисел.

a) 1,2,3,4,5,6,7,8,9 . Среднее - 5. Ст. отклонение = 2,7386

б) 20,1,7,1,15,-1,-20,4,18,5 . Среднее - 5. Ст. отклонение = 12,2066

Если не держать перед глазами всего ряда чисел, то по показателю стандартного отклонения видно, что в случае «б» величины гораздо сильнее разбрасываются вокруг своего среднего значения.

Грубо говоря, в ряде «б» значение равно 5 плюс-минус 12 (в среднем) - не точно, но раскрывает смысл.

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

Для расчета стандартного отклонения можно использовать формулу, позаимствованную из расчета стандартного отклонения доходности ПИФов :

Здесь N - количество величин,
ДОХсредн - среднее всех величин,
ДОХпериода - величина N.

В экселе соответствующая функция называется СТАНДОТКЛОН (или STDEV в английской версии программы).

Пошаговая инструкция такова:

1. Рассчитайте среднее значение для ряда чисел.
2. Для каждого значения определите разницу между средним и этим значением.
3. Вычислите сумму квадратов этих разниц.
4. Разделите получившуюся сумму на количество чисел в ряде.
5. Возьмите квадратный корень от получившегося в прошлом пункте числа.

Вашим друзьям будет полезна эта информация. Поделитесь с ними!

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

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

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

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

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

Вводите данные в любой табличный редактор (в нашем случае Excel), заполняя ячейки слева направо. Начинать следует с колонки «А». Заголовки вводите в строке сверху, а названия в тех же столбцах, которые относятся к заголовкам, только ниже. Затем дату и данные, которые подлежат расчету, справа от даты.

Этот документ сохраняете.

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

Вписываете знак «=» и прописываете далее формулу. Знак равенства обязателен. Иначе программа не посчитает предложенные данные. Формула вводится без пробелов.

Утилита выдаст названия нескольких формул. Выбираете «СТАНДОТКЛОН ». Это формула вычисления стандартного отклонения. Существует два вида расчета:

• с вычислением по выборке;
• с вычислением по генеральной совокупности.

Выбрав одну из них, указываете диапазон данных. Вся введенная формула будет выглядеть так: «=СТАНДОТКЛОН (В2: В5)».

Затем кликаете по кнопке «Enter ». Полученные данные появятся в отмеченном пункте.

Расчет среднего арифметического

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

• останется только выделить диапазон и кликнуть по кнопке «Ввод». А в ячейке теперь отобразится результат из взятых данных выше.

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

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

V= S/X, где S – это стандартное отклонение, а X – среднее значение.

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

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

Теперь выделяете ячейку, которую отвели под число для вычисления вариации. Открываете вкладку «Главная », если она не открыта. Кликаете по инструменту «Число ». Выбираете процентный формат.

Переходите к отмеченной ячейке и кликаете по ней дважды. Затем вводите знак равенства и выделяете пункт, куда вписан итог стандартного отклонения. Затем кликаете на клавиатуре по кнопке «слэш» или «разделить» (выглядит так: «/»). Выделяете пункт , куда вписано среднее арифметическое, и кликаете по кнопке «Enter». Должно получиться так:

А вот и результат после нажатия «Enter»:

Также для расчета коэффициента вариации можно использовать онлайн калькуляторы, например planetcalc.ru и allcalc.ru . Достаточно внести необходимые цифры и запустить расчет, после чего получить необходимые сведения.

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

Среднеквадратичное отклонение в Excel решается с помощью двух формул:

Простыми словами, извлекается корень из дисперсии. Как вычислить дисперсию рассмотрено ниже.

Среднее квадратичное отклонение является синонимом стандартного и вычисляется точное также. Выделяется ячейка для результата под числами, которые нужно рассчитать. Вставляется одна из функций, указанных на рисунке выше. Кликается кнопка «Enter ». Результат получен.

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

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

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

Дисперсия

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

Переменные принимают такие значения:

В Excel есть две функции, которые определяют дисперсию:

Чтобы произвести расчет, под числами, которые необходимо посчитать, выделяется ячейка. Заходите во вкладку вставки функции. Выбираете категорию «Статистические ». В выпавшем списке выбираете одну из функций и кликаете по кнопке «Enter».

Максимум и минимум

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

Чтобы вычислить максимум, выделяете весь диапазон необходимых чисел в таблице и отдельную ячейку, затем кликаете по значку «Σ» или «Автосумма ». В выпавшем окне выбираете «Максимум» и, нажав кнопку «Enter» получаете нужное значение.

Тоже самое делаете, чтобы получить минимум. Только выбираете функцию «Минимум».

Программа Excel высоко ценится как профессионалами, так и любителями, ведь работать с нею может пользователь любого уровня подготовки. Например, каждый желающий с минимальными навыками «общения» с Экселем может нарисовать простенький график, сделать приличную табличку и т.д.

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

Что это такое

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

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

Формула

Помогает рассчитать среднее квадратичное отклонение в excel формула, которая автоматически предусмотрена в программе Excel. Чтобы ее найти, необходимо найти в Экселе раздел формулы, а уже там выбрать ту, которая имеет название СТАНДОТКЛОН, так что очень просто.

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

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

Видео в помощь

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

Вычисляем дисперсию

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

Рассчитываем по генеральной совокупности

Чтобы вычислить мат. ожидание в программе будет применяться функция ДИСП.Г, а ее синтаксис выглядит следующим образом «=ДИСП.Г(Число1;Число2;…)».

Возможно применить максимум 255 аргументов, не более. Аргументами могут быть простые числа или ссылки на ячейки, в которых они указаны. Давайте рассмотрим, как посчитать дисперсию в Microsoft Excel:

1. Первым делом следует выделить ячейку, где будет отображаться итог вычислений, а далее кликнуть по кнопке «Вставить функцию».

2. Откроется оболочка управления функциями. Там нужно искать функцию «ДИСП.Г», которая может быть в категории «Статистические» или «Полный алфавитный перечень». Когда она будет найдена, следует выделить ее и кликнуть «ОК».

3. Запустится окно с аргументами функции. В нем нужно выделить строку «Число 1» и на листе выделить диапазон ячеек с числовым рядом.

4. После этого в ячейке, куда была введена функция будут выведены результаты расчетов.

Вот так несложно можно найти дисперсию в Excel.

Производим расчет по выборке

В данном случае выборочная дисперсия в Excel высчитывается с указанием в знаменателе не общего количества чисел, а на одно меньше. Это делается для более меньшей погрешности при помощи специальной функции ДИСП.В, синтаксис которой =ДИСП.В(Число1;Число2;…). Алгоритм действий:

• Как и в предыдущем методе нужно выделить ячейку для результата.
• В мастере функции следует найти «ДИСП.В» в категории «Полный алфавитный перечень» или «Статистические».

• Далее появится окно, и действовать следует также, как и в предыдущем методе.

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

Заключение

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

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

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

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

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

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

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

дисперсии выборки используется функция ДИСП() , англ. название 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, только в том случае, если все значения равны между собой и, соответственно, равны среднему значению . Обычно, чем больше величина дисперсии , тем больше разброс значений в массиве.

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

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

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

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

дисперсия вычисляется по формуле:

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

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

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

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

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

Var(aХ)=a 2 Var(X)

Var(Х)=E[(X-E(X)) 2 ]=E=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-х выборок: (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(Выборка-СРЗНАЧ(Выборка)))/СЧЁТ(Выборка) , где Выборка - ссылка на диапазон, содержащий массив значений выборки.

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