Добавлен: 23.10.2018
Просмотров: 1488
Скачиваний: 13
Можно
проводить линию регрессии на основании
полного набор данных, однако изначально
мы не работаем для получения точных
значений, поэтому требуется разбить
курсы доллара и евро на соответствующие
интервалы. Возьмём число интервалов
и поделим соответствующие курсы на
интервалы в соответствии с таблицами:
|
Для евро: |
Для доллара: |
||||||||
|
5 месяцев |
|
|
|
5 месяцев |
|
|
|
||
|
|
41,1311 |
41,13 |
41,84 |
41,49 |
|
29,3916 |
29,39 |
30,27 |
29,83 |
|
|
46,8392 |
41,84 |
42,56 |
42,20 |
|
36,4267 |
30,27 |
31,15 |
30,71 |
|
|
8 |
42,56 |
43,27 |
42,91 |
|
8 |
31,15 |
32,03 |
31,59 |
|
|
0,7135125 |
43,27 |
43,99 |
43,63 |
|
0,879388 |
32,03 |
32,91 |
32,47 |
|
|
44,2204404 |
43,99 |
44,70 |
44,34 |
|
33,47334 |
32,91 |
33,79 |
33,35 |
|
|
44,70 |
45,41 |
45,06 |
|
33,79 |
34,67 |
34,23 |
||
|
45,41 |
46,13 |
45,77 |
34,67 |
35,55 |
35,11 |
||||
|
46,13 |
46,84 |
46,48 |
35,55 |
36,43 |
35,99 |
||||
Используя данные таблиц требуется построить матрицу корреляции по интервалам, после чего составлять линию регрессии.
Матрица
корреляции представляет из себя таблицу
на 
полей, в каждой из клеток которой записано
количество дней, курсы которых попадают
в соответствующий ей интервал по доллару
и по евро.
Однако, в рамках Excel это можно осуществить при помощи умножения двух матриц.
Табл. $
|
Дата и курс доллара |
01.01.2009 |
02.01.2009 |
03.01.2009 |
04.01.2009 |
... |
31.05.2009 |
|
|
|
|
29,3916 |
29,3916 |
29,3916 |
29,3916 |
... |
30,9843 |
|
35,55 |
36,43 |
0 |
0 |
0 |
0 |
... |
0 |
|
34,67 |
35,55 |
0 |
0 |
0 |
0 |
... |
0 |
|
33,79 |
34,67 |
0 |
0 |
0 |
0 |
... |
0 |
|
32,91 |
33,79 |
0 |
0 |
0 |
0 |
... |
0 |
|
32,03 |
32,91 |
0 |
0 |
0 |
0 |
... |
0 |
|
31,15 |
32,03 |
0 |
0 |
0 |
0 |
... |
0 |
|
30,27 |
31,15 |
0 |
0 |
0 |
0 |
... |
1 |
|
29,39 |
30,27 |
1 |
1 |
1 |
1 |
... |
0 |
Табл. Є
|
Дата и курс евро |
|
41, 1311 |
41, 8446 |
42, 5581 |
43, 2716 |
43, 9852 |
44, 6987 |
45, 4122 |
46, 1257 |
|
|
41, 8446 |
42, 5581 |
43, 2716 |
43, 9852 |
44, 6987 |
45, 4122 |
46, 1257 |
46, 8392 |
|
|
01.01. 2009 |
41, 4275 |
1 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
02.01. 2009 |
41, 4275 |
1 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
03.01. 2009 |
41, 4275 |
1 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
04.01. 2009 |
41, 4275 |
1 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
|
31.05. 2009 |
43, 378 |
0 |
0 |
0 |
1 |
0 |
0 |
0 |
0 |
Создадим
две таблицы, 8x151
и 151x8,
которые будут служить нам вспомогательными
матрицами. В каждой из таблиц число 151
– это число дней в исследуемом временном
промежутке (5 месяцев), а 8 – число
интервалов. Над каждым из 151 столбцов
(151 строк соответственно во второй
таблице) проставим даты и соответствующие
курсы доллара (евро), а перед каждой из
8 строк (столбцов) проставим значения
и 
для данного интервала так, как указано
в таблицах.
Теперь,
в каждой ячейке таблицы требуется
определить, попадает ли соответствующий
ей курс в интервал, указанный для этой
ячейки. Результат выполнения условий
и 
получаем, используя логическую функцию
«ЕСЛИ» и объединение условий «И». На
выходе имеем требуемую таблицу, в которой
каждому дню должна соответствовать
лишь одна единица и 7 нулей (очевидно,
что каждый курс может попадать лишь в
один интервал).
Далее при помощи умножения матриц (именно в порядке Доллар на Евро, а не наоборот, так как в результате должна выйти матрица 8х8) мы получаем матрицу корреляции:
|
|
Евро:
|
41,1311 |
41,8446 |
42,5581 |
43,2716 |
43,9852 |
44,6987 |
45,4122 |
46,1257 |
|
|
Доллар:
|
|
41,8446 |
42,5581 |
43,2716 |
43,9852 |
44,6987 |
45,4122 |
46,1257 |
46,8392 |
|
|
35,5473 |
36,4267 |
0 |
0 |
0 |
0 |
0 |
14 |
8 |
9 |
31 |
|
34,6679 |
35,5473 |
0 |
0 |
0 |
0 |
2 |
8 |
3 |
0 |
13 |
|
33,7885 |
34,6679 |
0 |
0 |
0 |
0 |
5 |
5 |
1 |
0 |
11 |
|
32,9092 |
33,7885 |
0 |
0 |
0 |
10 |
19 |
10 |
5 |
0 |
44 |
|
32,0298 |
32,9092 |
0 |
6 |
4 |
14 |
0 |
0 |
0 |
0 |
24 |
|
31,1504 |
32,0298 |
1 |
1 |
1 |
8 |
0 |
0 |
0 |
0 |
11 |
|
30,2710 |
31,1504 |
2 |
0 |
0 |
4 |
0 |
0 |
0 |
0 |
6 |
|
29,3916 |
30,2710 |
11 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
11 |
|
|
|
14 |
7 |
5 |
36 |
26 |
37 |
17 |
9 |
|
Данная
корреляционная матрица может быть
переведена в графический вид. Если на
координатной плоскости, по оси 
которой отмечается курс евро, а по оси
– курс доллара, отметить пары курсов
за каждые сутки, после чего поделить
плоскость по осям на данные интервалы,
то в каждую из образовавшихся ячеек
войдёт указанное в таблице количество
точек. Для проверки: суммарное количество
точек должно соответствовать количеству
дней в исследуемом интервале. Если нет,
то имеет смысл пересмотреть правильность
выполнения предыдущих пунктов.
По
данным таблицы 20 можно построить линию
регрессии. Для этого для каждого интервала
по евро и по доллару определяем среднее
арифметическое (середину интервала)
,
,
а для доллара определяем математическое
ожидание в соответствующем интервале
евро, 
,
где 
- это 
ячейка
таблицы или количество дней, попавшее
в соответствующие интервалы по евро и
доллару. Получаем таблицу значений:
|
|
41,4879 |
42,2014 |
42,9149 |
43,6284 |
44,3419 |
45,0554 |
45,7689 |
46,4824 |
|
|
30,0825473 |
32,344 |
32,294 |
32,323 |
33,653 |
34,846 |
34,952 |
35,987 |
Линия
регрессии определяется двумя коэффициентами
и 
в уравнении 
.
В данном случае 
выступает в роли 
,
а 
в роли 
.
Эти коэффициенты можно определить при
помощи графиков Excel.
Для этого достаточно построить точечную
диаграмму по данным таблицы, добавить
в него линию тренда и включить опцию
«отображать уравнение» в настройках
линии тренда. Можно воспользоваться
статистическими формулами и при помощи
функций «КОВАР», «ДИСПР» и «СРЗНАЧ»
определить коэффициенты по формулам:
,
,
где 
- это дисперсия значений 
,
а 
– ковариация между
и 
.
Таким образом имеем полученные
коэффициенты:
|
|
1,057184 |
|
|
-13,1902 |
Теперь выпишем реальные данные за Июнь 2009 г. в отдельную таблицу, рядом добавим дополнительные столбцы, которые будут отражать расчётный курс доллара на этот месяц по коэффициентам из таблицы и реальным данным евро и ошибку в расчётах на основе реальных данных по доллару на этот месяц:
|
Дата |
Курс Евро |
Курс доллара реальный |
Курс расчётный |
Ошибка |
|
01.06.2009 |
43,378 |
30,9843 |
32,6683447 |
-1,6840447 |
|
02.06.2009 |
43,4875 |
30,7441 |
32,78410637 |
-2,0400064 |
|
03.06.2009 |
43,4152 |
30,7321 |
32,70767195 |
-1,975572 |
|
04.06.2009 |
43,649 |
30,5131 |
32,95484161 |
-2,4417416 |
|
05.06.2009 |
43,8542 |
30,8767 |
33,1717758 |
-2,2950758 |
|
06.06.2009 |
43,6009 |
30,6919 |
32,90399105 |
-2,212091 |
|
07.06.2009 |
43,6009 |
30,6919 |
32,90399105 |
-2,212091 |
|
08.06.2009 |
43,6009 |
30,6919 |
32,90399105 |
-2,212091 |
|
09.06.2009 |
43,328 |
31,0751 |
32,61548549 |
-1,5403855 |
|
10.06.2009 |
43,4909 |
31,2637 |
32,78770079 |
-1,5240008 |
|
11.06.2009 |
43,5895 |
30,9277 |
32,89193915 |
-1,9642391 |
|
12.06.2009 |
43,3546 |
30,9124 |
32,64360659 |
-1,7312066 |
|
13.06.2009 |
43,3546 |
30,9124 |
32,64360659 |
-1,7312066 |
|
14.06.2009 |
43,3546 |
30,9124 |
32,64360659 |
-1,7312066 |
|
15.06.2009 |
43,3546 |
30,9124 |
32,64360659 |
-1,7312066 |
|
16.06.2009 |
43,2958 |
31,1548 |
32,58144416 |
-1,4266442 |
|
17.06.2009 |
43,3511 |
31,3185 |
32,63990645 |
-1,3214064 |
|
18.06.2009 |
43,2796 |
31,1297 |
32,56431778 |
-1,4346178 |
|
19.06.2009 |
43,434 |
31,0998 |
32,72754701 |
-1,627747 |
|
20.06.2009 |
43,3914 |
31,1541 |
32,68251097 |
-1,528411 |
|
21.06.2009 |
43,3914 |
31,1541 |
32,68251097 |
-1,528411 |
|
22.06.2009 |
43,3914 |
31,1541 |
32,68251097 |
-1,528411 |
|
23.06.2009 |
43,3216 |
31,2408 |
32,60871951 |
-1,3679195 |
|
24.06.2009 |
43,7556 |
31,5765 |
33,06753744 |
-1,4910374 |
|
25.06.2009 |
43,9274 |
31,1365 |
33,24916168 |
-2,1126617 |
|
26.06.2009 |
43,5728 |
31,2037 |
32,87428417 |
-1,6705842 |
|
27.06.2009 |
43,6965 |
31,1184 |
33,00505786 |
-1,8866579 |
|
28.06.2009 |
43,6965 |
31,1184 |
33,00505786 |
-1,8866579 |
|
29.06.2009 |
43,6965 |
31,1184 |
33,00505786 |
-1,8866579 |
|
30.06.2009 |
43,8191 |
31,2904 |
33,13466863 |
-1,8442686 |
Как видно по таблице, расчётный курс на каждый день оказался гораздо выше реального, что может быть обусловлено разнообразными причинами. Вероятно, что линейная регрессия по 5-и месяцам даёт слишком большую ошибку, поэтому можно уменьшить исследуемый промежуток до 4-ёх или 3-ёх месяцев. Для этого потребуется повторить все действия, начиная с формирования интервалов и заканчивая корреляционной таблицей. В итоге можно получить следующие результаты:
|
Курс по 5 месяцам |
Курс по 3 месяцам |
Курс по 4 месяцам |
Ошибка 5 месяцев |
Ошибка 3 месяца |
Ошибка 4 месяца |
|
32,6683447 |
32,3189752 |
32,59936249 |
-1,6840447 |
-1,334675199 |
-1,615062492 |
|
32,78410637 |
32,45399694 |
32,70034908 |
-2,0400064 |
-1,709896938 |
-1,956249075 |
|
32,70767195 |
32,3648456 |
32,63367026 |
-1,975572 |
-1,632745598 |
-1,901570263 |
|
32,95484161 |
32,65313859 |
32,84929276 |
-2,4417416 |
-2,140038591 |
-2,336192758 |
|
33,1717758 |
32,90616563 |
33,03853885 |
-2,2950758 |
-2,029465632 |
-2,161838849 |
|
32,90399105 |
32,59382767 |
32,80493244 |
-2,212091 |
-1,901927672 |
-2,113032441 |
|
32,90399105 |
32,59382767 |
32,80493244 |
-2,212091 |
-1,901927672 |
-2,113032441 |
|
32,90399105 |
32,59382767 |
32,80493244 |
-2,212091 |
-1,901927672 |
-2,113032441 |
|
32,61548549 |
32,25732144 |
32,5532499 |
-1,5403855 |
-1,182221436 |
-1,478149897 |
|
32,78770079 |
32,45818939 |
32,70348473 |
-1,5240008 |
-1,194489394 |
-1,439784732 |
|
32,89193915 |
32,57977061 |
32,79441877 |
-1,9642391 |
-1,652070614 |
-1,86671877 |
|
32,64360659 |
32,29012124 |
32,5777818 |
-1,7312066 |
-1,377721238 |
-1,665381797 |
|
32,64360659 |
32,29012124 |
32,5777818 |
-1,7312066 |
-1,377721238 |
-1,665381797 |
|
32,64360659 |
32,29012124 |
32,5777818 |
-1,7312066 |
-1,377721238 |
-1,665381797 |
|
32,64360659 |
32,29012124 |
32,5777818 |
-1,7312066 |
-1,377721238 |
-1,665381797 |
|
32,58144416 |
32,21761641 |
32,52355339 |
-1,4266442 |
-1,062816413 |
-1,368753385 |
|
32,63990645 |
32,28580547 |
32,57455392 |
-1,3214064 |
-0,967305475 |
-1,256053916 |
|
32,56431778 |
32,19764059 |
32,5086129 |
-1,4346178 |
-1,067940594 |
-1,378912905 |
|
32,72754701 |
32,38802741 |
32,6510086 |
-1,627747 |
-1,288227413 |
-1,551208599 |
|
32,68251097 |
32,33549841 |
32,61172067 |
-1,528411 |
-1,181398407 |
-1,457620667 |
|
32,68251097 |
32,33549841 |
32,61172067 |
-1,528411 |
-1,181398407 |
-1,457620667 |
|
32,68251097 |
32,33549841 |
32,61172067 |
-1,528411 |
-1,181398407 |
-1,457620667 |
|
32,60871951 |
32,24942975 |
32,54734748 |
-1,3679195 |
-1,008629755 |
-1,306547485 |
|
33,06753744 |
32,78458441 |
32,94760481 |
-1,4910374 |
-1,208084413 |
-1,371104811 |
|
33,24916168 |
32,99642674 |
33,10604769 |
-2,1126617 |
-1,85992674 |
-1,969547688 |
|
32,87428417 |
32,55917826 |
32,77901716 |
-1,6705842 |
-1,355478257 |
-1,575317163 |
|
33,00505786 |
32,71170967 |
32,89309972 |
-1,8866579 |
-1,593309665 |
-1,774699723 |
|
33,00505786 |
32,71170967 |
32,89309972 |
-1,8866579 |
-1,593309665 |
-1,774699723 |
|
33,00505786 |
32,71170967 |
32,89309972 |
-1,8866579 |
-1,593309665 |
-1,774699723 |
|
33,13466863 |
32,86288469 |
33,00616781 |
-1,8442686 |
-1,572484691 |
-1,715767807 |
Как видно из таблицы, ошибки заметно уменьшаются в зависимости от того, как уменьшается интервал. Это совсем не значит, что минимальный интервал даст наибольшую точность, однако промежуток в 3 месяца (сезон) может быть наиболее точным и рациональным в отношении прогнозирования зависимости курсов на будущее.
Конечно же, используя данные по курсам трудно сделать прогноз, если нет никаких данных. Однако, данная методика может помочь в планировании, так как по вычисленной ошибке при помощи методики Пирсона можно определить примерный коридор курса нужной валюты и, опираясь на минимум или максимум рамок этого коридора можно обезопасить себя от рисков.
Однако, эти данные не позволяют делать полноценный прогноз, так как определить один курс, не зная второго, при помощи регрессии невозможно. Однако, продолжая исследования в этой области, есть возможность найти определённые тенденции изменения ошибок по месяцам, неделям и даже дням, тем самым определить направление движения курса в ту или иную сторону.
-
РЕАЛИЗАЦИЯ ЗАДАЧ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ СРЕДСТВАМИ MICROSOFT EXCEL.
Обучение студентов использованию современных информационных технологий при решении прикладных производственных задач является актуальнейшим требованием нашего времени. Темой одного из таких специальных курсов могло бы стать линейное программирование задач из различных отраслей экономики и управления при помощи электронных таблиц Microsoft Excel.
Линейное программирование – это раздел математики, занимающийся решением таких задач на отыскание наибольших и наименьших значений, для которых методы математического анализа оказываются непригодными. Другими словами термин «линейное программирование» характеризует определение программы (плана) работы конкретного экономического объекта на основе выявления линейных связей между его элементами. Задачей линейного программирования является нахождение оптимального, т. е. наилучшего, плана при заданной системе налагаемых на решение ограничений.
К классу задач линейного программирования относится большое количество разнообразных задач планирования и управления, как, например:
-
нахождение оптимального плана выпуска продукции (оптимальное распределение ресурсов);
-
оптимизация межотраслевых потоков (планирование производства различных видов продукции по отраслям);
-
определение оптимального рациона (оптимизация состава химической смеси);
-
транспортная задача (оптимальное распределение потоков товарных поставок по транспортной сети);
-
задача о размещении производства (планирование с учетом затрат на производство и транспортировку продукции);
-
задача о назначениях (оптимальное распределение различных видов транспортных средств) и др.
В настоящее время одним из перспективных, но недостаточно распространенных способов численного решения задач линейного программирования является использование надстройки «Поиск решения» электронных таблиц Microsoft Excel. В частности, «Поиск решения» предоставляет возможность: