2.1.5 Excel. Поиск решений

Excel позволяет решать сложные задачи со многими неизвестными и ограничениями. Для этого применяется математический метод линейной оптимизации.

Поиск решений можно запустить, выбрав команду меню Данные/Поиск решения. Если этого пункта в меню нет, то его следует загрузить, выполнив команду меню Сервис/Дополнения. В открывшемся диалоге следует поставить флажок Поиск решений.

По команде меню Данные/Поиск решений откроется диалог Поиск решений.

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

Для того, чтобы задать новое ограничение, следует щелкнуть на кнопке Добавить. Кнопка Удалить удаляет ограничение из списка, а кнопка Редактировать дает возможность вносить изменения в параметры условия. Поиск решения начинается щелчком на кнопке Выполнить.

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

2.2. Порядок выполнения работы

Составить математические модели. Найти решения в Excel. По каждой модели сформировать 3 отчета: Отчет по результатам, Отчет по чувствительности и Отчет по пределам. Сформулировать выводы по каждому отчету.

Задание 1. Задача оптимального использования ресурсов. Для составления плана выпуска четырех видов продукции Р1, P2, Р3, P4 на предприятии используют три вида сырья S1, S2, S3. Объемы выделенного сырья, нормы расходов сырья и прибыль, полученная в результате реализации каждого вида продукции, приведены в таблице 1.

Таблица 1

Bиды сырья	Запасы сырья	Вид продукции
		P1	P2	P3	P4
S1 S2 S3	35 30 40	4 1 3	2 1 1	2 2 2	3 3 1
Прибыль		14	10	14	11

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

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

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

Предположим, что на все филиалы предприятие для освоения 300 тыс. новых изделий может выделить 18 млн. у.е. Необходимо найти такой вариант распределения объёмов производства продукции и капитальных вложений по филиалам, при котором суммарная стоимость изделий будет минимальной.

Таблица 2

Показатель	Филиал предприятия
	1	2	3	4
Себестоимость производства изделия, руб.	83	89	95	98
Удельные капиталовложения, руб.	120	80	50	40

Задание 3. Исходя из специализации и своих технологических возможностей предприятие может выпускать четыре вида продукции. Сбыт любого количества обеспечен. Для изготовления этой продукции используются трудовые ресурсы, полуфабрикаты и станочное оборудование. Общий объем ресурсов (в расчете на трудовую неделю), расход каждого ресурса на единицу выпускаемой продукции и прибыль, полученная за единицу продукции, приведены в табл. 3. Требуется определить план выпуска, доставляющий предприятию максимум прибыли.

Таблица 3

Ресурсы		Выпускаемая продукция				Объем
		П1	П2	П3	П4	ресурсов
P1	Трудовые ресурсы, чел.-час	4	2	2	8	4800
Р2	Полуфабрикаты, кг	2	10	6	0	2400
P3	Станочное оборудование, станко-час	1	0	2	1	1500
Цена единицы продукции, руб.		65	70	60	120

Задание 4. На основании информации, приведённой в табл. 4, составить план производства, максимизирующий объём прибыли.

Таблица 4

Ресурсы	Затраты ресурсов на единицу продукции		Наличие ресурсов
	А	Б
Труд	2	4	2000
Сырьё	4	1	1400
Оборудование	2	1	800
Прибыль на единицу продукции	40	60

Задание 5.Фабрика выпускает три вида тканей, причем суточное плановое задание составляет: не менее 90 м ткани 1 вида, 70 м – 2-го вида и 60 м – 3-го вида. Суточные ресурсы следующие: 780 единиц производственного оборудования, 850 единиц сырья и 790 единиц электроэнергии, расход которых на 1 м ткани представлен в табл. 5.

Цена 1 м ткани 1-го вида равна 80 денежным единицам, 2-го вида – 70 денежным единицам, 3-го вида – 60 денежным единицам.

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

Таблица 5

Ресурс	Расход ресурса на 1 м ткани
	1	2	3
Оборудование	2	3	4
Сырье	1	4	5
Электроэнергия	3	4	2

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