Методическая разработка "Применение элементарных вычислительных возможностей табличного процессора Excel к решению систем линейных алгебраических уравнений n-го порядка"

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

Общая формулировка задачи:

Решить систему n уравнений с n неизвестными x₁, x₂, …, x_n:

                                  (1)

Здесь {x_i} – неизвестные, их n штук. Чтобы не использовать для разных неизвестных разные буквы (например, x, y, z, …), используют одну и ту же букву с индексом – x₁, x₂, x₃ и т.д.

{a_ij} – коэффициенты (числа, множители) при неизвестных x в каждом уравнении, их n² штук (n строк, n коэффициентов в каждой), все они могут быть произвольными (разными) числами. Два нижних индекса используют для удобства: первый индекс обозначает номер строки, второй – номер неизвестного, при котором стоит этот коэффициент. Например, число a₂₃ является множителем для x₃ во втором уравнении.

N (любых, произвольных) чисел {b_i} – это числа, стоящие в правой части каждого уравнения.

Таким образом, частным случаем общего представления (1) является, например, система 4x4 (4 уравнения, 4 неизвестных x₁, x₂, x₃ и x₄):

                                                      (2)

Разумеется, некоторые коэффициенты при x могут быть равны 0 или 1; в таком случае система может иметь примерно такой вид:

                                                               (3)

В этом примере отсутствие некоторых неизвестных в уравнениях говорит о том, что соответствующие числовые коэффициенты при "отсутствующих" "иксах" равны 0 (в частности, коэффициент при x₄ в первом уравнении равен нулю: a₁₄ = 0).

Таким образом, квадратная таблица чисел с коэффициентами {a_ij} и столбец правых частей однозначно определяют систему линейных уравнений. Иными словами, для краткости систему уравнений можно записать и "без иксов". Например, систему (3) можно представить таким образом:

                      1               -3                1                0                                  1

                      0                2                0                1                                 -1                      (3*)

                   71                1              44               -1                                  0

                                                                  0                0                1               -1                                10

Для решения подобной системы вполне достаточно манипуляций с числовыми коэффициентами и со столбцом правых частей. 

Математическая теория ("метод Гаусса") гласит, что решение системы линейных уравнений не изменяется, если к одному (любому) уравнению прибавить (любое) другое уравнение, умноженное на отличное от нуля число. Воспользуемся этим, чтобы решить систему уравнений 5x5 (5 уравнений,

5неизвестных), приведённую в файле "01 - Исходная система уравнений.xlsx"

ЗАДАЧА: Решить систему уравнений

9	102	-20	17	91		-306
8	95	-18	17	85		-284
-2 -5                                                       -5	17 -83 -9	-4 16 1	-1 -13 -6	6 -69 -19		-12 224 74	(4)

Поочерёдно к каждой строке, начиная со второй, будем прибавлять первую, умноженную на некоторое число так, чтобы в каждой строке коэффициент при x₁ стал равен нулю. Например, чтобы сделать нулём коэффициент a₂₁ (множитель при x₁ во второй строке, изначально равный 8), прибавим ко второй строке первую, умноженную на дробь ⁻⁸⁄9. После приведения подобных слагаемых во второй строке коэффициент при x₁ станет равным 0. Не забывайте, что умножать и прибавлять необходимо "строку к строке", т.е. и правые части в том числе. Естественно, все числа в таблице в результате наших действий изменятся (кроме первой строки, она остаётся неизменной). Однако не забывайте, что решение системы (т.е. 5 неизвестных, которые нам надо найти) при этом не изменится.

Для каждой строки (начиная со второй) подберём свой множитель, "зануляющий" её коэффициент при x₁ (на который будем умножать первую строку перед прибавлением). Он легко рассчитывается по формуле, см. файл "02 - Множитель для первой строки.xlsx".

После чего выполним непосредственно сложение: к каждой строке, начиная со второй, прибавляем первую, умноженную на свой коэффициент (предварительно посчитанный в отдельном столбце для удобства), см. файл "03 - Прибавляем первую строку.xlsx". Обратите внимание, что первая строка системы остаётся неизменной:

 

Обратите внимание на расстановку знаков "$" в формулах. Это сделано для того, чтобы формулу из любой ячейки можно было копировать в соседние, в том числе и в другие строки. Исключение составляют формулы в первой строке (как вы помните, первая строка остаётся неизменной).

В результате все коэффициенты при x₁ стали равны 0 во всех строках, начиная со второй. Поступим аналогичным образом, прибавляя вторую строку ко всем низлежащим, умножив её предварительно на определённый множитель (см. файлы "04 - Множитель для второй строки.xlsx" и "05 - Прибавляем вторую строку.xlsx"):

 

Как видим, в 3-й, 4-й и 5-й строках первые два коэффициента стали нулями. Все остальные числа в этих строках, в том числе и правые части, изменились – но это сейчас не важно, так как решение этой системы – то же самое, что и решение исходной системы (4).

Поступая аналогичным образом, добьёмся того, чтобы наша система уравнений стала "треугольной", т.е. под диагональю все коэффициенты a_ij стали нулями (см. файл "06 - Треугольная таблица.xlsx"):

   (5)

Не забывайте, что эти числа – не что иное, как коэффициенты (числовые множители) при неизвестных x_i в каждой из строк (а каждая строка – это одно из уравнений системы). Отдельный столбец справа – правая часть уравнения.

 Главный смысл проделанных нами преобразований состоит в том, что решение полученной системы уравнений – то же самое, что и решение исходной системы уравнений (4). Таким образом, решив полученную систему

(5), мы получим решение поставленной задачи.

 Прежде всего обратите внимание, что в последнем уравнении остался только один ненулевой коэффициент – тот, что при x₅: последнее уравнение имеет вид

0.045455∙𝑥₅ = −0.36364 Следовательно, мы легко можем найти x₅: 

 

или x₅ = – 8

Подставив x₅ в предыдущее (четвёртое) уравнение, найдём x₄, зная x₄ и x₅ и подставив их в третье уравнение, найдём x₃ и т.д. Двигаясь "Снизу вверх", можно последовательно вычислить все неизвестные x₂ и x₁. Таким образом, система будет решена. В файле "07 - Вычисление неизвестных X.xlsx" приведены возможные формулы для получения ответа (не забудьте, что для вычисления каждой неизвестной все вычисленные ранее x, умноженные на соответствующие коэффициенты, необходимо перенести в правую часть). Для наглядности очерёдности нахождения неизвестных строки с расчётом формул приведены в обратном порядке.

 

Последнее, что осталось сделать – проверить найденное решение, т.е. подставить найденные x1, x2, x3, x4, x5 в исходную систему (4). В файле "08 - Проверка решения.xlsx" рядом с исходной системой рассчитывается столбец правых частей по известным значениям x₁, x₂, x₃, x₄, x₅. Тот факт, что рассчитанные значения совпали со столбцом правых частей в исходной системе, означает, что задача решена верно.

 

ВЫВОД:

Таким образом, элементарные вычислительные возможности Excel позволяют решать системы линейных уравнений, в том числе – и с бОльшим количеством уравнений и неизвестных (главное условие для решения подобной системы – количество уравнений и неизвестных должно совпадать, но это – математическая теория, в данной публикации она не рассматривается). Данный способ решения (с использованием формул на рабочем листе Excel) не лишён недостатков, в частности, совсем неочевидно, как действовать, если в какой-то момент в ячейке, на которую производится деление, окажется ноль (деление используется для расчёта коэффициента, на которую умножается строка перед прибавлением её к другим, на листе эти формулы – в столбце "B"). Также решение этим методом невозможно, если после очередного прибавления строки к строке все числа становятся нулями.

Но тем не менее, если решение системы линейных уравнений существует, то оно может быть получено при помощи этого способа.

Краткое описание документа:

В данной разработке на практическом примере предлагается способ решения систем линейных алгебраических уравнений при помощи табличного процессора Excel. Используя элементарные формулы рабочего листа, можно получить решение системы из n уравнений с n неизвестными. Используется метод Гауссовых преобразований