1.2. Поиск решения

Команда Подбор параметраявляется удобной для решения задач поиска определенного целевого значения, зависящего от одного неизвестного параметра. Для более сложных задач, когда необходимо найти оптимальное (минимальное или максимальное) или заданное значение для отдельной ячейки (целевой ячейки), путем подбора значений других ячеек (изменяемых ячеек) с учетом требуемых ограничений, следует использовать командуПоиск решения(Решатель). Для вызова данной команды воспользуйтесь пунктом менюСервис/Поиск решения.

Чтобы применить это средство, нужно определить входные данные – переменные задачи, целевую функцию и ограничения, если они существуют, то есть осуществить постановку задачи оптимизации:

найти минимальное, максимальное или заданное значение целевой функции от n переменных F(x₁, x₂,…x_n ) при наличии или отсутствии

ограничений на значения переменных:

a_i <= x_i <= b_i , i =1,2,…, n

x_i – целое , i =1,2,…, n

и функциональных ограничений, которые в общем случае имеют вид:

g_j(X) <= 0, j =0,1,2,…,J

h_k(X) = 0, k =0,1,2,…, K

Искомые переменные - ячейки рабочего листа Excel - называются регулируемыми ячейками. Целевая функция F(х₁, х₂, … , х_n), называемая иногда просто целью, должна задаваться в виде формулы в ячейке рабочего листа. Эта формула может содержать функции, определенные пользователем, и должна зависеть (ссылаться) от регулируемых ячеек. В момент постановки задачи определяется, что делать с целевой функцией. Возможен выбор одного из вариантов:

• найти максимум целевой функции F(х₁, х₂, … , х_n);

• найти минимум целевой функции F(х₁, х₂, … , х_n);

• добиться того, чтобы целевая функция F(х₁, х₂, … , х_n)имела фиксированное значение:F(х₁, х₂, … , х_n) = a.

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

1. в поле Установить целевую ячейкуввести адрес ячейки, содержащей формулу для вычисления значений оптимизируемой функции, в нашем примере целевая ячейка - это С4, а формула в ней имеет вид:= C3^2 - 5*C3 + 6;

2. для максимизации значения целевой ячейки, установить радиокнопку максимальному значению, для минимизации используется переключательминимальному значению, в нашем случае устанавливаем переключатель в положениезначениюи вводим значение0;

3. в поле Изменяя ячейкиввести адреса изменяемых ячеек, т.е. аргументов целевой функции (С3), разделяя их знаком ";" (или щелкая мышью при нажатой клавишеСtrlна соответствующих ячейках), для автоматического поиска всех влияющих на решение ячеек используется кнопкаПредположить;

4. в поле Ограниченияс помощью кнопкиДобавитьввести все ограничения, которым должен отвечать результат поиска: для нашего примера ограничений задавать не нужно;

   

5. для запуска процесса поиска решения нажать кнопку Выполнить.

Для сохранения полученного решения необходимо использовать переключатель Сохранить найденное решениев открывшемся окне диалогаРезультаты поиска решения. Полученное решение зависит от выбора начального приближения, которое задается в ячейке С4 (аргумент функции). Если в качестве начального приближения в ячейку С4 ввести значение, равное1,0, то с помощьюПоиска решениянайдем второй корень, равный2,0.

Опции, управляющие работой Поиска решения, задаваемые в окнеПараметры(окно появляется, если нажать на кнопкуПараметрыокнаПоиск решения), следующие: