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

Определение

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

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

Математическое ожидание

Наш начинающий стрелок долго практиковался в стрельбе и заметил, что он попадает в разные значения с определенной вероятностью. Допустим, на основании большого количества выстрелов он выяснил, что попадает в 10 с вероятностью 15 %. Остальные значения получили свои вероятности:

• 9 - 25 %;
• 8 - 20 %;
• 7 - 15 %;
• 6 - 15 %;
• 5 - 5 %;
• 4 - 5 %.

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

Определим матожидание для нашего примера:

• M = 10 × 0,15 + 9 × 0,25 + 8 × 0,2 + 7 × 0,15 + 6 × 0,15 + 5 × 0,05 + 4 × 0,05
• M = 7,75

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

Дисперсия

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

D[X] = M − (M[X]) 2

Давайте рассчитаем разброс выстрелов для нашего случая:

• M = 10 2 × 0,15 + 9 2 × 0,25 + 8 2 × 0,2 + 7 2 × 0,15 + 6 2 × 0,15 + 5 2 × 0,05 + 4 2 × 0,05
• M = 62,85
• D[X] = M − (M[X]) 2 = 62,85 − (7,75) 2 = 2,78

Итак, наше отклонение равно 2,78. Это означает, что от области на мишени со значением 7,75 пулевые отверстия разбросаны на 2,78 балла. Однако в чистом виде значение дисперсии не используется - в результате мы получаем квадрат значения, в нашем примере это квадратный балл, а в других случаях это могут быть квадратные килограммы или квадратные доллары. Дисперсия как квадратная величина не информативна, поэтому она представляет собой промежуточный показатель для определения среднеквадратичного отклонения - героя нашей статьи.

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

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

S = sqrt(D) = sqrt(2,78) = 1,667

Мы получили баллы и теперь можем использовать их для связки с математически ожиданием. Наиболее вероятный результат выстрела в этом случае будет выражен как 7,75 плюс-минус 1,667. Этого достаточно для ответа, но так же мы можем сказать, что практически наверняка стрелок попадет в область мишени между 6,08 и 9,41.

Стандартное отклонение или сигма - информативный показатель, иллюстрирующий разброс величины относительно ее центра. Чем больше сигма, тем больший разброс демонстрирует выборка. Это хорошо изученный коэффициент и для нормального распределения известно занимательное правило трех сигм. Установлено, что 99,7 % значений нормально распределенной величины лежат в области плюс-минус трех сигм от среднего арифметического.

Рассмотрим на примере

Волатильность валютной пары

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

Наиболее волатильным активом рынка Форекс по праву считается валютная пара фунт/иена. Пусть теоретически в течение недели цена закрытия токийской биржи принимала следующие значения:

145, 147, 146, 150, 152, 149, 148.

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

Заключение

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

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

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

Что это такое

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

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

Формула

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

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

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

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

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

Что измеряет стандартное отклонение?

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

Но если внимательнее изучить деятельность второго магазина, можно убедится, что хотя среднее значение корректно, вариабельность стока очень высокая (от 10 до 58 у.е.). Таким образом, можно сделать вывод, что среднее значение не всегда правильно оценивает данные. Вот где на выручку приходит стандартное отклонение.

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

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

В случае с первым менеджером, стандартное отклонение составило 2. Это говорит нам о том, что каждое значение в выборке в среднем откланяется на 2 от среднего значения. Хорошо ли это? Давайте рассмотрим вопрос под другим углом – стандартное отклонение равное 0, говорит нам о том, что каждое значение в выборке равно его среднему значению (в нашем случае, 32,2). Так, стандартное отклонение 2 ненамного отличается от 0, и указывает на то, что большинство значений находятся рядом со средним значением. Чем ближе стандартное отклонение к 0, тем надежнее среднее. Более того, стандартное отклонение близкое к 0, говорит о маленькой вариабельности данных. То есть, величина стока со стандартным отклонением 2, указывает на невероятную последовательность первого менеджера.

В случае со вторым магазином, стандартное отклонение составило 18,9. То есть стоимость стока в среднем отклоняется на величину 18,9 от среднего значения от недели к неделе. Сумасшедший разброс! Чем дальше стандартное отклонение от 0, тем менее точно среднее значение. В нашем случае, цифра 18,9 указывает на то, что среднему значению (32,8 у.е. в неделю) просто нельзя доверять. Оно также говорит нам о том, что еженедельная величина стока обладает большой вариабельностью.

Такова концепция стандартного отклонения в двух словах. Хотя оно не дает представление о других важных статистических измерениях (Мода, Медиана…), фактически стандартное отклонение играет решающую роль в большинстве статистических расчетов. Понимание принципов стандартного отклонения прольет свет на суть многих процессов вашей деятельности.

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

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

Рассмотрим набор данных от 10 до 70 с шагом 10. Как видите, я уже рассчитал для них значение стандартного отклонения с помощью функции СТАНДОТКЛОН в ячейке H2 (оранжевым).

Ниже описаны шаги, которые предпринимает Excel, чтобы прийти к цифре 21,6.

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

Для начала Excel находит среднее значение выборки. В нашем случае, среднее получилось равным 40, которое на следующем шаге отнимают от каждого значения выборки. Каждую полученную разницу возводят в квадрат и суммируют. У нас получилась сумма равная 2800, которую необходимо разделить на количество элементов выборки минус 1. Так как у нас 7 элементов, получается необходимо 2800 разделить на 6. Из полученного результата находим квадратный корень, это цифра будет стандартным отклонением.

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

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

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

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

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

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

Андрей Липов

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

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

Пример

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

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 считают с помощью статистических функций. Можно также вручную ввести собственную формулу. Рассмотрим различные варианты.

Как найти среднее арифметическое чисел?

Чтобы найти среднее арифметическое, необходимо сложить все числа в наборе и разделить сумму на количество. Например, оценки школьника по информатике: 3, 4, 3, 5, 5. Что выходит за четверть: 4. Мы нашли среднее арифметическое по формуле: =(3+4+3+5+5)/5.

Как это быстро сделать с помощью функций Excel? Возьмем для примера ряд случайных чисел в строке:

Или: сделаем активной ячейку и просто вручную впишем формулу: =СРЗНАЧ(A1:A8).

Теперь посмотрим, что еще умеет функция СРЗНАЧ.

Найдем среднее арифметическое двух первых и трех последних чисел. Формула: =СРЗНАЧ(A1:B1;F1:H1). Результат:



Среднее значение по условию

Условием для нахождения среднего арифметического может быть числовой критерий или текстовый. Будем использовать функцию: =СРЗНАЧЕСЛИ().

Найти среднее арифметическое чисел, которые больше или равны 10.

Функция: =СРЗНАЧЕСЛИ(A1:A8;">=10")

Результат использования функции СРЗНАЧЕСЛИ по условию ">=10":

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

Внимание! Критерий поиска можно указать в ячейке. А в формуле сделать на нее ссылку.

Найдем среднее значение чисел по текстовому критерию. Например, средние продажи товара «столы».

Функция будет выглядеть так: =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). Диапазон – столбец с наименованиями товаров. Критерий поиска – ссылка на ячейку со словом «столы» (можно вместо ссылки A7 вставить само слово "столы"). Диапазон усреднения – те ячейки, из которых будут браться данные для расчета среднего значения.

В результате вычисления функции получаем следующее значение:

Внимание! Для текстового критерия (условия) диапазон усреднения указывать обязательно.

Как посчитать средневзвешенную цену в Excel?

Как мы узнали средневзвешенную цену?

Формула: =СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).

С помощью формулы СУММПРОИЗВ мы узнаем общую выручку после реализации всего количества товара. А функция СУММ - сумирует количесвто товара. Поделив общую выручку от реализации товара на общее количество единиц товара, мы нашли средневзвешенную цену. Этот показатель учитывает «вес» каждой цены. Ее долю в общей массе значений.

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

Различают среднеквадратическое отклонение по генеральной совокупности и по выборке. В первом случае это корень из генеральной дисперсии. Во втором – из выборочной дисперсии.

Для расчета этого статистического показателя составляется формула дисперсии. Из нее извлекается корень. Но в Excel существует готовая функция для нахождения среднеквадратического отклонения.

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

среднеквадратическое отклонение / среднее арифметическое значение

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

СТАНДОТКЛОНП (диапазон значений) / СРЗНАЧ (диапазон значений).

Коэффициент вариации считается в процентах. Поэтому в ячейке устанавливаем процентный формат.