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

На этой неделе в понедельник у меня была интересная задача: рассчитать стоимость сотой (тысячной) доли каждого легирующего элемента на тонну стали другого предприятия. Исходные данные были следующие: несколько химических составов,  т.е. несколько плавок, и стоимость тонны каждой плавки.  Всё, вроде бы, здорово: зная стоимость каждой сотой или тысячной доли процента, можно в будущем, разрабатывая концепцию легирования новой стали или же играясь с хим. составом существующей стали, сразу же оценивать экономический эффект от уменьшения легирования или замены одного элемента другим по сравнению с другими производителями.
Естественно, матрицу составить можно и решение найти тоже можно, вот только получится полная ересь. Например, может выйти так, что стоимость легирования ниобием будет отрицательная, а стоимость легирования углеродом исчисляться миллионами. Короче, затея неудачная: ведь помимо набора химических элементов есть еще куча факторов, влияющих на стоимость конечного продукта. Тем не менее нашлось два положительных момента:

1. я вспомнил, как решать системы линейных уравнений, и что примечательно, понял, как решать системы системы уравнений в Excel, поэтому в будущем это умение пригодится, когда, например, придется рассчитать компоненты тензора напряжений или еще чего-нибудь эдакого.
2. появилась тема для статьи.

Итак, для решения системы уравнений в Excel существует несколько способов:

• метод обратной матрицы;
• метод Гаусса;
• метод Зейделя.

Есть и еще, но названия перечисленных мне хотя бы знакомы с университета. Подробнее обо всех методах и многих других можно почитать в [1, стр. 268-315].

Метод Гаусса состоит в последовательном исключении неизвестных: в одном уравнении остается одна неизвестная, во втором – две, в третьем – три, и т.п. А потом начинаем вычислять неизвестные, начиная с уравнения, где она одна. Затем подставляем найденное значение первой неизвестной в уравнение с двумя неизвестными и вычисляем вторую и т.п. Более подробно узнать о методе можно опять же в [1, стр. 272-279].

На нем мы концентрироваться не будем. Мы же хотим получить результат быстро, а не поупражняться в математике.

По шагам при желании его можно выполнить, вооружившись бумагой и ручкой чисто в академических целях, когда нужно просто продемонстрировать себе, что вы это знаете, умеете и можете. Вот, например, один из преподавателей математики в моем университете интегральчики вычислял ради потехи, пока в метро домой ехал (я, кстати, пишу эту статью тоже в метро, пока еду домой). Нам интересен метод обратной матрицы, потому как Excel обладает набором функций, который позволяет применять этот метод для решения систем уравнений. Как сказал один из умнейших людей прошлого столетия: «Теория должна шагать в ногу с практикой», – так что пошаговую инструкцию решения уравнений буду приправлять теорией по ходу дела. У нас есть система n уравнений с n неизвестными:

a₁₁x₁+a₁₂x₂+…+a_1nx_n=b₁

a₂₁x₁+a₂₂x₂+…+a_2nx_n=b₂

……………………

a_n1x₁+a_n2x₂+…+a_nnx_n=b_n

Пусть у нас есть пример:

16,28·x₁+0,38·x₂+0,56·x₃=1130

16,15·x₁+4,5·x₂+0,6·x₃=1160

16,15·x₁+·x₂+0,56·x₃=1240

Можем нашу систему уравнений записать в матричном виде. Составим матрицу коэффициентов (A), столбец неизвестных (X) и столбец свободных членов  (B):

A – матрица коэффициентов	X – столбец неизвестных	B – столбец свободных членов
a11 a12 … a1na21 a22 … a2n ……………… an1 an2 … ann	x1x2 … xn	b1b2… bn

Наше уравнение с учетом введенных обозначений может быть записано в кратком виде: AX=B. Если определитель матрицы коэффициентов не равен нулю, то наша система уравнений имеет единственное решение [1, стр. 269] (то есть набор значений x₁,_,x₂,…x_n), которое равно

X=A^-1B,

где A^-1 – обратная матрица.

А теперь к практике. Запишем наше уравнение в виде таблиц A, X, B:

Теперь нужно убедиться, что определитель матрицы A не равен 0. Для вычисления определителя в Excel есть функция «МОПРЕД».

В одной из ячеек ниже вычисляем определитель нашей матрицы:

Он равен «-6,3803», а, значит, не нулю. Идем дальше.

Выделяем такую же по размеру область ячеек, как и область таблицы с коэффициентами (в нашем случае это 3х3), нажимаем «=» и вводим функцию «МОБР» и диапазон ячеек матрицы коэффициентов. Нажимаем «Ctr» + «Shift» + «Enter» на клавиатуре – функция «МОБР» вычислит обратную матрицу.

Теперь у нас есть обратная матрица и столбец коэффициентов, а значит, мы можем найти решение уравнения, умножив обратную матрицу на столбец свободных членов: X=A-1B. Для умножения матриц в Excel есть функция «МУМНОЖ».Выделяем пустой столбец, в котором количество строк равно количеству неизвестных нашего уравнения, т.е. трём. После этого нажимаем «=» и вводим функцию «МУМНОЖ», задаем диапазоны ячеек – сначала обратной матрицы (A-1), затем – столбца свободных  членов (B).

Нажимаем «Ctr» + «Shift» + «Enter» на клавиатуре. В результате в столбце будут выведены значения неизвестных:

Вот и решение нашего уравнения! Можете проверить, убедиться, что все нормально, и подписаться на обновления блога.

Подытожим:

1. записать уравнение в виде двух таблиц: матрицы коэффициентов и вектора-столбца свободных членов;
2. убедиться, что определитель матрицы коэффициентов не равен нулю, воспользовавшись функцией «МОПРЕД»;
3. найти матрицу, обратную матрице коэффициентов, с помощью функции «МОБР»;
4. умножить обратную матрицу на вектор-столбец свободных членов с помощью функции «МУМНОЖ».

Всё! Всем удачи!

Ссылки

[1] Демидович Б.П., Марон И.А. Основы вычислительной математики. М: «Наука», 1966, 664 с.