Методические указания. Упражнение 1. Пример 1

Упражнение 1. Пример 1.

Задача об использовании ресурсов.

Мебельная фабрика выпускает стулья и кресла.

На изготовление одного стула стоимостью 80 рублей расходуется 2 метра досок, 0.5 м² обивочной ткани и 2 чел./часа рабочего времени.

На изготовление кресла стоимостью 120 рублей - 4 метра, 0.25 м² и 2.5 чел./часа.

В распоряжении фабрики имеется 440 метров досок, 65 м² обивочной ткани и 320 чел./часов рабочего времени.

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

Решение.

Обозначим через S запланированное к производству число стульев и через K - число кресел.

Стоимость произведенной продукции будет вычисляться по формуле:

СТОИМОСТЬ = 80* S + 120 * K

Ограничения на ресурсы:

2 * S + 4 * K <= 440

0.5 * S + 0.25 * K <= 65

2 * S + 2.5 * K <= 320.

Кроме того, по смыслу задачи

S>= 0 и

K>= 0.

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

1. Откройте новую книгу в Excel и занесите в рабочий лист исходные данные (см. рис). Для удобства присвойте ячейкам имена: ячейке B4 - имя S, ячейке В5 - имя K, ячейке B7 - имя СТОИМОСТЬ):

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

3. Установите в качестве целевой ячейки ячейку СТОИМОСТЬ (или B7).

4. В поле "Изменяя ячейки" укажите ячейки $B$4:$B$5.

5. Добавьте ограничения и нажмите кнопку "Выполнить":

6. В появившемся окне "Результаты поиска решения" нажмите кнопку "Сохранить сценарий" и введите имя сценария. Затем выберите Тип отчета "Результаты" и нажмите кнопку "ОК".

7. Просмотрите отчет о результатах.

8. Проведите графический анализ задачи.

Упражнение 2. Пример 2.

Задача.

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

Обработка каждого изделия 1-го типа занимает 5 часов в цехе А и 3 часа в цехе В.

Обработка каждого изделия 2-го типа занимает 2 часа в цехе А и 4 часа в цехе В.

Цех А в состоянии работать в месяц не более 150 часов, а цех В - не более 132 часов.

Предприятие за каждое изготовленное изделие 1-го типа получает прибыль в 300 руб. И за каждое изделие 2-го типа - 200 руб.

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

Решение.

Пусть X и Y - количество изделий 1-го и 2-го типов соответственно.

Прибыль предприятия равна P = 300*X + 200*Y.

Ограничения на ресурсы:

5 * X + 2 * Y <= 150

3 * X + 4 * Y <= 132

X >= 0

Y >= 0

(Ответ: Прибыль = 10200)

Упражнение 3. Задания для самостоятельной работы.

Задача 2.

В городе имеется два склада муки и два хлебозавода. Ежедневно с первого склада вывозится 50 тонн муки, со второго - 70 тонн.

Эта мука доставляется на хлебозаводы: на первый - 40 тонн, на второй - 80 тонн.

Перевозка 1 тонны муки с 1-го склада на 1-ый завод стоит 12 рублей,

с 1-го склада на 2-ой завод - 16 рублей,

со 2-го склада на 1-ый завод - 8 рублей,

со 2-го склада на 2-ой завод - 10 рублей.

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

(Ответ: Стоимость перевозок = 1340 руб.)

Методические указания.

Команда Поиск решения.

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

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

1. Задание цели.

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

2. Задание переменных.

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

Если переменные находятся не в смежных ячейках, они указываются через ";" (при их выделении мышью нужно держать нажатой клавишу Ctrl).

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

3. Задание ограничений.

Задание ограничений является необязательным.

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

Ограничение состоит из трех компонентов: ссылки на ячейку, оператора сравнения и значения ограничения.

4. Задание целочисленных ограничений.

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

5. Завершение поиска и сохранение модели поиска решения.

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

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

6. Другие параметры поиска решения.

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

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

7. Флажок Показывать результаты итераций.

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

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

8. Генерация отчетов.

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

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

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

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

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

Задача 3.

В ресторане готовятся фирменные блюда трех видов (блюдо А, блюдо В и блюдо С) с использованием при приготовлении ингредиентов трех видов (ингредиент 1, ингредиент 2 и ингредиент З). Расход ингредиентов в граммах на блюдо задается следующей таблицей:

Вид ингредиента	Блюдо А	Блюдо В	Блюдо С
Ингредиент1
Ингредиент2
Ингредиент

Стоимость приготовления блюд одинакова (100руб).

Ежедневно в ресторан поступает 5 кг ингредиента 1 и по 4 кг ингредиентов видов 2 и З. Каково оптимальное соотношение дневного производства блюд различного вида, если производственные мощности ресторана позволяют использовать весь запас поступивших продуктов?

Ответ: оптимальный набор блюд: блюдо А – 184 порции, блюдо В – 24 порции и блюдо С – 8 порций.

Задача 4.

Туристская фирма заключила контракте двумя турбазами: в г. Сухими и в окрестных городах, рассчитанных, соответственно, на 200 и 150 человек.

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

обезьяний питомник принимает в день 70 человек, ботанический сад — 180 человек, а в горы в один день могут пойти 110 человек;

стоимость одного посещения выражается таблицей:

Турбаза	ОП	БС	Поход

Ответ: оптимальный набор туристов: число туристов турбазы в Сухуми, посещающих обезьяний питомник -30, ботанический сад – 170 и отправляющихся в поход – 0; число туристов из окрестной турбазы, посещающих обезьяний питомник – 40, посещающих ботанический сад – 0 и отправляющихся в поход – 110. при этом суммарные расходы турфирмы составят 2120 руб. и будут минимальны.

Задача 5.

Составьте оптимальный план производства продукции, чтобы стоимость всего объема произведенного была максимальной, если: цена 1 единицы каждой продукции по 20 денежных единиц. На каждую единицу первой продукции расходуется 2 единицы сырья; 4 единицы материалов и 1 человеко-день; второй продукции — соответственно, 2, 3 и 3. Общие объемы ресурсов:

фонд рабочего времени — 12;

фонд сырья — 16;

фонд материалов — 9;

цена 1 единицы сырья —1 денежная единица;

цена материалов — З денежных единицы.

Проанализируйте математическую постановку этой задачи; как увеличить стоимость всей продукции, если можно привлечь дополнительные ресурсы, лишние продавать?

Ответ: Оптимальный план: 3 единицы первой продукции и 0 единиц – второй.

4. Каждому животному нужно ежедневно выдать не менее б единиц белков, 8 единиц жиров и 12 единиц углеводов. Есть два вида корма. Одна единица первого корма содержит 21 единицу белка, 2 единицы жира, 4 единицы углеводов и стоит З руб. Для второго корма соответствующие цифры следующие: 3, 2, 2 и 2. Составьте математическую модель и найдите оптимальный рацион питания.

Ответ: Оптимальны рацион: 2 единицы первого корма и 2 единицы второго

Задача 6.

Продукцию, производимую на предприятиях А и В, надо развезти по магазинам №1, №2 и №3. Предприятие А производит 320 единиц продукции, предприятие В — 380. Магазин №1 реализует за сутки 200 кг, №2 — 280 кг, №З — 220 кг. Составьте план перевозок продукции, при котором их стоимость будет наименьшей, если стоимость перевозки 1 кг продукции задана таблицей:

Предприятие	Магазин
	№1	№2	№З
А
В

Ответ: Оптимальный план перевозок можно записать так:

Предприятие	Магазин
	№1	№2	№З
А
В

Задача 7.

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

Ответ: необходимо сшить 70 женских костюмов, 80 – мужских.