Порядок выполнения и оформления задания №2
1. По своему шифру выберите вариант и условие задачи (номер варианта определяется по последним цифрам зачетной книжки).
2. Постройте математическую модель с необходимыми комментариями и обозначениями.
3. На рабочий лист Excel введите условие задачи.
4. Постройте компьютерную модель, оформите расчетные таблицы.
5. Произведите поиск решения и сохраните его вместе с отчетами Excel.
6. Результаты поиска решения оформите в виде отчета. Он должен содержать:
· условие задачи и математическую модель;
· фрагмент рабочего листа Excel с условием;
· фрагмент рабочего листа Excel с построенной компьютерной моделью;
· фрагменты рабочего листа Excel с результатами - в обычном режиме и режиме формул;
· фрагменты настройки диалоговых окон поиска решения, параметров поиска решения, добавления ограничений;
· фрагмент Отчета по результатам.
7. К отчету приложите дискету с выполненной задачей, сохраненной в виде книги Excel.
Методика поиска решения в Excel
- В меню СЕРВИС выберите команду Поиск решения.
- Если команда Поиск решения отсутствует в меню СЕРВИС, установите надстройку Поиск решения.
- Заполните диалоговое окно поиска решения. При этом в поле Установить целевую ячейку введите имя или ссылку на ячейку с целевой функцией. Целевая ячейка должна содержать формулу.
- Выполните одно из следующих действий:
· чтобы максимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установите переключатель в положение максимальному значению;
· чтобы минимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установите переключатель в положение минимальному значению;
· чтобы установить значение в целевой ячейке равным некоторому числу, установите переключатель в положение значению и введите в соответствующее поле требуемое число.
- В поле Изменяя ячейки введите имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Допускается задание до 200 изменяемых ячеек.
- Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажмите кнопку Предположить.
- В поле Ограничения введите все ограничения, накладываемые на поиск решения.
Добавление ограничения
- В поле Ссылка на ячейку введите адрес ячейки, на значение которой накладываются оганичения.
- Выберите из раскрывающегося списка условный оператор ( <=, =, >=, цел или двоич ), который должен располагаться между ссылкой и ограничением. Если выбрано цел, в поле Ограничение появится «целое». Если выбрано двоич, в поле Ограничение появится «двоичное».
- В поле Ограничение введите число, ссылку на ячейку или ее имя, либо формулу. Выполните одно из следующих действий:
· чтобы принять ограничение и приступить к вводу нового, нажмите кнопку Добавить.
· чтобы принять ограничение и вернуться в диалоговое окно Поиск решения, нажмите кнопку OK.
· условные операторы типа цел и двоич можно применять только при наложении ограничений на изменяемые ячейки.
- В диалоговом окне Поиск решения нажмите кнопку Параметры:
· в диалоговом окне Параметры поиска решения установите:
· флажок Линейная модель, что позволяет задать любое количество ограничений. При решении нелинейных задач на значения изменяемых ячеек можно наложить более 100 ограничений, в дополнение к целочисленным ограничениям на переменные.
· флажок Неотрицательные значения – это освобождает от дополнительных ограничений по неотрицательности переменных.
· чтобы просмотреть значения всех найденных решений, в диалоговом окне Параметры поиска решенияустановите флажок Показывать результаты итераций, а затем нажмите кнопку ОК.
- Нажмите кнопку Выполнить и выполните одно из следующих действий:
· чтобы сохранить найденное решение на листе, выберите в диалоговом окне Результаты поиска решения вариант Сохранить найденное решение;
· чтобы восстановить исходные данные, выберите вариант Восстановить исходные значения.
· чтобы прервать поиск решения, нажмите клавишу ESC. Лист Microsoft Excel будет пересчитан с учетом найденных значений влияющих ячеек.
- Если решение будет найдено, выберите тип отчета в списке Отчеты и нажмите кнопку ОК. Отчет будет помещен на новый лист книги.
- В диалоговом окне Результаты поиска решения выберите Сохранить сценарий.
· Введите в поле Название сценария название сценария
· Чтобы просмотреть различные сохраненные наборы значений влияющих ячеек, просмотрите все сценарии.
· Чтобы создать сценарий, не сохраняя результат поиска решения или не отображая результаты на листе, сохраните сценарий в диалоговом окне Результаты поиска решения, а затем выберите Восстановить исходные значения.
Варианты заданий 1-30
Вариант 1
Для откорма крупного рогатого скота используются корма двух видов b1 и b2, в которые входят питательные вещества a1, a2, a3, a4,. Содержание количества единиц питательных веществ в 1 кг каждого корма, стоимость 1 кг корма и нормы содержания питательных веществ в дневном рационе животного (количество питательных веществ должно быть не менее нормы) представлены в таблице.
Составить рацион при условии минимальной стоимости.
| Питательное вещество | Вид корма | Норма содержания питательного вещества | |
| b1 | b2 | ||
| a1 | |||
| a2 | |||
| a3 | |||
| a4 | |||
| Стоимость 1 кг корма, тыс.руб. |
Вариант 2
Трикотажная фабрика использует для производства свитеров и кофточек чистую шерсть, силон и нитрон, запасы которых составляют соответственно 800, 400 и 300 кг. Количество видов трикотажа (кг), необходимых для изготовления 10 изделий приведены в таблице.
Составить план производства изделий, обеспечивающий получение максимальной прибыли.
| Вид сырья | Затраты пряжи на 10 шт. | |
| Свитер | Кофточка | |
| Шерсть | ||
| Силон | ||
| Нитрон | ||
| Прибыль, тыс. руб. |
Вариант 3
При подкормке посевов необходимо внести на 1 га почвы не менее 8 единиц химического вещества a, не менее 21 единицы химического вещества b и не менее 16 единиц химического вещества с. Фермер закупает комбинированные удобрения двух видов: I и II. В таблице указаны содержание количества единиц химического вещества в 1 кг каждого вида удобрений и цена 1 кг удобрений.
Определить потребность фермера в удобрениях I и II вида на 1 га посевной площади при минимальных затратах на их приобретение.
| Химическое вещество | Содержание химического вещества в 1 кг удобрения | |
| I | II | |
| a | ||
| b | ||
| c | ||
| Цена 1 кг удобрения, тыс. руб. |
Вариант 4
На звероферме могут выращиваться черно-бурые лисы и песцы. Для обеспечения нормальных условий их выращивания используются три вида кормов. Количество корма каждого вида, которое должны получать животные, приведено в таблице. В ней также указаны общее количество корма каждого вида, которое может быть использовано зверофермой, и прибыль от реализации одной шкурки лисицы и песца.
Составить план выращивания животных, обеспечивающий максимальную прибыль.
| Вид корма | Количество единиц корма, которое ежедневно должно получать животное | Общее количество корма | |
| Лисица | Песец | ||
| I | |||
| II | |||
| III | |||
| Прибыль от реализации одной шкурки, руб. |
Вариант 5
На четырех станках обрабатываются два вида изделий. Каждое изделие проходит обработку на каждом станке. В таблице задана трудоемкость обработки (т.е. время обработки) одного изделия и фонд полезного времени работы станков.
Составить план производства изделий, обеспечивающий максимальную загрузку станков.
| Станок | Трудоемкость обработки изделия | Фонд времени | |
| B1 | B2 | ||
| A1 | |||
| A2 | |||
| A3 | |||
| A4 |
Вариант 6
Лакокрасочное предприятие выпускает краску в двух видах тары – больших и малых банках (барабанах), емкость которых соответственно составляет 55 и 15 л, а стоимость пустых барабанов – 30 и 24 тыс. руб. Литр краски стоит 14600 руб. Некий оптовый покупатель желает приобрести краску на 14 млн. руб.
Необходимо, не выходя за пределы договорной суммы, получить от лакокрасочного предприятия как можно больше краски. При этом имеется возможность лишь указать количество больших и малых барабанов с краской, но нельзя взять краску в разлив.
Вариант 7
Кондитерская фабрика для производства трех видов карамели А, В и С использует три вида основного сырья: сахарный песок, патоку и фруктовое пюре. Нормы расхода сырья каждого вида на производство 1т карамели данного вида приведены в таблице. В ней также указано общее количество сырья каждого вида, которое может быть использовано фабрикой, и прибыль от реализации 1т карамели данного вида.
Найти план производства карамели, обеспечивающий максимальную прибыль от ее реализации.
| Вид сырья | А | В | С | Общее кол-во сырья |
| Сахарный песок | 0,8 | 0,5 | 0,6 | |
| Патока | 0,4 | 0,4 | 0,3 | |
| Фруктовое пюре | - | 0,1 | 0,1 | |
| Прибыль от реализации 1т продукции |
Вариант 8
Фирма производит две модели А и В сборных книжных полок. Их производство ограничено наличием сырья (высококачественных досок) и временем машинной обработки. Для каждого изделия модели А требуется 3 м2досок, а для изделия модели В – 4 м2. Фирма может получать от своих поставщиков до 1700 м2досок в неделю. Для каждого изделия модели А требуется 12 мин машинного времени, а для изделия модели В – 30 мин. В неделю можно использовать 160 ч. машинного времени.
Сколько изделий каждой модели следует выпускать фирме в неделю, если каждое изделие модели А приносит 2 долл. прибыли, а каждое изделие модели В – 4 долл. прибыли?
Вариант 9
Имеются три сплава. Первый сплав содержит 70% олова и 30% свинца, второй – 80% олова и 20 % цинка, третий – 50% олова, 10% свинца и 40% цинка. Из них необходимо изготовить новый сплав, содержащий 15% свинца. Какое наибольшее и наименьшее процентное содержание олова может быть в этом сплаве?
Вариант 10
Фирма производит три вида продукции А, В, С, для выпуска каждого требуется определенное время обработки на всех четырех устройствах I, II, III, IV
| Вид продук-ции | Время обработки, ч | Прибыль, долл | |||
| I | II | III | IV | ||
| А | |||||
| В | |||||
| С |
Пусть время работы на устройствах соответственно 84, 42, 21, 42 часа. Определите, какую продукцию и в каких количествах стоит производить для максимизации прибыли (рынок сбыта для каждого продукта не ограничен).
Вариант 11
Имеются 6 предметов, каждый из которых характеризуется весом и ценой:
| Предмет | вес | цена |
Нужно выбрать из них такие предметы, чтобы их общий вес не превышал 12, а суммарная цена была максимальной.
Вариант 12
Имеются n пунктов производства и m пунктов распределения продукции. Стоимость перевозки единицы продукции с i –го пункта производства в j–й центр распределения сij приведена в таблице, где под строкой понимается пункт производства, а под столбцом – пункт распределения. Кроме того, в этой таблице в i –й строке указан объем производства в i –м пункте производства, а в j–м столбце указан спрос в j–м центре распределения. Необходимо составить план перевозок по доставке требуемой продукции в пункты распределения, минимизирующий суммарные транспортные расходы.
Вариант 13
Имеются n пунктов производства и m пунктов распределения продукции. Стоимость перевозки единицы продукции с i –го пункта производства в j–й центр распределения сij приведена в таблице, где под строкой понимается пункт производства, а под столбцом – пункт распределения. Кроме того, в этой таблице в i –й строке указан объем производства в i –м пункте производства, а в j–м столбце указан спрос в j–м центре распределения. Необходимо составить план перевозок по доставке требуемой продукции в пункты распределения, минимизирующий суммарные транспортные расходы.
Вариант 14
Имеются n рабочих и m видов работ. Стоимость cij выполнения i-м рабочим j-й работы приведена в таблице, где рабочему соответствует строка, а работе– столбец. необходимо составить план работ так, чтобы все работы были выполнены, каждый рабочий был занят только на одной работе, а суммарная стоимость выполнения всех работ была бы минимальной.
Вариант 15
Предприятие производит 3 вида продукции: А1, А2, А3, используя сырьё двух типов. Известны затраты сырья каждого типа на единицу продукции, запасы сырья на планируемый период, а также прибыль от единицы продукции каждого вида.
| Сырьё | Затраты сырья на единицу продукции | Запас сырья | ||
| А1 | А2 | А3 | ||
| I | 3,5 | 4,2 | ||
| II | ||||
| Прибыль от ед. прод. |
Сколько изделий каждого вида необходимо произвести, чтобы получить максимум прибыли?
Вариант 16
Фирма производит два вида продукции – А и В. Объем сбыта продукции А составляет не менее 60% общего объема реализации продукции обоих видов. Для изготовления продукции А и В используется одно и то же сырье, суточный запас которого ограничен величиной 100 кг. Расход сырья на единицу продукции А составляет 2 кг, а на единицу продукции В – 4 кг. Цены продукции и В равны 20 и 40 долларов, соответственно. Определить оптимальное распределение сырья для изготовления продукции А и В.
Вариант 17
В 1-ом столбце указаны мощности поставщиков, в 1-ой строке – спрос потребителей. Остальные числа таблицы – это стоимость перевозки единицы груза от соответствующего поставщика к соответствующему потребителю. Например, стоимость перевозки единицы груза от 3-го поставщика ко 2-му потребителю равна 6. Нужно составить оптимальный план перевозок.
Вариант 18
В 1-ом столбце указаны мощности поставщиков, в 1-ой строке – спрос потребителей. Остальные числа таблицы – это стоимость перевозки единицы груза от соответствующего поставщика к соответствующему потребителю. Например, стоимость перевозки единицы груза от 3-го поставщика ко 2-му потребителю равна 2. Нужно составить оптимальный план перевозок.
Вариант 19
Предприятие выпускает два вида продукции А и В. Для производства необходимы ресурсы: сырье, оборудование и трудоресурсы. Нормы затрат ресурсов на единицу продукции, цена реализации и объемы имеющегося ресурса приведены в варианте исходных данных. Требуется рассчитать оптимальный план производства, позволяющий максимизировать цену реализации для исходных данных. Решите задачу.
| Таблица затрат | |||
| Ресурс | Норма затрат | Объем ресурса | |
| На продукт А | На продукт В | ||
| Сырье (кг) | |||
| Оборудование (ст.-ч.) | |||
| Трудоресурсы (чел.-ч.) | |||
| Цена реализации (руб.) |
Вариант 20
Предприятие выпускает два вида продукции А и В. Для производства необходимы ресурсы: сырье, оборудование и трудоресурсы. Нормы затрат ресурсов на единицу продукции, цена реализации и объемы имеющегося ресурса приведены в варианте исходных данных. Требуется рассчитать оптимальный план производства, позволяющий максимизировать цену реализации для исходных данных. Решите задачу.
| Таблица затрат | |||
| Ресурс | Норма затрат | Объем ресурса | |
| На продукт А | На продукт В | ||
| Сырье (кг) | |||
| Оборудование (ст.-ч.) | |||
| Трудоресурсы (чел.-ч.) | |||
| Цена реализации (руб.) |
Вариант 21
Предприятие выпускает два вида продукции А и В. Для производства необходимы ресурсы: сырье, оборудование и трудоресурсы. Нормы затрат ресурсов на единицу продукции, цена реализации и объемы имеющегося ресурса приведены в варианте исходных данных. Требуется рассчитать оптимальный план производства, позволяющий максимизировать цену реализации для исходных данных. Решите задачу.
| Таблица затрат | |||
| Ресурс | Норма затрат | Объем ресурса | |
| На продукт А | На продукт В | ||
| Сырье (кг) | |||
| Оборудование (ст.-ч.) | |||
| Трудоресурсы (чел.-ч.) | |||
| Цена реализации (руб.) |
Вариант 22
Предприятие выпускает два вида продукции А и В. Для производства необходимы ресурсы: сырье, оборудование и трудоресурсы. Нормы затрат ресурсов на единицу продукции, цена реализации и объемы имеющегося ресурса приведены в варианте исходных данных. Требуется рассчитать оптимальный план производства, позволяющий максимизировать цену реализации для исходных данных. Решите задачу.
| Таблица затрат | |||
| Ресурс | Норма затрат | Объем ресурса | |
| На продукт А | На продукт В | ||
| Сырье (кг) | |||
| Оборудование (ст.-ч.) | |||
| Трудоресурсы (чел.-ч.) | |||
| Цена реализации (руб.) |
Вариант 23
Предприятие выпускает два вида продукции А и В. Для производства необходимы ресурсы: сырье, оборудование и трудоресурсы. Нормы затрат ресурсов на единицу продукции, цена реализации и объемы имеющегося ресурса приведены в варианте исходных данных. Требуется рассчитать оптимальный план производства, позволяющий максимизировать цену реализации для исходных данных. Решите задачу.
| Таблица затрат | |||
| Ресурс | Норма затрат | Объем ресурса | |
| На продукт А | На продукт В | ||
| Сырье (кг) | |||
| Оборудование (ст.-ч.) | |||
| Трудоресурсы (чел.-ч.) | |||
| Цена реализации (руб.) |
Вариант 24
Фирма производит два вида мороженого: сливочное и шоколадное. Для изготовления мороженого используется два исходных продукта: молоко и наполнители, расходы которых на 1 кг готового продукта т их суточные запасы приведены в таблице.
| Исходный продукт | Расход исходных продуктов на 1 кг мороженого (кг) | Запас на складе (кг) | |
| Сливочное | Шоколадное | ||
| Молоко | 0,8 | 0,5 | |
| Наполнители | 0,4 | 0,8 |
Суточный спрос на сливочное мороженое превышает спрос на шоколадное не более чем на 100 кг. Кроме того, известно, что спрос на шоколадное мороженое не превышает 350 кг в сутки. Отпускная цена 1 кг сливочного мороженого – 16 ден. ед., шоколадного – 14 ден. ед. Требуется определить, в каком количестве мороженого каждого вида должна производить фирма, чтобы доход от реализации продукции был максимальным.
Вариант 25
На складах А1, А2, А3 имеются запасы товаров в количествах 90, 400 и 110 т соответственно. Грузополучатели В1, В2, В3 должны получить эти товары в количествах 130, 300, 160 т соответственно. Требуется найти вариант перевозки грузов, при котором сумма затрат на перевозки будет минимальной. Расходы по перевозке 1 т грузов в условных единицах приведены в таблицу.
| Грузополучатели | Склад А1 | Склад А2 | Склад А3 |
| В1 | |||
| В2 | |||
| В3 |
Вариант 26
Фирма выпускает изделия двух видов – А и В. Для выпуска этих изделий используется сырье четырех типов. Расход сырья каждого типа на изготовление единицы продукции и запасы сырья заданы таблицей.
| Расход на единицу продукции | ||||
| Показатели | Тип сырья | |||
| Изделие | ||||
| А | ||||
| В | ||||
| Запас сырья |
Выпуск одного изделия типа А приносит доход 300 ден. ед., а одного изделия типа В – 200 ден. ед. Требуется составить план производства, обеспечивающей фирме наибольший доход.
Вариант 27
Требуется спланировать перевозку строительных материалов с трех заводов к четырем строительным площадкам по железной дороге. В течение каждого
квартала на четырех площадках требуется соответственно 5, 10, 20, 15 вагонов строительных материалов. Возможности заводов соответственно равны 10, 15 и 25 вагонов в квартал. В таблице приведены стоимости перевозки одного вагона ( в ден. ед.) от различных заводов к различным строительным площадкам.
| Стоимость перевозки одного вагона, ден. ед. | ||||
| Номер завода | Номер строительной площадки | |||
Вариант 28
Цех может производить стулья и столы. На производство стула идет 5 единиц материала, на производство стола - 20 единиц (футов красного дерева). Стул требует 10 человеко-часов, стол - 15. Имеется 400 единиц материала и 450 человеко-часов. Прибыль при производстве стула - 45 долларов США, при производстве стола - 80 долларов США. Сколько надо сделать стульев и столов, чтобы получить максимальную прибыль?
Вариант 29
Колхоз имеет возможность приобрести не более 19 трехтонных автомашин и не более 17 пятитонных. Отпускная цена трехтонного грузовика - 4000 руб., пятитонного - 5000 руб. Колхоз может выделить для приобретения автомашин 141 тысяч рублей. Сколько нужно приобрести автомашин, чтобы их суммарная грузоподъемность была максимальной?
Вариант 30
Компания производит полки для ванных комнат двух размеров - А и В. Агенты по продаже считают, что в неделю на рынке может быть реализовано до 550 полок. Для каждой полки типа А требуется 2 м2 материала, а для полки типа В - 3 м2 материала. Компания может получить до 1200 м2 материала в неделю. Для изготовления одной полки типа А требуется 12 мин машинного времени, а для изготовления одной полки типа В - 30 мин; машину можно использовать 160 час в неделю. Если прибыль от продажи полок типа А составляет 3 денежных единицы, а от полок типа В - 4 ден. ед., то сколько полок каждого типа следует выпускать в неделю?