Автоматизированный априорный анализ статистической совокупности в среде MS Excel

 

 

В процессе статистического  исследования необходимо решить ряд  задач.

1. Установить наличие стохастической связи между факторным признаком Х и результативным признаком Y: 
   а) графическим методом; 
   б) методом сопоставления параллельных рядов.
2. Установить наличие корреляционной связи между признаками Х и Y методом аналитической группировки.
3. Оценить тесноту связи признаков Х и Y на основе: 
   а) эмпирического корреляционного отношения η; 
   б) линейного коэффициента корреляции r.

Сравнить значения η и r и сделать вывод о возможности линейной связи между признаками Х и Y.

4. Построить однофакторную линейную регрессионную модель связи признаков Х и Y, используя инструмент Регрессия надстройки Пакет анализа, и рассчитать доверительные интервалы коэффициентов уравнения линейной регрессии.

Построить теоретическую  кривую регрессии.

Дать экономическую  интерпретацию коэффициента регрессии.

Рассчитать коэффициент  эластичности и дать его экономическую  интерпретацию.

5. Найти наиболее адекватное уравнение регрессии с помощью средств инструмента Мастер диаграмм.

II. Рабочий файл с результативными таблицами и графиками.

 

									Таблица 2.1
Исходные данные
Номер предприятия				Среднегодовая стоимость  основных производственных фондов, млн.руб.									Выпуск продукции, млн. руб.
5				215,00									175,00
23				232,50									232,50
27				252,50									200,00
1				260,00									257,50
8				270,00									275,00
32				275,00									290,00
22				295,00									247,50
19				302,50									237,50
2				307,50									282,50
3				317,50									315,00
13				320,00									335,00
26				327,50									307,50
9				332,50									322,50
4				335,00									350,00
28				342,50									312,50
17				345,00									320,00
6				352,50									300,00
14				352,50									365,00
25				352,50									325,00
7				362,50									405,00
31				377,50									325,00
18				382,50									380,00
10				385,00									402,50
20				387,50									325,00
24				395,00									372,50
29				397,50									342,50
15				405,00									442,50
12				422,50									425,00
21				432,50									437,50
16				465,00									475,00
																						Таблица 2.2
Зависимость выпуска  продукции от среднегодовой стоимости  основных фондов
Номер группы					Группы предприятий  по стоимости основеных фондов						Число предприятий					Выпуск продукции
																Всего						В среднем  на одно  предприятие
1					215-265						4					865,00						216,25
2					265-315						5					1332,50						266,50
3					315-365						11					3657,50						332,50
4					365-415						7					2590,00						370,00
5					415-465						3					1337,50						445,83
Итого											30					9782,50						326,0833333
																Таблица 2.3
Показатели внутригрупповой  вариации
Номер группы					Группы предприятий  по стоимости основеных фондов						Число предприятий					Внутригрупповая дисперсия
1					215-265						4					982,81
2					265-315						5					416,50
3					315-365						11					847,73
4					365-415						7					1603,57
5					415-465						3					451,39
Итого											30					4302,00
																Таблица 2.4
Показатели дисперсии и эмпирического корреляционного отношения
Общая дисперсия					Средняя из внутригрупповых  дисперсия						Межгрупповая дисперсия					Эмпирическое корреляционное отношение
5029,868056					930,5972222						4099,270833					0,902765617
											Таблица 2.5
Линейный коэффициент  корреляции признаков
					Столбец 1						Столбец 2
Столбец 1					1
Столбец 2					0,91318826						1
Выходные  таблицы
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R			0,91319
R-квадрат			0,83391
Нормированный R-квадрат			0,82798
Стандартная ошибка			29,9177
Наблюдения			30
Дисперсионный анализ
			df				SS			MS		F		Значимость F
Регрессия			1				125834,14			125834		140,586		1,98E-12
Остаток			28				25061,901			895,068
Итого			29				150896,04
			Коэффициенты			Стандартная ошибка				t-статистика		P-Значение		Нижние 95%	Верхние 95%			Нижние 68,3%	Верхние 68,3%
Y-пересечение			-44,297			31,711529				-1,3969		0,17342		-109,256	20,6608			-76,607	-11,988315
Переменная X 1			1,08936			0,0918752				11,8569		2E-12		0,901157	1,27755			0,99575	1,1829617
ВЫВОД ОСТАТКА
Наблюдение		Предсказанное Y						Остатки
1		189,914						-14,91394
2		208,978						23,522349
3		230,765						-30,76476
4		238,935						18,565081
5		249,828						25,171529
6		255,275						34,724753
7		277,062						-29,56235
8		285,233						-47,73251
9		290,679						-8,17929
10		301,573						13,427158
11		304,296						30,70377
12		312,466						-4,966394
13		317,913						4,5868305
14		320,637						29,363443
15		328,807						-16,30672
16		331,53						-11,53011
17		339,7						-39,70027
18		339,7						25,299727
19		339,7						-14,70027
20		350,594						54,406175
21		366,934						-41,93415
22		372,381						7,6190715
23		375,104						27,395684
24		377,828						-52,8277
25		385,998						-13,49787
26		388,721						-46,22126
27		396,891						45,60858
28		415,955						9,0448642
29		426,849						10,651312
30		462,253						12,747269

 

          

 

 

 

 

 

 

 

 

 

                                                  

 

 

 

 

 

 

 

 

    

 

   _{Диаграмма 2}

 

                                                                                                     

Уравнение регрессии  и их графики       _{Диаграмма 2.1}

 

Уравнение регрессии и его график               _{Диаграмма 2.2}

 III. Выводы по результатам выполнения лабораторной работы.

Задача 1. Установление наличия стохастической связи между факторным признаком Х и результативным признаком Y:

а) графическим методом.

Вывод: На основе анализа диаграммы рассеяния из Лабораторной  работы №1, полученной после удаления аномальных значений, можно сделать вывод, что имеет место стохастическая связь. Предположительный вид связи: линейная прямая.

б) методом сопоставления параллельных рядов.

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

Задача 2. Установление наличия корреляционной связи между признаками Х и Y методом аналитической группировки.

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

Задача 3.Оценка тесноты связи признаков Х и Y:

а) на основе эмпирического корреляционного отношения

Для анализа тесноты  связи между факторным и результативным признаками, рассчитывается показатель η - эмпирическое корреляционное отношение, задаваемое формулой

          

Для вычисления η необходимо знать общую дисперсию  и межгрупповую дисперсию результативного признака Y - Выпуск продукции.

Результаты выполненных  расчетов представляются табл. 2.4 Рабочего файла.

Вывод:  Величина η= 0,9 является близкой к единице, что свидетельствует о том, что связь тесная.

б) на основе линейного коэффициента корреляции признаков

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

Результатом работы инструмента Корреляции является табл. 2.5 Рабочего файла.

Вывод: Значение коэффициента корреляции r= 0,91, лежит в интервале от 0,9-0,99, что в соответствии со шкалой Чэддока, говорит о весьма высокой связи.

Так как значение коэффициента корреляции r положительное, то связь между признаками соответствует прямой линейной зависимости.

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

Вывод: Так как значения показателей совпадают, значит существует прямолинейная связь.

Задача 4. Построение однофакторной линейной регрессионной модели связи изучаемых признаков с помощью инструмента Регрессия надстройки Пакет анализа.

Построение регрессионной модели заключается в определении аналитического выражения связи между факторным признаком X и результативным признаком Y.

Инструмент Регрессия производит расчет параметров а₀ и а₁ уравнения однофакторной линейной регрессии и проверка его адекватности исследуемым фактическим данным.

В результате работы инструмента Регрессия были получены результативные таблицы 2.6 – 2.9 Рабочего файла.

Вывод: Однофакторная линейная регрессионная модель связи факторного и результативного признаков имеет вид: y = 1E-05x³ - 0,0119x² + 4,8267x - 421,85,            R² = 0,838 – означает выспкую степень тесноты связи признаков в уравнении.

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

Коэффициенты	Границы доверительных интервалов
	С надежностью Р=0,68		С надежностью Р=0,95
	Нижние	Верхние	Нижние	Верхние
а0	-76,60	-11,98	-109,25	20,66
а1	0,99	1,18	0,90	1,27

 

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

Экономическая интерпретация коэффициента регрессии а_{1 ,} коэффициент является значимым

Коэффициент эластичности =1,13

Экономическая интерпретация  коэффициента эластичности Э, следовательно при изменении факторного признака на 1%, повышение выпуска продукции будет в среднем на 1,13%.

Задача 5. Нахождение наиболее адекватного уравнения регрессии с помощью средств инструмента Мастер диаграмм. Построение для этого уравнения теоретической линии регрессии.

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

Построение моделей  осуществляется непосредственно на диаграмме рассеяния.

На диаграмме рассеяния отображается линия и уравнение регрессии, а также коэффициент детерминации R².

В лабораторной работе уравнения  регрессии и их графики были построены  для 5-ти видов зависимости между признаками и даны на Диаграмме 2.1

 

Уравнения регрессии  и соответствующие им коэффициент детерминации R² даны в следующей таблице:

Регрессионные модели связи³

Вид уравнения	Уравнение регрессии	Коэффициент детерминации R2
Линейное
Полином 2-го порядка	У=0,0006х2+0,6816х+21,986	0,8353
Полином 3-го порядка	У=1Е-0,5х3-0,0119х2+4,8267х-421,85	0,8381
Степенное	У=96,534е0,0035х	0,8272
Экспоненциальное	У=0,4087х1,1452	0,8372

 

Выбор наиболее адекватного  уравнения регрессии определяется максимальным значением коэффициента детерминации R²: чем ближе значение R² к единице, тем более точно регрессионная модель соответствует фактическим данным

Вывод: Максимальное значение коэффициента детерминации R² =0,8381

Вид искомого уравнения  регрессии – У=1Е-0,5х³-0,0119х²+4,8267х-421,85

Это уравнение регрессии  и его график приведены на отдельной диаграмме рассеяния 2.2 рабочего файла.

Вместе с тем, так  как значения коэффициентов R² кубического и линейного уравнения расходятся очень незначительно, а для показателей тесноты связи имеет место неравенство , то в качестве адекватного уравнения регрессии может быть принято линейное уравнение У=0,0006х²+0,6816х+21,986, совпадающее с найденным с помощью инструмента Регрессия надстройки Пакет анализа.

 

¹ Все статистические показатели представляются с точностью до 2-х знаков после запятой.

² Выводы должны раскрывать экономический смысл результатов проведенного статистического анализа совокупности предприятия, поэтому ответы на поставленные вопросы задач 1-6, должны носить экономический характер со ссылками на результаты анализа статистических свойств совокупности (п. 1-5 для выборочной совокупности и п. 1-3 для генеральной совокупности).

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