Эконометрика в Excel (часть 2). Ю. Е. Воскобойников
 Скачать 1.67 Mb.
|
|
Сервис и в появившемся меню щелкнуть мышью на команде Поиск решения. Рис. 2.11. Задание параметров команды Поиск решения 65 Затем в появившемся диалоговом окне выполнить следую- щие действия (см. рис. 2.11): • в поле ввода Установить целевую ячейку: ввести адрес ячейки, в которой вычисляется значение минимизируемого функ- ционала (в нашем примере D9); • включить опцию Равной: минимальному значению: (ищут- ся значения коэффициентов, при которых функционал достигает своего минимального значения); • в поле ввода Изменяя значения: ввести адреса ячеек, в которых находятся значения искомых коэффициентов (в нашем примере это ячейки В9, В10); • щелкнув мышью на кнопке Добавить, сформировать ог- раничения на значения искомых коэффициентов (в нашем приме- ре это требование неотрицательности искомых коэффициентов). Рис. 2.12.Результатывыполнения командыПоиск решения 66 После выполнения этих операций щелкнуть на кнопке Выполнить. Начинается поиск решения введенной оптимизаци- онной задачи. Спустя некоторое время на экране появится новое диалоговое окно Результаты поиска решения (рис. 2.12). Для со- хранения найденных значений коэффициентов в соответствую- щих ячейках необходимо включить опцию Сохранить найден- ное решение и щелкнуть на кнопке OK. Из рис. 2.12 видно, что вычисленные значения коэффициен- тов находятся в ячейках В9, В10 и равны 0 10.28299 b = , 1 0.354496 b = . Ячейка D9 содержит значение минимизируемого функционала. Найденные значения коэффициентов незначитель- но отличаются от значений, вычисленных в примере 2.2.2 с по- мощью команды Добавить линию тренда. ☻ 2.2.2. Выделение трендовой составляющей с использованием режима Скользящее среднее В режиме Скользящее среднее реализуется алгоритм 0 2 1 , 2 1, 2 2,.., 2 1 j j l l L t y j L L n L + =− = ⋅ = + + ∑ + (2.2.1) с одинаковыми весами 1 2 1 l c L = + . Для вызова режима Скользя- щее среднее необходимо обратиться к пункту меню Сервис, ко- манде Анализ данных, выбрать в списке режимов Скользящее среднее и щелкнуть на кнопке OK. В появившемся диалоговом окне Скользящее среднее задать следующие параметры (рис. 2.13): Входной интервал: диапазон адресов ячеек, содержащих зна- чения i y Метки в первой строке: включается, если первая строка во входном интервале содержит заголовки. В этом случае автомати- чески будут созданы стандартные названия. Интервал: задается размер окна ( 2 1 L + ). По умолчанию размер 3, т.е. 1 L = . 67 Выходной интервал: Новый рабочий лист: : / Новая рабочая книга: содержит адрес верхней ячейки, начиная с которой выво- дятся вычисленные сглаженные значения. Вывод графика – включает вывод графика заданных и сгла- женных значений временного ряда. Стандартные погрешности – включает вычисление и вывод в виде столбца стандартных погрешностей j t σ , которые вычис- ляются по формуле 0 2 2 1 ( ) 2 1 j j i j i t i L y t L σ + + =− = ⋅ − ∑ + . (2.2.2) Рис. 2.13. Диалоговое окно режима Скользящее среднее Пример 2.2.4. По данным табл. 2.1 вычислить значения тренда ( ) j t τ , используя режим Скользящее среднее. Решение. Введем в документ Excel исходные данные (рис. 2.13), а затем вызовем режим Скользящее среднее и зададим необходимые параметры (см. рис. 2.13). На рис. 2.14 показаны графики значений j y (маркированные ромбами) и j t (маркирован- ные квадратами). ☻ 68 Замечание 2.2.1. Из формулы (2.2.1) следует, что в первых 2L ячейках значения j t не определены, а из выражения (2.2.2) следует, что значения j t σ не вычисляются в первых 4L ячей- ках. ♦ Пример 2.2.4 хорошо иллюстрирует это замечание. Так в ячейках С2, С3 документа на рис. 2.14 не определены сглаженные значения, в ячейках D2÷D5 не определены значения стандартных погрешностей j t σ Рис. 2.14. Результаты работы режима Скользящее среднее Задание. Вычислите значение тренда, задав (2 1) 5 L + = , сравните с предыдущими результатами. 69 2.2.3. Выделение трендовой составляющей с использованием режим Экспоненциальное сглаживание Режим Экспоненциальное сглаживание реализует алгоритм (2.1.33). Для вызова режима необходимо обратиться к пункту ос- новного меню Сервис, выполнить команду Анализ данных, а за- тем в появившемся списке режимов работы выделить Экспонен- циальное сглаживание и щелкнуть на OK. В появившемся диа- логовом окне (см. рис. 2.15) задать необходимые параметры. Па- раметры режима Экспоненциальное сглаживание совпадают с параметрами режима Скользящее среднее за исключением одно- го параметра. Вместо параметра Интервал необходимо задать Фактор затухания, равный величине α в формуле (2.1.33), кото- рый может меняться в интервале (0,1) (см. соотношение (2.1.36)). Рис. 2.15. Диалоговое окно режима Экспоненциальное сглаживание Пример 2.2.5. По данным табл. 2.1 вычислить значение тренда ( ) j j t t τ = , используя режим Экспоненциальное сглажи- вание Решение. Введем в документ Excel исходные данные (рис. 2.16), вызовем режим Экспоненциальное сглаживание и 70 зададим необходимые параметры ( 0.2 α = ) (см. рис. 2.15). На рис. 2.16 показаны графики значений j y (маркированные ромба- ми) и j t (маркированные квадратами). Рис. 2.16. Результаты работы режима Экспоненциальное сглаживание Видно, что: первое значение 1 t не вычисляется (ячейка С2); чем меньше значение α , тем больше степень сглаживания значений i y временной выборки. 71 В столбце D (ячейки D5÷D8) содержатся значения j t σ – среднеквадратические отклонения значений j t , которые вычис- ляются по формуле 0 2 2 ( ) 3 j j i j i i t y t σ + + =− − ∑ = (2.2.3) Задание. Вычислите значение тренда, задав 0.6 α = . Сравни- те полученные результаты с результатами примера и объясните отличия. 2.3. Выделение тригонометрической составляющей временного ряда К тригонометрической составляющей c( τ ) временного ряда относятся: сезонная составляющая ( ) s t , отражающая повторяемость экономических процессов в течение не очень длительного перио- да (года, иногда месяца); периодическая составляющая ( ) p τ , отражающая повто- ряемость экономических процессов в течение длительных перио- дов (например, волны экономической активности Кондратьева). Решение о наличии тригонометрической составляющей в структуре временного ряда принимается на основе анализа авто- корреляционной функции и коррелограммы временного ряда (см. п. 1.2). Для выделения и анализа тригонометрической составляющей временного ряда используются методы гармонического анализа периодических функций. 2.3.1. Основы гармонического анализа периодических функций Гармонический анализ позволяет представить периодиче- скую функцию линейной комбинацией косинусов и синусов. 72 Предположим, что функция ( ) ϕ τ является непрерывной функцией с периодом T . Тогда функцию ( ) ϕ τ можно предста- вить рядом Фурье вида 0 1 2 2 ( ) [ cos( ) sin( )] k k k k k a a b T T π π ϕ τ τ τ ∞ = = + + ∑ , 0 T τ ≤ ≤ , (2.3.1) где k – номер гармоники. Видно, что при увеличении номера уменьшается период функций 2 cos( ) k T π τ , 2 sin( ) k T π τ . Коэффи- циенты разложения определяются формулами: 0 0 1 ( ) ; T a d T ϕ τ τ = ∫ 0 2 2 ( )cos( ) ; T k k a d T T π ϕ τ τ τ = ∫ 0 2 2 ( )sin( ) . T k k b d T T π ϕ τ τ τ = ∫ Аргументы тригонометрических функций cos, sin можно тракто- вать как частоты k ω , определяемые соответствующим номером гармоники, т.е. 2 k k T π ω = (2.3.2) Величины 2 2 k k k S a b = + характеризуют «энергетический вклад» k-й гармоники в функцию ( ) ϕ τ . Зависимость величины k S от номера гармоники k (или от частоты k ω (2.3.2)) характеризует спек- тральный состав (или спектр) функции ( ) ϕ τ . Сравнительно большие величины k S определяют частоты, на которых сосредо- точена основная энергия функции ( ) ϕ τ Под аппроксимацией функции ( ) ϕ τ рядом Фурье понимают новую функцию ( ) ϕ τ , полученную суммированием первых чле- нов ряда (2.3.1), число которых обозначим 0 K , т.е. 73 0 0 1 2 2 ( ) [ cos( ) sin( )] K k k k k k a a b T T π π ϕ τ τ τ = = + + ∑ . (2.3.3) Видно, что в функции ( ) ϕ τ отсутствуют «высокочастотные» гармоники с номерами 0 k K > , которые присутствовали в исход- ной функции ( ) ϕ τ . Такой способ получения функции ( ) ϕ τ часто называют низкочастотной фильтрацией функции ( ) ϕ τ По аналогии можно построить новую функцию ( ) ϕ τ , содер- жащую только заданные гармоники, например, гармоники с наи- более значимым спектром k S . Предположим, что такие гармони- ки имеют номера 3,8 k = . Тогда функция ˆ( ) ϕ τ , содержащая только эти гармоники, записывается в виде 3 3 2 3 2 3 ( ) cos( ) sin( ) a b T T π π ϕ τ τ τ = + + 8 8 2 8 2 8 cos( ) sin( ) a b T T π π τ τ + + Такой способ построения функции широко используется для вы- деления тригонометрической составляющей временного ряда. Пример 2.3.1. Дана функция 2 2 ( ) 0.1 0.4 0.5 3sin( 5 ) 3.2 π ϕ τ τ τ τ = + + + , (2.3.4) определенная на интервале [0,3.2]. График функции показан сплошной линией на рис. 2.17. Необходимо вычислить спектр k S этой функции и выделить из функции ( ) ϕ τ основную (имеющую наибольшее значение спектра) тригонометрическую составляю- щую. 74 Рис. 2.17. Тригонометрическая составляющая функции ( ) ϕ τ Решение. Из аналитического задания ( ) ϕ τ (2.3.4) следует, что тригонометрическая составляющая этой функции обусловле- на слагаемым 2 3sin( 5 ) 3.2 π τ ⋅ и соответствует гармоники с номером 5. Так как функция задана на интервале [0,3.2] , то период этой функции задаем 3.2 T = . Используя приведенные выше формулы, вычисляем коэффициенты 0 1, , 20 , , , k k a a b k = … и определяем спектры 2 2 2 0 0 , k k k S a S a b = = + . Значения k S приведены на рис. 2.18 (кривая 1). Большие значения 0 1 , S S обусловлены наличием в функции ( ) ϕ τ квадратичного тренда (первые три слагаемых в (2.3.4)), большое значение 5 S обусловлено присутствием в ( ) ϕ τ тригонометрической составляющей, для которой вычислены ко- эффициенты 5 5 0.021, 2.593 a b = = Построим функцию 2 2 ( ) 0.021 cos( 5 ) 2.593 sin( 5 ) 3.2 3.2 π π ϕ τ τ τ = ⋅ + ⋅ , которая соответствует этой гармонике. График этой функции приведен на рис. 2.17 (штриховая кривая), здесь же приведен график функции τ 75 2 3sin( 5 ) 3.2 π τ ⋅ (точечная кривая), которая входит в исходную функцию ( ) ϕ τ . Из хорошего совпадения этих графиком можно сделать вывод об эффективности применения методов гармони- ческого анализа для выделения тригонометрических составляю- щих периодических функций. ☻ Рис. 2.18. Спектральный состав функции ( ) ϕ τ 2.3.2. Выделение тригонометрической составляющей временного ряда методами гармонического анализа Использование рядов Фурье для выделения тригонометриче- ской составляющей временного ряда отличается от рассмотрен- ного выше следующим: 1. Значения временного ряда заданы в дискретные моменты времени i τ и чаще всего эти моменты представляют собой ариф- метическую прогрессию с шагом τ Δ , т.е. Номер гармоники Спектр k S 2 1 2 3 76 ( 1) , 1,2, , i нач i i n τ τ τ = + − ⋅ Δ = … . (2.3.5) Тогда в качестве периода принимается величина T n τ = Δ ⋅ , (2.3.6) а условие периодичности значений временного ряда имеет вид , 1,..., n i i y y i n + = = В дальнейшем полагается, что i τ образуют арифметическую про- грессию (2.3.5) при 0 нач τ = . 2. Временной ряд ( ) Y τ , кроме тригонометрической состав- ляющей, содержит случайную составляющую ( ) ε τ , которую не- обходимо отделить от тригонометрической составляющей. Первое отличие обусловливает замену интегралов, опреде- ляющих значения 0 , k a a , k b квадратурными формулами, в кото- рые входят значения , 1, 2,.., i y i n = . В качестве примера примем формулу левых прямоугольников и тогда получим следующие выражения для вычисления интегралов: * 0 1 1 ; n i i a y n = = ⋅ ∑ (2.3.7) * 1 2 2 cos( ); n k i i i k a y n T π τ = = ⋅ ∑ * 1 2 2 sin( ) n k i i i k b y n T π τ = = ∑ Символ «*» означает, что коэффициенты вычислены по дискрет- ным значениям временного ряда, или иначе – по временной вы- борке. Если период T n τ = ⋅ Δ , то две последние формулы принима- ют вид * 1 2 2 cos( ) n k i i k a y i n n π = = ⋅ ∑ , (2.3.8) * 1 2 2 sin( ) n k i i k b y i n n π = = ∑ (2.3.9) 77 По аналогии со спектром k S определим дискретный спектр как * * 2 * 2 ( ) ( ) k k k S a b = + . Дискретность задания значений i y обу- словливает симметричность спектра * k S относительно точки / 2 n , т.е. * * / 2 / 2 , 1, , / 2 1 n j n j S S j n + − = = − … . Поэтому имеет смысл вычис- лить коэффициенты ряда Фурье для гармоник с номерами 0,1,2,.., / 2 k n = , а саму функцию ( ) p τ аппроксимировать рядом 1 1 2 2 * * * * 0 1 1 2 2 2 ( ) sin n n k k n k k k k c a a cos b a T T π π τ τ τ − − = = ⎛ ⎞ ⎛ ⎞ = + ⋅ + ⋅ + ⎜ ⎟ ⎜ ⎟ ⎝ ⎠ ⎝ ⎠ ∑ ∑ , (2.3.10) где ( ) * 1 2 1 1 n i n i i a y n = = ⋅ − ∑ (2.3.11) Учет второго отмеченного момента основан на следующем предположении: амплитуда случайной составляющей ( ) ε τ на- много меньше амплитуды ( ) c τ и спектр ( ) ε τ более менее равно- мерно «распределен» по гармоникам с различными номерами, т.е. сигнал ( ) ε τ имеет «широкий» спектр, но его вклад в спектр каж- дой гармоники тригонометрической составляющей сравнительно мал. |