Одним из основных инструментов статистического анализа является расчет среднего квадратичного отклонения. Данный показатель позволяет сделать оценку стандартного отклонения по выборке или по генеральной совокупности. Давайте узнаем, как использовать формулу определения среднеквадратичного отклонения в Excel.
Сразу определим, что же представляет собой среднеквадратичное отклонение и как выглядит его формула. Эта величина является корнем квадратным из среднего арифметического числа квадратов разности всех величин ряда и их среднего арифметического. Существует тождественное наименование данного показателя — стандартное отклонение. Оба названия полностью равнозначны.
Но, естественно, что в Экселе пользователю не приходится это высчитывать, так как за него все делает программа. Давайте узнаем, как посчитать стандартное отклонение в Excel.
Расчет в Excel
Рассчитать указанную величину в Экселе можно с помощью двух специальных функций СТАНДОТКЛОН.В (по выборочной совокупности) и СТАНДОТКЛОН.Г (по генеральной совокупности). Принцип их действия абсолютно одинаков, но вызвать их можно тремя способами, о которых мы поговорим ниже.
Способ 1: мастер функций
Способ 2: вкладка «Формулы»
Способ 3: ручной ввод формулы
Существует также способ, при котором вообще не нужно будет вызывать окно аргументов. Для этого следует ввести формулу вручную.
Как видим, механизм расчета среднеквадратичного отклонения в Excel очень простой. Пользователю нужно только ввести числа из совокупности или ссылки на ячейки, которые их содержат. Все расчеты выполняет сама программа. Намного сложнее осознать, что же собой представляет рассчитываемый показатель и как результаты расчета можно применить на практике. Но постижение этого уже относится больше к сфере статистики, чем к обучению работе с программным обеспечением.
Для расчетов средней геометрической простой используется формула:
Геометрическая взвешенная
Для определения средней геометрической взвешенной применяется формула:
редние диаметры колес, труб, средние стороны квадратов определяются при помощи средней квадратической.
Среднеквадратические величины используются для расчета некоторых показателей, например коэффициент вариации, характеризующего ритмичность выпуска продукции. Здесь определяют среднеквадратическое отклонение от планового выпуска продукции за определенный период по следующей формуле:
Эти величины точно характеризуют изменение экономических показателей по сравнению с их базисной величиной, взятое в его усредненной величине.
Квадратическая простая
Средняя квадратическая простая вычисляется по формуле:
Квадратическая взвешенная
Средняя квадратическая взвешенная равна:
22. Абсолютные показатели вариации включают:
размах вариации
среднее линейное отклонение
дисперсию
среднее квадратическое отклонение
Размах вариации (r)
Размах вариации - это разность между максимальным и минимальным значениями признака
Он показывает пределы, в которых изменяется величина признака в изучаемой совокупности.
Опыт работы у пяти претендентов на предшествующей работе составляет: 2,3,4,7 и 9 лет. Решение: размах вариации = 9 - 2 = 7 лет.
Для обобщенной характеристики различий в значениях признака вычисляют средние показатели вариации, основанные на учете отклонений от средней арифметической. За отклонение от средней принимается разность .
При этом во избежании превращения в нуль суммы отклонений вариантов признака от средней (нулевое свойство средней) приходится либо не учитывать знаки отклонения, то есть брать эту сумму по модулю , либо возводить значения отклонений в квадрат
Среднее линейное и квадратическое отклонение
Среднее линейное отклонение - этосредняя арифметическая из абсолютных отклонений отдельных значений признака от средней.
Среднее линейное отклонение простое:
Опыт работы у пяти претендентов на предшествующей работе составляет: 2,3,4,7 и 9 лет.
В нашем примере: лет;
Ответ: 2,4 года.
Среднее линейное отклонение взвешенное применяется для сгруппированных данных:
Среднее линейное отклонение в силу его условности применяется на практике сравнительно редко (в частности, для характеристики выполнения договорных обязательств по равномерности поставки; в анализе качества продукции с учетом технологических особенностей производства).
Среднее квадратическое отклонение
Наиболее совершенной характеристикой вариации является среднее квадратическое откложение, которое называют стандартом (или стандартным отклонение). Среднее квадратическое отклонение () равно квадратному корню из среднего квадрата отклонений отдельных значений признака отсредней арифметической:
Среднее квадратическое отклонение простое:
Среднее квадратическое отклонение взвешенное применяется для сгруппированных данных:
Между средним квадратическим и средним линейным отклонениями в условиях нормального распределения имеет место следующее соотношение: ~ 1,25.
Среднее квадратическое отклонение, являясь основной абсолютной мерой вариации, используется при определении значений ординат кривой нормального распределения, в расчетах, связанных с организацией выборочного наблюдения и установлением точности выборочных характеристик, а также при оценке границ вариации признака в однородной совокупности.
Инструкция
Пусть имеется несколько чисел, характеризующих -либо однородные величины. Например, результаты измереений, взвешиваний, статистических наблюдений и т.п. Все представленные величины должны измеряться одной и той же измерения. Чтобы найти квадратичное отклонение, проделайте следующие действия.
Определите среднее арифметическое всех чисел: сложите все числа и разделите сумму на общее количество чисел.
Определите дисперсию (разброс) чисел: сложите квадраты найденных ранее отклонений и разделите полученную сумму на количество чисел.
В палате лежат семь больных с температурой 34, 35, 36, 37, 38, 39 и 40 градусов Цельсия.
Требуется определить среднее отклонение от средней .
Решение:
« по палате»: (34+35+36+37+38+39+40)/7=37 ºС;
Отклонения температур от среднего (в данном случае нормального значения): 34-37, 35-37, 36-37, 37-37, 38-37, 39-37, 40-37, получается: -3, -2, -1, 0, 1, 2, 3 (ºС);
Разделите полученную раннее сумму чисел на их количество. Для точности вычисления лучше воспользоваться калькулятором. Итог деления является средним арифметическим значением слагаемых чисел.
Внимательно отнеситесь ко всем этапам расчета, так как ошибка хоть в одном из вычислений приведет к неправильному итоговому показателю. Проверяйте полученные расчеты на каждом этапе. Среднее арифметическое число имеет тот же измеритель, что и слагаемые числа, то есть если вы определяете среднюю посещаемость , то все показатели у вас будут «человек».
Данный способ вычисления применяется только в математических и статистических расчетах. Так, например, среднего арифметического значения в информатике имеет другой алгоритм вычисления. Среднее арифметическое значение является очень условным показателем. Оно показывает вероятность того или иного события при условии, что у него только один фактор либо показатель. Для наиболее глубокого анализа необходимо учитывать множество факторов. Для этого применяется вычисление более общих величин.
Среднее арифметическое - одна из мер центральной тенденции, широко используемая в математике и статистических расчетах. Найти среднее арифметическое число для нескольких значений очень просто, но у каждой задачи есть свои нюансы, знать которые для выполнения верных расчетов просто необходимо.
Количественных результатов проведенных подобных опытов.
Как найти среднее арифметическое число
Поиск среднего арифметического числа для массива чисел следует начинать с определения алгебраической суммы этих значений. К примеру, если в массиве присутствуют числа 23, 43, 10, 74 и 34, то их алгебраическая сумма будет равна 184. При записи среднее арифметическое обозначается буквой μ (мю) или x (икс с чертой). Далее алгебраическую сумму следует разделить на количество чисел в массиве. В рассматриваемом примере чисел было пять, поэтому среднее арифметическое будет равно 184/5 и составит 36,8.Особенности работы с отрицательными числами
Если в массиве присутствуют отрицательные числа, то нахождение среднего арифметического значения происходит по аналогичному алгоритму. Разница имеется только при рассчетах в среде программирования, или же если в задаче есть дополнительные условия. В этих случаях нахождение среднего арифметического чисел с разными знаками сводится к трем действиям:1. Нахождение общего среднего арифметического числа стандартным методом;
2. Нахождение среднего арифметического отрицательным чисел.
3. Вычисление среднего арифметического положительных чисел.
Ответы каждого из действий записываются через запятую.
Натуральные и десятичные дроби
Если массив чисел представлен десятичными дробями, решение происходит по методу вычисления среднего арифметического целых чисел, но сокращение результата производится по требованиям задачи к точности ответа.При работе с натуральными дробями их следует привести к общему знаменателю, который умножается на количество чисел в массиве. В числителе ответа будет сумма приведенных числителей исходных дробных элементов.
Используется в тех случаях, когда при замене индивидуальных значений признака на среднюю величину необходимо сохранить неизменной сумму квадратов исходных величин.
Главная сфера её использования – измерение степени колеблемости индивидуальных значений признака относительно средней арифметической (среднее квадратическое отклонение). Кроме этого, средняя квадратическая применяется в тех случаях, когда необходимо вычислить средний величину признака, выраженного в квадратных или кубических единицах измерения (при вычислении средней величины квадратных участков, средних диаметров труб, стволов и т. д.).
Средняя квадратическая рассчитывается в двух формах:
- как простая
как взвешенная
(4.22)
Все степенные средние различаются между собой значениями показателя степени.При этом, чем выше показатель степени, тем больше количественное значение среднего показателя :
Это свойство степенных средних называется свойством мажорантности средних.
Таким образом, выбор вида среднего показателя оказывает существенное влияние на его численную величину. Выбор вида средней определяется в каждом отдельном случае путем анализа исследуемой совокупности, изучения содержания явления. Степенная средняя выбрана правильно, если на всех этапах вычислений не меняется её логическая формула, т.е. реально сохраняется социально-экономическое содержание усредняемого признака.
Особый вид средних показателей – структурные средние. Они используются при изучении внутреннего строения рядов распределения значений признака. К ним относятся мода и медиана.
Мода и медиана характеризуют значение признака у статистической единицы, занимающей определенное положение в вариационном ряду.
Мода (Mo ) - наиболее часто встречаемое значение признака в совокупности. Мода широко используется в статистической практике при изучении покупательского спроса, регистрации цен и др.
Медиана ( Me ) - значение признака у статистической единицы, стоящей в середине ранжированного ряда и делящей совокупность на две равные по численности части.
Для дискретных вариационных
рядов Mo
и Me
выбираются в
соответствии с определениями: мода -
как значение признака с наибольшей
частотой\
n
i
; положение медианы
при нечетном объеме совокупности
определяется ее номером
,
гдеN
– объем статистической совокупности.
При четном объеме ряда медиана равна
средней из двух вариантов, находящихся
в середине ряда.
Медиану используют как
наиболее надежный показатель типичного
значения неоднородной
совокупности, так как она нечувствительна
к
крайним
значениям признака, которые могут
значительно отличаться от
основного массива
его значений. Кроме этого, медиана
находит
практическое
применение вследствие особого
математического свойства:
.
Рассмотрим определение моды и медианы на следующем примере:
Имеется ряд распределения рабочих участка по уровню квалификации. Данные приведены в таблице 4.4.
Таблица 4.4 - Распределения рабочих участка по уровню квалификации
|
Накопленная |
|||
Мода выбирается по максимальному значению частоты: при n max = 14, Mo = 4, т.е. чаще всего встречается 4-ый разряд. Для нахождения медианы Me определяются центральные единицы (N +1)/2 . Это 25 и 26-ая единицы. По накопленным частотам определяется группа, в которую попадают эти единицы. Это 4-ая группа, в которой значение признака равно 4. Таким образом, Me = 4, это означает, что у половины рабочих разряд ниже 4-го, а у другой – выше четвертого.
В интервальном ряду значения Mo и Me вычисляются более сложным путем.
Мода определяется следующим образом:
По максимальному значению частоты определяется интервал, в котором находится значение моды. Он называется модальным.
Внутри модального интервала значение моды вычисляется по формуле:
где
-
нижняя граница модального интервала;
a Мо - ширина модального интервала;
n Мо , n Мо-1 , n Мо+1 - соответственно частоты модального, предмодального (предшествующего модальному) и постмодального (следующего за модальным) интервалов.
Для расчета медианы в интервальных рядах используется следующий подход:
По накопленным частотам находится медианный интервал.
Медианным называется интервал, содержащий центральную единицу.
Внутри медианного интервала значение Me определяется по формуле:
(4.25)
где
-
нижняя граница медианного интервала;
a Ме -ширина медианного интервала;
N – объем статистической совокупности;
N Ме-1 - накопленная частота предмедианного интервала;
n Ме - частота медианного интервала.
Расчет моды и медианы для интервального ряда распределения рассмотрим на примере ряда распределения рабочих по стажу (табл. 4.5).
Таблица 4.5 - Распределение рабочих участка по стажу
|
Интервал |
а i |
n i |
N i |
||
|
|
|
||||
Расчет Mo :
Максимальная частота n max = 13, она соответствует четвертой группе, следовательно, модальным является интервал с границами 12 – 16 лет.
Моду рассчитаем по формуле:
Чаще всего встречаются рабочие со стажем работы около 13 лет.
Мода не находится в середине модального интервала, она смещена к его нижней границе, связано это со структурой данного ряда распределения (частота предмодального интервала значительно больше частоты постмодального интервала).
Расчет медианы:
По графе накопленных частот определяется медианный интервал. Он содержит 25 и 26-ую статистические единицы, которые находятся в разных группах – в 3-ей и 4-ой. Для нахождения Me можно использовать любую из них. Расчет проведем по 3-ей группе:
Такое же значение Me можно получить при её расчете по 4-ой группе:
При сдвоенном центре Me всегда находится на стыке интервалов, содержащих центральные единицы. Вычисленное значениеMe показывает, что у первых 25 рабочих стаж работы – менее 12 лет, а у оставшихся 25-ти, следовательно, - более 12 лет.
Моду можно определить графически по полигону распределения в дискретных рядах, по гистограмме распределения – в интервальных, а медиану - по кумуляте.
Для нахождения моды в интервальном ряду правую вершину модального прямоугольника нужно соединить с правым верхним углом предыдущего прямоугольника, а левую вершину – с левым верхним углом последующего прямоугольника. Абсцисса точки пересечения этих прямых и будет модой распределения.
Для определение медианы высоту наибольшей ординаты кумуляты, соответствующей общей численности совокупности, делят пополам. Через полученную точку проводят прямую, параллельную оси абсцисс, до пересечения ее с кумулятой. Абсцисса точки пересечения является медианой.
Кроме Mo иMe в вариантных рядах могут быть определены и другие структурные характеристики – квантили. Квантили предназначены для более глубокого изучения структуры ряда распределения.Квантиль – это значение признака, занимающее определенное место в упорядоченной по данному признаку совокупности. Различают следующие виды квантилей:
- квартили – значения признака, делящие упорядоченную совокупность на 4 равные части;
- децили – значения признака, делящие совокупность на 10 равных частей;
- перцентели - значения признака, делящие совокупность на 100 равных частей.
Таким образом, для характеристики положения центра ряда распределения можно использовать 3 показателя: среднее значение признака ,мода, медиана .
При выборе вида и формы конкретного показателя центра распределения необходимо исходить из следующих рекомендаций:
Для устойчивых социально-экономических процессов в качестве показателя центра используют среднюю арифметическую. Такие процессы характеризуются симметричными распределениями, в которых
= Me
= Mo
;
Для неустойчивых процессов положение центра распределения характеризуется с помощью Mo илиMe . Для асимметричных процессов предпочтительной характеристикой центра распределения является медиана, поскольку она занимает положение между средней арифметической и модой.
Стоит отметить, что у такого расчета дисперсии есть недостаток – она получается смещенной, т.е. ее математическое ожидание не равно истинному значению дисперсии. Подробней об этом . В то же время не все так плохо. При увеличении объема выборки она все-таки приближается к своему теоретическому аналогу, т.е. является асимптотически не смещенной. Поэтому при работе с большими размерами выборок можно использовать формулу выше.
Язык знаков полезно перевести на язык слов. Получится, что дисперсия — это средний квадрат отклонений. То есть вначале рассчитывается среднее значение, затем берется разница между каждым исходным и средним значением, возводится в квадрат, складывается и затем делится на количество значений в данной совокупности. Разница между отдельным значением и средней отражает меру отклонения. В квадрат возводится для того, чтобы все отклонения стали исключительно положительными числами и чтобы избежать взаимоуничтожения положительных и отрицательных отклонений при их суммировании. Затем, имея квадраты отклонений, мы просто рассчитываем среднюю арифметическую. Средний – квадрат – отклонений. Отклонения возводятся в квадрат, и считается средняя. Разгадка заключается всего в трех словах.
Однако в чистом виде, как, например, средняя арифметическая, или индекс, дисперсия не используется. Это скорее вспомогательный и промежуточный показатель, который необходим для других видов статистического анализа. У нее даже единицы измерения нормальной нет. Судя по формуле, это квадрат единицы измерения исходных данных. Без бутылки, как говорится, не разберешься.
{module 111}
Дабы вернуть дисперсию в реальность, то есть использовать в более приземленных целей, из нее извлекают квадратный корень. Получается так называемое среднеквадратичное отклонение (СКО) . Встречаются названия «стандартное отклонение» или «сигма» (от названия греческой буквы). Формула стандартного отклонения имеет вид:
Для получения этого показателя по выборке используют формулу:
Как и с дисперсией, есть и немного другой вариант расчета . Но с ростом выборки разница исчезает.
Среднеквадратичное отклонение, очевидно, также характеризует меру рассеяния данных, но теперь (в отличие от дисперсии) его можно сравнивать с исходными данными, так как единицы измерения у них одинаковые (это явствует из формулы расчета). Но и этот показатель в чистом виде не очень информативен, так как в нем заложено слишком много промежуточных расчетов, которые сбивают с толку (отклонение, в квадрат, сумма, среднее, корень). Тем не менее, со среднеквадратичным отклонением уже можно работать непосредственно, потому что свойства данного показателя хорошо изучены и известны. К примеру, есть такое правило трех сигм , которое гласит, что у данных 997 значений из 1000 находятся в пределах ±3 сигмы от средней арифметической. Среднеквадратичное отклонение, как мера неопределенности, также участвует во многих статистических расчетах. С ее помощью устанавливают степень точности различных оценок и прогнозов. Если вариация очень большая, то стандартное отклонение тоже получится большим, следовательно, и прогноз будет неточным, что выразится, к примеру, в очень широких доверительных интервалах.
Коэффициент вариации
Среднее квадратическое отклонение дает абсолютную оценку меры разброса. Поэтому чтобы понять, насколько разброс велик относительно самих значений (т.е. независимо от их масштаба), требуется относительный показатель. Такой показатель называется коэффициентом вариации и рассчитывается по следующей формуле:
Коэффициент вариации измеряется в процентах (если умножить на 100%). По этому показателю можно сравнивать самых разных явлений независимо от их масштаба и единиц измерения. Данный факт и делает коэффициент вариации столь популярным.
В статистике принято, что, если значение коэффициента вариации менее 33%, то совокупность считается однородной, если больше 33%, то – неоднородной. Мне здесь трудно что-то прокомментировать. Не знаю, кто и почему так определил, но это считается аксиомой.
Чувствую, что я увлекся сухой теорией и нужно привести что-то наглядное и образное. С другой стороны все показатели вариации описывают примерно одно и то же, только рассчитываются по-разному. Поэтому разнообразием примеров блеснуть трудно, Отличаться могут лишь значения показателей, но не их суть. Вот и сравним, как отличаются значения различных показателей вариации для одной и той же совокупности данных. Возьмем пример с расчетом среднего линейного отклонения (из ). Вот исходные данные:
И график для напоминания.
По этим данным рассчитаем различные показатели вариации.
Среднее значение – это обычная средняя арифметическая.
Размах вариации – разница между максимумом и минимумом:
Среднее линейное отклонение считается по формуле:
Стандартное отклонение:
Расчет сведем в табличку.
Как видно, среднее линейное и среднеквадратичное отклонение дают похожие значения степени вариации данных. Дисперсия – это сигма в квадрате, поэтому она всегда будет относительно большим числом, что, собственно, ни о чем не говорит. Размах вариации – это разница между крайними значениями и может говорить о многом.
Подведем некоторые итоги.
Вариация показателя отражает изменчивость процесса или явления. Ее степень может измеряться с помощью нескольких показателей.
1. Размах вариации – разница между максимумом и минимумом. Отражает диапазон возможных значений.
2. Среднее линейное отклонение – отражает среднее из абсолютных (по модулю) отклонений всех значений анализируемой совокупности от их средней величины.
3. Дисперсия – средний квадрат отклонений.
4. Среднеквадратичное отклонение – корень из дисперсии (среднего квадрата отклонений).
5. Коэффициент вариации – наиболее универсальный показатель, отражающий степень разброса значений независимо от их масштаба и единиц измерения. Коэффициент вариации измеряется в процентах и может быть использован для сравнения вариации различных процессов и явлений.
Таким образом, в статистическом анализе существует система показателей, отражающих однородность явлений и устойчивость процессов. Часто показатели вариации не имеют самостоятельного смысла и используются для дальнейшего анализа данных (расчет доверительных интервалов
