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

Как уже упоминалось выше большинство встречающихся на практике вычислительных задач сводится к решению систем линейных уравнений. Это большая удача, поскольку такие системы решаются достаточно просто. Большая их часть поддается решению встроенными математическими функциями EXCEL, поддерживающими обращение и умножение матриц размерностью до 60.

В электронных таблицах легко реализуются также итерационный метод Гаусса — Зейделя и метод верхней и нижней релаксации, которые применимы к решению как линейных, так и нелинейных систем.

Матричные методы

Легче всего в EXCEL реализуются матричные методы решения системы n линейных уравнений с n неизвестными вида:

(1).

Для их применения систему (1) необходимо записать в матричном форме:

(2).

где, ,.

А — матрица коэффициентов, х — вектор неизвестных, b — вектор правой части уравнений.

Для решения матричного уравнения (2) обе его части умножаются на матрицу, обратную к А:

(3).

По определению, произведение матрицы на обратную к ней дает единичную матрицу, а произведение единичной матрицы на любой вектор равно этому же вектору, поэтому предыдущее уравнение преобразуется в следующий вид:

(4).

Это и есть решение системы уравнений (1). Хотя в абстрактном виде все очень просто нахождение обратной матрицы — задача достаточно трудная. Средствами EXCEL эта задача просто решается. В EXCEL есть для этого встроенная функция: МОБР (), которая возвращает матрицу, обратную к данной.

Пример.

Совместная фирма выпускает четырёх видов ткани А1, А2, А3, А4 при этом используются нити четырех типов S1, S2, S3 и S4. Нормы расхода каждого из них на один вид ткани и объем расхода нити на 1 день заданы в таблице:

Найти ежедневный объем выпуска ткани каждого вида.

Решение: Пусть ежедневно фирма выпускает x₁ ткань вида А1, x₂ ткань вида А2, x₃ ткань вида А3 и x₄ ткань вида А4. Тогда в соответствии с расходом нити каждого типа имеем систему:

(1).

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

A · X = B, (2).

(3).

Если определитель матрицы, А не равен нулю, т. е. = А 0, то система уравнений (1) называется совместимой и имеет единственное решение. Следовательно, существует обратная матрица А^-1 матрицы коэффициентов А. Тогда решение матричного уравнения (2) можно представит в векторной форме: Х= А^-1В.

Первый способ. Решение системы (1) помощью обратной матрицы Алгоритм решение системы (1) средствами Excel состоит из следующих этапов:

Ввод исходных данных (т.е. элементы матрицы, А и вектора В).

Исследование совместимости системы (т.е. вычисления определителя матрицы А).

Нахождение обратной матрицы А^-1.

Умножение обратной матрицы А^-1 на вектор В.

Проверка полученных результатов.

Каждый из этапов осуществляется следующим образом:

1. Введем элементы матрицу, А в диапазон А1: D4, а элементы вектора B в диапазоне Е1: Е4; (рис. 19).

Рис. 19. Ввод исходных данных

2. Проверяем на совместимость систему (1). Для этого выполняем следующие действия:

Выделим ячейку, например А6 (поставим табличный курсор в ячейку А6) в которой требуется получить значение определителя матрицы, А (рис. 20).

Нажимаем на панели инструментов «Стандартная» кнопку «Вставка функции».

В появившемся диалоговом окне «Мастер функции» в рабочем поле «Категория» выбираем «Математические», а в рабочем поле «Функция» — имя функция «МОПРЕД» (функция МОПРЕД вычисляет определитель матрицы, хранящейся в виде массива), после этого щелкнем на кнопке «ОК» (рис. 21).

Рис. 20. Выделение места для значение определителя матрицы А

Появившееся диалоговое окно «МОПРЕД» мышью отодвинем в сторону от исходной матрицы и введем диапазон исходной матрицы А1: D4 в рабочее поле «Массив» (указателем мыши при нажатой левой кнопки), затем нажимаем кнопку «ОК» (рис. 22).

Рис. 21. Выбор функции МОПРЕД

В результате этих процедур в ячейки А6 появится значение определителя матрицы — 6.

Следовательно, = А = 6 0, т. е. система совместима.

Рис. 22. Заполнения диалогового окна «МОПРЕД»

3. Найдем обратную матрицу А-1. Для этого:

Выделим блок ячеек под обратную матрицу, например, выделим блок G1: J4 (указателем мыши при нажатой левой кнопке) (рис. 23).

Рис. 23. Выделение места для обратной матрицы А^-1

Нажимаем на панели инструментов «Стандартная» кнопку «Вставка функции»;

В появившемся диалоговом окне «Мастер функций» в рабочем поле «Категория» выберем пункт «Математические», а в рабочем поле «Функция» — имя функции «МОБР» (функция МОБР вычисляет обратную матрицу для матрицы, хранящейся в таблице в виде массива), после этого нажмем кнопку «ОК» (рис. 24).

Рис. 24. Выбор функции «МОБР»

Появившееся диалоговое окно «МОБР» мышью отодвинем в сторону от исходной матрицы и введем диапазон исходной матрицы А1: D4 в рабочее поле «Массив» (указателем мыши при нажатой левой кнопке). Нажимаем сочетание клавиш (Рис. 25).

Рис. 25. Заполнение поле массив диалогового окна «МОБР»

Если обратная матрица не появилась в диапазоне G1: J4, то следует щелкнуть указателем мыши в строке формуле и повторить нажатие. В результате в диапазоне G1: J4 появится обратная матрица (рис. 26).

Рис. 26. Полученная обратная матрица в диапазоне G1: J4.

4. Умножая обратную матрицу А-1 на вектор В находим координаты вектор Х, т. е. решения исходной системы. Для этого выполняем следующие действия:

Выделим блок ячеек под результирующую матрицу (под вектор Х), ее размерность будет 4×1. Например, выделим блок ячеек L1: L4 (указателем мыши при нажатой левой кнопке).

Нажимаем на панели инструментов «Стандартная» кнопку «Вставка функции».

В появившееся диалоговое окне «Мастер функций» в рабочем поле «Категория» выбираем «Математические», а в рабочем поле «Функция» — имя функции «МУМНОЖ» (рис. 27), щелкнем на кнопке «ОК»;

Рис. 27. Выбор функции «МУМНОЖ»

Появившееся диалоговое окно «МУМНОЖ» мышью отодвинем в сторону от исходных матриц и введем диапазон обратной матрицы А^-1 — G1: J4 в рабочее поле Массив 1 (указателем мыши принажатой левой кнопке), а диапазон вектора В — Е1: Е4 введем в рабочее поле Массив 2, после этого нажмем сочетание клавиш (рис. 28).

Рис. 28. Заполнение поле Массива 1 и Массива 2 диалогового окна «МУМНОЖ» и полученные результаты в диапазоне L1: L4

Если вектор Х не появляется в диапазоне L1: L4, следует щелкнут указателем мыши в строке формуле и повторить нажатие «Ctrl + Shift + Enter».

В результате в диапазоне L1: L4 появится вектор Х координаты которого является решением данной системы (рис. 28).

Следовательно, ежедневно фирма выпускает х₁=300 ед. ткань вида А1, х₂=200 ед. ткань вида А2, х₃=250 ед. ткань вида А3, х₄=250 ед. ткань вида А4.

5. Проверка. Можно проверить истинности найденного решения. Для этого найденный вектор решения Х необходимо поставить в исходное матричное уравнение АХ=В.

Алгоритм проверки:

Выделим блок ячеек под вектор В (ее размерность будет 41), например выделим блок ячеек М1: М4 (указателем мыши при нажатой левой кнопке).

Нажимаем на панели инструментов «Стандартная» кнопку «Вставка функции».

В появившемся диалоговом окне «Мастер функций» в рабочем поле «Категория» выбираем «Математические» а в рабочем поле «Функция» — имя функция «МУМНОЖ» (рис. 29). Затем нажимаем кнопку «OK».

Рис. 29. Выделение блока ячеек М1: М4 под вектор В и выбор функции «МУМНОЖ»

Появившееся диалоговое окна «МУМНОЖ» мышью отодвинем в сторону от исходных матриц и введем диапазон исходной матрицы, А — А1: D4 в рабочее поле Массив 1 (указателем мыши при нажатой левой кнопке), а диапазон вектора (матрица столбец) Х — L1: L4 рабочее поле Массив 2, после этого нажимаем сочетание клавиш (рис. 30).

Рис. 30. Заполнение поле Массив 1 и Массив 2 диалогового окна «МУМНОЖ»

Если вектор не появится в диапазоне М1: М4, то следует щелкнуть указатель мыши в строке формуле и повторить нажатие .

Рис. 31. Результаты проверки

В результате в диапазоне М1: М4 появится вектор В, и, если система решена правильно, появившиеся вектор будет равен исходному:

В=(2250, 1550, 1850, 1700)^Т (рис. 31).

Второй способ. Решение системы (1) с помощью функции «МУМНОЖ» и «МОБР».

Второй способ решение системы (1) отделяется от других способ по своей простоты и компактности. Ниже приведем алгоритм решение системы (1) по второму способу:

Вводим исходные данные:

Рис. 32. Ввод исходных данных

Выделим тот диапазон в который будет введено решение. Например, Н2: Н5 (рис. 32).

Введем в него формулы:

=МУМНОЖ (МОБР (А2:С5);F2:F5).

Завершим ввод формулы нажатием комбинации клавиш. MS Excel возьмет формулу в строке формул в фигурные скобки и произведет требуемые вычисления с элементами массива и дает результаты вычисления (рис. 33).

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

Таким образом, решение системы уравнений (1) является вектор:

Третьей способ. Решение системы линейных уравнений (1) с помощью инструмента Excel «Поиск решения» (Solver).

Систему (1) решаем с помощью средства Excel «Поиск решения». «Поиск решения» является одной из надстроек (add — ins) MS Excel. Если в меню «Сервис» отсутствует команда «Поиск решения», для ее установки необходимо выбрать команду «Сервис», затем «Надстройки». На экране отобразится диалоговое окно «Надстройки». В списке «Список надстроек» выбираем «Поиск решения» и нажимаем кнопку «ОК».

Алгоритм решение состоит из следующих шагов:

Вводим элементы матрицы, А в диапазон ячеек А2: D5.

Рис. 34. Ввод исходных данных

Свободные члени внесем в столбец F. В столбец E внесем формулы вычисления свободных членов: (Е2 =СУМПРОИЗВ ($А$ 7:$D$ 7;А2:D2)).

Наша задача состоит — добиться совпадения значений вычисленных и фактических значений столбцов Е и F. В качестве изменяемых значений используется ячейки А7, В7, С7, D7, т. е. диапазон ячеек А7: D7. Первоначально они остаются пустыми, т. е. равными нулю (рис. 34).

Загружаем надстроек «Поиск решения». Для этого выбираем команду «Сервис», затем «Поиск решения». В результате на экране отобразится диалоговое окно «Поиск решения» (рис. 35).

Рис. 35. Окно «Поиск решения» после заполнения для задачи

В окне «Поиск решения» в нашей конкретном случае вводятся значения только параметров: «Изменяемые ячейки» и «Ограничения» т. е. соответственно $А$ 7:$D$ 7 и $E$ 2:$E$ 5=$F$ 2:$F$ 5 (рис. 35).

Изменяем параметры поиска (т.е. задаем точности решения, количества выполняемых итерации, допустимое отклонение (в %), максимальное время поиска решение и т. д.), для этого нажимаем кнопку «Параметры». На экране отобразится диалоговое окно «Параметры поиска решения» (рис. 36).

Рис. 36. Окно «Параметры поиска решения»

Следовательно в диалоговом окне «Параметры поиска решения» можно изменять условия и варианты поиска решения исследуемой конкретной задачи. Значения параметров, которые используются по умолчанию можно оставить так, как они и есть. Нажимаем кнопку «OK». На экране опять отобразится окно «Поиск решения».

Получим результаты поиска. Для этого нажимаем кнопку «Выполнить». На экране отобразится окно «Результаты поиска решения» (рис. 37).

Рис. 37. Окно «Результаты поиска решения»

После нажатия кнопки «OK», результаты будет внесены в рабочий лист (рис. 38).

Есть и другие способы решение системы линейных уравнений, например, метод Гаусса, метод Крамера и т. д.

Рис. 38. Полученный результат