Математическое программирование в excel. Работа в диалоговом окне Поиск решения. Решение транспортной задачи с помощью средства Поиск решения

Решим данную задачу графическим методом в табличном редакторе Microsoft Excel (рис. 1). Для построения ОДР, и линий уровня воспользуемся Мастером диаграмм . ОДР представляет собой многоугольник с вершинами в точках: (0;0), (0;6), (2;5), (4;3), (5;0).

При перемещении линии уровня в направлении вектора получаем оптимальное решение в точке с координатами (2;5).

Аналогичным образом можно решить данную задачу графическим методом в табличном редакторе OpenOffice.org Calc воспользовавшись пунктом меню Диаграмма .

Решение ЗЛП в Microsoft Excel и OpenOffice.org Calc с помощью встроенной функции Поиск решения

В табличном процессоре Microsoft Excel существует встроенная функция Поиск решения , с помощью которой можно решить задачу линейного программирования. Если данный модуль установлен, его можно запустить выбрав команду Сервис/Поиск решения (рис. 2). На экране появится диалоговое окно Поиск решения (рис. 3).

Р и с. 2. Р и с. 3.

Если такого пункта в меню Сервис не оказалось, следует загрузить соответствующую программу-надстройку. Для этого выберите команду Сервис/Надстройки (рис. 4) и в диалоговом окне Надстройки установите флажок в строке Поиск решения (рис. 5).

Разберем решение ЗЛП с помощью функции Поиск решения на примере задачи 1.

1. Создадим таблицу для ввода исходных данных: переменных, целевой функции, ограничений.

2. Введем начальные нулевые значения для и .

3. Зададим целевую функцию в ячейке D41 и ограничения в ячейках Е39, Е40 и E41 (рис. 6).

Р и с. 4. Р и с. 5.

4. Выберем команду Сервис/Поиск решения , в открывшемся окне Поиск решения установим целевую ячейку D41, зададим условие отыскания максимального значения (рис. 7).

5. В поле Изменяя ячейки установим ссылку на ячейки С40 и С41, которые будут изменены (можно ввести адреса или имена ячеек с клавиатуры или указать диапазон ячеек на рабочем листе с помощью мыши). При щелчке на кнопке Предположить автоматически выделяются ячейки, на которые есть прямая или косвенная ссылка в формуле целевой ячейки (рис. 7).

6. Определим ограничения, для этого щелчком по кнопке Добавить откроем диалоговое окно Добавление ограничения . Введем ограничения для ячеек E39, E40, E41. Ограничения можно задать как для изменяемых ячеек, так и для целевой ячейки, а также для других ячеек, прямо или косвенно присутствующих в модели (рис. 8, 9).

Р и с. 8. Р и с. 9.

7. Щелчком на кнопке Параметры откроем диалоговое окно Параметры поиска решения . В данном окне выберем линейную модель и неотрицательные значения (неотрицательные значения для ячеек С40 и С41 можно было также установить при определении ограничений). Подробнее узнать о задаваемых параметрах можно щелкнув на кнопке Справка (рис. 10).

8. После того как все параметры и ограничения заданы, запускаем поиск решения, щелкнув на кнопке Выполнить (рис. 9). По мере того как идет поиск, отдельные его шаги отражаются в строке состояния. Когда поиск будет закончен, в таблицу будут внесены новые значения и на экране появится диалоговое окно Результаты поиска решения , сообщающие о завершении операции (рис. 11).

Решение найдено. Все ограничения и условия оптимальности выполнены. Сохраним найденное решение. В этом случае таблица будет обновлена. В случае необходимости всегда можно будет восстановить исходные данные с помощью отчета. Для выбора типа отчета достаточно выделить название нужного отчета в списке Тип отчета (или несколько названий, удерживая нажатой клавишу Сtrl ). Они будут вставлены на отдельных листах в рабочую книгу перед листом с исходными данными.

Предлагаемые отчеты содержат следующую информацию:

отчет Результаты содержит сведения о начальных и текущих значениях целевой ячейки и изменяемых ячеек, а также о соответствии значений заданным ограничениям;

отчет Устойчивость отражает найденный результат, а также нижние и верхние предельные значения для изменяемых ячеек;

отчет Пределы показывает зависимость решений от изменения формулы или ограничений.

Если планируется использовать созданную модель в дальнейшем, найденное решение можно сохранить как сценарий. Для этого в диалоговом окне Результаты поиска решения необходимо щелкнуть на кнопке Сохранить сценарий .

Аналогично Поиск решения осуществляется в OpenOffice.org Calc.

Задание

1. Решить задачи 2 и 3 графическим методом.

2. Решить задачи 2 и 3 в редакторе Microsoft Excel или OpenOffice.org Calc используя встроенную функцию Поиск решения .

3. Сравнить и проанализировать полученные результаты.

4. Ответить на контрольные вопросы.

5. Оформить отчет.

Задача 2. Фармацевтическая фирма Ozark ежедневно производит не менее 800 фунтов некой пищевой добавки – смеси кукурузной и соевой муки, состав которой представлен в таблице 2.

Таблица 2

Диетологи требуют, чтобы в пищевой добавке было не менее 30% белка и не более 5% клетчатки. Фирма Ozark хочет определить рецептуру смеси минимальной стоимости с учетом требований диетологов.

Задача 3. Предприятие, специализирующееся на производстве трикотажного полотна двух видов, использует для своего производства четыре вида сырья (шерстяную, хлопковую, вискозную, и акриловую нити), запасы которого на планируемый период составляют соответственно 80, 80, 260 и 410 бобин. В приведенной ниже таблице даны технологические коэффициенты, т.е. расход каждого вида сырья на производство одного метра каждого вида трикотажа.

Таблица 3

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

Контрольные вопросы

1. Что означает составить математическую модель ЗЛП?

2. Из каких этапов состоит графический метод решения ЗЛП?

3. Какова геометрическая интерпретация решения системы линейных неравенств с двумя переменными?

4. Как определяется направление наискорейшего возрастания целевой функции?

5. Какое решение называется оптимальным решением ЗЛП?

6. В каком случае ЗЛП имеет множество решений?

7. При каких условиях ЗЛП может быть неразрешима?

8. Как установить модуль Поиск решения ?

9. Для чего предназначена кнопка Предположить в окне Поиск решения ?

10. Какие типы отчетов можно получить при решении ЗЛП с помощью встроенной функции Поиск решения ?

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

Симплексный метод. Задача определения оптимального плана выпуска продукции. Использование встроенных функций редакторов Microsoft Excel и OpenOffice.org Calc для построения математической модели и решения ЗЛП.

Цель лабораторного занятия:

Приобретение навыков решения ЗЛП симплекс-методом. Освоение приемов записи математической модели ЗЛП с большим количеством неизвестных в табличных редакторах Microsoft Excel и OpenOffice.org Calc с помощью встроенной функций СУММПРОИЗВ. Приобретение навыков решения ЗЛП с большим количеством неизвестных с помощью функции Поиск решения .

Задачи лабораторного занятия:

1. Освоение симплекс-метода решения ЗЛП.

2. Построение математической модели задачи в табличных редакторах Microsoft Excel и OpenOffice.org Calc с помощью встроенной функций СУММПРОИЗВ.

3. Нахождение максимума (минимума) целевой функции с помощью команды Поиск решения .

4. Анализ полученных результатов.

5. Оформление отчета.

1. Краткие теоретические сведения.

2. Решение ЗЛП симплекс методом без использования табличных редакторов.

3. Решение ЗЛП на определение оптимального плана выпуска продукции в Microsoft Excel и OpenOffice.org Calc с помощью встроенной функции Поиск решения .

4. Задание.

5. Контрольные вопросы.

Краткие теоретические сведения

В основу симплекс-метода (симплексного метода) легла идея последовательного улучшения решения.

Геометрический смысл симплексного метода состоит в последовательном переходе от одной вершины многогранника ограничений (называемой первоначальной) к соседней, в которой линейная целевая функция принимает лучшее или, по крайней мере, не худшее значение. Этот процесс осуществляется до тех пор, пока не будет найдено оптимальное решение – вершина, где достигается оптимальное значение целевой функции (если задача имеет конечный оптимум).

Реализация симплекс-метода предусматривает содержание трех основных элементов:

1. Определение какого-либо первоначального допустимого базисного решения задачи (базисное решение называется допустимым, если значения, входящих в него переменных неотрицательны);

2. Правила перехода к лучшему (точнее, не худшему) решению;

3. Критерий проверки оптимальности найденного решения.

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

Практические расчеты при решении прикладных задач симплексным методом выполняются в настоящее время с помощью компьютерных программ, таких как табличный процессор Microsoft Excel, пакеты прикладных программ MathCAD, Math Lab и др. Однако, если расчеты осуществляются вручную, удобно использовать так называемые симплексные таблицы.

Использование Microsoft Excel для решения задач линейного программирования .

В Excel 2007 для включения пакета анализа надо нажать перейти в блок Параметры Excel , нажав кнопку в левом верхнем углу, а затем кнопку «Параметры Excel » внизу окна:

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

Для того чтобы решить задачу ЛП в табличном процессоре Microsoft Excel , необходимо выполнить следующие действия:

1. Ввести условие задачи:

a) создать экранную форму для ввода условия задачи :

· переменных,

· целевой функции (ЦФ),

· ограничений,

· граничных условий;

b) ввести исходные данные в экранную форму :

· коэффициенты ЦФ,

· коэффициенты при переменных в ограничениях,

· правые части ограничений;

c) ввести зависимости из математической модели в экранную форму :

· формулу для расчета ЦФ,

· формулы для расчета значений левых частей ограничений;

d) задать ЦФ (в окне "Поиск решения" ):

· целевую ячейку,

· направление оптимизации ЦФ;

e) ввести ограничения и граничные условия (в окне "Поиск решения" ):

· ячейки со значениями переменных,

· граничные условия для допустимых значений переменных,

· соотношения между правыми и левыми частями ограничений.

2. Решить задачу:

a) установить параметры решения задачи (в окне "Поиск решения" );

b) запустить задачу на решение (в окне "Поиск решения" ) ;

c) выбрать формат вывода решения (в окне "Результаты поиска решения" ).

Рассмотрим подробно использование MS Excel на примере решения следующей задачи.

Задача.

Фабрика "GRM pic" выпускает два вида каш для завтрака - "Crunchy" и "Chewy". Используемые для производства обоих продуктов ингредиенты в основ-ном одинаковы и, как правило, не являются дефицитными. Основным ограничением, накладываемым на объем выпуска, является наличие фонда рабочего времени в каждом из трех цехов фабрики.

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

Цех	Необходимый фонд рабочего времени чел.-ч/т		Общий фонд рабочего времени чел.-ч. в месяц
	"Crunchy"	"Chewy"
А. Производство	10	4	1000
В. Добавка приправ	3	2	360
С. Упаковка	2	5	600

Доход от производства 1 т "Crunchy" составляет 150 ф. ст., а от производства "Chewy" - 75 ф, ст. На настоящий момент нет никаких ограничений на возможные объемы продаж. Имеется возможность продать всю произведенную продукцию.

Требуется:

а) Сформулировать модель линейного программирования, максимизи-рующую общий доход фабрики за месяц.

б) Решить ее c помощью MS Excel.

Формальная постановка данной задачи имеет вид:

(1)
Ввод исходных данных
Создание экранной формы и ввод исходных данных

Экранная форма для решения в MS Excel представлена на рисунке 1.


Рисунок 1.

В экранной форме на рисунке 1 каждой переменной и каждому коэффициенту задачи поставлена в соответствие конкретная ячейка на листе Excel. Имя ячейки состоит из буквы, обозначающей столбец, и цифры, обозначающей строку, на пересечении которых находится объект задачи ЛП. Так, например, переменным задачи 1 соответствуют ячейки B4 (), C4 (), коэффициентам ЦФ соответствуют ячейки B6 (150), C6 (75), правым частям ограничений соответствуют ячейки D 18 (1000), D 19 (360), D 20 (600) и т.д.
Ввод зависимостей из формальной постановки задачи в экранную форму

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

Одним из самых простых способов определения функций в MS Excel является использование режима "Вставка функций", который можно вызвать из меню "Вставка" или при нажатии кнопки "

Рисунок 2

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

· курсор в поле D 6;

· нажав кнопку "

· в окне "Функция" выберитефункцию СУММПРОИЗВ (рис. 3);


Рисунок 3

· в появившемся окне "СУММПРОИЗВ" в строку "Массив 1" введите выражение B $4: C $4 , а в строку "Массив 2" - выражение B 6: C 6 (рис. 4);

Рисунок 4

Левые части ограничений задачи (1) представляют собой сумму произведений каждой из ячеек, отведенных для значений переменных задачи (B 3, C 3 ), на соответствующую ячейку, отведенную для коэффициентов конкретного ограничения (B 13, C 13 - 1-е ограничение; B 14, С14 - 2-е ограничение и B 15, С15 - 3-е ограничение). Формулы, соответствующие левым частям ограничений, представлены в табл.1.

Таблица 1.
Формулы, описывающие ограничения модели (1)

Левая часть ограничения	Формула Excel
	=СУММПРОИЗВ(B 4: C 4; B 13: C 13))
	=СУММПРОИЗВ(B 4: C 4; B 14: C 14))
	=СУММПРОИЗВ(B 4: C 4; B 15: C 15)

Задание ЦФ

Дальнейшие действия производятся в окне "Поиск решения" , которое вызывается из меню "Сервис" (рис.5):

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

· введите адрес целевой ячейки $ D $6 или сделайте одно нажатие левой клавиши мыши на целевую ячейку в экранной форме ¾ это будет равносильно вводу адреса с клавиатуры;

· введите направление оптимизации ЦФ, щелкнув один раз левой клавишей мыши по селекторной кнопке "максимальному значению".

Рисунок 5
Ввод ограничений и граничных условий
Задание ячеек переменных

В окно "Поиск решения" в поле "Изменяя ячейки" впишите адреса $ B $4:$С$4 . Необходимые адреса можно вносить в поле "Изменяя ячейки" и автоматически путем выделения мышью соответствующих ячеек переменных непосредственно в экранной форме.
Задание граничных условий для допустимых значений переменных

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

· Нажмите кнопку "Добавить" , после чего появится окно "Добавление ограничения" (рис.6).

· В поле "Ссылка на ячейку" введите адреса ячеек переменных $ B $4:$С$4 . Это можно сделать как с клавиатуры, так и путем выделения мышью всех ячеек переменных непосредственно в экранной форме.

· В поле знака откройте список предлагаемых знаков и выберите .

· В поле "Ограничение" введите 0.

Рис.6 - Добавление условия неотрицательности переменных задачи (1)
Задание знаков ограничений , , =

· Нажмите кнопку "Добавить" в окне "Добавление ограничения" .

· В поле "Ссылка на ячейку" введите адрес ячейки левой части конкретного ограничения, например $ B $18 . Это можно сделать как с клавиатуры, так и путем выделения мышью нужной ячейки непосредственно в экранной форме.

· В соответствии с условием задачи (1) выбрать в поле знака необходимый знак, например, .

· В поле "Ограничение" введите адрес ячейки правой части рассматриваемого ограничения, например $ D $18 .

· Аналогично введите ограничения: $ B $19<=$ D $19 , $ B $20<=$ D $20 .

· Подтвердите ввод всех перечисленных выше условий нажатием кнопки OK .

Окно "Поиск решения" после ввода всех необходимых данных задачи (1) представлено на рис. 5.

Если при вводе условия задачи возникает необходимость в изменении или удалении внесенных ограничений или граничных условий, то это делают, нажав кнопки "Изменить" или "Удалить" (см. рис. 5).
Решение задачи
Установка параметров решения задачи

Задача запускается на решение в окне "Поиск решения". Но предварительно для установления конкретных параметров решения задач оптимизации определенного класса необходимо нажать кнопку "Параметры" и заполнить некоторые поля окна "Параметры поиска решения" (рис. 7).

Рис. 7 - Параметры поиска решения, подходящие для большинства задач ЛП

Параметр "Максимальное время" служит для назначения времени (в секундах), выделяемого на решение задачи. В поле можно ввести время, не превышающее 32 767 секунд (более 9 часов).

Параметр "Предельное число итераций" служит для управления временем решения задачи путем ограничения числа промежуточных вычислений. В поле можно ввести количество итераций, не превышающее 32 767.

Параметр "Относительная погрешность" служит для задания точности, с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 до 1. Чем меньше количество десятичных знаков во введенном числе, тем ниже точность. Высокая точность увеличит время, которое требуется для того, чтобы сошелся процесс оптимизации.

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

Параметр "Сходимость" применяется только при решении нелинейных задач.Установка флажка "Линейная модель" обеспечивает ускорение поиска решения линейной задачи за счет применение симплекс-метода.

Подтвердите установленные параметры нажатием кнопки " OK " .
Запуск задачи на решение

Запуск задачи на решение производится из окна "Поиск решения" путем нажатия кнопки "Выполнить".

После запуска на решение задачи ЛП на экране появляется окно "Результаты поиска решения" с сообщением об успешном решении задачи, представленном на рис. 8.


Рис. 8 -. Сообщение об успешном решении задачи

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

Если при заполнении полей окна "Поиск решения" были допущены ошибки, не позволяющие Excel применить симплекс-метод для решения задачи или довести ее решение до конца, то после запуска задачи на решение на экран будет выдано соответствующее сообщение с указанием причины, по которой решение не найдено. Иногда слишком малое значение параметра "Относительная погрешность" не позволяет найти оптимальное решение. Для исправления этой ситуации увеличивайте погрешность поразрядно, например от 0,000001 до 0,00001 и т.д.

В окне "Результаты поиска решения" представлены названия трех типов отчетов: "Результаты", "Устойчивость", "Пределы" . Они необходимы при анализе полученного решения на чувствительность. Для получения же ответа (значений переменных, ЦФ и левых частей ограничений) прямо в экранной форме просто нажмите кнопку " OK ". После этого в экранной форме появляется оптимальное решение задачи (рис. 9).


Рис.9 - Экранная форма задачи (1) после получения решения

Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже

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

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

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

Частное образовательное учреждение высшего образования «Санкт-Петербургский университет технологий управления и экономики»

Кафедра экономики и менеджмента

КОНТРОЛЬНАЯ РАБОТА

По дисциплине: МЕТОДЫ ОПТИМАЛЬНЫХ РЕШЕНИЙ

Выполнил:

Студент (ка) 3 курса, группа № 19731Д/3-2

Крюк Альбина Владимировна

Руководитель:

к.э.н., доцент Ж.М. Козлова.

Барнаул 2016

• Введение
• Заключение
• ВВЕДЕНИЕ
• Решение широкого круга задач электроэнергетики и других отраслей народного хозяйства основывается на оптимизации сложной совокупности зависимостей, описанных математически с помощью некоторой «целевой функции» (ЦФ). Подобные функции можно записать для определения затрат на топливо для электростанций, на потери электроэнергии при транспорте ее от электростанции к потребителям и многие другие проблемные задачи. В таких случаях требуется найти ЦФ при определенных ограничениях, накладываемых на ее переменные. Если ЦФ линейно зависит от входящих в ее состав переменных и все ограничения образуют линейную систему уравнений и неравенств, то такая частная форма оптимизационной задачи получила название «задачи линейного программирования».
• Темы контрольной работы «Решение задач линейного программирования в MS Excel», получить практические навыки в использовании электронных таблиц Microsoft Excel и решения оптимизационных задач линейного программирования.

1. Типовые задачи оптимизации и их экономико-математические модели

Экономико-математическое моделирование представляет собой процесс выражения экономических явлений математическими мо­делями. Экономическая модель -- это схематичное представление экономического явления или процесса с использованием научной абстракции, отражение их характерных черт. Математические мо­дели -- основное средство решения задач оптимизации любой дея­тельности. По своей сути эти модели -- средство плановых расче­тов. Ценность их для экономического анализа и оптимизации реше­ний состоит в том, что они позволяют оценить напряженность плановых заданий, определить лимитирующую группу оборудова­ния, видов ресурсов, получать оценки их дефицитности и т.п. Мате­матическое моделирование экономических явлений и процессов дает возможность получить четкое представление об исследуемом объекте, охарактеризовать и количественно описать его внутреннюю струк­туру и внешние связи. Модель -- условный образ объекта управле­ния /1/.

Экономико-математическая модель должна быть адекватной действительности, отражать существенные стороны и связи изучаемо­го объекта. Отметим принципиальные черты, характерные для по­строения экономико-математической модели любого вида. Процесс моделирования можно условно подразделить на три этапа:

1) ана­лиз теоретических закономерностей, свойственных изучаемому яв­лению или процессу и эмпирических данных о его структуре и особенностях; на основе такого анализа формируются модели;

2) определение методов, с помощью которых можно решить задачу;

3) анализ полученных результатов.

Важнейшим моментом первого этапа моделирования является четкая формулировка конечной цели построения модели, а также определение критерия, по которому будут сравниваться различные варианты решения. Такими критериями в системе менеджмента могут быть:

а) максимизация полезного эффекта товара при ограни­чении совокупности затрат;

б) максимизация прибыли фирмы при условии, что качество товара не снизится; в) снижение себестоимо­сти товара при условии, что его качество не снизится, затраты у потребителя не увеличатся;

г) рост производительности труда, улуч­шение использования оборудования или материалов, повышение оборачиваемости оборотных средств при условии, что качество то­вара не снизится и другие критерии не ухудшатся.

Таким образом, в качестве критерия оптимизации может быть целое или любой компонент прибыли, эффективности товара, объема рынка при ус­ловии, что другие компоненты при этом не ухудшатся.

Например, уравнение целевой функции (L) и система ограниче­ний по оптимизации прибыли фирмы (правда, у авторов нет огра­ничений по качеству товара) будет иметь следующий вид:

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

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

аij -- норма расхода i-го производственного ресурса на про­изводство единицы j-го вида продукции;

щj -- запасы i-го вида производственного ресурса на рас­сматриваемый период времени.

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

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

Третьим этапом моделирования является всесторонний анализ результата, полученного при изучении экономического явления. Окончательным критерием достоверности и качества модели явля­ются практика, соответствие полученных результатов и выводов реальным условиям, экономическая содержательность полученных оценок. Если результаты не соответствуют реальным условиям, то необходим анализ причин несоответствия, в качестве которых могут быть недостоверность информации, несоответствие модели эконо­мическим условиям и др. По результатам анализа причин несоответствия экономико-математическая модель корректируется и ре­шение задачи повторяется.

Решим графическим методом типовую задачу оптимизации

Некоторая фирма выпускает два набора удобрений для газонов: обычный и улучшенный. В обычный набор входит 3 кг азотных, 4 кг фосфорных и 1 кг калийных удобрений, а в улучшенный - 2 кг азотных, 6 кг фосфорных и 3 кг калийных удобрений. Известно, что для некоторого газона требуется по меньшей мере 10 кг азотных, 20 кг фосфорных и 7 кг калийных удобрений. Обычный набор стоит 3 ден. Ед., а улучшенный - 4 ден. Ед. Какие и сколько наборов удобрений нужно купить, чтобы обеспечить эффективное питание почвы и минимизировать стоимость?

Построить экономико-математическую модель задачи, дать необходимые комментарии к ее элементам и получить решение графическим методом. Что произойдет, если решать задачу на максимум, и почему?

Сформулируем прямую оптимизационную задачу.

Пусть х1 - количество обычных наборов удобрений;

х2 - количество улучшенных наборов удобрений.

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

3х1 + 2х2 ? 10

4х1 + 6х2 ? 20

Стоимость необходимых наборов удобрений составит:

Таким образом, получим следующую экономико-математическую модель задачи:

min (х) = 3х1 + 4х2

3х1 + 2х2 ? 10

4х1 + 6х2 ? 20

Построим область решений системы ограничений. Для этого рассмотрим равенства и построим их графики - прямые.

1) 3х1 + 2х2 ? 10

3х1 + 2х2 = 10

3) х1 + 3х2 ? 7

Неравенство не выполняется, значит, исходному неравенству соответствует полуплоскость, не содержащая точку О(0;0).

х1 = 0 - ось ОХ2.

х2 = 0 - ось ОХ1.

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

Рис.1. Графическое решение ЗЛП

Находим общую часть всех построенных полуплоскостей. Это выпуклая заштрихованная область.

Для нахождения оптимального решения задачи изобразим графически функцию цели:

(х) = d1x1 + d2x2

(х) = 3х1 + 4х2

Для этого строим вектор d, начало которого в точке (0;0), а конец в точке (d1;d2).

И строим одну из линий уровня функции цели (это линия, на которой функция цели принимает постоянное значение).

Для определения минимума данной функции, передвигаем линию уровня в направлении, противоположном вектору d, и видим, что она последний раз соприкасается с областью решений в точке В, где и будет достигнут min(х).

Определим координаты точки В:

3х1 + 2х2 = 10 *(-3)

4х1 + 6х2 = 20

9х1 - 6х2 = -30

4х1 + 6х2 = 20

Складываем почленно уравнения и получаем:

(х) = 3*2 + 4*2 = 14 (ден. ед.)

Таким образом, чтобы минимизировать стоимость удобрений, нужно купить 2 обычных набора удобрений и 2 улучшенных набора удобрений. При этом минимальные затраты на покупку удобрений составят 14 денежных единиц. microsoft excel программирование математический

Если решать данную задачу на максимум, то конечного оптимума не найдем, т.к. функция цели неограниченна, область решений системы ограничений бесконечна.

2. Задачи линейного программирования, решение средствами MS Excel

Линейное программирование является разделом, с которого начала развиваться дисциплина «математическое программирование». Термин «программирование» в названии дисциплины ничего общего с термином «программирование (т.е. составление программ) для ЭВМ» не имеет, так как дисциплина «линейное программирование» возникла еще до того времени, когда ЭВМ стали широко применяться при решении математических, инженерных, экономических и других задач. Термин «линейное программирование» возник в результате неточного перевода английского «linear programming». Одно из значений слова «programming» - составление планов, планирование. Следовательно, правильным переводом «linear programming» было бы не «линейное программирование», а «линейное планирование», что более точно отражает содержание дисциплины. Однако, термин линейное программирование, нелинейное программирование и т.д. в нашей литературе стали общепринятыми. Задачи линейного программирования является удобной математической моделью для большого числа экономических задач (планирование производства, расходование материалов, транспортные перевозки и т.д.). Использование метода линейного программирования представляет собой важность и ценность - оптимальный вариант выбирается из достаточно значительного количества альтернативных вариантов. Также все экономические задачи, решаемые с применением линейного программирования, отличаются альтернативностью решения и определенными ограничивающими условиями.
В электронных таблицах Excel с помощью функции поиска решения можно вести поиск значения в целевой ячейке, изменения значения переменных. При этом для каждой переменной можно задать ограничения, например верхнюю границу. Перед тем как запустить поиск решения, необходимо четко сформулировать в модели решаемую проблему, т.е. определить условия, выполняемые при оптимизации. Отправленной точкой при поиске оптимального решения является модель вычисления, созданная в рабочем листе. Программе поиска решения при этом необходимы следующие данные. 1. Целевая ячейка - это ячейка в модели вычисления, значения в которой должно быть максимизировано, минимизировано или же равняться определенному указанному значению. Она должна содержать формулу, которая прямо или косвенно ссылается на изменяемые ячейки, или же самой быть изменяемой. 2. Значения в изменяемых ячейках будут последовательно (методом итераций) изменяться до тех пор, пока не будет получено нужное значение в целевой ячейке. Эти ячейки, следовательно, прямо или косвенно должны влиять на значение целевой ячейки. 3. Вы можете задать как для целевой, так и для изменяемых ячеек, ограничения и граничные условия. Можно задать также ограничения для других ячеек. Прямо или косвенно присутствующих в модели. Программа предоставляет возможность задать специальные параметры, определяющие процесс поиска решения. После задания всех необходимых параметров можно запустить поиск решения. Функция поиска решения создаст по итогам своей работы три отчета, которые можно пометить в рабочую книгу.Ограничения - это условия, которые должны быть выполнены аппаратом поиска решения при оптимизации модели.

Изучение литературы показало, что:

1. Линейное программирование - это один из первых и наиболее подробно изученных разделов математического программирования. Именно линейное программирование явилось тем разделом, с которого начала развиваться сама дисциплина «математическое программирование».

Линейное программирование представляет собой наиболее часто используемый метод оптимизации. К числу задач линейного программирования можно отнести задачи:

· рационального использования сырья и материалов; задачи оптимизации раскроя;

· оптимизации производственной программы предприятий;

· оптимального размещения и концентрации производства;

· составления оптимального плана перевозок, работы транспорта;

· управления производственными запасами;

· и многие другие, принадлежащие сфере оптимального планирования.

2. Графический метод довольно прост и нагляден для решения задач линейного программирования с двумя переменными. Он основан на геометрическом представлении допустимых решений и ЦФ задачи.

Суть графического метода заключается в следующем. По направлению (против направления) вектора в ОДР производится поиск оптимальной точки. Оптимальной считается точка, через которую проходит линия уровня, соответствующая наибольшему (наименьшему) значению функции. Оптимальное решение всегда находится на границе ОДР, например, в последней вершине многоугольника ОДР, через которую пройдет целевая прямая, или на всей его стороне.

ЗАКЛЮЧЕНИЕ

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

Благодаря программному продукту Excel, который входит в пакет MS Office, решение наших задач ускоряется в несколько десятков раз. А благодаря точным математическим расчетам данного ПО, мы можем без сомнения найти самые точные результаты исследований.

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

...

Подобные документы

Краткие сведения об электронных таблицах MS Excel. Решение задачи линейного программирования. Решение с помощью средств Microsoft Excel экономической оптимизационной задачи, на примере "транспортной задачи". Особенности оформления документа MS Word.

курсовая работа , добавлен 27.08.2012


История развития и функции линейного программирования. Исследование условий типовых задач и возможностей табличного процессора. Решение задач о рационе питания, плане производства, раскрое материалов и рациональной перевозке груза в среде MS Excel.

курсовая работа , добавлен 28.04.2014


Принципы решения задач линейного программирования в среде электронных таблиц Excel, в среде пакета Mathcad. Порядок решения задачи о назначении в среде электронных таблиц Excel. Анализ экономических данных с помощью диаграмм Парето, оценка результатов.

лабораторная работа , добавлен 26.10.2013


Алгоритм решения задач линейного программирования симплекс-методом. Построение математической модели задачи линейного программирования. Решение задачи линейного программирования в Excel. Нахождение прибыли и оптимального плана выпуска продукции.

курсовая работа , добавлен 21.03.2012


Изучение и укрепление на практике всех моментов графического метода решения задач линейного программирования о производстве журналов "Автомеханик" и "Инструмент". Построение математической модели. Решение задачи с помощью электронной таблицы Excel.

курсовая работа , добавлен 10.06.2014


Общее понятие и характеристика задачи линейного программирования. Решение транспортной задачи с помощью программы MS Excel. Рекомендации по решению задач оптимизации с помощью надстройки "Поиск решения". Двойственная задача линейного программирования.

дипломная работа , добавлен 20.11.2010


Анализ метода линейного программирования для решения оптимизационных управленческих задач. Графический метод решения задачи линейного программирования. Проверка оптимального решения в среде MS Excel с использованием программной надстройки "Поиск решения".

курсовая работа , добавлен 29.05.2015


Разработка таблиц в Excel методами линейного программирования с целью оптимизации расходов ресурсов и запасов на изготовление продукции: определение переменных величин, структуры целевой функции, построение математической модели и блок-схем решения задач.

курсовая работа , добавлен 07.06.2010


Методы решения задач линейного программирования: планирования производства, составления рациона, задачи о раскрое материалов и транспортной. Разработка экономико-математической модели и решение задачи с использованием компьютерного моделирования.

курсовая работа , добавлен 13.03.2015


Графическое решение задач. Составление математической модели. Определение максимального значения целевой функции. Решение симплексным методом с искусственным базисом канонической задачи линейного программирования. Проверка оптимальности решения.

Рассмотрим пример задачи линейного программирования.

Требуется определить, в каком количестве надо выпустить продукцию четырех типов Прод1, Прод2, Прод3, Прод4, для изготовления которой требуются ресурсы трех видов: трудовые, сырье и финансы. Количество ресурса каждого вида, необходимое для выпуска единицы продукции данного типа, называется нормой расхода. Нормы расхода, а также прибыль, получаемая от реализации единицы каждого типа продукции, приведены на рис. 1.

Ресурс	Прод1	Прод2	Прод3	Прод4	Знак	Наличие
Прибыль
Трудовые
Сырье
Финансы

Рисунок 1.

Математическая модель задачи имеет вид:

где x j – количество выпускаемой продукции j-го типа; F – функция цели; в левых частях выражений ограничений указаны величины потребного ресурса , а правые части показывают количество имеющегося ресурса .

Ввод условий задачи

Для решения задачи с помощью Excel следует создать форму для ввода исходных данных и ввести их. Форма ввода показана на рис. 2.

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

В ячейки F8:F10 введены левые части ограничений для ресурсов каждого вида.

Рисунок 2.

Рисунок 3.

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

Для решения задач линейного программирования в Excel используется мощный инструмент, называемый Поиск решения . Обращение к Поиску решения осуществляется из меню Сервис , на экран выводится диалоговое окно Поиска решения (рис. 4).

Рисунок 4.

Ввод условий задачи для поиска ее решения состоит из следующих шагов:

1 Назначить целевую функцию, для чего установить курсор в поле Установить целевую ячейку окна Поиск решения и щелкнуть в ячейке F6 в форме ввода;

2 Включить переключатель значения целевой функции, т.е. указать ее Равной Максимальному значению ;

3 Ввести адреса изменяемых переменных (x j): для этого установить курсор в поле Изменяя ячейки окна Поиск решения, а затем выделить диапазон ячеек B3:E3 в форме ввода;

4 Нажать кнопку Добавить окна Поиск решения для ввода ограничений задачи линейного программирования; на экран выводится окно Добавление ограничения (рис. 5) :

Ввести граничные условия для переменных x j (x j ³0), для этого в поле Ссылка на ячейку указать ячейку В3, соответствующую х 1 , выбрать из списка нужный знак (³), в поле Ограничение указать ячейку формы ввода, в которой хранится соответствующее значение граничного условия, (ячейка В4), нажать кнопку Добавить ; повторить описанные действия для переменных х 2 , х 3 и х 4 ;

Ввести ограничения для каждого вида ресурса, для этого в поле Ссылка на ячейку окна Добавление ограничения указать ячейку F9 формы ввода, в которой содержится выражение левой части ограничения, наложенного на трудовые ресурсы, в полях Ограничение указать знак £ и адрес Н9 правой части ограничения, нажать кнопку Добавить ; аналогично ввести ограничения на остальные виды ресурсов;

После ввода последнего ограничения вместо Добавить нажать ОК и возвратиться в окно Поиск решения.

Рисунок 5.

Решение задачи линейного программирования начинается с установки параметров поиска:

В окне Поиск решения нажать кнопку Параметры , на экран выводится окно Параметры поиска решения (рис. 6);

Установить флажок Линейная модель, что обеспечивает применение симплекс-метода;

Указать предельное число итераций (по умолчанию – 100, что подходит для решения большинства задач);

Установить флажок , если необходимо просмотреть все этапы поиска оптимального решения;

Нажать ОК , возврат в окно Поиск решения .

Рисунок 6.

Для решения задачи нажать кнопку Выполнить в окне Поиск решения , на экране – окно Результаты поиска решения (рис. 7), в котором содержится сообщение Решение найдено. Все ограничения и условия оптимальности выполнены. Если условия задачи несовместны, то выводится сообщение Поиск не может найти подходящего решения . Если целевая функция не ограничена, то появляется сообщение Значения целевой ячейки не сходятся .

Рисунок 7.

Для рассматриваемого примера решение найдено и результат оптимального решения задачи выводится в форме ввода: значение целевой функции, соответствующее максимальной прибыли и равное 1320, указывается в ячейке F6 формы ввода, оптимальный план выпуска продукции х 1 =10, х 2 =0, х 3 =6, х 4 =0 указывается в ячейках В3:С3 формы ввода (рис. 8).

Количество использованных для выпуска продукции ресурсов выводится в ячейки F9:F11: трудовых – 16, сырья – 84, финансов – 100.

Рисунок 8.

Если при установке параметров в окне Параметры поиска решения (рис. 6) был установлен флажок Показывать результаты итераций , то будут показаны последовательно все шаги поиска. На экран будет выводиться окно (рис. 9). При этом текущие значения переменных и функции цели будут показаны в форме ввода. Так, результаты первой итерации поиска решения исходной задачи представлены в форме ввода на рисунке 10 .

Рисунок 9.

Рисунок 10.

Чтобы продолжить поиск решения, следует нажимать кнопку Продолжить в окне Текущее состояние поиска решения .

Анализ оптимального решения

Прежде чем, перейти к анализу результатов решения, представим исходную задачу в форме

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

Составим для исходной задачи двойственную задачу и введем дополнительные двойственные переменные v i .

Анализ результатов поиска решения позволит увязать их с переменными исходной и двойственной задач.

С помощью окна Результаты поиска решения можно вызвать отчеты трех типов, позволяющие анализировать найденное оптимальное решение:

Результаты,

Устойчивость,

Пределы.

Для вызова отчета в поле Тип отчета выделить название нужного типа и нажать ОК .

1 Отчет по результатам (рис. 11) состоит из трех таблиц:

Таблица 1 содержит сведения о целевой функции; в столбце Исходно указывается значение целевой функции до начала вычислений;

Таблица 2 содержит значения искомых переменных x j , полученных в результате решения задачи (оптимальный план выпуска продукции);

Таблица 3 показывает результаты оптимального решения для ограничений и для граничных условий.

Для Ограничений в графе Формула приведены зависимости, которые были введены при задании ограничений в окне Поиск решения ; в графе Значение указаны величины использованного ресурса; в графе Разница показано количество неиспользованного ресурса. Если ресурс используется полностью, то в графе Состояние выводится сообщение связанное ; при неполном использовании ресурса в этой графе указывается не связан. Для Граничных условий приводятся аналогичные величины с той лишь разницей, что вместо неиспользованного ресурса показана разность между значением переменной x j в найденном оптимальном решении и заданным для нее граничным условием (x j ³0).

Именно в графе Разница можно увидеть значения дополнительных переменных y i исходной задачи в формулировке (2). Здесь у 1 =у 3 =0, т.е. величины неиспользованных трудовых и финансовых ресурсов равны нулю. Эти ресурсы используются полностью. Вместе с тем, величина неиспользованных ресурсов для сырья у 2 =26, значит, имеются излишки сырья.

Рисунок 11.

2 Отчет по устойчивости (рис. 12)состоит из двух таблиц.

В таблице 1 приводятся следующие значения:

Результат решения задачи (оптимальный план выпуска);

- Нормир. стоимость , т.е. величины, показывающие, насколько изменится целевая функция при принудительном включении единицы продукции соответствующего типа в оптимальный план;

Коэффициенты целевой функции;

Предельные значения приращения коэффициентов целевой функции, при которых сохраняется оптимальный план выпуска.

В таблице 2 содержатся аналогичные данные для ограничений:

Величины использованных ресурсов;

- Теневая цена , показывающая, как изменится целевая функция при изменении величины соответствующего ресурса на единицу;

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

Рисунок 12.

Отчет по устойчивости позволяет позволяет получить двойственные оценки.

Как известно, двойственные переменные z i показывают, как изменится целевая функция при изменении ресурса i-го типа на единицу. В отчете Excel двойственная оценка называется Теневой ценой .

В нашем примере сырье не используется полностью и его ресурс у 2 =26. Очевидно, что увеличение количества сырья, например, до 111 не повлечет за собой увеличения целевой функции. Следовательно, для второго ограничения двойственная переменная z 2 =0. Таким образом, если по данному ресурсу есть резерв, то дополнительная переменная будет больше нуля, а двойственная оценка этого ограничения равна нулю.

В рассматриваемом примере трудовые ресурсы и финансы использовались полностью, поэтому их дополнительные переменные равны нулю (у 1 =у 3 =0). Если ресурс используется полностью, то его увеличение или уменьшение повлияет на объем выпускаемой продукции, и следовательно, на величину целевой функции. Двойственные оценки ограничений на трудовые и финансовые ресурсы отличны от нуля, т.е. z 1 =20, z 3 =10.

Значения двойственных оценок находим в Отчете по устойчивости , в таблице 2, в графе Теневая цена .

При увеличении (уменьшении) трудовых ресурсов на единицу целевая функция увеличится (уменьшится) на 20 единиц и будет равна

F=1320+20×1=1340 (при увеличении).

Аналогично, при увеличении объема финансов на единицу целевая функция будет

F=1320+10×1=1330.

Здесь же, в графах Допустимое увеличение и Допустимое уменьшение таблицы 2, показаны допустимые пределы изменения количества ресурсов j-го вида. Например, для при изменении приращения величины трудовых ресурсов в пределах от –6 до 3,55, как показано в таблице, структура оптимального решения сохраняется, т.е наибольшую прибыль обеспечивает выпуск Прод1 и Прод3, но в других количествах.

Дополнительные двойственные переменные также отражены в Отчете по устойчивости в графе Нормир. стоимость таблицы 1.

Если основные переменные не вошли в оптимальное решение, т.е. равны нулю (в примере х 2 =х 4 =0), то соответствующие им дополнительные переменные имеют положительные значения (v 2 =10, v 4 =20). Если же основные переменные вошли в оптимальное решение (х 1 =10, х 3 =6), то их дополнительные двойственные переменные равны нулю (v 1 =0, v 3 =0).

Эти величины показывают, насколько уменьшится (поэтому знак минус в значениях переменных v 2 и v 4) целевая функция при принудительном выпуске единицы данной продукции. Следовательно, если мы захотим принудительно выпустить единицу продукции вида Прод3, то целевая функция уменьшится на 10 единиц и будет равна 1320 -10×1 =1310.

Обозначим через Dс j изменение коэффициентов целевой функции в исходной модели (1). Эти коэффициенты определяют прибыль, получаемую при реализации единицы продукции j-го вида.

В графах Допустимое увеличение и Допустимое Уменьшение таблицы 1 Отчета по устойчивости показаны пределы изменения Dс j , при которых сохраняется структура оптимального плана, т.е. будет выгодно по-прежнему выпускать продукцию вида Продj. Например, при изменении Dс 1 в пределах -12£ Dс 1 £ 40, как показано в отчете, по-прежнему будет выгодно выпускать продукцию вида Прод1. При этом значение целевой функции будет F=1320+x 1 ×Dс j =1320+10×Dс j .

3 Отчет по пределам приведен на рис. 13. В нем показывается, в каких пределах могут изменяться значения x j , вошедшие в оптимальное решение, при сохранении структуры оптимального решения. Кроме этого, для каждого типа продукции приводятся значения целевой функции, получаемые при подстановке в оптимальное решение значения нижнего предела выпуска изделий соответствующего типа при неизменных значениях выпуска остальных типов. Например, если при оптимальном решении х 1 =10, х 2 =0, х 3 =6, х 4 =0 положить х 1 =0 (нижний предел) при неизменных х 2 , х 3 и х 4 , то значение целевой функции будет равно 60×0+70×0+120×6+130×0=720.

Ввод условий задачи состоит из следующих основных шагов:

Создание формы для ввода условий задачи.

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

Ввод зависимостей из математической модели.

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

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

Ход решения задачи:

Форма для ввода условий задачи:

		Переменные
	Значение
	Коэффициент в целевой функции			(формула)
		Ограничения
		Коэффициенты в ограничениях				Правая часть ограничения

Поочередно в представленную форму заносятся коэффициенты целевой функции, ограничений, их знаки, формулы описания целевой функции и ограничений, представленные в математической модели задачи.

Для описания формулы целевой функции и ограничений используется диалоговое окно Мастер функций; категория функций – математические; функция СУММПРОИЗВ. (в диалоговом окне в массиве 1 указывается интервал ячеек значения переменной В3:С3, в массиве 2 – коэффициенты при этих переменных. В функции это интервал ячеек В4:С4, в ограничениях – В8:C8, В9:C9 и т.д.)

Решение задачи осуществляется с использованием команд Сервис, Поиск решения…

В диалоговом окне Поиск решения заполняем строки, указывая адреса ячеек:

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

Равная: max (min)

Изменяя ячейки: указывается месторасположения переменных (В3:C3)

Ограничения: с использованием клавиши Добавить записываются адреса ячеек с указанием условий ограничений (например: D8>= F8 и т.д.). Обязательным является ввод ограничения целочисленного решения.

Если при вводе задачи возникает необходимость в изменении или удалении внесенных ограничений или граничных условий, то это осуществляется с помощью команд Изменить.., Удалить.

Для получения оптимального решения задачи линейного программирования в Поиске решения задействуется клавиша Параметры…:

Максимальное время: 100 сек

Предельное число итераций: 100

Относительная погрешность 0,000001

Допустимое отклонение: 5%

Устанавливаем флажок Линейная модель, что обеспечивает применение симплекс-метода.

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

Решение найдено, результат оптимального решения приведен в исходной таблице.

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

Используя данные прямой двойственной задачи, решите ее в системе Excel, с помощью следующих таблиц

Переменные

Ограничения

Вид ресурса	Коэффициенты в ограничениях		Левая часть ограничения (формула)		Правая часть ограничения