Решение задач оптимизации с помощью MS Excel
Задача линейного программирования.
Для перевозки грузов используются машины типов Х и У. Грузоподъемность каждой машины 10 т. За одну ездку машина Х расходует 2 кг. смазочных материалов и 45л. топлива, машина У – 1,5 кг. смазочных материалов и 30л. топлива. На складе имеется 45 кг. смазочных материалов и 700л. топлива. Прибыль от одной ездки машины типа Х составляет 8у.е., машины У 6 у. е.. Необходимо перевезти 200 т. груза.
Сколько ездок надо сделать машинам обоих типов, чтобы доход от перевозки груза был максимальным?
Решение:
Построим вспомогательную таблицу:
|
A |
B |
C |
D |
E |
F |
|
|
1 |
КАМАЗ |
Урал |
СУММПРОИЗВ |
Тип ограничений |
Запас |
|
|
2 |
ездки |
0 |
0 |
|||
|
3 |
грузоподъемность |
10 |
10 |
0 |
= |
200 |
|
4 |
солидол |
2 |
1,5 |
0 |
=< |
45 |
|
5 |
дт |
45 |
30 |
0 |
=< |
700 |
|
6 |
прибыль |
8 |
6 |
0 |
® |
max |
В столбце СУММПРОИЗВ используется аналогичная функция. /Вставка-Функция…-Математические/.В «массив 1» вносим ездки, в «массив 2» грузоподъемность.
Например, для ячейки D3 =СУММПРОИЗВ($B$2:$C$2;B3:C3)
Войдите в меню /Сервис - Поиск решения/.
На вкладке «Параметры» отметить поля «Линейная модель» и «Неотрицательные значения». Нажать кнопку «Выполнить».
После выполнения «Поиска решения» сохранить найденное решение и выбрать все отчеты.
Необходимо самостоятельно провести анализ по отчетам.
Если все сделано правильно, то в таблице появится количество ездок. Видно, что получились дробные значения. Чтобы получить целочисленное решение, нужно добавить на строку «Ездки» ограничение целочисленности. /Сервис – Поиск решения – Добавить/
Транспортная задача
Цемент поступает на 4 ж/д узла, с каждого узла, можно перевезти n т. цемента на любую из 4 строек. В таблице представлены тарифы, руб./т, а также запасы и потребности.
|
Тарифы, руб./т. |
Стройпуть |
ДСК |
ДальСтрой |
Эра |
Запасы, т. |
|
Батуевская ветка |
1,2 |
1,6 |
1,7 |
1,5 |
40 |
|
Хабаровск-2 |
1,4 |
1,0 |
1,2 |
1,5 |
10 |
|
ст. Амур |
1,6 |
1,4 |
1,2 |
1,4 |
50 |
|
плт. Северная |
1,5 |
1,2 |
1,4 |
1,2 |
60 |
|
Потребности, т. |
40 |
20 |
40 |
60 |
Решение:
Составим вспомогательную таблицу:
|
A |
B |
C |
D |
E |
F |
|
|
10 |
Перевезено груза, т. |
Стройпуть |
ДСК |
ДальСтрой |
Эра |
Запасы, т. |
|
11 |
Батуевская ветка |
0 |
0 |
0 |
0 |
0 |
|
12 |
Хабаровск-2 |
0 |
0 |
0 |
0 |
0 |
|
13 |
ст. Амур |
0 |
0 |
0 |
0 |
0 |
|
14 |
плт. Северная |
0 |
0 |
0 |
0 |
0 |
|
15 |
Потребности, т. |
0 |
0 |
0 |
0 |
0 |
Правая нижняя ячейка вычисляется как сумма произведений тарифов (таблица условий) и перевезенного груза (вспомогательная таблица)
Строка «Потребности» и столбец «Запасы» вычисляются как суммы по соответствующим строкам и столбцам.
Система ограничений в /Сервис – поиск решеня/ примет вид
На вкладке «Параметры» отметить поля «Линейная модель» и «Неотрицательные значения». Нажать кнопку «Выполнить». Если все сделано правильно, то в таблице появится количество перевезенных тонн.
После выполнения «Поиска решения» сохранить найденное решение.
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.