Как решить нелинейное уравнение в excel
Перейти к содержимому

Как решить нелинейное уравнение в excel

  • автор:

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

Нелинейные уравнения – это уравнения вида f(x)=0, где f(x) – нелинейная функция. Решение уравнения f(x)=0 сводится к поиску таких значений х * (корней уравнения), которые превращают уравнение в тождество. Различают нелинейные алгебраические уравнения и трансцендентные.

Например, нелинейное алгебраическое уравнение ax 2 + вx +с =0 имеет два корня, которые могут быть действительными или мнимыми. Например, уравнение х 2 + 2=0 имеет два мнимых корня х1= -2 и х2= --2 .

В дальнейшем будет идти речь о вычислении только действительных корней.

Трансцендентным называется уравнение, если в f(x) входит хотя бы одна трансцендентная функция. Например, sin(x) –1=0;

Решение нелинейных уравнений выполняют в два этапа:

  1. Этап отделения корней.
  2. Этап уточнения корней , т.е. поиск коней с заданной точностью.

Этап отделения корней Для этого построим график заданной функции f(x)=0. В столбце А располагаем изменение аргумента, а в столбце В табулируемую функцию. Строим график. На графике выделяем границы корня и в этих границах берем начальное приближение корня (нарисовать график, выделить корни и взять начальное приближение). Этап уточнение корня Команда Подбор параметров Порядок уточнения: 1. В ячейку A1 вводим начальное приближение корня Х1. 2. В ячейку В1 вводим формулу с заданной функцией. 3. Выполняем команды Сервис,Подбор параметра. Появляется окно Подбор параметра (рис. 7.7). 4. В поле «Установить в ячейке» записать адрес первой формулы (можно снять окно и щелкнуть ячейку В1, затем восстановить окно). 5. В поле «Значение» установить 0. 6. В поле «Изменяя значениеячейки« установить адрес А1 (снять окно и щелкнуть А1). 7. Щелкнуть ОК. Появляется окно Результат подбора параметра (рис. 7.8), а в ячейке А1 будет уточненное значение корня. Рис. 7.8 Рис. 7.7

7.5. Вычисления по итерационным формулам

Итерационной называется формула типа yi+1 = f (yi) . Пример1. Вычисление задано итерационной формулой yi+1=(x/yi 2 +2yi)/3 Начальное приближении у0=1 и значение х= 27. Составим ЭТ для вычисления: 1. В ячейку a2 запишем значение х равное 27 (рис. 7.9). 2. В ячейку b2 запишем значение у0 равное 1. 3 Рис. 7.9 . В ячейку b3 запишем формулу = ($A$1/B1^2+2*B1)/3, которую копируем вниз Пример 2. Заданы итерационные формулы x i =2xi-1 и yi= xi-1 + 3yi-1 при изменении i=2,3,4,5. При i=2 х2 = 2х1 и y2= x1 + 3y1 Начальные значения x1=1 ; y1=1 (рис. 7.10) запишем в В2 и С2 соответственно. В ячейки В3 и С3 запишем формулы для х2 и у2 . Выделяем В3:С3 и копируем вниз до С6. Результат вычисления на рис. 7.11. Рис. 7.10 Рис. 7.11 Пример 3. Решение задач следующего типа: Даны действительные числа у1, у2,…у5, которые записаны в В2:В6 Составить ЭТ для вычисления и определения min(z1 2 , z2 2 , …,z5 2 ) при i=1,2,…,5 Рис. 7.12

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

Возможно вы слышали о нобелевском лауреате, психологе и исследователе по имени Дэниель Канеман. Канеман занимался наукой, которую называют термином «поведенческая экономика», т.е. изучал реакции, поведение и суждения людей в типовых жизненных (и экономических) ситуациях и условиях неопределенности. В его книге, которая называется «Думай медленно — решай быстро» (очень рекомендую, кстати) в качестве одного из примеров когнитивных искажений — несознательной автоматической реакции — приводится следующая задача: Бейсбольная бита и мяч стоят вместе 1 доллар 10 центов.
Бита дороже мяча на 1 доллар.
Сколько стоит мяч?
Подозреваю, что вашей первой рефлекторной мыслью, скорее всего, будет «10 центов!» 🙂 Но весьма скоро, я уверен, вы сообразите, что на самом деле всё не так примитивно и для получения ответа нужно решить простую систему уравнений (здесь b — это бита, а m — это мяч): Система линейных уравнений
Конечно можно «тряхнуть стариной» и решить всё вручную на бумажке через подстановку переменных — как-то так: Решение системы уравнений через подстановку переменных
Но, во-первых, на практике уравнения могут быть сложнее и переменных может оказаться сильно больше двух и, во-вторых, у нас с вами есть Microsoft Excel — универсальный мега-инструмент, величайшее изобретение человечества. Так что давайте-ка лучше разберём как решить нашу задачу с его помощью.

Способ 1. Матричные функции МУМНОЖ и МОБР

Вычисляем обратную матрицу

Само собой, изобретать велосипед тут не надо — прогрессивное человечество в лице математиков давным-давно придумало кучу способов для решения подобных задач. В частности, если уравнения в нашей системе линейные (т.е. не используют степени, логарифмы, тригонометрические функции типа sin, cos и т.д.), то можно использовать метод Крамера. Сначала записываем числовые коэффициенты, стоящие перед нашими переменными в виде матрицы (в нашем случае — размером 2х2, в общем случае — может быть и больше). Затем находим для неё так называемую обратную матрицу , т.е. матрицу, при умножении которой на исходную матрицу коэффициентов получается единица. В Excel это легко сделать с помощью стандартной математической функции МОБР (MINVERSE) :
Здесь важно отметить, что если у вас свежая версия Excel 2021 или Excel 365, то достаточно ввести эту функцию обычным образом в первую ячейку (G7) — сразу получится динамический массив с обратной матрицей 2х2. Если же у вас более старая версия Excel, то эту функцию нужно обязательно вводить как формулу массива, а именно:

  1. Выделить диапазон для результатов — G7:H8
  2. Ввести функцию =МОБР(B7:C8) в строку формул
  3. Нажать на клавиатуре сочетание клавиш Ctrl + Shift + Enter

Замечательное свойство обратной матрицы состоит в том, что если умножить её на значения правых частей наших уравнений (свободные члены), то мы получим значения переменных, при которых левые и правые части уравнений будут равны, т.е. решения нашей задачи. Выполнить такое матричное умножение можно с помощью ещё одной стандартной экселевской функции МУМНОЖ (MMULT) :

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

Если у вас старая версия Excel, то не забудьте также ввести её в режиме формулы массива, т.е. сначала выделить диапазон K7:K8, а после ввода функции нажать сочетание клавиш Ctrl + Shift + Enter .

Само собой, уравнений и переменных может быть больше, да и посчитать всё можно сразу в одной формуле, вложив используемые функции одна в другую:

Решение системы из 3 уравнений

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

Способ 2. Подбор надстройкой «Поиск решения» (Solver)

Принципиально другой способ решения подобных задач — это итерационные методы, т.е. последовательный подбор значений переменных, чтобы после подстановки их в наши уравнения мы получили верные равенства. Само собой, подбор имеется ввиду не тупой и долгий (брутфорс), а умный и быстрый, благо математики, опять же, давным-давно придумали кучу различных методов для решения таких задач буквально за несколько итераций.

В Microsoft Excel некоторые из этих методов реализованы в стандартной надстройке Поиск решения (Solver) . Её можно подключить через Файл — Параметры — Надстройки — Перейти (File — Options — Add-ins — Go to) или на вкладке Разработчик — Надстройки (Developer — Add-ins) .

Давайте рассмотрим её использование на следующей задаче. Предположим, что нам с вами нужно решить вот такую систему из двух нелинейных уравнений:

Система нелинейных уравнений

Подготавливаем основу для оптимизации в Excel:

Модель для оптимизации

  • В жёлтых ячейках C9:C10 лежат текущие значения наших переменных, которые и будут подбираться в процессе оптимизации. В качестве стартовых можно взять любые значения, например, нули или единицы — роли не играет. Для удобства, кстати, этим ячейкам можно дать имена, назвав их именами переменных x и y, — для этого выделите диапазон C9:C10 и выберите команду Формулы — Создать из выделенного — Слева (Formulas — Create from selection — Left column) .
  • В зелёных ячейках E9:E10 введены наши уравнения с использованием либо прямых ссылок на жёлтые ячейки переменных, либо созданных имён (так нагляднее). В результате мы видим, чему равны наши уравнения при текущих значениях переменных.
  • В синих ячейках F9:F10 введены значения правых частей наших уравнений, к которым мы должны стремиться.

Теперь запускаем нашу надстройку на вкладке Данные — Поиск решения (Data — Solver) и вводим в появившемся диалоговом окне следующие параметры:

Надстройка Поиск решения в Excel

  • Оптимизировать целевую функцию (Set target cell) — любая из двух наших зелёных ячеек с уравнениями, например E9.
  • Изменяя ячейки переменных (By changing cells) — жёлтые ячейки с текущими значениями переменных, которыми мы «играем».
  • Добавляем ограничение с помощью кнопки Добавить (Add) и задаём равенство левой и правой части наших уравнений, т.е. зелёного и голубого диапазонов.
  • В качестве метода решения выбираем Поиск решения нелинейных задач методом ОПГ, т.к. уравнения у нас нелинейные. Для линейных можно смело выбирать симплекс-метод.

После нажатия на кнопку Найти решение (Solve) через пару мгновений (или не пару — это зависит от сложности задачи) мы должны увидеть окно с результатами. Если решение найдено, то в жёлтых ячейках отобразятся подобранные значения наших переменных:

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

Обратите внимание, что поскольку мы здесь используем итерационные, а не аналитические методы, то зеленые ячейки не совсем равны голубым, т.е. найденное решение не абсолютно точно. На практике, конечно же, такой точности вполне достаточно для большинства задач, и если необходимо, её можно настроить, вернувшись в окно Поиск решения и нажав кнопку Параметры (Options) .

Решение нелинейного уравнения в Excel

Открывается окно Параметры поиска решения. В поле оптимизировать целевую функцию выбираем ячейку B4, ставим Значения 0, ячейку переменной указываем A4, ставим галочку сделать переменные без ограничений неотрицательными, выбираем метод решения — поиск решения нелинейных задач методом ОПГ (обобщенного приведенного градиента) и жмем Найти решение

Параметры поиска решения

Получаем решение искомой задачи

x=1,06744215530327

решение Excel

Отчет результатов вычисления в Excel

7799

Решение нелинейного уравнения методом итерации в Excel

Задание: на отрезке [a,b] требуется найти корень нелинейного уравнения методом итерации в табличном процессоре Excel с использованием циклических ссылок.

x-x 3 +1=0 a=1 b=2

Найдем корень нелинейного уравнения в табличном процессоре Excel методом итерации с использованием циклических ссылок. Для включения режима циклических вычислений в Excel 2003 в меню Сервис/Параметры/вкладка Вычисления следует поставить флажок Итерации и флажок выбора вида ведения вычислений: автоматически. В MS Excel 2010 следует зайти в меню Файл/Параметры/Формулы и поставить флажок в поле «Включить итеративные вычисления».

формула решения нелинейного уравнения методом итерации

M – максимальное значение производной на промежутке (по модулю). Найдем М, для этого вычислим

М=11
В ячейку А7 введем значение а =1, в ячейку В7 введем формулу расчета текущего значения х: =ЕСЛИ(B7=0;A7;B7-(-B7+СТЕПЕНЬ(B7;3)-1)/11)
В ячейку С7 введем формулу для контроля значения f(x): =B7-СТЕПЕНЬ(B7;3)+1
Получим корень уравнения х=1,375

решение уравнения методом итерации в Excel (эксель)

В А7 введем начальное приближение = 2, получим корень х=1,375

Получен тот же результат, значит корень на данном промежутке один.

решение уравнения в Excel (эксель) со вторым начальным приближением методом итерации

Источник: help-informatika.ru

Знаете ли Вы, что, как ни тужатся релятивисты, CMB (космическое микроволновое излучение) — прямое доказательство существования эфира, системы абсолютного отсчета в космосе, и, следовательно, опровержение Пуанкаре-эйнштейновского релятивизма, утверждающего, что все ИСО равноправны, а эфира нет. Это фоновое излучение пространства имеет свою абсолютную систему отсчета, а значит никакого релятивизма быть не может. Подробнее читайте в FAQ по эфирной физике.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *