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

 

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

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

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

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

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

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

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

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

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

 

							Таблица 2.1
	Исходные данные
	Номер предприятия			Среднегодовая стоимость  основных производственных фондов, млн.руб.			Выпуск продукции, млн. руб.
	5			890,00			700,00
	23			960,00			930,00
	27			1040,00			800,00
	1			1070,00			1030,00
	8			1110,00			1100,00
	32			1130,00			1160,00
	22			1210,00			990,00
	19			1240,00			950,00
	2			1260,00			1130,00
	3			1300,00			1260,00
	13			1310,00			1340,00
	26			1340,00			1230,00
	9			1360,00			1290,00
	4			1370,00			1400,00
	28			1400,00			1250,00
	17			1410,00			1280,00
	6			1440,00			1200,00
	14			1440,00			1460,00
	25			1440,00			1300,00
	7			1480,00			1620,00
	31			1540,00			1300,00
	18			1560,00			1520,00
	10			1570,00			1610,00
	20			1580,00			1300,00
	24			1610,00			1490,00
	29			1620,00			1370,00
	15			1650,00			1770,00
	12			1720,00			1700,00
	21			1760,00			1750,00
	16			1890,00			1900,00
														Таблица 2.2
Зависимость выпуска  продукции от среднегодовой стоимости  основных фондов
Номер группы		Группы предприятий  по стоимости основеных фондов			Число предприятий			Выпуск продукции
								Всего		В среднем  на одно  предприятие
1		890-1090			4			3460,00		865,00
2		1090-1290			5			5330,00		1066,00
3		1290-1490			11			14630,00		1330,00
4		1490-1690			7			10360,00		1480,00
5		1690-1890			3			5350,00		1783,33
Итого		х			30			39130,00		1304,333333

 

 

 

 

 

 

			Таблица 2.3
Показатели внутригрупповой  вариации
Номер группы	Группы предприятий  по стоимости основеных фондов	Число предприятий	Внутригрупповая дисперсия
1	890-1090	4	15725,00
2	1090-1290	5	6664,00
3	1290-1490	11	13563,64
4	1490-1690	7	25657,14
5	1690-1890	3	7222,22
Итого	х	30	68832,00

 

			Таблица 2.4
Показатели дисперсии  и эмпирического корреляционного  отношения
Общая дисперсия	Средняя из внутригрупповых  дисперсия	Межгрупповая дисперсия	Эмпирическое корреляционное отношение
80477,88889	14889,55556	65588,33333	0,902765617
		Таблица 2.5
Линейный коэффициент  корреляции признаков
	Столбец 1	Столбец 2
Столбец 1	1
Столбец 2	0,91318826	1

 

ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R	0,91318826
R-квадрат	0,833912798
Нормированный R-квадрат	0,827981112
Стандартная ошибка	119,6707419
Наблюдения	30
Дисперсионный анализ
	df	SS		MS	F	Значимость F
Регрессия	1	2013346,245		2013346,245	140,5861384	1,97601E-12
Остаток	28	400990,4212		14321,08647
Итого	29	2414336,667

 

 

	Коэффициенты			Стандартная ошибка		t-статистика	P-Значение	Нижние 95%
Y-пересечение	-209,8703682			129,5620448		-1,61984452	0,116475575	-475,2661825
Переменная X 1	1,089355181			0,09187519		11,85690257	1,97601E-12	0,901157387
Верхние 95%		Нижние 68,5%	Верхние 68,5%
55,52544608		-342,4306281	-77,31010827
1,277552975		0,995353893	1,183356469

 

ВЫВОД ОСТАТКА

Наблюдение	Предсказанное Y	Остатки	Стандартные остатки
1	759,6557429	-59,65574286	-0,507322656
2	835,9106055	94,08939448	0,800152328
3	923,05902	-123,05902	-1,046514986
4	955,7396754	74,26032457	0,631522521
5	999,3138827	100,6861173	0,85625199
6	1021,100986	138,8990137	1,181221006
7	1108,249401	-118,2494008	-1,00561316
8	1140,930056	-190,9300562	-1,623701905
9	1162,71716	-32,71715981	-0,278232332
10	1206,291367	53,70863295	0,456747415
11	1217,184919	122,8150811	1,044440489
12	1249,865574	-19,86557429	-0,168940247
13	1271,652678	18,3473221	0,15602877
14	1282,54623	117,4537703	0,998846983
15	1315,226885	-65,22688514	-0,554700605
16	1326,120437	-46,12043695	-0,392216096
17	1358,801092	-158,8010924	-1,350471693
18	1358,801092	101,1989076	0,860612846
19	1358,801092	-58,80109238	-0,500054562
20	1402,3753	217,6247004	1,850717732
21	1467,73661	-167,7366105	-1,426460869
22	1489,523714	30,47628591	0,259175556
23	1500,417266	109,5827341	0,931910343
24	1511,310818	-211,3108177	-1,797023392
25	1543,991473	-53,99147314	-0,459152736
26	1554,885025	-184,885025	-1,572293923
27	1587,56568	182,4343196	1,551452705
28	1663,820543	36,17945695	0,3076763
29	1707,39475	42,60524972	0,362322342
30	1849,010924	50,98907619	0,433619838

 

 

 

 

ВЫВОД ВЕРОЯТНОСТИ
Персентиль	Y
1,666666667	700
5	800
8,333333333	930
11,66666667	950
15	990
18,33333333	1030
21,66666667	1100
25	1130
28,33333333	1160
31,66666667	1200
35	1230
38,33333333	1250
41,66666667	1260
45	1280
48,33333333	1290
51,66666667	1300
55	1300
58,33333333	1300
61,66666667	1340
65	1370
68,33333333	1400
71,66666667	1460
75	1490
78,33333333	1520
81,66666667	1610
85	1620
88,33333333	1700
91,66666667	1750
95	1770
98,33333333	1900

 

 

 

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

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

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

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

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

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

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

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

 

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

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

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

           .

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

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

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

 

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

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

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

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

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

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

Вывод: Т.к. |0,9² – 0,91²| = 0,02, следовательно гипотезу о прямолинейной связи можно считать подтвержденной.

 

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