2.4. Поиск корней нелинейного уравнения
Используя возможности Excel можно находить корни нелинейного уравнения в допустимой области определения переменной. Последовательность операций (алгоритм решения) нахождения корней следующая:
Уравнение представляется в виде функции одной переменной;
Производится табулирование функции в диапазоне вероятного существования корней;
По таблице фиксируются ближайшие приближения к значениям корней;
Используя средство Excel Подбор параметра,вычисляются корни уравнения с заданной точностью.
Рассмотрим последовательность отыскания корней нелинейного уравнения на примере.
Требуется найти все корни уравнения:
на отрезке [-1; 1]. Правая часть уравнения представлена полиномом третьей степени, следовательно, уравнение может иметь не более трех корней.
Решим задачу согласно указанному алгоритму решения.
Представим уравнение в виде функции
Известно, что корни исходного уравнения
находятся в точках пересечения графика
функции с осью
.
Для выбора начальных приближений необходимо определить интервалы значений
,
внутри которых функция пересекает ось
абсцисс, то есть функция меняет знак.
С этой целью табулируем функцию на
отрезке [–1;+1] с шагом 0,2, получим
табличные значения функции (Рис. 5). Из
полученной таблицы находим, что значение
функции трижды пересекает ось
,
следовательно, исходное уравнение
имеет на заданном отрезке три корня.Анализ таблицы показывает, что функция меняет знак в следующих интервалах значений аргумента Х: (-1;-0,8), (-0,2;0,4) и (0,6;0,8). Поэтому в качестве начальных приближений возьмем значения Х: -0,8; -0,2 и 0,6 .
На свободном участке рабочего листа, как показано на рисунке, в ячейки А15:A17 введите начальные приближения, а соответствующие ячейки столбца В скопируйте формулу. Выполните команду меню Сервис/Параметры, во вкладке Вычисленияустановите относительную погрешность вычислений E=0,00001, а число итераций N=1000, установите флажокИтерации. Выполните команду менюСервис/Подбор параметра. В диалоговом окне (Рис. 2.5) заполните следующие поля:
Установить в ячейке: в поле указывается адрес ячейки, в которой записана формула правой части функции –B15;
Значение: в поле указывается значение, которое должна получить функция в результате вычислений, то есть правая часть уравнения (в нашем случае 0);
Изменяя значение: в поле указывается адрес ячейки (где записано начальное приближение), в которой будет вычисляться корень уравнения и на которую ссылается формула – $A$15.
После щелчка на ОК получим значение первого корня: -0,92.
Выполняя последовательно операции аналогичные предыдущим, вычислим значения остальных корней: -0,209991 и 0,720002.
Рис. 2.5. Поиск решения корней нелинейного уравнения
2.5. Задача о портфеле ценных бумаг
Портфель ценных бумаг состоит из 50 акций первого типа стоимостью 100 руб. с доходностью 10% и 30 акций второго типа стоимостью 50 руб. с доходностью 15%. Определить, сколько надо докупить акций второго типа, чтобы доход по всем ценным бумагам составил 12%.
Составим функцию дохода по всем ценным бумагам, аргумент которой – это количество докупаемых акций второго типа:
Откроем программу Excel. Поскольку в Excel нет такой стандартной функции, то создадим пользовательскую функцию. Нажмем Alt+F11, чтобы открыть VBA. Далее выбираем в меню Вставка (Insert) пункт Модуль (Module), а потом вписываем нашу функцию:
Function fun(x)
fun=(100*0.1*50+50*0.15*(30+x))/(100*50+50*(30+x))
End Function
Вернемся на рабочий лист программы Excel. Введем все исходные данные в рабочий лист: в ячейку B1 впишем начальное число докупаемых акций второго типа (например, 30). В ячейку B2 впишем формулу =fun(B1). Затем выберем команду «Подбор параметра» в меню Сервис. В появившемся диалоговом окне укажем, в какой ячейке нужно установить заданное значение функции (ячейка B2), чему оно равняется (вводим 0,12) и за счет изменения какого параметра это значение достигается ($B$1). Щелкнув мышью на кнопке ОК, получаем результат (рис.2.6)
.
Рис. 2.6. Результат составления портфеля акций
Функция funв этой задаче принимает значения, не превышающие 0,2. Поскольку по умолчанию установлена относительная погрешность вычислений только 0,001, велика опасность получить неправильный результат. Во вкладкеВычисленияокна «Параметры» следует установить относительную погрешность равной 0,000001. Полученный результат надо округлить до ближайшего целого числа.