Решение уравнений и систем линейных уравнений

1.1.1 Решение уравнений в Excel

Решение уравнений в Excel производится в два этапа:

● локализация корней, т. е. определение приближенного значения корня или интервала его нахождения (аналитически или графически);

● уточнение корней, которое проще выполнять, пользуясь инструментом Подбор параметра.

Пример 4. Решить уравнение

                             .                        (4)

Решение.

1 Преобразуйте уравнение к стандартной форме f (x) = 0:

                           .                      (5)

2 С целью отделения корней создайте столбец значений переменной x, изменяющейся в диапазоне [–5; 5] с шагом 0,5, и столбец соответствующих значений функции

                         .                     (6)

Для этого сначала в ячейке A1 наберите x =, а в ячейке B1 − f (x) =.

3 В ячейку A2 введите отрицательное число –5, являющееся левой границей интервала изменения переменной x.

4 В ячейку A3 занесите число –4,5, которое на величину шага 0,5 больше числа из ячейки A2.

5 Выделите ячейки A2 и A3:

6 Примените инструмент автозаполнения ячеек в диапазоне от A4 доA22.

7 В ячейку B2 введите формулу, начиная ее знаком =. Для ввода степени вызовите мастер функций, щелкнув на кнопке  панели инструментов Стандартная. В категории Математические выберите функцию СТЕПЕНЬ. Появится окно, отображенное на рисунке 18.

8 Чтобы окно не мешало набору формулы, переместите его на свободное место рабочего листа посредством технологии Drag - and - Drop.

Рисунок 18 – Диалоговое окно Аргументы функции

В поле Степень введите число 3.

9 Установите курсор в поле Число. Для ввода аргумента  щелкните на ячейке A2, наберите знак деления /, а затем число 2. Должно получиться A2/2.

10 Не закрывая окно Аргументы функции, щелкните в окне Excel в строке формул в конце выражения и наберите знак минус (–).

11 В списке функций в левом верхнем углу окна Excel

выберите Другие функции… Далее в категории Математические найдите и вставьте в формулу обращение к функции LN(), а затем в качестве аргумента функции LN − обращение к функции ABS(А2).

12 Перейдите в конец формируемого выражения в строке формул, наберите знак минус, число 2 и нажмите клавишу Enter.

В ячейке B2 должен отобразиться результат в виде отрицательного числа –19,23444.

13 Выделите ячейкуB2 и примените инструмент автозаполнения ячеек в диапазоне от B3доB22.

Фрагменты итоговой электронной таблицы в режиме отображения значений и в режиме формул соответственно показаны на рисунке 19.

            

Рисунок 19 – Результаты вычислений

Обратите внимание, что при  функция f (x) меняет знак с минуса на плюс. Следовательно, корень уравнения f (x) = 0 находится в интервале x от 2,5 до 3.

Этап отделения корней завершен.

14 Чтобы зафиксировать найденное начальное приближение, скопируйте содержимое ячеек A18 иB18 и вставьте, например, в ячейкиA25 иB25 соответственно. Можно также поместить в ячейкуA25 среднее арифметическое чисел, содержащихся в ячейках A18 и A19.

15 Установите точность дальнейших вычислений равной 0,0001. Для этого командой Сервис / Параметры вызовите диалоговое окно Параметры, на его вкладке Вычисления в поле Относительная погрешность введите число 0,0001 и щелкните на кнопке Enter.

16 Активизируйте ячейкуB25, в которой находится значениефункции f (x) при x = 3, близком к корню уравнения.

17 Выполните команду Сервис / Подбор параметра. В отобразившемся диалоговом окне заполните поля, как показано на рисунке 20.

Рисунок 20 – Результаты вычислений

Значение переменной x, которое находится в ячейке $A$25, будет варьироваться таким образом, чтобы соответствующее ему значение функции (в ячейкеB25) стало равным 0, т. к. правая часть уравнения (5), корень которого определяется, равна нулю.

18 После щелчка мышью на кнопке OK появится информационное окно, показанное на рисунке 21.

Рисунок 21 – Результаты подбора параметра

Видно, что решение найдено с заданной точностью (погрешность – менее 10^-6). Значение корня уравнения x = 2,9057 отобразилось в ячейке A25, а соответствующее ему значение функции − в ячейкеB25 (рисунок 22), т. е. в тех ячейках, в которые ранее были введены начальное приближение для корня и значение функции.

Рисунок 22 – Результаты определения корня уравнения

1.1.2 Решение систем линейных уравнений в Excel

В Excel имеется ряд функций для обработки двумерных массивов. В частности, категория Математические содержит функции:

1. МОБР(массив) − возвращает обратную матрицу для выбранного диапазона ячеек, хранящего значения элементов квадратной матрицы;

2. МОПРЕД(массив) − возвращает определитель матрицы (матрица хранится в массиве);

3. МУМНОЖ(массив1; массив2) − возвращает произведение матриц;

4. ТРАНСП(массив) – преобразует вертикальный диапазон ячеек в горизонтальный, или наоборот;

5. ЧИСЛСТОЛБ(массив) – возвращает число столбцов в массиве;

6. ЧСТРОК(массив) – возвращает число строк в массиве.

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

Пример 5. Решить систему уравнений

                                            (7)

Решение.

1 В ячейкеA2 наберите текст А =, а в ячейки диапазонаB1:D3 поместите значения коэффициентов при неизвестных системы уравнений (7).

2 В ячейку F2 введите текстb =, а ячейки диапазона G1:G3 заполните значениями правых частей уравнений системы.

Результаты ввода исходных данных приведены на рисунке 23.

Рисунок 23 – Исходные данные системы уравнений

3 В ячейкеA5 наберите текст | А| = и перейдите в ячейкуB5.Чтобы вычислить определитель матрицы A, вызовите Мастер функций и в категории Математические щелкните на имени функции МОПРЕД,которая возвращает величину определителя матрицы. Откроется диалоговоеокно Аргументы функции для функции МОПРЕД. Выделите на рабочем листе диапазон ячеекB1:D3.При этомв диалоговомокне Аргументы функции, показанном на рисунке 24, в поле Массив появится ссылка на диапазон ячеекB1:D3, содержимое первых ячеек данного диапазона и равное –234,024 значение определителя матрицы.

Рисунок 24 – Результаты вычисления определителя матрицы

Щелкните на кнопке OK. На рабочем листе в ячейке B5 отобразится величина определителя матрицы A: .

4 Так как определитель матрицы отличен от нуля, то система линейных уравнений имеет единственное решение, определяемое по формуле

                                 .                            (8)

5 В ячейкеA8 наберите текст X = и выделите диапазон ячеекB7:B9, предназначенный для отображения найденного решения:

6 Поместите курсор в строку формул, введите знак = и вызовите Мастер функций. Щелкните на имени функции МУМНОЖ, которая возвращает результат умножения матриц. Откроется диалоговое окно Аргументы функции для функции МУМНОЖ. В формулу (8) входит обратная матрица. Для ее нахождения установите курсор в поле Массив1 и наберите строку МОБР(B1:D3), а в поле Массив2 введите ссылку на диапазон ячеекG1:G3, определяющий значения столбца свободных слагаемых b. Диалоговое окно Аргументы функции для функции МУМНОЖ примет вид, показанный на рисунке 25.

Рисунок 25 – Результаты вычислений

7 Завершите ввод формулы не традиционным щелчком на кнопке OK, а комбинацией клавиш Ctrl Shift Enter. Нажимать их следует последовательно и не отпускать 1−2 секунды, пока не зафиксируется одновременное нажатие всех трех клавиш. При этом формула в строке формул будет заключена в фигурные скобки, а диапазонB7:B9 заполнится числами, являющимися решением системы уравнений. Соответствующий фрагмент электронной таблицы приведен на рисунке 26.

Рисунок 26 – Результаты вычислений

8 Выполните умножение матрицы коэффициентов при неизвестных системы A на столбец со значениями найденного решения X. Если система уравнений решена правильно, то в результате умножения должен получиться столбец ячеек, числа в которых отличаются от значений вектора b на величину погрешности расчета или совпадают с этими значениями.

Выделите диапазон ячеекD7:D9, предназначенный для отображения результатов умножения A на X, введите в строку формул последовательность символов =МУМНОЖ(B1:D3;B7:B9)и нажмите комбинацию клавиш Ctrl Shift Enter.

Введенная формула преобразуется к виду

{=МУМНОЖ(B1:D3;B7:B9)},

а на рабочем листе появится результат проверки решения системы уравнений: числа в ячейках диапазона D7:D9, как видно из рисунка 27, совпадают с элементами вектора b.

Рисунок 27 – Результат проверки

Следовательно, система уравнений решена правильно.