Указания

Использование средства "Подбор параметра"

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

Для решения подобных задач Ехcel располагает средством, называемым Подбор Параметра.

Общая постановка задач, решаемых указанным средством, может заключаться в следующем. Требуется определить неизвестное X из соотношения F (А, Х,У, Z) = В, при известных зна­чениях А, У, Z и В.

Вычислительная схема решения этого уравнения рассматриваемым методом имеет вид.

	A	Раздел 2. B	C	D
	Значение A	<число>
	Значение X
	Значение Y	<число>
	Значение Z	<число>
	Значение B	<число>
	Значение функции	<формула>

=F (А, Х,У, Z)

Построение этой схемы решения заключается в следующем. Резервируются две ячейки дляввода значений параметров уравнения А (ячейка В1) и В (ячейка В5), три ячейки для зна­чений аргументов Х,У,Z (соответственно ячейки В2, ВЗ, В4) и одна ячейка для ввода собственно левой части уравнения (ячейка В6).

Следует заметить, что ячейка для значения X (ячейка В2) оставляется пустой, поскольку именно в нее Ехсе1 будет помещать результат, т.е. решение заданного уравнения.

Затем выполняется следующая последовательность действий: меню Сервис => команда Подбор параметра. Вокне диалога Подбор параметра в поле ввода Установить в ячейке вводится ссылка на ячейку В6, в поле ввода Значение вводится значение В (число, а не ссылка), в поле ввода Изменяя ячейку вводится ссылка на ячейку В2.

Аналогично можно решать уравнения относительно другой переменной У, Z (но только одной) — изменяются лишь ссылки на ячейки в поле ввода Изменяя ячейку.

Отметим некоторые особенности решения задач с использованием средства Подбор па­раметра. Как правило, оно выдает точные ответы. Однако в некоторых, даже простых, ситуа­циях это средство может не дать желаемого результата. Причина этого заключается в том, что по умолчанию средство Подбор параметра производит 100 попыток найти ответ, причем поиск прекращается, когда найден ответ с точностью до 0,001. Для получения точного результата в этом случае требуется изменить количество итераций или уменьшить относительную погрешность вычислений (меню Сервис => команда Параметры вкладка Вычисления).

Использование средства "Поиск решения"

Средство Поиск решения представляет собой мощный инструмент решения таких задач, которые удовлетворяют следующим критериям:

* цель (результат) задачи определяется как достижение определенного, максимального или минимального значения для некоторой функции, т.е. решение задач вида F(А, Х,У,Z) = В или F(А,Х,У,Z) -> ехtr(min,mах);

* на аргументы и параметры функции накладываются определенные ограничения в виде системы линейных или нелинейных равенств (неравенств).

Рассмотрим применение указанного средства для решения системы линейных уравнений. Пусть задана система линейных уравнений вида:

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

Подготовим вычислительную схему решения. Зарезервируем три ячейки для решения сис­темы (интервал В1:ВЗ) и для их интерпретаций (интервал А1:АЗ). В ячейку В4 введем левую часть первого уравнения: =а₁₁*В1+а₁₂*В2+ + а₁₃*ВЗ; в ячейку В5 — левую часть второго уравнения: =а₂₁*В1+а₂₂*В2+а₂₃*ВЗ; в ячейку В6 — левую часть третьего уравнения: =а31*В1+а32*В2+а33*ВЗ. Здесь aij— числа, используемые непосредственно в формулах, хотя для обеспечения большей гибкости вычислительной схемы для них можно было бы зарезерви­ровать ячейки; В1, В2, ВЗ — ссылки на ячейки, зарезервированные для Х1 Х2, ХЗ. Окончательно вычислительная схема принимает следующий вид:

	A	B	C	D
	Значение X1
	Значение X2
	Значение X3
	Первое уравнение	<формула>
	Второе уравнение	<формула>
	Третье уравнение	<формула>

Завершив формирование схемы вычисления, запускаем средство Поиск решения после­довательностью действий: меню Сервис => команда Поиск решения. В появляющемся диало­говом окне Поиск решения (Рис.3.2) необходимо сделать следующие установки.

Рис. 3.2. Диалоговое окно Поиск решения

В поле Установить целевую ячейку ввести ссылку на ячейку В4, в разделе Равной в по­ле Значению ввести значение b1, в поле Изменяя ячейки ввести ссылки на ячейки В1:ВЗ (ячейки, зарезервированные для значений неизвестных). Для того чтобы сформировать ограни­чения, в разделе Ограничения нажимается кнопка Добавить..., в появляющемся диалоговом окне Добавить ограничение (Рис. 3.3) формулируются ограничения следующим образом: в поле Ссылка на ячейку вносится ссылка на ячейку, в которой расположено второе уравнение системы (ячейка В5), в поле Ограничение, состоящее из двух элементов вначале выбирается из списка знак отношения '= ', затем вводится значение b2. В окне Добавить ограничение нажимается кнопка Добавить. Аналогичным образом строится второе ограничение.

Рис. 3.3. Диалоговое окно Добавить ограничения

После ввода ограничений диалоговое окно Добавить ограничение закрывается кнопкой ОК и происходит возврат к окну Поиск решения.

При этом в поле ввода раздела Ограничение появляются сформированные ограничения. Воспользовавшись кнопками Изменить и Удалить, можно скорректировать либо удалить любое ограничение.

Теперь все параметры средства Поиск решения заданы, и его можно запустить в работу. Для этого необходимо в диалоговом окне Поиск решения нажать кнопку Выполнить.

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

Решение задачи линейного программирования. Задача линейного программирования формулируется следующим образом: найти такие значения неизвестных X1,X2,X3, которые макси­мизируют линейную форму F при заданных линейных ограничениях.

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

при ограничениях

Подготовим вычислительную схему решения. Зарезервируем три ячейки для решения за­дачи (интервал В1:ВЗ) и для их интерпретаций (интервал А1:АЗ). В ячейку В4 введем формулу, определяющую линейную форму, которую требуется максимизировать: =5 *В1 - 4*В2 + 2*ВЗ; в ячейку В5 — левую часть первого ограничения: =2*В1 + В2 - 2*ВЗ; в ячейку В6 — левую часть второго ограничения: =3*В1 + 4*В2 – 3*ВЗ, в ячейку В7 — левую часть третьего ограниче­нии =2*В1 +8*В2 + 2*ВЗ.

И ячейки А4:А7 введем соответствующие интерпретации для ограничений системы.

Окончательно вычислительная схема решения примет следующий вид:

	A	B	C	D
	Значение X1
	Значение X2
	Значение X3
	Линейная форма F	<формула>
	Первое ограничение	<формула>
	Второе ограничение	<формула>
	Третье ограничение	<формула>

Завершив формирование схемы вычисления, запускаем средство Поиск решения. В появ­ляющемся диалоговом окне Поиск решения (Рис.3.2) производятся установки.

В поле Установить целевую ячейку требуется ввести ссылку на ячейку В4, в разделе Равной установить флажок максимальному значению, в поле Изменяя ячейки ввести ссыл­ки на ячейки В1:ВЗ (ячейки, зарезервированные для значений неизвестных).

Формирование системы линейных ограничений проводят аналогично изложенному выше. Задав все параметры средства Поиск решения, требуется нажать кнопку Выполнить для того, чтобы запустить его в работу.

После завершения работы средства Поиск решения в ячейках, зарезервированных для значений неизвестных, сформируется решение задачи линейного программирования.