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

 

 

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

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

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

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

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

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

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

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

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

				Таблица 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	144,400003		4885,468053		0,985541218
				Таблица 2.5
Линейный коэффициент  корреляции признаков
		Столбец 1		Столбец 2
Столбец 1		1
Столбец 2		0,91318826		1
Выходные таблицы

 

ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R	0,91318826
R-квадрат	0,833912798
Нормированный R-квадрат	0,827981112
Стандартная ошибка	29,91768548
Наблюдения	30

 

Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия	1	125834,1403	125834,1403	140,5861384	1,97601E-12
Остаток	28	25061,90133	895,0679045
Итого	29	150896,0417

 

	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%	Нижние 68.3%	Верхние 68.3%
Y-пересечение	-44,29742819	31,71152863	-1,396887192	0,1734215	-109,255549	20,66069267	-76,60653786	-11,98831852
Переменная X 1	1,089355181	0,09187519	11,85690257	1,97601E-12	0,901157387	1,277552975	0,995748668	1,182961694

 

 

 

 

 

 

 

ВЫВОД ОСТАТКА
Наблюдение	Предсказанное Y	Остатки
1	189,9139357	-14,91393571
2	208,9776514	23,52234862
3	230,764755	-30,764755
4	238,9349189	18,56508114
5	249,8284707	25,17152933
6	255,2752466	34,72475343
7	277,0623502	-29,56235019
8	285,232514	-47,73251405
9	290,67929	-8,179289952
10	301,5728418	13,42715824
11	304,2962297	30,70377029
12	312,4663936	-4,966393571
13	317,9131695	4,586830524
14	320,6365574	29,36344257
15	328,8067213	-16,30672129
16	331,5301092	-11,53010924
17	339,7002731	-39,7002731
18	339,7002731	25,2997269
19	339,7002731	-14,7002731
20	350,5938249	54,4061751
21	366,9341526	-41,93415262
22	372,3809285	7,619071476
23	375,1043165	27,39568352
24	377,8277044	-52,82770443
25	385,9978683	-13,49786829
26	388,7212562	-46,22125624
27	396,8914201	45,6085799
28	415,9551358	9,044864238
29	426,8486876	10,65131243
30	462,252731	12,74726905

 

 

 

 

 

 

 

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

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

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

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

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

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

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

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

(315-366) и выпуск  продукции в среднем на одно  предприятие составляет, 370,00.  По  общему числу исследуемых предприятий  (30) выпуск продукции в среднем  на одно предприятие составляет 326,08.

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

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

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

          

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вывод: Однофакторная линейная регрессионная модель связи факторного и результативного признаков имеет вид прямой.

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

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

 

С увеличением надежности границы  доверительных интервалов увеличиваются.

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

 

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

Экономическая интерпретация коэффициента эластичности Э: с возрастанием основных фондов на 1% выпуск продукции в среднем увеличивается на 1,135%.  Задача 5. Нахождение наиболее адекватного уравнения регрессии с помощью средств инструмента Мастер диаграмм. Построение для этого уравнения теоретической линии регрессии.