Y только от одной объясняющей переменной X
 Скачать 1.62 Mb.
|
|
. 1.12 Рис. 1.13 Дальнейшие усовершенствования графика, не имеющие прямого от- ношения к сути задачи, можно выполнить нажав кнопки «Далее» и «Гото- во». График вставится в текущий лист документа. После этого его можно редактировать: изменять размер, положение и т.п. Отметим, что масштаб диаграммы по осям OX и OY выбирается ав- томатически, и не всегда он оптимален для восприятия. Для большей наглядности желательно, чтобы график занимал все пространство диаграм- мы как по оси OX, так и по OY. Если необходимо изменить шкалу графика, щелкните два раза точно на нужную ось и в раскрывшемся окне установите нужные параметры (рис.1.14). Рис. 1.14 Рис. 1.15 В результате точечный график экспериментальных значений за- Полянский Ю.Н. Эконометрика. Экономическое моделирование и прогнозирование. 24 дачи будет выглядеть в виде, аналогичном приведенному на рис.1.15. Его общий вид позволяет сделать предварительный вывод о наличии линейной связи между случайными величинами X (цена на товар) и Y ( спрос на товар), т.к. точки на графике лежат близко к воображаемой убывающей прямой. Однако эмпирически трудно определить, насколько эта связь тесная. 2) Мерой тесноты линейной связи между двумя случайными ве- личинами является линейныйкоэффициентпарнойкорреляции r : y x s s y x - y x r = , где x и y – средние арифметические случайных величин X и Y; y x ⋅ – среднее арифметическое произведений СВ X и Y; 2 2 x x s x − = – выборочное среднее квадратичное отклонение СВ X; 2 2 y y s y − = – выборочное среднее квадратичное отклонение СВ Y; 2 x , 2 y – средние арифметические квадратов случайных величин X и Y; 2 x , 2 y – квадраты средних арифметических случайных величин X и Y; Анализ формулы показывает, что для расчетов необходимы сред- ние значения, для чего в исходной таблице удобно ввести вспомога- тельные столбцы «x 2 », «y 2 » и «xy»(D, E и F на рис.1.16). Расчет x 2 для 1-го наблюдения осуществляется вводом в ячейку D2 формулы «=С2*С2». Последующее протягивание (указателем мыши за черную точку в правом нижнем углу ячейки D2) до ячейки D11 поз- воляет аналогично рас- считать x 2 для осталь- ных наблюдений. Для расчета xy введем в E2 формулу «=C2*B2» и протянем ее до E11. Расчет средних значений будем прово- дить в строке 12 с по- мощью функции СРЗНАЧ (впрочем, это можно сделать и иначе, Рис. 1.16 Полянский Ю.Н. Эконометрика. Экономическое моделирование и прогнозирование. 25 например, вычислением по формуле среднего арифметического). Для этого, встав на ячейку B12 (там, где Y ср .), щелкнем на панели ин- струментов кнопку мастера функций . В окне мастера функций найдем функцию СРЗНАЧ (рис.1.17). В вер- сиях выше Microsoft Excel 2000 её можно вызвать и иначе [15]. Вызвав функцию СРЗНАЧ, зададим диапазон ячеек B2:B11 для расчета среднего значения Y ( рис.1.18). Аналогично рассчита- ем остальные средние значе- ния. Процесс можно ускорить протягиванием по строке 12 ячейки B12. Рис. 1.18 Для расчетов выборочных средних квадратичных отклонений x s и y s ниже расчетной таблицы в ячейках A15 и A16 напишем коммента- рии «сигма X=» и «сигма Y=». В ячейку B15 введем соответствующую формулу «=КОРЕНЬ(D12-C12*C12)», а в B16 - формулу «=КОРЕНЬ(E12–B12*B12)». Напомним, что ввод формулы может осуществляться как вписыванием формулы, так и вызовом мастера функций кнопкой . Для расчета коэффициента корреляции в ячейке C14 впишем коммен- тарий «r=», а в ячейке D14 - формулу «=(F12-C12*B12)/(B15*B16)». Обратите внимание на формат ячеек для отображения на экране полу- чившихся значений x s , y s и r . Для них достаточно 3-4-х знаков после за- пятой. Желательно выделить получившиеся значения цветом, фоном и т.п. Результаты расчетов показаны на рис.1.19. Рис. 1.17 Полянский Ю.Н. Эконометрика. Экономическое моделирование и прогнозирование. 26 Получен 928 , 0 − = r близкий по модулю к 1, что говорит о наличии очень тесной линейной связи между X и Y (т.е. их коррелированности). Знак « минус» означает, что имеет место обратная линейная корреляция, т.е. с ро- стом X уменьшается Y, что соответствует экономическому смыслу: с ростом цены на товар спрос на него обычно падает. Рис. 1.19 Заметим, что в иных экономических ситуациях возможен иной эффект от увеличения цены на товар, например, нелинейное падение спроса или даже его рост. Но здесь обработаны конкретные статистические данные, для которых и получен соответствующий вывод. 3) По общему виду точечного графика экспериментальных значений ( рис.1.15) можно сделать предварительное предположение о том, что в дан- ном случае может подойти модель парной линейной регрессии. Оценки её коэффициентов вычисляются по формулам: 2 2 x x y x y x bˆ − ⋅ − ⋅ = , x bˆ y aˆ ⋅ − = Как видно, все необходимые промежуточные данные уже посчи- таны. Поэтому, подписав в ячейках C15 и C16 комментарии «b =» и «a =», введем в D15 и D16 соответствующие формулы «=(F12- C12*B12)/(D12-C12*C12)» и «=B12-D15*C12». Получены оценки коэффициентов регрессии 630 , 1 bˆ − = , 949 , 79 ˆ = a Отрицательный знак bˆ соответствует убывающей регрессии, а его модуль характеризует угол наклона прямой линии. Итак x , , y ˆ 630 1 949 79 − = Полянский Ю.Н. Эконометрика. Экономическое моделирование и прогнозирование. 27 4) Чтобы увидеть её график и визуально оценить точность подгонки, необходимо нанести эту прямую линию на ту же диаграмму, что и экспери- ментальные данные. Из геометрии известно, что для ручного построения прямой линии достаточно нанести две её точки, которые потом соединить по линейке. Но средства мастера диаграмм Microsoft Excel не позволяют этого сделать. Поэтому рассчитаем прогнозные (теоретические) значения Y для каждого имеющегося в таблице значения объясняющей переменной X. Для этого введем в расчетной таблице дополнительный столбец G, озагла- вив его «Y т ». Т.к. уравнение регрессии уже получено, осталось лишь для каждого i- го значения X рассчитать в столбце G соответствующее прогнозное значе- ние Y. Для этого в ячейке G2 введём формулу «=$D$16+$D$15*C2» и про- тянем по диапазону ячеек G2:G11 (рис.1.19). ! Замечания. • Обратите внимание на абсолютные адреса $D$15 и $D$16. Они необходимы для того, чтобы при протягивании не происходила переиндексация формул по этим ячейкам, соответствующим значениям коэффициентов регрессии. • Быстро проставить абсолютные адреса можно после набора в строке формул адреса ячейки и нажатия клавиши F4. Когда теоретические (прогнозные) значения переменной Y получены, можно их наносить на один график с экспериментальны- ми (так удобнее сравнивать). Для этого на построенном ранее графике (рис.1.15) щелкнем правой кнопкой мыши и в всплывающем ме- ню выберем пункт «Исход- ные данные». В открывшем- ся окне мастера диаграмм ( закладка «Ряд») нажмем на кнопку «Добавить» и введем данные нового ряда (имя, данные X и Y). Точечный график по умолчанию нари- суется маркерами. Но теоретическая прямая должна быть именно непре- рывной прямой, а не набором точек. Для этого дважды щелкнем левой кнопкой мыши на любую вновь построенную теоретическую точку. Откро- ется окно «Формат ряда данных», в котором на вкладке «Вид» укажем: ли- ния – «Другая», толщина – желательно потолще, маркер – «Отсутствует». После нажатия кнопки «ОК» диаграмма должна принять вид, аналогичный рис.1.20. Т.к. экспериментальные и теоретические графики (ряды) нанесе- Рис. 1.20 Полянский Ю.Н. Эконометрика. Экономическое моделирование и прогнозирование. 28 ны на одну диаграмму, хорошо видно, что экспериментальные точки лежат достаточно близко к теоретической прямой линии – графику уравнения парной линейной регрессии. Это согласуется с полученным выше значением коэффициента корреляции, близким по модулю к 1. Задача 1.2 Используя условия и предварительные результаты решения задачи 1.1: 1) оценить качество подгонки полученного уравнения регрессии с по- мощью коэффициента детерминации 2 R ; 2) оценить значимость (статистическую надежность) модели на уровне 05 , 0 = α с помощью F-критерия Фишера-Снедекора; 3) оценить качество полученного уравнения регрессии с помощью сред- ней ошибки аппроксимации A ; 4) дать оценку силы связи между Y и X с помощью среднего коэффици- ента эластичности Э ; Решение. 1) Оценим качество подгонки полученного уравнения регрессии ( качество модели). Или проще: насколько близко проходит её график от всех экспериментальных точек в совокупности, «насколько модель хороша». Оно оценивается коэффициентом детерминации 2 R Q Q Q Q R e R − = = 1 2 , где ∑ = − = n 1 i 2 i r ) y y ˆ ( Q – сумма квадратов, обусловленная регрессией (RSS); ∑ = − = n 1 i 2 i i e ) y ˆ y ( Q – остаточная сумма квадратов (ESS); ∑ = − = n 1 i 2 i ) y y ( Q – общая сумма квадратов (TSS). Здесь i y – это данные столбца B, i yˆ – столбца G таблицы данных. Для расчета R Q и Q необходимы разности y y i − ˆ и y y i − для каждого i-го наблюдения. Введем столбцы H и I, озаглавив их в ячей- ках H1 и I1 соответственно «(Y Т -Y ср.)^2» и «(Y-Yср.)^2» (рис.1.21). В ячейках H2 и I2 введем соответствующие расчетные формулы «=(G2- $B$12)^2» и «=(B2-$B$12)^2» и протянем их по соответствующим диа- пазонам H2:H11 и I2:I11. ! Замечания. • Запись «^2» в Microsoft Excel, как и во многих языках программирования, означает возведение в квадрат. Полянский Ю.Н. Эконометрика. Экономическое моделирование и прогнозирование. 29 • Обратите внимание на абсолютный адрес $B$12, что необходимо для того, чтобы при протягивании столбцов H и I не происходила его переадресация. Суммы полученных квадратов разностей (т.е. столбцов H и I) по- лучим в 13-й строке, озаглавив её «Суммы». Это можно сделать в ячейках H13 и I13 вызовом функции СУММ или щелкнув на кнопку панели инструментов и выделив соответствующий диапазон ячеек (H2:H11 или I2:I11). Осталось только внизу таблицы в ячейке E14 ввести комментарий «R=», а в ячейке F14 – формулу «=H13/I13». Полученное значение коэффициента детерминации 861 , 0 2 = R близко к единице, что говорит о хорошем качестве построенной моде- ли. Можно для проверки даже получить квадрат коэффициента корре- ляции. Как известно, для парной линейной модели имеет место равен- ство 2 2 r R = В ячейке E15 подпишем комментарий «r2=», а в F15 – формулу «=D14*D14». Действительно, 861 , 0 2 2 = = r R Результат пока- зан на рис.1.21. Рис. 1.21 2) Оценим значимость модели в целом. Или проще: «насколько модели можно доверять при имеющихся исходных данных». Как известно, уравнение регрессии значимо, если наблюдаемое значение статистики F больше табличного значения F-критерия Фи- шера-Снедекора (табл. 4 приложения) на уровне α ( обычно 05 , 0 = α ) при 1 m p k 1 − = = и 1 p n m n k 2 − − = − = степенях свободы: Полянский Ю.Н. Эконометрика. Экономическое моделирование и прогнозирование. 30 2 1 ; ; ) 1 ( ) ( k k e R F m Q m n Q F α > − − = Для парной линейной регрессии количество неизвестных 2 = m А ∑ = − = n 1 i 2 i i e ) y ˆ y ( Q , или иначе: R e Q Q Q − = : R R Q Q ) 2 n ( Q F − − = Величины R Q и Q у нас уже посчитаны в ячейках H13 и I13. До- полнительных вычислений не требуется. Впишем в ячейку E16 ком- ментарий «F =» и в ячейку F16 формулу «=H13*(B14-2)/(I13-H13)». Полученное значение 425 , 49 = F надо сравнить с табличным значени- ем. В таблице F-критерия Фишера-Снедекора (таблица 4 приложения) для уровня значимости 05 , 0 = α выберем столбец 1 1 = k и строку 8 2 10 2 2 = − = − = n k Имеем 32 , 5 F 8 ; 1 ; 05 , 0 = Т.к. . табл F F > , то модель значима на уровне 05 , 0 = α Даже на уровне 01 , 0 = α она тоже значима, т.к. 26 , 11 F F 8 ; 1 ; 01 , 0 = > 3) Оценим качество полученного уравнения регрессии с помо- щью средней относительной ошибки аппроксимации % 100 y y ˆ y n 1 A n 1 A n 1 i i |