Решение кубических уравнений в excel

3.1 Теория и исходные данные

Кубическое уравнение – это уравнение, общий вид которого выражается формулой (27).

(27)

Численное решение нелинейных уравнений состоит из двух этапов. Первый из них называется отделение корней. Цель этого этапа – найти приближенные значения корней уравнения или выделить отрезки, содержащие только один корень. Самой простой реализацией этого этапа является построение графика. На втором этапе найденные приближенные значения корней уравнения уточняются с помощью какого-либо из численных методов. В курсовой работе рассматривается метод Ньютона.

Классический метод Ньютона или касательных заключается в том, что если x_n – некоторое приближение к корню x уравнения (28), то следующее приближение определяется как корень касательной к функции , проведенной в точке x_n.

(28)

Уравнение касательной к функции в точке x_n имеет вид (29):

(29)

В уравнении касательной положим y=0 и x=x_n+1.

Тогда алгоритм последовательных вычислений в методе Ньютона отражает формула (30):

(30)

Сходимость метода касательных квадратичная, порядок сходимости равен 2.

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

Исходные данные: a=1, b=1, c=5, d=6, f=433 [1].

3.2 Решение кубического уравнения с помощью MS Excel

Найдем приближенное значение корня нелинейного уравнения. Для этого построим его график, приведя уравнение к общему виду (26).

Поместим в ячейки А2:А18 значения x от 0 до 8 с шагом 0,5. В ячейках В2:В18 вычислим значения у, соответствующие каждому х. Для этого в ячейку В2 введем формулу (31) и растянем ее до ячейки B18 включительно.

По значениям A2:B18 построим график, используя точечную диаграмму. Снимем с графика начальное приближение к корню уравнения и запишем его в ячейку D18, в ячейке E18 рассчитаем значение y, соответствующее приближенному корню. Далее активируем ячейку Е18 и воспользуемся опцией «Подбор параметра», установив требуемое значение y равным нулю. Поиск решения осуществляется автоматическим изменением x.

Результаты решения уравнения представлены на рисунке 17.

Рисунок 17 – Решение кубического уравнения средствами MS Excel

3.3 Решение кубического уравнения в MathCad

Решение задачи средствами MathCad может быть осуществлено с помощью кода, представленного на рисунке 18.

Для решения задачи так же, как и в предыдущем пункте, строим график, «снимаем» с него приближенный корень и далее уточняем его. Уточнение корней в MathCad производится с помощью функции root. Первым аргументом данной функции является левая часть уравнения, второй аргумент – переменная, содержащая приближение к соответствующему корню.

Рисунок 18 – Решение кубического уравнения в MathCad

3.4 Решение кубического уравнения в VBA

Решение данной задачи осуществляется с помощью вызова формы UserForm, создания на ней 8 компонентов Text, 11 компонентов Label и двух командных кнопок для вывода решения и закрытия формы. Используем графики уравнения из предыдущего пункта для определения приближенного значения корня.

Программный код решения представлен на рисунке 19, результат выполнения программы – на рисунке 20.

Рисунок 19 – Текст программы для решения кубического уравнения

Рисунок 20 – Результат решения кубического уравнения

3.5 Решение кубического уравнения в MatLab

Функция для вычисления левой части уравнения представлена на рисунке 21. На рисунке 22 представлен m-файл для построения графика и уточнения корней. График, построенный с помощью MatLab отображен на рисунке 23, результат уточнения корней – на рисунке 24. Уточнение корней в MatLab производится с помощью функции fzero.

Рисунок 21 – вычисление левой части уравнения

Рисунок 22 – M-файл в MatLab

Рисунок 23 – График уравнения в MatLab

Рисунок 24 – Результат решения в MatLab

Результаты решения уравнения в разных программах представлены в таблице 5

Таблица 5 – Решение кубического уравнения

Программа	MS Excel	VBA	MathCad	MatLab
Корень уравнения	7,00	7,000

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

В программе 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. Получим тот же результат:

ПОИСК РЕШЕНИЯ В EXCEL

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

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

Цель – решать уравнения n -ой степени и системы уравнений с помощью Excel .

Для достижения данной цели поставим следующие задачи :

изучить возможности инструмента «Поиск решения»;

создать в Excel шаблоны для решения различных типов задач;

разработать инструкции нахождения решений;

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

Модели всех задач на оптимизацию состоят из следующих элементов:

Переменные – неизвестные величины, которые нужно найти при решении задачи.

Целевая функция – величина, которая зависит от переменных и является целью, ключевым показателем эффективности или оптимальности модели.

Ограничения – условия, которым должны удовлетворять переменные.

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

Найти минимальное значение функции .

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

Получен результат: минимальное значение функции y = -9 при x = 1. Так как исследована квадратичная функция, графиком которой является парабола, ветви направлены вверх, тогда точка (1, -9) является вершиной параболы. Значит, с помощью инструмента Поиск решения также можно найти и координаты вершины параболы, что в свою очередь сокращает время в их нахождении.

Найти максимальное значение функции .

Решить уравнение