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

В дополнение темы по построению балансовых моделей в Microsoft Excel я решил выпустить отдельную статью на тему решения систем линейных алгебраических уравнений (СЛАУ) в Excel, которая имеет более широкое применение.

 

В математике есть несколько методов решения СЛАУ. Применительно к Excel самым эффективным и простым является так называемый матричный метод. Приведенная методика решения уравнений проста в освоении и очень производительна. Данная информация будет полезна для учащихся, тем, кто связан в работе с математическими расчетами, а также всем, кто интересуется продвинутыми возможностями Excel.

 Рассмотрим систему из трех линейных уравнений:

 

 

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

 

 

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

 

 

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

 

Сформируем на листе Excel матрицы коэффициентов и свободных членов, как показано на рисунке.

 

Рис. 1 — Исходные данные

 

Вычислим обратную матрицу коэффициентов, т.е. A^-1, воспользовавшись специальной функцией МОБР() (вводится через формулы массива, т.е. при помощи нажатия Ctrl+Shift+Enter):

 

Рис.2 — Вычисление обратной матрицы коэффициентов

 

Результат работы команды:

 

Рис.3 — Обратная матрица коэффициентов

 

Далее перемножим полученную матрицу с матрицей линейных коэффициентов, т.е. вычислим Y*A^-1 через функцию по перемножению матриц МУМНОЖ() (также формулы массивов!), что и будет решением уравнения:

 

Рис.4 — Вычисление корней уравнения

 

Рис.5 — Корни уравнения

 

Данные вычисления можно сделать вручную, как показано в приведенном примере, но можно и автоматизировать! В нашей надстройке SubEx для Excel есть мастер решения систем уравнений. Задайте матрицы коэффициентов и свободных членов - все вычисления программа сделает автоматически:

Как было сказано выше, приведенный метод очень производительный и может с легкостью решать системы из сотен и более уравнений.

 

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

© эксель-быстро.рф, 2016-2019

 All rights reserved

Логотипы Excel и

MS Office являются собственностью компании Microsoft