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

1.2.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.2.2 Решение систем линейных уравнений в Excel

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

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

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

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

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

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

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

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

Пример 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 – Результат проверки

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