ВСЕРОССИЙСКИЙ ЗАОЧНЫЙ ФИНАНСОВО-ЭКОНОМИЧЕСКИЙ ИНСТИТУТ
КАФЕДРА СТАТИСТИКИ
О Т Ч Е Т

о результатах выполнения

компьютерной лабораторной работы
Автоматизированный корреляционно-регрессионный анализ взаимосвязи статистических данных в среде MS Excel
Вариант № 65
Выполнил: ст. III курса гр. 3

Широких Е.Б.
Проверил: доц. Левчегов О.Н.
Липецк 2011 г.

1. Постановка задачи статистического исследования

Корреляционно-регрессионный анализ взаимосвязи признаков является составной частью проводимого статистического исследования деятельности 30-ти предприятий и частично использует результаты ЛР-1.

В ЛР-2 изучается взаимосвязь между факторным признаком Среднегодовая стоимость основных производственных фондов (признак Х) и результативным признаком Выпуск продукции (признак Y), значениями которых являются исходные данные ЛР-1 после исключения из них аномальных наблюдений.

Исходные данные
Номер предприятия	Среднегодовая стоимость основных производственных фондов, млн.руб.	Выпуск продукции, млн. руб.
5	1205,00	945,00
23	1299,50	1255,50
27	1407,50	1080,00
1	1448,00	1390,50
8	1502,00	1485,00
32	1529,00	1566,00
22	1637,00	1336,50
19	1677,50	1282,50
2	1704,50	1525,50
3	1758,50	1701,00
13	1772,00	1809,00
26	1812,50	1660,50
9	1839,50	1741,50
4	1853,00	1890,00
28	1893,50	1687,50
17	1907,00	1728,00
6	1947,50	1620,00
14	1947,50	1971,00
25	1947,50	1755,00
7	2001,50	2187,00
31	2082,50	1755,00
18	2109,50	2052,00
10	2123,00	2173,50
20	2136,50	1755,00
24	2177,00	2011,50
29	2190,50	1849,50
15	2231,00	2389,50
12	2325,50	2295,00
21	2379,50	2362,50
16	2555,00	2565,00

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

1. 
   Установить наличие статистической связи между факторным признаком Х и результативным признаком Y графическим методом.
   
2. 
   Установить наличие корреляционной связи между признаками Х и Y методом аналитической группировки.
   
3. 
   Оценить тесноту связи признаков Х и Y на основе эмпирического корреляционного отношения η.
   
4. 
   Построить однофакторную линейную регрессионную модель связи признаков Х и Y, используя инструмент Регрессия надстройки Пакет анализа, и оценить тесноту связи признаков Х и Y на основе линейного коэффициента корреляции r.
   
5. 
   Определить адекватность и практическую пригодность построенной линейной регрессионной модели, оценив:
   

а) значимость и доверительные интервалы коэффициентов а₀, а₁;

б) индекс детерминации R² и его значимость;

в) точность регрессионной модели.

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

а) коэффициента регрессии а₁;

б) коэффициента эластичности К_Э;

в) остаточных величин ε_i.

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

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

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

Статистическая связь является разновидностью стохастической (случайной) связи, при которой с изменением факторного признака X закономерным образом изменяется какой–либо из обобщающих статистических показателей распределения результативного признака Y.

Вывод:

Точечный график связи признаков (диаграмма рассеяния, полученная в ЛР-1 после удаления аномальных наблюдений) позволяет сделать вывод, что имеет место статистическая связь. Предположительный вид связи – линейная прямая.

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

Корреляционная связь – важнейший частный случай стохастической статистической связи, когда под воздействием вариации факторного признака Х закономерно изменяются от группы к группе средние групповые значения результативного признака Y (усредняются результативные значения , полученные под воздействием фактора ). Для выявления наличия корреляционной связи используется метод аналитической группировки.

Вывод:

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

Задача 3. Оценка тесноты связи признаков Х и Y на основе эмпирического корреляционного отношения.

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

,

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

Для качественной оценки тесноты связи на основе показателя эмпирического корреляционного отношения служит шкала Чэддока:

Значение η	0,1 – 0,3	0,3 – 0,5	0,5 – 0,7	0,7 – 0,9	0,9 – 0,99
Сила связи	Слабая	Умеренная	Заметная	Тесная	Весьма тесная

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

Вывод:

Значение коэффициента η =0,56, что в соответствии с оценочнойшкалой Чэддока говорит о заметной
степени связи изучаемых признаков.

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

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

Инструмент Регрессия на основе исходных данных (x_i , y_i), производит расчет параметров а₀ и а₁ уравнения однофакторной линейной регрессии , а также вычисление ряда показателей, необходимых для проверки адекватности построенного уравнения исходным (фактическим) данным.

Примечание. В результате работы инструмента Регрессия получены четыре результативные таблицы (начиная с заданной ячейки А75). Эти таблицы выводятся в Рабочий файл без нумерации, поэтому необходимо присвоить им номера табл.2.5 – табл.2.8 в соответствии с их порядком.

Вывод:

Рассчитанные в табл.2.7 (ячейки В91 и В92) коэффициенты а₀ и а₁ позволяют построить линейную регрессионную модель связи изучаемых признаков в виде уравнения -728,665+1,089х.

4.2. В случае линейности функции связи для оценки тесноты связи признаков X и Y, устанавливаемой по построенной модели, используется линейный коэффициент корреляции r.

Значение коэффициента корреляции r приводится в табл.2.5 в ячейке В78 (термин "Множественный R").
Вывод:

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

Задача 5
. Анализ адекватности и практической пригодности построенной линейной регрессионной модели.

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

Оценка соответствия построенной регрессионной модели исходным (фактическим) значениям признаков X и Y выполняется в 4 этапа:

1. 
   оценка статистической значимости коэффициентов уравнения а₀, а₁ и определение их доверительных интервалов для заданного уровня надежности;
   
2. 
   определение практической пригодности построенной модели на основе оценок линейного коэффициента корреляции r и индекса детерминации R²;
   
3. 
   проверка значимости уравнения регрессии в целом по F-критерию Фишера;
   
4. 
   оценка погрешности регрессионной модели.
   

1. 
   Оценка статистической значимости коэффициентов уравнения а₀, а₁ и определение их доверительных интервалов
   

Так как коэффициенты уравнения а₀ , а₁ рассчитывались, исходя из значений признаков только для 30-ти пар (x_i , y_i), то полученные значения коэффициентов являются лишь приближенными оценками фактических параметров связи а₀ , а₁. Поэтому необходимо:

1. 
   проверить значения коэффициентов на неслучайность (т.е. узнать, насколько они типичны для всей генеральной совокупности предприятий отрасли);
   
2. 
   определить (с заданной доверительной вероятностью 0,95 и 0,683) пределы, в которых могут находиться значения а₀, а₁ для генеральной совокупности предприятий.
   

Для анализа коэффициентов а₀, а₁ линейного уравнения регрессии используется табл.2.7, в которой:

– значения коэффициентов а₀, а₁ приведены в ячейках В91 и В92 соответственно;

– рассчитанный уровень значимости коэффициентов уравнения приведен в ячейках Е91 и Е92;

– доверительные интервалы коэффициентов с уровнем надежностиР=0,95 и Р=0,683 указаны в диапазоне ячеек F91:I92.

5.1.1. Определение значимости коэффициентов уравнения

Уровень значимости
– это величина α=1–Р, где Р – заданный уровень надежности (доверительная вероятность).

Режим работы инструмента Регрессия использует по умолчанию уровень надежности Р=0,95. Для этого уровня надежности уровень значимости равен α = 1 – 0,95 = 0,05. Этот уровень значимости считается заданным.

В инструменте Регрессия надстройки Пакет анализа для каждого из коэффициентов а₀ и а₁ вычисляется уровень его значимости α_р, который указан в результативной таблице (табл.2.7 термин "Р-значение"). Если рассчитанный для коэффициентов а₀, а₁ уровень значимости α_р, меньше заданного уровня значимости α= 0,05, то этот коэффициент признается неслучайным (т.е. типичным для генеральной совокупности), в противном случае – случайным.

Примечание. В случае, если признается случайным свободный член а₀, то уравнение регрессии целесообразно построить заново без свободного члена а₀. В этом случае в диалоговом окне Регрессия необходимо задать те же самые параметры за исключением лишь того, что следует активизировать флажок Константа-ноль (это означает, что модель будет строиться при условии а₀=0). В лабораторной работе такой шаг не предусмотрен.

Если незначимым (случайным) является коэффициент регрессии а₁, то взаимосвязь между признаками X и Y в принципе не может аппроксимироваться линейной моделью.

Вывод:

Для свободного члена а₀ уравнения регрессии рассчитанный уровень значимости есть α_р =0,1. Так как он больше заданного уровня значимости α=0,05, то коэффициент а₀ признается случайным.

Для коэффициента регрессии а₁ рассчитанный уровень значимости есть α_р = Так как он меньше заданного уровня значимости α=0,05, то коэффициент а₁ признается типичным.

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

Доверительные интервалы коэффициентов а₀, а₁ построенного уравнения регрессии при уровнях надежности Р=0,95 и Р=0,683 представлены в табл.2.7, на основе которой формируется табл.2.9.

Таблица 2.9

Границы доверительных интервалов коэффициентов уравнения

Коэффициенты	Границы доверительных интервалов
	Для уровня надежности Р=0,95		Для уровня надежности Р=0,683
	нижняя	верхняя	нижняя	верхняя
а0	-1622,1	164,8	-1173,04	-284,3
а1	0,90	1,28	1,00	1,2

Вывод:

В генеральной совокупности предприятий значение коэффициента а₀ следует ожидать с надежностью Р=0,95 в пределах-1622,1а₀164,8 значение коэффициента а₁ в пределах 0,90а₁1,28. Уменьшение уровня надежности ведет к сужению доверительных интервалов коэффициентов уравнения.

0. 
   Определение практической пригодности построенной регрессионной модели.
   

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

• 
  близость к единице свидетельствует о хорошей аппроксимации исходных (фактических) данных с помощью построенной линейной функции связи ;
  
• 
  близость к нулю означает, что связь между фактическими данными Х и Y нельзя аппроксимировать как построенной, так и любой другой линейной моделью, и, следовательно, для моделирования связи следует использовать какую-либо подходящую нелинейную модель.
  

Пригодность построенной регрессионной модели для практического использования можно оценить и по величине индекса детерминации R², показывающего, какая часть общей вариации признака Y объясняется в построенной модели вариацией фактора X.

В основе такой оценки лежит равенство R = r (имеющее место для линейных моделей связи), а также шкала Чэддока, устанавливающая качественную характеристику тесноты связи в зависимости от величины r.

Согласно шкале Чэддока высокая степень тесноты связи признаков достигается лишь при >0,7, т.е. при >0,7. Для индекса детерминации R² это означает выполнение неравенства R² >0,5.

При недостаточно тесной связи признаков X, Y (слабой, умеренной, заметной) имеет место неравенство 0,7, а следовательно, и неравенство .

С учетом вышесказанного, практическая пригодность построенной модели связи оценивается по величине R² следующим образом:

• 
  неравенство R² >0,5 позволяет считать, что построенная модель пригодна для практического применения, т.к. в ней достигается высокая степень тесноты связи признаков X и Y, при которой более 50% вариации признака Y объясняется влиянием фактора Х;
  
• 
  неравенство означает, что построенная модель связи практического значения не имеет ввиду недостаточной тесноты связи между признаками X и Y, при которой менее 50% вариации признака Y объясняется влиянием фактора Х, и, следовательно, фактор Х влияет на вариацию Y в значительно меньшей степени, чем другие (неучтенные в модели) факторы.
  

Значение индекса детерминации R² приводится в табл.2.5 в ячейке В79 (термин "R - квадрат").

Вывод:

Значение линейного коэффициента корреляции r и значение индекса детерминации R² согласно табл. 2.5 равны: r =0,91, R² =0,83. Поскольку и , то построенная линейная регрессионная модель связи пригодна для практического использования.

0. 
   Общая оценка адекватности регрессионной модели по F-критерию Фишера
   

Адекватность построенной регрессионной модели фактическим данным (x_i, y_i) устанавливается по критерию Р.Фишера, оценивающему статистическую значимость (неслучайность) индекса детерминации R².

Рассчитанная для уравнения регрессии оценка значимости R² приведена в табл.2.6 в ячейке F86 (термин "Значимость F"). Если она меньше заданного уровня значимости α=0,05, то величина R² признается неслучайной и, следовательно, построенное уравнение регрессии может быть использовано как модель связи между признаками Х и Y для генеральной совокупности предприятий отрасли.

Вывод:

Рассчитанный уровень значимости α_р индекса детерминации R² есть α_р=. Так как он меньше заданного уровня значимости α=0,05, то значение R² признается типичным и модель связи между признаками Х и Y -728,665+1,089х. применима для генеральной совокупности предприятий отрасли в целом.

0. 
   Оценка погрешности регрессионной модели
   

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

Погрешность регрессионной модели выражается в процентах и рассчитывается как величина ^.100.

В адекватных моделях погрешность не должна превышать 12%-15%.

Значение приводится в выходной таблице "Регрессионная статистика" (табл.2.5) в ячейке В81 (термин "Стандартная ошибка"), значение – в таблице описательных статистик (ЛР-1, Лист 1, табл.3, столбец 2).

Вывод:

Погрешность линейной регрессионной модели составляет что подтверждает адекватность построенной модели -728,665+1,089х

Задача 6. Дать экономическую интерпретацию:

1) коэффициента регрессии а₁;

3) остаточных величин _i.

2) коэффициента эластичности К_Э;

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

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

Вывод:

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

6.2. Экономическая интерпретация коэффициента эластичности.

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

Средние значения и приведены в таблице описательных статистик (ЛР-1, Лист 1, табл.3).

Расчет коэффициента эластичности:



Вывод:

Значение коэффициента эластичности К_э=1,17 показывает, что при увеличении факторного признака Среднегодовая стоимость основных производственных фондов на 1% значение результативного признака Выпуск продукции увеличивается в среднем на 1,17 %.

6.3. Экономическая интерпретация остаточных величин ε_i

Каждый их остатков характеризует отклонение фактического значения y_i от теоретического значения , рассчитанного по построенной регрессионной модели и определяющего, какого среднего значения следует ожидать, когда фактор Х принимает значение x_i.

Анализируя остатки, можно сделать ряд практических выводов, касающихся выпуска продукции на рассматриваемых предприятиях отрасли.

Значения остатков _i (таблица остатков из диапазона А98:С128) имеют как положительные, так и отрицательные отклонения от ожидаемого в среднем объема выпуска продукции (которые в итоге уравновешиваются, т.е.).

Экономический интерес представляют наибольшие расхождения между фактическим объемом выпускаемой продукции y_i и ожидаемым усредненным объемом .

Вывод:

Согласно таблице остатков максимальное превышение ожидаемого среднего объема выпускаемой продукции имеют три предприятия - с номерами 7,14,30, а максимальные отрицательные отклонения - три предприятия с номерами 18, 19, 28. Именно эти шесть предприятий подлежат дальнейшему экономическому анализу для выяснения причин наибольших отклонений объема выпускаемой ими продукции от ожидаемого среднего объема и выявления резервов роста производства.

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

Уравнения регрессии и их графики построены для 3-х видов нелинейной зависимости между признаками и представлены на диаграмме 2.1 Рабочего файла.

Уравнения регрессии и соответствующие им индексы детерминации R² приведены в табл.2.10 (при заполнении данной таблицы коэффициенты уравнений необходимо указывать не в компьютерном формате, а в общепринятой десятичной форме чисел).

Таблица 2.10

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

Вид уравнения	Уравнение регрессии	Индекс детерминации R2
Полином 2-го порядка	5Е-0,5 х2 +0,670х+ 210,7	0,835
Полином 3-го порядка	7E-0,8x3 - 0,0009x2 + 5,0506x – 6265,1	0,8381
Степенная функция	0,2044x1,1789	0,8371

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

Вывод:

Максимальное значение индекса детерминации R² =0,8381.Следовательно, наиболее адекватное исходным данным нелинейное уравнение регрессии имеет вид 7E-0,8x³ - 0,0009x² + 5,0506x – 6265,1
ПРИЛОЖЕНИЕ

Результативные
таблицы и графики

Исходные данные
Номер предприятия	Среднегодовая стоимость основных производственных фондов, млн.руб.	Выпуск продукции, млн. руб.
1	3608,00	3450,50
2	4244,50	3785,50
3	4378,50	4221,00
4	4613,00	4690,00
5	3005,00	2345,00
6	4847,50	4020,00
7	4981,50	5427,00
8	3742,00	3685,00
9	4579,50	4321,50
10	5283,00	5393,50
12	5785,50	5695,00
13	4412,00	4489,00
14	4847,50	4891,00
15	5551,00	5929,50
16	6355,00	6365,00
17	4747,00	4288,00
18	5249,50	5092,00
19	4177,50	3182,50
20	5316,50	4355,00
21	5919,50	5862,50
22	4077,00	3316,50
23	3239,50	3115,50
24	5417,00	4991,50
25	4847,50	4355,00
26	4512,50	4120,50
27	3507,50	2680,00
28	4713,50	4187,50
29	5450,50	4589,50
31	5182,50	4355,00
32	3809,00	3886,00

					Таблица 2.2
Зависимость выпуска продукции от среднегодовой стоимости основных фондов
Номер группы	Группы предприятий по стоимости основеных фондов	Число предприятий	Выпуск продукции
			Всего	В среднем на одно предприятие
1	3005-3675	4	16147,00	4036,75
2	3675-4345	5	19798,50	3959,70
3	4345-5015	11	55543,00	5049,36
4	5015-5685	7	26766,50	3823,79
5	5685-6355	3	12830,50	4276,83
Итого		30	131085,50	4369,52

			Таблица 2.3
Показатели внутригрупповой вариации
Номер группы	Группы предприятий по стоимости основеных фондов	Число предприятий	Внутригрупповая дисперсия
1	3005-3675	4	216874,81
2	3675-4345	5	994044,16
3	4345-5015	11	780900,50
4	5015-5685	7	561903,70
5	5685-6355	3	85540,39
Итого		30

			Таблица 2.4
Показатели дисперсии и эмпирического корреляционного отношения
Общая дисперсия	Средняя из внутригрупповых дисперсия	Межгрупповая дисперсия	Эмпирическое корреляционное отношение
903163,1081	620585,7564	282577,3517	0,559352496

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

Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия	1	22594778,24	22594778,24	140,5861384	1,97601E-12
Остаток	28	4500115,002	160718,3929
Итого	29	27094893,24

	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%
Y-пересечение	-728,6655802	436,1611477	-1,670633856	0,10593656	-1622,101178
Переменная X 1	1,089355181	0,09187519	11,85690257	1,97601E-12	0,901157387

	Верхние 95%	Нижние 68,3%	Верхние 68,3%
Y-пересечение	164,7700179	-1173,045872	-284,2852881
Переменная X 1	1,277552975	0,995748668	1,182961694

ВЫВОД ОСТАТКА
Наблюдение	Предсказанное Y	Остатки
1	3201,727913	248,7720873
2	3895,102485	-109,6024854
3	4041,07608	179,9239204
4	4296,52987	393,4701305
5	2544,846739	-199,8467386
6	4551,983659	-531,9836595
7	4697,957254	729,0427463
8	3347,701507	337,2984931
9	4260,036471	61,46352902
10	5026,397841	367,1021592
11	5573,798819	121,2011808
12	4077,569478	411,4305218
13	4551,983659	339,0163405
14	5318,345029	611,1549707
15	6194,186595	170,8134052
16	4442,503464	-154,5034638
17	4989,904442	102,0955578
18	3822,115688	-639,6156882
19	5062,891239	-707,8912393
20	5719,772413	142,7275865
21	3712,635493	-396,1354926
22	2800,300529	315,1994715
23	5172,371435	-180,871435
24	4551,983659	-196,9836595
25	4187,049674	-66,54967386
26	3092,247717	-412,247717
27	4406,010065	-218,5100652
28	5208,864834	-619,3648336
29	4916,917645	-561,9176451
30	3420,688304	465,3116959

Рис. 1