Файл: Контрольная работа по теме Базы данных в Excel 72 IV. Макросы в ms excel 78 Макросы для автоматизации работ 78.doc

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

Суть метода заключается в следующем.

1. 
   Сначала выделяется наиболее существенная регулярная составляющая ряда. Для этого необходимо представить имеющиеся данные в виде графика и хотя бы визуально оценить ее вид.
   
2. 
   Наиболее простые приближения :
   

линейная ;

логарифмическая ;

степенная ;

экспоненциальная .

Здесь а₀, а₁, а₂ – некоторые числовые коэффициенты.

3. 
   Для всех указанных функций в Excel имеется возможность строить так называемые линии тренда.
   

При выполнении лабораторной работы используется программа Excel «Временные ряды.xls». С помощь кнопки «Генерация» произвести генерацию данных. При этом программа случайным образом выбирает вид регулярной составляющей и выводит полученные данные на лист Excel. Фрагмент данных приведен в таблице.

В	С
1	781,4921
2	1179,071
3	1021,827
4	1613,392
5	1330,227
6	1097,985
…	…
…	…

Скопировать эти данные в новую книгу и на их основе построить диаграмму. Тип диаграммы – график. Для получения линии тренда:

Выделить построенную диаграмму – выбрать пункт «Диаграмма» главного меню – Добавить линию тренда – Выбрать тип линии – Через закладку «Параметры» поставить галочку на пункт «Показывать уравнение на диаграмме» - Ok.

---

Если выбран линейный тренд, для представленных данных уравнение имеет вид y=193,87x+234,50.

4. 
   С помощью полученного уравнения рассчитать значение параметра ряда в тех же точках. Для этого в соседнем с данными столбце (D) ввести формулу =193,87*В1+234,5 и скопировать ее на весь столбец. Должно получиться следующее:
   

В	С	D
1	781,4921	428,37
2	1179,071	622,24
3	1021,827	816,11
4	1613,392	1009,98
5	1330,227	1203,85
…	…	…
…	…	…

5. 
   Получить разность между рассчитанными и имеющимися данными.
   

Для рассматриваемого примера должно получиться следующее:

B	C	D	E
1	781,4921	428,37	353,1221
2	1179,071	622,24	556,8315
3	1021,827	816,11	205,7171
4	1613,392	1009,98	603,4116
5	1330,227	1203,85	126,3771
…	…	…	…
…	…	…	…

6. 
   По данным столбца Е построить диаграмму остатков (тип диаграммы – график). Для представленных данных получается примерно следующее (рис. 7.10):
   

Рис. 7.10. Результат удаления из временного ряда регулярной составляющей

7. 
   Оценить качество выбранной линии тренда можно как визуально, так и с помощью суммы остатков. Эта сумма должна быть близкой к нулю. В данном случае она будет равна -0,97. Визуальная оценка состоит в том, что график остатков должен быть похож на искаженную синусоиду.
   
8. 
   Если полученная диаграмма непохожа на синусоиду (например, большая часть данных смещена относительно оси Х), то это означает, что вид регулярной составляющей выбран неправильно. Поэтому следует повторить этапы 3-7 с трендом другого вида.
   

---

В некоторых случаях визуальная оценка между конкурирующими трендами затруднена. Поэтому среди них следует выбрать тот, у которого сумма остатков меньше.

9. 
   Выделение остатков завершает выделение регулярной составляющей ряда. Вторым этапом является выделение циклической составляющей ряда.
   
10. 
    Для этого используется одна из периодических функций – обычно синусоида, уравнение которой в общем виде выглядит следующим образом:
    

, (7.18)

где b₀, b₁, b₃, b₃ – некоторые числовые коэффициенты.

Коэффициенты отвечают за следующие параметры синусоиды:

b₀ – за сдвиг синусоиды по оси Y.

b₁ – за размах значений синусоиды.

b₃ – за сдвиг синусоиды по оси Х.

b₃ – за растяжение синусоиды по оси Х.

11. 
    Для определения параметров синусоиды используется встроенное в Excel средство «Поиск решения».
    
12. 
    Для организации вычислений с его помощью производится следующее:
    

Создается строка коэффициентов, в нее вводятся их начальные значения и по указанному выше уравнению рассчитываются значения остатков. Например:

E	F	G	H	I	J	K
353,1221	287,6553		b0	b1	b2	b3
556,8315	504,8826		0	600	0	0,5
205,7171	598,497					2306951
603,4116	545,5785
126,3771	359,0833
-299,735	84,672
…	…
…	…

---

При этом в столбец F введена формула
=$H$2+$I$2*SIN($J$2+$K$2*B1),

которая затем скопирована на весь столбец F.

13. 
    Для нахождения точных значений коэффициентов синусоиды:
    

а) Вычисляется функция СУММКВРАЗН, в качестве аргументов которой указываются столбцы E и F. Пусть это вычисление произведено в ячейке К3 и его начальный результат приведен в предыдущей таблице.

б) В ячейки I2, J2 и K2 вводятся примерные значения коэффициентов синусоиды. Их можно оценить следующим образом:

b₁ – определяет размах синусоиды. Из предыдущего рисунка он примерно равен 600;

b₂ – определяет сдвиг синусоид по оси Х. Из предыдущего рисунка он, примерно, равен 0;

b₃ – определяет растяжение синусоиды по оси Х. Из предыдущего рисунка он равен, примерно, 0,5.

Эти значения и введены в предыдущую таблицу.

в) Курсор устанавливается на ячейку К3 и вызывается средство «Поиск решения». В появившемся окне переключатель установить в положение «Минимальное значение», а в поле «Изменяя ячейки» указать I2:K2 и, затем «Выполнить». Если все было сделано правильно, то для данных чисел получается следующее:

E	F	G	H	I	J	K
353,1221	291,9481		b0	b1	b2	b3
556,8315	455,3418		1,014694	510,8945	0,116407	0,491859
205,7171	510,7799					1970646
603,4116	445,1186
126,3771	273,9255
-299,735	37,78814
-159,236	-207,308

---

При этом уточнились как сами коэффициенты, так и рассчитанные по ним значения.

14. 
    По данным столбцов E и F построить совместную диаграмму следующего вида (рис. 7.11)
    

Рис. 7.11. Результат сглаживания сезонной составляющей с помощью формулы (7.18)
Ее внешний вид позволяет визуально оценить качество аппроксимации остатков синусоидой.

15. 
    В столбце G рассчитать остатки второго уровня, т.е. разность между остатками и синусоидой. Для этого в ячейку G1 вводится формула =E1-F1, которая затем копируется на весь столбец.
    
16. 
    По данным столбца строится диаграмма остатков второго уровня (рис. 7.12).
    

Рис. 7.12. Диаграмма остатков второго уровня
Если регулярная составляющая была подобрана и рассчитана правильно и, если также правильно была рассчитана периодическая составляющая, то диаграмма должна иметь вид случайного недетерминированного ряда. Признаком случайности является значение суммы этих остатков. Она должна быть много меньшей, чем сумма остатков первого уровня. В данном случае она оказалась равной 0,002332, что намного меньше суммы остатков первого уровня (-0.97).

17. 
    Используя уравнения отдельных составляющих, можно составить общее уравнение ряда. В данном случае оно будет выглядеть следующим образом:
    

y=235,515+193,87*x+510,89*Sin(0,116+0,49*x).
Полученное уравнение можно использовать для целей прогноза. Для этого достаточно подставить в него то значение Х, для которого мы хотим узнать значение Y.

Литература

1. 
   Бухвалов, А.В. Финансовые вычисления для менеджеров : учеб. пособие / А. В. Бухвалов, В. В. Бухвалова. – 2-е изд., испр. и доп. – СПб. : Высш. шк. менеджмента, 2009. - 367 с.
   
2. 
   ГОСТ 34.601-90. Информационная технология. Комплекс стандартов на автоматизированные системы. Автоматизированные системы. Стадии создания. – М.: Стандартинформ, 2009. – 6 с.
   
3. 
   Каплан, А. В. Решение экономических задач на компьютере / Каплан А. В., Каплан В. Е, Мащенко М. В., Овечкина Е. В. – М. : ДМК Пресс, 2008 . – 600 с. : ил.
   
4. 
   Карлсберг, Конрад. Бизнес-анализ с использованием Excel. Решение бизнес-задач : Пер. с англ.: учеб. Пособие / К. Карлсберг. – 4-е изд., – М.: Издательский дом «Вильямс», 2012. – 576 с.: ил.
   
5. 
   Россия в цифрах, 2002 : краткий статистический сборник / Гос. ком. Рос. Федерации по статистике (Госкомстат России) . – М. : Госкомстат России, 2002. – 399 с. – 5–89476–103–4.
   
6. 
   Титоренко, Г. А. Информационные технологии в экономике. / Г. А. Титоренко. – 2-е изд., перераб. и доп. – М. : Юнити-Дана, 2008. – 463 с.
   
7. 
   Уокенбах, Джон. Excel 2010: профессиональное программирование на VBA. : Пер с англ. / Дж. Уокенбах. – М. : ООО «И. Д. Вильямс», 2012. – 944 с.: ил.
   
8. 
   Федеральный государственный образовательный стандарт высшего профессионального образования по направлению подготовки 080200 «Менеджмент». – М., 2010. – 31 с.
   
9. 
   Филимонова, Е. В. Информационные технологии в экономике / Филимонова Е. В. , Черненко Н. А. , Шубин А. С. – М. : Феникс, 2008. – 443 с.
   
10. 
    Цисарь, И. Ф. Компьютерное моделирование экономики / И. Ф. Цисарь, В. Г. Нейман – М.: «Издательство ДИАЛОГ-МИФИ», 2008. – 384 с.
    
11. 
    Чувашская Республика и регионы Приволжского федерального округа, 2004 : статистический сборник / Ком. гос. статистики Чуваш. Республики. – Чебоксары : Госкомстат Чуваш. Респ. , 2004. – 181 с.
    

---