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

Методы решения систем линейных алгебраических уравнений хорошо описаны в учебнике "Основы вычислительной математики. Демидович Б.П., Марон И.А. 1966". Скачать - 11Мб

1. Метод обратной матрицы (решение в Excel)

Если дано уравнение:
A*X = B, где A - квадратная матрица, X,B - вектора;
причем B - известный вектор (т е столбец чисел), X - неизвестный вектор,
то решение X можно записать в виде:
X = A^-1*B, где A^-1 - обратная от А матрица.
В MS Excel обратная матрица вычисляется функцией МОБР(), а перемножаются матрицы (или матрица на вектор) - функцией МУМНОЖ().

Имеются "тонкости" использования этих матричных действий в Excel. Так, чтобы вычислить обратную матрицу от матрицы А, нужно:

1. Мышкой выделить квадратную область клеток, где будет размещена
   обратная матрица.
2. Начать вписывать формулу =МОБР(
3. Выделить мышкой матрицу А. При этом правее скобки впишется 
   соответствующий диапазон клеток.
4. Закрыть скобку, нажать комбинацию клавиш: Ctrl-Shift-Enter
5. Должна вычислиться обратная матрица и заполнить предназначенную 
   для неё область

Чтобы умножить матрицу на вектор:

1. Мышкой выделить область клеток, где будет размещён результат
   умножения
2. Начать вписывать формулу =МУМНОЖ(
3. Выделить мышкой матрицу - первый сомножитель. При этом правее скобки впишется 
   соответствующий диапазон клеток.
4. С клавиатуры ввести разделитель ; (точка с запятой)
5. Выделить мышкой вектор- второй сомножитель. При этом правее скобки впишется 
   соответствующий диапазон клеток.
6. Закрыть скобку, нажать комбинацию клавиш: Ctrl-Shift-Enter
7. Должно вычислиться произведение и заполнить предназначенную 
   для него область

Есть и другой спососб, при котором используется кнопка построителя функции Excel.

Пример СЛАУ 4-го порядка

Скачать документ Excel, в котором этот пример решён различными методами.

2. Метод Гаусса

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

Краткое описание.

Решаю систему уравнений: A*X=B, где A - квадратная матрица n-го порядка, X,B - вектора
К матрице A справа приписываю вектор B. Получаю расширенную матрицу A
В дальнейшем A обозначает расширенную матрицу (n строк, n+1 столбец)
Aij - обозначает элемент матрицы, находящийся на i-й строке и j-м столбце
Делю 1-ю строку на A11, т е A'1j = A1j/A11 (j = 1..n+1). В результате A'11 = 1. A' обозначает преобразованную строку
Преобразую остальные строки по формуле: A'ij = Aij - A'1j*Ai1 (i = 2..n; j = 1..n+1)
В результате 1-й столбец в строках 2..n заполнится нулями
Отметим, что все эти преобразования не нарушают правильность уравнений
Аналогичные действия проводим для обнуления 2-го столбца в строках 3..n, то есть:
Делю 2-ю строку на A'22, т е A''2j = A'2j/A'22 (j = 2..n+1). В результате A''22 = 1. A'' обозначает резельтат 2-го преобразования строки
Преобразую остальные строки по формуле: A''ij = A'ij - A''2j*A'i2 (i = 3..n; j = 2..n+1)
В результате 2-й столбец в строках 3..n заполнится нулями
Аналогичные действия проводим далее
В результате левые n столбцов матрицы A превращаютс в верхнюю треугольную матрицу, т е ниже главной диагонали находятся только нули (а на главной диагонали - единицы) - см Рис 1. На этом рисунке вектор B - слева, S - номер шага

Затем выполняется "обратный ход", начиная с нижней строки, из которой можно вычислить Xn = Bn/Ann, например: Х4 = 9,55741/68,6388 = 0,13924 (рис. 1)
Затем можно вычислить X3 = (0,9065 - 2,40919*0,13924) = 0,57059
Затем из второй строки: X2 + 2,83562*X3 + 8,17808*X4 = 2,47945 вычисляю X2, и т д

3. Метод Якоби (метод простых итераций)

Для применения метода Якоби (и метода Зейделя) необходимо, чтобы диагональные компоненты матрицы А были больше суммы остальных компонент той же строки. Заданная система не обладает таким свойством, поэтому выполняю предварительные преобразования.

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

(1)’ = (1) + 0,43*(2) - 0,18*(3) – 0,96*(4)   
(2)’ = (2) + 0,28*(1) – 1,73*(3) + 0,12*(4) 
(3)’ = (3) – 0,27*(1) - 0,75*(2) + 0,08*(4) 
(4)’ = (4) + 0,04*(1) – 6,50*(2) + 8,04*(3)  

Примечание: подбор коэффицентов выполнен на листе "Анализ". 
Решаются системы уравнений, цель которых - обратить внедиагональные
элементы в нуль. Коэффиценты - это округлённые результаты решения
таких систем уравнений. Конечно, это не дело.

В результате получаю систему уравнений:

Для применения метода Якоби систему уравнений нужно преобразовать к виду:
X = B2 + A2*X Преобразую:

Далее делю каждую строку на множитель левого столбца, то есть на 16, 7, 3, 70 соответственно. Тогда матрица А2 имеет вид :

А вектор В2:

Скачать