Расчет показателей вариации, структурных средних велечин

Расчет показателей  вариации в Excel

расчет в Excel следующих показателей вариации:

- максимальное и минимальное значение

- среднее линейное отклонение

- дисперсия (по генеральной совокупности и по выборке)

- стандартное отклонение (по генеральной совокупности и по выборке)

- коэффициент вариации

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

Максимальное  и минимальное значение

Начнем с формул максимума и минимума. Что такое максимальное и минимальное значение, уверен, знают почти все. Максимум – самое большое значение из анализируемого набора данных, минимум – самое маленькое (может быть и отрицательным числом). Это крайние значения в совокупности данных, обозначающие границы их вариации. Примеры реального использования каждый может придумать сам – их полно. Это и минимальные/максимальные цены на что-нибудь, и выбор наилучшего или наихудшего решения задачи, и всего, чего угодно. Минимум и максимум – весьма информативные показатели. Давайте теперь их рассчитаем в Excel.

Как нетрудно догадаться, делается сие элементарно – как два клика об асфальт. Если вызывать формулу через Мастера функций, то из списка функций следует выбрать: МАКС – для расчета максимального значения, МИН – для расчета минимального значения. Для облегчения поиска список всех функций можно отфильтровать по категории «Статистические».

 

Выбираем нужную функцию, в следующем окошке указываем  диапазон данных (в котором ищется максимальное или минимальное значение) и жмем «ОК». Воля!

Функции МАКС и МИН достаточно часто используются, поэтому разработчики Экселя предусмотрительно добавили соответствующие кнопки в панель инструментов. Они находятся там же, где сумма и среднее значение – в разворачивающемся списке.

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

Среднее линейное отклонение

Среднее линейное отклонение, напоминаю, представляет собой среднее из абсолютных (по модулю) отклонений от средней величины в анализируемой совокупности данных. Математическая формула имеет вид:

где

a – среднее линейное отклонение,

x – анализируемый показатель, с черточкой сверху – среднее значение показателя,

n – количество значений в анализируемой совокупности данных.

В Excel эта функция называется =СРОТКЛ(). Вызов из панели инструментов отсутствует (используется редко), поэтому искать придется в Мастере функций.

После выбора функции СРОТКЛ указываем диапазон данных, по которому должен произойти расчет. Нажимаем «ОК». Наслаждаемся результатом.

 

 

Дисперсия

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

где

D – дисперсия,

x – анализируемый показатель, с черточкой сверху – среднее значение показателя,

n – количество значений в анализируемой совокупности данных.

Excel также предлагает готовую функцию для расчета генеральной дисперсии =ДИСП.Г().

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

в Excel выборочная дисперсия рассчитывает через функцию =ДИСП.В().

Выбираем в Мастере  функций нужную дисперсию (генеральную  или выборочную), указываем диапазон, жмем кнопку «ОК». Полученное значение может оказаться очень большим  из-за предварительного возведения отклонений в квадрат, поэтому дисперсия  сама по себе мало о чем говорит. Ее обычно используют для дальнейших расчетов.

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

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

- стандартное отклонение  по генеральной совокупности =СТАНДОТКЛОН.Г()

- стандартное отклонение  по выборке =СТАНДОТКЛОН.В().

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

Коэффициент вариации

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

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

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

=СТАНДОТКЛОН.Г()/СРЗНАЧ()

В скобках должен быть указан диапазон данных. При необходимости  используется стандартное отклонение по выборке (СТАНДОТКЛОН.В).

Коэффициент вариации обычно выражается в процентах, поэтому  ячейку с формулой можно обрамить процентным форматом. Нужная кнопка находится  на панели инструментов в закладке «Главная»:

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

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

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

Стандартное отклонение (оценка среднеквадратического отклонения случайной величины x относительно её математического ожидания на основе несмещённой оценки её дисперсии):

где — дисперсия; — i-й элемент выборки; — объём выборки; — среднее арифметическое выборки:

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

Правило трёх сигм

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

Правило трёх сигм ( ) — практически все значения нормально распределённой случайной величины лежат в интервале . Более строго — не менее чем с 99,7 % достоверностью значение нормально распределенной случайной величины лежит в указанном интервале (при условии, что величина истинная, а не полученная в результате обработки выборки).

Если же истинная величина неизвестна, то следует пользоваться не , а s. Таким образом, правило трёх сигм преобразуется в правило трёх s.

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

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

Например, у нас есть три  числовых множества: {0, 0, 14, 14}, {0, 6, 8, 14} и {6, 6, 8, 8}. У всех трёх множеств средние значения равны 7, а среднеквадратические отклонения, соответственно, равны 7, 5 и 1. У последнего множества среднеквадратическое отклонение маленькое, так как значения в множестве сгруппированы вокруг среднего значения; у первого множества самое большое значение среднеквадратического отклонения — значения внутри множества сильно расходятся со средним значением.

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

Медиана в статистке

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

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

где Хm — нижняя граница медианного интервала; 
im — медианный интервал; 
Sme— сумма наблюдений, которая была накоплена до начала медианного интервала; 
fme — число наблюдений в медианном интервале.

Свойства медианы

1. Медиана не зависит от тех значений признака, которые расположены по обе стороны от нее.
2. Аналитические операции с медианой весьма ограничены, поэтому при объединении двух распределений с известными медианами невозможно заранее предсказать величину медианы нового распределения.
3. Медиана обладает свойством минимальности. Его суть заключается в том, что сумма абсолютных отклонений значений х, от медианы представляет собой минимальную величину по сравнению с отклонением X от любой другой величины

Графическое определение медианы

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

Определение моды в статистике

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

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

где ХМо — нижняя граница модального интервала; 
imo — модальный интервал; 
fм0, fм0-1,, fм0+1 - частоты в модальном, предыдущем и следующем за модальным интервалах. 
Модальный интервал определяется по наибольшей частоте. 
Мода широко используется в статистической практике при анализе покупательного спроса, регистрации цен и т. д.

Соотношения между средней арифметической, медианой и модой

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Используемая  литература:

1 Балииова B.C. Статистика в вопросах и ответах: Учеб. пособие. — М.: ТК. Велби, Изд-во Проспект, 2004. — 344 с.

 

2 http://statanaliz.info/rabotaem-v-excel/12-formuly-excel/36-raschet-pokazateley-variatsyi-v-excel.html

 

3 Елисеева И.И., Юзбашев М.М. Общая теория статистики: Учебник - 4-е изд., перераб. и доп. - М.: Финансы и статистика, 2002. - 480 с: ил.. - 480 с:

ил.

4 Прикладная статистика: Основы моделирования и первичная обработка данных: Справ. изд./С. А. Айвазян, И. С. Енюков, Л. Д. Мешал-кин. - М.: Финансы и статистика, 1983. - 471с.