Линейное программирование в 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 и др. Однако, если расчеты осуществляются вручную, удобно использовать так называемые симплексные таблицы.

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

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

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

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

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

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

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

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

Этап А.

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

Этап В.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Этап С.

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

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

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

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

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

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

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

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

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

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

Цель: научиться решать задачи линейного программирования в Excel с помощью надстройки «Поиск решения».

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

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

Постановка задачи линейного программирования (ЗЛП).

Имеется множество переменных X= (x 1 , х 2 ,..., х n). Целевая функция линейно зависит от управляемых параметров:

Имеются ограничения, которые представляют собой линейные формы

где (2)

Требуется определить максимум (минимум) линейной функции

при условии, что точка (х 1 , х 2 ,..., х n) принадлежит некоторому множеству D, которое определяется системой линейных неравенств

(4)

Любое множество значений (х 1 *, х 2 *,..., х n *), которое удовлетворяет системе неравенств (4) задачи линейного программирования, является допустимым решением данной задачи. Если при этом выполняется неравенство

c 1 х 1 o + c 2 х 2 o +..+ c n х n o ≥ c 1 х 1 + c 2 х 2 +..+ c n х n

для всего множества значений x 1 , х 2 ,..., х n , то значение х 1 o ..х n o является оптимальным решением задачи линейного программирования.

Пример построения математической модели и решения ЗЛП.

Задача. Требуется определить, в каком количестве надо выпускать продукцию четырех типов A, B, C иD, для изготовления которой требуются ресурсы трех видов: трудовые, сырье и финансы. Количество ресурса каждого вида, необходимое для выпуска единицы продукции данного типа, называется нормой расхода. Нормы расхода, а также прибыль, получаемая от реализации единицы каждого типа продукции, приведены в таблице1. Там же приведено наличие располагаемого ресурса.

Таблица1.

Ресурс	A	B	C	D	знак	наличие
трудовые

Составим математическую модель, для чего введем следующие обозначения:

x i - количество выпускаемой продукции i-го типа, i = 1,2,3,4

b j – количество располагаемого ресурса j-го вида, j = 1,2,3

a ji – норма расхода j-го ресурса для выпуска i-ой продукции

c i – прибыль от реализации единицы продукции i-го типа.

Как видно из таблицы 1, для выпуска единицы продукции A требуется 6 единиц сырья, значит, для выпуска всей продукции A требуется 6x 1 единиц сырья, где x 1 - количество выпускаемой продукции A . С учетом того, что для других видов продукции зависимости аналогичны, ограничение по сырью будет иметь вид:

6x 1 + 5x 2 + 4x 3 + 3x 4 ≤ 110

В этом ограничении левая часть равна величине требуемого ресурса, а правая часть показывает количество имеющегося ресурса.

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

x 1 + x 2 + x 3 + x 4 ≤ 16

6x 1 + 5x 2 + 4x 3 + 3x 4 ≤ 110

4x 1 + 6x 2 + 10x 3 + 13x 4 ≤ 100

x i ≥ 0, i=1,2,3,4

1. Для ввода условий задачи создадим форму в Excel (рис.1). В ячейках B3:E3 будут отображаться вычисленные значения x i .

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

2. Введем коэффициенты целевой функции и ограничений в форму. Из математической модели введем зависимости. Введенные данные отображены на рис.2.

рис.2. Исходные данные задачи

В ячейке F6 записана формула целевой функции, в F9-F11- левые части ограничений из математической модели. На рис. 3 отображен режим представления формул. Перейти к данному режиму можно с помощью последовательности действий: нажмите кнопку Microsoft Office , щелкните Параметры Excel, откройте вкладку Дополнительно и установите флажок Показывать формулы, а не их значения.

рис.3. Режим представления формул.

3. Загрузим надстройку поиск решения Данные │Анализ │Поиск решения .

4. В поле Установить целевую ячейку введем ссылку на целевую ячейку, для чего установим курсор в поле и щелкнем левой кнопкой мыши по ячейке F6.

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

6. Установим курсор в поле Изменяя ячейки и введем с помощью мыши имена изменяемых ячеек B3:E3. В этих ячейках в результате поиска решения будет выведено решение – значения переменных x i ., при которых целевая функция имеет максимальное значение при заданных ограничениях.

7. Введем ограничения на искомые переменные: x i ≥ 0 (нижняя граница по умолчанию равна 0, количество выпускаемой продукции не может быть отрицательным). Так же введем ограничения на ресурсы (н е может быть использовано больше ресурсов, чем их запасы). Щелкнем по кнопке Добавить , в появившемся окне Добавление ограничения в левом поле с помощью мыши введем ссылку на ячейку B3, из раскрывающегося списка выберем знак ≥, в правом поле щелкнем мышью по ячейкеB4 (рис.4). Аналогично введем остальные ограничения.

Рис.4. Окно добавления ограничений.

На рисунке 5 показано заполненное окно Поиск решения.

Рис.5 Заполненное окно Поиск решения

8. Далее нажимаем на кнопку Выполнить. Появляется диалоговое окно Результаты поиска решения (рис.6). Решение найдено. Все ограничения и условия оптимальности выполнены. Сохраняем найденное решение. В этом окне также можно получить три вида отчетов: по результатам, устойчивости и пределам, отчеты формируются в новых рабочих листах.

рис.6. Окно Результаты поиска решения

Результаты оптимального решения задачи приведены в таблице (рис.7).

рис.7. Результаты оптимального решения

Таким образом, получилось оптимальное решение (10;0;6;0), т.е. целесообразно выпускать 10 единиц продукции А и 6 единиц продукции С. Максимальная прибыль равна 1320 денежным единицам, при этом используются все трудовые и финансовые ресурсы, 84 единиц сырья, в запасе остается 26 единиц сырья.

Задания для лабораторной работы.

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

Для перевозки грузов используются машины типов А и Б. Грузоподъемность машин обоих типов одинаковая и равна h т. За одну ходку машина А расходует а 11 кг смазочных материалов и а 12 л горючего, машина Б - а 21 кг смазочных материалов иа 22 л горючего. На базе имеется d 1 кг смазочных материалов и d 2 л горючего. Прибыль от перевозки одной машины А составляет с 1 руб., машины Б - с 2 руб. Необходимо перевезти H т груза (исходные данные приведены в нижеследующей таблице).

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

№ варианта

Инструкция по выполнению лабораторной работы.

1. Изучить теоретический материал.
2. Выполнить приведенный пример.
3. Выбрать свой вариант по последней цифре.
4. Составить математическую модель задачи.
5. Найти оптимальное решение с помощью Поиска решения.
6. Сделать выводы по полученным решениям, сформировать отчеты по результатам решения, устойчивости и пределам.
7. Создать отчет по лабораторной работе.

1. Титульный лист.
2. Словесная постановка задачи.
3. Математическая формулировка задачи.
4. Заполненное окно Поиск решения
5. Результаты поиска решения (таблица).
6. Выводы по полученным решениям.

Список источников

1. Гельман В.Я. Решение математических задач средствами Excel: Практикум. – СПб.:Питер, 2003
2. Курицкий Б.Я. Поиск оптимальных решений средствами Excel. – СПб.: BHV-Санкт-Петербург, 1997
3. Пазюк К.Т. Математические методы и модели в экономике. – Хабаровск: Издательство ХГТУ, 2002
4. Джон Уокенбах. MS OfficeExcel 2007 - Библия пользователя, Издатель: Вильямс, 2008

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Равная: max (min)

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

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

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

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

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

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

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

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

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

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

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

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

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

Переменные

Ограничения

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

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

Задача. Николай Кузнецов управляет небольшим механическим заводом. В будущем месяце он планирует изготавливать два продукта (А и В), по которым удельная маржинальная прибыль оценивается в 2500 и 3500 руб., соответственно. Изготовление обоих продуктов требует затрат на машинную обработку, сырье и труд. На изготовление каждой единицы продукта А отводится 3 часа машинной обработки, 16 единиц сырья и 6 единиц труда. Соответствующие требования к единице продукта В составляют 10, 4 и 6. Николай прогнозирует, что в следующем месяце он может предоставить 330 часов машинной обработки, 400 единиц сырья и 240 единиц труда. Технология производственного процесса такова, что не менее 12 единиц продукта В необходимо изготавливать в каждый конкретный месяц. Необходимо определить количество единиц продуктов А и В, которые Николай доложен производить в следующем месяце для максимизации маржинальной прибыли.

Скачать заметку в формате , пример в формате

1. Воспользуемся математической моделью построенной . Вот эта модель:

Максимизировать: Z = 2500 * х 1 + 3500 *х 2

При условии, что: 3 * х 1 + 10 * х 2 ≤ 330

16 * х 1 + 4 * х 2 ≤ 400

6 * х 1 + 6 * х 2 ≤ 240

2. Создадим экранную форму и введем в нее исходные данные (рис. 1).

Рис. 1. Экранная форма для ввода данных задачи линейного программирования

Обратите внимание на формулу в ячейке С7. Это формула целевой функции. Аналогично, в ячейки С16:С18 введены формулы для расчета левой части ограничений.

3. Проверьте, если у вас установлена надстройка «Поиск решения» (рис. 2), пропустите этот пункт.

Рис. 2. Надстройка Поиск решения установлена; вкладка «Данные», группа «Анализ»

Если надстройки «Поиск решения» вы на ленте Excel не обнаружили, щелкните на кнопку Microsoft Office, а затем Параметры Excel (рис. 3).

Рис. 3. Параметры Excel

Выберите строку Надстройки, а затем в самом низу окна «Управление надстройками Microsoft Excel» выберите «Перейти» (рис. 4).

Рис. 4. Надстройки Excel

В окне «Надстройки» установите флажок «Поиск решения» и нажмите Ok (рис. 5). (Если «Поиск решения» отсутствует в списке поля «Надстройки», чтобы найти надстройку, нажмите кнопку Обзор. В случае появления сообщения о том, что надстройка для поиска решения не установлена на компьютере, нажмите кнопку Да, чтобы установить ее.)

Рис. 5. Активация надстройки «Поиск решения»

После загрузки надстройки для поиска решения в группе Анализ на вкладке Данные становится доступна команда Поиск решения (рис. 2).

4. Следующим этапом заполняем окно Excel «Поиск решения» (рис. 6)

Рис. 6. Заполнение окна «Поиск решения»

В поле «Установить целевую ячейку» выбираем ячейку со значением целевой функции – $C$7. Выбираем, максимизировать или минимизировать целевую функцию. В поле «Изменяя ячейки» выбираем ячейки со значениями искомых переменных $C$4:$D$4 (пока в них нули или пусто). В области «Ограничения» с помощью кнопки «Добавить» размещаем все ограничения нашей модели. Жмем «Выполнить». В появившемся окне «Результат поиска решения» выбираем все три типа отчета (рис. 7) и жмем Ok. Эти отчеты нужны для анализа полученного решения. Подробнее о данных, представленных в отчетах, можно почитать .

Рис. 7. Выбор типов отчета

На основном листе появились значения максимизированной целевой функции – 130 000 руб. и изменяемых параметров х 1 = 10 и х 2 = 30. Таким образом, для максимизации маржинального дохода Николаю в следующем месяце следует произвести 10 единиц продукта А и 30 единиц продукта В.

Если вместо окна «Результат поиска решения» появилось что-то иное, Excel`ю найти решение не удалось. Проверьте правильность заполнения окна «Поиск решения». И еще одна маленькая хитрость. Попробуйте уменьшить точность поиска решения. Для этого в окне «Поиск решения» щелкните на Параметры (рис. 8.) и увеличьте погрешность вычисления, например, до 0,001. Иногда из-за высокой точности Excel не успевает за 100 итераций найти решение. Подробнее о параметрах поиска решения можно почитать .

Рис. 8. Увеличение погрешности вычислений