Решение транспортной задачи в Excel

Размещено на http://www.allbest.ru/

Федеральное государственное образовательное бюджетное учреждение

высшего профессионального образования

«Финансовый университет при Правительстве Российской Федерации»

(Финуниверситет)

Смоленский филиал Финуниверситета

Факультет финансово-кредитный

Направление бакалавр экономики

Лабораторная работа

по экономико-математические методы и прикладные модели

Вариант 2

Смоленск 2012

Содержание

Задача 1. Мэрия планирует перекрывать участки улиц: улицы Светлой в течение 3 дней, улицы Вишневой в течение 5 дней, улицы Институтской в течение 4 дней -- для проведения ремонтных работ. Если бы дорожная бригада ремонтировала улицу Светлую в течение 3 дней, улицу Вишневую в течение 2 дней, улицу Институтскую в течение 4 дней, то общая длина отремонтированного дорожного полотна не превысила бы 15 км. Если же ремонтировать улицу Светлую в течение 2 дней, улицу Вишневую в течение 3 дней, то общее количество отремонтированного дорожного полотна не превысит 8 км. Поскольку на Вишневой улице расположены социальные объекты, то необходимо за один день отремонтировать на этой улице более 2 км пути. Сколько км дорог в день необходимо ремонтировать бригаде на каждой улице, чтобы общая протяженность отремонтированных дорог была наибольшей (тогда бригада получит премию)?

Задача 2. Летом с четырех авиационных баз осуществляется завозка продовольствия в три северных поселка. В силу различия в типах самолетов и высоте полета, а также сложности трассы вес продовольствия, доставляемого с любой базы к любому из трех поселков за один вылет, определяется по следующей таблице:

С каждой базы за сезон производится 150 самолетовылетов, в каждый поселок необходимо организовать 200 самолетовылетов.

Определите план вылетов с каждой базы к каждому из поселков, дающий максимальный общий вес продовольствия.

Задача 1.

Мэрия планирует перекрывать участки улиц: улицы Светлой в течение 3 дней, улицы Вишневой в течение 5 дней, улицы Институтской в течение 4 дней -- для проведения ремонтных работ. Если бы дорожная бригада ремонтировала улицу Светлую в течение 3 дней, улицу Вишневую в течение 2 дней, улицу Институтскую в течение 4 дней, то общая длина отремонтированного дорожного полотна не превысила бы 15 км. Если же ремонтировать улицу Светлую в течение 2 дней, улицу Вишневую в течение 3 дней, то общее количество отремонтированного дорожного полотна не превысит 8 км. Поскольку на Вишневой улице расположены социальные объекты, то необходимо за один день отремонтировать на этой улице более 2 км пути.

Сколько км дорог в день необходимо ремонтировать бригаде на каждой улице, чтобы общая протяженность отремонтированных дорог была наибольшей (тогда бригада получит премию)?

Решение

Экономико-математические модель:

Обозначаем через Х1, Х2, Х3 количество км за 1 день проведения ремонтных работ.

Целевая функция - это математическая запись критерия оптимальности, т. е. выражение, которое необходимо максимизировать

f(x) = 3Х1+5Х2+4 Х3 max

Ограничения по ресурсам:

3х1 + 2х2 + 4х3 <= 15

2x1 + 3x2 + 4x3 <= 8

x2 <= 2

Приведем подробное описание технологии получения решения приведенной ЗЛП.

Создаем форму для ввода условия задачи. Запустим Excel. Создаем текстовую форму - таблицу для ввода условий задачи (рис. 1.1).

Рис. 1.1

Введем исходные данные задачи в созданную форму - таблицу, представленную на рис. 1.2

Рис. 1.2

Введем зависимость для целевой функции:

· Курсор в ячейку F4.

· Курсор на кнопку «Мастер функций», расположенную на панели инструментов (Рис. 1.3).



Рис. 1.3

· Курсор в окно «Категория» на категорию Математические;

· Курсор в окно Функции на СУММПРОИЗ;

· В строку «Массив 1» введем $B$3:$D$3;

· В строку «Массив 2» введем B4:D4;

· Кнопка «ОК» на экране: в ячейку F4 введена функция (рис. 1.4).

Рис. 1.4

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

· Курсор в ячейку E4;

· На панели инструментов кнопка Копировать в буфер;

· Курсор в ячейку E7;

· На панели инструментов кнопка Вставить из буфера;

· Курсор в ячейку E8;

· На панели инструментов кнопка Вставить из буфера;

· Курсор в ячейку E9;

· На панели инструментов кнопка Вставить из буфера;



Рис. 1.5

В строке Меню указатель мыши на имя Сервис. В развернутом меню команда «Поиск решения». Появляется диалоговое окно Поиск решения (рис. 1.6).

Назначим целевую функцию (установим целевую ячейку):

· Курсор в строку Установить целевую ячейку;

· Введем адрес ячейки $Е$4;

· Введем направление целевой функции - Максимальному значению;

· Курсор в строку Изменяя ячейки;

· Введем адреса искомых переменных B$3:D$3.

Рис. 1.6

Введем ограничения:

· Указатель мышки на кнопку Добавить. Появляется диалоговое окно Добавление ограничения (рис. 1.7);

· В строке Ссылка на ячейку введем адрес $Е$7;

· Введем знак ограничения <=;

· В строке Ограничения введем адрес $G$7;

· Указатель мыши на кнопку Добавить. На экране вновь диалоговое окно Добавление ограничения (Рис. 1.7).

· Введем остальные ограничения задачи, по вышеописанному алгоритму;

· После введения последнего ограничения кнопка ОК.

Рис. 1.7

На экране появится диалоговое окно Поиск решения с введенными условиями.

Введем параметры для решения ЗЛП:

· В диалоговом окне указатель мыши на кнопку Параметры. На экране появляется диалоговое окно Параметры поиска решения (Рис. 1.8);

Рис. 1.8

· Установим флажки в окнах Линейная модель (это обеспечит применение симплекс-метода) и Неотрицательные значения;

· Указатель мыши на кнопку ОК. на экране диалоговое окно Поиск решения;

· Указатель мыши на кнопку Выполнить.

Результаты поиска решения и исходная таблица с заполненными ячейками В3:Е3 для значений Хi и ячейка Е4 c максимальным значением целевой функции (Рис. 1.9).

Рис. 1.9

Полученное решение означает, что максимально отремонтированных дорог 13 км мэрия может получить при проведении ремонтных работ первого варианта в течение 1 дня и второго варианта в течение 2 дней. При этом будет отремонтировано 7 км из 15 км варианта 1, и полностью отремонтирован вариант 2, а вариант 3 ремонтировать не целесообразно.

Задача 2.

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



С каждой базы за сезон производится 150 самолетовылетов, в каждый поселок необходимо организовать 200 самолетовылетов.

Определите план вылетов с каждой базы к каждому из поселков, дающий максимальный общий вес продовольствия.

Экономико-математическая модель:

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

Решение

Ввод условий задачи состоит из следующих основных этапов.

1. Создание матрицы вылетов (Рис. 2.1)

Рис. 2.1

2. Ввод граничных условий.

excel транспортный модель

где ai - количество i-го самолетовылетов;

xij - количество самолетовылетов от i-го самолетовылетов производимых к j-му поселку;

n - количество самолетовылетов.

Для этого необходимо выполнить следующие операции:

- курсор в ячейку А3;

- щелкнуть знак «?»;

- выделить необходимые для суммирования ячейки В3D3;

- нажать ENTER - подтверждение ввода формулы для суммирования.

Аналогичные действия выполнить для ячеек А4,А5,А6, т.е. ввести условия самолетовылетов всех поселков (для всех строк).

2.Ввод исходных данных.

3.Назначение целевой функции.

4.Ввод ограничений.

Введение условия необходимых организовать самолетовылетов,

где b - количество организованных самолетовылетов j-го поселка;

m- количество поселков.

Для этого необходимо выполнить следующие операции:

- курсор в В7;

- щелкнуть знак «?». При этом автоматически выделяется весь столбец В3В6;

- нажать ENTER - подтверждение ввода формулы для суммирования.

Аналогичные действия выполнить для ячеек С7-D7.

Таким образом, введены ограничения для всех количеств производимых самолетовылетов в каждый поселок и необходимых количеств для организации самолетовылетов

3. Ввод исходных данных (Рис. 2.2)

В конкретном примере осуществляется ввод самолетовылетов для трех поселков (ячейки А11:А14), потребности самолетовылетов с каждой базы к каждому из поселков (В10:D10), а так же дающий максимальный общий вес продовольствия с каждой базы к каждому из поселков (блок В11:D14).

Рис. 2.2

4. Назначение целевой функции (Рис 2.3).

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

где Cij - количество доставки грузов от i-го производимых самолетовылетов к j-му для организации самолетовылетов;

xij - количество поселков от i-го производимых самолетовылетов и j-му для организации самолетовылетов;

Для этого:

- курсор в ячейку В15, в данную ячейку будет помещаться значение целевой функции после решения задачи;

- щелкнуть Матер функций;

- в окне Категория выбрать Математические;

- в окне Функция при помощи спинера выбрать СУММПРОИЗВ;

- ОК;

- в окне СУММПРОИЗВ указать адреса массивов. Элементы которых обрабатываются этой функцией.

Рис. 2.3

В задаче целевая функция представляет собой произведение количество самолетовылетов дающий максимальный общий вес продовольствия (расположенных в блоке В11:D14) и организованных самолетовылетов для каждого из поселков (содержимое ячеек В3:D6) Для этого:

- в поле МАССИВ1 указать адреса В11:D14;

- в поле МАССИВ2 указать адреса В3:D6;

- ОК- подтверждение окончания ввода адресов массивов.

В поле ячейка В15 появится некоторое числовое значение, равное произведению единичных самолетовылетов на удельное коэффициенты продовольствия для каждого поселка (число 76 в данной задаче, рис. 2.5).

5. Ввод зависимостей из математической модели.(Рис. 2.5)

- щелкнуть Сервис - Поиск решения;

- курсор подвести в поле Установить целевую (ячейку);

- ввести адрес $B$15. Таким образом производится указание ячейки, куда при решении задачи помещается значение целевой функции;

- установить направление изменения целевой функции, равное «максимальному значению»;

- ввести адреса изменяемых ячеек В3:D6. Для этого:

- щелкнуть в поле изменяя ячейки;

- ввести адреса $B$3:$D$6.

Ввести ограничения задачи. В матрицу самолетовылетов, содержащую исходные данные по задаче, необходимо ввести условие организации самолетовылетов для всех поселков. Для этого:

- щелкнуть Добавить ограничения;

- в поле ссылка на ячейку ввести адреса $A$3:$A$6;

- в среднем поле установить знак «=»;

- в поле ограничение установить адреса $A$11:$A$14.

- щелкнуть ОК.

Далее вводится ограничение, которое реализует условие удовлетворения самолетовылетов для всех поселков. Для этого:

- щелкнуть Добавить ограничения;

- в поле ссылка на ячейку ввести адреса $В$7:$D$7;

- в среднем поле установить знак «=»;

- в поле ограничение установить адреса $B$10:$D$10.

- щелкнуть ОК.



Рис. 2.5

6. Ввод ограничений.(Рис. 2.6)

Далее необходимо установить ограничения на решение задачи. Для этого:

- щелкнуть Параметры;

- установить Линейная модель;

- установить неотрицательные значения, так как количество самолетовылетов отрицательной величиной быть не могут;

- ОК;

- нажать Выполнить.

Рис. 2.6

В матрице самолетовылетов содержится оптимальный план вылетов с каждой базы к каждому из поселков, дающий максимальный общий вес продовольствия. Значение целевой функции содержится в ячейке В15 и для конкретной задачи равно 4250 (Рис. 2.7)



Рис. 2.7

Из вышеизложенного можно сделать следующий вывод:

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

- от первой базы к поселку Алмазный в количестве 100 вылетов и к поселку к Зимний в количестве 50 вылетов;

- от второй базы к поселку Зимний в количестве 150 вылетов;

- от третьей базы к поселку Снежный в количестве 150 вылетов;

- от четвертой базы к поселку Алмазный в количестве 100 вылетов и к поселку Снежный в количестве 50 вылетов.

При данной схеме самолетовылетов от то всех баз будет организован максимальный общий вес продовольствия.

Размещено на Allbest.ru