Аналитическое решение уравнения в excel

Отделение корней В Excel

Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.

«Актуальность создания школьных служб примирения/медиации в образовательных организациях»

Свидетельство и скидка на обучение каждому участнику

Лабораторная работа

Отделение корней нелинейного уравнения

Пусть имеется нелинейное уравнение .

Требуется найти корни этого уравнения. Численный процесс приближенного решения поставленной задачи разделяют два этапа: отделение корня и уточнение корня.

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

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

Методы отделения корней

Отделение корней во многих случая можно произвести графически. Учитывая, что действительные корни уравнения F ( x )=0 – это есть точки пересечения графика функции y = F ( x ) с осью абсцисс y =0, нужно построить график функции y = F ( x ) и на оси OX отметить отрезки, содержащие по одному корню. Но часто для упрощения построения графика функции y = F ( x ) исходное уравнение заменяют равносильным ему уравнением f ₁ ( x )= f ₂ ( x ). Далее строятся графики функций y ₁ = f ₁ ( x ) и y ₂ = f ₂ ( x ), а затем по оси OX отмечаются отрезки, локализующие абсциссы точек пересечения двух графиков.

На практике данный способ реализуется следующим образом: например, требуется отделить корни уравнения cos(2 x )+ x -5=0 графически на отрезке [–10;10], используя Excel .

Построим график функции f (x)=cos(2 x )+x-5 в декартовой системе координат. Для этого нужно:

Ввести в ячейку A1 текст х .

Ввести в ячейку B1 текст y =cos(2 x )+ x -5.

Ввести в ячейку А2 число -10, а в ячейку А3 число -9.

Выделить ячейки А2 и А3.

Навести указатель «мыши» на маркер заполнения в правом нижнем углу рамки, охватывающий выделенный диапазон. Нажать левую кнопку «мыши» и перетащить маркер так, чтобы рамка охватила диапазон ячеек А2:А22.

Ячейки автоматически заполняются цифрами :

Ввести в ячейку В2 формулу =COS(2*A2)+A2-5.

Методом протягивания заполнить диапазон ячеек В3:В22.

Вызвать «Мастер диаграмм» и выбрать диаграмму график (первый вид), нажать «далее».

Указать диапазон данных, для этого щелкнуть кнопку в поле «Диапазон» и выбрать диапазон данных В2:В22.

Выбрать вкладку ряд, указать имя ряда, щелкнув кнопку в поле «ряд» и выбрав В1.

В поле «подписи по оси Х», щелкнуть кнопку и выбрать диапазон А2:А22, нажать «далее».

Подписать названия осей x и y соответственно, нажать «далее».

Вывести диаграмму на том же листе, что и таблица, нажать кнопку «готово».

В итоге получаем следующее (рисунок 1):

Рисунок 1 – Локализация корня

Анализируя полученное изображение графика, можно сказать, что уравнение cos(2 x )+ x -5=0 имеет один корень – это видно из пересечения графика функции y=cos(2 x )+ x -5 с осью OX. Можно выбрать отрезок, содержащий данный корень: [5;6] – отрезок локализации .

Для подтверждения полученных данных, можно решить эту же задачу вторым способом. Для этого необходимо уравнение cos(2 x )+ x -5=0 преобразовать к виду: cos(2 x )=5- x . Затем следует каждую часть уравнения рассмотреть как отдельную функцию. Т. е. y ₁ =cos(2 x ) и y ₂ =5- x . Для решения этой задачи в Excel необходимо выполнить следующие действия:

Вести в ячейки А1:C1 соответственно текст: « x », « y ₁ =cos(2 _x )», « y ₂ =5- x ».

A2:A22 заполнить так же как при решении задачи первым способом.

В В2 ввести формулу =COS(2*A2).

Методом протягивания заполнить диапазон ячеек В3:В22.

В С2 ввести =5-A2.

Методом протягивания заполнить диапазон ячеек С3:С22.

С помощью Мастера диаграмм выбрать график (первый вид).

В данном случае диапазон данных следует указывать для построения двух графиков. Для этого нужно нажать кнопку в поле «Диапазон» и выделить ячейки В2:В22, затем нажать Ctrl (на клавиатуре) и выделить следующий диапазон C2:C22.

Перейти на вкладку ряд, где выбрать именем ряда 1 ячейку В1, а именем ряда 2 ячейку С2.

Подписать ось x , выбрав диапазон А2:А22.

Подписать соответственно оси x и y .

Поместить диаграмму на имеющемся листе.

Результат представлен на рисунке 2: Анализируя полученный результат, можно сказать, что точка пересечения двух графиков попадает на тот же самый отрезок локализации [5;6] , что и при решении задачи первым способом.

Рисунок 2 – Локализация корня

Аналитический способ отделения корней

Аналитический способ отделения корней основан на следующей теореме , известной из курса математического анализа.

ТЕОРЕМА: Если непрерывная на функция , определяющая уравнение , на концах отрезка принимает значения разных знаков, т.е. , то на этом отрезке содержится, по крайней мере, один корень уравнения. Если же функция непрерывна и дифференцируема и ее производная сохраняет знак внутри отрезка , то на этом отрезке находится только один корень уравнения.

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

Для отделения корней аналитическим способом выбирается отрезок , на котором находятся все интересующие вычислителя корни уравнения. Причем на отрезке функция F (x) определена, непрерывна и F ( a )* F ( b ) . Требуется указать все частичные отрезки , содержащие по одному корню.

Б
удем вычислять значение функции F ( x ) , начиная с точки x = a , двигаясь вправо с некоторым шагом h . Если F ( x )* F (x+ h ) , то на отрезке [ x ; x + h ] существует корень (рисунок 3).

Рисунок 3 – Аналитический способ локализации корней

Доказательство существования и единственности корня на отрезке.

В качестве примера рассмотрим функцию f (x)=cos(2 x )+x-5 .

Ввести в ячейки А1, В1 и С1 соответственно « x », « y =cos(2 x )+ x -5» и «ответ».

В А2 и А3 ввести граничные значения отрезка изоляции.

В В2 ввести формулу =COS(2*A2)+A2-5 и методом протягивания заполнить В3.

В С2 ввести формулу =ЕСЛИ(B2*B3

Таким образом, на отрезке изоляции корень существует:

Р
исунок 4 – Проверка существования корня на отрезке

Для доказательства единственности корня на отрезке изоляции необходимо выполнить следующие действия:

Продолжить работу в том же документе MS Excel.

Заполнить D1 и E1 соответственно: « y’ =-sin(2 x )*2+1» и «ответ» (причем выражение y’ =-sin(2 x )*2+1 – это производная первого порядка от функции y =cos(2 x )+ x -5).

Ввести в D2 формулу =-SIN(2*A2)*2+1 и методом протягивания заполнить D3.

Ввести в E2 =ЕСЛИ(D2*D3>0;»корень на данном отрезке единственный»;»Корень не единственный»).

В
результате получаем (рисунок 5):

Рисунок 5 – Доказательство единственности корня на отрезке

Таким образом доказано существование и единственность корня на отрезке изоляции.

Рассмотрим решение задачи отделения корней уравнения
cos(2 x )+ x -5=0 аналитическим способом с шагом 1 на отрезке [-10;10].

Чтобы отделить корни уравнения аналитическим способом с помощью Excel, необходимо выполнить следующее:

Заполнить ячейки A1:D1 соответственно: « x », « y =cos(2 x )+ x -5», « h », «ответ».

В С2 ввести значение 1.

Ввести в А2 значение -10.

Ввести в А3 =A2+$C$2 и методом протягивания заполнить ячейки А4:А22.

В В2 ввести =COS(2*A2)+A2-5 и методом протягивания заполнить диапазон В3:В22.

В
С3 ввести формулу =ЕСЛИ(B2*B3

В результате получаем следующее (рисунок 6):

Рисунок 6 – Отделение корня

Следующий пример (рисунок 7) демонстрирует отделение нескольких корней. Пусть исследуется функция cos ( x )=0,1 x на интервале [–10;10] с шагом 1.

Табулирование функции и построение графика осуществляется как в предыдущих примерах. Видно, что на заданном отрезке имеем 7 корней, находящихся внутри отрезков: [-10;-9]; [-9;-8]; [-5;-4]; [-2;-1]; [1;2]; [5;6]; [7;8].

Рисунок 7 – Отделение корней

Обратим внимание на то, что надежность рассмотренного алгоритма отделения корней уравнения зависит как от характера функции F (x), так и от выбранной величины шага h . Для повышения надежности следует выбирать при отделении корней достаточно малые значения h .

1. Выполнить отделение корней следующих функций:

Решение уравнений в excel — примеры решений

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

Первый метод

Суть этого способа заключается в использовании специального инструмента программы – подбор параметра. Найти его можно во вкладке Данные на Панели управления в выпадающем списке кнопки Анализ «что-если».

1. Зададимся простым квадратичным уравнением и найдем решение при х=0.

2. Переходите к инструменту и заполняете все необходимые поля

3. После проведения вычислений программа выдаст результат в ячейке с иксом.

4. Подставив полученное значение в исходное уравнение можно проверить правильность решения.

Второй метод

Используем графическое решение этого же уравнения. Суть заключается в том, что создается массив переменных и массив значений, полученных при решении выражения. Основываясь на этих данных, строится график. Место пересечения кривой с горизонтальной осью и будет неизвестной переменной.

1. Создаете два диапазона.

На заметку! Смена знака результата говорит о том, что решение находится в промежутке между этими двумя переменными.

2. Переходите во вкладку Вставка и выбираете обычный график.

3. Выбираете данные из столбца f (x), а в качестве подписи горизонтальной оси – значения иксов.

Важно! В настройках оси поставьте положение по делениям.

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

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

Третий метод

Решение систем уравнений можно проводить матричным методом. Для этого в редакторе есть отдельная функция МОБР. Суть заключается в том, что создаются два диапазона: в один выписываются аргументы при неизвестных, а во второй – значения в правой стороне выражения. Массив аргументов трансформируется в обратную матрицу, которая потом умножается на цифры после знака равно. Рассмотрим подробнее.

1. Записываете произвольную систему уравнений.

2. Отдельно выписываете аргументы при неизвестных в каждую ячейку. Если нет какого-то из иксов – ставите ноль. Аналогично поступаете с цифрами после знака равно.

3. Выделяете в свободной зоне диапазон ячеек равный размеру матрицы. В строке формул пишете МОБР и выбираете массив аргументов. Чтобы функция сработала корректно нажимаете одновременно Ctrl+Shift+Enter.

4. Теперь находите решение при помощи функции МУМНОЖ. Также предварительно выделяете диапазон размером с матрицу результатов и нажимаете уже известное сочетание клавиш.

Четвертый метод

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

Важно! Если первый аргумент является нулевым, то необходимо поменять строки местами.

1. Зададимся произвольной системой уравнений и выпишем все коэффициенты в отдельный массив.

2. Копируете первую строку в другое место, а ниже записываете формулу следующего вида: =C67:F67-$C$66:$F$66*(C67/$C$66).

Поскольку работа идет с массивами, нажимайте Ctrl+Shift+Enter, вместо Enter.

3. Маркером автозаполнения копируете формулу в нижнюю строку.

4. Выделяете две первые строчки нового массива и копируете их в другое место, вставив только значения.

5. Повторяете операцию для третьей строки, используя формулу

=C73:F73-$C$72:$F$72*(D73/$D$72). На этом прямая последовательность решения закончена.

6. Теперь необходимо пройти систему в обратном порядке. Используйте формулу для третьей строчки следующего вида =(C78:F78)/E78

7. Для следующей строки используйте формулу =(C77:F77-C84:F84*E77)/D77

8. В конце записываете вот такое выражение =(C76:F76-C83:F83*D76-C84:F84*E76)/C76

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

Метод Гаусса является одним из самых трудоемких среди прочих вариантов, однако позволяет пошагово просмотреть процесс поиска неизвестных.

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

Жми «Нравится» и получай только лучшие посты в Facebook ↓

Решение уравнений в Excel методом итераций Крамера и Гаусса

В программе Excel имеется обширный инструментарий для решения различных видов уравнений разными методами.

Рассмотрим на примерах некоторые варианты решений.

Решение уравнений методом подбора параметров Excel

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

Путь к команде: «Данные» — «Работа с данными» — «Анализ «что-если»» — «Подбор параметра».

Рассмотрим на примере решение квадратного уравнения х 2 + 3х + 2 = 0. Порядок нахождения корня средствами Excel:

1. Введем в ячейку В2 формулу для нахождения значения функции. В качестве аргумента применим ссылку на ячейку В1.
2. Открываем меню инструмента «Подбор параметра». В графе «Установить в ячейку» — ссылка на ячейку В2, где находится формула. В поле «Значение» вводим 0. Это то значение, которое нужно получить. В графе «Изменяя значение ячейки» — В1. Здесь должен отобразиться отобранный параметр.
3. После нажатия ОК отобразится результат подбора. Если нужно его сохранить, вновь нажимаем ОК. В противном случае – «Отмена».

Для подбора параметра программа использует циклический процесс. Чтобы изменить число итераций и погрешность, нужно зайти в параметры Excel. На вкладке «Формулы» установить предельное количество итераций, относительную погрешность. Поставить галочку «включить итеративные вычисления».

Как решить систему уравнений матричным методом в Excel

Дана система уравнений:

1. Значения элементов введем в ячейки Excel в виде таблицы.
2. Найдем обратную матрицу. Выделим диапазон, куда впоследствии будут помещены элементы матрицы (ориентируемся на количество строк и столбцов в исходной матрице). Открываем список функций (fx). В категории «Математические» находим МОБР. Аргумент – массив ячеек с элементами исходной матрицы.
3. Нажимаем ОК – в левом верхнем углу диапазона появляется значение. Последовательно жмем кнопку F2 и сочетание клавиш Ctrl + Shift + Enter.
4. Умножим обратную матрицу Ах -1х на матрицу В (именно в таком порядке следования множителей!). Выделяем диапазон, где впоследствии появятся элементы результирующей матрицы (ориентируемся на число строк и столбцов матрицы В). Открываем диалоговое окно математической функции МУМНОЖ. Первый диапазон – обратная матрица. Второй – матрица В.
5. Закрываем окно с аргументами функции нажатием кнопки ОК. Последовательно нажимаем кнопку F2 и комбинацию Ctrl + Shift + Enter.

Получены корни уравнений.

Решение системы уравнений методом Крамера в Excel

Возьмем систему уравнений из предыдущего примера:

Для их решения методом Крамера вычислим определители матриц, полученных заменой одного столбца в матрице А на столбец-матрицу В.

Для расчета определителей используем функцию МОПРЕД. Аргумент – диапазон с соответствующей матрицей.

Рассчитаем также определитель матрицы А (массив – диапазон матрицы А).

Определитель системы больше 0 – решение можно найти по формуле Крамера (D_x / |A|).

Для расчета Х₁: =U2/$U$1, где U2 – D1. Для расчета Х₂: =U3/$U$1. И т.д. Получим корни уравнений:

Решение систем уравнений методом Гаусса в Excel

Для примера возьмем простейшую систему уравнений:

3а + 2в – 5с = -1
2а – в – 3с = 13
а + 2в – с = 9

Коэффициенты запишем в матрицу А. Свободные члены – в матрицу В.

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

1. Приведем все коэффициенты при а к 0. Кроме первого уравнения. Скопируем значения в первой строке двух матриц в ячейки В6:Е6. В ячейку В7 введем формулу: =B3:Е3-$B$2:$Е$2*(B3/$B$2). Выделим диапазон В7:Е7. Нажмем F2 и сочетание клавиш Ctrl + Shift + Enter. Мы отняли от второй строки первую, умноженную на отношение первых элементов второго и первого уравнения.
2. Копируем введенную формулу на 8 и 9 строки. Так мы избавились от коэффициентов перед а. Сохранили только первое уравнение.
3. Приведем к 0 коэффициенты перед в в третьем и четвертом уравнении. Копируем строки 6 и 7 (только значения). Переносим их ниже, в строки 10 и 11. Эти данные должны остаться неизменными. В ячейку В12 вводим формулу массива.
4. Прямую прогонку по методу Гаусса сделали. В обратном порядке начнем прогонять с последней строки полученной матрицы. Все элементы данной строки нужно разделить на коэффициент при с. Введем в строку формулу массива: <=B12:E12/D12>.
5. В строке 15: отнимем от второй строки третью, умноженную на коэффициент при с второй строки (<=(B11:E11-B16:E16*D11)/C11>). В строке 14: от первой строки отнимаем вторую и третью, умноженные на соответствующие коэффициенты (<=(B10:E10-B15:E15*C10-B16:E16*D10)/B10>). В последнем столбце новой матрицы получаем корни уравнения.

Примеры решения уравнений методом итераций в Excel

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

Делается это на вкладке «Формулы» в «Параметрах Excel». Найдем корень уравнения х – х 3 + 1 = 0 (а = 1, b = 2) методом итерации с применением циклических ссылок. Формула:

M – максимальное значение производной по модулю. Чтобы найти М, произведем вычисления:

f’ (1) = -2 * f’ (2) = -11.

Полученное значение меньше 0. Поэтому функция будет с противоположным знаком: f (х) = -х + х 3 – 1. М = 11.

В ячейку А3 введем значение: а = 1. Точность – три знака после запятой. Для расчета текущего значения х в соседнюю ячейку (В3) введем формулу: =ЕСЛИ(B3=0;A3;B3-(-B3+СТЕПЕНЬ(B3;3)-1/11)).

В ячейке С3 проконтролируем значение f (x): с помощью формулы =B3-СТЕПЕНЬ(B3;3)+1.

Корень уравнения – 1,179. Введем в ячейку А3 значение 2. Получим тот же результат: