Анализ данных системы Excel для решения экономических задач прогнозирования.html
Работа добавлена на сайт samzan.net:
Содержание.
1.1 Решение систем линейных уравнений с помощью обратной матрицы 2
1.2. Решение систем линейных уравнений методом Крамера 2
2. Решение экономических задач оптимизации в Поиске решения. 4
3. Транспортная задача. 6
4. Использование пакета «Анализ данных» системы Excel для решения экономических задач прогнозирования. 9
- Решение систем линейных уравнений с помощью обратной матрицы
Для применения систему необходимо записать в матричной форме; Ах=B. Здесь А-матрица коэффициентов, х - вектор неизвестных, а B - вектор правой части уравнений. Для решения этого матричного уравнения обе его части умножаются на матрицу, обратную к А: А-1Ах=А-1B. По определению, произведение матрицы на обратную к ней дает единичную матрицу, а произведение единичной матрицы на любой вектор равно этому же вектору, поэтому предыдущее уравнение преобразуется к следующему виду:
х=А-1 B.
Это и есть решение системы уравнений. Рассмотрим следующую систему линейных уравнений:
5х1+3x2+x3-x4=37
5x1+18x2+0x3+2x4=122
6x1+2x2+5x3+x4=64
2x1+x2+3x3+0x4=28
Запишем систему в матричном виде
Выделим ячейки K8 : N11, Вставка – функция – МОБР, введем следующее: = МОБР(B2 :E5) и нажмем вместе клавиши F2, <Ctrl+Shift+Enter> для вставки этой формулы во все выбранные ячейки
Следующим действием перемножим матрицы A-1 и В, для чего выделим ячейки Q8:Q11, Вставка – функция - МУМНОЖ, введем следующее: = МУМНОЖ(K8:N11;H2:H5) и нажмем вместе клавиши F2, <Ctrl+Shift+Enter> для вставки этой формулы во все выбранные ячейки
Получим результат Х1=4,23, Х2=5,12, Х3=4,80, Х4=4,34
- Решение систем линейных уравнений методом Крамера
Согласно правилу Крамера xi = Di / D (i=1,2,3,4), где D – определитель исходной матрицы А Di - определитель матрицы, полученной из матрицы заменой i-го столбца на столбец свободных членов. Для решения системы в ЕХСЕL проделаем следующее.
- Найдем определитель матрицы Da, для чего в ячейке G9, Вставка – функция –МОПРЕД, введем =МОПРЕД(B8:E11) , Enter.
Далее сформируем матрицу Dх1, заменив для этого в исходной матрице А первый столбец на вектор правой части – вектор В.
- После чего, как и в 1-ом действии вычислим определитель матрицы Dx1
- Выполняя шаги 2-3, сформируем матрицы Dх2, Dх3, Dх4 и вычислим определители Dx2, Dx3 и Dx4.
Для нахождения х выполним следующее:
5. В ячейку B34 введем формулу =G14/G9.
Для нахождения значений х2, х3 и х4 в ячейки B35, B36 и B37 введем соответствующие формулы.
Выполним проверку А*х=В
- Решение экономических задач оптимизации в Поиске решения.
Решить задачу линейного программирования, используя модуль Поиск решения электронных таблиц EXCEL.
Вариант №10.
z= 7x1 +9x2→max
x1≥0; x2≥0
Решение:
- Введем текст в ячейки А1 и А2:
А1 =«Х1=»
А2 =«Х2=»
- Запишем уравнения в виде формул
А4 «=10*Х1+9*Х2» в соседней ячейке В4 введем 1870
А5 «=5*Х1+11*Х2» в соседней ячейке В5 введем 1455
А6 «=4*Х1+15*Х2» в соседней ячейке В6 введем 1815
- Введем в ячейку А9 текст «Z=» и в ячейку В9 формулу «=7*В1+9*В2»
Получим следующий результат:
- Далее воспользуемся функцией Поиск решения, указав, что целевая ячейка В9 стремится к максимальному значению и введя ограничения (условия равенства).
- В итоге получаем следующий результат:
Вывод:
При заданных задачей условиях Z будет максимальна (1525), при Х1=115 и Х2=80
- Транспортная задача.
Имеются три пункта поставки однородного груза А1, А2, А3 и пять пунктов В1, В2, В3, В4, В5 потребления этого груза. На пунктах А1, А2 и А3 находится груз соответственно в количестве а1, а2 и а3 тонн. В пункты В1, В2, В3, В4, В5 требуется доставить соответственно b1, b2, b3, b4 и b5 тонн груза. Расстояние между пунктами поставки и пунктами потребления приведено в следующей матрице- таблице:
|
Пункты поставки |
Пункты потребления |
||||
|
В1 |
В2 |
В3 |
В4 |
В5 |
|
|
А1 |
d11 |
d12 |
d13 |
d14 |
d15 |
|
А2 |
d21 |
d22 |
d23 |
d24 |
d25 |
|
А3 |
d31 |
d32 |
d33 |
d34 |
d35 |
Найти план закрепления потребителей за поставщиками однородного груза, чтобы общие затраты по перевозкам были минимальными. Составить математическую модель задачи и решить ее, используя поиск решения таблиц EXCEL
а1=280
а2=220
а3=300
b1 =190; b2 =140; b3 =180; b4 =120; b5=170
D =
Решение
Найдем сумму запасов и сумму потребностей:
а1+а2+а3=280+220+300=800
b1+b2+b3+b4+b5=190+140+180+120+170=800
Сумма запасов равна сумме потребностей, то есть все запасы должны
быть вывезены, и все потребности удовлетворены.
Обозначим xi,j – количество груза перевозимого от Ai к Bj и составим ма-
тематическую модель задачи.
Целевая функция – общие затраты на перевозки
Z=7X11+ 3X12+ 9X13+15X14+35X15+
3X21+10X22+12X23+20X24+46X25+
15X31+11X32+16X33+19X34+48X35 стремиться к min
Ограничения:
X11+X12+X13+X14+X15=280
X21+X22+X23+X24+X25=220
X31+X32+X33+X34+X35=300
X11+X21+X31=190
X12+X22+X32=140
X13+X23+X33=180
X14+X24+X34=120
X15+X25+X35=170
Xij>=0,i=1..3,j=1..5
Найдем неотрицательные значения xij, удовлетворяющие системе ограни-
чений и минимизирующие функцию z – затраты на перевозки.
Подготовим исходные данные на листе Exel:
В ячейку A1 ввести текст «Транспортная задача».
В ячейку B2 текст «Потребители».
В ячейки B3 по F3 названия потребителей.
В ячейку A4 ввести текст «Поставщики»
В ячейку G4 текст «Запасы».
В ячейки A5 по A7 названия поставщиков.
В ячейку A8 текст «Потребности».
Значения запасов ввести в блок ячеек G5 : G7.
Значения потребностей ввести в блок ячеек B8 : F8.
Стоимости перевозки единицы груза от Ai к Bj ввести в блок ячеек B5 : F7.
Для плана перевозок отведем блок ячеек B12 : F14.
В ячейку A9 ввести текст «Доставлено».
В ячейку H4 ввести текст «Вывезено».
В ячейку B10 ввести текст «План перевозок».
В ячейки B11 по F11 названия потребителей.
В ячейки A12 по A14 названия поставщиков.
В B15 ввести текст «Затраты на перевозки»
Введем формулы:
В ячейку B16 вставим функцию Суммпроизв.
В окне этой функции указать первый массив B5 : F7, второй массив B12 : F14.
В ячейки H5, H6, H7 ввести функции:
в H5 – функцию СУММ(B12 : F12),
в H6 – функцию СУММ(B13 : F13),
в H7 – функцию СУММ(B14 : F14).
В ячейки B9, C9, D9, E9, F9 ввести функции:
в B9 – функцию СУММ(B12 : B14),
в C9 – функцию СУММ(C12 : C14),
в D9 – функцию СУММ(D12 : D14),
в E9 – функцию СУММ(E12 : E14),
в F9 – функцию СУММ(F12 : F14).
После чего, войдем в меню Сервис-Поиск решения.
(Данная контрольная частично выполнениа с использованием OpenOffice поэтому названия и внешний вид могут отличаться)
В окне Поиска решения(Решатель) установим целевую ячейку B16, переключатель установим на min
В поле Изменяя ячейки укажем мышкой блок B12 : F14.
Далее введем ограничения. Щелкнуть по кнопке Добавить.
В окне ограничений введем одно за другим 9 ограничений:
первое ограничение: H5=G5; второе ограничение: H6=G6;
третье ограничение: H7=G7; четвертое ограничение: B9=B8;
пятое ограничение: C9=C8; шестое ограничение: D9=D8;
седьмое ограничение: E9=E8; восьмое ограничение: F9=F8;
девятое ограничение: B12 : F14 ≥ 0
Далее проверяем правильность ввода ограничений и нажимаем «Выполнить»(Решить).
В результате получим оптимальный план перевозок и значение функции минимальных затрат: Zmin=12220 единиц.
- Использование пакета «Анализ данных» системы Excel для решения экономических задач прогнозирования.
Задача. В таблице приведены данные о курсе ЕВРО Х1, фондовом индексе Х2 и котировке акций Y за последние 10 дней. Требуется с помощью линейной регрессии спрогнозировать котировку акций, если курс ЕВРО составит 32 руб. , а значение фондового индекса окажется равным 5.
Замечание. Уравнение регрессии имеет вид : Y=a0+a1X1+a2X2
Найти а0, а1, а2, использовав команду регрессия из пакета анализ данных электронных таблиц EXCEL. Найти также интервальные оценки параметров а1, а2 и показать значимость уравнения регрессии.
|
10 вариант |
X1 |
23,2 |
25,4 |
26,2 |
28,1 |
29,2 |
29,8 |
30,1 |
31,2 |
31,4 |
32,1 |
|
X2 |
4,5 |
4,8 |
4,8 |
4,9 |
4,9 |
5,0 |
5,1 |
5,2 |
5,2 |
5,1 |
|
|
Y |
45,4 |
45,8 |
45,9 |
46,1 |
46,8 |
46,9 |
47,2 |
47,3 |
48,1 |
48,5 |
Решение:
1. Присваиваем листу имя Анализ данных.
2. В ячейке А1 введем текст «Курс ЕВРО». В ячейках А3 : А12 введем
данные Х1.
3. В ячейку В1 введем текст «Фондовый индекс». В ячейках В3 : В12 введем данные Х2.
4.В ячейку С1 введем текст «Котировки акций». В ячейках С3 : С12 введем данные Y
5.Войти в меню – Сервис – Анализ данных – регрессия.
5. Войти в меню – Сервис – Анализ данных – регрессия.
6. В поле Входной интервал Y введем диапазон $C$3 : $C$12.
7. В поле Входной интервал Х введем диапазон $A$3 : $B$12.
8. Поставим флажок на поле Уровень надежности (95%) , укажем в поле выходной интервал ячейку A13.
9. Щелкнем на кнопке ОК и получим таблицу Вывод итогов.
Результаты выведены, начиная с ячейки А13. Поясним смысл регрессионной статистики. Здесь множественный R – это коэффициент корреляции.
R = 0,976. Он показывает, что связь между Y и факторами X1, X2 высокая, то
есть степень зависимости котировки акций Y от двух факторов курса доллара и
фондового индекса достаточно велика. R-квадрат или R2 – коэффициент детерминации. R2 = 0,96. Это означает, что 96% изменения котировки акций связано
с линейным влиянием курса доллара и фондового индекса.
Вторая таблица в выводе итогов – это дисперсионный анализ. В этой таблице рассмотрим F и значимость F. Выдвигается гипотеза H0, состоящая в том,
что уравнение регрессии не значимо. Уровень значимости α – вероятность совершить ошибку при принятии гипотезы. Примем α = 0,05.
Если значимость F > α, то гипотезу H0 принимают, и уравнение регрессии
будет не значимо. В противном случае H0 отбрасывается, и уравнение регрессии будет значимо. В примере значимость F = 0,0000214 < 0,05. Поэтому уравнение регрессии будет значимо.
В третьей таблице получим оценки коэффициентов уравнения регрессии.
В столбце коэффициентов получим a0 = 14,216; a1 = 0,308; a2 = 5,430. Окончательно, уравнение регрессии примет вид: 14,216 + 0,308*x1 + 5.430*x2.
Во второй и в третьей строках этой таблицы приведены 95% -ые интервальные оценки параметров a1 и a2 : -0,820 < a1 <1,436 и 1,139< a2 <9,720. В интервале для а1 содержится нуль, поэтому а1 не значим. В интервале для а2 нуль не содержится, а2 будет значим. По полученным результатам сделаем прогноз.
Продолжим последовательность действий.
10. В ячейку А33 введем текст «уравнение регрессии:
Y = a0 + a1X1 + a2X2».
11. В ячейку А36 введем текст «Y = 14,162 + 0,417*X1 + -1,276*X2».
12. В ячейку А37 введем текст «прогнозируемый курс ЕВРО Х1».
13. В ячейку А38 введем = 32.
14. В ячейку D37 введем текст «Прогнозируемое значение фондового индекса Х2».
15. В ячейку D38 введем = 5.
16. В ячейку А40 введем текст «Прогноз котировки акций».
17. В ячейке А41 произведем расчет. Для этого введем в ячейку А41 формулу
= В29 + В30*А38 + В31*D38 и нажмем клавишу <Enter>.
Вывод: Прогноз котировки акций на 10-й день будет равен 48,125.
8
2. Планирование сбытовой политики предприятия
3. Пояснительная записка к курсовой работе по дисциплине
4. устаревшие. В большинстве ТНВД всех типов автоматические нагнетательные клапаны разъединяющие линию вы
5. Средние века был употреблен итальянскими гуманистами для обозначения периода между классической древност
6. 2014 29012014 15
7. Гласнет FREEnet причем конечным пользователям предоставлялись в основном лишь услуги электронной почты
8. Организационно-техническое обеспечение логистической системы
9. 022014 ~ 13022014 Стоимость программы- 1360 1 день
10. это законодательство Ликурга.
11. Московский транспорт в дометрополитеновскую эру и строительство первой очереди
12. Реферат- Наука как часть культуры
13. История болезни - Хронический мезоэпитимпанит
14. Определяем предел пропорциональности
15. Управленческие решения в коммуникационном процессе
16. Влияние влажнотепловой обработки на изменение линейных размеров При изготовлении одежды и особенно в пе
17. Рисунок семьи Благодаря работам С
18. Лабораторная работа По теме- Фазовая зонная пластинка Выполнил- Файрушин Равил гр 911п.
19. тема входящая в состав тканей подчиненная высшим регуляторным системам целостного организма
20. Общая экономическая теория Интерес владельца монопол