Решение уравнений с помощью встроенных функций
Рассмотрим решение двух типов уравнений:
линейных вида
;квадратных вида
.
В качестве инструмента для решения применяется программа Microsoft Excel.
5.1. Решение линейных уравнений вида
Значения коэффициентов a и b будем водить соответственно в ячейки B2 и B3, а информацию об этом запишем в ячейки A2 и A3 (рис. 1.19). В ячейке B4 будем выводить информацию о том, имеет ли заданное пользователем уравнение корень или нет, и в случае положительного ответа значение корня укажем в ячейке D4.
Рис. 1.19
Формула в ячейке B4 должна иметь вид:
=ЕСЛИ(B2=0;"Нет";"Есть")
– а в ячейке С4:
=ЕСЛИ(B2<>0;"Его значение";"")
– а в ячейке D4:
=ЕСЛИ(B2<>0;-B3/B2;"")
Вид фрагмента листа для двух возможных случаев приведен на рис. 1.20-1.21.
Рис. 1.20
Рис. 1.21
Очевидно, что при представленном оформлении листа в ячейке B4 сообщение Нет будет выводиться даже в случае, когда коэффициент a вообще не задан, что не совсем корректно. Чтобы устранить этот недостаток, можно использовать функцию ЕПУСТО(), возвращающую значение ИСТИНА, если в ячейке, адрес которой указан в качестве аргумента функции в скобках, значение отсутствует.
С использованием указанной функции формулы в ячейках A4, B4, C4 и D4 будут иметь вид (соответственно):
=ЕСЛИ(ЕПУСТО(B2);"";"Есть ли корень?")
=ЕСЛИ(ЕПУСТО(B2);"";ЕСЛИ(B2=0;"Нет";"Есть"))
=ЕСЛИ(ЕПУСТО(B2);"";ЕСЛИ(B2<>0;"Его значение";""))
=ЕСЛИ(ЕПУСТО(B2);"";ЕСЛИ(B2<>0;-B3/B2;""))
5.2. Решение квадратных уравнений вида
Здесь значения коэффициентов a, b и c будем задавать соответственно в ячейках B2, B3 и B4, а информацию об этом запишем в ячейки A2, A3, A4 (рис. 1.22). В ячейке B5 будем выводить информацию о том, имеет ли заданное пользователем уравнение корень или нет, и в случае положительного ответа значение корней укажем в ячейках D4 и D6.
Рис. 1.22
Очевидно, что для решения квадратного уравнения следует рассчитывать его дискриминант. Однако значение дискриминанта уравнения является вспомогательным и для пользователя может быть скрыто, например, выведено за пределы страницы, оформлено белым цветом и т.п. Мы вычисляем значение дискриминанта за пределами страницы в ячейке M3. Формула для его расчета следующая:
=B3^2-4*B2*B4.
А формулы в ячейках B5, C5, C6, D5 и D6 соответственно:
=ЕСЛИ(M3<0;"Нет";"Есть")
=ЕСЛИ(M3>=0;"Значение первого корня";"")
=ЕСЛИ(M3>=0;"Значение второго корня";"")
=ЕСЛИ(M3>=0;(-B3-M3^0,5)/(2*B2);"")
=ЕСЛИ(M3>=0;(-B3+M3^0,5)/(2*B2);"")
Нахождение корней уравнения с помощью подбора параметра
В данном пункте будет продемонстрировано, как на рабочем листе при помощи подбора параметра можно находить корни уравнения с одним аргументом. В качестве базового примера рассмотрим следующее уравнение:
.
Примечание
Так как мы ищем корни полинома третьей степени, то имеются не более трех вещественных корней.
Для нахождения корней их первоначально надо локализовать, т.е. найти интервалы, на которых эти корни существуют. Такими интервалами локализации корней служат промежутки, на концах которых функция имеет противоположный знак. С целью нахождения интервалов, на концах которых функция изменяет знак, необходимо построить ее график или ее протабулировать. Например, протабулируем наш полином на интервале [-1; 1] с шагом 0,2.
Из рис. 1.23 видно, что полином меняет знак на интервалах [-1; -0,8], [0,2; 0,4] и [0,6; 0,8], и поэтому на каждом из этих интервалов имеется свой корень. Так как полином третьей степени имеет не более трех корней, то они все и локализованы.
Рис. 1.23. Локализация корней полинома и диалоговое окно Подбор параметра
Прежде чем приступить к нахождению корней при помощи подбора параметра, необходимо выполнить некоторую подготовительную работу:
Установите точность, с которой находится корень. Корень при помощи подбора параметра находится методом последовательных приближений. Для этого выберите команду Сервис Параметры и на вкладке Вычисления диалогового окна Параметры задайте относительную погрешность и предельное число итераций равными 0,00001 и 1000, соответственно.
Отведите на рабочем листе ячейку, например C2, под искомый корень. Эта ячейка будет играть двоякую роль. До применения подбора параметра в ней находится начальной приближение к корню уравнения, а после применения – найденное приближенное значение корня.
Корень при помощи подбора параметра находим методом последовательных приближений. Поэтому в ячейку C2 надо ввести значение, являющееся приближением к искомому корню. В нашем случае, первым отрезком локализации корня является [-1; -0,8] . Следовательно, за начальное приближение к корню разумно взять среднюю точку этого отрезка -0,9.
Отвести ячейку, например D2, под функцию, для которой ведется поиск корня, причем вместо неизвестной y этой функции должна указываться ссылка на ячейку, отведенную под искомый корень. Таким образом, в ячейку D2 введите формулу
=C2^3-0,01*C2^2-0,7044*C2+0,139104
Аналогично надо поступить с другими искомыми корнями.
Теперь можно переходить к нахождению корня уравнения:
Выберите команду СервисПодбор параметры. На экране отобразится диалоговое окно Подбор параметра.
В поле Установить в ячейке введите ссылку на ячейку D2 (рис. 1.24). В этом поле дается ссылка на ячейку, в которой введена формула, вычисляющая значение левой части уравнения. Для нахождения корня с помощью подбора параметра уравнение надо представить в таком виде, чтобы его правая часть не содержала переменную.
В поле Значение введите 0. Здесь указывается значение из правой части уравнения.
В поле Изменяя значение ячейки введите C2. В данном поле приводится ссылка на ячейку, отведенную под переменную.
Нажмите кнопку OK.
Рис. 1.24. Все найденные корни уравнения и диалоговое окно Результат подбора
параметра после успешного завершения поиска третьего корня
Примечание
Вводить ссылки на ячейки в поле диалогового окна Подбор параметра удобно не с клавиатуры, а выбором соответствующей ячейки на рабочем листе. При этом MS Excel автоматически будет превращать их в абсолютные ссылки.
На экране отображается окно Результат подбора параметра с результатами работы команды Подбор параметра. Кроме того, рассматриваемое средство помещает найденное приближенное значение корня в ячейку C2. В данном случае оно равно -0,919999.
Аналогично в ячейках C2 и C4 находятся два оставшихся корня. Они равны 0,21000 и 0,71999 (рис. 1.24).