Решение систем уравнений с помощью табличного процессора Excel.

В табличном процессоре Ехсеl для решения систем уравнений есть два варианта: реализация алгоритмов в электронной таблице с помощью основных средств табличного процессора и использование специальных средств.

Первый вариант проиллюстрирован на примере системы урав­нений

х₁ + 2х₂ + 2х₄ = 5;

2х₁ + 4х₂ - х₃ + 5х₄ = -1;

х₁ + 3 х₂ + 5х₄ = -3;

3х_{ + 7 х₂ - 3 х₃ + 9х₄ = -13.

На рис. 3.1 приведены идентичные тексты в Ехсе1, но один — в режиме отображения формул, а другой — значений. Из них пре­красно видно устройство алгоритма.

Рисунок 3.1. Решение системы линейных алгебраических уравнений в Excel.

Второй вариант не столь очевиден. Среди встроенных в Ехсеl математических программ программы решения систем уравнений, строго говоря, нет.

Но точно так же, как для решения уравнений в лабораторной работе № 2 было ис­пользовано средство Подбор параметра, для решения систем мо­жет быть использовано средство, предназначенное совсем для другой цели (для решения задач оптимизации).

Это средство — Поиск решения. Поясним его использование и приведем примеры применения. Поиск решения активируется в меню Сервис (если это средство не установлено, то это необходимо сделать).

Предварительно проводят следующую подготовительную ра­боту.

1.Отводят для каждой переменной по ячейке.

2.Вводят формулы для вычисления правых частей уравнений системы (по одной формуле в ячейку).

После этого запускают Поиск решения. Возможный вид экрана для подготовки к решению той же системы, что и выше, приве­ден на рис. 3.2. На этом рисунке под переменные отведены ячейки А2: D2, под формулы — ячейки А4, А6, А8 и А10. Какие именно числовые значения переменных будут введены в ячейки А2: D2, при решении системы линейных уравнений значения не имеет (итерационная процедура, заложенная в Поиск решения, стартует с этих значений).

Рисунок 3.2. Подготовка к решению системы уравнений посредством опции Поиск решения.

Еще один элемент в таблице — целевая функция. Она в данном случае особой роли не играет, но какую-нибудь формулу ввести необходимо, иначе Поиск решения работать не может (напомним, что эта программа нацелена на другой класс задач). Точно так же неважно, как установлен флажок: “максимальному значению” или “минимальному значению”.

Теперь необходимо ввести то, что в форме на рис. 3.2 именует­ся ограничениями. Щелкнув по кнопке Добавить, получают дру­гую форму (рис. 3.3).

Рисунок 3.3. Форма Добавление ограничений.

В форму, изображенную на рис. 3.3, надо ввести четыре усло­вия (по числу уравнений системы). На рисунке отражено послед­нее условие. Ссылка на ячейку А10 обусловлена тем, что в ней — формула для левой части 4-го уравнения, знак '=' выбран из меню, число (-13) введено с клавиатуры (правая часть 4-го уравнения). После ввода последнего ограничения нажимают кнопку ОК ивоз­вращаются в основную форму Поиск решения. Щелкнув по кнопке Выполнить, получают результат (рис. 3.4).

Рисунок 3.4. Результаты поиска решения: x₁=9, 5, x₂=-2, 5, x₃=5, x₄=-1.

Каким методом это решение получено, можно узнать, щелк­нув по кнопке Параметры (см. рис. 3.2). Там, в частности, есть метод Ньютона (наверняка сильно видоизменен­ный, поскольку применяется на самом деле к решению гораздо более сложной задачи).

Поиск решения можно попытаться применить и к решению си­стем нелинейных уравнений. В этом случае выбор начального при­ближения очень важен, в зависимости от него решение может быть получено или не получено и могут быть получены разные решения.