Расчет описательных характеристик статистической совокупности с помощью MS Office Excel

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

Показатели описательной статистики можно разбить на несколько групп.

1. Показатели положения описывают положение данных на числовой оси. Примеры таких показателей: минимальный и максимальный элементы выборки (первый и последний члены вариационного ряда), верхний и нижний квартили (ограничивают зону, в которую попадают 50% центральных элементов выборки). Сведения о середине совокупности могут дать средняя арифметическая, средняя гармоническая, медиана и другие характеристики.

2. Показатели разброса описывают степень разброса данных относительно своего центра. К ним, в первую очередь, относятся: дисперсия, стандартное отклонение, размах выборки (разность между максимальным и минимальным элементами), межквартильный размах (разность между верхним и нижним квартилем), эксцесс и т.п. Эти показатели определяют, насколько кучно основная масса данных группируется около центра.

3. Показатели асимметрии характеризуют симметрию распределения данных около своего центра. К ним относят коэффициент асимметрии, положение медианы относительно среднего и т.п.

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

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

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

Надстройка Пакет анализа в MS Office Excel содержит различные статистические процедуры, предоставляющие широкий спектр средств для проведения статистического анализа (Приложение 2).

 

Описательная статистика

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

На рисунке 4.1 показан рабочий лист, содержащий три ряда данных (три независимые выборки, имеющие разные распределения), и диалоговое окно «Описательная статистика».

В диалоговом окне данного режима (рис. 4.1) задаются следующие параметры:

1. Входной интервал – указывается диапазон ячеек, в котором содержатся данные.

2. Группирование - демонстрируется, сгруппированы ли данные, и если сгруппированы, то по столбцам или строкам.

3. Метки в первой строке/Метки в первом столбце – устанавливается, если задается входной диапазон данных вместе с заголовками.

4. Выходной интервал/Новый рабочий лист/Новая рабочая книга – указывается, куда будут выводиться результаты расчетов.

5. Итоговая статистика — устанавливается в активное состояние, если в выходном диапазоне необходимо получить по одному полю для всех показателей описательной статистики.

 

Рисунок 4.1. Три выборки и диалоговое окно «Описательная статистика»

 

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

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

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

Если имеются выборки разных размеров, то средство «Описательная статистика» правильно определяет размеры выборок, игнорируя пустые ячейки.

На рисунке 4.2 показан рабочий лист с результатами расчетов.

 

Рис.4.2. Результаты работы средства «Описательная статистика»

 

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

Таблица 4.2

Выборочные характеристики, вычисляемые средством «Описательная статистика»

 

№	Числовая характеристика	Описание
1	Среднее	Выборочное среднее; вычисляется по формуле . Функция СРЗНАЧ
2	Стандартная ошибка	Оценка среднеквадратического отклонения выборочного среднего; вычисляется по формуле
3	Медиана	Значение медианы, т.е. квантиля порядка 0,5. Функция МЕДИАНА
4	Мода	Значение моды. Вычисляется также, как и функцией МОДА. Если нет одинаковых выборочных значений, то возвращается значение ошибки #Н/ Д.

 

 

5	Стандартное отклонение	Оценка среднеквадратического отклонения генеральной совокупности. Вычисляется по формуле "исправленного" среднеквадратического отклонения. Функция СТАНДОТКЛОН
6	Дисперсия выборки	Оценка дисперсии генеральной совокупности. Функция ДИСП
7	Эксцесс	Выборочный коэффициент эксцесса. Функция ЭКСЦЕСС
8	Асимметричность	Выборочный коэффициент асимметрии. Функция СКОС.
9	Интервал	Размах выборки. Вычисляется как разность между максимальным и минимальным выборочными значениями
10	Минимум	Минимальное выборочное значение. Функция МИН.
11	Максимум	Максимальное выборочное значение. Функция МАКС
12	Сумма	Сумма выборочных значений. Функция СУММ
13	Счет	Объем выборки. Функция СЧЁТ
14	Наибольший (К)	К-е наибольшее значение. Если К = 1, то выводится максимальное выборочное значение. Функция НАИБОЛЬШИЙ
15	Наименьший (К)	К-е наименьшее значение. Если К = 1, то выводится минимальное выборочное значение. Функция НАИМЕНЬШИЙ
16	Уровень надежности (g %)	Граница доверительного интервала для неизвестного математического ожидания с доверительным уровнем g %; доверительный интервал строится как выборочное среднее плюс-минус данное значение. Граница вычисляется с помощью распределения Стьюдента, т.е. здесь неявно используется предположение о нормальности распределения генеральной совокупности, Поэтому к данному показателю следует относиться осторожно, особенно при малых выборках.

Ранг и перцентиль

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

Применим это средство для данных Выборки 1 (рис.4.1). Для этого выберем в Пакете анализа Ранг и перцентиль (рис.4.3).

 

Рисунок 4.3. Средство анализа данных Ранг и перцентиль

 

На рисунках 4.4 и 4.5 показаны диалоговое окно Ранг и перцентиль и исходные данные.

 

Рисунок 4.4. Исходные данные	Рисунок 4.5. Диалоговое окно Ранг и перцентиль

 

Результат применения этого средства представлен на рисунке 4.6. Итоговая таблица (рис.4.6) содержит порядковый номер выборочного значения, столбец выборочных значений, отсортированных в порядке убывания, столбец рангов и столбец процентных рангов этих значений, причем наибольшему значению присваивается ранг 1 и процентный ранг 100%, а наименьшему – наибольший ранг и процентный ранг, равный 0%.

 

Рисунок 4.6. Результаты вычислений Ранг и перцентиль

 

Пример 4.1.

Рассмотрим данные примера 2.2 - значения процентных ставок по кредитам по 30 коммерческим банкам (табл.2.7). Определим для этой совокупности описательные статистики, ранги и перцентили.

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

1. Входной интервал – указывается диапазон ячеек $A$1:$A$31.

2. Группирование - по столбцам.

3. Метки в первой строке – устанавливается, так как задается входной диапазон данных вместе с заголовками.

4. Выходной интервал – ячейка $B$1 (достаточно указывать адрес ячейки, которая определяет верхний левый угол выходного диапазона).

5. Итоговая статистика — устанавливается в активное состояние.

6. Уровень надежности, К-ый наибольший и К-ый наименьший – устанавливается (не является необходимым, в дальнейшем можно оставлять эти опции неактивными).

 

Рисунок 4.7. Диалоговое окно Описательная статистика

 

Результаты расчетов представлены на рисунке 4.8.

 

Рисунок 4.8. Описательные статистики

 

Выводы: По совокупности из 30 банков получены следующие результаты.

Средние. Средняя процентная ставка банка составила 17,99% (среднее); при этом наибольшее число банков в выборке имеет ставку в размере 17,5% (мода); у половины банков размер процентной ставки ниже 17,55%, а у второй половины – больше (медиана).

Вариация. Значения процентной ставки у каждого банка в среднем отклоняются от средней ставки (17,99%) на 3,55% в большую и меньшую стороны (стандартное отклонение). Минимальная ставка по выборке составила 11% (минимум), а максимальная – 26% (максимум), в результате размах вариации составил 15% (интервал).

Распределение. Распределение значений процентной ставки плосковершинное (эксцесс меньше 0) и смещено вправо (асимметричность больше 0).

 

Определим ранги значений процентной ставки, рассчитаем перцентили. Для этого выберем в Пакете анализа Ранг и перцентиль, установим следующие параметры (рис.4.9).

Рисунок 4.9. Диалоговое окно средства Ранг и перцентиль

 

Результаты представлены на рисунке 4.10.

 

Рисунок 4.10. Результаты расчетов

 

Выводы.

По совокупности из 30 банков получены следующие результаты.

Ранг. Четвертый банк имеет наименьшую ставку процента (последний ранг и ставка 11%), а 26-ой банк – наибольшую ставку (первый ранг и ставка 26%).

Перцентиль. Охарактеризуем структуру ряда: например, 5% совокупности банков имеет ставку в размере меньше 13,6% (смотрим на последний столбец – третье значение снизу отсекает 6,80% совокупности, второе снизу – 3,40%, то есть два последних банка со значениями ставки 11% и 12,2% попадают в эти пять процентов, с процентной ставкой меньше, чем 13,6%). Аналогично – 50% совокупности банков имеет ставку меньше, чем 17,6%, то есть до 17,5% включительно (получили значение медианы).