7 лабораторных работ по Эконометрике
ПОЛНОЕ ЗАДАНИЕ В ДЕМО ФАЙЛЕ,
ЧАСТЬ ДЛЯ ПОИСКА ДУБЛИРУЮ НИЖЕ
5
Лабораторная работа №1
Простейшая обработка данных. Линейная регрессия.
Коэффициент корреляции. Его значимость
Цель: научиться находить коэффициент корреляции и определять
его значимость; находить коэффициенты регрессии и строить уравнение
регрессии.
Основные сведения
Парная регрессия – это уравнение связи двух переменных у и х:
y=f(х),
где у – зависимая переменная (результат, отклик);
х – независимая, объясняющая переменная (фактор).
Различают линейные и нелинейные регрессии.
Линейная регрессия: x y a b x
.
Построение уравнения регрессии сводится к оценке ее параметров.
Для оценки параметров регрессий, линейных по параметрам, используют
метод наименьших квадратов (МНК). МНК позволяет получить такие
оценки параметров, при которых сумма квадратов отклонений
фактических значений результативного признака у от теоретических ух
минимальна.
Для линейных и нелинейных уравнений, приводимых к линейным,
решается следующая система относительно а и в:
2
;
.
a n b x y
a x b x x y
Можно воспользоваться готовыми формулами, которые вытекают из
этой системы:
a y b x ,
2
cov , cov ,
var( ) x
x y x y
b
x
,
где
1
cov( , ) 1 ( )( )
n
i i
i
x y x x y y ху х у
n
– ковариация признаков x и y ,
____
2 2 2
x x x – дисперсия признака x и
1 2
1
1 ,
n
n
i
i
x x x x x
n n
1 2
1
1 ,
n
n
i
i
y y y y y
n n
______ 1 y x y x
n
,
____
x2 1 x2
n
2 2 2 2
1
var 1 ,
п
i х
i
x x x х х
n
2 2 2 2
1
var( ) 1 ,
п
i у
i
y y y у у
n
6
2 2
1 var( ), 1 var( ).
n n
k k
k k
y х
y y x x
y x
n n
Параметр b называется коэффициентом регрессии. Его величина
показывает среднее изменение результата с изменением фактора на одну
единицу.
Тесноту связи изучаемых явлений оценивает линейный коэффициент
парной корреляции xy r для линейной регрессии ( 1 1 xy r ):
2 2 2 2
cov , cov( , )
( )( ) var( )var( )
x
xy
y x y
x y ух х у x y r b
х х у у x y
.
Теснота линейной связи между переменными может быть оценена на
основании шкалы Чеддока:
Теснота связи Значение коэффициента корреляции при
наличии:
Прямой связи Обратной связи
Слабая 0,1–0,3 (–0,3)–(–0,1)
Умеренная 0,3–0,5 (–0,5)–(–0,3)
Заметная 0,5–0,7 (–0,7)–(–0,5)
Высокая 0,7–0,9 (–0,9)–(–0,7)
Весьма высокая 0,9–1 (–1)–(–0,9)
Положительное значение коэффициента корреляции говорит о
положительной связи между х и у, когда с ростом одной из переменных
другая тоже растет. Отрицательное значение коэффициента корреляции
означает, с ростом одной из переменных другая убывает, с убыванием
одной из переменной другая растет.
Оценку статистической значимости коэффициента корреляции
проводят с помощью t-критерия Стьюдента. Выдвигают гипотезу Н0 о
статистически незначимом отличии коэффициента от нуля. Оценка
значимости коэффициента корреляции с помощью t-критерия Стьюдента
проводится путем сопоставления его значения с величиной случайной
ошибки:
tr=r/mr.
Стандартная (случайная) ошибка коэффициента корреляции
определяется по формуле:
1 2
r 2
т r
n
.
Сравнивая фактическое и табличное (критическое) значения
t-статистики – табл t и факт t – принимает или отвергаем гипотезу Н0.
7
Если табл t < факт t , то гипотеза Н0 отклоняется, коэффициент
корреляции не случайно отличается от. Если табл t > факт t , то гипотеза Н0 не
отклоняется и признается случайная природа формирования коэффициента
корреляции.
Порядок выполнения работы.
По заданной выборке исследовать зависимость результата у от
фактора х. Для этого
1. Создать таблицу данных.
2. Найти средние значения х, у , выборочные дисперсии 2 , 2 x y S S ,
исправленные средние квадратические отклонения , x y S S .
3. Найти коэффициент корреляции и проверить его значимость.
4. Найти коэффициенты линейного уравнения регрессии.
5. Построить график прямой регрессии.
Пример выполнения лабораторной работы.
В табл. 1.1 приведены данные об объеме производства у (тыс.ед.) в
зависимости от численности занятых х (тыс.чел.) некоторой фирмы.
Таблица 1.1.
Исходные данные
х 11 13 15 18 20 22 24 25 27
у 15 17 21 20 28 33 34 32 29
1. В диапазоне В3:C11 подготовим исходные данные.
2. Вводим следующие формулы:
Ячейка Формула Примечание
D3 =B3*C3 Копируем в диапазон D3:D11
E3 =B3*B3 Копируем в диапазон E3:E11
F3 =C3*C3 Копируем в диапазон F3:F11
B12 =СРЗНАЧ(В3:В11) Копируем в диапазон В12:F12
А17 =E12-B12*B12 Выборочная средняя фактора
В17 =F12-C12*C12 Выборочная средняя результата
А20 =СТАНДОТКЛОН(B3:B11) Исправленное среднее
квадратическое отклонение
фактора
В20 =СТАНДОТКЛОН(C3:C11) Исправленное среднее
квадратическое отклонение
результата
Получим следующие результаты (см. рис. 1.1).
8
Рис. 1.1. Результаты простейшей обработки данных
3. Для определения коэффициента корреляции воспользуемся
формулой
( 2 2 )( 2 2 )
xy
r ух х у
х х у у
. Для этого в ячейку Е16
вводим формулу
=(D12-B12*C12)/КОРЕНЬ(A17*B17)
Из расчетов следует, что коэффициент корреляции r=0,97. Это
свидетельствует о том, что связь между объемом выпуска продукции и
численностью занятых весьма высокая и положительная.
4. Для проверки значимости коэффициента корреляции введем
вспомогательные данные:
Ячейки
К16 9 число предприятий;
К17 0,05 уровень значимости.
5. Далее вводим следующие формулы:
9
Н19 =КОРЕНЬ((1-E16*E16)/(K16-2)) Стандартная
ошибка
Н20 =E16/H19 t-статистика
Н21 =СТЬЮДРАСПОБР(K17;K16-2) Критическое
значение
t-статистики
Н22 =ЕСЛИ(ABS(H20)>H21;"Значим";"Незначим") Вывод
Таким образом, получим данные, представленные на рис. 1.2.
Рис. 1.2. Анализ значимости коэффициента корреляции
6. Для определения коэффициентов уравнения линейной регрессии на
основе формул
2 2
в ух у х
х х
; а у вх ,
следует в ячейки I3, I4 ввести соответственно следующие формулы:
=(D12-B12*C12)/A17;
=C12-I3*B12.
Уравнение регрессии у=7,9+1,47х.
Значение коэффициента b=1,47 говорит о том, что при увеличении
численности занятых на 1 тыс.чел. объем продукции увеличится на
1,74 тыс.ед.
Результаты расчетов приведены на рис.1.3.
10
Рис. 1.3. Результаты расчетов
7. Для построения графика выделим диапазон В3:С11. Вызовем
Мастер диаграмм. Чтобы ось отражала фактические данные,
выберем тип диаграммы Точечная. После чего нажмем кнопку
Готово. На построенной диаграмме выделим график функции,
щелкнув по нему левой кнопкой мыши. Выделение обозначается
светлыми маркерами на функции. Нажав правую кнопку мыши,
выведем контекстно-зависимое меню, в котором выберем опцию
Добавить линию тренда. В окне Линия тренда по вкладке Тип
выберем тип функции Линейная, а во вкладке Параметры –
установим флажок показывать уравнение на диаграмме. В
результате на диаграмме появиться вид теоретической кривой –
тренда и ее уравнение (рис.1.4).
Рис. 1.4. Графики фактических данных и построенной регрессии
11
8. Вычисление параметров регрессии с помощью статистических
функций Excel:
КОРРЕЛ(массив1;массив2) вычисляет коэффициент корреляции
между двумя переменными; значения первой из них приведены в
диапазоне массив1, значения второй – в диапазоне массив2;
НАКЛОН(известные_значения_y;известные_значения_x) служит
для определения коэффициента b;
ОТРЕЗОК(известные_значения_y;известные_значения_x)
служит для определения коэффициента a.
Вводим формулы:
С27 =КОРРЕЛ(B3:B11;C3:C11) Коэффициент корреляции
С28 =НАКЛОН(C3:C11;B3:B11) Коэффициент b
С29 =ОТРЕЗОК(C3:C11;B3:B11) Коэффициент a
Встроенная статистическая функция ЛИНЕЙН определяет
параметры линейной регрессии. Порядок вычислений следующий:
1) выделите область пустых ячеек 5х2 (5 строк, 2 столбца) с целью
вывода результатов регрессионной статистики (А27:В3);
2) в главном меню выберите Вставка/Функция;
3) в строке Категория (рис.1.5) выберите Статистические, в окне
Функция – ЛИНЕЙН. Щелкните ОК.
Рис. 1.5. Диалоговое окно «Мастер функций»
12
4) Заполните аргументы функции (рис.1.6.):
Известные_значения_у – диапазон, содержащий данные
результативного признака;
Известные_значения_х – диапазон, содержащий данные факторов
независимого признака;
Константа – логическое значение, которое указывает на наличие
или на отсутствие свободного члена в уравнении; если Константа = 1, то
свободный член рассчитывается обычным образом, если Константа = 0,
то свободный член равен 0.
Статистика – логическое значение, которое указывает выводить
дополнительную информацию по регрессионному анализу или нет. Если
Статистика = 1, то дополнительная информация выводится, если
Статистика = 0, то выводится только оценки параметров уравнения.
Далее ОК.
Рис.1.6. Диалоговое окно ввода аргументов функции ЛИНЕЙН
5) В левой верхней ячейке выделенной области появится первый
элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите на
клавишу F2, а затем – на комбинацию клавиш CTRL+SHIFT+ENTER.
Дополнительная регрессионная статистика будет выводиться в порядке,
указанном в следующей схеме:
Значение коэффициента b Значение коэффициента а
Среднеквадратическое отклонение b Среднеквадратическое отклонение а
Коэффициент детерминации R2 Среднеквадратическое отклонение у
F-статистика Число степеней свободы
Регрессионная сумма квадратов Остаточная сумма квадратов.
Результаты регрессионного анализа представлены на рис.1.7.
13
Рис. 1.7.Результаты регрессионного анализа
Индивидуальное задание к лабораторной работе
По предприятиям легкой промышленности региона получена
информация, характеризующая зависимость объема выпуска продукции (y,
млн. руб.) от объема капиталовложений (x, млн. руб.)
№ 1 2 3 4 5 6 7 8 9 10
1 x 66 58 73 82 81 84 55 67 81 59
y 133 107 145 162 163 170 104 132 159 116
2 x 72 52 73 74 76 79 54 68 73 64
y 121 84 119 117 129 128 102 111 112 98
3 x 38 28 27 37 46 27 41 39 28 44
y 69 52 46 63 73 48 67 62 47 67
4 x 36 28 43 52 51 54 25 37 51 29
y 104 77 117 137 143 144 82 101 132 77
5 x 31 23 38 47 46 49 20 32 46 24
y 38 26 40 45 51 49 34 35 42 24
6 x 33 17 23 17 36 25 39 20 13 12
y 43 27 32 29 45 35 47 32 22 24
7 x 36 28 43 52 51 54 25 37 51 29
y 85 60 99 117 118 125 56 86 115 68
8 x 17 22 10 7 12 21 14 7 20 3
y 26 27 22 19 21 26 20 15 30 13
9 x 12 4 18 27 26 29 1 13 26 5
y 21 10 26 33 34 37 9 21 32 14
10 x 26 18 33 42 41 44 15 27 41 19
y 43 28 51 62 63 67 26 43 61 33
Отчет по лабораторной работе
1. Запишите уравнение линейной парной регрессии для своего варианта и
поясните экономическую сущность параметров уравнения.
2. Что является показателем тесноты связи в парной линейной регрессии?
3. Каково значение коэффициента корреляции?
4. Каково значение коэффициента детерминации и что он характеризует?
5. Как оценивается значимость коэффициента корреляции?
6. Является ли коэффициент корреляции для вашего варианта значимым и
почему?
_
14
Лабораторная работа №2
Проверка качества уравнения линейной регрессии
Цель: научиться проверять статистическую значимость
коэффициентов и общего качества уравнения линейной регрессии.
Основные сведения
Оценку качества построенной модели дает коэффициент (индекс)
детерминации 2 ( 2 ) xy xy r , а также средняя ошибка аппроксимации.
Средняя ошибка аппроксимации – среднее отклонение расчетных
значений от фактических:
1 x 100%. A y y
n y
Допустимый предел значений средней ошибки аппроксимации – не
более 8–10%.
Согласно основной идее дисперсионного анализа, общая сумма
квадратов отклонений переменной y от среднего значения y
раскладывается на две части – «объясненную» и «необъясненную»:
2 2
2
x x y y y y y y
,
где 2 y y – общая сумма квадратов отклонений;
2
x y y
– сумма
квадратов отклонений, объясненная регрессией (или факторная сумма
квадратов отклонений);
2
x y y
– остаточная сумма квадратов
отклонений, характеризующая влияние неучтенных в модели факторов.
2 2
y y y n ;
2
2 2 2 2
x y x y y n R b n
;
2
2 (1 2 ) x y y y n R
.
Определение дисперсии на одну степень свободы приводит
дисперсии к сравнимому виду. Сопоставляя факторную и остаточную
дисперсии в расчете на одну степень свободы, получим величину F -
критерия Фишера:
2
факт
2
ост
S
F
S
.
Фактическое значение F -критерия Фишера (1.9) сравнивается с
табличным значением табл 1 2 F ; k ; k при уровне значимости и степенях
свободы 1 k m и 2 k n m 1. При этом, если фактическое значение
F -критерия больше табличного, то признается статистическая значимость
уравнения в целом.
15
Для парной линейной регрессии m 1, поэтому
2
2
факт
2 2
ост
2
x
x
S y y
F n
S
y y
.
Величина F -критерия связана с коэффициентом детерминации 2
xy r , и
ее можно рассчитать по следующей формуле:
2
2 2
1
xy
xy
r
F n
r
.
В парной линейной регрессии оценивается значимость не только
уравнения в целом, но и отдельных его параметров. С этой целью по
каждому из параметров определяется его стандартная ошибка: b m и a m .
Стандартная ошибка коэффициента регрессии определяется по
формуле:
2
ост ост
b 2
x
m S S
x x n
,
где
2
2
ост 2
x y y
S
n
– остаточная дисперсия на одну степень свободы.
Величина стандартной ошибки совместно с t -распределением
Стьюдента при n 2 степенях свободы применяется для проверки
существенности коэффициента регрессии и для расчета его
доверительного интервала.
Для оценки существенности коэффициента регрессии его величина
сравнивается с его стандартной ошибкой, т.е. определяется фактическое
значение t -критерия Стьюдента: b
b
t b
m
которое затем сравнивается с
табличным значением при определенном уровне значимости и числе
степеней свободы n 2. Доверительный интервал для коэффициента
регрессии определяется как табл b b t m .
Стандартная ошибка параметра a определяется по формуле:
2 2
2
a ост 2 ост
x
x x
m S S
n x x n
.
Процедура оценивания существенности данного параметра не
отличается от рассмотренной выше для коэффициента регрессии.
Вычисляется t -критерий: a
a
t a
m
, его величина сравнивается с табличным
16
значением при n 2 степенях свободы. Доверительный интервал для
коэффициента регрессии определяется как табл a a t m .
Если в границы доверительного интервала попадает ноль, т.е.
нижняя граница отрицательна, а верхняя положительна, то оцениваемый
параметр принимается нулевым, т.к. он не может одновременно принимать
и положительное, и отрицательное значения.
Порядок выполнения лабораторной работы
Используя данные к лабораторной работе №1, найти уравнение
линейной регрессии и проверить:
I. Значимость коэффициента b. Для этого надо найти:
1. Сумму квадратов остатков:
2 ( )2 i x е y y .
2. Найти сумму квадратов отклонений:
(x x)2 .
3. Стандартную ошибку параметра b:
2
2
( )
.
( 2) ( )
x
b
y y
т
n x x
4. Наблюдаемой значение t-статистики параметра b:
tb=b/mb.
5. Число степеней свободы k=n−2 и критическое значение табл ;k t t .
6. Сделать вывод о значимости коэффициента b: если табл t < b t , то
параметр регрессии b статистически значим, а в противном случае
статистически незначим.
II. Значимость коэффициента а. Для этого надо найти:
1. Сумму квадратов:
х2 .
2. Стандартную ошибку параметра а:
2
. а b
х
т т
n
3. Наблюдаемой значение t-статистики параметра а:
ta=a/ma.
4. Сделать вывод о значимости коэффициента а: если табл t < a t , то
параметр регрессии а статистически значим, а в противном случае
статистически незначим.
III. Общее качество уравнения регрессии. Для этого надо найти:
1. Сумму квадратов отклонений:
17
(y y)2 .
2. Коэффициент детерминации:
2
2
2 1 .
( )
i е
R
y y
3. Наблюдаемое значение F-статистики:
2
2 ( 2).
факт 1
F R п
R
4. Число степеней свободы критерия Фишера-Снедекора:
k1=1; k2= n−2 и критическое значение этого критерия Fтабл= ;k1;k2 F .
5. Сделать вывод о значимости уравнения регрессии: если Fфакт >
Fтабл, то уравнение регрессии статистически значимо и надежно, если Fфакт
< Fтабл признается статистическая незначимость, ненадежность уравнения
регрессии.
IV. Общее качество уравнения регрессии с помощью средней
ошибки аппроксимации. Для этого надо найти:
1. Отклонения:
х у у .
2. Аi:
x 100%.
i
А y y
y
3. Среднюю ошибку аппроксимации A:
1 . i A A
n
4. Сделать вывод о качестве уравнения регрессии: если A не
превышает предела значений 8-10%, то качество модели хорошее.
V. Представить результаты с помощью инструмента анализа данных
Регрессия ППП Excel.
Пример выполнения лабораторной работы
1. В диапазоне А2:С11 подготовить исходные.
2. Введем вспомогательные данные:
Ячейка Формула Примечание
С16 9 Число предприятий
С17 0,05 Уровень значимости
С18 =ОТРЕЗОК(C3:C11;B3:B11) Коэффициент a
С19 =НАКЛОН(C3:C11;B3:B11) Коэффициент b
С20 =СРЗНАЧ(B3:B11) Среднее значение фактора
С21 =СРЗНАЧ(C3:C11) Среднее значение результата
18
Проверка значимости коэффициента b.
1) Для расчетов сумм квадратов отклонений введем формулы:
Ячейка Формула Примечание
D3 =$C$18+$C$19*B3 Копируем в диапазон D3:D11
E3 =(C3-D3)^2 Копируем в диапазон E3:E11
F3 =(B3-$C$20)^2 Копируем в диапазон F3:F11
E12 =СУММ(E3:E11) 2 ( )2 i x е y y
F12 =СУММ(F3:F11) (x x)2
2) Стандартная ошибка параметра b определяется по формуле:
2
2
( )
( 2) ( )
x
b
y y
т
n x x
,
поэтому введем в ячейку D24 формулу:
=(E12/((C16-2)*F12))^0,5.
3) В ячейке D25 рассчитана t-статистика параметра b как отношение
величины этого параметра к его стандартной ошибке:
=C19/D24.
4) Критическое значение t-статистики определим в ячейке D26 с
помощью функции СТЬЮДРАСПОБР, у которой первым
аргументом является пороговая значимость или вероятность (в
нашем случае примем ее равной 0,05), а вторым – число степеней
свободы (n–2=9–2=7). Таким образом, формула, введенная в D26,
должна иметь вид:
=СТЬЮДРАСПОБР($C$17;$C$16-2).
5) Для того чтобы автоматически был получен вывод о значимости
параметра b построим в ячейке D27 формулу:
=ЕСЛИ(ABS(D25)>D26;"Значим";"Незначим").
6) Для расчета доверительного интервала определяем предельную
ошибку в ячейке D28:
=D26*D24.
7) Нижняя граница доверительного интервала в ячейке D29: =C19-D28.
8) Верхняя граница доверительного интервала в ячейке D30:
=C19+D28.
Таким образом, доверительный интервал параметра b имеет вид
(1,12; 1,83).
Проверка значимости коэффициента а.
Вводим формулы:
19
Ячейка Формула Примечание
G3 =B3*B3 Копируем в
диапазон G3:G11
G12 =СУММ(E3:E11) х2
D33 =D24*КОРЕНЬ(G12/C16) 2
. а b
х
т т
n
D34 =C18/D33 t-статистика
параметра a
D35 =СТЬЮДРАСПОБР($C$17;$C$16-2) Критическое
значение
t-статистики
D36 =ЕСЛИ(ABS(D34)>D35;"Значим";"Незначим")
D37 =D35*D33 Предельная
ошибка
D38 =C18-D37 Нижняя граница
доверительного
интервала
D39 =C18+D37 Верхняя граница
доверительного
интервала
Проверка общего качества уравнения регрессии с помощью
F-теста.
Вводим формулы:
Ячейка Формула Примечание
D42 =(КОРРЕЛ(B3:B11;C3:C11)^2) Коэффициент
детерминации
D43 =D42*(C16-2)/(1-D42) F-статистика
D44 =FРАСПОБР(C17;1;C16-2) Критическое
значение
F-статистики
D45 =ЕСЛИ(D43>D44;"Значимо";"Незначимо")
Результаты расчетов приведены на рис. 1.2.
20
Рис. 1.2. Анализ значимости уравнения регрессии
Проверка общего качества уравнения регрессии с помощью
средней ошибки аппроксимации.
Вводим формулы:
Ячейка Формула Примечание
Н3 =C3-D3
I3 =ABS(H3/C3)*100
I12 =СРЗНАЧ(I3:I11) Средняя ошибка аппроксимации
21
В среднем расчетные значения отклоняются от фактических на 4,5%.
Качество построенной модели оценивается как хорошее, так как средняя
ошибка аппроксимации не превышает 8-10% (см. рис. 2.2).
Рис. 2.2. Анализ общего уравнения регрессии с помощью ошибки
аппроксимации
Представим результаты с помощью инструмента анализа данных
Регрессия ППП Excel.
Для этого:
В главном меню выберите Сервис – Анализ данных – Регрессия – ОК.
Заполнить диалоговое окно ввода данных и параметров вывода (см.
рис. 3.2).
Рис. 3.2. Окно Регрессия
Входной интервал Y – диапазон, содержащий данные
результативного признака;
Входной интервал Х – диапазон, содержащий данные факторов
независимого признака;
Метки – флажок, который указывает, содержит ли первая строка
название столбцов или нет;
22
Константа – ноль – флажок, указывающий на наличие или
отсутствие свободного члена в уравнении;
Выходной интервал – достаточно указать левую верхнюю ячейку
будущего диапазона;
Новый рабочий лист – можно задать произвольное имя нового листа.
На новом рабочем листе появляются данные регрессионного анализа
(см. рис. 4.2.)
Рис. 4.2. Результаты регрессионного анализа
Отчет по лабораторной работе
1. Запишите уравнение линейной парной регрессии для своего варианта.
2. Как оценивается значимость параметров уравнения регрессии?
3. Являются ли параметры уравнения регрессии для вашего варианта
значимыми и почему?
4. Запишите доверительные интервалы для параметров уравнения
регрессии для вашего варианта.
5. Каким образом осуществляется проверка значимости уравнения в
целом.
6. Значимо ли уравнение регрессии для вашего варианта и почему?
7. Каким образом осуществляется проверка качества уравнения
регрессии?
8. В чем смысл средней ошибки аппроксимации и каково ее значение для
вашего варианта?
9. Сравнить полученные результаты с результатами применения
инструмента Регрессия.
_
23
Лабораторная работа №3
Нелинейные модели. Коэффициент детерминации
Цель: научиться строить нелинейные модели и находить
коэффициент детерминации.
Основные сведения
Если между экономическими явлениями существуют нелинейные
соотношения, то они выражаются с помощью соответствующих
нелинейных функций.
Различают два класса нелинейных регрессий:
1. Регрессии, нелинейные относительно включенных в анализ
объясняющих переменных, но линейные по оцениваемым параметрам,
например
– полиномы различных степеней – 2
yx a b x c x ;
– равносторонняя гипербола –
x y a b x ;
– полулогарифмическая функция – ln x y a b x .
2. Регрессии, нелинейные по оцениваемым параметрам, например
– степенная – b
x y a x ;
– показательная – x
x y a b ;
– экспоненциальная – у=аеbх.
Регрессии нелинейные по включенным переменным приводятся к
линейному виду простой заменой переменных, а дальнейшая оценка
параметров производится с помощью метода наименьших квадратов.
Несколько иначе обстоит дело с регрессиями нелинейными по
оцениваемым параметрам, которые делятся на два типа: нелинейные
модели внутренне линейные (приводятся к линейному виду с помощью
соответствующих преобразований, например, логарифмированием) и
нелинейные модели внутренне нелинейные (к линейному виду не
приводятся).
К внутренне линейным моделям относятся, например, степенная
функция – b
x y a x , показательная – x
x y a b , экспоненциальная –
ˆ bx
х у а е , обратная – 1
x y
a b x
.
К внутренне нелинейным моделям можно, например, отнести
следующие модели: c
x y a b x , 1 1
x 1 b y a
x
.
Приведем формулы для расчета параметров наиболее часто
используемых типов уравнений регрессии (табл. 1.3):
24
Таблица 1.3
Вид функции, y Линеаризация
Параметры
уравнения
регрессии
Искомое
уравнение
Степенная
у=ахb
Х=lnх,
Y=lny,
A=lna,
B=b
2 ( )2
b YX Y X
X X
A Y bX
у=eA·хb
Показательная
у=аbх
Х=х,
Y=lny,
В=lnb,
A=lna,
2 ( )2
B Yx Y x
x x
A Y Bx
у=(eA)·(eB)x
Обратная
y 1
a b x
Х=х,
Y=1/y,
A=a,
B=b
2 ( )2
b Yx Y x
x x
а Y bx
y 1
a b x
Полулогарифмическая
y a b ln x
Х=lnх,
Y=y,
A=a,
B=b
2 ( )2
b yX y X
X X
а y bX
y a b ln x
Гиперболическая
y a b x
Х=1/х,
Y=y,
A=a,
B=b
2 ( )2
b yX y X
X X
a y bX
y a b x
Экспоненциальная
у=аеbх
Х=х,
Y=lny,
A=lna,
B=b
2 ( )2
b Yx Y x
x x
A Y bx
у=eA·ebx
В случае нелинейной зависимости тесноту связи между величинами
оценивают по величине корреляционного отношения:
2 2
2 2 1 x 1 ост
xy
y
y y
y y
.
Интервал изменения корреляционного отношения 0 1 xy .
Оценку качества построенной модели дает индекс детерминации 2
xy .
Коэффициент детерминации R2= 2
xy – квадрат индекса корреляции –
характеризует долю дисперсии, объясняемую регрессией, в общей
дисперсии результативного признака у.
25
2
2
2
( )
1 .
( )
x y у
R
y y
Чем ближе коэффициент детерминации к 1, тем выше качество
уравнения регрессии, тем в большей мере оно объясняет поведение
отклика.
Порядок выполнения работы.
Используя данные лабораторной работы №1, построить линейную,
степенную, показательную, экспоненциальную, полулогарифмическую,
гиперболическую и обратную модели и с помощью коэффициента
детерминации сравнить эти модели. Для чего необходимо:
1. Найти уравнение регрессии.
2. Найти общую сумму квадратов отклонений и остаточную сумму
квадратов отклонений.
3. Найти коэффициент детерминации.
4. Найти параметры регрессии с помощью статистической функции
ЛИНЕЙН.
Пример выполнения лабораторной работы.
Создадим новую рабочую книгу с семью листами.
Название листа Назначение
Линейная Для анализа линейной модели
Степенная Для анализа степенной модели
Показательная Для анализа показательной модели
Обратная Для анализа обратной модели
Полулогарифмическая Для анализа полулогарифмической модели
Гиперболическая Для анализа гиперболической модели
Экспоненциальная Для анализа экспоненциальной модели
Будем использовать данные из лабораторной работы №1.
1. Лист Линейная оформим, как показано на рис.1.3:
Рис. 1.3. Лист Линейная
26
На этом листе коэффициенты линейной регрессии определяются с
помощью статистических функций (см. лабораторную работу №1).
Для расчета сумм, которые понадобятся при определении
коэффициента детерминации (и при выполнении следующей лабораторной
работы), введем формулы:
Ячейка Формула Примечание
Е3 =$A$16+$B$16*B3 Расчет теоретических значений
результата ут.
Копируем в диапазон Е3:Е11
F3 =(C3-$C$12)^2 Копируем в диапазон F3:F11
G3 =(C3-E3)^2 Копируем в диапазон G3:G11
Н3 =(E3-$C$12)^2 Копируем в диапазон H3:H11
F12 =СУММ(F3:F11) Копируем в диапазон F12:H12
Замечание. В приведенных формулах неоднократно используется
абсолютная адресация, содержащая знак «$». Это необходимо для того,
чтобы при копировании формул данный адрес не изменялся. Для того
чтобы превратить относительный адрес А16 в абсолютный ($A$16),
достаточно нажать клавишу F4 в то время, когда курсор находится на
ячейке А16.
Для вычисления коэффициента детерминации в ячейку Н15 введем
формулу:
=1-G12/F12.
2. Регрессия в виде степенной функции имеет вид: у=ахb.
Для нахождения параметров регрессии у=ахb необходимо провести
ее линеаризацию:
Y=A+bX,
где Y=ln y, X=ln x, A=ln a.
Составляем вспомогательную таблицу для преобразованных данных
(рис. 2.3):
27
Рис. 2.3 Лист Степенная
Вводим формулы:
Ячейка Формула Примечание
D3 =LN(C3) Y=ln y
Копируем в диапазон D3:D11
E3 =LN(B3) X=ln x
Копируем в диапазон E3:E11
F3 =D3*E3 Копируем в диапазон F3:F11
G3 =E3^2 Копируем в диапазон G3:G11
D12 =СРЗНАЧ(D3:D11) Копируем в диапазон D12:G12
Для вычисления коэффициентов регрессии введем следующие
формулы
Ячейка Формула Примечание
В16 =(F12-E12*D12)/(G12-E12^2) b
А16 =D12-B16*E12 A
После потенцирования находим искомые коэффициенты регрессии:
Ячейка Формула Примечание
А19 =EXP(A16) а
В19 =B16 b
Тогда уравнение регрессии будет иметь вид: у=4,02х0,75.
Для расчета сумм введем формулы:
28
Ячейка Формула Примечание
I3 =$A$19*B3^$B$19 Расчет теоретических значений
результата ут.
Копируем в диапазон I3:I11
J3 =(C3-$C$12)^2 Копируем в диапазон J3:J11
K3 =(C3-E3)^2 Копируем в диапазон K3:K11
L3 =(E3-$C$12)^2 Копируем в диапазон L3:L11
J12 =СУММ(J3:J11) Копируем в диапазон J12:L12
Для вычисления коэффициента детерминации в ячейку L15 введем
формулу:
=1-K12/J12.
Проведем расчеты параметров регрессии с помощью статистической
функции ЛИНЕЙН.
Выделим диапазон А22:В26. введем формулу
=ЛИНЕЙН(D3:D11;E3:E11;1;1).
В левой верхней ячейке выделенной области появится первый
элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите на
клавишу F2, а затем – на комбинацию клавиш CTRL+SHIFT+ENTER.
3. Расчеты на остальных листах во многом повторяют расчеты,
произведенные на листе Степенная, поэтому остальные листы лучше
всего получить копированием листа Степенная.
Для этого необходимо:
находясь на листе Степенная, выделить его полностью,
щелкнув мышью на пересечении названий столбцов и строк; с
помощью кнопки (Копировать) скопировать лист в Буфер
обмена;
перейти на следующий лист и выделив ячейку А1,щелкнуть
мышью по кнопке (Вставить).
Получим следующие результаты (рис. 3.3-7.3):
29
Рис. 3.3. Лист Показательная
Рис. 4.3. Лист Обратная
30
Рис. 5.3. Лист Полулогарифмическая
Рис. 6.3. Лист Гиперболическая
31
Рис. 7.3. Лист Экспоненциальная
Выберем наилучшую модель, для чего объединим результаты
построения парных регрессий в одной таблице (табл. 2.1).
Все уравнения регрессии достаточно хорошо описывают исходные
данные. Некоторое предпочтение можно отдать показательной или
экспоненциальной функции, для которых значение коэффициента
детерминации наибольшее.
Вид регрессии Уравнение регрессии Коэффициент
детерминации
Линейная у=7,9+1,47х 0,9335
Степенная у=4,03х0,75 0,9283
Показательная у=15,83·1,04х 0,9353
Обратная у=1/(0,05–0,0012х) 0,9145
Полулогарифмическая у= –39,74+26,07lnx 0,9060
Гиперболическая у=60,41–425,79/х 0,8528
Экспоненциальная у=15,83е0,0417х 0,9353
Отчет по лабораторной работе
1. Запишите все виды моделей, нелинейных относительно
включаемых переменных и оцениваемых параметров.
2. Как осуществляется линеаризация модели?
3. Назовите показатели корреляции, используемые при нелинейных
соотношениях рассматриваемых признаков.
4. Записашите уравнения линейной, степенной, показательной,
экспоненциальной, полулогарифмической, гиперболической и
обратной моделей и с помощью коэффициента детерминации
сравнить эти модели.
_
32
Лабораторная работа №4
Прогнозирование на основании линейной регрессии
Цель: научиться прогнозировать индивидуальные значения
зависимой переменной на основании линейной регрессии; уметь
определять точность прогноза.
Краткие теоретические сведения.
Пусть по заданной выборке объема n найдено выборочное уравнение
линейной регрессии
у=а+bх
С помощью этого уравнения можно прогнозировать значение
результата ур при определенном прогнозном значении фактора хр.
Прогнозное значение ур определяется путем подстановки в уравнение
регрессии у=а+bх соответствующего прогнозного значения хр.
Точное уравнение регрессии нам неизвестно. Поэтому мы не можем
сделать точный прогноз. Можно только утверждать, что прогнозное
значение результата ур при данном хр с вероятностью γ попадет в
доверительный интервал γр. Вероятность γ называется уровнем
надежности.
Ошибка прогноза составляет:
2
2
( )
1 ( ) 1
х х
х х
п
т S р
р ,
где
2 ( )2
2 2
x у а у b ху y y
S
п n
– стандартная
ошибка регрессии (дисперсия ошибки или остаточная дисперсия).
Предельная ошибка прогноза, составит:
р табл р . t т
Доверительный интервал прогноза:
. р р р у
Точность прогноза можно оценить с помощью относительной
ошибки прогноза:
100%
p
p
p y
.
33
Порядок выполнения работы.
Используя данные к лабораторной работе №1 при хр=20:
1. найти уравнение регрессии;
2. рассчитать доверительный интервал прогноза при значениях уровня
надежности 80%, 90%, 95%;
3. найти относительную ошибку прогноза;
4. построить графики линии регрессии с доверительными границами.
Пример выполнения лабораторной работы.
Расчеты для каждого из уровней надежности производить на
отдельных листах, которые назовем , соответственно: 80%, 90%, 95%.
I. Лист 80%.
9. В диапазоне А2:C11 подготовим исходные данные.
10. В ячейку В12 запишем значение хр=15,5, для которого необходимо
спрогнозировать значение результата ур.
11. Вводим следующие формулы:
Ячейка Формула Примечание
С16 =ОТРЕЗОК(С3:С11;В3:В11) Определяем коэффициенты
С17 =НАКЛОН(C3:C11;B3:B11) регрессии
С18 =СРЗНАЧ(B3:B11) х
С19 =СТОШYX(C3:C11;B3:B11) Стандартная ошибка регрессии
С20 9 Количество предприятий
С21 =80/100 Уровень надежности
С22 =1−С21 Уровень значимости
С23 =С20−2 Число степеней свободы
С24 =СТЬЮДРАСПОБР(C22;C23) критическое значение t-
статистики
D3 =(B3−$C$18)^2 Формулу скопируем вниз до
D11
D12 =СУММ(D3:D11) Находим (x x)2
С27 =C19*КОРЕНЬ(1+1/C20+(B12-
C18)^2/D12)
Стандартная ошибка прогноза
С28 =C24*C27 Предельная ошибка прогноза
С29 =C16+C17*B12 Прогнозное значение
результата
С30 =C29−C28 Нижняя граница
доверительного интервала
С31 =C29+C28 Верхняя граница
доверительного интервала
С32 =С28*100/С29 Относительная ошибка
прогноза
34
12. Для графического представления полученных результатов:
Вводим следующие формулы:
Е3 =$C$16+$C$17*B3 Копируем вниз до Е11
F3 =$C$19*КОРЕНЬ(1+1/$C$20+(B3-
$C$18)^2/$D$12)
Копируем вниз до F11
G3 =F3*$C$24 Копируем вниз до G11
Н3 =E3−G3 Копируем вниз до Н11
I3 =E3+G3 Копируем вниз до I11
Таким образом получим данные, представленные на рис. 1.4.
Выделим одновременно диапазоны В2:С11, E2:E11, H2:I11
(поскольку эти диапазоны несмежные, при этом должна быть
нажата клавиша Ctrl);
Вызовем Мастер диаграмм. Чтобы ось отражала фактические
данные, выберем тип диаграммы Точечная;
Для добавления на диаграмму прогнозируемых значений в
Мастере диаграмм на шаге 2 перейдем на вкладку Ряд (рис. 2.4).
Щелкнем по кнопке Добавить и введем с помощью левой кнопки
мыши: Имя − Прогноз, Значения Х – В12, Значения Y – С29.
Щелкнув по кнопке Готово, получим диаграмму, представленную
на рисунке 3.4.
Отформатируем диаграмму. Для этого щелкнем дважды по фону и
выберем заливку прозрачная, затем щелкнем дважды по линии регрессии
и выберем тип линии, цвет и толщину, а переключатель маркера поставим
в положение отсутствует. Аналогичным образом форматируются линии,
представляющие границы доверительных интервалов, и точки,
отображающие прогнозируемые значения. В итоге получим диаграмму,
представленную на рис. 4.4.
II. Лист 90% и 95%.
Чтобы получить расчеты для уровней надежности 90% и 95%,
достаточно скопировать лист 80% на листы 90% и 95% и ввести на них в
ячейку С21 соответственно значения 0,9 и 0,95. При этом диаграммы,
полученные при таком копировании, следует удалить и построить заново
на основе расчетов, полученных на листах 90% и 95% (рис. 5.4 и 6.4).
35
Рис. 1.4. Прогнозирование на основании линейной модели при уровне
надежности 80%
36
Рис. 2.4. Шаг 2 Мастера диаграмм
Рис. 3.4. Диаграмма, построена с помощью Мастера
37
Рис. 4.4. Итоговый вид диаграммы при уровне надежности 80%
Рис. 5.4. Прогнозирование на основании линейной модели при уровне
надежности 90%
38
Рис. 6.4. Прогнозирование на основании линейной модели при уровне
надежности 95%
Сравним относительные погрешности прогнозов при различных
уровнях надежности, для хр=15,5:
Уровень надежности 80% 90% 95%
Относительная погрешность 11,7% 15,7% 19,6%
Повышение уровня надежности с 80% до 95% снижает точность прогноза в
19,6/11,7≈1,68 раза.
Отчет по лабораторной работе
1. Запишите завершающие данные: прогнозное значение результата,
стандартная и предельная ошибки прогноза, доверительный интервал
прогноза, относительная погрешность прогноза.
2. Сравнить относительные погрешности прогнозов при различных
уровнях надежности, например, для хр=20.
_
39
Лабораторная работа №5
Многофакторная линейная регрессия. Мультиколлинеарность
Цель: научиться проверять факторы на мультиколлинеарность;
находить уравнение многофакторной линейной регрессии, проверять его
качество; находить средние коэффициенты эластичности.
Краткие теоретические сведения.
Множественная регрессия – уравнения связи с несколькими
независимыми переменными:
( , ,..., ), 1 2 p y f x x x
где у – зависимая переменная (результативный признак);
х1, х2,…, хр – независимые переменные (факторы).
Для оценки параметров уравнения множественной регрессии
применяют метод наименьших квадратов (МНК).
Линейное уравнение регрессии имеет вид: у=b1x1+b2x2+a.
Средний коэффициент эластичности Э показывает, на сколько
процентов в среднем по совокупности изменится результат у от своей
средней величины при изменении фактора хj на 1% от своего среднего
значения. Средние коэффициенты эластичности для линейной регрессии
рассчитываются по формуле:
. j
j
yx j
x
Э b
y
Качество построенной модели в целом оценивает коэффициент
(индекс) детерминации:
.
( )
1 2
2
2
y y
е
R i
F-тест – оценивание качества уравнения регрессии – состоит в
проверке гипотезы Н0 о статистической незначимости уравнения
регрессии и показателя тесноты связи. Для этого выполняется сравнение
фактического Fфакт и критического(табличного) Fтабл значений F-критерия
Фишера. Fфакт определяется из соотношения значений факторной и
остаточной дисперсий, рассчитанных на одну степень свободы:
.
Число степеней свободы критерия Фишера-Снедекора: k1=m;
k2= n−m–1 и критическое значение этого критерия Fкр= ; k1 ; k2 F .
Если Fфакт > Fтабл, то гипотеза Н0 о случайной природе оцениваемых
характеристик отклоняется и признается их статистическая значимость и
m
n m
R
F R 1
1 2
2
40
надежность. Если Fфакт < Fтабл, то гипотеза Н0 не отклоняется и признается
статистическая незначимость, ненадежность уравнения регрессии.
Оценка значимости коэффициентов регрессии с помощью t-критерия
Стьюдента сводится к вычислению наблюдаемого значения t-статистики:
.
;
a
a набл
b
j
j набл
m
t a
m
b
t
j
,
где
bi m – средняя квадратическая ошибка коэффициента регрессии ,
она может быть определена по следующей формуле:
а m – средняя квадратическая ошибка коэффициента регрессии а.
Критическое значение t-статистики: tкр=tα;n–m–1 где k=n–m–1 – число
степеней свободы, m – число факторов;
Если tнабл tкр , то коэффициент регрессии статистически значим; в
противном случае – статистически незначим.
При построении уравнений множественной регрессии может
возникнуть проблема мультиколлинеарности факторов, их тесной
линейной связанности.
Считая, что две переменные явно коллинеарны, то есть находятся
между собой в линейной зависимости, если 0,7. xi xj r
Статистическая функция ЛИНЕЙН
Анализ многофакторных моделей в Excel удобно проводить с
помощью статистической функции ЛИНЕЙН, которая на основе МНК
рассчитывает массив данных, описывающих уравнение линейной
многофакторной регрессии у=b1x1+b2x2+a.
Синтаксис
ЛИНЕЙН(известные_значения_y;известные_значения_x;конст;статистика)
Известные_значения_y – множество значений y.
Известные_значения_x – диапазон известных значений факторов.
Конст – логическое значение, которое указывает, требуется ли,
чтобы константа (свободный член) а была равна 0.
Если конст имеет значение 1 или опущено, то а вычисляется
обычным образом.
i b
.
1
1
1
1
...
2
2
...
1
1
R n m
R
m
i i p
p
i
x x x x
y yx x
b
41
Если аргумент конст имеет значение 0, то свободный член
полагается равным нулю.
Статистика – логическое значение, которое указывает, требуется ли
вернуть дополнительную статистику по регрессии.
Если аргумент статистика имеет значение 1 то функция ЛИНЕЙН
возвращает дополнительную регрессионную статистику.
Если аргумент статистика имеет значение 0 или опущен, то функция
ЛИНЕЙН не возвращает дополнительную регрессионную статистику.
В случае, когда значение аргумента стат равно 1, таблица
результатов, выводимых функцией ЛИНЕЙН имеет вид:
b2 b1 a
mb2 mb1 ma
R2 S
Fнабл k
Sфакт Sостат
Величина Описание
mb2 mb1 ma Стандартные значения ошибок для коэффициентов.
R2 Коэффициент детерминации.
S Стандартная ошибка регрессии.
Fнабл
F-статистика, или F-наблюдаемое значение.
F-статистика используется для определения того,
является ли наблюдаемая взаимосвязь между зависимой
и независимой переменными случайной или нет.
k Степени свободы. k=n–m–1.
Sфакт Регрессионная сумма квадратов.
Sостат Остаточная сумма квадратов.
Регрессия и Excel
Excel позволяет при построении уравнения линейной регрессии
большую часть работы сделать очень быстро. Важно понять, как
интерпретировать полученные результаты. Воспользуемся надстройкой
Пакет анализа.
Сервис – Анализ данных – Регрессия – ОК. Появляется диалоговое
окно, которое нужно заполнить. В графе Входной интервал Y: указывается
ссылка на ячейки, содержащие значения результативного признака у. В
графе Входной интервал X: указывается ссылка на ячейки, содержащие
значения факторов х1, ..., хт (т < 16). Если первые из ячеек содержат
пояснительный текст, то рядом со словом Метки нужно поставить
«галочку». Уровень надежности (доверительная вероятность) по
42
умолчанию предполагается равным 95%. Если исследователя это значение
не устраивает, то рядом со словами Уровень надежности нужно поставить
«галочку» и указать требуемое значение. Поставив «галочку» рядом со
словом константа-ноль, исследователь получит а = 0 по умолчанию. Если
нужны значения остатков et и их график, то нужно поставить «галочки»
рядом со словами Остатки и График остатков. ОК. Появляется итоговое
окно.
Если число в графе Значимость F превышает 1 – Уровень
надежности, то принимается гипотеза R2 = 0. Иначе принимается гипотеза
R2 ≠ 0.
Р-значение – это значения уровней значимости, соответствующие
вычисленным t-статистикам.
Нижние 95% и Верхние 95% – это нижние и верхние границы 95%
доверительных интервалов для коэффициентов теоретического уравнения
линейной регрессии. Если исследователь согласился с принятым по
умолчанию значением доверительной вероятности 95%, то последние два
столбца будут дублировать два предыдущих столбца. Если исследователь
вводил свое значение доверительной вероятности р, то последние два
столбца содержат значения соответственно нижней и верхней границы
р-процентных доверительных интервалов.
Порядок выполнения работы.
1. Проверить факторы на мультиколлинеарность. Поскольку их
всего два, то следует проверить их на коллинеарность по значению
парного коэффициента корреляции. Поэтому необходимо:
1) Найти коэффициент корреляции между факторами r12.
2) Проверить статистическую значимость полученного
коэффициента корреляции при α=0,05. Для этого:
найти наблюдаемое значение t-статистики:
2
12
12 1
2
r
t r n набл
;
найти критическое значение t-статистики: tкр=tα;n–2 где
к=n–2 – число степеней свободы;
сделать вывод о значимости коэффициента корреляции
r12 и, значит о наличие коллинеарности: если tнабл tкр , то
коэффициент корреляции статистически значим и
факторы коллинеарны; в противном случае – факторы
неколлинеарны.
2. Найти линейное уравнение регрессии у=b1x1+b2x2+a и
проверить его качество. Для этого необходимо:
1) Найти коэффициенты уравнения b1, b2, a.
43
2) Для проверки статистической значимости коэффициентов
уравнения b1, b2, a при α=0,05 необходимо:
найти наблюдаемые значения t-статистик:
.
;
a
a набл
b
j
j набл
m
t a
m
b
t
j
найти критическое значение t-статистики: tкр=tα;n–m–1 где
k=n–m–1 – число степеней свободы, m – число факторов;
сделать вывод о значимости каждого из коэффициентов
b1, b2, a: если tнабл tкр , то коэффициент регрессии
статистически значим; в противном случае –
статистически незначим.
3) Для проверки общего качества уравнения регрессии проверить
значимость коэффициента детерминации. Для этого:
найти коэффициент детерминации;
найти наблюдаемое значение F-статистики
( 2)
1 2
2
п
R
F R факт ;
найти критическое значение F-статистики. Число
степеней свободы критерия Фишера-Снедекора: k1=1;
k2= n−m–1 и критическое значение этого критерия
Fкр= ; k1 ; k2 F .
сделать вывод о значимости уравнения регрессии: если
Fфакт > Fкр, то уравнение регрессии статистически
значимо и надежно, если Fфакт < Fкр признается
статистическая незначимость, ненадежность уравнения
регрессии.
3. Найти средние коэффициенты эластичности по переменным х1
и х2 по формуле:
. j
j
yx j
x
Э b
y
Пример выполнения лабораторной работы
Дана зависимость объема выпускаемой продукции (млн. ден. ед.) от
соответствующих затратах на рабочую силу х1 (млн. ден. ед.) и средств
производства х2 (млн. ден. ед.) для десяти регионов страны. Необходимо в
соответствии со сформулированным выше заданием построить и
проанализировать линейную двухфакторную модель.
44
№ п/п х1 х2 у
1 1,83 1,41 12,2
2 1,41 1,43 7,89
3 1,31 1,16 8,36
4 0,83 1,54 6,9
5 0,86 1,01 5,74
6 1,18 1,84 9,77
7 1,75 1,78 10,99
8 1,13 1,16 6,95
9 0,95 1,99 9,97
10 1,4 1,68 9,16
I. Проверка факторов на мультиколлинеарность
1. Подготовим исходные данные, как показано на рис 1.5.
2. Вводим следующие формулы:
Ячейка Формула Примечание
В15 =КОРРЕЛ(В3:В12;С3:С12) Коэффициент корреляции
между факторами
В16 =В15*((А12–2)/(1–В15*В15))^0,5 Наблюдаемое значение t-
статистики коэффициента
корреляции
В17 =СТЬЮДРАСПОБР(0,05;А12-2) Критическое значение t-
статистики коэффициента
корреляции
В18 =ЕСЛИ(ABS(B16)>B17;"факторы
мультиколлинеарны";"факторы
не мультиколлинерны")
Выводы о проверке на
мультиколлинеарность
Рис. 1.5. Проверка мультиколлинеарности
45
II. Уравнение линейной регрессии
1. Найдем массив данных, описывающих уравнение линейной
многофакторной регрессии (см. рис. 2.5). Для этого:
выделим мышью весь диапазон С22:E26;
вызовем функцию ЛИНЕЙН, в окне которой введем следующие
значения параметров: Изв_знач_у – D3:D12, Изв_знач_х – В3:С12,
Конст – 1, Статистика – 1;
после ввода параметров вместо кнопки ОК нажмем одновременно
три клавиши: <Shift>+<Ctrl>+<Enter>.
Рис. 2.5. Анализ уравнения регрессии
Из полученных данных следует, что уравнение линейной регрессии
имеет вид:
у= 4,042х1+3,177х2–1,085.
(0,885) (0,939) (1,71)
В скобках указаны стандартные ошибки соответствующих
коэффициентов.
2. Вводим следующие формулы:
Ячейка Формула Примечание
С28 =ABS(C22)/C23 t-статистика
коэффициентов.
Копируем до E28.
С30 =СТЬЮДРАСПОБР(0,05;A12-2-1) Критическое значение
t-статистики
коэффициента
корреляции.
С31 =FРАСПОБР(0,05;A12-1-D25;D25) Критическое значение
F-статистики.
С29 =ЕСЛИ(C28>$C$30;"Значим";"Незначим") Выводы о значимости
коэффициентов.
Копируем до Е29.
С32 =ЕСЛИ(C25>C31;"Значимо";"Незначимо") Вывод о значимости
уравнения регрессии
46
III. Средние коэффициенты эластичности
Вводим следующие формулы:
Ячейка Формула Примечание
А37 =СРЗНАЧ(B3:B12) Средние значения фактора и
результата. Копируем до С37.
А40 =D22*A37/C37 Средний коэффициент
эластичности для х1
В40 =C22*B37/C37 Средний коэффициент
эластичности для х2
Результаты вычислений представлены на рис. 5.3.
Рис. 3.5. Расчет средних коэффициентов эластичности
Следовательно при увеличении затрат на рабочую силу на 1% объем
выпускаемой продукции увеличится в среднем на 0,581%, при увеличении
затрат на средства производства на 1% объем выпускаемой продукции
увеличится в среднем на 0,542%.
IV. Регрессия и Excel.
1. В главном меню выберите Сервис – Анализ данных – Регрессии.
Щелкните по кнопке ОК.
2. Заполните диалоговое окно ввода данных и параметров вывода
(рис. 4.5):
Рис. 4.5. Диалоговое окно ввода параметров инструмента Регрессия
47
3. Результаты регрессионного анализа представлены на рис. 5.5.
Рис. 5.5. Результат применения инструмента Регрессия
Индивидуальное задание к лабораторной работе
Исследуется зависимость между стоимостью грузовой
автомобильной перевозки, весом груза и расстоянием.
№
компа
нии
Стоимость грузовой автомобильной перевозки y (тыс. руб.)
№ варианта
1 2 3 4 5 6 7 8 9 10
1 51,0 56,1 20,4 84,2 38,8 127,5 71,4 182,2 83,8 89,8
2 16,0 17,6 6,4 26,4 12,2 40,0 22,4 57,2 26,3 28,2
3 74,0 81,4 29,6 122,1 56,2 185,0 103,6 264,3 121,6 130,2
4 7,5 8,3 3,0 12,4 5,7 18,8 10,5 26,8 12,3 13,2
5 33,0 36,3 13,2 54,5 25,1 82,5 46,2 117,9 54,2 58,1
6 26,0 28,6 10,4 42,9 19,8 65,0 36,4 92,9 42,7 45,8
7 11,5 12,7 4,6 19,0 8,7 28,8 16,1 41,1 18,9 20,2
8 52,0 57,2 20,8 85,8 39,5 130,0 72,8 185,7 85,4 91,5
9 15,8 17,4 6,3 26,1 12,0 39,5 22,1 56,4 26,0 27,8
10 8,0 8,8 3,2 13,2 6,1 20,0 11,2 28,6 13,1 14,1
11 26,0 28,6 10,4 42,9 19,8 65,0 36,4 92,9 42,7 45,8
12 6,0 6,6 2,4 9,9 4,6 15,0 8,4 21,4 9,9 10,6
13 5,8 6,4 2,3 9,6 4,4 14,5 8,1 20,7 9,5 10,2
14 13,8 15,2 5,5 22,8 10,5 34,5 19,3 49,3 22,7 24,3
15 6,2 6,8 2,5 10,2 4,7 15,5 8,7 22,1 10,2 10,9
16 7,9 8,7 3,2 13,0 6,0 19,8 11,1 28,2 13,0 13,9
17 5,4 5,9 2,2 8,9 4,1 13,5 7,6 19,3 8,9 9,5
48
18 56,0 61,6 22,4 92,4 42,6 140,0 78,4 200,0 92,0 98,6
19 25,5 28,1 10,2 42,1 19,4 63,8 35,7 91,1 41,9 44,9
20 7,1 7,8 2,8 11,7 5,4 17,8 9,9 25,4 11,7 12,5
Вес груза x1 (тонн)
№ варианта
1 2 3 4 5 6 7 8 9 10
1 35,0 45,5 14,0 54,6 26,6 91,0 54,6 125,0 61,3 72,8
2 16,0 20,8 6,4 25,0 12,2 41,6 25,0 57,2 28,0 33,3
3 18,0 23,4 7,2 28,1 13,7 46,8 28,1 64,3 31,5 37,4
4 2,0 2,6 0,8 3,1 1,5 5,2 3,1 7,1 3,5 4,2
5 14,0 18,2 5,6 21,8 10,6 36,4 21,8 50,0 24,5 29,1
6 33,0 42,9 13,2 51,5 25,1 85,8 51,5 117,9 57,8 68,6
7 20,0 26,0 8,0 31,2 15,2 52,0 31,2 71,4 35,0 41,6
8 25,0 32,5 10,0 39,0 19,0 65,0 39,0 89,3 43,8 52,0
9 13,0 16,9 5,2 20,3 9,9 33,8 20,3 46,4 22,8 27,0
10 2,0 2,6 0,8 3,1 1,5 5,2 3,1 7,1 3,5 4,2
11 21,0 27,3 8,4 32,8 16,0 54,6 32,8 75,0 36,8 43,7
12 11,0 14,3 4,4 17,2 8,4 28,6 17,2 39,3 19,3 22,9
13 3,0 3,9 1,2 4,7 2,3 7,8 4,7 10,7 5,3 6,2
14 3,5 4,6 1,4 5,5 2,7 9,1 5,5 12,5 6,1 7,3
15 2,8 3,6 1,1 4,4 2,1 7,3 4,4 10,0 4,9 5,8
16 17,0 22,1 6,8 26,5 12,9 44,2 26,5 60,7 29,8 35,4
17 3,4 4,4 1,4 5,3 2,6 8,8 5,3 12,1 6,0 7,1
18 24,0 31,2 9,6 37,4 18,2 62,4 37,4 85,7 42,0 49,9
19 9,0 11,7 3,6 14,0 6,8 23,4 14,0 32,1 15,8 18,7
20 4,5 5,9 1,8 7,0 3,4 11,7 7,0 16,1 7,9 9,4
Расстояние x2 (тыс. км)
№ варианта
1 2 3 4 5 6 7 8 9 10
1 2,0 2,4 0,6 3,8 1,1 5,4 2,2 5,1 2,5 3,8
2 1,1 1,3 0,3 2,1 0,6 3,0 1,2 2,8 1,4 2,1
3 2,6 3,1 0,8 4,9 1,5 6,9 2,8 6,5 3,1 4,9
4 1,7 2,0 0,5 3,3 1,0 4,6 1,8 4,4 2,1 3,3
5 2,4 2,9 0,7 4,6 1,4 6,5 2,6 6,2 3,0 4,6
6 1,6 1,9 0,5 3,0 0,9 4,2 1,7 4,0 1,9 3,0
7 0,6 0,7 0,2 1,2 0,3 1,6 0,6 1,5 0,7 1,2
8 2,3 2,8 0,7 4,4 1,3 6,2 2,5 5,9 2,8 4,4
9 1,4 1,7 0,4 2,7 0,8 3,8 1,5 3,6 1,7 2,7
10 2,1 2,5 0,6 4,0 1,2 5,7 2,3 5,4 2,6 4,0
11 1,3 1,6 0,4 2,5 0,7 3,5 1,4 3,3 1,6 2,5
12 0,4 0,4 0,1 0,7 0,2 0,9 0,4 0,9 0,4 0,7
13 1,7 2,0 0,5 3,2 0,9 4,5 1,8 4,2 2,0 3,2
49
14 2,9 3,5 0,9 5,6 1,7 7,8 3,1 7,4 3,6 5,6
15 0,8 0,9 0,2 1,4 0,4 2,0 0,8 1,9 0,9 1,4
16 0,6 0,7 0,2 1,2 0,3 1,6 0,6 1,5 0,7 1,2
17 0,9 1,1 0,3 1,7 0,5 2,4 1,0 2,3 1,1 1,7
18 2,5 3,0 0,8 4,8 1,4 6,8 2,7 6,4 3,1 4,8
19 2,2 2,6 0,7 4,2 1,3 5,9 2,4 5,6 2,7 4,2
20 1,0 1,1 0,3 1,8 0,5 2,6 1,0 2,4 1,2 1,8
Отчет по лабораторной работе
1. Сформулируйте требования, предъявляемые к факторам для включения
их в модель множественной регрессии.
2. Мультиколлинеарны ли факторы для вашего варианта? Почему?
3. Запишите уравнение линейной множественной регрессии для вашего
варианта и интерпретируйте оценки параметров регрессии.
4. Как оценивается значимость параметров уравнения регрессии?
5. Являются ли параметры уравнения регрессии для вашего варианта
значимыми и почему?
6. Запишите доверительные интервалы для параметров уравнения
регрессии для вашего варианта.
7. Каким образом осуществляется проверка значимости уравнения в
целом.
8. Значимо ли уравнение регрессии для вашего варианта и почему?
9. Найти частные уравнения регрессии.
10. Найти средние коэффициенты эластичности. Сделать выводы.
11. Сравните полученные результаты с результатами, полученными с
помощью инструмента анализа данных Регрессия.
_
50
Лабораторная работа №6
Построение линейного, логарифмического, полиномиального,
степенного и экспоненциального трендов
Цель: научиться проводить расчет параметров линейного,
логарифмического, полиномиального, степенного и экспоненциального
трендов, строить графики ряда динамики и трендов. Уметь выбирать
наилучший вид трендов на основании графического изображения и
значения коэффициента детерминации.
Порядок выполнения работы.
1. Провести расчет параметров линейного, логарифмического,
полиномиального, степенного и экспоненциального трендов;
2. Построить графики ряда динамики и трендов;
3. Выбрать наилучший вид трендов на основании графического
изображения и значения коэффициента детерминации.
Пример выполнения лабораторной работы
Динамика выпуска продукции некоторой страны характеризуется
данными (усл. ед.), представленными в таблице:
Год Выпуск
продукции
Год Выпуск
продукции
Год Выпуск
продукции
1961 1054 1973 3837 1985 13617
1962 1104 1974 5490 1986 16356
1963 1149 1975 5502 1987 20037
1964 1291 1976 6342 1988 21748
1965 1427 1977 7665 1989 23298
1966 1505 1978 8570 1990 26570
1967 1513 1979 11172 1991 23080
1968 1635 1980 14150 1992 23981
1969 1987 1981 14004 1993 23446
1970 2306 1982 13088 1994 29658
1971 2367 1983 12518 1995 39573
1972 2913 1984 13471 1996 38435
В диапазоне А4:С39 (рис. 1.6) введем исходные данные.
Для определения параметров линейного тренда по методу
наименьших квадратов воспользуемся статистической функцией
ЛИНЕЙН. В качестве зависимой переменной в данном примере выступает
время (t = 1, 2,…, n). Порядок вычисления был рассмотрен в лабораторной
работе №1.
51
Рис. 1.6 Исходные данные
52
Запишем уравнения линейного тренда, используя данные рис.1.6:
уt = –5969,52+977,12t.
Выделим диапазон А4:А39 и, нажав Ctrl, выделим диапазон С4:С39,
выбрав в Мастере диаграмм тип – График, построим график зависимости
выпуска продукции от времени (см. рис. 2.6):
Рис.2.6. Динамика выпуска продукции.
В ППП MS Excel линия тренда может быть добавлена в диаграмму с
областями гистограммы или в график. Для этого:
1. выделите область построения диаграммы; в главном меню
выберите Диаграмма/ Добавить линию тренда;
2. в появившемся диалоговом окне (рис.3.6.) выберите вид линии
тренда и задайте соответствующие параметры. Для
полиномиального тренда необходимо задать степень
аппроксимирующего полинома, для скользящего среднего –
количество точек усреднения.
Рис. 3.6. Диалоговое окно типов линий тренда
0
5000
10000
15000
20000
25000
30000
35000
40000
45000
1961
1964
1967
1970
1973
1976
1979
1982
1985
1988
1991
1994
Год, х
Выпуск продукции, у
53
В качестве дополнительной информации на диаграмме можно
отобразить уравнение регрессии и значение среднеквадратического
отклонения, установив соответствующие значки на закладке Параметры
(рис.4.6.). Щелкните по кнопке ОК.
Рис. 4.6. Диалоговое окно параметров линии тренда
На рис.5.6.– 9.6. представлены различные виды трендов, описывающие
исходные данные задачи.
Рис. 5.6. Линейный тренд
y = 977,12x - 5969,5
R2 = 0,8841
-10000
0
10000
20000
30000
40000
50000
19 61
1964
19 67
1970
19 73
19 76
1979
19 82
19 85
19 88
19 91
1994
Год, х
Выпуск продукции, у Линейный тренд
54
Рис.6.6. Логарифмический тренд
Рис.7.6. Полиномиальный тренд
Рис.8.6. Степенной тренд
y = 9706,7Ln(x) - 13702
R2 = 0,5886
-20000
-10000
0
10000
20000
30000
40000
50000
1961
1964
1967
1970
1973
1976
1979
1982
1985
1988
1991
1994
Год, х
Выпуск продукции, у Логарифмический тренд
y = 0,0003x6 - 0,0191x5 + 0,4309x4 - 2,206x3 +
9,3848x2 - 19,739x + 1159,6
R2 = 0,9728
0
10000
20000
30000
40000
50000
19 61
1964
1967
19 70
1973
19 76
1979
19 82
1985
19 88
1991
19 94
Год, х
Выпуск продукции, у
Полиномиальный тренд 6-ой степени
y = 243,5x1,2621
R2 = 0,847
0
5000
10000
15000
20000
25000
30000
35000
40000
45000
1961
1964
1967
1970
1973
1976
1979
1982
1985
1988
1991
1994
Год, х
Выпуск продукции, у Степенной тренд
55
Рис.9.6. Экспоненциальный тренд
Сравним значения коэффициентов детерминации по разным
уравнениям трендов:
Полиномиальный 6-й степени 0,9738;
Экспоненциальный 0,9647;
Линейный 0,8841;
Степенной 0,847;
Логарифмический 0,5886.
Исходные данные лучше всего описывает полином 6-й степени.
Следовательно, в рассматриваемом примере для расчета прогнозных
значений следует использовать полиномиальное уравнение.
Индивидуальное задание к лабораторной работе
№ Год
2001 2002 2003 2004 2005 2006 2007 2008 2009 2010
1 25 27 30 29 30 35 33 40 40 42
2 29,4 23,5 26,2 48,5 73,4 56,6 77 78,4 81,2 82
3 183,5 153,5 140,7 107,1 87,5 68,3 83,1 84,2 79,6 80
4 105,3 94,9 92 83,9 72,7 56,9 49,1 46,5 39,7 35,5
5 130 150 296 542 254 274 272 262 369 334
6 41 42 49 64 53 44 52 51 71 92
7 252 217 210 229 302 320 270 287 291 237
8 97 89 77 81 87 94 90 90 93 87
9 18,5 15,1 30,8 34,4 25,4 35,1 36,9 44,7 57,3 64,6
10 143 130,3 149,9 296,6 541,5 363,2 254,1 272,4 368,4 334,3
Отчет по лабораторной работе
1. Записать уравнения линейного, логарифмического,
полиномиального, степенного и экспоненциального трендов.
2. Выбрать наилучший вид трендов на основании значения
коэффициента детерминации.
y = 901,67e0,1106x
R2 = 0,9647
0
10000
20000
30000
40000
50000
60000
1961
1964
1967
1970
1973
1976
1979
1982
1985
1988
1991
1994
Год, х
Выпуск продукции, у Экспоненциальный тренд
_
56
Лабораторная работа №7
Автокорреляция уровней временного ряда и выявление его структуры
Цель: научиться находить коэффициенты автокорреляции.
Краткие теоретические сведения.
Модели, построенные по данным, характеризующим один объект за
ряд последовательных моментов (периодов), называются моделями
временных рядов.
Временной ряд – это совокупность значений какого-либо показателя
за несколько последовательных моментов или периодов.
Каждый уровень временного ряда формируется из трендовой (Т),
циклической (сезонной) (S) и случайной (Е) компонент.
Автокорреляция уровней ряда – это корреляционная зависимость
между последовательными уровнями временного ряда:
,
( ) ( )
( )( )
2
2
1 2
2
2
1
2
1 1 2
1
n
t
t
n
t
t
n
t
t t
y y y y
y y y y
r
где
1
;
1
2
1
2
2
1 n
y
y
n
y
y
n
t
t
n
t
t
коэффициент автокорреляции уровней ряда
первого порядка;
,
( ) ( )
( )( )
3
2
2 4
3
2
3
3
3 2 4
2
n
t
t
n
t
t
n
t
t t
y y y y
y y y y
r
где
2
;
2
3
2
4
3
3 n
y
y
n
y
y
n
t
t
n
t
t
коэффициент автокорреляции уровней ряда
второго порядка.
Формулы для расчета коэффициентов автокорреляции старших
порядков легко получить из формулы линейного коэффициента
корреляции.
Последовательность коэффициентов автокорреляции уровней
первого, второго и т.д. порядков называют автокорреляционной функцией
временного ряда, а график зависимости ее значений от величины лага
(число периодов, по которым рассчитывается коэффициент
автокорреляции) – коррелограммой.
57
Порядок выполнения работы.
Для заданного временного ряда:
1. Найти коэффициенты автокорреляции до пятого порядка.
2. Построить коррелограмму.
Пример выполнения лабораторной работы
Представлены поквартальные данные о валовом объеме продаж
(млн.шт.) за последние четыре года:
Квартал Год
1 2 3 4
I 6 7,2 8 9
II 4,4 4,8 5,6 6,6
III 5 6 6,4 7
IV 9 10 11 10,8
1. В диапазоне В6:Е9 (рис. 1.7) введем исходные данные.
Рис. 1.7. Исходные данные
Для анализа временного ряда перепишем поквартальные данные в
один столбец. Для этого:
в столбце G запишем номера кварталов;
в столбце Н запишем числа 1,2,…,16;
чтобы перенести данные для временного ряда, в столбцы
запишем формулы согласно таблице:
58
Ячейка Формула Примечание
I5 =B6 Копируем в диапазон I6:I8
I9 =C6 Копируем в диапазон I10:I12
I13 =D6 Копируем в диапазон I14:I16
I17 =E6 Копируем в диапазон I18:I20
2. В столбцах J, K, L, M, N подготовим сдвинутые соответственно 1,
2, 3, 4, 5 уровней значений временного ряда. Для этого запишем в каждую
из ячеек: J6, K7, L8, M9, N10 – одну и ту же формулу:
=I5,
которую затем скопируем вниз до строки 20.
Найдем коэффициенты автокорреляции с помощью следующих
формул:
Ячейка Формула Коэффициент автокорреляции
В13 =КОРРЕЛ(I6:I20;J6:J20) 1-го порядка
В14 =КОРРЕЛ(I7:I20;K7:K20) 2-го порядка
В15 =КОРРЕЛ(I8:I20;L8:L20) 3-го порядка
В16 =КОРРЕЛ(I9:I20;M9:M20) 4-го порядка
В17 =КОРРЕЛ(I10:I20;N10:N20) 5-го порядка
3. Выделим диапазон А13:В17 и, выбрав в Мастере диаграмм тип –
Гистограмма, построим коррелограмму (рис. 2.7).
Рис. 2.7. Коррелограмма
Таким образом, коэффициенты автокорреляции временного ряда:
Порядок 1 2 3 4 5
Коэффициент
автокорреляции
0,1652 – 0,567 0,1136 0,983 0,1187
Наибольшее значение имеет коэффициент автокорреляции 4-го
порядка. Следовательно, временной ряд имеет тенденцию и сезонную
компоненту с периодом, равным четырем кварталам.
59
Индивидуальное задание к лабораторной работе
Вариант 1
Квартал Год
1 2 3 4
I 6,0 8,5 9,5 5,7
II 5,8 5,3 8,6 6,2
III 8,5 5,3 5,9 6,8
IV 9,9 7,9 9,7 7,0
Вариант 2
Квартал Год
1 2 3 4
I 6,6 5,7 7,5 9,4
II 9,8 8,6 8,3 8,2
III 7,8 5,2 5,1 6,7
IV 6,7 6,3 5,7 8,7
Вариант 3
Квартал Год
1 2 3 4
I 8,7 8,3 8,6 5,3
II 6,2 9,1 7,3 6,6
III 7,2 7,6 7,8 8,4
IV 7,0 9,4 8,6 9,6
Вариант 4
Квартал Год
1 2 3 4
I 5,5 8,2 9,1 8,5
II 5,7 8,0 9,7 7,5
III 5,5 6,6 5,9 8,7
IV 6,6 6,2 5,6 6,1
Вариант 5
Квартал Год
1 2 3 4
I 9,3 9,8 8,7 6,6
II 9,9 9,0 8,0 7,1
III 7,9 8,8 9,9 9,9
IV 7,5 6,1 5,7 8,0
Вариант 6
Квартал Год
1 2 3 4
I 8,3 5,9 9,9 6,7
II 7,5 7,5 6,3 7,0
III 8,8 5,4 8,5 6,6
IV 5,1 6,2 8,7 6,5
Вариант 7
Квартал Год
1 2 3 4
I 6,3 7,0 5,5 9,5
II 8,2 9,8 5,3 8,0
III 8,1 6,8 8,0 8,3
IV 6,0 5,2 5,4 8,5
Вариант 8
Квартал Год
1 2 3 4
I 9,1 8,9 5,7 9,6
II 5,2 8,4 9,2 9,1
III 7,2 8,0 5,6 7,5
IV 8,1 7,5 9,5 5,6
Вариант 9
Квартал Год
1 2 3 4
I 8,9 5,6 8,8 7,6
II 6,9 6,6 9,2 8,3
III 5,4 6,9 8,1 6,6
IV 5,0 8,0 8,0 9,8
Вариант 10
Квартал Год
1 2 3 4
I 9,9 9,6 5,0 9,7
II 7,7 7,6 5,1 8,0
III 9,8 7,7 9,2 6,2
IV 7,9 8,7 5,5 9,1
Отчет по лабораторной работе
1. Что такое автокорреляция уровней временного ряда и как ее
можно оценить количественно?
2. Записать значения коэффициентов автокорреляции до пятого
порядка. Сделать вывод.
