5.4. Процедура Поиск решения

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

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

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

• определить переменные модели;

• выбрать, что именно должно считаться решением;

• записать ограничения на переменные.

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

Пример. Нужно провести водопровод длиной 167 м. Имеются трубы длиной 5 м и 7 м. Сколько нужно использовать тех и других труб, чтобы сделать наименьшее количество соединений?

Построение математической модели. Обозначим через переменную х количество 7-метровых труб, а через через у – количество 5-метровых. Отсюда получаем ограничение, связанное с длиной трубопровода: 7х + 5у = 167. Кроме того, на решение накладываются еще одно условие: x > 1, y > 1 – целые.

Целевая функция, определяющая число стыков, имеет вид:

F = x + y min.

Т

	A	B	C	D
2	Длина труб, м	7	5	=B2*B3+C2*C3
3	Количество труб	0	0
4	Число стыков	=B3+C3

Рис. 26. Исследование линейной функции

ехнология решения задачи в Excel. Построена таблица размещения данных, рис. 26. Здесь в ячейки B2:C2 введены исходные данные. Под значения переменных x и y отведены ячейки B3:C3. В начальном варианте они заполняются произвольными числами – в таблице эти значения равны нулю. В ячейку D3 введена формула для расчета длины трубопровода, в ячейку B4 – целевая функция.

После заполнения экранной формы подключена процедура Поиск решения (рис. 26).

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

Переключатель параметров установлен в положение, соответствующее поиску минимума целевой функции.

В поле Изменяя ячейки введен диапазон изменяемых ячеек.

В списке Ограничения введены условия, которые накладываются на диапазон изменения переменной. Элементы списка сформированы с помощью кнопки Добавить.

Запуск задачи на решение произведен путем нажатия кнопки Выполнить. В ячейках B3:C3 получено решение: соответственно числа 21 и 4.

Задание 19. С помощью процедуры Поиск решения на листе Поиск1 найдите решение для классической задачи из рассказа А.П.Чехова «Репетитор», предварительно построив математическую модель.

Купец купил 138 аршин черного и синего сукна за 540 р. Спрашивается, сколько аршин купил он того и другого, если синее стоило 5 р. за аршин, а черное – 3 р. за аршин?

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

П

Детали	Изделие				Запас деталей
	a1	a2	a3	a4
Тип 1	1	1	1	1	16
Тип 2	6	9	10	15	110
Тип 3	4	6	10	13	150

Рис. 27. Расход комплектующих деталей на изделия

ример. Завод выпускает изделия четырех наименований: a₁, a₂, a₃, a₄. Прибыль на одно изделие составляет 100, 115, 110 и 120 у.е. соответственно. Для их изготовления используются комплектующие детали трех типов, расходы которых на каждое изделие известны (рис. 27). Определим план выпуска изделий, приносящий максимальную прибыль в предположении, что запас деталей каждого типа ограничен.

Построение математической модели. Обозначим неизвестные задачи (количество выпускаемых изделий) через x_i. Тогда целевая функция, определяющая общую прибыль завода, будет иметь вид:

F(X)= , max, где c_i – прибыль на одно изделие.

Обозначим расход деталей первого типа на изделие через b1, второго типа – b2, третьего типа – b3. Ограничения по расходу комплектующих деталей на программу выпуска записываются следующим образом:

, , .

Кроме того, решение дополняется ограничением: x_i ≥ 0 – целые.

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

П

Рис. 28. Размещение данных в задаче планирования производства

еременным задачи x_i отведены ячейки B4:E4, рис. 28. Коэффициентам c_i целевой функции соответствуют ячейки B5:E5. Коэффициентам левой части ограничений (b1_i, b2_i, b3_i) отведены ячейки B7:E9.

В ячейку F5 введена формула для вычисления целевой функции. Формула записана как сумма произведений каждой ячейки, отведенной под значения переменных, на соответствующую ячейку, отведенную для коэффициентов c_i целевой функции. Копированием этой формулы в диапазон ячеек F7: F9 заданы зависимости левых частей ограничений.

Дальнейшие действия проведены в окне Поиск решения, рис. 28. Полученное решение выглядит следующим образом. Оптимальным является производство изделий в количестве: x₁ = 1, x₂ = 7, x₃ = 0, x₄ = 8. Этот объем производства принесет заводу 1865 у.е. прибыли.

Задание 20. Для изготовления двух видов изделий Ф1 и Ф2 завод расходует в качестве сырья сталь и цветные металлы, а также токарные и фрезерные станки, ресурсы которых ограничены (рис. 29).

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

1

Затраты на одно изделие		Ф1	Ф2	Ресурсы
Материалы	Сталь (кг)	10	70	320
	Цветные металлы (кг)	20	50	420
Станки	Токарные (станко-час.)	300	400	6200
	Фрезерные (станко-час.)	200	100	3400
Прибыль на одно изделие (тыс. р.)		3	8

Рис. 29. Исходные данные к заданию 20

. Постройте математическую модель задачи.

2. Решите задачу на рабочем листе Поиск2.

3. Изучите окно настройки параметров процедуры Поиска решения (кнопка Параметры). Проведите настройку соответствующих параметров при решении задачи.