Microsoft EXCEL решение нелинейных уравнений       

Решение нелинейных уравнений

Нахождение корней уравнения с помощью подбора параметра

Рассмотрим, как на рабочем листе при помощи подбора параметра можно находить корни уравнения с одним аргументом. В качестве базового примера рассмотрим следующее уравнение:

x³ - 0.01х² - 0.7044х + 0.139104 =0.

Так как мы ищем корни полинома третьей степени, то имеется не более трех вещественных корней.

Для нахождения корней их первоначально надо локализовать, т. е. найти интервалы, на которых эти корни существуют. Такими интервалами локализации корней могут служить промежутки, на концах которых функция имеет противоположный знак. С целью нахождения интервалов, на концах которых функция изменяет знак, необходимо построить ее график или ее протабулировать. Например, протабулируем наш полином на интервале [-1; 1] с шагом 0,2. С этой целью:

1. Введите в ячейку A2 значение -1, а в ячейку A3- значение -0,8.
2. Выберите диапазон A2:А3, расположите указатель мыши на маркере заполнения этого диапазона и протяните его на диапазон A4 :А12. Аргумент протабулирован.
3. В ячейку B2 введите формулу:
   =A2^3-0,01*A2^2-0,7044*A2+0,139104
4. Выберите ячейку B2. Расположите указатель мыши на маркере заполнения этой ячейки и протяните его на диапазон B3:B12. Функция также протабулирована.

На  рис. 1 видно, что полином меняет знак на интервалах [-1; -0.8], [0.2; 0.4] и [0.6; 0.8], и поэтому на каждом из этих интервалов имеется свой корень. Так как полином третьей степени имеет не более трех корней, то они все  локализованы.

Прежде чем приступить к нахождению корней при помощи подбора параметра, необходимо выполнить некоторую подготовительную работу:

• Установите точность, с которой находится корень. Корень при помощи подбора параметра находится методом последовательных приближений. Для этого выберите команду Сервис → Параметры и на вкладке Вычисления диалогового окна Параметры задайте относительную погрешность и предельное число итераций равными 0,00001 и 1000, соответственно
• Отведите на рабочем листе ячейку  под искомый корень,  например,  C2. Эта ячейка будет играть двойную роль. До применения подбора параметра в ней находится начальное приближение к корню уравнения, а после применения - найденное приближенное значение корня.

Рис. 1. Локализация корней полинома и диалоговое окно Подбор параметра

• Корень при помощи подбора параметра находим методом последовательных  приближений. Поэтому в ячейку C2 надо ввести значение, являющееся приближением к искомому корню. В нашем случае первым отрезком локализации корня является [-1; -0.8]. Следовательно, за начальное приближение к корню разумно взять среднюю точку этого отрезка - 0.9.
• Отведите  ячейку, например,  D2, под функцию, для которой ведется поиск корня.  Причем,  вместо неизвестного, у   этой функции должна указываться ссылка на ячейку, отведенную под искомый корень. Таким образом, в ячейку D2 введите формулу
  =C2^3- 0,01*C2^2-0,7044*C2+0,139104

Аналогично надо поступить с двумя другими искомыми корнями:

• Отвести ячейку C3 под второй корень, ввести в нее начальное приближение  0,3. а в ячейку D3 ввести следующую формулу
  =C3^3-0,01*C3^2-0,7044*C3+0,139104
• Отвести ячейку C4 под второй корень, ввести в нее начальное приближение 0,7, а в ячейку D4 ввести следующую формулу
  =C4^3-0,01*C4^2- 0,7044*C4+0,139104

Теперь можно переходить к нахождению первого корня уравнения:

1.       Выберите команду Сервис → Подбор параметра. На экране отобразится диалоговое окно Подбор параметра.

2.       В поле Установить в ячейке введите ссылку на ячейку D2 (рис. 1). В этом поле дается ссылка на ячейку, в которой введена формула, вычисляющая значение левой части уравнения. Для нахождения корня с помощью подбора параметра уравнение надо представить в таком виде, чтобы его правая часть не содержала переменную.

3.       В поле Значение введите 0. Здесь указывается значение из правой части уравнения.

4.       В поле Изменяя значение ячейки введите С2. В данном поле приводится  ссылка на ячейку, отведенную под переменную.

5.       Нажмите кнопку ОК.

Рис. 2. Все на корни уравнения и диалоговое окно Результат подбора параметра после успешного завершения поиска третьего корня.

Примечание. Вводить ссылки на ячейки в поля диалогового окна Подбор параметра удобнее не с клавиатуры, а выбором соответствующей ячейки на рабочем листе. При этом MS Excel автоматически будет превращать их в абсолютные ссылки - в нашем случае $D$2 и $C$2.

На экране отображается окно Результат подбора параметра с результатами работы  команды Подбор параметра. Кроме того, рассматриваемое средство помещает найденное приближенное значение корня в ячейку C2. В данном случае оно равно -0.919999.

Аналогично в ячейках C3 и C4 находятся два оставшихся корня. Они равны 0.21000 и  0.71999 (рис. 2).

Нахождение  корней уравнения методом деления отрезка пополам

Хорошим упражнением по работе с MS Excel является программирование на рабочем листе алгоритма нахождения корня уравнения F(x) = 0  методом деления отрезка пополам. Пусть непрерывная функция F(x) имеет значения разных знаков на концах отрезка [a; b], т. е. F(a)*F(b)=0.

Тогда уравнение F(x)=0 имеет корень внутри этого отрезка. Отрезок [a; b] называется отрезком локализации корня. Пусть c = (а + b) / 2 - середина отрезка [a; b]. Если F(а)*F(с)<=0, то корень находится на отрезке [a; с], который берем  за новый отрезок локализации корня. Если F(а)*F(с)=0, то за отрезок локализации корня берем [c;b]. Отметим, что новый отрезок локализации корня в два раза меньше первоначального. Процесс деления локализации корня продолжаем до тех пор, пока его длина не станет меньше, точности нахождения корня. В этом случае любая точка локализации отличается от корня не более чем на ε/2.

На рисунке приведены результаты нахождения корня с точностью до 0.001 методом деления отрезка пополам уравнения x² —2 = 0. За первоначальный отрезок локализации корня выбран [0; 2].

Для реализации этого метода введите в ячейки рабочего лист формулы либо значения (табл. 1).

Таблица 1. Формулы для нахождения корней уравнения

Примечание. На самом деле в диапазон C4 :E4 не надо вводить формулы с клавиатуры. Выберите диапазон C3: F3, расположите указатель мыши на маркере заполнения,  и протяните его на одну строку вниз.

Рис. 3 Нахождение корня уравнения методом деления отрезка пополам

Теперь осталось только выбрать диапазон A4:F4, расположить указатель мыши на маркере его заполнения и протянуть указатель вниз  до тех пор, пока в столбце F не появится сообщение о том, что корень найден. В данном случае сообщение появится в ячейке F14, а значение корня с точностью до 0.001 равно 1.414.

В заключение отметим, что в рассмотренном примере использовались:

• Операция конкатенации строк, которая объединяет несколько строк в одну. Операция конкатенация обозначается символом амперсанда &. При  объединении двух строк вторая строка добавляется непосредственно в конец первой. Результатом является строка большего размера, содержащая целиком обе исходные строки. Например, “Корень “ & “уравнения” равно одной строке “ Корень уравнения ”. Обратите внимание, что пробел в конце первой строки используется для того, чтобы в результирующей строке отделить первое слово от второго. Вместо символа конкатенации можно также использовать функцию СЦЕПИТЬ(CONCATENATE). Тот же самый результат дает формула =СЦЕПИТЬ(“Корень “;“уравнения”). Если значение Корень  введено в ячейку A1, а значение уравнения  — в ячейку B1 то тот результат получается формулой = СЦЕПИТЬ (A1;” “;B1) или A1&“ “&B1:
• Функция рабочего листа из категории функций по работе с текстом ТЕКСТ (ТЕХТ). Данная функция преобразует значение в текст в заданном числовом формате.

Синтаксис функции текст:

текст (значение; формат)

• значение — либо числовое значение, либо формула, вычисление которой дает числовое значение, либо ссылка на ячейку, содержащую числовое  значение;
• формат — числовой формат с вкладки Число диалогового окна Формат ячеек, отображаемого на экране выбором команды Формат - Ячейки. Формат не может содержать звездочку * и не может принадлежать к категории Общий.

Приведем три примера работы функции ТЕКСТ:

• =ТЕКСТ(2,7153;"0,00р.") возвращает 2.72 р.
• =ТЕКСТ(2,7153;"0,000") возвращает 2.715
• =ТЕКСТ("17.5.03";"Д МММ ГГГГ") возвращает 17 май 2003