Режим Подбор параметра определяет значение одной входной (изменяемой) ячейки, которое обеспечивает получение необходимого результата в зависимой (целевой) ячейке.
Поиск решения осуществляется в Excel методом итераций (от лат. iteratio – повторение, последовательное приближение). Порядок работы при этом следующий:
1) в изменяемой ячейке задать начальное (приближенное) значение искомого параметра;
2) в целевой ячейке ввести формулу для расчета результата;
3) выполнить команду меню Сервис→Подбор параметра, ввести в открывшемся окне необходимые данные и нажать OK.
При этом если начальное значение искомого параметра не дает требуемого результата в целевой ячейке, то Excel автоматически делает следующий шаг итерационного процесса и находит новое значение параметра в изменяемой ячейке. Этот процесс продолжается до тех пор, пока не будет найдено нужное значение (при условии, что решение задачи существует). По умолчанию команда Подборпараметра прекращает вычисления после выполнения 100 итераций или при получении результата, который находится в пределах 0,001 от заданного целевого значения. При необходимости изменить эти установки можно в меню Сервис→Параметры. Для этого на вкладке Вычислениянадо изменить значения полей Предельноечислоитерацийи/или Относительнаяпогрешность.
Если задача имеет несколько решений, то будет найдено одно из них. Если найденное решение не устраивает пользователя, можно процесс поиска повторить, задав новое начальное приближение, точность вычисления и предельное число итераций.
Пример 1. Для перевозки груза весом 400 т был подан состав из 5 крытых вагонов и 7 платформ. Каков должен быть вес груза в вагоне каждого типа, если на погрузку установлены следующие ограничения: вес груза в вагонах одного типа должен быть одинаковым, а вес груза на платформе в 2 раза больше, чем в крытом вагоне?
Введем на листе Excel исходные данные и соответствующие формулы (рис. 1). Затем установим курсор в целевую ячейку (ячейку с результирующей формулой) C4 и выполним команду Сервис→Подбор параметра, в открывшемся окне введем необходимые данные (рис. 2). После нажатия на кнопку OK в ячейках C2, C3 и C4 получаем результат (рис. 3).
а б
Рис. 1. Вид рабочего листа Excel для примера 1 (а – обычный режим просмотра, б – режим показа формул)
Рис. 2. Вид окна Подбор параметра для примера 1
Рис. 3. Вид окна Результат подбора параметра для примера 1
Аналогичным образом Excel позволяет легко решить уравнение с одним неизвестным. Общие рекомендации по нахождению корней уравнений можно сформулировать следующим образом:
1) построить таблицу значений или график функции и определить, на каком интервале изменения аргумента функция меняет знак;
2) определить начальное (приближенное) значение искомого корня уравнения и задать его во входной (изменяемой) ячейке;
3) ввести решаемое уравнение (формулу) в целевую ячейку (т. е. ячейку результата);
4) ввести необходимые данные в меню Сервис→Подбор параметра.
Пример 2. Рассмотрим пример решения трансцендентного (т. е. содержащего неалгебраические функции) уравнения x3=ex.
Для удобства преобразуем это уравнение к виду x3 – ex= 0.
Для определения начального приближения корня протабулируем функцию и построим ее график, например, на отрезке [0; 3] (рис. 4).
Из таблицы значений функции и графика следует, что функция меняет знак на отрезке xЄ[1,5; 2], значит, на этом отрезке находится корень функции. Для уточнения его значения выберем в качестве начального приближения корня любую границу этого отрезка, например, 1,5. Введем это значение в ячейку A14. Для удобства присвоим ей имя x. В ячейку B14 введем формулу – левую часть решаемого уравнения, т. е. =x^3-EXP(x). Затем, находясь в этой ячейке, выполним команду Сервис→Подбор параметра и в диалоговом окне зададим необходимые параметры (см. рис. 4). При этом в окошке «Значение» вводится правая часть решаемого уравнения (в нашем случае 0).
Рис. 4. Вычисление корня уравнения
Установки в окне «Подбор параметра» на рис. 4 поручают Excel перебирать значения в ячейке x (A14) до тех пор, пока зависящее от x выражение, записанное в ячейке B14, не достигнет заданного значения 0.
По окончании процесса поиска получим значение корня x=1,85694 (рис. 5).
Рис. 5. Вид окна Результат подбора параметра для примера 2
