Решение задач оптимального программирования с использованием Мicrosoft Еxcel

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ

ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ

ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ

ВЫСШЕГО ОБРАЗОВАНИЯ

«БРАТСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ»

ФАКУЛЬТЕТ ЭКОНОМИКИ И УПРАВЛЕНИЯ

Кафедра «Государственное и муниципальное управление»

Профиль «Государственное и муниципальное управление»

Отчет по лабораторной работе №3

по дисциплине

«Экономико-математические методы»

Решение задач оптимального программирования

с использованием Мicrosoft Еxcel

(вариант 3)

Работу выполнил

студент гр. ГМУ-14 _______________ А.В. Шестаков

(подпись)

Поверил

доцент кафедры ГиМУ, к.э.н. _______________ С.В. Либеровская

(подпись)

Братск 2016

Содержание

2. Решение двухиндексных задач линейного программирования с использованием Microsoft Exсel 6

Задание 12

Кондитерская фабрика в Покрове освоила выпуск новых видов шоколада «Лунная начинка» и «Малиновый дождь», спрос на которые составляет соответственно не более 12 тонн и 7,7 тонны в месяц. По причине занятости трех цехов выпуском традиционных видов шоколада, каждый цех может выделить только ограниченный ресурс времени в месяц. В силу специфики оборудования затраты времени на производство шоколада разные и представлены в таблице.

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

L(X) = 8000x1 + 6000x2 → max

При ограничениях

x1 + 7x2 ≤ 56,

2x1 + 3x2 ≤ 35,

3x1 + 2x2 ≤ 40,

x1 ≤ 12, x2 ≤ 7,7.

На первом этапе в графическом редакторе Microsoft Exel необходимо разработать экранную форму и ввести исходные данные. Разработка экранной формы заключается в создании конкретных ячеек, которые соответствуют переменным, целевой функции (ЦФ), ограничений.

Ввод исходных данных включает в себя:

Согласно заданию, значение ЦФ определяется выражением: 8000x1 + 6000x2. Значит, формула для расчета ЦФ: =СУММПРОИЗВ (B3:С3;B6:С6).

Для расчета левых частей ограничений вводится следующая формула:

Для задания ЦФ необходимо поставить курсор в поле D6, открыть окно «Поиск решения» и задать направление оптимизации ЦФ «максимальное значение». Далее необходимо ввести ограничения. Для этого в окне «Поиск решения» в поле «Изменяя ячейки» вписываем B3:С3. В поле «Ограничения» вписываем ограничения и соответствующие им знаки. Для задания знаков необходимо добавить ограничения, вписав адрес ячейки D10, поставив знак ≤, вписав адрес ячейки F10. Аналогично вводим ограничения D11 ≤ F11, D12 ≤ F12, B3:C3≥B4:C4; и, так как нам надо определить количество столов и шкафов вводим ещё одно ограничение В3:С3 цел. (см. Рисунок 1).

Рисунок 1. Окно «Поиск решения»

Для решения задачи в окне «Параметры» заполнить поля так, как показано на рисунке 2.

Рисунок 2. Окно «Параметры»

В конце подтвердить ввод всех условий, нажав кнопку ОК. После этого появится оптимальное решение задачи (Рисунок 3).

Рисунок 3. Экранная форма задачи после получения решения

Задание 10:

Автотранспортная компания «Астрада» обеспечивает доставку шин «Brigestone» с трех оптовых складов, расположенных в Москве, Нижнем Новгороде и Покрове в пять магазинов в Чебоксарах, Нижнем Новгороде, Вязниках, Набережных челнах и Казани. Объемы запасов шин на складах, объемы заявок магазинов и тарифы на перевозку приведены в транспортной таблице.

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

Для того чтобы решить двухиндексную задачу линейного программирования в табличном редакторе Microsoft Excel, для начала необходимо создать экранную форму для ввода условия задачи. Разработка экранной формы заключается в создании конкретных ячеек, которые соответствуют переменным, целевой функции (ЦФ), ограничениям.

Ввод исходных данных включает в себя:

Формула для расчета ЦФ: =СУММПРОИЗВ (C11:G13; С3:G5).

Целевая функция:

L(x) = 14X11+8X12+6X13+20X14+16X15+6X21+X22+2X23+12X24+8X25+

+12X31+6X32+4X33+18X34+14X35→min

Ограничения:

X11+X12+X13+X14+X15=350

X21+X22+X23+X24+X25=400

X31+X32+X33+X34+X35=400

Ограничения:

X11+X21 +X31=200

X12 +X22 + X32 =280

X13 +X23+ X33 =240

X14 + X24 + X34=220

X15 + X25 + X35=210

Xij0; Xij=целое; (i=1;3); (j=1;4).

Для задания ЦФ необходимо поставить курсор в поле F15, открыть окно «Поиск решения» и задать направление оптимизации ЦФ «минимум». Для того чтобы задать ограничения в окне «Поиск решения» в поле «Изменяя ячейки», необходимо вписать ячейки С3: G5. В поле «Ограничения» вписываем ограничения и соответствующие им знаки. Для решения данной задачи вводим ограничения: С3: G5=целое, С3: G50, С6: G6= С8: G8, H3: H5=J3:J5 (см. Рисунок 1).

Рисунок 1 - Окно «Параметры поиска решения»

После этого в окне «Параметры» необходимо ввести данные так, как показано на рисунке 2 и нажать ОК.

Рисунок 2 – Окно «Параметры»

После ввода всех параметров нажимаем «Найти решение», после чего нажимаем ОК и получаем решение задачи (рисунок 3).

Рисунок 3 – Результат решения задачи