Решение нелинейных уравнений и систем»
Цель работы : Изучение возможностей пакета Ms Excel 2007 при решении нелинейных уравнений и систем. Приобретение навыков решения нелинейных уравнений и систем средствами пакета.
Задание1. Найти корни полинома x 3 - 0,01x 2 - 0,7044x + 0,139104 = 0.
Для начала решим уравнение графически. Известно, что графическим решением уравнения f(x)=0 является точка пересечения графика функции f(x) с осью абсцисс, т.е. такое значение x, при котором функция обращается в ноль.
Проведем табулирование нашего полинома на интервале от -1 до 1 с шагом 0,2. Результаты вычислений приведены на ри., где в ячейку В2 была введена формула: = A2^3 - 0,01*A2^2 - 0,7044*A2 + 0,139104. На графике видно, что функция три раза пересекает ось Оx, а так как полином третьей степени имеется не более трех вещественных корней, то графическое решение поставленной задачи найдено. Иначе говоря, была проведена локализация корней, т.е. определены интервалы, на которых находятся корни данного полинома: [-1,-0.8], и .
Теперь можно найти корни полинома методом последовательных приближений с помощью команды Данные→Работа с данными→Анализ «Что-Если» →Подбор параметра .
После ввода начальных приближений и значений функции можно обратиться к команде Данные→Работа с данными→Анализ «Что-Если» →Подбор параметра и заполнить диалоговое окно следующим образом.
В поле Установить в ячейке дается ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения (уравнение должно быть записано так, чтобы его правая часть не содержала переменную). В поле Значение вводим правую часть уравнения, а в поле Изменяя значения ячейки дается ссылка на ячейку, отведенную под переменную. Заметим, что вводить ссылки на ячейки в поля диалогового окна Подбор параметров удобнее не с клавиатуры, а щелчком на соответствующей ячейке.
После нажатия кнопки ОК появится диалоговое окно Результат подбора параметра с сообщением об успешном завершении поиска решения, приближенное значение корня будет помещено в ячейку А14.
Два оставшихся корня находим аналогично. Результаты вычислений будут помещены в ячейки А15 и А16.
Задание 2. Решить уравнение e x - (2x - 1) 2 = 0.
Проведем локализацию корней нелинейного уравнения.
Для этого представим его в виде f(x) = g(x) , т.е. e x = (2x - 1) 2 или f(x) = e x , g(x) = (2x - 1) 2 , и решим графически.
Графическим решением уравнения f(x) = g(x) будет точка пересечения линий f(x) и g(x).
Построим графики f(x) и g(x). Для этого в диапазон А3:А18 введем значения аргумента. В ячейку В3 введем формулу для вычисления значений функции f(x): = EXP(A3), а в С3 для вычисления g(x): = (2*A3-1)^2.
Результаты вычислений и построение графиков f(x) и g(x):
На графике видно, что линии f(x) и g(x) пересекаются дважды, т.е. данное уравнение имеет два решения. Одно из них тривиальное и может быть вычислено точно:
Для второго можно определить интервал изоляции корня: 1,5 < x < 2.
Теперь можно найти корень уравнения на отрезке методом последовательных приближений.
Введём начальное приближение в ячейку Н17 = 1,5, и само уравнение, со ссылкой на начальное приближение, в ячейку I17 = EXP(H17) - (2*H17-1)^2.
и заполним диалоговое окно Подбор параметра .
Результат поиска решения будет выведен в ячейку Н17.
Задание 3 . Решить систему уравнений:
Прежде чем воспользоваться описанными выше методами решения систем уравнений, найдем графическое решение этой системы. Отметим, что оба уравнения системы заданы неявно и для построения графиков, функций соответствующих этим уравнениям, необходимо разрешить заданные уравнения относительно переменной y.
Для первого уравнения системы имеем:
Выясним ОДЗ полученной функции:
Второе уравнение данной системы описывает окружность.
Фрагмент рабочего листа MS Excel с формулами, которые необходимо ввести в ячейки для построения линий, описанных уравнениями системы. Точки пересечения линий изображенных являются графическим решением системы нелинейных уравнений.
Не трудно заметить, что заданная система имеет два решения. Поэтому процедуру поиска решений системы необходимо выполнить дважды, предварительно определив интервал изоляции корней по осям Оx и Oy . В нашем случае первый корень лежит в интервалах (-0.5;0) x и (0.5;1) y , а второй - (0;0.5) x и (-0.5;-1) y . Далее поступим следующим образом. Введем начальные значения переменных x и y, формулы отображающие уравнения системы и функцию цели.
Теперь дважды воспользуемся командой Данные→Анализ→Поиск решений, заполняя появляющиеся диалоговые окна.
Сравнив полученное решение системы с графическим, убеждаемся, что система решена верно.
Задания для самостоятельного решения
Задание 1 . Найти корни полинома
Задание 2 . Найдите решение нелинейного уравнения.
Задание 3 . Найдите решение системы нелинейных уравнений.
Надстройка Excel «Поиск решения» – это аналитический инструмент, который позволяет нам быстро и легко определить, когда и какой результат мы получим при определенных условиях. Возможности инструмента поиска решения намного выше, чем может предоставить «подбор параметра » в Excel.
Основные отличия между поиском решения и подбором параметра:
- Подбор нескольких параметров в Excel.
- Наложение условий ограничивающих изменения в ячейках, которые содержат переменные значения.
- Возможность использования в тех случаях, когда может быть много решений одной задачи.
Примеры и задачи на поиск решения в Excel
Рассмотрим аналитические возможности надстройки. Например, Вам нужно накопить 14 000$ за 10 лет. На протяжении 10-ти лет вы хотите каждый год откладывать на депозитный счет в банке по 1000$ под 5% годовых. Ниже на рисунке построена таблица в Excel, по которой хорошо видно остаток накопленных средств на каждый год. Как видно при таких условиях депозитного счета и взносов накопления цель не будет достигнута даже через 10 лет. При решении данной задачи можно пойти двумя путями:- Найти банк, который предлагает более высокую процентную ставку по депозитам.
- Увеличить размер ежегодных накопительных взносов на банковский счет.
Мы можем изменять переменные значения в ячейках B1 и B2 так, чтобы подобрать необходимые условия для накопления необходимой суммы денег.
Надстройка «Поиск решения» - позволяет нам одновременно использовать 2 этих варианта, чтобы быстро смоделировать наиболее оптимальные условия для достижения поставленной цели. Для этого:
Как видно программа немного увеличила процентную ставку и сумму ежегодных взносов.
Ограничение параметров при поиске решений
Допустим, вы пошли в банк с этой таблицей, но банк отказывается поднять Вам процентную ставку. В таком случаи нам нужно узнать, насколько нам придется повысить сумму ежегодных вложений. Мы должны установить ограничение на ячейку с одним переменным значением. Но перед началом измените значения в переменных ячейках на исходные: в B1 на 5%, а в B2 на -1000$. А теперь делаем следующее.
Пользователи Excel давно и успешно применяют программу для решения различных типов задач в разных областях.
Excel – это самая популярная программа в каждом офисе во всем мире. Ее возможности позволяют быстро находить эффективные решения в самых разных сферах деятельности. Программа способна решать различного рода задачи: финансовые, экономические, математические, логические, оптимизационные и многие другие. Для наглядности мы каждое из выше описанных решение задач в Excel и примеры его выполнения.
Решение задач оптимизации в Excel
Оптимизационные модели применяются в экономической и технической сфере. Их цель – подобрать сбалансированное решение, оптимальное в конкретных условиях (количество продаж для получения определенной выручки, лучшее меню, число рейсов и т.п.).
В Excel для решения задач оптимизации используются следующие команды:
Для решения простейших задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев». Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения».
Условие. Фирма производит несколько сортов йогурта. Условно – «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 рублей. «2» - 250 рублей. «3» - 300 рублей. Сбыт, налажен, но количество имеющегося сырья ограничено. Нужно найти, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.
Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:
На основании этих данных составим рабочую таблицу:
- Количество изделий нам пока неизвестно. Это переменные.
- В столбец «Прибыль» внесены формулы: =200*B11, =250*В12, =300*В13.
- Расход сырья ограничен (это ограничения). В ячейки внесены формулы: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») и =0*B11+8*B12+6*B13 («сахар»). То есть мы норму расхода умножили на количество.
- Цель – найти максимально возможную прибыль. Это ячейка С14.
Активизируем команду «Поиск решения» и вносим параметры.
После нажатия кнопки «Выполнить» программа выдает свое решение.
Оптимальный вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» производить не стоит.
Решение финансовых задач в Excel
Чаще всего для этой цели применяются финансовые функции. Рассмотрим пример.
Оформим исходные данные в виде таблицы:
Так как процентная ставка не меняется в течение всего периода, используем функцию ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).
Заполнение аргументов:
- Ставка – 20%/4, т.к. проценты начисляются ежеквартально.
- Кпер – 4*4 (общий срок вклада * число периодов начисления в год).
- Плт – 0. Ничего не пишем, т.к. депозит пополняться не будет.
- Тип – 0.
- БС – сумма, которую мы хотим получить в конце срока вклада.
Вкладчику необходимо вложить эти деньги, поэтому результат отрицательный.
Для проверки правильности решения воспользуемся формулой: ПС = БС / (1 + ставка) кпер. Подставим значения: ПС = 400 000 / (1 + 0,05) 16 = 183245.
Решение эконометрики в Excel
Для установления количественных и качественных взаимосвязей применяются математические и статистические методы и модели.
Дано 2 диапазона значений:
Значения Х будут играть роль факторного признака, Y – результативного. Задача – найти коэффициент корреляции.
Для решения этой задачи предусмотрена функция КОРРЕЛ (массив 1; массив 2).
Решение логических задач в Excel
В табличном процессоре есть встроенные логические функции. Любая из них должна содержать хотя бы один оператор сравнения, который определит отношение между элементами (=, >, =,
Ученики сдавали зачет. Каждый из них получил отметку. Если больше 4 баллов – зачет сдан. Менее – не сдан.
- Ставим курсор в ячейку С1. Нажимаем значок функций. Выбираем «ЕСЛИ».
- Заполняем аргументы. Логическое выражение – B1>=4. Это условие, при котором логическое значение – ИСТИНА.
- Если ИСТИНА – «Зачет сдал». ЛОЖЬ – «Зачет не сдал».
Решение математических задач в Excel
Средствами программы можно решать как простейшие математические задачки, так и более сложные (операции с функциями, матрицами, линейными уравнениями и т.п.).
Условие учебной задачи. Найти обратную матрицу В для матрицы А.
- Делаем таблицу со значениями матрицы А.
- Выделяем на этом же листе область для обратной матрицы.
- Нажимаем кнопку «Вставить функцию». Категория – «Математические». Тип – «МОБР».
- В поле аргумента «Массив» вписываем диапазон матрицы А.
- Нажимаем одновременно Shift+Ctrl+Enter - это обязательное условие для ввода массивов.
Возможности Excel не безграничны. Но множество задач программе «под силу». Тем более здесь не описаны возможности которые можно расширить с помощью макросов и пользовательских настроек.
Поиск решения - это надстройка Microsoft Excel, с помощью которой можно найти оптимальное решение задачи с учетом заданных пользователем ограничений.
Поиск решения будем рассматривать в (эта надстройка претерпела некоторые изменения по сравнению с предыдущей версией в .
В этой статье рассмотрим:
- создание оптимизационной модели на листе MS EXCEL
- настройку Поиска решения;
- простой пример (линейная модель).
Установка Поиска решения
Команда Поиск решения находится в группе Анализ на вкладке Данные .
Если команда Поиск решения
в группе Анализ
недоступна, то необходимо включить одноименную надстройку.
Для этого:
- На вкладке Файл выберите команду Параметры , а затем - категорию Надстройки ;
- В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти;
- В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК.
Примечание . Окно Надстройки также доступно на вкладке Разработчик . Как включить эту вкладку .
После нажатия кнопки Поиск решения в группе Анализ, откроется его диалоговое окно.
При частом использовании Поиска решения его удобнее запускать с Панели быстрого доступа, а не из вкладки Данные. Чтобы поместить кнопку на Панель, кликните на ней правой клавишей мыши и выберите пункт Добавить на панель быстрого доступа .
О моделях
Этот раздел для тех, кто только знакомится с понятием Оптимизационная модель.
Совет . Перед использованием Поиска решения настоятельно рекомендуем изучить литературу по решению оптимизационных задач и построению моделей.
Ниже приведен небольшой ликбез по этой теме.
Надстройка Поиск решения помогает определить лучший способ сделать что-то :
- "Что-то" может включать в себя выделение денег на инвестиции, загрузку склада, доставку товара или любую другую предметную деятельность, где требуется найти оптимальное решение.
- "Лучший способ" или оптимальное решение в этом случае означает: максимизацию прибыли, минимизацию затрат, достижение наилучшего качества и пр.
Вот некоторые типичные примеры оптимизационных задач:
- Определить , при котором доход от реализации произведенной продукции максимальный;
- Определить , при которой общие затраты на перевозку были бы минимальными;
- Найти , чтобы общие затраты на производство продукции были бы минимальными;
- Определить минимальный срок исполнения всех работ проекта (критический путь).
Для формализации поставленной задачи требуется создать модель, которая бы отражала существенные характеристики предметной области (и не включала бы незначительные детали). Следует учесть, что модель оптимизируется Поиском решения
только по одному показателю
(этот оптимизируемый показатель называется целевой функцией
).
В MS EXCEL модель представляет собой совокупность связанных между собой формул, которые в качестве аргументов используют переменные. Как правило, эти переменные могут принимать только допустимые значения с учетом заданных пользователем ограничений.
Поиск решения
подбирает такие значения этих переменных (с учетом заданных ограничений), чтобы целевая функция была максимальной (минимальной) или была равна заданному числовому значению.
Примечание
. В простейшем случае модель может быть описана с помощью одной формулы. Некоторые из таких моделей могут быть оптимизированы с помощью инструмента . Перед первым знакомством с Поиском решения
имеет смысл сначала детально разобраться с родственным ему инструментом .
Основные отличия Подбора параметра
от Поиска решения
:
- Подбор параметра работает только с моделями с одной переменной;
- в нем невозможно задать ограничения для переменных;
- определяется не максимум или минимум целевой функции, а ее равенство некому значению;
- эффективно работает только в случае линейных моделей, в нелинейном случае находит локальный оптимум (ближайший к первоначальному значению переменной).
Подготовка оптимизационной модели в MS EXCEL
Поиск решения
оптимизирует значение целевой функции. Под целевой функцией подразумевается формула, возвращающая единственное значение в ячейку. Результат формулы должен зависеть от переменных модели (не обязательно напрямую, можно через результат вычисления других формул).
Ограничения модели могут быть наложены как на диапазон варьирования самих переменных, так и на результаты вычисления других формул модели, зависящих от этих переменных.
Все ячейки, содержащие переменные и ограничения модели должны быть расположены только на одном листе книги. Ввод параметров в диалоговом окне Поиска решения
возможен только с этого листа.
Целевая функция (ячейка) также должна быть расположена на этом листе. Но, промежуточные вычисления (формулы) могут быть размещены на других листах.
Совет . Организуйте данные модели так, чтобы на одном листе MS EXCEL располагалась только одна модель. В противном случае, для выполнения расчетов придется постоянно сохранять и загружать настройки Поиска решения (см. ниже).
Приведем алгоритм работы с Поиском решения , который советуют сами разработчики (www.solver.com ):
- Определите ячейки с переменными модели (decision variables);
- Создайте формулу в ячейке, которая будет рассчитывать целевую функцию вашей модели (objective function);
- Создайте формулы в ячейках, которые будут вычислять значения, сравниваемые с ограничениями (левая сторона выражения);
- С помощью диалогового окна Поиск решения введите ссылки на ячейки содержащие переменные, на целевую функцию, на формулы для ограничений и сами значения ограничений;
- Запустите Поиск решения для нахождения оптимального решения.
Проделаем все эти шаги на простом примере.
Простой пример использования Поиска решения
Необходимо загрузить контейнер товарами, чтобы вес контейнера был максимальным. Контейнер имеет объем 32 куб.м. Товары содержатся в коробках и ящиках. Каждая коробка с товаром весит 20кг, ее объем составляет 0,15м3. Ящик - 80кг и 0,5м3 соответственно. Необходимо, чтобы общее количество тары было не меньше 110 штук.
Данные модели организуем следующим образом (см. файл примера ).
Переменные модели (количество каждого вида тары) выделены зеленым.
Целевая функция (общий вес всех коробок и ящиков) – красным.
Ограничения модели: по минимальному количеству тары (>=110) и по общему объему (<=32) – синим.
Целевая функция рассчитывается по формуле =СУММПРОИЗВ(B8:C8;B6:C6)
– это общий вес всех коробок и ящиков, загруженных в контейнер.
Аналогично рассчитываем общий объем - =СУММПРОИЗВ(B7:C7;B8:C8)
. Эта формула нужна, чтобы задать ограничение на общий объем коробок и ящиков (<=32).
Также для задания ограничения модели рассчитаем общее количество тары =СУММ(B8:C8)
.
Теперь с помощью диалогового окна Поиск решения
введем ссылки на ячейки содержащие переменные, целевую функцию, формулы для ограничений и сами значения ограничений (или ссылки на соответствующие ячейки).
Понятно, что количество коробок и ящиков должно быть целым числом – это еще одно ограничение модели.
После нажатия кнопки Найти решение будут найдены такие количества коробок и ящиков, при котором общий их вес (целевая функция) максимален, и при этом выполнены все заданные ограничения.
Резюме
На самом деле, основной проблемой при решении оптимизационных задач с помощью Поиска решения
является отнюдь не тонкости настройки этого инструмента анализа, а правильность построения модели, адекватной поставленной задаче. Поэтому в других статьях сконцентрируемся именно на построении моделей, ведь «кривая» модель часто является причиной невозможности найти решение с помощью Поиска решения
.
Зачастую проще просмотреть несколько типовых задач, найти среди них похожую, а затем адаптировать эту модель под свою задачу.
Решение классических оптимизационных задач с помощью Поиска решения
рассмотрено .
Поиску решения не удалось найти решения (Solver could not find a feasible solution)
Это сообщение появляется, когда Поиск решения
не смог найти сочетаний значений переменных, которые одновременно удовлетворяют всем ограничениям.
Если вы используете Симплекс метод решения линейных задач
, то можно быть уверенным, что решения действительно не существует.
Если вы используете метод решения нелинейных задач, который всегда начинается с начальных значений переменных, то это может также означать, что допустимое решение далеко от этих начальных значений. Если вы запустите Поиск решения
с другими начальными значениями переменных, то, возможно, решение будет найдено.
Представим, что при решении задачи нелинейным методом, ячейки с переменными были оставлены не заполненными (т.е. начальные значения равны 0), и Поиск решения
не нашел решения. Это не означает, что решения действительно не существует (хотя это может быть и так). Теперь, основываясь на результатах некой экспертной оценки, в ячейки с переменными введем другой набор значений, который, по Вашему мнению, близок к оптимальному (искомому). В этом случае, Поиск решения
может найти решение (если оно действительно существует).
Примечание . О влиянии нелинейности модели на результаты расчетов можно прочитать в последнем разделе статьи .
В любом случае (линейном или нелинейном), Вы должны сначала проанализировать модель на непротиворечивость ограничений, то есть условий, которые не могут быть удовлетворены одновременно. Чаще всего это связано с неправильным выбором соотношения (например, <= вместо >=) или граничного значения.
Если, например, в рассмотренном выше примере, значение максимального объема установить 16 м3 вместо 32 м3, то это ограничение станет противоречить ограничению по минимальному количеству мест (110), т.к. минимальному количеству мест соответствует объем равный 16,5 м3 (110*0,15, где 0,15 – объем коробки, т.е. самой маленькой тары). Установив в качестве ограничения максимального объема 16 м3, Поиск решения
не найдет решения.
При ограничении 17 м3 Поиск решения найдет решение.
Некоторые настройки Поиска решения
Метод решения
Рассмотренная выше модель является линейной, т.е. целевая функция (M – общий вес, который может быть максимален) выражена следующим уравнением M=a1*x1+a2*x2, где x1 и x2 – это переменные модели (количество коробок и ящиков), а1 и а2 – их веса. В линейной модели ограничения также должны быть линейными функциями от переменных. В нашем случае ограничение по объему V=b1*x1+b2*x2 также выражается линейной зависимостью. Очевидно, что другое ограничение - Максимальное количество тары (n) – также линейно x1+x2
В этом случае необходимо выбрать метод для решения нелинейной задачи. Примеры нелинейных зависимостей: V=b1*x1*x1; V=b1*x1^0,9; V=b1*x1*x2, где x – переменная, а V – целевая функция.
Кнопки Добавить, Изменить, Удалить
Эти кнопки позволяют добавлять, изменять и удалять ограничения модели.
Кнопка Сбросить
Чтобы удалить все настройки Поиска решения
нажмите кнопку Сбросить
– диалоговое окно очистится.
Эта опция удобна при использовании разных вариантов ограничений. При сохранении параметров модели (кнопка Загрузить/ Сохранить, далее нажмите кнопку Сохранить ) предлагается выбрать верхнюю ячейку диапазона (столбца), в который будут помещены: ссылка на целевую функцию, ссылки на ячейки с переменными, ограничения и параметры методов решения (доступные через кнопку Параметры ). Перед сохранением убедитесь в том, что этот диапазон не содержит данных модели.
Для загрузки сохраненных параметров нажмите сначала кнопку Загрузить/ Сохранить , затем, в появившемся диалоговом окне кнопку Загрузить , после чего задайте диапазон ячеек, содержащих сохраненные ранее настройки (нельзя указывать только одну верхнюю ячейку). Нажмите кнопку OK. Подтвердите сброс текущих значений параметров задачи и их замену на новые.
Точность
При создании модели исследователь изначально имеет некую оценку диапазонов варьирования целевой функции и переменных. Принимая во внимание вычислений в MS EXCEL, рекомендуется, чтобы эти диапазоны варьирования были значительно выше точности вычисления (она обычно устанавливается от 0,001 до 0,000001). Как правило, данные в модели нормируют так, чтобы диапазоны варьирования целевой функции и переменных были в пределах 0,1 – 100 000. Конечно, все зависит от конкретной модели, но если ваши переменные изменяются более чем на 5-6 порядков, то возможно следует «загрубить» модель, например, с помощью операции логарифмирования.
В программе Excel имеется обширный инструментарий для решения различных видов уравнений разными методами.
Рассмотрим на примерах некоторые варианты решений.
Решение уравнений методом подбора параметров Excel
Инструмент «Подбор параметра» применяется в ситуации, когда известен результат, но неизвестны аргументы. Excel подбирает значения до тех пор, пока вычисление не даст нужный итог.
Путь к команде: «Данные» - «Работа с данными» - «Анализ «что-если»» - «Подбор параметра».
Рассмотрим на примере решение квадратного уравнения х 2 + 3х + 2 = 0. Порядок нахождения корня средствами Excel:
Для подбора параметра программа использует циклический процесс. Чтобы изменить число итераций и погрешность, нужно зайти в параметры Excel. На вкладке «Формулы» установить предельное количество итераций, относительную погрешность. Поставить галочку «включить итеративные вычисления».
Как решить систему уравнений матричным методом в Excel
Дана система уравнений:
Получены корни уравнений.
Решение системы уравнений методом Крамера в Excel
Возьмем систему уравнений из предыдущего примера:
Для их решения методом Крамера вычислим определители матриц, полученных заменой одного столбца в матрице А на столбец-матрицу В.
Для расчета определителей используем функцию МОПРЕД. Аргумент – диапазон с соответствующей матрицей.
Рассчитаем также определитель матрицы А (массив – диапазон матрицы А).
Определитель системы больше 0 – решение можно найти по формуле Крамера (D x / |A|).
Для расчета Х 1: =U2/$U$1, где U2 – D1. Для расчета Х 2: =U3/$U$1. И т.д. Получим корни уравнений:
Решение систем уравнений методом Гаусса в Excel
Для примера возьмем простейшую систему уравнений:
3а + 2в – 5с = -1
2а – в – 3с = 13
а + 2в – с = 9
Коэффициенты запишем в матрицу А. Свободные члены – в матрицу В.
Для наглядности свободные члены выделим заливкой. Если в первой ячейке матрицы А оказался 0, нужно поменять местами строки, чтобы здесь оказалось отличное от 0 значение.
Примеры решения уравнений методом итераций в Excel
Вычисления в книге должны быть настроены следующим образом:
Делается это на вкладке «Формулы» в «Параметрах Excel». Найдем корень уравнения х – х 3 + 1 = 0 (а = 1, b = 2) методом итерации с применением циклических ссылок. Формула:
Х n+1 = X n – F (X n) / M, n = 0, 1, 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. Получим тот же результат:
Корень на заданном промежутке один.
