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 .
Решение нелинейного уравнения в Excel
Разберём решение нелинейного уравнения в Excel вида:
y=4x 3 +2x–7
Ячейку A4 оставим пустой, а в ячейки B4 запишем формулу вида
Затем в Excel перейдём на вкладку Данные -> Поиск Решения
Открывается окно Параметры поиска решения. В поле оптимизировать целевую функцию выбираем ячейку B4, ставим Значения 0, ячейку переменной указываем A4, ставим галочку сделать переменные без ограничений неотрицательными, выбираем метод решения — поиск решения нелинейных задач методом ОПГ (обобщенного приведенного градиента) и жмем Найти решение
Получаем решение искомой задачи
x=1,06744215530327
Отчет результатов вычисления в Excel
Решение нелинейных уравнений с помощью средства MS Excel Подбор параметра
Постановка задачи. Дано уравнение: x 3 –0,01x 2 –0,7044x+0,139104 = 0. Необходимо решить его с помощью средства MS Excel Подбор параметра с точностью 0,001 [6].
Выполнение. Для начала решим уравнение графически. Известно, что графическим решением уравнения f(x) = 0является точка пересечения графика функции f(x) с осью абсцисс, т. е. такое значение x, при котором функция обращается в ноль.
Проведем табулирование нашего полинома на интервале от -1 до 1 с шагом 0,2. Результаты вычислений приведены на рис.1, где в ячейку В2 была введена формула: = A2^3-0,01*A2^2-0,7044*A2+0,139104.
На графике видно, что функция три раза пересекает ось Оx, а так как полином третьей степени имеет не более трех вещественных корней, то графическое решение поставленной задачи найдено: была проведена локализация корней, т. е. определены интервалы, на которых находятся корни данного полинома: [-1,-0.8], [0.2,0.4] и [0.6,0.8].
Теперь можно найти корни полинома методом последовательных приближений с помощью команды: Сервис → Подбор параметра. Относительная погрешность вычислений и предельное число итераций (например, 0,00001 и 1000) задаются на вкладке Сервис → Параметры.
Рис.1. Результаты вычислений
После ввода начальных приближений и значений функции можно обратиться к пункту меню Сервис → Подбор параметра и заполнить диалоговое окно следующим образом (рис.2.).
В поле Установить в ячейке дается ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения (уравнение должно быть записано таким образом, чтобы его правая часть не содержала переменную). В поле Значение вводим правую часть уравнения, а в поле Изменяя значения ячейки дается ссылка на ячейку, отведенную под переменную. Заметим, что вводить ссылки на ячейки в поля диалогового окна Подбор параметров удобнее не с клавиатуры, а щелчком на соответствующей ячейке.
Рис.2. Диалоговое окно «Подбор параметра» |
После нажатия кнопки ОК появится диалоговое окно Результат подбора параметра (рис. 3.) с сообщением об успешном завершении поиска решения приближенное значение корня будет помещено в ячейку А14.
Рис. 3. Диалоговое окно «Результат подбора параметра»
Рис.4. Результаты вычислений |
Два оставшихся корня находим аналогично. Результаты вычислений будут помещены в ячейки А15 и А16 (см. рис.4.).
Постановка задачи. Дано уравнение: e x – (2x – 1) 2 = 0.
Необходимо решить его с помощью средства MS Excel Подбор параметра – с точностью 0,001.
Выполнение.Проведем локализацию корней нелинейного уравнения.
Для этого представим его в виде 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). Для этого в диапазон А3:А18 введем значения аргумента. В ячейку В3 введем формулу для вычисления значений функции:
Результаты вычислений и построение графиков f(x) и g(x) в одной графической области показаны на рис.5.
Рис. 5. Результаты вычислений и построение графиков f(x) и g(x)
На графике видно, что линии f(x) и g(x) пересекаются дважды, т. е. данное уравнение имеет два решения. Одно из них тривиальное и может быть вычислено точно:
Для второго можно определить интервал изоляции корня: 1,5
http://www.matematicus.ru/excel/reshenie-nelinejnogo-uravneniya-v-excel
http://mydocx.ru/11-17827.html