Статистическая обработка данных

 

5.1. Вычисление основных характеристик выборки

 

Основными числовыми характеристиками выборочной совокупности являются: выборочное среднее, выборочная дисперсия, выборочное среднее квадратическое (или стандартное) отклонение, наименьшее и наибольшее значения, размах выборки, асимметрия, эксцесс.

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

Предположим, что статистические данные содержатся в блоке ячеек A1:J10, а значения вычисляемых характеристик должны располагаться в ячейках с G12 по G19, как показано на рис.9.

Рис.9. Расчет выборочных характеристик

 

Тогда используются следующие функции:

· выборочное среднее

G12 = СРЗНАЧ(A1:J10)

· выборочная дисперсия

G13 = ДИСП(A1:J10)

· выборочное среднее квадратическое отклонение

G14 = СТАНДОТКЛОН(A1:J10)

или

G14 = КОРЕНЬ(G13)

· наименьшее значение

G15 = МИН(A1:J10)

· наибольшее значение

G16 = МАКС(A1:J10)

· размах выборки

G17 = G16 - G15

· асимметрия

G18 = СКОС(A1:J10)

· эксцесс

G19 = ЭКСЦЕСС(A1:J10)

 

5.2. Формирование статистического ряда и графическое представление данных

 

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

· Наименьшее значение округляется в меньшую сторону, а наибольшее значение - в большую сторону до "хороших" чисел и .

* Выбирается количество групп k, удовлетворяющее неравенству 6£k£20; иногда оно определяется по формуле k=[5lgn]. Например, если объем выборки n=100, то k=10.

Находится шаг по формуле , где - длина промежутка, в котором содержатся статистические данные.

Определяются границы частичных интервалов:

.

В каждом интервале вычисляются средние значения .

Для каждого интервала находятся

- частоты , то есть число выборочных значений, попавших в интервал,

- относительные частоты ,

- накопленные частоты ,

- накопленные относительные частоты ,

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

 

Рис.10. Группировка статистических данных

 

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

A23 = 100, B23 = 100, C23 = 0, D23 = B23-C23, E23 = 10,

F23 = D23 / E23.

В ячейках A25:H25 указываются заголовки будущей таблицы. В этой таблице колонки A,B,C можно заполнить непосредственно, или заполнить две строки и скопировать их в последующие, так чтобы всего получилось k=10 строк. Колонку D можно заполнить, используя формулу:

D26 = (B26+C26) / 2

с последующим копированием в ячейки D27:D35.

Для заполнения колонки E следует выделить ячейки E26:E35 и обратиться к функции «Частота», указав массив статистических данных и массив правых границ интервалов:

{= ЧАСТОТА(A1:J10; C26:C35)}.

Одновременное нажатие клавиш Ctrl + Shift + Enter приведет к заполнению выделенных ячеек.

Заполнение колонки F производится по формуле:

F26 = E26 / $A$23

с последующим копированием в ячейки F27:F35.

Далее заполняются две ячейки колонки G по формулам

G26 = E26, G27 = G26 + E27

с последующим копированием G27 в ячейки G28:G35.

Колонка H заполняется по формуле:

H26 = G26 / $A$23

с последующим копированием в ячейки H27:H35.

Данные, собранные в таблицу (рис.10), нуждаются в наглядном представлении. Формами такого наглядного представления являются

* полигоны частот - графическая зависимость частот (относительных частот) от середин интервалов (рис.11),

*

кумуляты частот - графическая зависимость накопленных частот (накопленных относительных частот) от середин интервалов (рис.12).

Рис.11. Полигон частот Рис.12. Кумулята частот

 

5.3. Подбор походящего распределения вероятностей

 

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

Предположим, что случайная величина X имеет функцию распределения F(x). Будем называть это предположение гипотезой о виде распределения случайной величины X. Чтобы иметь полную информацию о распределении случайной величины, надо еще знать параметры этого распределения или их некоторые оценки. Как правило, параметры распределений берутся такими, чтобы математическое ожидание случайной величины X было бы равно выборочной средней, а среднее квадратическое отклонение случайной величины X было бы равно выборочному среднему квадратическому отклонению. Указанные выборочные характеристики находятся в ячейках G12 и G14 соответственно.

Откроем новый лист Excel и поместим эти значения в ячейки A2 и B2 соответственно (рис.13). Определим параметры равномерного, нормального, гамма распределений в соответствии с формулами (4)-(5) и запишем их в ячейки:

B5 = A2 - B2*КОРЕНЬ(3)

B6 = A2 + B2*КОРЕНЬ(3)

B8 = A2

B9 = B2

B11 = (A2/B2)^2

B12 = B2^2/A2

Далее построим таблицу, “шапка” которой располагается в ячейках A14:E14.

 

Рис.13

 

В ячейках A15:A24 содержатся середины частичных интервалов, взятые из ячеек D26:D35 предыдущего листа. В ячейках B15:B24 вычислены плотности относительных частот, как частное от деления относительных частот предыдущего листа (ячейки F26:F35) на шаг (ячейка $F$23).

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

C15 = ЕСЛИ(A15<$B$5;0;ЕСЛИ(A15<=$B$6;1/($B$6-$B$5);0))

D15 = НОРМРАСП(A15;$B$8;$B$9;ЛОЖЬ)

E15 = ГАММАРАСП(A15;$B$11;$B$12;ЛОЖЬ),

после чего они копируются в блок ячеек C16:E24.

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

Графическое изображение гистограммы и кривых различных распределений приведено на рис.14-16. При этом используется нестандартная диаграмма типа «график | гистограмма».

Рис.14. Сглаживание гистограммы плотностью равномерного распределения

Рис.15. Сглаживание гистограммы плотностью нормального распределения

 

Рис.16. Сглаживание гистограммы плотностью гамма-распределения

 

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

Используя критерий c², надо установить, верна ли принятая нами гипотеза о распределении случайной величины X, то есть о соответствии функции распределения F(x) экспериментальным данным, так, чтобы ошибка не превышала бы заданного уровня значимости a (вероятность того, что будет опровергнута правильная гипотеза).

Для применения критерия c² необходимо, чтобы частоты n_i, соответствующие каждому интервалу, были бы не меньше 5. Если это не так, рядом стоящие интервалы объединяются, а их частоты суммируются. В результате общее количество интервалов может уменьшиться до значения k^¢. Далее вычисляется следующая сумма

, (6)

где p_i - теоретическая вероятность того, что случайная величина X примет значение из интервала [a_i-1,a_i]. Мы предположили, что случайная величина X имеет функцию распределения F(x), поэтому p_i=F(a_i)-F(a_i-1). Образец расчетов по формуле (6) в Excel для трех распределений показан на рис.17.

Рис.17. Подбор распределения на основе критерия c²

 

В колонке A содержатся левые, а в колонке B правые границы интервалов. В колонке C - соответствующие частоты. Заметим, что интервалы с 5-го по 10-й объединены в один интервал, чтобы все частоты были бы не меньше пяти. Количество интервалов вместо k=10 стало равным k¢=5. В колонке D рассчитываются теоретические вероятности в зависимости от вида распределения. Как обычно, вычисляется одно значение, которое копируется в другие ячейки.

Для равномерного распределения

D45=ЕСЛИ(B52<$B$5;0;ЕСЛИ(B52<=$B$6;(B52-$B$5)/($B$6-$B$5);1))-

ЕСЛИ(A52<$B$5;0;ЕСЛИ(A52<=$B$6;(A52-$B$5)/($B$6-$B$5);1))

Для нормального распределения

D52 = НОРМРАСП(B52;$B$8;$B$9;ИСТИНА)-

НОРМРАСП(A52;$B$8;$B$9;ИСТИНА)

Для гамма-распределения

D59 = ГАММАРАСП(B59;$B$11;$B$12;ИСТИНА)-

ГАММАРАСП(A59;$B$11;$B$12;ИСТИНА)

В колонке E рассчитываются слагаемые соотношения (6) по формулам:

E45 = (C45-100*D45)^2/(100*D45),

которые копируются в другие ячейки колонки E.

Согласно (6) для каждого рассмотренного распределения определяются итоговые суммы:

E50 = СУММ(E45:E49)

E57 = СУММ(E52:E56)

E64 = СУММ(E59:E63),

которые соответственно равны 19,698; 14,214 и 1,789.

Гипотеза о виде закона распределения должна быть принята, если вычисленное значение достаточно мало, а именно, оно не превосходит критического значения , которое определяется по распределению c² в зависимости от заданного уровня значимости a и числа степеней свободы r=k¢-s-1. Здесь s - число неизвестных параметров распределения, которые были определены по выборке (для равномерного, нормального и гамма - распределений s=2). В данном примере r=k¢-s-1=5-3=2. Полагая a=0,05, критическое значение критерия c² в Excel рассчитывается по формуле:

E66 = ХИ2ОБР(0,05;2),

и, как следует из рис.17, оно равно 5,991.

Поскольку 1,789<5,991, то принимается гипотеза о том, что статистические данные имеют гамма-распределение с параметрами a=2,05 и b=9,64 соответственно.