Решение задачи в Excel

Варианты для выполнения контрольной работы.

Вариант 1

Выполнить заказ по производству 32 изделий И1 и 4 изделий И2 взялись бригады Б1 и Б2 . Производительность бригады Б1 по производству изделий И1 и И2 составляет соответственно 4 и 2 изделия в час, фонд рабочего времени этой бригады 9,5 ч. Производительность бригады Б2 – соответственно 1 и 3 изделия в час, а ее фонд рабочего времени – 4 ч. Затраты, связанные с производством единицы изделия, для бригады Б1 равны соответственно 9 и 20 руб., для бригады Б2 – 15 и 30 руб.

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

Вариант 2

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

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

Вариант 3

При изготовлении изделий И1 и И2 используются сталь и цветные металлы, а также токарные и фрезерные станки. По технологическим нормам на производство единицы изделия И1 требуется 300 и 200 станко-часов соответственно токарного и фрезерного оборудования, а также 10 и 20 кг соответственно стали и цветных металлов. Для производства единицы изделия И2 требуется 400, 100, 70 и 50 соответствующих единиц тех же ресурсов. Цех располагает 12400 и 6800 станко-часами соответственно токарного и фрезерного оборудования и 640 и 840 кг соответственно стали и цветных металлов. Прибыль от реализации единицы изделия И1 составляет 6 руб. и от единицы изделия И2 – 16 руб.

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

Вариант 4

Для сохранения нормальной жизнедеятельности человек должен в сутки потреблять белков не менее 120 условных единиц (усл. ед.), жиров – не менее 70 и витаминов – не менее 10 усл. ед. Содержание их в каждой единице продуктов П1 и П2 равно соответственно (0,2; 0,075; 0) и (0,1; 0,1; 0,1) усл. ед. Стоимость 1 ед. продукта П1 – 2 руб., П2 –3 руб. Постройте математическую модель задачи, позволяющую так организовать питание, чтобы его стоимость была минимальной, а организм получил необходимое количество питательных веществ.

Вариант 5

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

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

Вариант 6

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

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

Вариант 7

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

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

Вариант 8

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

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

Вариант 9

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

Стоимость 1 кг корма первого вида — 4 д.е., второго ~ 6 д.е.

Составьте дневной рацион питательности, имеющий минимальную стоимость.

Вариант 10

Хозяйство располагает следующими ресурсами: площадь — 100 ед., труд — 120 ед., тяга — 80 ед. Хозяйство производит четыре вида продукции П1, П2, П3 и П4, Организация производства характеризуется следующей таблицей:

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

Вариант 11

Цех выпускает трансформаторы двух видов. Для изготовления трансформаторов обоих видов используются железо и проволока. Общий запас железа - 3 т, проволоки ~ 18 т. На один трансформатор первого вида расходуются 5 кг железа и 3 кг проволоки, а на один трансформатор второго вида расходуются 3 кг железа и 2 кг проволоки. За каждый реализованный трансформатор первого вида завод получает прибыль 3 д. е., второго — 4 д. е.

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

Вариант 12

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

Сформулировать экономико-математическую модель задачи на максимум прибыли и найти оптимальный план выпуска продукции.

Вариант 13

Из трех продуктов - I, II, III составляется смесь. В состав смеси должно входить не менее 6 ед. химического вещества А, 8 ед. — вещества В и не менее 12 ед. вещества С. Структура химических веществ приведена в следующей таблице:

Сформулировать экономико-математическую модель задачи на максимум прибыли и найти оптимальный план выпуска продукции.

Вариант 13

Из трех продуктов - I, II, III составляется смесь. В состав смеси должно входить не менее 6 ед. химического вещества А, 8 ед. — вещества В и не менее 12 ед. вещества С. Структура химических веществ приведена в следующей таблице:

Составьте наиболее дешевую смесь.

Вариант 14

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

• купить акварельной краски по цене 30 д. е. за коробку, цветные карандаши по цене 20 д. е. за коробку, линейки по цене 12 д. е., блокноты по цене 10 д. е.;

• красок нужно купить не менее трех коробок, блокнотов -столько, сколько коробок карандашей и красок вместе, линеек не более пяти. На покупки выделяется не менее 300 д. е.

В каком количестве студент должен купить указанные предметы, чтобы общее число предметов было наибольшим?

Вариант 15

Цех выпускает три вида деталей — А, В, С. Каждая деталь обрабатывается тремя станками. Организация производства в цехе характеризуется следующей таблицей:

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

максимальной прибыли.

Вариант 16

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

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

Вариант 17

Нефтеперерабатывающий завод получает четыре полуфабриката: 400 тыс. л алкилата, 250 тыс. л крекинг-бензина, 350 тыс. л бензина прямой перегонки и 100 тыс. л изопентона. В результате смешивания этих четырех компонентов в разных пропорциях образуется три сорта авиационного бензина: бензин А-2:3:5:2, бензин

В-3:1:2:1, бензин С-2:2:1:3. Стоимость 1 тыс. л указанных сортов бензина характеризуется числами 120 д.е,, 100 д. е., 150 д. е.

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

Вариант 18

Фабрика производит два вида красок: первый – для наружных, а второй – для внутренних работ. Для производства красок используются два ингредиента: А и В. Максимально возможные суточные запасы этих ингредиентов составляют 6 и 8 т соответственно. Известны расходы А и В на 1 т соответствующих красок (таблица). Изучение рынка сбыта показало, что суточный спрос на краску 2-го вида никогда не превышает спроса на краску 1-го вида более, чем на 1 т. Кроме того, установлено, что спрос на краску 2-го вида никогда не превышает 2 т в сутки. Оптовые цены одной тонны красок равны: 3 тыс. руб. для краски 1-го вида; 2 тыс. руб. для краски 2-го вида.

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

Вариант 19

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

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

Вариант 20

Из двух сортов бензина образуются две смеси — А и В. Смесь А содержит бензина 60% 1-го сорта и 40% 2-го сорта; смесь В - 80% 1-го сорта и 20% 2-го сорта. Цена 1 кг смеси А - 10 д.е., а смеси В — 12 д.е.

Составьте план образования смесей, при котором будет получен максимальный доход, если в наличии имеется бензина 50 т 1-го сорта и 30 т 2-го сорта.

Вариант 21

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

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

Цех выпускает трансформаторы трех видов. Для изготовления трансформаторов всех видов используются железо, проволока и медь. Общий запас железа – 480 кг, проволоки – 960 кг, а меди – 1000 кг. На один трансформатор первого вида расходуются 2 кг железа и 7 кг проволоки и 8 кг меди, на один трансформатор второго вида расходуются 3 кг железа, 7 кг проволоки и 2 кг меди, а для третьего вида – по 7 кг железа, проволоки и меди соответственно. За каждый реализованный трансформатор первого вида завод получает прибыль 17 д. е., второго — 43 д. е., а третьего – 62 ден.ед.

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

1) Составление экономико-математической модели.

Пусть х1, х2 и х3– это искомое количество выпускаемых трансформаторов первого, второго и третьего вида соответственно. Тогда максимальная прибыль будет определяться как:

При этом ограничение на имеющееся количество железа будет выглядеть как:

Ограничение по проволоке:

 

Ограничение по меди:

Кроме того, накладывается техническое ограничение на неотрицательность переменных:

Решение задачи в Excel

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

Предварительно введём в ячейку А4 формулу целевой функцию в следующем виде: =17*A1+43*A2+62*A3.

Значения в ячейках А1, А2 и А3 отведём под значения переменных x1, x2 и x3 соответственно. Числовые значения переменных x1, x2 и x3 в эти ячейки будут введены автоматически в процессе решения задачи.

В ячейки В1, В2 и В3 введём математические формулы ограничений в виде, указанном в таблице 2.

Таблица 2.

Затем введём в ячейки С1, С2 и С3 значения 480, 960 и 1000 соответственно, ограничивающие численные значения переменных задачи. Таким образом, все исходные данные задачи записаны в том виде, в котором они используются в окне "Поиск решения". Теперь воспользуемся подпунктом меню ExcelПоиск решения...На экране появится следующее окно (рис. 1):

Рисунок 1 – Окно «Поиск решений»

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

В поле "Изменяя ячейки" указываем диапазон изменения ячеек от А1 до А3, а именно $A$1:$A$3.

Для приведения в рабочее состояние математической программы поиска оптимального решения заданной задачи необходимо установить ограничения, учитываемые при её решении. Для этого нажимаем на кнопку "Добавить", расположенную справа от поля "Ограничения". На экране появляется следующее окно (рис. 2):

 

Рисунок 2 – Окно добавления ограничений

Для добавления первого ограничения, а именно 2x1 + 3x2 + 7x3 ≤480, в поле "Ссылка на ячейку" указываем ячейку В1, затем в списке, расположенном посередине, выбираем знак "<=" и в поле "Ограничение" указываем ячейку С1. После этого нажимаем на кнопку "Добавить". Аналогично добавляем два оставшиеся ограничения задачи. Закрываем окно, нажав на кнопку "Отмена". Окно "Поиск решения" примет вид, показанный на рис. 3.

Рисунок 3 – Заполненное окно «Поиск решения»

Нажимаем на кнопку "Выполнить". Параметры в окне должны выбираться реальные, соответствующие заданным числовым характеристикам конкретной задачи. На экран выводится окно "Результаты поиска решения" (рис. 4).

 

Рисунок 4 – Окно вывода результатов поиска решений

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

 

Рисунок 5 - Результат вычислений

В ячейке А4 находим значение целевой функции F(X), соответствующее найденному решению. В ячейках А1, А2 и А3 указаны соответствующие значения переменных x1, x2 , x3. Для решаемой нами задачи оптимальное решение имеет следующий вид:

F = 6222,857, x1 = 0, x2 = 120 и x3 = 17.