Решение задачи в excel
Для решения задачи составим электронную таблицу, отражающую математическую модель задачи. Электронная модель (исходное состояние), представлена в режиме вычислений - на рис. 1, в режиме показа формул - на рис. 1-а. Для наглядности в качестве начальных значений переменным присвоены значения, равные 1.
|
A |
B |
C |
D |
E |
F |
|
1 |
|
ИСХОДНЫЕ ДАННЫЕ |
|
|
|||
2 |
|
|
Недельные портебности завода |
||||
3 |
Max отгрузка маш/нед. |
Завод 1 |
Завод 2 |
Завод 3 |
Завод 4 |
||
4 |
50 |
30 |
45 |
40 |
|||
5 |
Стоимости перевозки ед груза |
||||||
6 |
Поставщик 1 |
50 |
1600 |
1400 |
1100 |
1250 |
|
7 |
Поставщик 2 |
55 |
1300 |
1350 |
1500 |
1200 |
|
8 |
Поставщик 3 |
60 |
1000 |
1250 |
900 |
1400 |
|
9 |
|
|
|
|
|
|
|
10 |
|
|
Требуется Получить |
||||
11 |
Требуется поставить |
Завод 1 |
Завод 2 |
Завод 3 |
Завод 4 |
||
12 |
50 |
30 |
45 |
40 |
|||
13 |
Поставщик 1 |
50 |
1 |
1 |
1 |
1 |
|
14 |
Поставщик 2 |
55 |
1 |
1 |
1 |
1 |
|
15 |
Поставщик 3 |
60 |
1 |
1 |
1 |
1 |
|
16 |
Стоимость перевозки |
186 750 |
|
|
|
|
|
Рис. 1
|
ИСХОДНЫЕ ДАННЫЕ |
|
|
|
|
|
|
Недельные портебности завода |
|||
Max отгрузка маш/нед. |
Завод 1 |
Завод 2 |
Завод 3 |
Завод 4 |
|
50 |
30 |
45 |
40 |
||
Стоимости перевозки ед груза |
|||||
Поставщик 1 |
50 |
1600 |
1400 |
1100 |
1250 |
Поставщик 2 |
55 |
1300 |
1350 |
1500 |
1200 |
Поставщик 3 |
60 |
1000 |
1250 |
900 |
1400 |
|
|
|
|
|
|
|
|
Требуется Получить |
|||
Требуется поставить |
Завод 1 |
Завод 2 |
Завод 3 |
Завод 4 |
|
=СУММ(C13:C15) |
=СУММ(D13:D15) |
=СУММ(E13:E15) |
=СУММ(F13:F15) |
||
Поставщик 1 |
=СУММ(C13:F13) |
1 |
1 |
1 |
1 |
Поставщик 2 |
=СУММ(C14:F14) |
1 |
1 |
1 |
1 |
Поставщик 3 |
=СУММ(C15:F15) |
1 |
1 |
1 |
1 |
Стоимость перевозки |
=СУММПРОИЗВ(C6:F8;C13:F15) |
|
|
|
|
Рис. 1-а
Для работы в диалоговом окне Поиск решения следует выполнить:
1Команду: Сервис - Поиск решения. На экране появится диалоговое окно Поиск решения.
2В поле Установить целевую ячейку ввести адрес $В$16.
Выбрать направление изменения целевой функции: установить переключатель в положение Минимальному значению.
В поле Изменяя ячейки ввести адрес блока ячеек SC$13: $F$15.
Для ввода ограничений нажать кнопку Добавить. В диалоговом окне Добавление ограничения ввести: в левое поле - левую часть ограничения $В$13:$В$15, из раскрывающегося списка выбрать знак ограничения =, в правое поле - правую часть ограничения $В$6:$В$8; щелкнуть по кнопке Добавить; в диалоговое окно Добавление ограничения аналогично ввести второе ограничение $C$12:$F$12 = $C$4:$F$4; нажать кнопку ОК. На экране появится диалоговое окно Поиск решения с введенными ограничениями (рис. 2).
Рис. 2
Щелкнуть по кнопке Параметры. Появится диалоговое окно
Рис. 3
Оставить, предлагаемые по умолчанию, Максимальное время решения задачи (100 с) и Предельное число итераций (100).
Установить флажки Линейная модель и Неотрицательные значения.
Нажать кнопку ОК. Появится диалоговое окно Поиск решения.
Щелкните по кнопке Выполнить. Появится диалоговое окно Результаты поиска решения. Решение найдено.
В окне Результаты поиска решения в поле Тип отчета выделите названия всех трех отчетов: Результаты, Устойчивость, Пределы Появятся три новых листа с именами всех отчетов.
Нажать кнопку ОК. На экране появится исходная таблица (рис. 3), где в блоке ячеек C13:F15 находятся значения искомых переменных: Ху, а в ячейке В16 минимальное значение целевой функции.
|
ИСХОДНЫЕ ДАННЫЕ |
|
|
|
|
|
|
Недельные портебности завода |
|||
Max отгрузка маш/нед. |
Завод 1 |
Завод 2 |
Завод 3 |
Завод 4 |
|
50 |
30 |
45 |
40 |
||
Стоимости перевозки ед груза |
|||||
Поставщик 1 |
50 |
1600 |
1400 |
1100 |
1250 |
Поставщик 2 |
55 |
1300 |
1350 |
1500 |
1200 |
Поставщик 3 |
60 |
1000 |
1250 |
900 |
1400 |
|
|
|
|
|
|
|
|
Требуется Получить |
|||
Требуется поставить |
Завод 1 |
Завод 2 |
Завод 3 |
Завод 4 |
|
50 |
30 |
45 |
40 |
||
Поставщик 1 |
50 |
0 |
15 |
35 |
0 |
Поставщик 2 |
55 |
0 |
15 |
0 |
40 |
Поставщик 3 |
60 |
50 |
0 |
10 |
0 |
Стоимость перевозки |
186 750 |
|
|
|
|
Рис. 4
Таким образом, минимальная стоимость перевозок равна Z =186 750 и достигается при объемах перевозок:
, , , , ,
Как видно, вычисления, выполненные при ручном счете и средствами Excel, совпадают.