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

 

 

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

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

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

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

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

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

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

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

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

 

		Таблица 2.1		Номер варианта
Исходные данные				20
Номер предприятия	Среднегодовая стоимость  основных производственных фондов, млн.руб.	Выпуск продукции, млн. руб.
5	980,00	770,00
23	1057,00	1023,00
27	1145,00	880,00
1	1178,00	1133,00
8	1222,00	1210,00
32	1244,00	1276,00
22	1332,00	1089,00
19	1365,00	1045,00
2	1387,00	1243,00
3	1431,00	1386,00
13	1442,00	1474,00
26	1475,00	1353,00
9	1497,00	1419,00
4	1508,00	1540,00
28	1541,00	1375,00
17	1552,00	1408,00
6	1585,00	1320,00
14	1585,00	1606,00
25	1585,00	1430,00
7	1629,00	1782,00
31	1695,00	1430,00
18	1717,00	1672,00
10	1728,00	1771,00
20	1739,00	1430,00
24	1772,00	1639,00
29	1783,00	1507,00
15	1816,00	1947,00
12	1893,00	1870,00
21	1937,00	1925,00
16	2080,00	2090,00
				Таблица 2.2
Зависимость выпуска  продукции от среднегодовой стоимости  основных фондов
Номер группы	Группы предприятий  по стоимости основеных фондов	Число предприятий	Выпуск продукции
			Всего	В среднем  на одно  предприятие
1	980-1200	4	3806,00	951,50
2	1200-1420	5	5863,00	1172,60
3	1420-1640	11	16093,00	1463,00
4	1640-1860	7	11396,00	1628,00
5	1860-2080	3	5885,00	1961,67
Итого		30	43043,00	1434,766667
			Таблица 2.3
Показатели внутригрупповой  вариации
Номер группы	Группы предприятий по стоимости основеных фондов	Число предприятий	Внутригрупповая дисперсия
1	980-1200	4	19027,25
2	1200-1420	5	8063,44
3	1420-1640	11	16412,00
4	1640-1860	7	31045,14
5	1860-2080	3	8738,89
Итого		30	83286,72
			Таблица 2.4
Показатели дисперсии  и эмпирического корреляционного  отношения
Общая дисперсия	Средняя из внутригрупповых  дисперсия	Межгрупповая дисперсия	Эмпирическое корреляционное отношение
97378,24556	18016,36222	79361,88333	0,902765617
		Таблица 2.5
Линейный коэффициент  корреляции признаков
	Столбец 1	Столбец 2
Столбец 1	1
Столбец 2	0,91318826	1
Выходные  таблицы
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R	0,91318826
R-квадрат	0,833912798
Нормированный R-квадрат	0,827981112
Стандартная ошибка	131,6378161
Наблюдения	30
Дисперсионный анализ
	df	SS	MS	F
Регрессия	1	2436148,957	2436148,957	140,5861384
Остаток	28	485198,4097	17328,51463
Итого	29	2921347,367
	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение
Y-пересечение	-231,9467602	142,6088095	-1,626454642	0,115056566
Переменная X 1	1,089355181	0,09187519	11,85690257	1,97601E-12
ВЫВОД ОСТАТКА
Наблюдение	Предсказанное Y	Остатки
1	835,6213171	-65,62131714
2	919,5016661	103,4983339
3	1015,364922	-135,364922
4	1051,313643	81,68635703
5	1099,245271	110,7547291
6	1123,211085	152,7889151
7	1219,074341	-130,0743408
8	1255,023062	-210,0230618
9	1278,988876	-35,98887579
10	1326,920504	59,07949625
11	1338,903411	135,0965893
12	1374,852132	-21,85213171
13	1398,817946	20,1820543
14	1410,800853	129,1991473
15	1446,749574	-71,74957366
16	1458,732481	-50,73248065
17	1494,681202	-174,6812016
18	1494,681202	111,3187984
19	1494,681202	-64,68120162
20	1542,61283	239,3871704
21	1614,510272	-184,5102715
22	1638,476086	33,5239145
23	1650,458992	120,5410075
24	1662,441899	-232,4418995
25	1698,39062	-59,39062046
26	1710,373527	-203,3735274
27	1746,322248	200,6777516
28	1830,202597	39,79740265
29	1878,134225	46,86577469
30	2033,912016	56,08798381

 

 

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

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

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

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

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

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

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

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

 

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

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

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

          

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

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

Вывод:  Величина η=0,903 является близкой к единице, Это в соответствии с оценочной шкалой   Чэддока   говорит о весьма высокой степени связи   изучаемых признаков    (0,9≤ = 0,903≤0,99).

 

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

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

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

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

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

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

Вывод: При  = 0,903, r = 0,913,   | |≤ |0,815 – 0,834| = 0,019 , следовательно,  зависимость признака Y от фактора X можно считать прямолинейной.

 

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

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

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

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

Вывод: Однофакторная линейная регрессионная модель связи факторного и результативного признаков имеет вид = -231,9467 + 1,0894х

 

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

Коэффициенты	Границы доверительных интервалов
	С надежностью Р=0,68		С надежностью Р=0,95
	Нижние	Верхние	Нижние	Верхние
а0	-377,2429	-86,6506	-524,0677	60,1741
а1	0,9958	1,1839	0,9012	1,2776

 

Из таблицы видно, что  увеличение уровня надежности ведет  к расширению доверительных интервалов коэффициентов уравнения.

 

Экономическая интерпретация коэффициента регрессии  а₁

В     случае    линейного    уравнения    регрессии    = а₀+а₁х  величина коэффициента регрессии a₁ показывает, на сколько в среднем (в абсолютном выражении) изменяется значения результативного признака Y при изменении фактора X на единицу его измерения. Знак при a₁ показывает направление этого изменения.

Вывод:

Коэффициент  регрессии  a₁ = 1,089  показывает, что при увеличении стоимости основных фондов на 1 млн. руб., выпуск продукции увеличится в среднем на 1,089  млн. руб.

 

Коэффициент эластичности =1,162 (1,089 * 1542,138/1445,172)

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

Вывод:                                              

Коэффициента эластичности  Э показывает, что при увеличении среднегодовой стоимости основных фондов на 1% выпуск продукции возрастет в среднем на 1,16%.

 

 

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

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

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

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

В лабораторной работе уравнения  регрессии и их графики были построены  для 

4-ти видов зависимости между признаками и даны на диаграмме

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

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

Вид уравнения	Уравнение регрессии	Коэффициент детерминации R2
Полином 2-го порядка	Y=0.0001x2+0.6722x+73.723	0.8353
Полином 3-го порядка	Y=6E-07x3-0.0028x2+5.0133x-2023.4	0.8381
Степенное	Y=0.2612x 1.1733	0.8371
Экспоненциальное	Y=4.13.4e0.0008x	<span class="dash041e_0431_044b_0447_043d_044b_0439__Char" style=" font-family: 'Arial CYR', 'Arial'; text-decoration: none;