Как решить систему линейных уравнений методом гаусса в excel

Как решить систему линейных уравнений методом гаусса в excel

БлогNot. Преобразование Жордана-Гаусса и симплекс-метод в Excel

Преобразование Жордана-Гаусса и симплекс-метод в Excel

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

Метод базируется на элементарных преобразованиях (переводящих систему в эквивалентную), к которым относятся:

  • прибавление к обеим частям уравнения системы другого уравнения той же системы, умноженного на число, отличное от нуля;
  • перестановка местами уравнений в системе;
  • удаление из системы уравнений вида 0 = 0.

В отличие от метода Гаусса, на каждом шаге одна переменная исключается из всех уравнений, кроме одного.

Шаг метода состоит в следующем:

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

Алгоритмизировать это можно так:

Для СЛАУ в матричном виде A*x=b (матрица A размерности m*n , совсем необязательно квадратная) составляется следующая таблица:

В таблице выбран разрешающий элемент ar,s≠0 , тогда r — разрешающая строка, s — разрешающий столбец.

Переход к следующей таблице выполняется по правилам:

1. вычисляются элементы разрешающей строки: a’r,j=ar,j/ar,s — то есть, r-строка таблицы делится на разрешающий элемент;

2. все элементы разрешающего столбца, кроме ar,s, равного единице, становятся равны нулю;

3. элементы вне разрешающих строки и столбца вычисляются по формуле, изображённой ниже:

Легко не запутаться, если увидеть, что числитель этой формулы похож на вычисление определителя матрицы 2 на 2.

4. При ручном расчёте значение в последнем контрольном столбце сравнивается с суммой предыдущих элементов строки. Если значения не совпадают, ошибки надо искать в данной строке. При автоматизированном расчёте контрольный столбец можно опустить.

Возможны следующие случаи:

1. В процессе исключений левая часть уравнения системы обращается в 0, а правая b≠0 , тогда система не имеет решения.

2. Получается тождество 0 = 0 — уравнение является линейной комбинацией остальных и строка нулей может быть вычеркнута из системы.

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

Запрограммируем метод в Excel одной формулой, изменять которую должно быть не слишком трудоёмко. Например, для решения СЛАУ

заполним коэффициентами системы ячейки листа от A1 до D4 включительно, выберем разрешающий элемент a1,1=1 , а первый шаг метода сделаем в ячейке A6 , куда загоним "универсальную" формулу для преобразования Жордана-Гаусса:

=ЕСЛИ(СТРОКА($A$1)=СТРОКА(A1);A1/$A$1;
ЕСЛИ(СТОЛБЕЦ($A$1)=СТОЛБЕЦ(A1);0;(A1*$A$1-
ДВССЫЛ(АДРЕС(СТРОКА(A1);СТОЛБЕЦ($A$1)))*
ДВССЫЛ(АДРЕС(СТРОКА($A$1);СТОЛБЕЦ(A1))))/$A$1))

Здесь ссылка $A$1 показывает разрешающий элемент, а ссылка на текущий элемент не закреплена. Остаётся только растянуть формулу на ячейки A6:D9 :

На следующем шаге разрешающим элементом может быть, например, a2,2=1 (ячейка B7 ). Нам останется скопировать формулу из A6 в A11 (по пустой строке оставляем, чтоб визуально разделить шаги метода), войти в режим редактирования формулы (двойной щелчок по ячейке или выбрать её и нажать клавишу F2 ) и поправить (аккуратно перетащить мышкой за границу) все закреплённые ссылки с ячейки A1 на B7 .

Конечно, можно заменить везде в формуле закреплённую ссылку $A$1 на конструкцию вида ДВССЫЛ(ЯЧЕЙКА) , образующую динамический адрес ссылки. Скажем, ДВССЫЛ(F8) , а в ячейке F8 будет автоматически формироваться адрес ячейки разрешающего элемента по заданным пользователем номеру строки и столбца. Тогда для этих номеров строки и столбца придётся предусмотреть отдельные ячейки, например, так:

Увы, всё это ничего не даст — вместо $A$1 мы просто вынуждены будем закрепить в формуле ДВССЫЛ($F$8) и всё равно потом перетаскивать столько же ссылок при копировании формулы. Кроме того, "вручную" введённые номера строки и столбца придётся ещё и проверять на допустимость (хотя бы как на рисунке), так что, не будем умножать сущностей.

Посмотреть метод в работе можно на двух первых листах приложенного файла Excel (2 разных примера).

На преобразовании Жордана-Гаусса основан и такой универсальный метод решения линейных задач оптимизации, как симплекс-метод. Описания его обычно страшны, длинны и перегружены теоремами. Попробуем сделать простое описание и разработать пригодный для расчёта в Excel алгоритм. На самом деле, симплекс-метод уже встроен в стандартную надстройку Пакет анализа, и программировать его "вручную" не нужно, так что наш код имеет, скорее, учебную ценность.

Сначала минимум теории.

Если вектор-столбцы СЛАУ линейно независимы, соответствующие им переменные являются базисными, а остальные – свободными. Например, в СЛАУ

переменные x2 и x4 — базисные, а x1 и x3 — свободные. Базисные переменные между собой независимы, а свободные можно сделать, например, нулями и получить < x2=2, x4=1 > – базисное решение системы.

Выбирая различные разрешающие элементы, можно получить решения СЛАУ с различными базисами. Любое неотрицательное базисное решение СЛАУ называется опорным.

Симплекс-метод обеспечивает переход от одного опорного решения к другому, пока не будет достигнуто оптимальное решение, дающее минимум целевой функции.

Алгоритм симплекс-метода состоит в следующем:

1. Задача ЛП преобразуется к каноническому виду:

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

добавляются дополнительные балансовые переменные, число которых соответствует числу ограничений-неравенств m (ограничения на неотрицательность значений неизвестных не учитываются). После этого неравенства со знаком " ≤ " превращаются в равенства, например, система ограничений вида

То есть, "экономический" смысл балансовых переменных очень прост – это "остатки" неиспользованных ресурсов каждого вида.

Если в исходной задаче искался не минимум, а максимум, целевая функция Z заменятся на Z1 = -Z . Решения задач совпадают, при этом min Z = — max Z1 . Например, цель

переписывается в виде

Если в исходной задаче были уравнения-неравенства со знаками " ≥ " вместо " ≤ ", обе части каждого такого неравенства умножаются на -1 , а знак неравенства меняется на противоположный, например,

Канонический вид модели получен, для него выписывается симплекс-таблица:

В левом столбце записываются базисные переменные (БП), если они ещё не выделены – пусто.

2. С помощью шагов Жордана–Гаусса ищется первоначальный опорный план, т.е. СЛАУ приводится к базисному виду с неотрицательными свободными членами bi>0 . При этом целевая функция Z должна быть выражена только через свободные неизвестные (нулевые коэффициенты в Z-строке стоят только под переменными xi , которые есть в базисе). При выборе разрешающего элемента ar,s в строку r столбца БП выписываем переменную xs , если там уже была переменная – вычеркиваем её (выводим из базиса).

3. Выписываем под столбцами xi опорный план X * : под свободными переменными — нули, под базисными – соответствующие базисной переменной коэффициенты из столбца b .

Ниже выписываем вектор R по правилу: под базисными переменными – нули, под свободными Ri=Zi .

Если все Ri≥0 , найдено оптимальное решение X * и значение цели Zmin = -q , иначе нужен новый план, а у вас он есть, товарищ Жюков? (п. 4).

4. Для выбора разрешающего столбца s выбираем максимальную по модулю отрицательную компоненту вектора R , разрешающий столбец s выбран. Затем анализируем коэффициенты s-го столбца матрицы системы ограничений. Если все ai,s≤0 , решения нет и Zmin стремится к минус бесконечности, иначе переходим к п.5.

5. Для выбора разрешающей строки r составляем неотрицательные отношения bi/Ai,s≥0 , i=1,2. m , и выбираем среди них наименьшее. Если минимум достигается для нескольких строк, за разрешающую можно принять любую из них, при этом, в новом опорном плане значения некоторых базисных переменных станут равными 0, т.е., получаем вырожденный опорный план.

6. Выполняем преобразование Жордана-Гаусса с разрешающим элементом ar,s и переходим к п.3

Геометрически симплекс-методу соответствует кратчайший обход вершин n-мерного выпуклого многогранника, образующего область допустимых решений задачи:

Здесь мы перешли от опорного плана C , представляющего собой одну из вершин многомерного многоугольника, к оптимальному плану E=X * .

Запрограммировать это всё в Excel нелегко, но можно. В прилагаемом документе приведены 3 примера, реализующие решение задач симплекс-методом. Правда, при выполнени шага менять уже придётся 3 формулы, на листе первого примера на симплекс-метод они выделены жёлтым цветом: расчёт отношений для выбора разрешающей строки в ячейке I2 , заполнение столбца БП в ячейке A12 , шаг преобразования Жордана-Гаусса в ячейке B12 . Как и в примере на преобразование Жордана-Гаусса, изменение формул связано только с необходимостью сослаться на новую строку, содержащую адрес ячейки с разрешающим элементом (для первого шага — ячейка C9 ).

23.03.2014, 08:18; рейтинг: 31897

Блог инженера-программиста / шапку скоро поменяю /

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

Автор Инженер

На днях понадобилось найти корни системы линейных уравнений методом Гаусса в Microsoft Excel. Готовый алгоритм решения можно найти в книге Гарнаева «Использование Excel и VBA в экономике и финансах», но объяснение там очень скудное и не совсем понятное. Постараюсь описать подробней для тех, кому может понадобиться этот алгоритм.

Лирическое отступление: в тексте будет предлагаться ввести в диапазон ячеек формулу вида: <=A1:B3+$C$2:$C$3>и т.п., это так-называемые «формулы массива» (формула, выполняющая несколько вычислений над одним или несколькими наборами значений, а затем возвращающая один или несколько результатов. Формулы массива заключены в фигурные скобки < >). Microsoft Excel автоматически заключает ее в фигурные скобки ( < >). Для введения такого типа формул необходимо выделить весь диапазон, куда нужно вставить формулу, в первой ячейке ввести формулу без фигурных скобок (для примера выше — =A1:B3+$C$2:$C$3 ) и нажать Ctrl+Shift+Enter .

Система линейных уравнений

Пускай имеем систему линейных уравнений:

1. Запишем коэффициенты системы уравнений в ячейки A1:D4 а столбец свободных членов в ячейки E1:E4 . Если в ячейке A1 находится 0, необходимо поменять строки местами так, чтоб в этой ячейке было отличное от ноля значение. Для большей наглядности можно добавить заливку ячеек, в которых находятся свободные члены. (скриншот)

2. Необходимо коэффициент при x1 во всех уравнениях кроме первого привести к 0. Для начала сделаем это для второго уравнения. Скопируем первую строку в ячейки A6:E6 без изменений, в ячейки A7:E7 необходимо ввести формулу: <=A2:E2-$A$1:$E$1*(A2/$A$1)>. Таким образом мы от второй строки отнимаем первую, умноженную на A2/$A$1, т.е. отношение первых коэффициентов второго и первого уравнения. Для удобства заполнения строк 8 и 9 ссылки на ячейки первой строки необходимо использовать абсолютные (используем символ $). (скриншот)

3. Копируем введенную формулу формулу в строки 8 и 9, таким образом избавляемся от коэффициентов перед x1 во всех уравнениях кроме первого. (скриншот)

4. Теперь приведем коэффициенты перед x2 в третьем и четвертом уравнении к 0. Для этого скопируем полученные 6-ю и 7-ю строки (только значения) в строки 11 и 12, а в ячейки A13:E13 введем формулу <=A8:E8-$A$7:$E$7*(B8/$B$7)>, которую затем скопируем в ячейки A14:E14 . Таким образом реализуется разность строк 8 и 7, умноженных на коэффициент B8/$B$7 . Не забываем проводить перестановку строк, чтоб избавиться от 0 в знаменателе дроби. (скриншот)

5. Осталось привести коэффициент при x3 в четвертом уравнении к 0, для этого вновь проделаем аналогичные действия: скопируем полученные 11, 12 и 13-ю строки (только значения) в строки 16-18, а в ячейки A19:E19 введем формулу <=A14:E14-$A$13:$E$13*(C14/$C$13)>. Таким образом реализуется разность строк 14 и 13, умноженных на коэффициент C14/$C$13 . Не забываем проводить перестановку строк, чтоб избавиться от 0 в знаменателе дроби. (скриншот)

6. Прямая прогонка методом Гаусса завершена. Обратную прогонку начнем с последней строки полученной матрицы. Необходимо все элементы последней строки разделить на коэффициент при x4. Для этого в строку 24 введем формулу <=A19:E19/D19>. (скриншот)

7. Приведем все строки к подобному виду, для этого заполним строки 23, 22, 21 следующими формулами:
23: <=(A18:E18-A24:E24*D18)/C18>— отнимаем от третьей строки четвертую умноженную на коэффициент при x4 третьей строки.
22: <=(A17:E17-A23:E23*C17-A24:E24*D17)/B17>— от второй строки отнимаем третью и четвертую, умноженные на соответствующие коэффициенты.
21: <=(A16:E16-A22:E22*B16-A23:E23*C16-A24:E24*D16)/A16>— от первой строки отнимаем вторую, третью и четвертую, умноженные на соответствующие коэффициенты.
Результат (корни уравнения) вычислены в ячейках E21:E24 . (скриншот)

UPDATE от 25 апреля 2012 г. Выкладываю xls-файл с решением линейных уравнений методом Гаусса в Microsoft Excel: Показать ссылку

Ссылка на основную публикацию