Решение уравнений.

• Поиск параметра.
• Поиcк решения.

Как Вы уже знаете, формулы в Microsoft Excel позволяют определить значение функции по ее аргументам. Однако может возникнуть ситуация, когда значение функции известно, а аргумент требуется найти (т.е. решить уравнение). Для решения подобных проблем предназначена специальная функция Goal Seek (Подбор параметра).

Поиск параметра.

Специальная функция Goal Seek (Подбор параметра) позволяет определить параметр (аргумент) функции если известно ее значение. При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки, не возвратит заданное значение.

Рассмотрим процедуру поиска параметра на простом примере: решим уравнение 10 * x - 10 / x = 15. Здесь параметр (аргумент) - x. Пусть это будет ячейка A3. Введем в эту ячейку любое число, лежащее в области определения функции (в нашем примере это число не может быть равно нулю). Это значение будет использовано в качестве начального. Пусть это будет 3. Введем формулу =10*A3-10/A3, по которой должно быть получено требуемое значение, в какую-либо ячейку, например, B3. Теперь можно запустить функцию поиска параметра, выбрав команду Goal Seek (Подбор параметра) в меню Tools (Сервис). Введите параметры поиска:

• В поле Set cell (Установить в ячейке) введите ссылку на ячейку, содержащую необходимую формулу.
• Введите искомый результат в поле To value (Значение).
• В поле By changing cell (Изменяя значение ячейки) введите ссылку на ячейку, содержащую подбираемое значение.
• Кликните на клавише OK.

По окончании работы функции на экране появится окно, в котором будут отображены результаты поиска. Найденный параметр появится в ячейке, которая была для него зарезервирована. Обратите внимание на тот факт, что в нашем примере уравнение имеет два решения, а параметр подобран только один - это происходит потому, что параметр изменяется только до тех пор, пока требуемое значение не будет возвращено. Первый найденный таким образом аргумент и возвращается к нам в качестве результата поиска. Если в качестве начального значения в нашем примере указать -3, тогда будет найдено второе решение уравнения: -0,5.

Достаточно сложно правильно определить наиболее подходящее начальное значение. Чаще мы можем сделать какие-либо предположения об искомом параметре, например, параметр должен быть целым (тогда мы получим первое решение нашего уравнения) или неположительным (второе решение).

Задачу поиска параметра при налагаемых граничных условиях поможет решить специальная надстройка Microsoft Excel Solver (Поиск решения).

Поиcк решения.

Надстройка Microsoft Excel Solver (Поиск решения) не устанавливается автоматически при обычной установке:

• В меню Tools (Сервис) выберите команду Add-Ins (Надстройки). Если диалоговое окно Add-Ins (Надстройки) не содержит команды Solver (Поиск решения), нажмите кнопку Browse (Обзор) и укажите диск и папку, в которой содержится файл надстройки Solver.xla (как правило, это папка Library\Solver) или запустите программу установки Microsoft Office, если найти файл не удается.
• В диалоговом окне Add-Ins (Надстройки) установите флажок Solver (Поиск решения).

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

В диалоговом окне Solver (Поиск решения) так же, как и в диалоговом окне Goal Seek (Подбор параметра), необходимо указать целевую ячейку, ее значение и ячейки, которые следует изменять для достижения цели. Для решения задач оптимизации целевую ячейку следует указать равной максимальному или минимальному значению.

Если Вы щелкните на кнопке Guess (Предположить), Excel сам попытается найти все ячейки, влиящие на формулу.

Вы можете добавить граничные условия, кликнув на клавише Add (Добавить).

Кликнув на кнопке Options (Параметры), можно изменить условия поиска решения: максимальное время поиска решения, количество итераций, точность решения, допуск на отклонение от оптимального решения, метод экстраполяции (линейная или квадратичная), алгоритм оптимизации и т.д.

Вернемся к предыдущему примеру: для того, чтобы получить второе (неположительное) решение, достаточно добавить граничное условие A3<=-0.01. Так же как и при подборе параметра, на экране появится окно, в котором будет отображен отчет о результатах поиска требуемого решения. Само решение будет показано в предназначенных для него ячейках (в ячейке A3 отобразится значение -0.50).

Надстройка Microsoft Excel Solver (Поиск решения) позволяет, также, решать системы уравнений или неравенств. Рассмотрим простой пример: попробуем решить систему уравнений

x + y = 2
x - y = 0

 

• Введем в ячейки, предназначенные для решения (A1:A2) произвольные величины, лежащие в области определения (начальные значения).
• В ячейки B1 и B2 внесем формулы, по которым должны вычисляться правые части уравнений (= A1 + A2 и = A1 - A2).
• Запустим Solver (Поиск решения) из меню Tools (Сервис).
• Выберем одну из ячеек, содержащих формулы, в качестве целевой ячейки (например, B1), сделаем ее равной 2.
• Кликнем на кнопке Guess (Предположить) для того, чтобы Excel определил влияющие ячейки (A1:A2).
• Добавим ограничение B2 = 0.
• Кликнем на клавише Solve (Выполнить).

Результаты поиска отобразятся в предназначенных для решения ячейках (A1:A2), отчет о результатах появится на экране.