Материал: Контрольная работа по теме Базы данных в Excel 72 IV. Макросы в ms excel 78 Макросы для автоматизации работ 78

Необходимо составить дневной рацион нужной питательности, причём затраты на него должны быть минимальными. Для составления математической модели задачи обозначим через х_i, количество килограммов корма К_i в дневном рационе (i =1, 2,…,5). Принимая во внимание значения, приведённые в табл. 2, и условие, что дневной рацион удовлетворяет требуемой питательности только в случае, если количество единиц питательных веществ не меньше предусмотренного, получаем систему ограничений: . (4)Очевидно, что должны выполняться условия неотрицательности неизвестных:х_i , (i =1,…,5) (5)Общую стоимость дневного рациона можно выразить в виде линейной функцииf= 12x₁+ 36x₂+32x₃+ 18x₄+10x₅. (6)Таким образом, задача заключается в нахождении решения системы (4), удовлетворяющего условиям (5), при котором функция (6) принимает минимальное значение.Рассмотрим этапы реализации данной задачи в MS Excel.В Excel необходимо создать таблицу с формулами, которые связывают план, ограничения и целевую функцию Стоимость (рис. 2.2):Рис. 2.2. Таблица с исходными данными и формульными зависимостямиПрограмма Поиск решений запускается командой: Сервис > Поиск решения.В полях Установить целевую ячейку, Изменяя ячейки, Ограничения вводятся соответствующие данные (рис. 2.3).Рис. 2.3. Окно Поиск решенияТак как это линейная модель (целевая функция S является линейной), то необходимо установить в окне Параметры поиска решений переключатель в позицию Линейная модель. После нажатия на кнопку Выполнить в появившемся окне Результаты поиска решения укажите Отчет по устойчивости. Результаты поиска решения и полученный отчет представлены на рисунках 2.4 и 2.5.

Рис. 2.5. Отчет по устойчивостиОтчет по устойчивости отражает чувствительность структуры полученного плана до изменений начальных данных и дальнейшие действия менеджера с целью улучшения результатов. Такой отчет не создается для моделей, значения в которых ограничены множеством целых чисел. 1. Результирующее значение – оптимальный план задачи. В данной конкретной задаче оптимальный рацион минимальной стоимости 150 д. ед. состоит из 0,83 кг сушеной рыбы, 5 кг. фруктов и 3,33 л. молока.2. Нормированная стоимость неизвестных плана указывает, как изменится стоимость рациона при желании добавить в его состав «невыгодный» продукт, например, единица хлеба в рационе увеличит его стоимость на 0,2 д. ед., единица сои – на 4,6 д. ед.3. Коэффициенты целевой функции.4, 5. Границы изменений значений коэффициентов целевой функции при условии, что количество оптимальной продукции (план) не изменится. Например, если целевой коэффициент Фруктов (КФ) равен 18 (цена за 1 кг. товара), то изменяя его в рамках 18-0,22<КФ<18+2, 17,78<КФ<20 план не изменится, но значения стоимости может уменьшиться или увеличиться. 6. Количество использованных ресурсов;7. Теневые цены показывают уровень влияния значения норм (в сравнении с другими ресурсами) на стоимость рациона относительно ее увеличения. В данном примере нормы на состав витаминов более «влиятельные» на стоимость, чем белки (2,5>2,2).Например, если увеличить норму витаминов на 1 единицу (до 41), то стоимость увеличится на 2,5 д. ед. и будет составлять 152,5 д. ед.8. Нормы белков, жиров, углеводов и витаминов в дневном рационе. Соответствуют условию задачи.9, 10. Задают диапазон для 8, в котором действует теневая цена 7 (аналогично 4, 5). 2.3.3. Варианты заданий

1. 
   Фирма производит три вида изделий – А, В и С. Для их выпуска требуется обработка на станках: I, II, III, IV. Время обработки на станках, а также прибыль от реализации изделия каждого вида приведены в таблице.
   

Изделие	Время обработки, ч				Прибыль, $
	I	II	III	IV
А	1	3	1	2	3
В	6	1	3	3	6
С	3	3	2	4	4

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

2. 
   Фирме для производства требуется уголь с содержанием фосфора не более 0.03% и с примесью пепла не более 3.25%. Доступны три сорта угля – А, В и С, параметры которых приведены в таблице.
   

Сорт угля	Содержание фосфора, %	Содержание пепла, %	Цена, $
А	0,06	2,0	30
В	0,04	4,0	30
С	0,02	3,0	40

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

3. 
   Фирма производит два продукта А и В, рынок сбыта которых неограничен. Каждый продукт должен быть обработан на машинах: I, II, III. Время обработки в часах для каждого из изделий А и В приведено в таблице.
   

	I	II	III
А	0,5	0,4	0,2
В	0,25	0,3	0,4

Недельный фонд рабочего времени машин I, II, III равен соответственно 40, 36 и 36 часам. Прибыль от изделий А и В составляет соответственно 5 и 3 доллара. Фирме надо определить недельные нормы выпуска изделий А и В, максимизирующие прибыль.

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

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

Наименование конфет	Вес конфет в наборе, кг			Запасы конфет, кг
	А	В	С
«Сникерс»	0,3	0,2	0,4	600
«Марс»	0,2	0,3	0,2	700
«Баунти»	0,2	0,1	ОД	500
Цена, руб.	72	62	76

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

Тип изделия	Цена, руб.	Вес, кг
I	400	12
II	500	16
III	600	15

Общий вес комплектующих должен быть равен 326 кг. Определить максимальную и минимальную возможную суммарную стоимость находящихся в контейнере комплектующих изделий.

6. 
   Предприятие электронной промышленности выпускает две модели радиоприемников, причем каждая модель выпускается на отдельной технологической линии. Максимальная производительность линий составляет 60 и 75 радиоприемников в сутки. На приемники первой модели расходуется 10 типовых электронных схем, а на вторую – 8 схем. Суточный запас схем равен 800 единиц. Прибыль от реализации одного радиоприемника первой и второй модели равна соответственно 30 и 20$. Определить оптимальный суточный объем производства радиоприемников первой и второй моделей.
   
7. 
   Процесс изготовления двух видов промышленных изделий состоит в последовательной обработке каждого из них на трех станках. Суточный фонд машинного времени каждого станка равен 10 часов. Время обработки и прибыль от продажи каждого изделия приведены в таблице.
   

Изделие	Время обработки одного изделия, мин			Прибыль, $
	Станок 1	Станок 2	Станок 3
1	10	6	8	2
2	5	20	15	3

Найти оптимальный объем производства изделий каждого вида.

8. 
   Фирма производит два вида продукции – А и В. Объем сбыта продукции А составляет не менее 60% общего объема реализации продукции обоих видов. Для изготовления продукции используется одно и то же сырье, суточный запас которого равен 100 кг. Расход сырья на единицу продукции А составляет 2 кг, а на единицу продукции В – 4 кг. Цены на продукцию А и В равны соответственно 20$ и 40$. Составить план распределения сырья для изготовления продукции А и В так, чтобы затраты были минимальные.
   
9. 
   Кондитерская фабрика в Покрове освоила выпуск новых видов шоколада «Лунная начинка» и «Малиновый дождик», спрос на которые составляет соответственно не более 12 т и 7,7 т в месяц. По причине занятости трех цехов выпуском традиционных видов шоколада, каждый цех может выделить только ограниченный ресурс времени в месяц. В силу специфи­ки технологического оборудования затраты времени на произ­водство шоколада разные и представлены в таблице.
   

Номер цеха	Время на производство шоколада, ч		Время, отведенное цехами под производство, ч/мес
	«Лунная начинка»	«Малиновый дождик»
I	1	7	56
II	2	3	35
III	3	2	40
Оптовая цена, руб./т.	8000	6000

Определить оптимальный объем выпуска шоколада, обеспе­чивающий максимальную выручку от продажи.

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

Сырье	Расход сырья на производство, г		Поставки сырья в неделю, кг
	ваза	графин
Кобальт	20	18	30
Сусальное 24-каратное золото	13	10	12
Оптовая цена, руб. /шт.	800	560

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

11. 
    Изделия четырех типов проходят последовательную обработку на двух станках. Суточный фонд машинного времени каждого станка равен 10 часов. Время обработки и прибыль от продажи каждого изделия приведены в таблице.