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

Поставить курсор мыши в ячейку E3 и нажать на левую кнопку мы­ши: туда после решения задачи будет занесено вычисленное значение целевой функции.

Войти в меню Сервис, выбрать в нем Поиск решения и щелкнуть на нем левой кнопкой мыши. На экране появится диалоговое окно Поиск решения (рис. 1.2). В поле Установить целевую ячейку занести ячейку с адресом $E$3. Для этого проще всего установить курсор мыши внутрь поля Установить целевую ячейку, щелкнуть в нем левой кнопкой мы­ши, а затем - мышью на ячейке E3. Именно в ячейке E3 будет вычислено значение целевой функции.

Поскольку ищется максимум целевой функции, то после слова Рав­ной выделим Максимальному значению, щелкнув в кружочке мышью.

В поле Изменяя ячейки занести диапазон ячеек $А$2:$D$2, так как именно эти ячейки отведены под значения вычисляемых переменных х₁, х₂, х₃ и х₄. Для этого поставить курсор в поле Изменяя ячейки и щелкнуть на нем левой кнопкой мыши. Затем поставить курсор на ячейку А2 и при нажатой левой кнопке мыши перевести («протащить») курсор на ячейку D2. В поле Изменяя ячейки появится необходимый диапазон ячеек.

Рис.1.2. Фрагмент Листа Excel с диалоговым окном Поиск решения

В поле Ограничения занести ограничения (2)-(4). Для этого необ­ходимо щелкнуть мышью на кнопке Добавить диалогового окна Поиск решения. Появится диалоговое окно Добавление ограничения (рис. 1.3).

Занесем ограничение (2).

В поле Ссылка на ячейку поставить курсор и щелкнуть на нем ле­вой кнопкой мыши, затем поставить курсор на ячейку E4, где задана формула ограничения (2), и щелкнуть на нем левой кнопкой мыши. В поле Ссылка на ячейку появится адрес ячейки E4.

Рис.1.3. Диалоговое окно Добавление ограничения

В среднем поле щелкнуть на кнопке справа от этого поля (со стре­лочкой) и выбрать соответствующий знак неравенства. В среднем поле появится знак ≤.

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

После проделанных действий щелкнуть на кнопке ОК. Попадаем снова в поле Поиск решения. Повторяя описанные выше действия, зано­сим остальные ограничения (рис. 1.2).

Снова в поле Поиск решения (рис. 1.2). Щелкнуть мышью на кноп­ке Параметры.

На экране появится диалоговое окно Параметры поиска решения. В этом окне (рис. 1.4) устанавливаются параметры поиска решения. Здесь отметить квадратики Линейная модель, Неотрицательные значения, Автоматическое масштабирование. Щелкнуть мышью на кнопке ОК.

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

Снова попадаем в диалоговое окно Поиск решения. Вэтом окне (рис. 1.2) щелкнем левой кнопкой мыши на кнопку Выполнить. На экран выводится окно Результаты поиска решения (рис. 1.5). Вдиалоговом окне (если решение найдено) Результаты поиска решения появляется надпись (рис. 1.5) Решение найдено. Все ограничения и условия оптимальности выполнены. Щелкнуть левой кнопкой мыши на кнопке ОК.

Одновременно на Листе экрана также появляются результаты реше­ния задачи (рис. 1.6): в столбце Ограничения выводятся их рассчитан­ные значения. В строке переменные - значения рассчитанных опти­мальных переменных х₁, х₂ х₃ и х₄. В строке Целевая функция в ячейке E3 - рассчитанное значение целевой функции.

Итак, найдено оптимальное решение: х₁= 0, х₂ = 30, х₃= 10, х₄ = 0, при этом максимальная стоимость выпущенной продукции равна F_max = 150.

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

В окне Результаты поиска решения (рис. 1.5 ) содержится Тип от­чета: Результаты, Устойчивость, Пределы. Для получения всех видов отчетов надо щелкнуть левой кнопкой мыши на каждом из них - соответ­ствующие строчки будут закрашены, - а затем на кнопке ОК. Отчеты отображаются в нижней строке Листа на экране Excel. Для их вызова необходимо щелкнуть на соответствующем отчете.

Рис.1.6. Результаты решения, расположенные на Листе экрана

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

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

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

Рис.1.7. Содержание отчета по результатам

В отчете по результатам содержатся оптимальные значения переменных х₁, х₂ х₃ и х₄, которые соответственно равны 0, 30, 10 и 0, значение целевой функции – 150, а также левые части ограничений.

Содержание остальных отчетов будет рассмотрено в других лабораторных работах.

Варианты индивидуальных заданий

Задание 1. Небольшая фабрика изготовляет два вида красок: для наружных (№1) и внутренних (№2) работ. Продукция обоих видов поступает в оптовую продажу. Для производства красок используются два исходных продукта – А и В. Максимально возможные суточные запасы этих продуктов составляют 6 и 8 т соответственно. Расходы продуктов А и В на 1т соответствующих красок приведены в таблице.

Изучение рынка сбыта показало, что суточный спрос на краску для внутренних работ (№2) никогда не превышает спрос на краску для наружных работ (№1) более чем на 1 т. Кроме того, установлено, что спрос на краску № 2 никогда не превышает 2т в сутки.

Прибыль от реализации одной тонны красок № 1 равна 3 тыс. ден. единиц, а для краски № 2 – 2 тыс. ден. ед.

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

Задание 2. Пошивочное предприятие намечает выпуск двух видов костюмов – мужских и женских. На женский костюм требуется 1 м шерсти, 2м лавсана и 1чел/день трудозатрат. На мужской костюм требуется 3,5 м шерсти, 0,5 м лавсана и 1чел/день трудозатрат. Всего имеется 350 м шерсти, 240 м лавсана и 150 чел/день трудозатрат. Определить сколько костюмов каждого вида необходимо сшить, чтобы обеспечить максимальную прибыль, если прибыль от реализации женского костюма составляет 10 денежных единиц, от мужского – 20 денежных единиц. При этом следует иметь в виду, что необходимо сшить не менее 60 мужских костюмов.

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

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

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

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

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

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

Задача 6. На заводе используется сталь трех марок: А, В, С, запасы которых равны соответственно 10, 16 и 12 ед. Завод выпускает два вида изделий. Для изделия 1 требуется по одной единице стали всех марок. Для изделия 2 требуется 2 единицы стали марки В, одна – марки С и не требуется сталь марки А. От реализации единицы изделия вида 1 завод получает 300 руб. прибыли, а вида 2 – 200 руб. Составить план выпуска продукции, дающий наибольшую прибыль.

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

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

Задача 8. Компания производит полки для ванных комнат двух размеров – А и В. Агенты по продаже считают, что в неделю на рынке может быть реализовано до 550 полок. Для каждой полки типа А требуется 2 м² материала, а для полки типа В – 3 м² материала. Компания может получить до 1200 м² материала в неделю. Для изготовления одной полки типа А требуется 12 мин машинного времени, а для изготовления одной полки типа В – 30 мин. Машину можно использовать 160 час в неделю.

Считая, что полки производятся круглосуточно (в три смены), прибыль от продажи одной полки типа А составляет 3 ден. единицы, а от продажи одной полки типа В – 4 ден. ед., определить, сколько полок каждого типа следует выпускать в неделю, чтобы получить максимальную прибыль от продажи.

Задача 9. Небольшая фирма производит два вида продукции: столы и стулья. Для изготовления одного стула требуется 3 фута древесины, а для изготовления одного стола – 7 футов. На изготовление одного стула уходит 2 часа рабочего времени, а на изготовление стола – 8 часов. Каждый стул приносит 1 долл. прибыли, а каждый стол – 3 долл. Сколько стульев и сколько столов должна изготовить эта фирма, если она располагает 420 футами древесины и 400 часами рабочего времени и хочет получить максимальную прибыль?

Задача 10. На имеющихся у фермера 400 акрах земли он планирует посеять кукурузу и сою. Сев и уборка кукурузы требует на каждый акр 200 долл. затрат, а сои – 100 долл. На покрытие расходов, связанных с севом и уборкой, фермер получил ссуду в 60 тыс. долл. Каждый акр, засеянный кукурузой, приносит 40 бушелей, а каждый акр, засеянный соей, – 80 бушелей. Фермер заключил договор на продажу, по которому каждый бушель кукурузы принесет ему 3 долл., а каждый бушель сои – 1 долл. Однако, согласно этому договору, фермер обязан хранить убранное зерно в течение нескольких месяцев на складе, максимальная вместимость которого равна 21 тыс. бушелей. Сколько акров должен засеять фермер каждой из этих культур, чтобы получить максимальную прибыль?