После чего, войдем в меню Сервис-Поиск решения.

Решение

Найдем сумму запасов и сумму потребностей:

а1+а2+а3=280+220+300=800

b1+b2+b3+b4+b5=190+140+180+120+170=800

Сумма запасов равна сумме потребностей, то есть все запасы должны

быть вывезены, и все потребности удовлетворены.

Обозначим xi,j – количество груза перевозимого от Ai к Bj и составим ма-

тематическую модель задачи.

Целевая функция – общие затраты на перевозки

Z=7X₁₁+ 3X₁₂+ 9X₁₃+15X₁₄+35X₁₅+

3X₂₁+10X₂₂+12X₂₃+20X₂₄+46X₂₅+

15X₃₁+11X₃₂+16X₃₃+19X₃₄+48X₃₅ стремиться к min

Ограничения:

X₁₁+X₁₂+X₁₃+X₁₄+X₁₅=280

X₂₁+X₂₂+X₂₃+X₂₄+X₂₅=220

X₃₁+X₃₂+X₃₃+X₃₄+X₃₅₌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 единиц.

4. Использование пакета «Анализ данных» системы Excel для решения экономических задач прогнозирования.

Задача. В таблице приведены данные о курсе ЕВРО Х₁, фондовом индексе Х₂ и котировке акций Y за последние 10 дней. Требуется с помощью линейной регрессии спрогнозировать котировку акций, если курс ЕВРО составит 32 руб. , а значение фондового индекса окажется равным 5.

Замечание. Уравнение регрессии имеет вид : Y=a₀+a₁X₁+a₂X₂

Найти а₀, а₁, а₂, использовав команду регрессия из пакета анализ данныхэлектронных таблицEXCEL.Найти также интервальные оценки параметров а₁, а₂ и показать значимость уравнения регрессии.

Решение:

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.

Не нашли, что искали? Воспользуйтесь поиском по сайту: