Эконометрика. Лабораторный практикум

Магазин	X	Y	Магазин	X	Y	Магазин	X	Y
1	26	77	11	87	16	21	18	67
2	57	34	12	57	55	22	25	57
3	36	59	13	64	32	23	23	68
4	87	25	14	98	34	24	78	31
5	44	56	15	12	78	25	25	68
6	35	72	16	45	78	26	44	76
7	19	68	17	5	89	27	38	57
8	26	67	18	48	35	28	52	38
9	48	58	19	72	42	29	25	65
10	33	79	20	30	78	30	37	73

Здравый смысл подсказывает, что издержки торговых предприятий (складов, магазинов) зависят от характера товаров, находящихся в обороте. Чем выше доля продовольственных товаров, тем издержки выше (эти товары хуже хранятся, необходимо холодильное оборудование и т.д.). Поэтому можно предположить, что с ростом доли промтоваров X , т.е. с уменьшением доли продовольственных товаров, удельные издержки Y будут в среднем снижаться.

При выполнении задания понадобится вычисление следующих величин.

Выборочные средние:

		1	n			1	n			1	n
x =			∑xi ,	y =			∑ yi , xy =				∑xi yi .
		n i=1				n i=1				n i=1

Выборочные дисперсии:

1

n

2

2

1

n

2

2

σ x2

=

∑xi2

− x

= x 2 − x

,

σ y2

=

∑yi2

− y

= y 2 − y

.

n i=1

n i=1

Выборочный коэффициент корреляции

r	=		xy	−		x			y		.
			σ		σ
xy				x			y

Величины σ x ,σ y называются выборочными среднеквадратическими отклонениями признаков.

Смысл настоящей работы состоит в том, чтобы провести корреляционный анализ как с помощью приведённых формул, так и с помощью функций EXCEL, убеждаясь в тождественности результатов и связывая характер исходных данных со значением коэффициента корреляции.

11

Инструкция по выполнению задания

1. Создать в рабочем листе EXCEL следующую таблицу:

	A	B	C	D		E
1	x	y	x2	y2		xy
2	26	77	676	5929		2002
3	…	…	…	…		…
4

При этом следует использовать формулы с операциями возведения в квадрат (^2) и умножения (*). Формулы достаточно ввести лишь в ячейки C2:E2 и применить автозаполнение. В последней строке таблицы поместить средние значения.

2. Построить диаграмму рассеивания по исходным данным (точечная диаграмма), подписать оси, дать название диаграмме.

3. Рассчитать дисперсии признаков и коэффициент их корреляции двумя способами: с помощью обычных формул и с помощью функций ДИСПР и КОРРЕЛ (статистические). Для нахождения среднеквадратических отклоне-

12

ний можно использовать функцию КОРЕНЬ (из дисперсии) или функцию СТАНДОТКЛОНП (статистические).

Дополнительное задание

Задание 2.2. Ниже приводится выборка данных о рынке жилья в г. Иркутске в декабре 2000 года. Провести корреляционный анализ данных. (Можно только с помощью функций).

Квартира	Площадь,	Цена, тыс.	Квартира	Площадь, Цена, тыс.
	м2	руб.		м2	руб.
1	51	465	13	60	480
2	56	490	14	67	460
3	60	350	15	45	260
4	52	480	16	38	270
5	60	370	17	50	240
6	36	320	18	44	270
7	44	270	19	80	600
8	30	215	20	20	160
9	42	275	21	25	170
10	44	275	22	30	225
11	49	430	23	30	210
12	40	200	24	34	240

Контрольные вопросы

1.Почему в задании 2.1 коэффициент корреляции оказался отрицательным, а в задании 2.2 – положительным? Дайте объяснение как с математической, так и с экономической точек зрения.

2.В обоих заданиях проверьте гипотезу о значимости коэффициента корреляции (см. п. 3.2 пособия [14]). Для нахождения критической точки распределения Стьюдента можно использовать либо соответствующую таблицу (приложение 1 в [14]), либо функцию СТЬЮДРАСПОБР (Статистические) в EXCEL.

Работа 3

Парный регрессионный анализ

Время на выполнение и защиту – 2 часа

Цель работы:

1)проведение парного регрессионного анализа данных с помощью известных формул для оценок коэффициентов регрессии, а также с помощью функций и диаграмм EXCEL;

2)использование полученных регрессионных зависимостей для линейного предсказания.

13

Что такое регрессия?

Корреляция и регрессия – смежные научные понятия, употребляемые, как правило, совместно. Термин «регрессия» уже появлялся в работе 1 («функция регрессии»). Оба понятия связаны с именем выдающегося английского антрополога Фрэнсиса Гальтона. Изучая наследственность, Гальтон собрал статистический материал, который доказывал (как ему казалось), что в среднем рост сыновей уменьшается по сравнению с ростом отцов. Говоря точнее, дети высоких родителей тоже выше своих сверстников, но всё же они ближе к среднему росту, чем родители. Такое явление Гальтон назвал regression to mediocrity (возврат к среднему состоянию) и даже вывел соответствующее уравнение. Хотя наблюдение Гальтона не нашло подтверждения в других исследованиях, разработанный им метод стал одной из основ обработки статистических данных.

Регрессией в теории вероятностей и математической статистике называется зависимость среднего значения величины Y от значения другой величины X (или нескольких величин).

Метод наименьших квадратов

Пусть изучается взаимозависимость двух количественных признаков (X ,Y ). В результате n опытов или наблюдений получены пары чисел: (xi, yi),

где i = 1, 2, ..., n. На основе предварительного анализа этих данных и с учётом самой природы признаков мы можем сделать предположение о некоторой ли-

нии связи y x = f (x, β) , где под y x подразумевается среднее значение признака Y, соответствующее значению X = x, а под β – совокупность варьируемых па-

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

y x = ax + b .

Для получения оценок коэффициентов a и b применяется метод наименьших квадратов (МНК). Он состоит в минимизации суммы квадратов отклонений наблюдаемых значений количественного признака от теоретических значений того же признака, соответствующих некоторой гипотетической формуле. Например, для случая, когда в качестве линии связи выбирается прямая, минимизируется функция двух переменных:

n

S(a, b) = ∑( yi − axi −b)2 → min .

i=1

Задание для лабораторной работы

Задание 3.1. Построить уравнение регрессии по данным, приведённым в задании 2.1 (задача про магазины). Для этого воспользоваться файлом, из-

14

готовленным при выполнении работы 2. Решить задачу несколькими способами и убедиться в их тождественности.

При оценивании регрессионной зависимости уравнением y x = a* x + b* используются формулы, полученные по методу наименьших квадратов:

−

x

2

−

a* =

xy

x

y

, b* =

y

x

xy

.

x 2

− (

)2

x 2

− (

)2

x

x

При этом нужно помнить, что b* = y − a* x .

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

Инструкция по выполнению задания

1. Оценить регрессию уравнением y x = a* x + b* непосредственно по формулам, выведенным методом наименьших квадратов. Кроме этого, убедиться, что оценку b* можно найти по формуле b* = −a* x + y .

2. Найти оценку a* с помощью функции НАКЛОН (статистические), а

оценку b* с помощью функции ПРЕДСКАЗ (статистические), задав нулевое значение аргумента. С названными функциями разобраться самостоятельно.

		100		y = -0,6962x + 87,764
	)
	тыс.руб.оборота	80
Y , руб.		60
		40
	1	20
	(на						X , %
		0
		0	20	40	60	80	100

3. Вывести на диаграмме линию тренда и её уравнение. Для этого щёлкнуть правой кнопкой мыши на любой из точек графика (точечной диаграммы) и выбрать Добавить линию тренда. Для показа уравнения переключиться с закладки Тип на закладку Параметры.

15

4. Сопоставить результаты оценивания коэффициентов регрессии всеми описанными способами.

Дополнительное задание

Задание 3.2. Построить уравнение регрессии Y по X по данным, приведённым в задании 2.2 (задача про квартиры). Для этого воспользоваться файлом, изготовленным при выполнении работы 2. Решить задачу несколькими способами и убедиться в их тождественности.

Контрольные вопросы

1.Сформулируйте математический и экономический смысл коэффициента регрессии a в заданиях 3.1 и 3.2.

2.Объясните, почему оценку b* можно дать с помощью функции ПРЕДСКАЗ, задав нулевое значение аргумента.

3.Каков экономический смысл коэффициента b в задании 3.1? Почему невозможно найти экономическую интерпретацию коэффициенту b в задании 3.2?

4.Предскажите величину издержек Y , если доля промтоваров в обороте X будет равна 50%, 70%, 100% (задание 3.1) и стоимость квартиры Y , если площадь X будет равна 80 квадратным метрам (задание 3.2). Используйте для этого разные способы: полученное уравнение регрессии; функцию ПРЕДСКАЗ.

Работа 4

Качество регрессии

Время на выполнение и защиту – 2 часа

Цель работы:

изучение характеристик качества регрессии и способов их вычисления в

EXCEL.

Некоторые сведения о характеристиках качества регрессии

Формально мы можем для любого набора парных данных построить линейную модель регрессии и получить оценки коэффициентов регрессии. Вопрос лишь в том, можно ли будет эту модель достаточно уверенно использовать. Не окажется ли, что отклонения предсказания от реальных данных настолько велики, что это обесценивает полученные результаты? Это и есть вопрос о качестве регрессии.

Пусть используется модель регрессии

yi = axi + b +εi (i =1, ..., n),

16

где εi – ошибки регрессии, и оценки a* и b* получены. Тогда предсказание значения признака Y по значению X = xi делается с помощью выборочного уравнения регрессии

y x = a* xi +b* .

Пусть реально в i-ом наблюдении при X = xi было получено значение Y = yi ,

которое, как правило, не совпадает с предсказанием. Разность реального значения и предсказания

ei = yi − y x = yi − a* xi − b*

называется остатком регрессии.

Величину

	n
	∑( yi − y x )2
s =	i=1
	n − 2

называют стандартной ошибкой оценки Y. Стандартные отклонения оценок коэффициентов регрессии связаны с s следующими формулами:

s

a

=

s

,

s

b

= s ×

∑xi2

,

где u

i

= x

i

− x .

∑ui2

n∑ui2

При проверке гипотезы о значимости коэффициента регрессии a рассматрива-

ется величина ta = a* , называемая t - статистикой коэффициента a . Чем вы- sa

ше значение этой величины, тем больше шансов, что коэффициент регрессии значим. Более подробно этот вопрос изучается в работе 5.

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

S 2 = Se2 + Sr2 ,

где S 2

= ∑( yi

−

)2 – полная сумма квадратов,

y

Sr2

= ∑(

x

−

)2

–

сумма квадратов, объясняемая регрессией Y по X,

y

y

Se2

= ∑( yi

−

x )2

–

остаточная сумма квадратов.

y

Коэффициентом детерминации регрессионной модели называется вели-

чина

r 2 = Sr2 S 2 ,

17

которая достигает 1 при идеальной (функциональной) линейной зависимости и поэтому может рассматриваться как показатель качества регрессии.

Задание для лабораторной работы

Задание 4.1. По данным, приведённым в заданиях 2.1 и 3.1 (задача про магазины), найти: стандартную ошибку оценки Y и стандартные отклонения коэффициентов a и b регрессии Y по X; коэффициент детерминации модели.

В работе 3 была построена модель и получены оценки коэффициентов линейной регрессии.

Смысл настоящей работы состоит в том, чтобы рассчитать показатели качества регрессии как непосредственно, с помощью приведённых выше формул, так и с помощью специальной функции, имеющейся в EXCEL.

Инструкция по выполнению задания

1.Получить уравнение регрессии Y по X и коэффициент корреляции (эта часть задачи выполнялась в работах 2 и 3).

2.Создать таблицу вида

		A	B	C	D			E	F					G			H			I
1		xi	yi	xi2	xi yi			x =a* xi +b*	( yi −		)2	(		x −		)2	( yi −		x )2	ui2
							y						y		y			y
										y
2		26	77	676	2002	69,662			371,204			142,294					53,846			293,551
3		57	34	3249	1938	198,217			563,271			93,2067					198,217			192,284
4		…	…	…	…			…	…					…			…			…

При задании формул ни в коем случае не вводите в них конкретных чисел «с клавиатуры», а задавайте адреса соответствующих ячеек. Особое внимание обратите на корректность автозаполнения. Так, в ячейку E2 вы введёте формулу,

вычисляющую значение y x по ячейкам, в которых находятся оценки коэффициентов регрессии a* и b* и текущее значение xi . Перед тем, как производить

автозаполнение (вниз), следует сделать ссылки на ячейки, в которых находятся a* и b*, абсолютными (для этого используется знак $). Ссылка же на ячейку со значением xi должна остаться относительной. Относительные ссылки автома-

тически корректируются при копировании или автозаполнении ячеек, а абсолютные – нет.

Замечание о необходимости фиксирования некоторых ссылок при автозаполнении относится к столбцам E-I.

В последних строках таблицы поместите формулы для сумм и средних величин (где это необходимо).

18

3.По приведённым выше формулам найти стандартную ошибку оценки Y и стандартные отклонения коэффициентов регрессии.

4.Рассчитать суммы квадратов (полную, объясняемую регрессией и остаточную) и коэффициент детерминации и убедиться, что он равен квадрату коэффициента корреляции.

5.Построить диаграмму рассеивания с линией тренда, уравнением регрессии и коэффициентом детерминации (достоверность аппроксимации).

6.Изучить и применить функцию

ЛИНЕЙН (массив {yi }, i =1, n ; массив {x ji }, j =1, k, i =1, n ); A ; B ), которая вычисляет параметры линейной регрессии Y по объясняющим пере-

менным X1 , X 2 , ..., X k .

Первый аргумент – диапазон, содержащий значения признака Y ; второй аргумент – диапазон, содержащий значения объясняющих переменных; A – логическое значение, которое указывает на наличие (1) или отсутствие (0) свободного члена в уравнении; B – логическое значение, которое указывает, выводить ли дополнительную статистику по регрессионному анализу (1) или нет

(0).

Рассмотрим использование этой функции в случае изучения парной регрессии (одна объясняющая переменная X ). Если дополнительная регрессионная статистика требуется, выделим в электронной таблице диапазон ячеек размером 5 на 2 (5 строк, 2 столбца). Вызовем функцию ЛИНЕЙН. Введём аргументы и щёлкнем по OK. В левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмём на клавишу F2, а затем – на комбинацию клавиш <CTRL>+<SHIFT>+<ENTER>. Дополнительная регрессионная статистика будет выводиться в следующем порядке:

Оценка коэффициента регрессии ( a* )	Оценка свободного члена (b* )
Стандартное отклонение ( sa )	Стандартное отклонение ( sb )
Коэффициент детерминации ( r 2 )	Станд. ошибка оценки Y ( s )
F - статистика	Число степеней свободы ( n − 2 )
Сумма квадратов, объясняемая регрессией	Остаточная сумма квадратов
( Sr2 )	( Se2 )

Сопоставьте результаты, полученные с помощью функции ЛИНЕЙН (…), с результатами непосредственных расчётов регрессионной статистики.

19

Дополнительное задание

Задание 4.2. По данным, приведённым в заданиях 2.2 и 3.2 (задача про квартиры), найти: стандартную ошибку оценки Y и стандартные отклонения коэффициентов регрессии Y по X; коэффициент детерминации модели (с помощью функции ЛИНЕЙН (…)).

Контрольные вопросы

1.Чем отличаются остатки регрессии от ошибок регрессии?

2.Чему, на ваш взгляд, равен средний остаток регрессии? Проверьте своё предполо-

жение.

3.Функция ЛИНЕЙН выводит в числе прочих характеристик F-статистику. Эта величина связана с коэффициентом детерминации [14]. Вычислите F-статистику через коэффициент детерминации и сравните с результатом обращения к ЛИНЕЙН.

4.Проверьте выполнение равенства S 2 = Se2 + Sr2 .

Работа 5

Множественный регрессионный анализ

Время на выполнение и защиту – 2 часа

Цель работы:

1)проведение множественного линейного регрессионного анализа в EXCEL;

2)определение значимости факторов, включённых в модель;

3)выполнение линейного предсказания с помощью модели множественной регрессии.

Модель множественной регрессии

Занимаясь корреляционно-регрессионным анализом, всегда приходится иметь в виду, что зависимость некоторого количественного признака Y от ка- кой-либо переменной X – не единственная (и, может быть, не самая существенная) причина вариации Y. Как правило, существует, по крайней мере, две-три переменные, влияние которых на Y является сопоставимым по важности. В работах 1-4 мы решали задачу о зависимости цены квартиры от её площади. Но за рамками рассмотрения осталось влияние других обстоятельств: удалённость от центра города, этаж, количество комнат и т.д. Между тем, без всяких вычислений ясно, что роль этих факторов весьма существенна.

Такого рода проблемы приводят к необходимости построения модели множественной регрессии, когда вместо одной объясняющей переменной X используется несколько переменных X1, X2, ..., Xk. При этом, как и в случае парной регрессии, нужно остерегаться ошибок в определении функциональной

20