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

Решение системы линейных уравнений методом Гаусса в 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: Показать ссылку

Комментарии на: "Решение системы линейных уравнений методом Гаусса в MS Excel" (35)

  1. Анатолий said:

    не корректно работает в 2007 версии

    • Инженер said:

      Что именно работает некорректно? Постараюсь глянуть в 2007 (если найду установленный) а также в 2010, если укажете где проблема (желательно прислать ссылку на заполненный и неверно работающий файл).

    • Анатолий said:

      Проблема на 5 этапе с формулой, учитывая её одинаковость с формулой из 4 этапа, возникает сомнение в её корректности. (19 строчка получается по значению одинакова с 18)

      • Инженер said:

        Спасибо что отписались о найденной ошибке. Ошибка действительно имела место, я, видимо, при создании этого руководства просто скопировал пункт 4, забыв изменить при этом его содержимое. Сейчас ошибку исправил, все работает.

  2. Анатолий said:

    Большое спасибо!

  3. Максим said:

    Ни как не могу сделать! может кт на почту скинуть **********_****@mail.ru
    Спасибо!

    • Инженер said:

      Что именно не получается? Прорешайте систему из примера — посмотрите, как нужно делать и по аналогии решите Ваше задание.

  4. Евгений said:

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

    • Инженер said:

      Странно, при беглом осмотре формула кажется правильной. В ближайшее время прорешаю еще раз и прикреплю решенный пример в xls файле к инструкции.

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

  5. Евгений said:

    Странно, матрица в вашем примере решена правильно
    моя матрицы состоит из десятичных дробей:
    0,63 -0,76 1,34 0,37 1,21

    0,54 0,83 -0,74 -1,27 0,86

    0,24 -0,44 0,35 0,55 0,25

    0,43 -1,21 2,32 -1,41 1,55
    получившиеся ответы:
    -0,35968
    0,417813
    0,545817
    -0,14788

    ответы, которые должны получиться:
    1,351
    0,418
    0,546
    -0,148
    в общем, как я и говорил ранее, не сходится х1..
    попробовал вбить ещё одну имеющуюся у меня матрицу, состоящую из десятичных дробей, опять неправильно считает х1.

    • Инженер said:

      Понял, спасибо за комментарий, до выходных посмотрю, где ошибка и Ваш пример прикреплю в xls файле.

  6. Евгений said:

    очень вам благодарен
    жду новостей

  7. Евгений said:

    нашёл ошибку, ваша программа работает корректно
    настолько глупая, что даже говорить не хочется
    по невнимательности
    ещё раз большое спасибо

    • Инженер said:

      Ну вот все и решилось :) Но на будущее, чтоб проблем у читателей не было все-таки придется прорешать и прикрепить файл.

      Удачи в освоении Excel!

  8. Надежда said:

    Спасибо большое, формулы очень помогли, сама бы долго разбиралась:) только есть небольшая ошибка в п.5:
    «в ячейки A19:E19 введем формулу {=A14:E14-$A$13:$E$13*(B14/$B$13)}» неверно, правильно:
    «в ячейки A19:E19 введем формулу {=A14:E14-$A$13:$E$13*(С14/$С$13)}»
    Еще раз спасибо:)

    • Инженер said:

      Надежда, огромное спасибо, действительно закралась ошибка в описание, уже исправил.

  9. ваня said:

    а как решить с з-мя неизвестными

    • Инженер said:

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

  10. У меня вот такой вопрос в моей матрице имеется 0, когда вычитаю по формулам у меня пишет на 0 делить нельзя. Как мне всё это посчитать правильно в эксель.
    Помогите пожалуйста
    0 1 -13 4 -5
    А=1 0 -2 3 b= -4
    3 21 0 -5 2
    4 3 -5 0 3

    • Спасибо, уже сообразила что нужно сделать))

    • Инженер said:

      Просто поменяйте местами строки, чтоб на первом месте в первой строке был не ноль.

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

    • Инженер said:

      Конечно, можно, ведь есть встроенный VisualBasic for Application, но, мне кажется, для такой ситуации, особенно когда придется считать много разных систем проще сделать программу на Pascal/Delphi или C++, которая будет заниматься решением системы уравнения методом Гаусса с выбором главного элемента.

      • Проблема в том, что это нужно выполнить именно в Excel :(

        • Инженер said:

          Ну я же написал, что в принципе это сделать можно, но не совсем удобно. Копать Вам нужно в сторону Visual Basic for Application, обычными формулами это будет сделать сложно, придется писать программу.

  12. Благодарю.

  13. Спасибо огромное за такую помощь!!! Дай Бог Вам здоровья!!! :)

  14. Спасибо за алгоритм.
    Почему у вас в скриншотах свободный член в ячейках E7, E8, E9, и E12 не изменился по сравнению с теми свободными членами, которые в условии, а в ячейках E13, E14 уже меняется?

    • Инженер said:

      Почему не меняется? Видимо, когда делал скриншоты что-то пошло не так :) Конечно, они также должны меняться, ведь вся строка делится на коэффициент. Меня больше удивило то, что за два года Вы первый нашли эту ошибку. Спасибо, сделаю правильно и исправлю скриншоты.

    • Инженер said:

      Все перепроверил, все-таки решено было правильно. Файл с решением системы линейных уравнений методом Гаусса в Excel прикрепил в конце статьи.

      Не изменялись свободные члены после первого этапа потому что свободный член в первой строке равен нулю, а мы эту первую строку несколько раз отнимали от второй, третьей и четвертой. А, как известно, сколько раз ноль не отнимай — результат не изменится.

      Скриншоты не пришлось переделывать — уже проще :) А файлом можно пользоваться. У кого система линейных уравнений с четырьмя неизвестными практически ничего не придется делать.

  15. Точно, это я не досмотрел, что там в первом уравнении ноль в качестве свободного члена. Извиняюсь. Спасибо за вашу работу.

  16. Здравствуйте! Помогите пожалуйста разобраться. Никогда проблнм небыло в Гауссе, а тут матрица с 0 попалась и не получается решить( не считается коэффиуиент, на 0 не делиться.
    Матрица 4 1 0 =2
    0-1-1=5
    1 0-1 =10
    Когда находим коэффициент для строки 2 например, -а11/а21, то есть первую строку делим на вторую
    то есть во второй строке 0 я делю 4 коэффициент не считается или равен нулю, следовательно не занулится первый элемент. И матрица вся не правильная будет. В общем как считать то с этим 0?

    • Инженер said:

      В пункте 1 я уже писал как нужно делать: Если в ячейке A1 находится 0, необходимо поменять строки местами так, чтоб в этой ячейке было отличное от ноля значение.
      Также и в Вашем случае — можно поменять местами 2-ю и 3-ю строки, а можно на первом этапе не делить на коэффициент вторую строку — она ведь уже имеет «правильный» вид. Просто находите коэффициент для 3-ей строки а 2-ю не трогайте на первом этапе — она уже «занулена» :) .

      • Спасибо Вам за оперативный ответ)
        А вы случайно В Access не разбираетесь? Не получается выполнить задание: Создать форму для поиска по цене (Форма звязанная с запросом) В поле вводим цену, при нажатии формируется запрос, который выводит все записи не более указанной цены. Даны две таблицы Производители программного обеспечения и программное обеспечение. Как правильно задать условие отбора чтобы выводились се записи не более указанной цены? Если я выбираю в поле со списком цену, то он и формирует запрос только по этой цене.

  17. Круто, все понятно. по коментам и по решению. очень хорошо все объясняется, спасибо))))