Расчет в программе MicrosoftExcelгруппировочного признака и ранжирование его по возрастанию.

Проведение типологической группировки с помощью программы MicrosoftExcel

 

На основе имеющейся информации (рис. 1) провести типологическую группировку совокупности регионов Центрального федерального округа. Группировочный признак - валовой региональный продукт на душу населения, тыс. руб.

Рисунок 1 – Исходные данные для проведения группировки

Названия показателей, представленных в исходных данных, приведены в таблице 1.

 

Таблица 1 - Ключ выборки

№ п/п	Название показателя
	Территория, тыс. кв. км
	Среднегодовая численность населения, тыс. чел.
	Валовой региональный продукт, млн. руб.
	Основные фонды, млн. руб.
	Инвестиции в основной капитал, млн. руб.
	Среднегодовая численность занятых в экономике, тыс. чел

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

Последовательность группировки

Расчет в программе MicrosoftExcelгруппировочного признака и ранжирование его по возрастанию.

Для расчета группировочного признака (валового регионального продукта на душу населения)необходимо разделить валовой региональный продукт на среднегодовую численность населения (показатель№3 на показатель №2 исходных данных). Для этого в таблице с исходными данными в следующем свободном столбце ввести формулу: =D2/C2 и протянуть ее до конца столбца.

Затем найденный группировочный признак (показатель по №7 в таблице с исходными данными) необходимо ранжировать по возрастанию. Для этого выделите одну ячейку в столбце, по которому требуется выполнить сортировку и на вкладке Данные в группе Сортировка и фильтр

нажмите кнопку , чтобы выполнить сортировку по возрастанию (от А до Я или от наименьшего числа до наибольшего).

В итоге вся совокупность исходных данных будет отсортирована по возрастанию показателя №7 (группировочного признака), что видно на рисунке 2.

2. Исследование характера изменения группировочного признака и выделение интервальных групп.

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

Для построения графика необходимо выделить ранжированный ряд группировочного признака (показатель №7) и на вкладке Вставить в группе Диаграммы выбрать тип диаграммы «График» подтип «График с маркерами».

Рисунок 2 – Исходные данные для проведения группировки, отсортированные по возрастанию показателя №7.

 

С помощью вкладки Макет подписать оси получившейся диаграммы (рисунок 3).

Рисунок 3 - Огива распределения регионов по валовому региональному продукту на душу населения, тыс. руб.

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

,

где K – искомое число групп,N – объем выборки

Так как последние 2 региона сильно отличаются от рассматриваемой совокупности по величине группировочного признака, целесообразно выделить их в отдельную интервальную группу, а остальную совокупность разбить на 4 группы. Определим границы интервалов и найдем шаг интервала формуле:

h = (x_max-x_min)/K,

гдеh - шаг разбиения, x_max - наивысшее значение группировочного признака в совокупности,x_mix- наименьшее значение группировочного признака в совокупности.

h = (245,6-121,0) / 4 ≈ 31,2 тыс.руб.

Для определения границ интервалов минимальное значение (121 тыс. руб.) принимается за нижнюю границу первого интервала. Прибавляя к ней шаг интервала, получаем верхнюю границу первого интервала, которая служит нижней границей второго интервала. Аналогично определяем границы всех четырех интервалов.

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

Таблица 2- Интервальный вариационный ряд распределения регионов

№ группы	Интервалы по среднедушевым доходам, руб.	Число регионов
	121,0-152,2
	152,3-183,5
	183,6-214,8
	214,9-246,1
	Свыше 246,1

 

Для наглядности строим график интервального вариационного ряда распределения (рисунок 4). По оси абсцисс указываем границы интервалов в порядке возрастания, по оси ординат – число регионов в каждом интервале. Для построения диаграммы необходимо скопировать данные таблицы 2 из MicrosoftWordв Excel, выделить ее последний столбец (число регионов) и на вкладке Вставить в группе Диаграммы выбрать тип диаграммы «Гистограмма» подтип «Гистограмма с группировкой».

Рисунок 4 – Гистограмма распределения регионов по среднедушевым доходам, руб.