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

На практике часто возникают ситуации, когда желаемый результат одиночной формулы известен, но не известны значения, которые требуется ввести для получения этого результата. В этом случае очень удобно воспользоваться надстройкой Подбор параметра электронной таблицы Microsoft Excel. Воспользоваться средством «Подбор параметра» можно, выбрав команду Подбор параметра в меню Сервис. Говоря математическим языком, мы допускаем, что наша формула (целевая функция) меняется с изменением значения только одного параметра. Подходящее значение этого параметра отыскивается вычислительными методами, производящими итерационные вычисления для нахождения нужного решения (если, конечно, оно существует). Тем самым ищется решение уравнения с одним неизвестным. Подбор параметра является частью блока задач, который иногда называют инструментами анализа " что-если ".

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

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

Давайте теперь рассмотрим примеры практического применения надстройки Подбор параметра.

2.2. Пример использования надстройки «Подбор параметра»

Возьмем задачу из школьного учебника: Определение времени встречи двух объектов.

Из одного пункта по одной дороге вышли два путника. Первый идет со скоростью 5 км/ч, второй — 10 км/ч. Когда они встретятся, если первый вышел на два часа раньше?

Задачу можно сформулировать и так: при каком значении переменного параметра «время» разница в расстоянии между пешеходами будет равна нулю?

Откроем программу Excel. Введем все исходные данные в рабочий лист (рис. 1). Путь, пройденный первым путником, вычисляется по формуле =B3×B1, а вторым — по формуле =(B3-B6)×B2. Разница путей, в свою очередь, будет определена по формуле =B4-B5.

Рис. 2.1. Введенные данные задачи и вызванное окно надстройки «Подбор параметра»

Выбрав команду «Подбор параметра» в меню Сервис, видим диалоговое окно «Подбор параметра» (рис. 1). В данном окне укажем, в какой ячейке нужно установить заданный результат (в ячейке B7), чему равняется это значение (0) и за счет изменения какого параметра оно достигается ($B$3). Щелкнув мышью на кнопке ОК, получаем результат (рис. 2.2).

Конечно, приведенный пример легко решается и без Excel, однако, используя ту же методику, в Excel можно столь же быстро решать намного более сложные задачи.

Рис. 2.2. Результат подбора параметра