Решение нелинейного уравнения в Excel
Разберём решение нелинейного уравнения в Excel вида:
y=4x 3 +2x–7
Ячейку A4 оставим пустой, а в ячейки B4 запишем формулу вида
Затем в Excel перейдём на вкладку Данные -> Поиск Решения
Открывается окно Параметры поиска решения. В поле оптимизировать целевую функцию выбираем ячейку B4, ставим Значения 0, ячейку переменной указываем A4, ставим галочку сделать переменные без ограничений неотрицательными, выбираем метод решения — поиск решения нелинейных задач методом ОПГ (обобщенного приведенного градиента) и жмем Найти решение
Получаем решение искомой задачи
x=1,06744215530327
Отчет результатов вычисления в Excel
1. Решение нелинейных уравнений в MS Excel
1.1 Отделение корней
В общем виде любое уравнение одной переменной принято записывать так , при этом корнем (решением) называется такое значение x *, что оказывается верным тождеством. Уравнение может иметь один, несколько (включая бесконечное число) или ни одного корня. Как легко видеть, для действительных корней задача отыскания решения уравнения легко интерпретируется графически: корень есть такое значение независимой переменной, при котором происходит пересечение графика функции, стоящей в левой части уравнения f ( x ) , с осью абсцисс.
Например , для уравнения выполним преобразование и приведем его к виду f ( x )= 0 т.е. . График этой функции представлен на рисунке 1. Очевидно, что данное уравнение имеет два действительных корня – один на отрезке [-1, 0] , а второй – [1, 2].
Рисунок 1. График функции
1.2 Решение уравнений, используя инструмент “Подбор параметра”
Используя возможности Excel , можно находить корни нелинейного уравнения вида f ( x )=0 в допустимой области определения переменной. Последовательность операций нахождения корней следующая:
1. Производится вычисление значений функции в диапазоне вероятного существования корней от значений аргумента, изменяющегося с определенным шагом;
2. В таблице выделяются ближайшие приближения к значениям корней (пары соседних значений функции с разными знаками);
3. Используя средство Excel Подбор параметра, вычисляются корни уравнения.
2. Работа с матрицами в MS Excel . Решение систем уравнений.
Нахождение определителя матрицы
Перед нахождением определителя необходимо ввести матрицу в диапазон ячеек Excel в виде таблицы.
Для нахождения определителя матрицы в Excel необходимо:
· сделать активной ячейку, в которой в последующем будет записан результат;
· в меню Вставка – Функция в категории Математические выбрать функцию МОПРЕД и нажать OK ;
· на втором шаге задать диапазон ячеек, в котором содержатся элементы матрицы, и нажать OK .
Нахождение обратной матрицы
Для нахождения обратной матрицы необходимо
· выделить диапазон ячеек, в которых в последующем будут записаны элементы матрицы ( количество строк и количество столбцов должны равняться соответствующим параметрам исходной матрицы).
· в меню Вставка – Функция в категории Математические выбрать функцию МОБР и нажать OK ;
· на втором шаге задать диапазон ячеек, в котором содержатся элементы исходной матрицы, и нажать OK .
· после появления значения в левом верхнем углу выделенного диапазона последовательно нажать клавишу F 2 и комбинацию клавиш Ctrl + Shift + Enter .
Для перемножения матриц необходимо
· выделить диапазон ячеек, в которых в последующем будут записаны элементы результирующей матрицы.
· в меню Вставка – Функция в категории Математические выбрать функцию МУМНОЖ и нажать OK ;
· на втором шаге задать два диапазона ячеек с элементами перемножаемых матриц, и нажать OK .
· после появления значения в левом верхнем углу выделенного диапазона последовательно нажать клавишу F 2 и комбинацию клавиш Ctrl + Shift + Enter .
Решение системы уравнений в Excel .
Решение системы уравнений при помощи нахождения обратной матрицы.
Пусть дана линейная система уравнений.
Данную систему уравнений можно представить в матричной форме:
Матрица неизвестных вычисляется по формуле
где A -1 – обратная матрица по отношению к A .
Для вычисления уравнения в Excel необходимо:
· ввести матрицу A;
· ввести матрицу B;
· вычислить обратную матрицу по отношению к А ;
· перемножить полученную обратную матрицу с матрицей B .
Порядок выполнения работы
Задание 1
Найти все корни уравнения 2x 3 -15sin( x )+0,5x-5=0 на отрезке [-3 ; 3].
1. Построить таблицу значений функции f ( x ) для значений x от –3 до 3, шаг 0,2.
Для этого ввести первые два значения переменной x , выделить эти две ячейки, с помощью маркера автозаполнения размножить значения до 3.
Затем ввести формулу для вычисления f ( x ). Скопировать формулу с использованием маркера автозаполнения на весь столбец.
Из полученной таблицы находим, что значение функции трижды меняет знак, следовательно, исходное уравнение имеет на заданном отрезке три корня.
2. Выделить цветом пары значений x и f ( x ), где f ( x ) меняет знак (см .р исунок 2).
3. Построить график функции f ( x ).
Рисунок 2. Поиск приближенных значений корней уравнения
4. Скопировать рядом с таблицей произвольную пару выделенных значений x и f ( x ) (см .р исунок 3).
5. Выполнить команду меню Сервис/Подбор параметра. В диалоговом окне (рисунок 3) заполнить следующие поля:
þ Установить в ячейке : в поле указывается адрес ячейки, в которой записана формула правой части функции;
þ Значение : в поле указывается значение, которое должен получить полином в результате вычислений, т.е. правая часть уравнения (в нашем случае 0);
þ Изменяя значение : в поле указывается адрес ячейки (где записано начальное приближение), в которой будет вычисляться корень уравнения и на которую ссылается формула.
Рисунок 3. Диалоговое окно Подбор параметра для поиска первого корня
6. После щелчка на ОК должно получиться значение первого корня -1,65793685 .
7. Выполнить последовательно операции, аналогичные предыдущим, для вычисления значений остальных корней: -0,35913476 и 2,05170101 .
Задание 2
Решить систему уравнений:
1. Ввести значения элементов матриц A и B уравнения в ячейки Excel .
2. Вычислить обратную матрицу с помощью матричной функции МОБР.
3. Перемножить обратную матрицу A -1 на матрицу B с помощью матричной функции МУМНОЖ (Порядок умножения важен – первой должна идти матрица A -1 а второй B .)
4. Проверить правильность полученной матрицы корней X .
Контрольные вопросы
1. Порядок действий для решения нелинейного уравнения с помощью инструмента Подбор параметра MS Excel .
2. Порядок действий для решения системы уравнений матричным методом в MS Excel .
Решение нелинейных уравнений методом хорд в MS Excel
Тема урока: «Решение нелинейных уравнений в MS Excel».
Цель урока: изучение возможностей MS Excel по решению нелинейных уравнений и практическое освоение соответствующих умений и навыков.
Тип урока: комбинированный – урок изучения нового материала и практического закрепления полученных знаний, умений и навыков.
Вид урока: сдвоенный, продолжительность – 1,5 часа.
Задачи урока:
- обучающая – научить учащихся решать нелинейные уравнения в среде электронных таблиц MS Excel;
- развивающая – познакомить учащихся с применением компьютеров в качестве помощников при решении уравнений;
- воспитательная – выработать у учащихся умение рационально использовать время и возможности компьютерных технологий при решении задач.
Оборудование урока:
- Компьютеры с OS MS Windows;
- Программа Microsoft Excel;
- Программа Turbo Pascal;
- Презентация по теме, выполненная в программе Power Point;
- Карточки с заданиями для самостоятельной работы.
В данном уроке особое внимание уделено визуальному представлению информации – в ходе урока с помощью проектора демонстрируются слайды, подготовленные в пакете презентационной графики Microsoft Power Point.
I. Организационный момент
Учитель объявляет тему и цели урока.
II. Актуализация знаний, умений и навыков учащихся – повторение материала прошлого урока по теме «Решение нелинейных уравнений методом половинного деления»
Учащиеся повторяют указанный метод с помощью слайдов, подготовленных в пакете презентационной графики Microsoft Power Point метод половинного деления
Вопросы:
- Всегда ли существуют формулы для «точного» решения уравнений?
- Сформулируйте основное условие существования корня на заданном отрезке.
- Запишите уравнение, позволяющее определить координаты середины отрезка.
- Почему алгоритм решения этой задачи можно назвать циклическим?
- Какое действие в алгоритме повторяется?
- Определите условие, при котором действие алгоритма должно остановиться.
III. Изобразите блок-схему алгоритма. блок-схема
IV. Практическое задание с использованием программы на языке Turbo Pascal (Учащимся разрешено использовать программу, составленную на предыдущем уроке. Было решено уравнение y = x 3 – cos(x)) метод половинного деления TP
Задания для учащихся первой группы
Найти решение уравнения y = x 3 – cos(x) на отрезке [–1;1], при = 0,0001
Задания для учащихся второй группы
Найти решение уравнения y = x 3 – cos(x) на отрезке [–1;1], определить на каком шаге циклического алгоритма будет получено решение.
V. Изучение нового материала «Решение нелинейных уравнений методом хорд»
VI. Объяснить алгоритм решения уравнения f(x)=0 на отрезке [а;в] методом хорд с помощью слайдов, подготовленных в пакете презентационной графики Microsoft Power Point. метод хорд
Вопросы:
- Запишите уравнение, позволяющее определить координаты точки пересечения с осью ОХ.
- Почему алгоритм решения этой задачи можно назвать циклическим?
- Какое действие в алгоритме повторяется?
- Определите условие, при котором действие алгоритма должно остановиться.
- Изобразите блок-схему алгоритма блок-схема2
Этапы решения задачи
- Содержательная постановка задачи. Решение уравнения y = x 3 – cos(x) на отрезке [–1,4; 1,4] с точностью = 0,001.
- Визуализация решения задачи с помощью построения графика заданной функции с помощью процессора MS Excel, используя метод подбора параметра определить корень уравнения.
- Формальная математическая модель.
- Задание математической формулы для отыскания корня уравнения на отрезке
- Задание системы ограничений при использовании циклического алгоритма
- Требование к диапазону задания переменных
Для формализации модели используем математические формулы.
уравнение прямой, проходящей через две точки, где x1 = a, x2 = в, y1 = f(a), y2 = f(в).
После математических преобразований уравнение примет вид .
Определим корень уравнения
- Блок схема алгоритма решения задачи блок-схема2
- Программа на языке Turbo Pascal метод хорд ТР
- Заполнение расчетной таблицы в программе MS Excel метод хорд xls
http://zf.bsut.by/it/fbo/zb1/lab2.htm
http://urok.1sept.ru/articles/524570