Эконометрика в Excel (часть 2). Ю. Е. Воскобойников
 Скачать 1.67 Mb.
|
|
Замечание 2.1.2. Индекс детерминации 2 t R характеризует близость построенного уравнения тренда к исходным данным, ко- торые содержат «нежелательную» случайную составляющую ε Очевидно, что, взяв полином порядка 1 n − , можно получить «идеальное» значение 2 1 t R = , но такое уравнение содержит в себе не только независимую переменную τ , но и составляющую ε , и это снижает точность использования построенного уравнения для прогноза. Поэтому при выборе уравнения регрессии надо учиты- вать не только величину 2 t R , но и «сложность» уравнения тренда, определяемую количеством коэффициентов уравнения. Такой учет удачно реализован в так называемом приведенном индексе детерминации (для линейной регрессии – приведенный коэффи- циент детерминации): ( ) ( ) ( ) 2 2 1 1 1 1 1 e t t n Q n R R n m Q n m − ⋅ − = − = − ⋅ − − ⋅ − , (2.1.28) где m – количество коэффициентов регрессии. Видно, что при неизменных e Q , Q , увеличение m вызывает уменьшение значе- ния 2 t R . Если количество коэффициентов у сравниваемых урав- нений регрессии одинаково (например, 2 m = ), то отбор наилуч- шей регрессии можно осуществлять по величине 2 t R . Если в 51 уравнениях регрессии меняется число коэффициентов, то отбор целесообразно проводить по величине 2 t R . ♦ 2.1.5. Выделение трендовой составляющей сглаживающими методами В отличие от регрессионных методов выделения трендовых составляющих эти методы оценивают трендовую составляющую временного ряда только для моментов времени i τ , т.е. для тех же моментов, для которых заданы значения временного ряда i y , 1,..., i n = Локальное сглаживание. Определим оценку j t для значе- ний ( ) j t τ как взвешенное среднее тех исходных значений, кото- рые находятся в некоторой близости от точки j τ , т.е. L j l j l l L t c y + =− = ∑ , 1, 2,..., j L L n L = + + − , (2.1.29) где l c – весовые множители, удовлетворяющие условию 1 L l l L c =− = ∑ . (2.1.30) Видно, что суммируются L значений, находящихся левее точки j τ , L значений – правее точки j τ и само значение j y . Длина интервала суммирования равна ( ) 2 1 L + и этот интервал «скользит» по исходным данным. Наиболее часто используют ме- тод скользящего среднего, в котором множители задаются выра- жением 1 2 1 l c L = + , ,...,0,..., l L L = − (2.1.31) Так, если 1 L = , то 1 0 1 1/ 3 c c c − = = = , а сам метод скользящего среднего примет вид ( ) 1 1 1 3 j j j j l y y y − + = + + (2.1.32) 52 Очевидно, что чем больше величина L , тем меньше уровень «остаточных» возмущений в оценке j f . Действительно, если ( ) ( ) 2 j M ε τ σ = , то после алгоритма (2.1.32) дисперсия оценки j t будет равна ( ) 2 2 / 2 1 / 3 L σ σ + = . Следует помнить, что при воз- растании L увеличивается систематическая ошибка. Систематическая ошибка будет мала, если графическое изо- бражение временного ряда напоминает прямую линию. Если же тренд имеет явно нелинейный характер, то фильтр скользящего среднего может привести к значительным искажениям (т.е. к большой систематической ошибке). В таких случаях предпочти- тельнее использовать метод экспоненциального сглаживания, описанный ниже. К сожалению, выражение (2.1.29) не определяет «отфильтро- ванные» значения в первых и последних L -точках временного ряда. В этих случаях можно изменить алгоритм (2.1.29), исполь- зуя под знаком суммирования только известные j y . Например, в точке 1 τ алгоритм (2.1.32) имеет вид ( ) 1 1 2 1 2 t y y = + , а в точке n τ определяется выражением ( ) 1 1 2 n n n t y y − = + Пример 2.1.4. По данным табл. 2.1, отражающим спрос (в условных единицах) на некоторый товар вычислить значение тренда в точках j τ , 1,2,...,8 j = , используя алгоритм (2.1.32), т.е. 1 L = . Решение. Фрагмент документа Excel, вычисляющий значения j t по формуле (2.1.32) приведен на рис. 2.3, сами значения нане- сены на рис. 2.2 (кривая 3, маркированная треугольниками, ис- ходные значения – кривая 1). Сравнивая эти значения со значени- ем оценки ( ) 2 132.3 55.09 3.26 t τ τ τ = + − (кривая 2), построенной регрессионными методами в примере 2.1.1, видим некоторые от- личия, которые можно объяснить использованием разных мето- дов для выделения тренда временного ряда. 53 Рис. 2.3. Выделение тренда методом скользящего среднего Экспоненциальное сглаживание. В отличие от метода скользящего среднего в определении экспоненциальной средней участвуют все наблюдения исходного временного ряда, но с раз- ными весовыми множителями. Алгоритм метода экспоненциаль- ного сглаживания определяется выражением ( ) 1 1 j j j t t y α α − = − + , 1,2,..., j n = , (2.1.33) где α – коэффициент экспоненциального сглаживания, задавае- мый как 0 1 α < < . Можно доказать справедливость выражения ( ) 1 0 1 j i j j i i t y α α − − = = ⋅ − ∑ , (2.1.34) из которого следует, что каждое «старое» измерение i y входит в оценку ( ) j t i j ≤ с весом ( ) 1 i α α − , т.е. по мере удаления от точки j τ вес измерения i y уменьшается. В качестве начального значе- ния 0 t может быть принято среднее арифметическое всей вре- менной выборки или только ее части, например, среднее трех первых значений. 54 Значение j t можно рассматривать как прогнозное значение тренда в момент j τ и представить как ( ) 1 1 j j j j t t y t α − − = + − . (2.1.35) Из этого выражения видно, что прогноз в момент j τ состоит из двух слагаемых: прогнозного значения 1 j t − в предыдущий момент и невязки (ошибки) прогнозирования 1 j j y t − − , взятой с весом α Из выражения (2.1.34) видно, что, уменьшая величину α , увеличивается степень сглаживания (за счет увеличения числа «значимых» слагаемых). Рекомендуется α определять по форму- ле 2 1 n α = + , (2.1.36) где n – объем выборки. Пример 2.1.5. По данным табл. 2.1, отражающим спрос (в условных единицах) на некоторый товар, вычислить значение тренда в точках j τ , 1,2,...,8 j = , используя алгоритм (2.1.33) при 0.2, 0.5 α = Решение. Фрагмент документа Excel, вычисляющий значения j t по формуле (2.1.33) при 0.5 α = , приведен на рис. 2.4, В каче- стве 0 t принято среднее значение y (ячейка В10). На рис. 2.5 приведены графики: кривая 1 – исходные значения i y (маркированная квадра- тиками); кривая 2 – значения j t , вычисленные при 0.5 α = (марки- рованная треугольниками); кривая 3 – значения j t , вычисленные при 0.2 α = (марки- рованная крестиками). Видим, что меньшему значению параметра α соответствует большая степень сглаживания исходных значений i y . 55 Рис. 2.4. Выделение тренда методом экспоненциального сглаживания Рис. 2.5. Результаты экспоненциального сглаживания при различных значениях 0.2, 0.5 α = Проанализировав примеры выделения тренда разными мето- дами можно сделать вывод, что: наиболее эффективным являет- ся метод, основанный на построении парной регрессии. Этот ме- тод достаточно универсален, позволяет непосредственно решать 56 задачи прогнозирования, лишен недостатка, присущего методам сглаживания при вычислении значений на концах временного ин- тервала. 2.2. Выделение трендовой составляющей с помощью табличного процессора Excel В п. 2.1 расчеты, необходимые для выделения трендовой со- ставляющей осуществлялись в Excel путем программирования соответствующих выражений. Автоматизация таких вычислений заключается в том, что табличный процессор Excel позволяет реализовать все рассмотренные в п. 2.1 методы выделения трен- довой составляющей с использованием следующих операций: • команды Добавить линию тренда; • команды Поиск решения; • режима Скользящее среднее модуля Анализ данных; • режима Экспоненциальное сглаживание модуля Анализ данных. Кратко рассмотрим эти возможности Excel. 2.2.1. Выделение трендовой составляющей регрессионными методами Выделить тренд (т.е. вычислить коэффициенты 0 1 , ,..., k b b b уравнения регрессии) с помощью табличного процессора Excel можно используя команду Добавить линию тренда; используя команду Поиск решения. Команда Добавить линию тренда.При использовании этой команды переменная x играет роль временного аргумента τ Эта команда позволяет построить следующие регрессии: • линейную 0 1 t b b x = + ; • полиномиальную 0 1 k k t b b x b x = + + + … ( 6 k ≤ ); • логарифмическую 0 1 ln t b b x = + ; • степенную 1 0 b t b x = ; • экспоненциальную 0 1 b x t b e = 57 Для построения одной из перечисленных регрессий необхо- димо выполнить следующие шаги: Шаг 1. В выбранном листе Excel ввести по столбцам исход- ные данные { , }, 1,2, , i i y i n τ = … (рис. 2.6). Рис. 2.6. Построение графика по исходным данным Шаг 2. По этим данным построить график в декартовый сис- теме координат (см. рис 2.6). Шаг 3. Установить курсор на построенном графике, сделать щелчок правой кнопкой и в появившемся контекстном меню вы- полнить команду Добавить линию тренда (см. рис. 2.6). Шаг 4. В появившемся диалоговом окне (рис. 2.7) активизи- ровать закладку Тип и выбрать нужное уравнение регрессии. 58 Рис. 2.7. Выбор вида уравнения регрессии Шаг 5. Активизировать закладку Параметры (рис. 2.8) и «включить» необходимые для нас опции: Показать уравнение на диаграмме – на диаграмме будет по- казано выбранное уравнение тренда с вычисленными коэффици- ентами; 59 Рис. 2.8. Задание опций вывода информации Поместить на диаграмму величину достоверности аппрок- симации (R^2) – на диаграмме будет показано значение индекса детерминации 2 t R (см. (2.1.18)), которое можно использовать для проверки значимости построенной регрессии с помощью F-кри- терия (2.1.25). Если по построенному уравнению регрессии необ- ходимо выполнить прогноз, то нужно указать число периодов прогноза (см. рис. 2.8). 60 Назначение других опций понятно из их названий. Шаг 6. После задания всех перечисленных опций щелкнуть OK и на диаграмме появятся формула построенного уравнения регрессии и значение индекса детерминации 2 t R . Пример 2.2.1. Используя команду Добавить линию тренда, по данным табл. 2.1 найти оценку ( ) t τ , предполагая, что ( ) t τ является квадратичной функцией. Решение. При сделанном предположении оценку ˆ( ) t τ будем искать в виде 2 0 1 2 ( ) t b b b τ τ τ = + + Первоначально в документ Excel вводим данные из табл. 4.1 (рис. 2.9, кривая 1). Затем по этим данным строим график и вызы- ваем команду Добавить линию тренда. В появившемся диалого- вом окне зададим необходимые параметры и щелкнем на OK. На экране появится график ˆ( ) t τ (кривая 2), уравнение функции ˆ( ) t τ и значение индекса детерминации (2.1.18), равное 0.849. Заметим, что коэффициенты 0 1 132.3, 55.09, b b = = 2 3.26 b = − совпадают с коэффициентами, вычисленными в примере 2.1.1. ☻ Пример 2.2.2. По данным табл. 2.2 построить уравнения регрессии (предусмотренные командой Добавить линию тренда) и по значению индекса детерминации 2 t R выбрать наилучшее уравнение. Таблица 2.2 i x 1 2 3 4 5 6 i y 10 13.4 15.4 16.5 18.6 19.1 61 Рис. 2.9. Построение уравнения тренда (пример 2.2.1) Решение. Построение каждого из пяти уравнений осуществ- ляем по описанным выше шагам. Для уравнения 1 0 b y b x = ⋅ вы- полнение шагов иллюстрируют рис. 2.6–2.8. В табл. 2.3 заносим регрессионное уравнение и соответствующее значение 2 t R . Сравнивая величину индекса детерминации 2 t R для этих уравне- ний, в качестве «наилучшего» уравнения выбираем степенную регрессию 0.3226 10.18 y x = (номер 5), для которой индекс детер- минации 2 t R = 0.9921. Таблица 2.3 № Уравнение 2 t R 2 t R 1 2 3 4 1 9.28 1.777 t x = + 0.949 0.938 2 9.8759 5.1289 ln t x = + ⋅ 0.9916 0.9895 3 2 6.93 3.5396 0.2518 t x x = + − (полиномиальная, 3 m = ) 0.9896 0.9827 Линия регрессии 62 Окончание табл. 2.3 1 2 3 4 4 2 3 5.8333 4.9192 0.7087 0.0435 t x x x = + − − − (полиномиальная, 4 m = ) 0.9917 0.9792 5 0.3626 10.18 t x = 0.9921 0.9901 6 0.1225 9.8675 x t e = ⋅ 0.9029 0,8786 В этой же таблице приведены значения приведенного ин- декса детерминации, определяемого по формуле(2.1.28). Видно, что по величине 2 t R наилучшей регрессией также является сте- пенная регрессия. Полиномиальная регрессия третьей степени имеет 2 t R значительно меньше коэффициента 2 t R . ☻ Рис. 2.10. График и уравнение построенной регрессии (пример 2.2.2) 63 Команда Поиск решения (пункт меню Сервис).Использует- ся для вычисления параметров (коэффициентов) при которых не- который функционал, зависящий от этих параметров, достигает наименьшего или наибольшего значения. Эта команда позволяет также решать задачи условной оптимизации, т.е. когда ищется минимум или максимум функционала с учетом дополнительных ограничений (линейных или нелинейных) на значения искомых параметров. Например, искомый параметр b должен удовлетво- рять ограничению 0.2 1 b ≤ < . Эта возможность обусловливает существенное преимущество рассматриваемого подхода по срав- нению с командой Добавить линию тренда. К недостатку следу- ет отнести необходимость программировать «вручную» вычисле- ние индекса детерминации 2 t R . Применение команды Поиск решениядля вычисления коэф- фициентов нелинейной регрессии на основе метода наименьших квадратов покажем на следующем примере. Пример 2.2.3. По данным табл. 2.2 построить уравнения степенной регрессии, используя команду Поиск решения. Решение. Первоначально на листе Excel введем исходные данные: значения i τ в ячейках А2÷А7; значения i y в ячейках В2÷В7. Затем в ячейку В9 введем произвольное значение коэф- фициента 0 b , а в ячейку В10 – произвольное значение коэффици- ента 1 b . На рис. 2.11 показан фрагмент документа Excel с введен- ными данными. Следующим шагом является вычисление по уравнению рег- рессии значений 1 0 b i i y b x = ⋅ , 1,...,6 i = . Так, для вычисления зна- чения 1 y в ячейке С2 программируется выражение =$B$9*A2^$B$10. Использование абсолютных адресов для ячеек В9, В10 позволяет «размножить» это выражение на ячейки С3÷С7. Далее в ячейках D2÷D7 вычисляется квадрат невязки при соответствующем значении i x . Так, в ячейку D2 вводится выра- жение =(C2–B2)^2, «размножаемое» в ячейках D3÷D7. Значение минимизируемого функционала МНК вычисляется в ячейке D9 64 (см. рис. 2.11). На этом подготовка необходимой для команды Поиск решения информации завершается. Для выполнения команды Поиск решения необходимо обра- титься к пункту основного меню |