Пример решения задачи линейного программирования с помощью MS Excel. Линейное программирование в Excel

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

Инструментом для решений задач оптимизации в MS Excel служит надстройка «Поиск решения». Процедура поис­ка решения позволяет найти оптимальное значение фор­мулы, содержащейся в ячейке, которая называется целе­вой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во вли­яющих ячейках.

Если данная надстройка установлена, то «Поиск реше­ния»запускается из меню «Сервис». Если такого пункта нет, следует выполнить команду «Сервис - Надстройки...» и вы­ставить флажок против надстройки «Поиск решения».

Решение задачи оптимизации состоит из трёх этапов.

A. Создание модели задачи оптимизации.

B. Поиск решения задачи оптимизации.

C. Анализ найденного решения задачи оптимизации.

Рассмотрим подробнее эти этапы.

Этап А.

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

Этап В.

Команда «Сервис - Поиск решения» открывает диалоговое окно «Поиск решения», в котором, в свою очередь, имеются следующие поля:

«Установить целевую ячейку» - служит для указания целе­вой ячейки, значение которой необходимо максими­зировать, минимизировать или установить равным за­данному числу. Эта ячейка должна содержать форму­лу.

«Равной» - служит для выбора варианта оптимизации зна­чения целевой ячейки (максимизация, минимизация или подбор заданного числа). Чтобы установить чис­ло, введите его в поле.

«Изменяя ячейки» - служит для указания ячеек, значения которых изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные огра­ничения и условие оптимизации значения ячейки, указанной в поле «Установить целевую ячейку».

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

«Ограничения» - служит для отображения списка гранич­ных условий поставленной задачи.

«Добавить» - служит для отображения диалогового окна «Добавить ограничение».

«Изменить» - служит для отображения диалоговое окна «Изменить ограничение».

«Удалить» – служит для снятия указанного ограничения.

«Выполнить» – служит для запуска поиска решения по­ставленной задачи.

«Закрыть» - служит для выхода из окна диалога без запус­ка поиска решения поставленной задачи. При этом сохраняются установки сделанные в окнах диалога, появлявшихся после нажатий на кнопки «Парамет­ры, Добавить, Изменить или Удалить».

«Параметры» - служит для отображения диалогового окна «Параметры поиска решения», в котором можно загрузить или сохранить оптимизируемую модель и ука­зать предусмотренные варианты поиска решения.

«Восстановить» - служит для очистки полей окна диалога и восстановления значений параметров поиска ре­шения, используемых по умолчанию.

Для решения задачи оптимизации выполните следую­щие действия.

1. В меню «Сервис» выберите команду «Поиск решения».

2. В поле «Установить целевую ячейку» введите адрес или имя ячейки, в которой находится формула оптимизируемой модели.

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

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

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

4. В поле «Изменяя ячейки» введите имена или адреса изменяемых ячеек, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Допускается установка до 200 изменяемых ячеек.

Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажмите кнопку «Предположить».

5. В поле «Ограничения» введите все ограничения, накладываемые на поиск решения.

6. Нажмите кнопку «Выполнить».

Чтобы восстановить исходные данные, установите пере­ключатель в положение «Восстановить исходные значения».

Этап С.

Для вывода итогового сообщения о результате решения используется диалоговое окно «Результаты поиска реше­ния».

Диалоговое окно «Результаты поиска решения» содер­жит следующие поля:

«Восстановить исходные значения» - служит для восста­новления исходных значений влияющих ячеек моде­ли.

«Отчеты» - служит для указания типа отчета, размещаемо­го на отдельном листе книги.

«Результаты» - используется для создания отчета, состоя­щего из целевой ячейки и списка влияющих ячеек модели, их исходных и конечных значений, а также формул ограничений и дополнительных сведений о наложенных ограничениях.

«Устойчивость» - используется для создания отчета, содер­жащего сведения о чувствительности решения к ма­лым изменениям в формуле (поле «Установить целе­вую ячейку», диалоговое окно «Поиск решения») или в формулах ограничений.

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

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

Одной из возможных задач и моделей линейной оптимизации является задача о планировании производства.

Предприятие должно производить изделия видов: , причем количество каждого выпускаемого изделия не должно превысить спрос и одновременно не должно быть меньше за­планированных величин соответственно. На изготовление изделий идет m видов сырья , за­пасы которых ограничены соответственно величинами Известно, что на изготовление i -ro изделия идет единиц j -го сырья. Прибыль, получаемая от реализации изделий равна соответственно . Требуется так спланировать производство из­делий, чтобы прибыль была максимальной и при этом выполнялся план на производство каждого изделия, но не превышался спрос на него.

Использование 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) после получения решения

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

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

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

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

• · рационального использования сырья и материалов; задачи оптимизации раскроя;
• · оптимизации производственной программы предприятий;
• · оптимального размещения и концентрации производства;
• · составления оптимального плана перевозок, работы транспорта;
• · управления производственными запасами;
• · и многие другие, принадлежащие сфере оптимального планирования.
• 2. Графический метод довольно прост и нагляден для решения задач линейного программирования с двумя переменными. Он основан на геометрическом представлении допустимых решений и ЦФ задачи.

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

Нижегородский Государственный Технический Университет

Павловский филиал

Курсовая работа

по информатике на тему:

“Технология решения задач линейного программирования с помощью Поиска решений приложения Excel” .

Выполнила : Бородулина Д.А.

Группа 05-АМ.

Проверила : Ловыгина М.Б.

Павлово 2006 г.

Введение……………………………………………………………………………стр. 3

Решение задач с помощью надстройки Поиск решения

1. Установка программы Поиск решения…………………………………………..…стр.4
2. Диалоговое окно Поиск решения…………………………………………………..…стр.4
3. Ввод и редактирование ограничений………………………………………………..стр.5
4. Настройка параметров алгоритма и программы……………………………….стр.6

1. Сохранение модели оптимизации…………………………………………………....стр.9
2. Загрузка модели оптимизации……………………………………………………….стр.9

Вычисления и результаты решения задачи………………………………..стр. 10

Просмотр промежуточных результатов поиска решения…………...стр.11

Возникающие проблемы и сообщения процедуры поиска решения…...стр.12

Итоговые сообщения процедуры поиска решения……………………....стр.13

Примеры выполнения задач

1. Пример № 1………………………………………………………………………………стр.15
2. Пример № 2 (графическим способом)……………………………………………...стр..20

Вывод……………………………………………………………………………....стр.24

Список литературы…………………………………………………………....стр.25

Введение

Линейная оптимизация – это раздел математического программирования, посвящённый нахождению экстремума линейных функций нескольких переменных при дополнительных линейных ограничениях, которые налагаются на переменные. Методы, с помощью которых решаются задачи, подразделяются на универсальные (например, симплексный метод) и специальные. С помощью универсальных методов решаются любые задачи линейного программирования. Особенностью задач линейного программирования является то, что экстремум целевой функции достигается на границе области допустимых решений.

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

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

Такие задачи в Excel решают с помощью Поиска решения .

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

Решение задач с помощью надстройки Поиск решения

1. Установка программы Поиск решения

В меню Сервис выберите команду Надстройки.

В диалоговом окне Надстройки установите флажок Поиск решения. Если диалоговое окно Надстройки не содержит команды Поиск решения , нажмите кнопку Обзор и укажите диск и папку, в которой содержится файл надстройки Solver. xla (как правило, это папка Library\ Solver folder) или запустите программу Setup , если найти файл не удаётся.

Надстройка, указанная в диалоговом окне Надстройки , остаётся активной до тех пор, пока она не будет удалена.

2. Диалоговое окно Поиск решения

Окно Поиск решения (рис. 1) вызывается командой меню Сервис>Поиск решения.

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

Рис.1.Диалоговое окно Поиск решения.

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

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

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

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

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

Команда Изменить Изменение ограничения.

Команда Удалить служит для снятия указанного курсором ограничения.

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

Команда Закрыть служит для выхода из окна диалога без запуска поиска решения поставленной задачи. При этом сохраняются установки, сделанные в окнах диалога, появлявшихся после нажатий на кнопки Параметры, Добавить, Изменить или Удалить.

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

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

3.Ввод и редактирование ограничений

Диалоговые окна изменения и добавления ограничений одинаковы, рис.2.

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

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

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

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

Условные операторы целого и двоичного типа можно применять только при наложении ограничений на изменяемые ячейки.

Рис.2.Диалоговое окно Изменение ограничения.

4. Настройка параметров алгоритма и программы

Настройка параметров алгоритма и программы производится в диалоговом окне Параметры поиска решения , рис. 3.

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

Рис. 3. Диалоговое окно Параметры поиска решения.

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

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

При достижении отведённого временного интервала или при выполнении отведённого числа итераций, на экране появляется диалоговое окно

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

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

Поле Сходимость результатов поиска решения применяется только к нелинейным задачам. Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость , поиск прекращается. Условием сходимости служит дробь из интервала от 0 (нуля) до 1. Лучшую сходимость характеризует большее количество десятичных знаков, например, 0,0001 – это меньшее относительное изменение, чем 0,01. Чем меньше его значение, тем выше точность результатов. Лучшая сходимость требует больше времени на поиск оптимального решения.

Флажок Линейная модель служит для ускорения поиска решения линейной задачи оптимизации или линейной аппроксимации нелинейной задачи.

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

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

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

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

Линейная служит для использования линейной экстраполяции вдоль касательного вектора.

Квадратичная служит для использования квадратичной экстраполяции, которая даёт лучшие результаты при решении нелинейных задач.

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

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

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

Кнопки Метод поиска служат для выбора алгоритма оптимизации (метод Ньютона или сопряжённых градиентов).

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

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

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

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

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

1. Сохранение модели оптимизации

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

Рис. 4. Диалоговое окно Сохранить модель.

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

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

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

Перед тем как использовать данную процедуру, необходимо сохранить хотя бы одну модель.

1 В меню Сервис выберите команду Поиск решения.

2. Нажмите кнопку Параметры.

3. Нажмите кнопку Загрузить модель. Появляется окно, аналогичное окну Сохранить модель.

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

Вычисления и результаты решения задачи

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

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

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

По окончании счёта появляется диалоговое окно Результаты поиска решения, рис. 5.

Рис. 5. Диалоговое окно Результаты поиска решения.

Поле Тип отчёта служит для указания типа отчёта, размещаемого на отдельном листе книги.

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

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

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

К сожалению, эти отчёты очень неудобны. Они перегружены плохо читаемыми абсолютными ссылками со знаками доллара. Желает лучшего и перевод с английского на русский.

Кнопка Сохранить сценарий служит для отображения диалогового окна Сохранение сценария , в котором можно сохранить сценарий решения задачи, чтобы использовать его в дальнейшем с помощью диспетчера сценариев Microsoft Excel. В поле Название сценария введите имя сценария. Чтобы создать сценарий, не сохраняя найденное решение и не отображая результатов на листе, сохраните сценарий в диалоговом окне Результаты поиска решения , а затем выберите Восстановить исходные значения.

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

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

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

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

На экране появится диалоговое окно , рис. 6, а влияющие ячейки листа изменят свои значения.

Чтобы остановить поиск решения и вывести на экран диалоговое окно Результаты поиска решения, нажмите кнопку Стоп.

Рис.6. Диалоговое окно Текущее состояние поиска решения.

Чтобы выполнить следующую итерацию и просмотреть её результаты, нажмите кнопку Продолжить.

Возникающие проблемы и сообщения процедуры поиска решения

Оптимальное решение не найдено

Поиск решения может остановиться до достижения оптимального решения по следующим причинам.

Пользователь прервал процесс поиска.

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

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

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

Значение, заданное в поле Установить целевую диалогового окна Поиск решения , неограниченно увеличивается или уменьшается. Необходимо уменьшить значения полей Максимальное время или Итерации в диалоговом окне Параметры поиска решения.

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

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

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

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

Итоговые сообщения процедуры поиска решения

1. Если поиск решения успешно завершён, в диалоговом окне Результаты поиска решения

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

Все ограничения соблюдены с установленной точностью и найдено заданное значение целевой ячейки.

Поиск свёлся к текущему решению. Все ограничения выполнены.

Относительное изменение значения в целевой ячейке за последние пять итераций стало меньше установленного значения параметра Сходимость в диалоговом окне Параметры поиска решения . Чтобы найти более точное решение, установите меньшее значение параметра Сходимость , но это займёт больше времени.

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

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

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

3. Поиск остановлен (истекло заданное на поиск время).

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

4. Поиск остановлен (достигнуто максимальное число итераций).

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

5. Значения целевой ячейки не сходятся.

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

6. Поиск не может найти подходящее решение.

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

7. Поиск остановлен по требованию пользователя.

Нажата кнопка Стоп в диалоговом окне Текущее состояние поиска решения после прерывания поиска решения в процессе выполнения итераций.

8. Условия для линейной модели не удовлетворяются.

Установлен флажок Линейная модель , однако итоговый пересчёт порождает такие значения, которые не согласуются с линейной моделью. Это означает, что решение недействительно для данных формул листа. Чтобы проверить линейность задачи, установите флажок Автоматическое масштабирование и повторно запустите задачу. Если это сообщение опять появится на экране, снимите флажок Линейная модель и снова запустите задачу.

9. При поиске решения обнаружено ошибочное значение в целевой ячейке или в ячейке ограничения.

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

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

10. Мало памяти для решения задачи.

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

Примеры выполнения задач

ПРИМЕР № 1

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

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

1. Формулировка математической модели задачи :

· переменные для решения задачи: x 1 – суточный объём изготовления продукции А, x 2 – суточный объём изготовления продукции Б, x 3 – суточный объём изготовления продукции В, x 4 – суточный объём изготовления продукции Г;

· определение функции цели (критерия оптимизации). Суммарная суточная прибыль от изготовления всех видов продукции равна:

F=12* x 1 +7* x 2 +18* x 3 +10* x 4,

поэтому цель состоит в том, чтобы среди всех допустимых значений x 1, x 2, x 3, x 4 найти такие, которые максимизируют суммарную прибыль от изготовления продуктов F:

· ограничения на переменные:

1. объём производства продукции не может быть отрицательным, т. е.

2. расход исходного продукта для изготовления всех видов продукции не может превосходить максимально возможного запаса данного исходного продукта, т. е.

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

· Найти максимум следующей функции:

F=12* x 1 +7* x 2 +18* x 3 +10* x 4 max;

· При ограничениях вида:

1* x 1 +2* x 2 +1* x 3 +0* x 4 ≤ 18,

1* x 1 +1* x 2 +2* x 3 +1* x 4 ≤ 30,

1* x 1 +3* x 2 +3* x 3 +2* x 4 ≤ 40,

x 1 ≥ 0, x 2 ≥ 0, x 3 ≥ 0, x 4 ≥ 0;

2. Подготовка листа рабочей книги MS Excel для вычислений – на рабочий лист вводим необходимый текст, данные и формулы в соответствии с рис. 7. Переменные задачи x 1, x 2, x 3, x 4 находятся соответственно в C3, С4, С5, С6 . Целевая функция находится в ячейке С8 и содержит формулу:

12*C3+7*C4+18*C5+10*C6

Ограничения на задачу учтены в ячейках С10:С12.

3. Работа с надстройкой Поиск решения – воспользовавшись командой Сервис | Поиск решения, вводим необходимые данные для рассматриваемой задачи (установка данных в окне Поиск решения приведена на рис. 8). Результат работы по поиску решения помещён на рис. 9 – 14.

Рис. 7. Рабочий лист MS Excel для решения задачи .

Рис. 8. Установка необходимых параметров задачи в окне Поиск решения .

Рис.9. Результаты расчёта надстройки Поиск решения.

Рис. 10. Отчёт по результатам поиска решения.

Рис. 11. Отчёт по устойчивости поиска решения.

Рис. 12. Отчёт по пределам поиска решения.

ВЫВОД : из решения видно, что оптимальный план выпуска предусматривает изготовление продукции видов "А" и "Г". А продукцию видов "Б" и "В" производить не стоит. Полученная Вами прибыль составит 326 усл. ед.

ПРИМЕР № 2

Задача распределения ресурсов

Предприятие изготавливает и продает краску двух видов: для внутренних и внешних работ. Для производства краски используется два исходных продукта A и B. Расходы продуктов A и B на 1 т. соответствующих красок и запасы этих продуктов на складе приведены в таблице:

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

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

I. Составление математической модели задачи.

1) Переменные задачи.

Обозначим: x 1 - количество производимой краски для

внутренних работ;

x 2 - соответствующее количество краски

для наружных работ.

2) Ограничения, которым должны удовлетворять переменные задачи:

по расходу продукта A: x 1 + 2x 2 3;

по расходу продукта B: 3x 1 + x 2 3;

В левых частях последних двух неравенств определены расходы продуктов A и B, а в правых частях неравенств записаны запасы этих продуктов.

3) Целевая функция задачи.

Обозначим Z доход от продажи краски (в тысячах рублей), тогда целевая функция задачи записывается так:

Z = 2x 1 + x 2 ,

таким образом, задача состоит в том, чтобы найти max Z=2x 1 +x 2 , при ограничениях:

x 1 + 2x 2 3 (A)

3x 1 + x 2 3 (B)

x 1 , x 2 0 .

Так как переменные задачи x 1 и x 2 входят в целевую функцию и ограничения задачи линейно , то соответствующая задача оптимизации называется задачей линейного программирования (ЛП)

В рассматриваемом примере содержатся только две переменные x 1 и x 2 , поэтому задачу можно решить графически.

1) На плоскости x 1 , x 2 строим область допустимых значений переменных, определяемую ограничениями задачи:

x 1 + 2x 2 3 (A)

3x 1 + 1x 2 3 (B)

x 1 , x 2 0 .

Последнее ограничение определяет первый квадрант плоскости. Чтобы построить множество точек удовлетворяющих неравенству (А) нанесем на плоскость график прямой, определяющий границу этого множества: x 1 +2x 2 =3 (A).

Линии уровня целевой функции. Линией уровня называется множество точек, на которых функция принимает постоянное значение:

Z = 2x 1 + x 2 = К,

где К - задаваемая постоянная.

При К = 1 уравнение линии уровня будет:

2x 1 + x 2 = 1

или (в отрезках) :

При К = 2, аналогично:

2x 1 + x 2 = 2 , или .

Нанеся линии уровня на область допустимых решений (рис.13), получим, что при увеличении значения Z соответствующая линия уровня перемещается параллельно предыдущей вправо и вверх. Таким образом, точкой из многоугольника ABCD в которой целевая функция Z имеет максимальное значение будет вершина С. Эта точка и определяет решение задачи.

x 1 + 2x 2 = 3 (A)

3x 1 + x 2 = 3 (B)

x 1 * = 0.6 ; x 2 * = 1.2 ;

максимальное значение Z:

Z * = 2*0.6 + 1.2 = 2.4.

Надстройка Поиск решения в Microsoft Excel даёт возможность найти решение, оптимальное при нескольких входных значениях и наборе ограничений на решение. Программа Поиск решения содержит параметры, управляющие процессом поиска решения: максимальное время, число итераций, точность, допустимое отклонение. Каждый из этих параметров имеет значение по умолчанию, подходящее для большинства задач. Использование новых установок параметров обычно необходимо для проведения серьёзных исследований сложных систем управления. Диспетчер сценариев способен запомнить несколько решений, найденных данным средством, и сгенерировать на этой основе отчёт. Надстройка Поиск решения готовит три вида отчётов, которые характеризуют найденное решение задачи: отчёт по результатам, отчёт по устойчивости и отчёт по пределам. Режим пошагового поиска позволяет наблюдать последовательность приближений к оптимальному решению задачи. Во многих случаях это помогает «почувствовать» сходимость процесса и установить причины неудач и тупиков при поиске оптимального решения. В результате поиска решения EXCEL выводит сообщения о том, удалось ли получить оптимальное решение задачи.

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

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

Список литературы

1. Л. В. Рудикова «Microsoft Excel для студента», Санкт – Петербург, БХВ-Петербург, 2005;

2. «Лабораторные работы на персональном компьютере» И. Ф. Цисарь, издательство «Экзамен», Москва, 2002;

3. Додж М. и др. «Эффективная работа с Microsoft Excel», 2000.СПб.:Питер, 2001.

4. Солодовников А. С. «Введение в линейную алгебру и линейное программирование». Москва, издательство «Просвещение», 1966. – 184 с.

5. Стрейвер А. «Теория линейного и целочисленного программирования» в двух томах, том 1: перевод с английского. – Москва: Мир, 1991. – 360 с.

6. Ашманов С.А.«Линейное программирование». - М.: Наука, 1981.

7. Банди Б. «Основы линейного программирования»: Пер. с англ. - М.: Радио и связь, 1989.

8. Кораблин М. А. «Информатика поиска управленческих решений», Москва, СОЛОН-Пресс, 2003.

9. Габасов Р., Кириллова Ф.М. Методы линейного программирования. Ч.1. Общие задачи, Минск, Изд-во БГУ им. В.И. Ленина, 1977. - 176 с.

Размер: px

Начинать показ со страницы:

Транскрипт

1 Министерство образования и науки Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования «Тихоокеанский государственный университет» Решение задач линейного программирования в Microsoft Excel 00 Методические указания к выполнению лабораторных работ по информатике для обучающихся по всем программам бакалавриата и специалитета дневной формы обучения Хабаровск Издательство ТОГУ 05

2 УДК 68.58(076.5) Решение задач линейного программирования в Microsoft Excel 00: методические указания к выполнению лабораторных работ по информатике для обучающихся по всем программам бакалавриата и специалитета дневной формы обучения / сост. Н. Д. Берман, Н. И. Шадрина. Хабаровск: Изд-во Тихоокеан. гос. ун-та, с. Методические указания составлены на кафедре информатики. Включают общие сведения о задачах линейного программирования, задания для выполнения лабораторных работ с вариантами задач, рекомендательный библиографический список. Печатается в соответствии с решениями кафедры информатики и методического совета факультета компьютерных и фундаментальных наук. Тихоокеанский государственный университет, 05

3 . ЗАДАЧИ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ В MICROSOFT EXCEL 00. ОБЩИЕ СВЕДЕНИЯ Общая характеристика задач оптимизации Задачи линейной оптимизации относятся к широко распространённому классу задач, встречающихся в различных сферах деятельности: в бизнесе, на производстве, в быту. Как оптимально распорядиться бюджетом или за минимальное время добраться до нужного места в городе, как наилучшим образом спланировать деловые встречи, минимизировать риски капитальных вложений, определить оптимальные запасы сырья на складе это те задачи, в которых нужно найти наилучшее из всех возможных решений. Различают следующие типы линейных оптимизационных задач: задачи о перевозках, например, минимизация расходов по доставке товаров с нескольких фабрик в несколько магазинов с учетом спроса; задачи распределения рабочих мест, например, минимизация расходов на содержание штата с соблюдением требований, определенных законодательством; управление ассортиментом товаров: извлечение максимальной прибыли с помощью варьирования ассортиментным набором товаров (при соблюдении требований клиентов). Аналогичная задача возникает при продаже товаров с разной структурой затрат, рентабельностью и показателями спроса; замена или смешивание материалов, например, манипуляция материалами с целью снижения себестоимости, поддержания необходимого уровня качества и соблюдения требований потребителей; задача о диете. Из имеющихся в распоряжении продуктов требуется составить такую диету, которая, с одной стороны, удовлетворяла бы минимальным потребностям организма в питательных веществах (белки, жиры, углеводы, минеральные соли, витамины), с другой требовала бы наименьших затрат; задача распределения ресурсов, например, распределение ресурсов между работами таким образом, чтобы максимизировать прибыль, или минимизировать затраты, или определить такой состав работ, который можно выполнить, используя имеющиеся ресурсы, и при этом достичь максимума опре- 3

4 деленной меры эффективности, или рассчитать, какие ресурсы необходимы для того, чтобы выполнить заданные работы с наименьшими издержками. Математическая постановка задачи линейного программирования Рассмотрим наиболее распространенный класс оптимизационных задач задачи линейного программирования. К такому классу относятся задачи, описываемые линейными математическими моделями. Общей задачей линейного программирования называется задача, которая состоит в определении максимального (минимального) значения функции () при условиях: () () () (3) () (4) где заданные постоянные величины и Функция () называется целевой функцией задачи, а условия ()(4) ограничениями задачи. Совокупность чисел (), удовлетворяющих ограничениям задачи, называется допустимым решением. Решение, при котором целевая функция задачи принимает максимальное (минимальное) значение, называется оптимальным. Использование надстройки Excel для решения задач линейного программирования Поиск решения это надстройка EXCEL, которая позволяет решать оптимизационные задачи. Если команда Поиск решения или группа Анализ отсутствует, необходимо загрузить надстройку Поиск решения. 4

5 На вкладке Файл выберите команду Параметры, а затем категорию Надстройки (рис.). Рис. В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти. В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения (рис.) и нажмите кнопку ОК. Рис. Пример решения оптимизационных линейных задач в MS Excel 00 Схема решения задач линейного программирования в MS Excel 00 следующая: 5

6 . Составить математическую модель.. Ввести на рабочий лист Excel условия задачи: а) создать форму на рабочем листе для ввода условий задачи; б) ввести исходные данные, целевую функцию, ограничения и граничные условия. 3. Указать параметры в диалоговом окне Поиск решения. 4. Проанализировать полученные результаты. Рассмотрим решение задачи оптимизации на примере. Пример. Задача определения оптимального ассортимента продукции Предприятие изготавливает два вида продукции П и П, которая поступает в оптовую продажу. Для производства продукции используются два вида сырья А и В. Максимально возможные запасы сырья в сутки составляют 9 и 3 ед. соответственно. Расход сырья на единицу продукции вида П и П табл.. Таблица Сырье Расход сырья на ед. продукции П П Запас сырья, ед. А 3 9 В 3 3 Опыт работы показал, что суточный спрос на продукцию П никогда не превышает спроса на продукцию П более чем на ед. Кроме того, известно, что спрос на продукцию П никогда не превышает ед. в сутки. Оптовые цены единицы продукции равны: 3 д. е. для П и 4 д. е. для П. Какое количество продукции каждого вида должно производить предприятие, чтобы доход от реализации продукции был максимальным? Решение. Построим математическую модель для решения поставленной задачи. Предположим, что предприятие изготовит x единиц продукции П и x единиц продукции П. Поскольку производство продукции ограничено имеющимися в распоряжении предприятия сырьем каждого вида и спросом на данную продукцию, а также учитывая, что количество изготовляемых изделий не может быть отрицательным, должны выполняться следующие неравенства: 6

7 Доход от реализации x единиц продукции П и x единиц продукции П составит Cреди всех неотрицательных решений данной системы линейных неравенств требуется найти такое, при котором функция F принимает максимальное значения F max. Рассматриваемая задача относится к разряду типовых задач оптимизации производственной программы предприятия. В качестве критериев оптимальности в этих задачах могут быть также использованы: прибыль, себестоимость, номенклатура производимой продукции и затраты станочного времени. Создадим на рабочем листе форму для ввода исходных данных (рис. 3). Заливкой выделены ячейки для ввода функций. Рис. 3 В ячейку E5 введем формулу для целевой функции (рис. 4). Используя обозначения соответствующих ячеек в Excel, формулу для расчета целевой функции можно записать как сумму произведений каждой из ячеек, отведенной для значений переменных задачи (B3, C3), на соответствующие ячейки, отведенные для коэффициентов целевой функции (B5, C5). 7

8 Рис. 4 Аналогично в ячейки D0:D введены формулы для расчета левой части ограничений (рис. 5). Рис. 5 На вкладке Данные в группе Анализ выберем команду Поиск решения. В диалоговом окне Параметры поиска решения установим следующее (рис. 6): 8

9 в поле Оптимизировать целевую функцию выбираем ячейку со значением целевой функции Е5; выбираем, максимизировать или минимизировать целевую функцию; в поле Изменяя ячейки переменных выбираем ячейки со значениями искомых переменных B3:C3 (пока в них нули или пусто); в области В соответствии с ограничениями с помощью кнопки Добавить размещаем все ограничения нашей задачи (рис. 7); в поле Выберите метод решения указываем Поиск решения линейных задач симплекс-методом; нажимаем кнопку Найти решение. Рис. 6 9

10 Добавляем ограничения для нашей задачи. Для неравенств указываем в поле Ссылка на ячейки диапазон D0:D, выбираем в раскрывающемся списке знак неравенства, в поле Ограничение выделяем диапазон F0:F и нажимаем кнопку Добавить (рис. 7), чтобы принять ограничение и добавить следующее ограничение. Для принятия ограничения и возврата к диалоговому окну Поиск решения нажмите кнопку Ok. Рис. 7 Покажем окна для добавления ограничений: преобразуем в (рис. 8); Рис. 8 0

11 (рис. 9); Рис. 9, (рис. 0). Рис. 0 После выбора кнопки Найти решение появляется окно Результаты поиска решения (рис.). Рис.

12 Для сохранения полученного решения необходимо использовать переключатель Сохранить найденное решение в открывшемся окне диалога Результаты поиска решения. После чего рабочий лист примет вид, представленный на рис.. Рис. Сохранить модель поиска решения можно следующим образом:) при сохранении книги Excel после поиска решения все значения, введенные в окнах диалога Поиск решения, сохраняются вместе с данными рабочего листа. С каждым рабочим листом в рабочей книге можно сохранить один набор значений параметров Поиска решения;) если в пределах одного рабочего листа Excel необходимо рассмотреть несколько моделей оптимизации (например, найти максимум и минимум одной функции или максимальные значения нескольких функций), то удобнее сохранить эти модели, используя кнопку Загрузить/Сохранить окна Параметры поиска решения. Диапазон для сохраняемой модели содержит информацию о целевой ячейке, об изменяемых ячейках, о каждом из ограничений и все значения диалога Параметры. Выбор модели для решения конкретной оптимизационной задачи осуществляется с помощью кнопки Загрузить/сохранить диалогового окна Параметры поиска решения; 3) сохранить модель можно в виде именованных сценариев, для этого необходимо нажать на кнопку Сохранить сценарий диалогового окна Результаты поиска решений (см. рис.). Кроме вставки оптимальных значений в изменяемые ячейки, Поиск решения позволяет представлять результаты в виде трех отчетов (Результаты,

13 Устойчивость и Пределы). Для генерации одного или нескольких отчетов необходимо выделить их названия в окне диалога Результаты поиска решения (рис.). Рассмотрим более подробно каждый из них. Отчет по устойчивости (рис. 3) содержит информацию о том, насколько целевая ячейка чувствительна к изменениям ограничений и переменных. Этот отчет имеет два раздела: один для изменяемых ячеек, а второй для ограничений. Правый столбец в каждом разделе содержит информацию о чувствительности. Каждая изменяемая ячейка и ограничения приводятся в отдельной строке. При использовании целочисленных ограничений Excel выводит сообщение Отчеты об устойчивости и Пределы не применимы для задач с целочисленными ограничениями. Рис. 3 Отчет по результатам (рис. 4) содержит три таблицы: в первой приведены сведения о целевой функции до начала вычисления, во второй значения искомых переменных, полученные в результате решения задачи, в третьей результаты оптимального решения для ограничений. Этот отчет также содержит информацию о таких параметрах каждого ограничения, как статус и разница. Статус может принимать три состояния: связанное, несвязанное или невыполненное. Значение разницы это разность между значением, выводимым в ячейке ограничения при получении решения, и числом, заданным в правой части формулы ограничения. Связанное ограничение это ограничение, для которого значение разницы равно нулю. Несвязанное 3

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

15 Полученное решение означает, что объем производства продукции вида П должен быть равен,4 ед., а продукции П,4 ед. продукции. Доход, получаемый в этом случае, составит,8 д. е. Допустим, что к условию задачи добавилось требование целочисленности значений всех переменных. В этом случае описанный выше процесс ввода условия задачи необходимо дополнить следующими шагами. В окне Поиск решения нажмите кнопку Добавить и в появившемся окне Добавление ограничений введите ограничения следующим образом (рис. 6): в поле Ссылка на ячейки введите адреса ячеек переменных задачи B3:C3; в поле ввода знака ограничения установите целое; подтвердите ввод ограничения нажатием кнопки OK. Рис. 6 Решение задачи при условии целочисленности ее переменных рис. 7. Рис. 7 5

16 . ЛАБОРАТОРНЫЕ РАБОТЫ Лабораторная работа Задание Найти максимум линейной функции при заданной системе ограничений. Вариант Целевая функция F Ограничения { { { { 3 { { 4 { { 5 { { 6 { { 7 { { 8 { { 9 { { 0 { { { { { { 3 { { 4 { { 5 { { 6

17 Лабораторная работа Задание. Построить математическую модель задачи.. Представить ее в табличной форме на листе Excel. 3. Найти решение задачи средствами надстройки Поиск решения. 4. Вывести отчеты по результатам и устойчивости. Вариант Для производства столов и шкафов мебельная фабрика использует необходимые ресурсы. Нормы затрат ресурсов на одно изделие данного вида, прибыль от реализации одного изделия и общее количество имеющихся ресурсов каждого вида табл.. Таблица Ресурсы Древесина, м 3: -го вида -го вида Нормы затрат ресурсов на одно изделие Стол Шкаф 0, 0, 0, 0,3 Общее количество ресурсов Трудоемкость, чел.ч,5 37,4 Прибыль от реализации одного изделия, р. 6 8 Определить, сколько столов и шкафов следует изготавливать фабрике, чтобы прибыль от их реализации была максимальной. Ответ. Прибыль 940 р. при количестве столов и шкафов 0 и 66. Вариант Для производства двух видов изделий A и В используется токарное, фрезерное и шлифовальное оборудование. Нормы затрат времени для каждого из типов оборудования на одно изделие данного вида, общий фонд рабочего времени каждого из типов оборудования, а также прибыль от реализации одного изделия табл. 3. 7

18 Таблица 3 Затраты времени, стан.-ч, Тип оборудования на обработку одного изделия А В Фрезерное 0 8 Токарное 5 0 Шлифовальное 6 Прибыль от реализации одного изделия, р. 4 8 Общий фонд полезного рабочего времени оборудования, ч Найти план выпуска изделий А и В, обеспечивающий максимальную прибыль от их реализации. Ответ. Прибыль 76 р. при выпуске изделий и 6. Вариант 3 Для изготовления трех видов изделий А, В и С используется токарное, фрезерное, сварочное и шлифовальное оборудование. Затраты времени на обработку одного изделия для каждого из типов оборудования, общий фонд рабочего времени каждого из типов используемого оборудования, прибыль от реализации одного изделия данного вида табл. 4. Таблица 4 Тип оборудования Фрезерное Токарное Сварочное Шлифовальное Затраты времени, стан.-ч, на обработку одного изделия вида А В С Прибыль, р. 0 4 Общий фонд рабочего времени оборудования, ч Требуется определить, сколько изделий и какого вида следует изготовить предприятию, чтобы прибыль от их реализации была максимальной. Ответ. Прибыль 49 р. при выпуске изделий 4, 8, 0. 8

19 Вариант 4 Для поддержания нормальной жизнедеятельности человеку ежедневно необходимо потреблять не менее 8 г белков, 56 г жиров, 500 г углеводов, 8 г минеральных солей. Количество питательных веществ, содержащихся в кг каждого вида потребляемых продуктов, а также цена кг каждого из этих продуктов табл. 5 Таблица 5 Питательные вещества Содержание, г, питательных веществ в кг продуктов Мясо Рыба Молоко Масло Сыр Крупа Картофель Белки Жиры Углеводы Минеральные соли Цена кг продуктов, р.,8,0 0,8 3,4,9 0,5 0, Составить дневной рацион, содержащий не менее минимальной суточной нормы потребности человека в необходимых питательных веществах при минимальной общей стоимости потребляемых продуктов. Ответ. Минимальная общая стоимость 0, р. при количестве продуктов: мясо 0; рыба 0; молоко 0; масло 0,03335; сыр 0; крупа 0,9053; картофель 0. Вариант 5 Кондитерская фабрика для производства трех видов карамели А, В, и С использует три вида основного сырья: сахарный песок, патоку и фруктовое пюре. Нормы расхода сырья каждого вида на производство т карамели данного вида, общее количество сырья каждого вида, прибыль от реализации т карамели табл. 6. 9

20 Таблица 6 Вид сырья Сахарный песок Патока Фруктовое пюре Нормы расхода сырья, т, на т карамели А В С 0,8 0,4 0,5 0,4 0, 0,6 0,3 0, Прибыль от реализации т продукции, р Общее количество сырья, т Найти план производства карамели, обеспечивающий максимальную прибыль от ее реализации. Ответ. Максимальная прибыль р. при выпуске карамели 00, 0, 00 т. Вариант 6 На швейной фабрике для изготовления четырех видов изделий может быть использована ткань трех артикулов. Нормы расхода тканей всех артикулов на пошив одного изделия, имеющееся в распоряжении фабрики общее количество тканей каждого артикула и цена одного изделия данного вида табл. 7. Таблица 7 Артикул ткани I II III Норма расхода ткани, м, на одно изделие вида 3 4 Цена одного изделия, р Общее количество ткани, м Определить, сколько изделий каждого вида должна произвести фабрика, чтобы стоимость изготовленной продукции была максимальной. Ответ. Максимальная стоимость продукции 5 р. при выпуске изделий 95, 0, 0, 0. 0

21 Вариант 7 Предприятие выпускает четыре вида продукции и использует три типа основного оборудования: токарное, фрезерное и шлифовальное. Затраты времени на изготовление единицы продукции для каждого из типов оборудования, общий фонд рабочего времени каждого из типов оборудования и прибыль от реализации одного изделия данного вида табл. 8. Таблица 8 Затраты времени, стан.-ч, Тип оборудования на единицу продукции вида 3 4 Токарное Фрезерное Шлифовальное Прибыль от реализации 3 единицы продукции, р. 8 3 Общий фонд рабочего времени, стан.-ч Определить такой объем выпуска каждого из изделий, при котором общая прибыль от их реализации является максимальной. Ответ. Максимальная прибыль 965 р. при выпуске изделий 70, 35, 0, 0. Вариант 8 Торговое предприятие планирует организовать продажу четырех видов товара, используя при этом только два вида ресурсов: рабочее время продавцов в количестве 840 ч и площадь торгового зала 80 м. При этом известны плановые нормативы затрат этих ресурсов в расчете на единицу товаров и прибыль от их продажи табл. 9. Таблица 9 Показатели Расход рабочего времени на единицу товара, ч Использование площади торгового зала на единицу товара, м Товар А В С D 0,6 0,8 0,6 0,4 0, 0, 0,4 0, Прибыль от продажи единицы товара, р Общее количество ресурсов

22 Требуется определить оптимальную структуру товарооборота, обеспечивающую торговому предприятию максимальную прибыль. Ответ. Максимальная прибыль 6 00 р. при продаже товаров 0, 0, 0, 800. Вариант 9 Из трех видов сырья необходимо составить смесь, в состав которой должно входить не менее 6 ед. химического вещества А, 30 ед. вещества В и 4 ед. вещества С. Количество единиц химического вещества, содержащегося в кг сырья каждого вида, цена кг сырья каждого вида табл. 0 Таблица 0 Вещество А В С Цена кг сырья, р. Количество единиц вещества, содержащегося в кг сырья вида Составить смесь, содержащую не менее нужного количества веществ данного вида и имеющую минимальную стоимость. Ответ. Минимальная стоимость 6 р. при количестве 0; 0; 0; 6,5 кг. Вариант 0 Для производства трех видов продукции предприятие использует два типа технологического оборудования и два вида сырья. Нормы затрат сырья и времени на изготовление одного изделия каждого вида, общий фонд рабочего времени каждой из групп технологического оборудования, объемы имеющегося сырья каждого вида, цена одного изделия каждого вида, ограничения на возможный выпуск каждого из изделий табл..

23 Ресурсы Производительность оборудования в нормочасах: I типа II типа Сырье, кг: -го вида -го вида Цена одного изделия, р. Выпуск, шт.: минимальный максимальный Нормы затрат на одно изделие вида Таблица Общее количество ресурсов Составить план производства продукции, по которому будет изготовлено необходимое количество изделий каждого вида, при максимальной общей стоимости всей изготовляемой продукции. Ответ. Общая стоимость 495 р. при выпуске продукции 0, 33, 45. Вариант При производстве четырех видов кабеля выполняется пять групп технологических операций. Нормы затрат на км кабеля данного вида для каждой из групп операций, прибыль от реализации км каждого вида кабеля, а также общий фонд рабочего времени, в течение которого могут выполняться эти операции, табл. Таблица Технологическая операция Нормы затрат времени, ч, на обработку км кабеля вида 3 4 Волочение Наложение изоляций Скручивание элементов в кабель Освинцовывание Испытание и контроль,0 6,4 3,0,8 0,4 5,6,5,6 0,8 6,0,8 0,8,4 0,7 8,0,4 3,0 Прибыль от реализации км кабеля, р., 0,8,0,3 Общий фонд рабочего времени, ч

24 Определить план выпуска кабеля, при котором общая прибыль от реализации изготовляемой продукции является максимальной. Ответ. Общая прибыль от реализации 939,48 57 р. при выпуске 00; 64,8 57; 0; 0. Вариант Стальные прутья длиной 0 см необходимо разрезать на заготовки длиной 45, 35 и 50 см. Требуемое количество заготовок данного вида составляет соответственно 40, 30 и 0 шт. Возможные варианты разреза и величина отходов при каждом из них табл. 3. Таблица 3 Варианты разреза Длина заготовки, см Величина отходов, см Определить, сколько прутьев по каждому из возможных вариантов следует разрезать, чтобы получить не менее нужного количества заготовок каждого вида при минимальных отходах. Ответ. Минимальные отходы равны 550 см при количестве прутьев 0, 0, 0, 0, 0, 0 шт. Вариант 3 Для производства трех видов изделий А, В, С предприятие использует четыре вида сырья. Нормы затрат сырья каждого вида на производство единицы продукции данного вида, прибыль от реализации одного изделия каждого вида табл. 4. 4

25 Таблица 4 Нормы затрат сырья, кг, на единицу продукции Вид сырья А В С I II III IV Прибыль от реализации одного изделия Изделия А, В и С могут производиться в любых соотношениях (сбыт обеспечен), но для их производства предприятие может использовать сырье I вида не более 00 кг, II вида не более 0 кг, III вида не более 80 кг, IV вида не более 38 кг. Определить план производства продукции, при котором общая прибыль предприятия от реализации всей продукции была бы наибольшей. Ответ. План производства изделий 7, 5, 0 кг при общей прибыли 5 кг. Вариант 4 Туристическое агентство собирается заказать издательству выпуск художественных альбомов трех типов A, B, C. Их изготовление лимитируется затратами ресурсов трех видов, удельные расходы которых приведены в табл. 5. Вид ресурса Финансы, $ Бумага, л. Трудозатраты, чел. ч Таблица 5 Удельные затраты ресурсов на выпуск альбомов A B C 4 4 Издательство для выполнения заказа получило финансовые средства в объеме $ 3 600, имеет в наличии л. бумаги и может использовать трудовые ресурсы в объеме 00 чел. ч. Агентство платит за выпуск одного альбома типа А дол., за альбом В 8 дол., за альбом С 30 дол. 5

26 Сколько альбомов каждого типа должно выпустить издательство, чтобы получить наибольшую прибыль? Ответ. Максимальный суммарный доход дол., количество альбомов: 400; 800; 0 шт. Вариант 5 Предприятие оптовой торговли может реализовать T j, j, 4 группы товаров. Для этого используется несколько видов ресурсов. Исходные данные для построения математической модели табл. 6. Лимитирующие ресурсы и показатели Товарная группа T T T 3 T4 Объем ресурса Таблица 6 Складские площади, м Трудовые ресурсы, чел.ч Издержки обращения, ден. ед Товарные запасы, ден. ед План товарооборота, ден. ед Минимально допустимые значения товарооборота по j-й группе, ед. Прибыль в расчете на единицу товарооборота j-й группы, ден. ед. Вид огра ниче- ния Требуется рассчитать план хозяйственной деятельности торгового предприятия, обеспечивающий максимум прибыли при заданных ограничениях на складские площади, трудовые ресурсы, издержки обращения, товарные запасы, величину товарооборота, если торговая прибыль в расчете на единицу товарооборота j -й группы задана. Ответ. Максимальна прибыль ден. ед. Товарооборот по группам: Т 00 ед., Т 000 ед., Т ед., Т ед. 6

27 3. РЕКОМЕНДАТЕЛЬНЫЙ БИБЛИОГРАФИЧЕСКИЙ СПИСОК. Акулич, И. Л. Математическое программирование в примерах и задачах: учеб. пособие для студентов экон. спец. сузов / И. Л. Акулич. М. : Высш. шк., с.. Леоненков, А. В. Решение задач оптимизации в среде MS Excel / А. В. Леоненков. СПб. : БХВ-Петербург, с. 3. Васильев, А. Н. Финансовое моделирование и оптимизация средствами Excel007 / А. Н. Васильев. СПб. : Питер, с. 4. Уокенбах, Дж. Microsoft Excel 00. Библия пользователя: пер. с англ. / Дж. Уокенбах. М. : И. Д. Вильямс, 0. 9 с. 5. Уокенбах, Дж. Формулы в Microsoft Excel 00: пер. с англ. / Дж. Уокенбах. М. : И. Д. Вильямс, с. 6. Иванов, И. Microsoft Excel 00 для квалифицированного пользователя / И. Иванов. М. : Академия АЙТИ, с. 7. Справка и инструкции по Excel // Поддержка по Microsoft Office [Электронный ресурс]. Режим доступа: (дата обращения:). 8. Решение задач оптимизации управления с помощью MS Excel 00 // НОУ «ИНТУИТ» [Электронный ресурс]. Режим доступа: (дата обращения:). Оглавление. Задачи линейного программирования в Microsoft Excel 00. Общие сведения... 3 Общая характеристика задач оптимизации... 3 Математическая постановка задачи линейного программирования... 4 Использование надстройки Excel для решения задач линейного программирования... 4 Пример решения оптимизационных линейных задач в MS Excel Лабораторные работы... 6 Лабораторная работа... 6 Лабораторная работа Рекомендательный библиографический список

28 Решение задач линейного программирования в Microsoft Excel 00 Методические указания к выполнению лабораторных работ по информатике для обучающихся по всем программам бакалавриата и специалитета дневной формы обучения Нина Демидовна Берман Нина Ивановна Шадрина Главный редактор Л. А. Суевалова Редактор Е. Н. Ярулина Подписано в печать Формат 60 x 84 / 6. Бумага писчая. Гарнитура «Калибри». Печать цифровая. Усл. печ. л.,68. Тираж 60 экз. Заказ 70. Издательство Тихоокеанского государственного университета, Хабаровск, ул. Тихоокеанская, 36. Отдел оперативной полиграфии издательства Тихоокеанского государственного университета, Хабаровск, ул. Тихоокеанская, 36. 8

ОБЪЕМНОЕ ПЛАНИРОВАНИЕ РАБОТЫ ТЕХНОЛОГИЧЕСКИХ СТАНОЧНЫХ СИСТЕМ Х а б а р о в с к 2 0 0 9 Федеральное агентство по образованию Государственное образовательное учреждение высшего профессионального образования

Практическое занятие 3. 1. Для данных условий сформулируйте оптимизационную задачу, составьте математическую модель, найдите оптимальный план производства с помощью надстройки «Поиск решения» в EXCEL.

Министерство образования и науки Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего образования «Тихоокеанский государственный университет» Н. И. Шадрина, Н.

Составление, решение и анализ задачи линейного программирования в Excel ЗАДАНИЕ. Построить математическую модель задачи и решить её средствами Excel. Записать сопряжённую задачу. Провести анализ и сделать

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

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

Лабораторная работа 3_9. Поиск и принятие решений в Excel. Что осваивается и изучается? Решение задачи определения оптимального плана и транспортной задачи при помощи надстройки «Поиск решения». Задание

Лабораторная работа 3. Поиск решения в Microsoft Excel Целью лабораторной работы является изучение возможностей средства Поиск решения MS Excel для решения оптимизационных задач. К защите лабораторной

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬ- НОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ «ДОНСКОЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ» Кафедра «Технология

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ОБРАЗОВАНИЯ «ТИХООКЕАНСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ» Совместная работа

ЛАБОРАТОРНАЯ РАБОТА СРЕДСТВА ПОДДЕРЖКИ ПРИНЯТИЯ РЕШЕНИЙ КАК ФУНКЦИИ EXCEL Команда Подбор параметра Задание 1. Рассмотрим задачу, составленную на основании задачи по использованию функции ЧПС. Вас просят

ВАРИАНТ Для изготовления изделий двух видов имеется 00 кг металла. На одно изделие -го вида расходуется кг металла, а изделия -говида кг. Составить план производства, обеспечивающий получение наибольшей

Лабораторная работа 4 Тема работы: Решение задачи об оптимальном распределении ресурсов при выпуске продукции с использованием процедуры Поиск решения Microsoft Excel. Цель работы: Научиться использовать

Практическая работа 5.4. Решение задачи об оптимальном распределении ресурсов при выпуске продукции с использованием процедуры «Поиск решения» Microsoft Excel Цель работы. Выполнив эту работу, Вы научитесь:

Московская Государственная Академия Тонкой Химической Технологии имени М. В. Ломоносова Корнюшко В.Ф., Морозова О.А. Детерминированные модели экономических систем Методическое пособие по дисциплине Математические

МИНИСТЕРСТВО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ КУРГАНСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ КАФЕДРА «ИНФОРМАТИКА» РЕАЛИЗАЦИЯ ОПТИМИЗАЦИОННЫХ МОДЕЛЕЙ В СРЕДЕ EXCEL Методические указания к проведению лабораторных

Оптимизация производственной программы Методические указания к лабораторной работе по экономике электротехнической промышленности Ульяновск 009 В 9 Васильев, В. Н. Оптимизация производственной программы

Экономико-математические методы и моделирование. Практическая работа 2. Симплексный метод решения задач линейного программирования. Решить задачу линейного программирования (ЛП) симплексным методом. Расчеты

РАБОТА 2 РЕШЕНИЕ ЗАДАЧ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ Цель работы: ознакомление с методами решения задач линейного программирования в табличном процессоре Ecel. Решение экономических задач, как правило, связано

ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ Государственное образовательное учреждение высшего профессионального образования «Тихоокеанский государственный университет» Кафедра «Технология деревообработки» МОДЕЛИРОВАНИЕ

АНАЛИЗ ДАННЫХ В MS EXCEL Гедранович Валентина Васильевна 27 июня 2012 г. Аннотация Глава 11 из УМК: Гедранович, В.В. Основы компьютерных информационных технологий: учеб.-метод. комплекс / В.В. Гедранович,

Решение задачи линейного программирования графическим методом, симплекс-методом и через «Поиск решения» в Ecel ЗАДАНИЕ. Предприятие выпускает два вида продукции: Изделие и Изделие. На изготовление единицы

Лабораторная работа 3. Надстройка Поиск решения в Microsoft Excel. Диспетчер сценариев в Microsoft Excel. Целью данной лабораторной работы является изучение возможностей средства Поиск решения в Microsoft

Негосударственное образовательное частное учреждение высшего профессионального образования Уральский институт фондового рынка Кафедра Экономики предприятия ЭКОНОМИКА ФИРМЫ Сборник кейсов тема «Планирование

Практическое занятие 4. Для условий задачи cформулируйте двойственную задачу и найдите объективно обусловленные оценки. Проанализируйте использование ресурсов в оптимальном плане. Вариант 1. Для изготовления

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ федеральное государственное бюджетное образовательное учреждение высшего профессионального образования «Курганский государственный университет» Кафедра

ЛАБОРАТОРНАЯ РАБОТА 6 Тема: Анализ данных в OpenOffice Calc 1. Основные понятия Процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул в OpenOffice.org Calc называется

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

2 ПЛАН ЛЕКЦИИ: АНАЛИЗ ДАННЫХ В MS EXCEL Информатика 2 семестр Кондратенко Ольга Брониславовна [email protected] Инструмент анализа «что если» Инструмент анализа «что если» создание таблиц данных с одной

Практическая работа 13 Тема: ЗАДАЧИ ОПТИМИЗАЦИИ (ПОИСК РЕШЕНИЯ) В MICROSOFT EXCEL Цель занятия. Изучение технологии поиска решения для задач оптимизации (минимизации, максимизации). Задание 13.1. Минимизация

Приложение Содержимое кейса Задача 1 Одна вновь организованная коммерческая фирма решила выпускать два типа стульев х1 и х2. Для их производства необходимо два вида материалов: дерево и ткань. Фирма ежемесячно

ЛАБОРАТОРНАЯ РАБОТА 2 ИСПОЛЬЗОВАНИЕ MICROSOFT EXCEL 2007 ПРИ РЕШЕНИИ ПРАКТИЧЕСКИХ ЗАДАЧ (ДЛЯ СТУДЕНТОВ НАПРАВЛЕНИЯ 100800.62) 2.1 Решение задач оптимизации Задача. Завод производит электронные приборы

МОСКОВСКИЙ РАДИОТЕХНИЧЕСКИЙ КОЛЛЕДЖ им. А.А.Расплетина ЛАБОРАТОРНАЯ РАБОТА По предмету «Математические методы» «Двухиндексные задачи линейного программирования» Составил: Преподаватель МРТК им.а.а.расплетина

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ федеральное государственное автономное образовательное учреждение высшего образования «НАЦИОНАЛЬНЫЙ ИССЛЕДОВАТЕЛЬСКИЙ ТОМСКИЙ ПОЛИТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ» УТВЕРЖДАЮ

СОДЕРЖАНИЕ. ЗАДАНИЕ.... ЭТАПЫ РАБОТЫ..... Формирование математической модели задачи..... Решение прямой задачи симплекс-методом..... Построение двойственной задачи... 6.4. Решение прямой и двойственной

ЛАБОРАТОРНАЯ РАБОТА РЕШЕНИЕ ЗАДАЧ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ С ИСПОЛЬЗОВАНИЕМ Microsoft Ecel ЦЕЛЬ РАБОТЫ Приобретение навыков решения задач линейного программирования (ЛП) в табличном редакторе Microsoft

МИНОБРНАУКИ РОССИИ Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования «САМАРСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ» Кафедра «Технология машиностроения»

Министерство образования и науки Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего образования «НИЖЕГОРОДСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ ИМ. Р.

Тверь Реферат Сервис Содержание Задача 1. Ассортимент продукции... 3 Условие задачи... 3 Математическая постановка задачи... 3 Табличная модель задачи... 5 Отчет о результатах решения задачи 1.... 6 Вывод...

ЗАДАНИЕ ПРАКТИЧЕСКОЙ РАБОТЫ 4 И ПРАКТИЧЕСКОЙ РАБОТЫ 5 Задачи линейной оптимизации Построение экономико-математических моделей (ЭММ). Решение задач линейной оптимизации с использованием информационных технологий.

ЛАБОРАТОРНЫЕ РАБОТЫ ПО MS EXCEL 2007 ЛАБОРАТОРНАЯ РАБОТА 1.... 1 ЛАБОРАТОРНАЯ РАБОТА 2... 3 ЛАБОРАТОРНАЯ РАБОТА 3... 4 ЛАБОРАТОРНАЯ РАБОТА 4... 7 ЛАБОРАТОРНАЯ РАБОТА 5... 8 ЛАБОРАТОРНАЯ РАБОТА 6... 10

Федеральное агентство по образованию Государственное образовательное учреждение высшего профессионального образования Ульяновский государственный технический университет ИНФОРМАЦИОННЫЕ СИСТЕМЫ В ЭКОНОМИКЕ

1 Лабораторная работа 3 Решение задач. Подбор параметров, поиск решения 1. Реализация математической модели в Excel Математическая модель это описание состояния поведения некоторой реальной системы (объекта,

Gnumeric: электронная таблица для всех И.А.Хахаев, 2007-2010 7 Линейная оптимизация (поиск решения) 7.1 Оптимизация как задача линейного программирования Пусть имеется функция, называемая целевой, линейно

ФЕДЕРАЛЬНОЕ АГЕНТСТВО ЖЕЛЕЗНОДОРОЖНОГО ТРАНСПОРТА Государственное образовательное учреждение высшего профессионального образования «МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ПУТЕЙ СООБЩЕНИЯ» Институт экономики

МИНОБРНАУКИ РОССИИ Федеральное государственное бюджетное образовательное учреждение высшего образования «Самарский государственный технический университет» ИНЖЕНЕРНО-ЭКОНОМИЧЕСКИЙ ФАКУЛЬТЕТ КАФЕДРА ЭКОНОМИКИ

ЗАНЯТИЕ ПРИБЛИЖЕННОЕ РЕШЕНИЕ НЕЛИНЕЙНЫХ УРАВНЕНИЙ Отделение корней Пусть дано уравнение f () 0, () где функция f () C[ a; Определение Число называется корнем уравнения () или нулем функции f (), если

Министерство образования и науки Российской Федерации Федеральное агентство по образованию Саратовский государственный технический университет РЕШЕНИЕ ЗАДАЧ ОПТИМИЗАЦИИ В СРЕДЕ MS EXCEL Методические указания

«Юго-Западный государственный университет» ЮЗГУ) Кафедра конструирования и технологии электронновычислительных средств МЕТОДЫ УСЛОВНОЙ ОПТИМИЗАЦИИ Методические указания по выполнению лабораторной работы

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования «Тихоокеанский государственный университет»

ФЕДЕРАЛЬНОЕ АГЕНТСТВО ЖЕЛЕЗНОДОРОЖНОГО ТРАНСПОРТА ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ «МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ПУТЕЙ СООБЩЕНИЯ» (МИИТ)

МИНОБРНАУКИ РОССИИ Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования «Самарский государственный технический университет» (ФГБОУ ВПО «СамГТУ») Кафедра

Министерство образования и науки РФ Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования Уральский государственный лесотехнический университет Кафедра

Лабораторная работа 4 «Электронные таблицы Excel и автоматизация вычислений на ПК» РАЗДЕЛ 4. Решение систем уравнений и оптимизационных задач. Вычислительные возможности программы Excel достаточно широки,

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

ФЕДЕРАЛЬНОЕ АГЕНСТВО ЖЕЛЕЗНОДОРОЖНОГО ТРАНСПОРТА ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ «МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ПУТЕЙ СООБЩЕНИЯ

АНАЛИЗ УСТОЙЧИВОСТИ КОММЕРЧЕСКОЙ ДЕЯТЕЛЬНОСТИ ПРЕДПРИЯТИЯ Дегтярёва Нина Адамовна, к.э.н., доцент Коммерческая работа - это деятельность предприятия, направленная на решение особого комплекса задач. Изучение

ЛАБОРАТОРНАЯ РАБОТА 2 РЕШЕНИЕ ЗАДАЧ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ 1. Цели работы: построение математической модели задачи линейного программирования; решение задачи линейного программирования графическим