Содержание
- Определение среднего квадратичного отклонения
- Расчет в Excel
- Способ 1: мастер функций
- Способ 2: вкладка «Формулы»
- Способ 3: ручной ввод формулы
- Вопросы и ответы
Одним из основных инструментов статистического анализа является расчет среднего квадратичного отклонения. Данный показатель позволяет сделать оценку стандартного отклонения по выборке или по генеральной совокупности. Давайте узнаем, как использовать формулу определения среднеквадратичного отклонения в Excel.
Определение среднего квадратичного отклонения
Сразу определим, что же представляет собой среднеквадратичное отклонение и как выглядит его формула. Эта величина является корнем квадратным из среднего арифметического числа квадратов разности всех величин ряда и их среднего арифметического. Существует тождественное наименование данного показателя — стандартное отклонение. Оба названия полностью равнозначны.
Но, естественно, что в Экселе пользователю не приходится это высчитывать, так как за него все делает программа. Давайте узнаем, как посчитать стандартное отклонение в Excel.
Рассчитать указанную величину в Экселе можно с помощью двух специальных функций СТАНДОТКЛОН.В (по выборочной совокупности) и СТАНДОТКЛОН.Г (по генеральной совокупности). Принцип их действия абсолютно одинаков, но вызвать их можно тремя способами, о которых мы поговорим ниже.
Способ 1: мастер функций
- Выделяем на листе ячейку, куда будет выводиться готовый результат. Кликаем на кнопку «Вставить функцию», расположенную слева от строки функций.
- В открывшемся списке ищем запись СТАНДОТКЛОН.В или СТАНДОТКЛОН.Г. В списке имеется также функция СТАНДОТКЛОН, но она оставлена из предыдущих версий Excel в целях совместимости. После того, как запись выбрана, жмем на кнопку «OK».
- Открывается окно аргументов функции. В каждом поле вводим число совокупности. Если числа находятся в ячейках листа, то можно указать координаты этих ячеек или просто кликнуть по ним. Адреса сразу отразятся в соответствующих полях. После того, как все числа совокупности занесены, жмем на кнопку «OK».
- Результат расчета будет выведен в ту ячейку, которая была выделена в самом начале процедуры поиска среднего квадратичного отклонения.
Способ 2: вкладка «Формулы»
Также рассчитать значение среднеквадратичного отклонения можно через вкладку «Формулы».
- Выделяем ячейку для вывода результата и переходим во вкладку «Формулы».
- В блоке инструментов «Библиотека функций» жмем на кнопку «Другие функции». Из появившегося списка выбираем пункт «Статистические». В следующем меню делаем выбор между значениями СТАНДОТКЛОН.В или СТАНДОТКЛОН.Г в зависимости от того выборочная или генеральная совокупность принимает участие в расчетах.
- После этого запускается окно аргументов. Все дальнейшие действия нужно производить так же, как и в первом варианте.
Способ 3: ручной ввод формулы
Существует также способ, при котором вообще не нужно будет вызывать окно аргументов. Для этого следует ввести формулу вручную.
- Выделяем ячейку для вывода результата и прописываем в ней или в строке формул выражение по следующему шаблону:
=СТАНДОТКЛОН.Г(число1(адрес_ячейки1); число2(адрес_ячейки2);…)
или
=СТАНДОТКЛОН.В(число1(адрес_ячейки1); число2(адрес_ячейки2);…).
Всего можно записать при необходимости до 255 аргументов.
- После того, как запись сделана, нажмите на кнопку Enter на клавиатуре.
Урок: Работа с формулами в Excel
Как видим, механизм расчета среднеквадратичного отклонения в Excel очень простой. Пользователю нужно только ввести числа из совокупности или ссылки на ячейки, которые их содержат. Все расчеты выполняет сама программа. Намного сложнее осознать, что же собой представляет рассчитываемый показатель и как результаты расчета можно применить на практике. Но постижение этого уже относится больше к сфере статистики, чем к обучению работе с программным обеспечением.
Еще статьи по данной теме:
Помогла ли Вам статья?
Из предыдущей статьи мы узнали о таких показателях, как размах вариации, межквартильный размах и среднее линейное отклонение. В этой статье изучим дисперсию, среднеквадратичное отклонение и коэффициент вариации.
Дисперсия
Дисперсия случайной величины – это один из основных показателей в статистике. Он отражает меру разброса данных вокруг средней арифметической.
Сейчас небольшой экскурс в теорию вероятностей, которая лежит в основе математической статистики. Как и матожидание, дисперсия является важной характеристикой случайной величины. Если матожидание отражает центр случайной величины, то дисперсия дает характеристику разброса данных вокруг центра.
Формула дисперсии в теории вероятностей имеет вид:
То есть дисперсия — это математическое ожидание отклонений от математического ожидания.
На практике при анализе выборок математическое ожидание, как правило, не известно. Поэтому вместо него используют оценку – среднее арифметическое. Расчет дисперсии производят по формуле:
где
s2 – выборочная дисперсия, рассчитанная по данным наблюдений,
X – отдельные значения,
X̅– среднее арифметическое по выборке.
Стоит отметить, что у такого расчета дисперсии есть недостаток – она получается смещенной, т.е. ее математическое ожидание не равно истинному значению дисперсии. Подробней об этом здесь. Однако при увеличении объема выборки она все-таки приближается к своему теоретическому аналогу, т.е. является асимптотически не смещенной.
Простыми словами дисперсия – это средний квадрат отклонений. То есть вначале рассчитывается среднее значение, затем берется разница между каждым исходным и средним значением, возводится в квадрат, складывается и затем делится на количество значений в данной совокупности. Разница между отдельным значением и средней отражает меру отклонения. В квадрат возводится для того, чтобы все отклонения стали исключительно положительными числами и чтобы избежать взаимоуничтожения положительных и отрицательных отклонений при их суммировании. Затем, имея квадраты отклонений, просто рассчитываем среднюю арифметическую. Средний – квадрат – отклонений. Отклонения возводятся в квадрат, и считается средняя. Теперь вы знаете, как найти дисперсию.
Генеральную и выборочную дисперсии легко рассчитать в Excel. Есть специальные функции: ДИСП.Г и ДИСП.В соответственно.
В чистом виде дисперсия не используется. Это вспомогательный показатель, который нужен в других расчетах. Например, в проверке статистических гипотез или расчете коэффициентов корреляции. Отсюда неплохо бы знать математические свойства дисперсии.
Свойства дисперсии
Свойство 1. Дисперсия постоянной величины A равна 0 (нулю).
D(A) = 0
Свойство 2. Если случайную величину умножить на постоянную А, то дисперсия этой случайной величины увеличится в А2 раз. Другими словами, постоянный множитель можно вынести за знак дисперсии, возведя его в квадрат.
D(AX) = А2 D(X)
Свойство 3. Если к случайной величине добавить (или отнять) постоянную А, то дисперсия останется неизменной.
D(A + X) = D(X)
Свойство 4. Если случайные величины X и Y независимы, то дисперсия их суммы равна сумме их дисперсий.
D(X+Y) = D(X) + D(Y)
Свойство 5. Если случайные величины X и Y независимы, то дисперсия их разницы также равна сумме дисперсий.
D(X-Y) = D(X) + D(Y)
Среднеквадратичное (стандартное) отклонение
Если из дисперсии извлечь квадратный корень, получится среднеквадратичное (стандартное) отклонение (сокращенно СКО). Встречается название среднее квадратичное отклонение и сигма (от названия греческой буквы). Общая формула стандартного отклонения в математике следующая:
На практике формула стандартного отклонения следующая:
Как и с дисперсией, есть и немного другой вариант расчета. Но с ростом выборки разница исчезает.
Расчет cреднеквадратичного (стандартного) отклонения в Excel
Для расчета стандартного отклонения достаточно из дисперсии извлечь квадратный корень. Но в Excel есть и готовые функции: СТАНДОТКЛОН.Г и СТАНДОТКЛОН.В (по генеральной и выборочной совокупности соответственно).
Среднеквадратичное отклонение имеет те же единицы измерения, что и анализируемый показатель, поэтому является сопоставимым с исходными данными.
Коэффициент вариации
Значение стандартного отклонения зависит от масштаба самих данных, что не позволяет сравнивать вариабельность разных выборках. Чтобы устранить влияние масштаба, необходимо рассчитать коэффициент вариации по формуле:
По нему можно сравнивать однородность явлений даже с разным масштабом данных. В статистике принято, что, если значение коэффициента вариации менее 33%, то совокупность считается однородной, если больше 33%, то – неоднородной. В реальности, если коэффициент вариации превышает 33%, то специально ничего делать по этому поводу не нужно. Это информация для общего представления. В общем коэффициент вариации используют для оценки относительного разброса данных в выборке.
Расчет коэффициента вариации в Excel
Расчет коэффициента вариации в Excel также производится делением стандартного отклонения на среднее арифметическое:
=СТАНДОТКЛОН.В()/СРЗНАЧ()
Коэффициент вариации обычно выражается в процентах, поэтому ячейке с формулой можно присвоить процентный формат:
Коэффициент осцилляции
Еще один показатель разброса данных на сегодня – коэффициент осцилляции. Это соотношение размаха вариации (разницы между максимальным и минимальным значением) к средней. Готовой формулы Excel нет, поэтому придется скомпоновать три функции: МАКС, МИН, СРЗНАЧ.
Коэффициент осцилляции показывает степень размаха вариации относительно средней, что также можно использовать для сравнения различных наборов данных.
Таким образом, в статистическом анализе существует система показателей, отражающих разброс или однородность данных.
Ниже видео о том, как посчитать коэффициент вариации, дисперсию, стандартное (среднеквадратичное) отклонение и другие показатели вариации в Excel.
Поделиться в социальных сетях:
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше
Оценивает стандартное отклонение по выборке. Стандартное отклонение — это мера того, насколько широко разбросаны точки данных относительно их среднего.
Важно: Эта функция была заменена одной или несколькими новыми функциями, которые обеспечивают более высокую точность и имеют имена, лучше отражающие их назначение. Хотя эта функция все еще используется для обеспечения обратной совместимости, она может стать недоступной в последующих версиях Excel, поэтому мы рекомендуем использовать новые функции.
Дополнительные сведения о новом варианте этой функции Функция СТАНДОТКЛОН.В.
Синтаксис
СТАНДОТКЛОН(число1;[число2];…)
Аргументы функции СТАНДОТКЛОН описаны ниже.
-
Число1 Обязательный. Первый числовой аргумент, соответствующий выборке из генеральной совокупности.
-
Число2… Необязательный. Числовые аргументы 2—255, соответствующие выборке из генеральной совокупности. Вместо аргументов, разделенных точкой с запятой, можно использовать массив или ссылку на массив.
Замечания
-
Функция СТАНДОТКЛОН предполагает, что аргументы являются только выборкой из генеральной совокупности. Если данные представляют всю генеральную совокупность, то стандартное отклонение следует вычислять с помощью функции СТАНДОТКЛОНП.
-
Стандартное отклонение вычисляется с использованием «n-1» метода.
-
Аргументы могут быть либо числами, либо содержащими числа именами, массивами или ссылками.
-
Учитываются логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.
-
Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения, текст и значения ошибок в массиве или ссылке игнорируются.
-
Аргументы, которые представляют собой значения ошибок или текст, не преобразуемый в числа, вызывают ошибку.
-
Чтобы включить логические значения и текстовые представления чисел в ссылку как часть вычисления, используйте функцию СТАНДОТКЛОНА.
-
Функция СТАНДОТКЛОН вычисляется по следующей формуле:
где x — выборочное среднее СРЗНАЧ(число1,число2,…), а n — размер выборки.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
|
Данные |
||
|---|---|---|
|
Прочность |
||
|
1345 |
||
|
1301 |
||
|
1368 |
||
|
1322 |
||
|
1310 |
||
|
1370 |
||
|
1318 |
||
|
1350 |
||
|
1303 |
||
|
1299 |
||
|
Формула |
Описание (результат) |
Результат |
|
=СТАНДОТКЛОН(A3:A12) |
Стандартное отклонение предела прочности (27,46392) |
27,46392 |
Нужна дополнительная помощь?
Среднее арифметическое – один из самых популярных статистических методов, который рассчитывается повсеместно. Но сам по себе он абсолютно ненадежный. Многие знают поговорку, что один человек ест капусту, другой – мясо, а в среднем они оба едят голубцы. На примере средней зарплаты очень легко это изобразить. Несколько процентов людей, которые зарабатываются миллионы, не сильно повлияют на статистику, но способны значительно испортить ее объективность, завышая показатель на несколько десятков процентов.
Чем ниже разброс между значениями, тем больше можно доверять этому статистическому показателю. Поэтому настоятельно рекомендуется всегда вместе со средним арифметическим рассчитывать и стандартное отклонение. Сегодня мы разберемся, как правильно это делать средствами Microsoft Excel.
Содержание
- Среднеквадратичное отклонение — что это
- Расчет среднеквадратичного отклонения в Excel
- Метод 1. Ручной ввод формулы
- Метод 2. Вкладка «Формулы»
- Метод 3. Мастер функций
- Заключение
Среднеквадратичное отклонение — что это
Стандартное (или среднеквадратичное) отклонение – это квадратный корень из дисперсии. В свою очередь, под последним термином подразумевается степень разброса значений. Для получения дисперсии, и, как следствие, ее производного в виде стандартного отклонения, существует специальная формула, которая, впрочем, нам не так важна. Она довольно сложная по своей структуре, но при этом ее можно полностью автоматизировать средствами Excel. Главное – знать, какие параметры нужно передавать функции. В целом как для вычисления дисперсии, так и стандартного отклонения, аргументы используются одинаковые.
- Сначала мы получаем среднее арифметическое.
- После этого каждое исходное значение сопоставляется со средним и определяется разница между ними.
- После этого каждая разница возводится во вторую степень, после чего получившиеся результаты складываются между собой.
- Наконец, финальный шаг – деление получившегося значения на общее количество элементов в данной выборке.
Получив разницу между одним значением и средним арифметическим всей выборки, мы можем узнать расстояние к нему от определенной точки на координатной прямой. Начинающему человеку вся логика понятна равно до третьего шага. Зачем возводить значение в квадрат? Дело в том, что иногда разница может быть отрицательной, а нам нужно получить положительное число. И, как известно, минус на минус дает плюс. А далее нам нужно определить среднее арифметическое из получившихся значений. Дисперсия имеет несколько свойств:
- Если выводить дисперсию из одного числа, то она всегда будет равняться нулю.
- Если случайное число умножить на константу А, то дисперсия увеличится в количество раз, равное А в квадрате. Проще говоря, константу можно вынести за знак дисперсии и возвести его во вторую степень.
- Если к произвольному числу добавить константу А или же отнять ее, то дисперсия от этого не поменяется.
- Если два случайных числа, обозначаемых, к примеру переменными X и Y не зависят друг от друга, то в таком случае для них справедлива формула. D(X+Y) = D(X) + D(Y)
- Если же в предыдущую формулу внести изменения и пытаться определить дисперсию разницы этих значений, то она также будет составлять сумму этих дисперсий.
Среднеквадратическое отклонение – это математический термин, являющийся производным от дисперси. Получить его очень просто: достаточно извлечь квадратный корень из дисперсии.
Разница между дисперсией и стандартным отклонением находится сугубо в плоскости единиц измерения, если можно так выразиться. Стандартное отклонение является значительно более простым для считывания показателем, поскольку оно показывается не в квадратах числа, а непосредственно в значениях. Простыми словами, если в числовой последовательности 1,2,3,4,5 средним арифметическим является 3, то соответственно, стандартным отклонением будет число 1,58. Это говорит о том, что в среднем одно число отклоняется от среднего числа (которым является тройка в нашем примере), на 1,58.
Дисперсия же будет тем же самым числом, только возведенным в квадрат. В нашем примере – чуть меньше, чем 2,5. В принципе, можно использовать как дисперсию, так и стандартное отклонение для статистических расчетов, только надо четко знать, с каким именно показателем пользователь работает.
Расчет среднеквадратичного отклонения в Excel
У нас есть два главных варианта формулы. Первый рассчитывается по выборочной совокупности. Второй – по генеральной. Чтобы рассчитать стандартное отклонения по выборочной совокупности, необходимо использовать функцию СТАНДОТКЛОН.В. Если же необходимо осуществить расчет по генеральной совокупности, то надо пользоваться функцией СТАНДОТКЛОН.Г.
Отличие выборочной совокупности от генеральной заключается в том, что в первом случае идет обработка непосредственно тех данных, на основе которых рассчитывается среднее арифметическое и среднеквадратическое отклонение. Если же мы говорим про генеральную совокупность, то это весь набор количественных данных, связанных с исследуемым явлением. В идеале выборка должна быть полностью репрезентативной. То есть, в исследовании должны участвовать люди, которых можно соотнести с генеральной совокупностью в равных пропорциях. Например, если в условной стране 50% мужчин и 50% женщин, то такие же пропорции должны быть у выборки.
Следовательно, стандартное отклонение для генеральной совокупности может несколько отличаться от выборочной, поскольку во втором случае исходные цифры меньше. Но в целом, обе функции работают одинаково. Сейчас мы распишем, что нужно сделать для того, чтобы их вызвать. А сделать это можно сразу тремя способами.
Метод 1. Ручной ввод формулы
Ручной ввод – довольно сложный метод, на первый взгляд. Тем не менее, каждый должен им владеть, если хочет быть профессиональным пользователем Excel. Его преимущество в том, что не нужно вообще вызывать окно ввода аргументов. Если хорошо потренироваться, это будет значительно быстрее, чем пользоваться двумя остальными способами. Главное – чтобы пальцы были тренированные. В идеале каждый пользователь Excel должен владеть слепым методом, чтобы быстро вводить формулы и функции.
- Делаем левый клик мышью по той ячейке, в которой будет записываться формула для получения стандартного отклонения. Также можно ее вводить в качестве аргумента любой другой из функций. В таком случае нужно кликнуть по строке ввода формул, а потом начать ввод в том аргументе, куда результат должен выводиться.
- Общая формула следующая: =СТАНДОТКЛОН.Г(число1(адрес_ячейки1); число2(адрес_ячейки2);…). Если мы используем второй вариант, то все осуществляется в точности так же, только буква Г в названии функции меняется на В. Максимальное количество поддерживаемых аргументов – 255.
- После того, как ввод формулы будет закончен, подтверждаем свои действия. Для этого нужно нажать клавишу ввода.
Таким образом, для вычисления стандартного отклонения нам нужно использовать те же аргументы, что и для получения среднего арифметического. Все остальное программа сможет сделать самостоятельно. Также в качестве аргумента можно использовать целый диапазон значений, на основе которых будет осуществляться расчет среднеквадратического отклонения. Теперь давайте рассмотрим другие методы, которые будут более понятными для начинающего пользователя Excel. Но в долгосрочной перспективе от них нужно будет отказаться, поскольку:
- Ручной ввод формулы способен значительно сэкономить время. Пользователь Excel, который помнит формулу и ее синтаксис, имеет существенное преимущество перед тем человеком, который только начинает и долго ищет нужную функцию в перечне в мастере функций или на ленте. Кроме этого, сам по себе ввод с клавиатуры является гораздо более быстрым, чем использование мыши.
- Меньше устают глаза. Не нужно постоянно переключать фокус внимания с таблицы на окно, потом на еще одно окно, потом на клавиатуру, а потом снова на таблицу. Это же помогает существенно сэкономить время и силы, которые потом можно тратить на обработку реальной информации, а не техническое обслуживание формул.
- Ручной ввод формул гораздо более гибкий по сравнению с использованием двух следующих методов. Пользователь может сразу указать нужные ячейки диапазона, не выделяя его непосредственно или сразу посмотреть на всю таблицу, избегая риска, что ее перекроет диалоговое окно.
- Использование формул вручную является своеобразным мостиком к написанию макросов. Конечно, это не поможет выучить язык VBA, но зато формирует правильные привычки. Если человек привык давать команды компьютеру с помощью клавиатуры, ему будет значительно проще освоить любой другой язык программирования, в том числе, и для разработки макросов для электронных таблиц.
Но конечно, да. Использование других методов значительно лучше, если вы новичок, и только начинаете. Поэтому переходим к рассмотрению иных способов, как можно рассчитать стандартное отклонение.
Метод 2. Вкладка «Формулы»
Еще один метод, доступный пользователю, желающему получить стандартное отклонение из диапазона – воспользоваться вкладкой «Формулы» в главном меню. Давайте более подробно распишем, что нужно сделать для этого:
- Выделить ту ячейку, в которую мы хотим записывать результат.
- После этого находим на ленте вкладку «Формулы» и переходим в нее.
- Воспользуемся блоком «Библиотека функций». Там есть кнопка «Другие функции». В перечне, который будет, мы найдем пункт «Статистические». После этого выбираем, какую разновидность формулы мы собираемся использовать.
- После этого появляется окно ввода аргументов. В нем указываем все числа, ссылки на ячейки или диапазоны, которые будут принимать участие в расчетах. После того, как закончим, нажимаем кнопку «ОК».
Преимущества этого метода:
- Скорость. Данный способ довольно быстрый и позволяет ввести нужную формулу буквально в несколько кликов.
- Точность. Нет риска случайно написать не ту ячейку или написать не ту букву, а потом тратить время на переделку.
Можно сказать, что это способ номер два по хорошести после ручного ввода. НО третий метод также полезен в некоторых ситуациях.
Метод 3. Мастер функций
Мастер функций – еще один удобный метод ввода формул для новичков, которые еще не запомнили названия и синтаксис функций. Кнопка для запуска мастера функций находится возле строки ввода формул. Его главное преимущество для новичка на фоне предыдущих способов заключается в детальных подсказках программы, какая функция за что отвечает и какие аргументы вводить в какой последовательности. Она являет собой две буквы – fx. Нажимаем на нее. 
После этого появится перечень функций. Можно как попытаться найти в полном алфавитном перечне, так и открыть категорию «Статистические», где также можно найти этот оператор.
Мы можем увидеть в списке, что функция СТАНДОТКЛОН все еще присутствует. Это сделано для того, чтобы сделать старые файлы совместимыми с новой версией Excel. При этом настоятельно рекомендуется пользоваться новыми функциями, приведенными выше, потому что в один момент эта устаревшая функция может перестать поддерживаться.
После того, как мы нажмем «ОК», у нас будет возможность открыть окно аргументов. Каждый аргумент являет собой отдельное число, адрес на ячейку (если в ней содержится числовое значение) или диапазоны значений, которые будут использоваться для среднего арифметического и получения стандартного отклонения. После того, как мы введем все аргументы, нажимаем на кнопку «ОК». Данные будут занесены в ту ячейку, в которой мы вводили формулу.
Заключение
Таким образом, вычислить стандартное отклонение средствами Excel несложно. Да и сама функция является основой статистических расчетов, которая является интуитивно понятной. Ведь очевидно, что важно не только среднее значение, но и разброс значений, из которых выводится среднее арифметическое. Ведь если половина народа богатая, а половина – бедная, то среднего класса по факту и не будет. Но при этом если вывести среднее арифметическое, то окажется, что среднестатистический гражданин как раз и является представителем среднего класса. Но это звучит, как минимум, странно. В общем, успехов в использовании этой функции.
Оцените качество статьи. Нам важно ваше мнение:
Содержание
- Как в офисе.
- Правило трёх сигм
- Среднеквадратическое отклонение. Правило 3-сигма
Как в офисе.
Функция стандартное отклонение это уже из разряда высшей математики относящейся к статистики. В Excel существует несколько вариантов использования Функции стандартного отклонения это:
- Функция СТАНДОТКЛОНП.
- Функция СТАНДОТКЛОН.
- Функция СТАНДОТКЛОНПА
Данные функции в статистике продаж нам понадобятся для выявления стабильности продаж (анализ XYZ). Эти данные можно использовать как для ценообразования, так и для формирования (корректирования) ассортиментной матрицы и для других полезных анализов продаж, о которых я обязательно расскажу в следующих статьях.
Предисловие
Давайте посмотрим на формулы сначала математическим языком, а после (ниже по тексту) подробно разберем формулу в Excel и как получившийся результат применяется в анализе статистических данных продаж.
Итак, Стандартное отклонение — это оценка среднеквадратического отклонения случайной величины x относительно её математического ожидания на основе несмещённой оценки её дисперсии )))) Не пугайтесь не понятных слов, потерпите и Вы все поймете!
Чтобы рассчитать Стандартное отклонение, нам нужно выяснить Среднеквадратическое отклонение по формуле
Описание формулы: Среднеквадратическое отклонение измеряется в единицах измерения самой случайной величины и используется при расчёте стандартной ошибки среднего арифметического, при построении доверительных интервалов, при статистической проверке гипотез, при измерении линейной взаимосвязи между случайными величинами. Определяется как квадратный корень из дисперсии случайной величины
Теперь стандартное отклонение — оценка среднеквадратического отклонения случайной величины x относительно её математического ожидания на основе несмещённой оценки её дисперсии:
— i -й элемент выборки;
— среднее арифметическое выборки:
Следует отметить, что обе оценки являются смещёнными. В общем случае несмещённую оценку построить невозможно. Однако оценка на основе оценки несмещённой дисперсии является состоятельной.
Правило трёх сигм () — практически все значения нормально распределённой случайной величины лежат в интервале . Более строго — приблизительно с 0,9973 вероятностью значение нормально распределённой случайной величины лежит в указанном интервале (при условии, что величина истинная, а не полученная в результате обработки выборки). Мы же будем использовать округленный интервал 0,1
Если же истинная величина неизвестна, то следует пользоваться не , а s . Таким образом, правило трёх сигм преобразуется в правило трёх s . Именно это правило поможет нам определить стабильность продаж, но об этом чуть позже.
Теперь Функция стандартного отклонения в Excel
Надеюсь я не слишком Вас загрузил математикой? Возможно кому то данная информация потребуется для реферата или еще каких-нибудь целей. Теперь разжуем как эти формулы работают в Excel.
Для определения стабильности продаж нам не потребуется вникать во все варианты функций стандартного отклонения. Мы будем пользоваться всего одной:
Функция СТАНДОТКЛОНП
СТАНДОТКЛОНП(число1;число2;. )
Число1, число2. — от 1 до 30 числовых аргументов, соответствующих генеральной совокупности.
Теперь разберем на примере:
Давайте создадим книгу и импровизированную таблицу. Данный пример в Excel Вы скачаете в конце статьи.
Продолжение следует.
Подпишитесь на рассылку, что бы не пропустить самое интересное
И снова здравствуйте. Ну что!? Выдалась свободная минутка. Давайте продолжим?
И так стабильность продаж при помощи Функции СТАНДОТКЛОНП
Для наглядности возьмем несколько импровизированных товаров:
В аналитике, будь то прогноз, исследование или еще что то, что связано с статистикой всегда необходимо брать три периода. Это может быть неделя, месяц, квартал или год. Можно и даже лучше всего брать как можно больше периодов, но не менее трех.
Я специально показал утрированные продажи, где не вооруженным глазом видно, что продается стабильно, а что нет. Так проще будет понять как работают формулы.
И так у нас есть продажи, теперь нам нужно рассчитать средние значения продаж по периодам.
Формула среднего значения СРЗНАЧ(данные периода) в моем случае формула выглядит вот так =СРЗНАЧ(C6:E6)
Протягиваем формулу по всем товарам. Это можно сделать взявшись за правый угол выделенной ячейки и протянуть до конца списка. Или поставить курсор на столбец с товаром и нажать следующие комбинации клавиш:
Ctrl + Вниз курсор переместиться в коней списка.
Ctrl + Вправо, курсор переместиться в правую часть таблицы. Еще раз вправо и мы попадем на столбец с формулой.
Ctrl + Shift и нажимаем вверх. Так мы выделим область протягивания формулы.
И комбинация клавиш Ctrl + D протянет функцию там где нам надо.
Запомните эти комбинации, они реально увеличивают Вашу скорость работы в Excel, особенно когда Вы работаете с большими массивами.
Следующий этап, сама функция стандартного откланения, как я уже говорил мы будем пользоваться всего одной СТАНДОТКЛОНП
Прописываем функцию и в значениях функции ставим значения продаж каждого периода. Если у Вас продажи в таблице друг за другом можно использовать диапазон, как у меня в формуле =СТАНДОТКЛОНП(C6:E6) или через точку с запятой перечисляем нужные ячейки =СТАНДОТКЛОНП(C6;D6;E6)
Ну вот, пол дела сделано. Далее находим вариацию для этого Стандартное отклонение делим на среднее значение.
Вот все расчеты и готовы. Но как понять, что продается стабильно, а что нет? Просто проставим условность XYZ где,
Х — это стабильно
Y — с не большими отклонениями
Z — не стабильно
Для этого используем интервалы погрешности. если колебания происходят в пределах 10% будем считать что продажи стабильны.
Если в пределах от 10 до 25 процентов — это будет Y.
И если значения вариации превышает 25% — это не стабильность.
Что бы правильно задать буквы каждому товару, воспользуемся формулой ЕСЛИ подробнее про функцию ЕСЛИ читайте тут. В моей таблице данная функция будет выглядеть так:
Источник
Правило трёх сигм
«Правило трёх сигм» на самом деле очень приблизительное. Оно даёт хорошее приближение только для определённого объёма выборки. Конечно, есть теория, которая предлагает красивую многоэтажную формулу для распределения показателя размаха вариации. Мы поступим попроще и пойдём путём практического знакомства.
Нас интересует, как размах значений зависит от объёма выборки. Чем больше выборка, тем больше шанс, что может появиться очень редкое значение, которое сильно отклонится от среднего. Гораздо дальше, чем на три сигмы.
Попробуем оценить зависимость размаха от объёма выборки. Используем нормальное распределение с нашими параметрами среднего и сигмы. Сгенерируем выборку размером в миллион значений. Первое, что мы обнаруживаем, — ограничение встроенного генератора случайных чисел надстройки Excel: Integer is not valid. Миллион чисел сгенерировать в надстройке не удаётся.
Попробуем сгенерировать хотя бы десять тысяч чисел. На этот раз попытка удалась. Вычислим размах и выразим его в сигмах.
Размах в сигмах
Построим график: объём выборки — размах в сигмах.
Размах и объём выборки
Рассмотрим начало графика поподробнее. Для этого используем логарифмический масштаб. Вместо объёма выборки используем его логарифм. Вставим новый столбец и вычислим lg (n). Здесь нам пригодится функция LOG10.
На графике видно несколько ступенек. Скорее всего, это вызвано недостаточным качеством псевдослучайных чисел. Тем не менее, общая картина просматривается.
При выборке 10 размах равен трём сигмам. При выборке 100 размах 6 сигм. При выборке 10 000 размах равен 13 сигм.
Пользуясь случаем, проверим качество другого генератора случайных чисел Excel. Создадим новый лист и повторим наш эксперимент. Используем метод преобразования — возьмём равномерное распределение и пропустим его через обратное нормальное распределение.
RAND ()
СЛЧИС ()
позволяет сгенерировать случайное число с равномерным распределением в интервале от 0 до 1. Аргументов у функции нет.
Чтобы из равномерного распределения получить нормальное, вызываем функцию NORM.INV. Формат вызова:
=NORM.INV (probability, mean, standard_dev)
=НОРМ. ОБР (вероятность; среднее; стандартное_откл)
Функция работает по принципу x (p). Это обратное преобразование для функции распределения p (x).
probability — вероятность. В нашем случае это равномерно распределённая величина.
mean — среднее. В нашем примере это 250.
standard_dev — с.к. о. В нашем варианте это 20.
Таким образом, вызываем функция со следующими параметрами
=NORM.INV (B2,250,20)
Используем логарифмический масштаб, как в предыдущем варианте.
Размах в сигмах
Особенность функции генератора случайных чисел в том, что он генерирует новые числа (пересчитывает значение функции) при каждом сохранении файла. Попробуем сохранить файл несколько раз. Сделаем копию графиков и вставим их как рисунки на новый лист.
Графики немного отличаются друг от друга. Но при этом общая картина зависимости сохраняется. Чем больше выборка, тем больше размах.
Подведём итоги эксперимента. Правило трёх сигм хорошо работает для выборки объёмом в несколько сотен единиц. Для инженерной работы этого достаточно. А вот если взять хорошую, большую выборку, то размах может вырасти.
Источник
Среднеквадратическое отклонение. Правило 3-сигма
Среднеквадратическое отклонение вычисляется как квадратный корень из выборочной или исправленной дисперсии:
(3.9)
где s 2 и 
вычисляются по формулам (3.5) – (3.8).
В программе Excel среднеквадратическое отклонение (называется стандартное отклонение) вычисляется с помощью функций СТАНДОТКЛОН() и СТАНДОТКЛОНП(). При этом СТАНДОТКЛОНП() соответствует выборочной дисперсии, т.е. значению s, а СТАНДОТКЛОН() отвечает значению 
(квадратного корня из исправленной дисперсии).
Пример 3.3. Найти выборочную и исправленную дисперсии для выборки из 10 значений, записанных в диапазоне В2:В11 (рис.3.2).
Решение. В ячейку В12 введите формулу =ДИСП(B2:B11), а в ячейку В13 — формулу =ДИСПР(B2:B11).
Для контроля введем в ячейку С12 формулу
а в ячейку С13 — формулу
Мы увидим, что функция ДИСП() вычисляет исправленную дисперсию, а ДИСПР() — выборочную.
Введите в ячейку В14 формулу
а в ячейку В15 — формулу
Для контроля введите в ячейку С14 формулу =КОРЕНЬ(B12), а в ячейку С15 — формулу =КОРЕНЬ(B13).
Мы видим, что функция СТАНДОТКЛОНП() соответствует квадратному корню из выборочной дисперсии, а СТАНДОТКЛОН() отвечает значению квадратного корня из исправленной дисперсии.
Правило «3-сигма» применяется для приближенной проверки гипотезы о том, что выборка соответствует генеральной совокупности с нормальным законом распределения и выводится из следующего факта. Для нормальной случайной величины с математическим ожиданием a и среднеквадратическим отклонением σ вероятность попадания в интервал (a – 3σ, a + 3σ) равна 0,997. Это следует из формулы (1.7):
.
В нашем случае α = a – 3σ, β = a + 3σ. По формуле (1.7) имеем
.
Значение функции Лапласа можно найти по таблице или с помощью формулы в Excel. Введите в любой ячейке формулу =НОРМСТРАСП(3)-0,5. Получим значение 0,498650102, умножив это значение на 2, получим приближенно 0,997.
Правило «3-сигма».Если «почти все» элементы выборки попадают в интервал 
, где 
выборочное среднее, а 
стандартное отклонение, то генеральная совокупность имеет нормальное распределение.
1.4. Общая, межгрупповая и внутригрупповая дисперсии.
Правило сложения дисперсий
Пусть выборочные данные представлены в виде k групп (верхние индексы обозначают принадлежность группе, а не возведение в степень!):
— первая группа, объем равен 
— вторая группа, объем равен 
— k-ягруппа, объем равен 
Общий объем выборки равен 
Тогда можно вычислитьобщую, межгрупповую и внутригрупповую дисперсии в следующем порядке.
Групповые средние 
и групповые дисперсии 
вычисляются по формулам:
(3.10)
Внутригрупповой дисперсией называется взвешенная средняя групповых дисперсий:
. (3.11)
Внутригрупповая дисперсия отражает случайную вариацию, т. е. часть вариации, происходящую под влиянием неучтенных факторов и не зависящую от признака-фактора, положенного в основание группировки.
Общая средняя и общая дисперсия вычисляются по формулам:
(3.12)
Общая дисперсия измеряет вариацию признака во всей совокупности под влиянием всех факторов, обусловливающих эту вариацию.
Межгрупповая дисперсия вычисляется по формуле:
(3.14)
Межгрупповая дисперсия характеризует систематическую вариацию, т. е. различия в величине изучаемого признака, возникающие под влиянием признака-фактора, положенного в основание группировки.
Существует закон, связывающий три вида дисперсий:
(3.15)
Данное соотношение называютправилом сложения дисперсий.Согласно этому правилу общая дисперсия, возникающая под влиянием всех факторов, равна сумме дисперсий, возникающих под влиянием всех прочих факторов, и дисперсии, возникающей за счет группировочного признака.
Зная любые два вида дисперсий, можно определить или проверить правильность расчета третьего вида.
Для демонстрации равенства (3.15) рассмотрим следующий пример.
Пример 3.4. Определить групповые средние, групповые дисперсии, среднюю из групповых дисперсий (внутригрупповую дисперсию), межгрупповую дисперсию, общую дисперсию по данным табл. 3.1, в которой приведены данные о производительности труда трех бригад рабочих-токарей за десять дней работы (за каждый день приведено среднее число изготовленных за час деталей на одного рабочего и число работавших в этот день рабочих в бригаде).
Дать толкование полученным результатам.
| Дни | 1-я бригада | Число работавших | 2-я бригада | Число работавших | 3-я бригада | Число работавших |
Решение. Введите данные в программе Excel в ячейках A1:D11, как показано на рис.3.3.
Объедините следующие группы ячеек: А12:В12, А13:В13, А14:В14, А15:D15, А16:D16, А17:D17, А18:D18, А19:D19 и впишите в них тексты, как показано на рис. 3.3.
В ячейках С12, Е12, G12 просуммируйте соответствующие столбцы с помощью значка «Автосумма» или введите в эти ячейки формулы:
=СУММ(C2:C11), =СУММ(E2:E11), =СУММ(G2:G11).
В ячейку С13 введите формулу
Выделите ячейку С13 и маркером заполнения протяните вправо до ячейки G13, а затем удалите в ячейках D13, F13 содержимое с помощью клавиши «Delete». В ячейках E13, G13 должны получиться формулы:
В ячейки С14, Е14, G14 для вычисления групповых дисперсий введите, соответственно, формулы:
Для вычисления общей средней в ячейку Е15 введите формулу:
Для вычисления внутригрупповой дисперсии в ячейку Е16 введите формулу:
Для вычисления межгрупповой дисперсии в ячейку Е17 введите формулу:
Для вычисления суммы межгрупповой и внутригрупповой дисперсий в ячейку Е18 введите формулу =E16+E17.
Для вычисления общей дисперсии в ячейку Е19 введите формулу:
Результаты вычислений приведены на рис. 3.3. как видим, равенство (3.15) выполняется.
Дадим теперь толкование полученным результатам.
Средняя производительность на одного рабочего за десять дней составляет
в первой бригаде 13,89 деталей в час,
во второй бригаде — 14,52 детали в час,
в третьей бригаде — 16,64 деталей в час.
Средняя производительность на одного рабочего (по всему цеху) за десять дней составляет 15,39 деталей в час.
Межгрупповая дисперсия, равная 1,44, характеризует систематическую вариацию, т. е. различия в величине изучаемого признака, возникающие под влиянием признака-фактора, положенного в основание группировки, т.е., в данном случае, принадлежность определенной бригаде.
Внутригрупповая дисперсия равна 1,29 и отражает случайную вариацию, т. е. часть вариации, происходящую под влиянием неучтенных факторов, другими словами, остаточную дисперсию.
Источник