9.4. Подбор параметра

Существует ряд математических пакетов, с помощью которых можно решать сложные математические задачи, например, широко используемый пакет Mathcad. Некоторые задачи можно решить и в Excel. Однако следует отметить, что в отличие от большинства современных математических пакетов, позволяющих находить решение в символьном виде, т. е. в виде аналитического выражения, в Excel можно получить только численное решение.

Например, при решении задачи дифференцирования какой-либо функции в Excel можно получить числовое значение производной в некоторой точке, но не формулу.

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

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

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

Рассмотрим пример решения трансцендентного (т. е. содержащего неалгебраические функции) уравнения:

(2x³ + 3)(1 – sin(x)) = ln(x) + 2.

Пусть начальное приближение к решению равно 1.

Рассмотрим вариант оформления данной задачи:

1) выделите ячейки A1:D1 и нажмите кнопку Объединить и поместить в центре на панели инструментов Форматирование. Введите надпись «Решение уравнения»;

2) выделите ячейки A2:D2 и нажмите кнопку Объединить и поместить в центре на панели инструментов Форматирование. Введите надпись «(2x³ + 3)(1 – sin(x)) = ln(x) + 2»;

3) в ячейку В4 введите начальное приближение 1;

4) задайте имя ячейке В4. Для этого выделите ячейку и выполните команду Вставка, Имя, Присвоить;

5) в ячейку С4 введите левую часть преобразованного уравнения, перенеся все его элементы в одну часть: «=(2*x^3 + 3)*(1 - sin(x)) – ln(x) – 2»;

6) задайте в диалоговом окне команды Подбор параметра значения, указанные на рис. 14;

7) нажмите кнопку ОК;

8) появится окно с сообщением о результате решения (см. рис. 15);

9) нажмите кнопку ОК. Электронная таблица примет вид, изображенный на рис. 16.

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