Лабораторная работа № 2

MS 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.

· после появления значения в левом верхнем углу выделенного диапазона последовательно нажать клавишу F2 и комбинацию клавиш Ctrl+Shift+Enter.

Перемножение матриц.

Для перемножения матриц необходимо

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

· в меню Вставка – Функция в категории Математические выбрать функцию МУМНОЖ и нажать OK;

· на втором шаге задать два диапазона ячеек с элементами перемножаемых матриц, и нажать OK.

· после появления значения в левом верхнем углу выделенного диапазона последовательно нажать клавишу F2 и комбинацию клавиш Ctrl+Shift+Enter.

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

Решение системы уравнений при помощи нахождения обратной матрицы.

Пусть дана линейная система уравнений.

Данную систему уравнений можно представить в матричной форме:

где

Матрица неизвестных вычисляется по формуле

где A^-1 – обратная матрица по отношению к A.

Для вычисления уравнения в Excel необходимо:

· ввести матрицу A;

· ввести матрицу B;

· вычислить обратную матрицу по отношению к А;

· перемножить полученную обратную матрицу с матрицей B.

Порядок выполнения работы

Задание 1

Найти все корни уравнения 2x³-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.