Как быстро решать в Excel системы линейных уравнений вручную формулами, а также автоматически при помощи надстройки PowerQuick
В дополнение темы по построению балансовых моделей в Microsoft Excel я решил выпустить отдельную статью на тему решения систем линейных алгебраических уравнений (СЛАУ) в Excel, которая имеет более широкое применение.
В математике есть несколько методов решения СЛАУ. Применительно к Excel самым эффективным и простым является так называемый матричный метод. Приведенная методика решения уравнений проста в освоении и очень производительна. Данная информация будет полезна для учащихся, тем, кто связан в работе с математическими расчетами, а также всем, кто интересуется продвинутыми возможностями Excel.
Рассмотрим систему из трех линейных уравнений:
Данную систему уравнений можно записать в так называемом матричном виде, то есть обобщить все элементы системы:
Преобразования над уравнением в матричной записи аналогичны обычному уравнению, таким образом, получим:
Таким образом, для решения системы уравнений необходимо решить полученное уравнение в матричном виде относительно X. Произведем несложные расчеты в Excel с применением функций по работе с матрицами.
Сформируем на листе Excel матрицы коэффициентов и свободных членов, как показано на рисунке.
Рис. 1 — Исходные данные
Вычислим обратную матрицу коэффициентов, т.е. A^-1, воспользовавшись специальной функцией МОБР() (вводится через формулы массива, т.е. при помощи нажатия Ctrl+Shift+Enter):
Рис.2 — Вычисление обратной матрицы коэффициентов
Результат работы команды:
Рис.3 — Обратная матрица коэффициентов
Далее перемножим полученную матрицу с матрицей линейных коэффициентов, т.е. вычислим Y*A^-1 через функцию по перемножению матриц МУМНОЖ() (также формулы массивов!), что и будет решением уравнения:
Рис.4 — Вычисление корней уравнения
Рис.5 — Корни уравнения
Пример использования данного метода на практике приведен в статье на сайте по составлению балансовой модели по расчету себестоимости продукции предприятия.
Приведенный метод очень производительный и может с легкостью решать системы из сотен и более уравнений, однако его не так просто реализовать, прописывая все формулы вручную. К счастью, у нас есть надстройка, которое автоматически решает системы уравнений, это надстройка PowerQuick для Excel.
В надстройке PowerQuick есть мастер решения систем уравнений. Задайте матрицы коэффициентов и свободных членов – все вычисления программа сделает автоматически:
