Решение задачи с помощью MS Excel

Создадим таблицу (табл. 1.1), в которую запишем формулы, начальные значения и пояснения.

Таблица 1.1

Решение задачи с помощью MS Excel - student2.ru

Примечание. Чтобы увидеть формулы в ячейках электронной таблицы, нужно последовательно активизировать пункты меню Сервис – Параметры. В появившемся меню следует выбрать вкладку Вид, и в Параметрах окнапоставить флажок напротив слова формулы. Чтобы обратно переключиться в режим, показывающий значения вместо формул, необходимо этот флажок убрать.

В ячейках B1 и B2 находятся исходные (начальные) значения х1 и х2 соответственно. Эти числа могут быть практически любыми.

В ячейку B3 записана формула данной целевой функции.

В ячейки D2, D3 и D4 занесены ограничения (только левая их часть, до знака «≤»). Условия неотрицательности (х1 ≥ 0, х2 ≥ 0) в таблицу не заносятся – они будут указаны при поиске решения.

Теперь в меню Сервис выбираем пункт Поиск решения… Появляется диалоговое окно (рис. 1.2).

Решение задачи с помощью MS Excel - student2.ru

Рисунок 1.2 – Диалоговое окно “Поиск решения”

В поле Установить целевую ячейку указываем ячейку B3, содержащую формулу целевой функции. Так как необходимо максимизировать целевую функцию, то переключатель Равной следует установить в положение максимальному значению.

В поле Изменяя ячейки указываем ячейки с начальными значениями х1 и х2. В поле Ограничения заносим условия неотрицательности (х1 ≥ 0, х2 ≥ 0). Также здесь указываем ячейки с ограничениями, вписывая их значение после знака “≤”.

Нажимаем кнопку Выполнить, после чего выбираем Сохранить найденное решение.

После этого в электронной таблице (табл. 1.2) в соответствующих полях появятся координаты х1 и х2 точки оптимума и значение целевой функции.

Таблица 1.2

Решение задачи с помощью MS Excel - student2.ru

Как видим, в данном случае аналитическое решение задачи и ее решение с помощью MS Excel дали одинаковый ответ.

Решение задачи в системе MathCAD

Задаем отсчет индексов, начиная с 1

Решение задачи с помощью MS Excel - student2.ru

Указываем количество неизвестных:

Решение задачи с помощью MS Excel - student2.ru

Задаем целевую функцию

Решение задачи с помощью MS Excel - student2.ru

Назначаем произвольные начальные значения переменных:

Решение задачи с помощью MS Excel - student2.ru

Блок вычислений

Решение задачи с помощью MS Excel - student2.ru

Решение задачи с помощью MS Excel - student2.ru

Решение задачи с помощью MS Excel - student2.ru

Решение задачи с помощью MS Excel - student2.ru

Решение задачи с помощью MS Excel - student2.ru Решение задачи с помощью MS Excel - student2.ru

Решение задачи с помощью MS Excel - student2.ru

Получаем матрицу оптимальных перевозок

Решение задачи с помощью MS Excel - student2.ru

Определяем стоимость оптимальных перевозок

Решение задачи с помощью MS Excel - student2.ru

Транспортная задача

Основные понятия

Транспортные задачи являются частным случаем задач линейного программирования и формулируются следующим образом.

Имеется m предприятий-производителей (заводы, фабрики) с запасом произведенных товаров (продуктов, материалов, топлива). Кроме того, имеется n предприятий-потребителей (магазины, рынки, бензозаправочные станции), у которых есть спрос на эти товары.

Наличие товаров (запасов) на i-том предприятии-производителе описывается вектором:

Решение задачи с помощью MS Excel - student2.ru .

Потребность товаров (спрос) на j-том предприятии-потребителе описывается вектором:

Решение задачи с помощью MS Excel - student2.ru .

Стоимость перевозок товаров между i-тым предприятием-производителем и j-тым предприятием-потребителем описывается матрицей:

Решение задачи с помощью MS Excel - student2.ru .

Необходимо так организовать перевозки, чтобы их общая стоимость была минимальной, то есть необходимо определить матрицу оптимальных перевозок:

Решение задачи с помощью MS Excel - student2.ru

которая будет удовлетворять минимуму целевой функции:

Решение задачи с помощью MS Excel - student2.ru .

Условие задачи

Бетон, производимы на трех заводах, необходимо доставить на четыре строительные площадки. Запасы груза в пунктах отправления равны соответственно 50, 30 и 40 единиц. Потребности пунктов назначения состав­ляют соответственно 30, 30, 10, 20 единиц. Затраты на перевозку единицы груза от i-гo поставщика j-му потребителю известны и представлены в таблице 2.1. Требуется определить оптимальный план перевозок бетона, соответствующий минимуму стоимости перевозок.

Таблица 2.1

Пункты отправления Пункты назначения Запасы
В1 В2 В3 В4
А1
А2
А3
Потребности  

Наши рекомендации