Выполнение задания в ППП MS Excel

1. Группировка

Значения каждого из трех признаков должны быть упорядочены, что реализуется с помощью сортировки (отдельно каждого из признаков): выбираем вкладку ДАННЫЕ – СОРТИРОВКА (традиционно по возрастанию).

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

где R – размах вариации, ;

- максимальное значение группировочного признака;

- минимальное значение группировочного признака;

k – число групп.

Число групп определяется следующими факторами: задачами исследования, основанием группировки, численностью совокупности, степенью вариации (изменчивости) признака.

Как ориентир для определения количества групп может быть использована формула Стерджесса:

где N – число единиц совокупности.

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

Далее определяются границы каждого интервала по следующей схеме:

№ интервала	Границы интервала
нижняя	верхняя
…
k

Например, пусть статистическая совокупность состоит из 40 туристических компаний, показатели выручки которых варьируют от 50 млн. у.е./год до 650 млн. у.е./год, что является, соответственно, минимальным и максимальным значениями признака. Тогда по формуле Стерджесса получаем: . Величина интервала для построения равноинтервальной группировки определяется следующим образом: (млн.у.е.). Таким образом, совокупность компаний будет разделена по показателю выручки на шесть равных групп: [50-150], [150-250], [250-350], [350-450], [450-550], [550-650] (млн. у.е./год).

После определения границ интервалов рассчитываются частоты, для чего используется функция ЧАСТОТА(вводится как формула массива, т.е. комбинацией клавиш CTRL + SHIFT + ВВОД). Выделяем столбец, в который должны поместиться частоты, соответствующие каждому интервалу. Вызываем функцию (из категории «Статистические») :

= ЧАСТОТА(массив_данных;массив_интервалов)

где массив_данных – это столбец исходных значений признака, для которых вычисляются частоты;

массив_интервалов – это столбец верхних границ интервалов с 1-го по k-1 –й (т.е. без последнего). Функция ЧАСТОТА предполагает формирование верхних границ по принципу «включительно», а нижних – «исключительно».

Сумма частот должна быть равна объему совокупности:

где - число наблюдений (частота) в i – ой группе.

Далее оценивается относительная структура совокупности через расчет частостей:

где - частость в i – ой группе, выраженная в долях единицы или в процентах к итогу.

В результате получаем структурную равноинтервальную группировку.

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

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

В этом случае величина интервалов определяется формулами:

где - величина i+1 – го интервала;

– константа арифметической прогрессии, для возрастающих интервалов , для убывающих интервалов ;

q – константа геометрической прогрессии, для возрастающих интервалов , для убывающих интервалов

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

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

.

Если полученное n не целое и/или в совокупности есть повторяющиеся значения признака, то число наблюдений в каждой группе может различаться. При этом надо стремиться к тому, чтобы эти различия были незначительны.

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

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

2. Ряд распределения

В результате на основе итоговой группировки формируется вариационный ряд распределения (см. табл. 1).

Таблица 1. Ряд распределения выручки туристических компаний, млн. у.е./год

Границы интервала	Частота	Частость	Плотность абсол.	Плотность отснос.
нижняя	верхняя
			0,300	0,12	0,00300
			0,250	0,10	0,00250
			0,200	0,08	0,00200
			0,125	0,05	0,00125
			0,100	0,04	0,00100
			0,025	0,01	0,00025
Итого			-	-

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

где - абсолютная плотность распределения в i – ой группе;

- относительная плотность распределения в i – ой группе;

- величина i – го интервала.

Далее строится кумулятивный ряд распределения, для чего рассчитываются накопленные частоты/частости к концу каждого интервала:

где / - накопленная частота/частость к концу i – ой группы.

3. Графики

Построение гистограммы и кумуляты выполняется с указанием названия графика и каждой оси. Для кумуляты в таблице рассчитываем накопленные частоты/частости (см. табл.2):

где / - накопленная частота/частость к концу i – ой группы.

Таблица 2. Кумулятивный ряд распределения выручки туристических компаний, млн. у.е./год

Границы интервала	Частота	Частость	Накопленная частота	Накопленная частость
нижняя	верхняя
			0,30		0,300
			0,25		0,550
			0,20		0,750
			0,13		0,875
			0,10		0,975
			0,03
Итого			-	-

Гистограмма– графическое изображение интервального вариационного ряда распределения, дающее представление о характере изменения его частот (рис. 1).

Для построения гистограммы выбираем вкладку ВСТАВКА и из разновидностей диаграмм MS Excel - ГИСТОГРАММА. На оси абсцисс откладываются величины интервалов значений признака, на оси ординат – частоты, частости или плотности распределения. Для равноинтервальных рядов могут быть использованы и частоты/частости, и плотности, для неравноинтервальных – только плотности.

Рис. 1. Гистограмма распределения выручки туристических компаний

Рис. 2. Кумулята распределения выручки туристических компаний

Кумулята – графическое изображение кумулятивной кривой, дающее представление о характере изменения накопленных частот/частостей (рис. 2).

Для построения кумуляты выбираем ВСТАВКА и из разновидностей диаграмм MS Excel – ТОЧЕЧНАЯ(с прямыми отрезками). На оси абсцисс откладываются величины интервалов значений признака, на оси ординат - накопленные частоты или частости. Равенство или неравенство интервалов для графика кумуляты значения не имеет.

4. Выводы

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