О надстройке «Поиск решения»

Поиск решений является частью блока задач, который иногда называют анализ "что-если". Процедура поиска решения позволяет найти оптимальное значение формулы содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки. Запуск программы осуществляется из ниспадающего меню СЕРВИС.

Постановка задачи и оптимизация модели с помощью процедуры поиска решения

В поле Установить целевую ячейку введите ссылку на ячейку или имя конечной ячейки. Конечная ячейка должна содержать формулу. Выполните одно из следующих действий: чтобы максимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение максимальному значению; чтобы минимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение минимальному значению; чтобы установить значение в конечной ячейке равным некоторому числу, установите переключатель в положение значению и введите в соответствующее поле требуемое число. В поле Изменяя ячейки введите имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с конечной ячейкой. Допускается задание до 200 изменяемых ячеек. Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажмите кнопку Предположить. В поле Ограничения введите все ограничения, накладываемые на поиск решения.

Добавление, изменение и удаление ограничения на поиск решения

В разделе Ограничения диалогового окна Поиск решения нажмите кнопку Параметры. В поле Ссылка на ячейку введите адрес или имя ячейки, на значение которой накладываются ограничения. Выберите из раскрывающегося списка условный оператор (<=, =, >=, цел или двоич. ), который должен располагаться между ссылкой и ограничение. Если выбрано цел, в поле Ограничение появится «целое». Если выбрано двоич, в поле Ограничение появится «двоичное». В поле Ограничение введите число, ссылку на ячейку или ее имя либо формулу. Выполните одно из следующих действий. Чтобы принять ограничение и приступить к вводу нового, нажмите кнопку Добавить. Чтобы принять ограничение и вернуться в диалоговое окно Поиск решения, нажмите кнопку OK.

Пример 1. Задача о распределении ресурсов, рассмотренная выше. Условия задачи приведены на рисунке 2 в виде листа книги EXCEL.

Рис. 2. Таблица исходных данных и форма поиск решения задачи о распределении ресурсов до выполнения алгоритма.

Транспортная задача линейного программирования

Транспортная задача получила широкое распространение в практическом применении на транспорте и в строительстве. Особенно важное значение она имеет в поиске рационализации авто перевозок. Некоторый однородный продукт, сосредоточенный у m поставщиков А_i в количестве a_i(i=1,2,..,m) единиц соответственно, необходимо доставить n потребителям В_j в количестве b_j(j=1,2,...,n) единиц. Известна стоимость c_ij перевозки единицы груза от i-го поставщика к j-му потребителю. Необходимо составить план перевозок, позволяющий вывезти все грузы, полностью удовлетворить потребности и имеющий минимальную стоимость.

Обозначим через x_ij количество единиц груза, запланированных к перевозке от i-го поставщиика к i-му потребителю; тогда условие задачи можно записать в виде таблицы, которую в дальнейшем будем называть матрицей планирования.

Таблица 1.

Матрица-таблица транспортной задачи.

Поставщики	Потребители				Мощности
	B1	B2	…	Bn
A1	x11	x12	…	x1n	a1
A2	x21	x22	…	x2n	a2
…	…	…	…	…	…
Am	xm1	сxm21	…	xmn	am
Потребности	b1	b2		bn

От i-го поставщика к j-му потребителю запланировано количество x_ij продукции. Общая стоимость всего плана перевозок при этом выразится двойной суммой:

Систему ограничений получаем из следующих условий задачи:

а) все грузы должны быть вывезены, т.е. x_i1+x_i2+x_i3+ ...+x_im=A_i (i=1,2,.., m).Эти уравнения получаются из строк таблицы. б) все потребности должны быть удовлетворены, т.е. x_i1+x_i2+x_i3+ ...+x_in=B_i (i=1,2,.., n). ( из столбцов таблицы). Таким образом, математическая модель транспортной задачи имеет следующий вид: найти наименьшее значение линейной функции P при ограничениях a) и б). В рассмотренной модели предлагается, что суммарные запасы перевозок равны суммарным потребностям, то есть: A₁+A₂+ A₃+ ...A_m=B₁+B₂+B₃+ ...B_n. Такая модель называется закрытой.

1.