Для определения стоимости перевозок в 1-й магазин
Z 1 = X11 C11 + X12 C12 + X13 C13
введем в ячейку С16 формулу:
=СУММПРОИЗВ(С5:С7;С11:С13).
(В OpenOffice.org Calc формула =SUMPRODUCT(C5:C7;C11:C13)).
В ячейке D16 вычислим стоимость перевозок во второй магазин
Z 2 = X12 C12+ X22 C22+ X32 C32, т.е. введем формулу.
=СУММПРОИЗВ(D5:D7;D11:D13) (=SUMPRODUCT(D5:D7;D11:D13)).
Аналогично в ячейки Е16:G16 введем формулы для вычисления стоимости перевозок в остальные магазины
В ячейку Е16 =СУММПРОИЗВ(Е5:Е7;Е11:Е13),
(=SUMPRODUCT(Е5:Е7;Е11:Е13)).
В ячейку F16 =СУММПРОИЗВ(F5:F7;F11:F13),
(=SUMPRODUCT(F5:F7;F11:F13)).
В ячейку G16 =СУММПРОИЗВ(G5:G7;G11:G13),
=SUMPRODUCT(G5:G7;G11:G13)).
5. Определим общую стоимость перевозок (целевую функцию ЦФ)
Z = Z1 + Z2 + Z3 + Z4 + Z5
Для этого введем в ячейку В17 формулу
=СУММ(С16:G16),
(=SUM(C16:G16)).
Для нашего начального плана полученная стоимость равна 44 денежным единицам.
Таблица 29
| A | B | C | D | E | F | G | |
| ОПТИМИЗАЦИЯ ПЛАНА ПЕРЕВОЗОК | |||||||
| Склады | Магазины | ||||||
| 1-й | 2-й | 3-й | 4-й | 5-й | |||
| Номер | Запас | Стоимость перевозок | |||||
| Потребности магазинов | |||||||
| Всего ввозится | План перевозок | ||||||
| Завоз в магазины | |||||||
| Стоимость перевозок в каждый магазин | |||||||
| Целевая функция |
| |||||||||
 | |||||||||
  | |||||||||
|
 | |||||
 | |||||
3.2.2.3. Улучшение (оптимизация) плана перевозок
I. Используем режим Поиск решения Excel Microsoft office.
1) После выполнения команд Сервис, Поиск решения открывается диалоговое окно Поиск решения (рис. 4).
(При работе с Microsoft Office 2007 следует выполнить команды Данные – Анализ – Поиск решения).
2) Введем данные:
Установить целевую ячейку В17
Равной: минимальному значению
изменяя ячейки C11:G13
3) Щелкнуть по кнопке Добавить для ввода ограничений.
4) В открывшемся окне Изменение ограничения (рис. 5) ввести ограничения.
C11:G13 >= 0
С11: G13 = целые
В11:В13 £ B5:D7
С15:G15 ³ C9:G9
 |
(левая часть каждого ограничения вводится в поле Ссылка на ячейку, правая часть ограничения – в поле Ограничение, знак выбирается в средней части окна. После ввода каждого ограничения нужно нажать кнопку Добавить, после ввода последнего ограничения нажать кнопку Ок.)
Рис. 4
5) Для запуска режима Поиск решения щелкнуть по кнопке Выполнить (см. рис. 4). Если все формулы в табл. 29 и все ограничения в окнах рис. 4 и рис. 5 введены правильно, появится окно с сообщением о том, что решение найдено. Щелкнув по кнопке Ок, получаем решение (табл. 32).
Рис. 5
II. При работе с режимом Поиск решения OpenOffice.org Calc
1) После выполнения команд Сервис, Поиск решения открывается диалоговое окно Поиск решения (рис. 6).
2) Введем данные:
Целевая функция В17
Цель: минимум
Параметры функции C11:G13
3) Щелкнуть по кнопке Добавить для ввода ограничений.
4) В открывшемся окне Ограничения значение параметров (рис. 7). ввести:
C11:G13 >= 0,
В11:В13 £ B5:D7,
С15:G15 ³ C9:G9.
После ввода каждого ограничения щелкнуть по Ок.
Рис. 6
Рис. 7
5) Щелкните по кнопке Настройки в окне рис. 6, установите флажок R Только положительные значения (рис. 8), затем Ok.
Рис. 8
В результате улучшения плана теперь мы имеем оптимальный план стоимости перевозок с целевой функцией, равной 121 денежной единице (табл. 32).
Таблица 32