II этап: Решение задачи на ЭВМ средствами пакета Excel

1. Формируем диапазон ячеек с исходными данными А1:F7 (см. рис. 22).

2. Определяем ячейки, в которых будут содержаться переменные (неизвестные) задачи B11:E13.

Примечание: Диапазон неизвестных удобно оформить в виде таблицы.

Присваиваем неизвестным произвольные значения, например, предполагаем, что от каждого завода к каждому объекту необходимо перевезти 1 усл. ед. кирпича (см. рис. 22).

Рис.22 Оформление задачи в Excel

3. Вводим формулу в ячейку, где будет находиться значение целевой функции С17 (см. рис. 23).

Рис.23 Ввод целевой функции

Примечание: Для удобства ввода рекомендуется воспользоваться функцией СУММПРОИЗВ() (Перемножает соответствующие элементы заданных массивов и возвращает сумму произведений.).

Аргументами функции являются массив со стоимостями перевозок В4:Е6 и массив с неизвестными В11:Е13.

4. Вводим зависимости для ограничений (см. рис.24).

Рис.24 Ввод ограничений

Примечание: В ячейках F11:F13 и B14:E14 занесены левые части ограничений, правые части ограничений содержаться в ячейках F4:F6 и B7:E7.

Примечание: Ограничения 20 и 21 будут учтены в окне Поиск решения.

5. Для получения численного решения задачи используем инструмент Поиск решения (Сервис/Поиск решения).

Рис.25 Поиск решения

· В окне Установить целевую ячейку указываем адрес ячейки с целевой функцией С17.

· В разделе Равной указать Минимальному значению.

Примечание: Для заполнения окна Установить целевую ячейку необходимо на листе выделить ячейку, в которой содержится значение целевой функции.

· В окно Изменяя ячейки вносим адреса ячеек с неизвестными задачи B11:E13.

Примечание: Для ввода неизвестных можно нажать кнопку Предположить.

· Для ввода ограничений необходимо перейти в поле Ограничения и нажать кнопку Добавить. В появившемся диалоговом окне Добавление ограничения, последовательно, для каждого неравенства в разделе Ссылка на ячейку указать адрес ячейки, соответствующей левой части ограничения, а в разделе Ограничения – адрес правой части ограничения.

Рис. 26 Ввод ограничения 1

Примечание: Ограничения 14-19 вводятся аналогично

Рис. 6 Ввод ограничения 8

Рис. 7 Ввод ограничения 9

· Нажать кнопку Выполнить.

Ответ

В результате решения задачи был получен следующий ответ:

x11=0 (с первого завода на первый объект кирпичи не поставляются),

x12=0 (с первого завода на второй объект кирпичи не поставляются),

x13=60 (с первого завода на третий объект необходимо поставить 60 усл ед. кирпичей),

…

X34=50 (с третьего завода на четвертый объект необходимо поставить 50 усл. ед. кирпичей),

.

При этом минимальная стоимость перевозки будет 660 руб., .

Рис. 29 результат решения задачи

Поиск по сайту: