Решение уравнений в Microsoft Excel презентация к уроку

Решение уравнений в Microsoft Excel Выполнила Соколова М.А.

Вариант № 13 индивидуального расчетного задания Найдите приближенное значение уравнения с точностью 0,001 Представьте графически поставленную задачу;

Состав задания: Ознакомиться с теоретической частью задания; Провести расчет для своего варианта индивидуального задания в Microsoft Excel Оформить презентацию в Ms Power Point , включающую: § постановку задачи; § алгоритм расчета; § таблицу с расчетом из Ms Excel , график исходной функции; результат расчета и его анализ.

Постановка задачи: Пусть дано уравнение f(x) = 0, (a, b) — интервал, на котором f(x) имеет единственный корень. Нужно приближенно вычислить этот корень с заданной точностью. Примечание: Заметим, что если f(x) имеет k корней, то нужно выделить соответственно k интервалов.

Общая постановка задачи. Найти действительные корни уравнения f ( x ) =0 , где f ( x ) –алгебраическая или трансцендентная функция. Точные методы решения уравнений подходят только к узкому классу уравнений ( квадратные, биквадратные, некоторые тригонометрические, показательные, логарифмические) Задача численного нахождения корней уравнения состоит из двух этапов: 1.Отделение(локализация) корня; 2.Приближенное вычисление корня до заданной точности (уточнение корней)

6 Уточнение корня . Если искомый корень уравнения f(x)=0 , отделен, т.е. определен отрезок [ a , b ], на котором существует только один действительный корень уравнения, то далее необходимо найти приближенное значение коня с заданной точностью. Такая задача называется уточнения корня. Уточнения корня можно производить различными методами: 1)Метод половинного деления(бисекции); 2)Метод итераций; 3)Метод хорд(секущих); 4)Метод касательных(Ньютона); 5)Комбинированные методы.

индивидуальное расчетное задание Дано: Найти: Отделить корень заданного уравнения, пользуясь графическим методом, и вычислите один корень с точностью 0,001 при помощи программы Microsoft Excel

Графический метод: Для отделения корней уравнения естественно приме­нять графический метод. График функции у = f ( х ) с уче­том свойств функции дает много информации для опре­деления числа корней уравнения f ( х ) = 0. До настоящего времени графический метод предлага­лось применять для нахождения грубого значения корня или интервала, содержащего корень, затем применять итерационные методы, т. е. методы последовательных приближений для уточнения значения корня. С появле­нием математических пакетов и электронных таблиц ста­ло возможным вычислять таблицы значений функции с любым шагом и строить графики с высокой точностью. Это позволяет уточнять очередной знак в приближенном значении корня при помощи следующего алгоритма: 1) если функция f ( x ) на концах отрезка [ а , b ] значения разных принимает значения разных знаков то делим отрезок на 10 равных частей и находим ту часть, которая содержит корень (таким способом мы можем уменьшить длину отрезка, содержащего корень, в 10 раз); 2) повторим действия предыдущего пункта для полу­ченного отрезка. Этот процесс можно продолжать до тех пор, пока длина отрезка не станет меньше заданной погрешности.

Метод половинного деления: Постановка задачи: Пусть дано уравнение f(x) = 0, (a, b) — интервал, на котором f(x) имеет единственный корень. Нужно приближенно вычислить этот корень с заданной точностью. Примечание: Заметим, что если f(x) имеет k корней, то нужно выделить соответственно k интервалов. Метод половинного деления или дихотомии ): Метод основан на той идее, что корень лежит либо на середине интервала (a, b) , либо справа от середины, либо — слева, что следует из существования единственного корня на интервале (a, b) . Алгоритм для программной реализации: а:=левая граница b:= правая граница m:= ( a+b )/2 середина определяем f(a) и f(m) если f(a)*f(m) e повторяем , начиная с пункта2 m- искомый корень.

Расчет уравнения по методу половинного деления:

Метод простой итерации: Смысл метода простой итерации состоит в том, что мы представляем уравнение f(x) в виде и по формуле будем строить итерации, которые сходятся к искомому корню с интересующей степенью точности, но тут есть проблемы: возможно f(x) очень сложно представить в таком виде, да и не факт, что любая будет строить сходящиеся итерации, поэтому алгорим сводится к тому, чтобы оптимально найт и . Подготовка: Ищем числа m и M такие, что на (a, b) ; Представляем , где ; Алгоритм: 1. Выбираем х 0 из (a, b) ; 2.Вычисляем ; 3.Проверяем условие , где q=(M-m)/( M+m ) ; 4.Если оно ложно, то переходим к пункту 7; 5. х 0 =х 1 ; 6.Переходим к пункту 2 ; 7. х 1 –искомый корень.

Расчет уравнения по методу простой итерации:

Метод хорд Метод хорд заключается в замене кривой у = f ( x ) отрезком прямой, проходящей через точки ( а , f ( a )) и ( b , f ( b )) . Абсцисса точки пересечения прямой с осью ОХ принимается за очередное приближение. Чтобы получить расчетную формулу метода хорд, за­пишем уравнение прямой, проходящей через точки ( a , f ( a )) и ( b , f ( b )) и, приравнивая у к нулю, найдем х : Алгоритм метода хорд : 1) П усть k = 0; 2) В ычислим следующий номер итерации: k = k + 1. Найдем очередное k -e приближение по формуле: x k = a — f ( a )( b — a )/( f ( b ) — f ( a )). Вычислим f ( x k ); 3) Е сли f ( x k )= 0 (корень найден), то переходим к п. 5. Если f ( x k ) × f ( b )>0, то b = x k , иначе a = x k ; 4) Е сли |x k – x k -1 | > ε , то переходим к п. 2; 5) В ыводим значение корня x k ; 6) К онец.

Расчет уравнения по методу хорд:

Метод касательных В точке пересечения касательной с осью Оx переменная у = 0. Приравнивая у к нулю, выразим х и получим формулу метода касательных: Теорема. Пусть на отрезке [а, b]выполняются условия: 1) функция f(x)и ее производные f ‘(х)и f »(x)непрерывны; 2) производные f ‘(x)и f »(x)отличны от нуля и сохраняют определенные постоянные знаки; 3) f(a)× f(b) 0, то итерационная последовательность сходится монотонно

Расчет уравнения по методу касательных:

Вывод о проделанной работе: Вывод: Решение уравнения в Microsoft Excel Было выполнено: графическим методом, методом половинного деления , хорд, касательных, простой итерации. Графический метод самый неточный, чем остальные методы. метод половинного деления быстрее графического метода, а метод простой итерации намного точнее предыдущих. Метод хорд более точный, чем все остальных методы. Метод касательный относительно быстрее и точнее всех методов.

Список использованной литературы и интернет-источников Зенков , А.В. ЧИСЛЕННЫЕ МЕТОДЫ /А.В. Зенков . — Екатеринбург: Издательство Уральского университета, 2016. — 127с. Вычислительные методы // Википедия. [2010—2019]. Дата обновления: 31.01.2019. URL: https://ru.wikipedia.org/?oldid=97827303 (дата обращения: 20.05.2019); Численное решение уравнений // Википедия. [2010—2018]. Дата обновления: 01.01.2018. URL: https://ru.wikipedia.org/?oldid=89982922 (дата обращения: 20.05.2019);

Метод итерации — это численный и приближенный метод решения СЛАУ.

Суть: нахождение по приближённому значению величины следующего приближения, которое является более точным. Метод позволяет получить значения корней системы с заданной точностью в виде предела последовательности некоторых векторов (итерационный процесс). Характер сходимости и сам факт сходимости метода зависит от выбора начального приближения корня x 0 .

Рассмотрим систему A x = b .

Чтобы применить итерационный метод, необходимо привести систему к эквивалентному виду x = B x + d . Затем выбираем начальное приближение к решению СЛАУ x ( 0 ) = ( x 1 0 , x 2 0 , . . . x m 0 ) и находим последовательность приближений к корню.

Для сходимости итерационного процесса является достаточным заданное условие В 1 . Окончание итерации зависит от того, какой итерационный метод применили.

Excel для математиков

В этой главе я продолжу рассмотрение задач, которые входят в сферу традиционного образования прикладных математиков и, обычно, даются при изучении программирования. Возможно, задачи этой главы потребуют чуть большей математической подготовки. Основное внимание я буду уделять здесь решению этих задач программным путем, даже в тех случаях, когда их решение может быть получено с использованием встроенных стандартных функций. Но прежде, чем перейти к рассмотрению отдельных задач давайте рассмотрим несколько вопросов, проясняющих семантику вычислений в Excel . Я хочу рассмотреть некоторые особенности пользовательских функций — функций, написанных на VBA и вызываемых в формулах рабочего листа

Функции с побочным эффектом и неявная передача данных

Возможность написать функцию с побочным эффектом или неявной передачей данных является одной из основных причин вычисления всех пользовательских функций при пересчете электронной таблицы. Давайте приведем примеры, проясняющие ситуацию. С этой целью я написал три функции:

• ПравильнаяФункция( X As Variant) As Variant . Это пример хорошей, правильно построенной функции. Через параметр X ей передается значение некоторой ячейки рабочего листа (объект Range ). В качестве результата она возвращает значение функции,- в нашем примере результат является копией входного параметра X .
• ПобочныйЭффект(X As Variant, Y As Variant) As Variant . В данной функции помимо вычисления результата изменяется и значение параметра Y . Поскольку по умолчанию параметр передается по ссылке ( By Ref ), то это должно было бы привести к побочному эффекту и изменить содержимое ячейки рабочего листа, переданной в качестве параметра Y . Мы увидим, что этого, однако, не происходит.
• НеявнаяПередача(X As Variant) As Variant В данной функции результат зависит не только от входного параметра X , но и от значения другой, неявно используемой ячейки рабочего листа.

Вот как выглядят описания наших функций:

Вот как выглядит рабочий лист Excel , на котором вызываются эти функции:

Анализируя полученные результаты, обратим внимание на следующие моменты:

• Функция, которую я назвал » ПравильнаяФункция «, при вызове ее из рабочей формулы получает объект Range в качестве своего параметра X , и возвращает объект Range в качестве результата выполнения функции. Объект Range может быть единственной ячейкой, и в этом случае функция может вызываться в обычной рабочей формуле. Если же функция получает массив ячеек и возвращает массив, то она должна вызываться в формуле над массивами. На рабочем листе я продемонстрировал оба способа вызова функции. В ячейку D4 я записал формулу » =ПравильнаяФункция(B4) «. Поскольку функция в качестве результата возвращает переданный ей аргумент, то значение, записанное ячейку D4, будет совпадать со значением 17 , хранящемся в ячейке B4. Затем я в ячейки D5:E5 записал формулу над массивами » <=ПравильнаяФункция(B4:C4)>«. В результате эта же функция позволяет скопировать диапазон ячеек.
• В функции ПобочныйЭффект , хотя Y и получает «правильное» значение переменной X , но это никак не сказывается на значении ячейки рабочего листа, переданной в качестве параметра Y , хотя параметр передается по ссылке. Так что можно полагать, что передача объектов Range рабочего листа Excel в функцию всегда происходит по значению, а не по ссылке.
• Всякая попытка явно или неявно изменить значения ячеек рабочего листа в процессе работы функции рабочего листа помимо возвращаемого функцией результата оканчивается неуспехом. Более того, попытка явно изменить значение в ячейке приводит к тому, что результат функции становится неопределенным. Так что побочный эффект во всех его проявлениях запрещен. Изменить содержимое листа можно только, возвращая результат работы вызываемых функций в формулах рабочего листа.
• В функции НеявныеДанные создается локальный объект Range . Он получает значение одной из ячеек рабочего листа, и это значение влияет на результат, возвращаемый функцией. Тем самым становится возможной неявная передача данных, минуя аппарат формальных параметров. Заметьте, что Excel не может обнаружить такой способ зависимости между ячейками. Мы специально отразили существующие, по мнению Excel зависимости. Как видите, Excel не подозревает, что ячейка D10 зависит от ячейки C4. Именно возможность неучтенных зависимостей заставляет Excel полностью проводить вычисления всех наличествующих пользовательских функций.
• Ни функции с побочным эффектом , ни функции с неявной передачей данных не вызывают никаких предупреждающих сообщений.

Циклические вычисления

Если зависимые ячейки Excel образуют цикл, то говорят, что имеют место циклические ссылки ( circular references ). В обычном режиме Excel обнаруживает цикл и выдает сообщение о возникшей ситуации, требуя устранить циклические ссылки. Следуя обычной семантике, он не может провести вычисления, так как циклические ссылки порождают бесконечные вычисления. Есть два выхода из этой ситуации, — устранить циклические ссылки или изменить настройку в машине вычислений так, чтобы такие вычисления стали возможными. В последнем случае, естественно, требуется, чтобы число повторений цикла было конечным. Excel допускает переход к новой семантике, обеспечивающей проведение циклических вычислений. Вручную, для этого достаточно на вкладке Вычисления ( меню Сервис, пункт Параметры) включить флажок Итерации и при необходимости изменить число повторений цикла в окошке » Максимум итераций». Можно также задать точность вычислений в окошке «Максимальное изменение», что также приводит к ограничению числа повторений цикла . По умолчанию максимальное число итераций и точность вычислений соответственно имеют значения 100 и 0,0001. Понятно, что включить циклические вычисления и задать значения параметров, определяющих окончание цикла , можно и программно.

Укажем, особенности семантики циклических вычислений:

• Формулы, связанные циклическими ссылками, вычисляются многократно.
• Запись формул на листе определяет порядок их вычисления. Формулы вычисляются сверху вниз, слева направо.
• Число повторений цикла определяется параметрами, заданными на вкладке Вычисления. Цикл заканчивается при достижении максимального числа итераций или, когда изменения значений во всех ячейках не превосходят заданной точности.

В каких же ситуациях требуется прибегать к циклическим вычислениям? Это, возможно, следует делать, когда речь идет о реализации итерационного процесса, вычислениях по рекуррентным соотношениям. У нас уже были примеры реализации итерационных процессов, например, вычисление суммы ряда, задающего экспоненту, в которых не применялись циклические ссылки. Платой за это было использование дополнительных ячеек таблицы Excel . Правда, появлялись и новые возможности, — возможность построить график , проанализировать процесс сходимости и т.д. Тем не менее, программисту, привыкшему к традиционным языкам, и привыкшему «с детства» экономить на переменных, может показаться странным предложенное решение задачи о нахождении корня уравнения, где на экран выводятся результаты всех приближений. В Excel экономия ячеек не главная задача. Тем не менее, при реализации итерационных процессов можно, конечно, и в Excel иметь одну единственную ячейку X , значение которой изменяется, начиная от начального приближения до искомого результата. Это в большей степени соответствует понятию переменной в языках программирования.

Циклические вычисления и нахождение корней уравнения

Покажем, как можно использовать циклические вычисления на примере задачи нахождения корня уравнения методом Ньютона. Для простоты я начну с квадратного уравнения, а позже рассмотрю и более «серьезные» уравнения. Итак, рассмотрим квадратное уравнение: X 2 -5X+6 =0 . Найти корень этого (и любого другого уравнения) можно, используя всего одну единственную ячейку Excel . Для этого достаточно включить режим циклических вычислений и ввести в произвольную ячейку с именем, скажем X , рекуррентную формулу, задающую вычисления по Ньютону:

где F и F1 задают соответственно выражения, вычисляющие функцию и производную. Для нашего квадратного уравнения после ввода формулы в ней появится значение 2 , соответствующее одному из корней уравнения. А как получить второй корень? Обычно, это можно сделать путем изменения начального приближения. В нашем случае начальное приближение не задавалось, итерационный процесс вычислений начинался со значения, хранимого в ячейке X по умолчанию и равного нулю. Как же задать начальное приближение в циклических вычислениях? Возникшая проблема не связана с данной конкретной задачей. Она возникает всегда в циклических вычислениях, — до начала цикла надо задать начальные установки. В рекуррентных соотношениях всегда есть некоторый начальный отрезок . Решать задачу задания начальных установок в каждом случае можно по -разному. Я продемонстрирую один прием, основанный на использовании функции ЕСЛИ . Вот как выглядит «настоящее» решение этой задачи, использующее 4 ячейки, две из которых нужны по существу дела, а две используются для повышения наглядности процесса вычислений: