На днях понадобилось найти корни системы линейных уравнений методом Гаусса в 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:
Показать ссылку
не корректно работает в 2007 версии
Что именно работает некорректно? Постараюсь глянуть в 2007 (если найду установленный) а также в 2010, если укажете где проблема (желательно прислать ссылку на заполненный и неверно работающий файл).
Проблема на 5 этапе с формулой, учитывая её одинаковость с формулой из 4 этапа, возникает сомнение в её корректности. (19 строчка получается по значению одинакова с 18)
Спасибо что отписались о найденной ошибке. Ошибка действительно имела место, я, видимо, при создании этого руководства просто скопировал пункт 4, забыв изменить при этом его содержимое. Сейчас ошибку исправил, все работает.
Большое спасибо!
Ни как не могу сделать! может кт на почту скинуть **********_****@mail.ru
Спасибо!
Что именно не получается? Прорешайте систему из примера — посмотрите, как нужно делать и по аналогии решите Ваше задание.
большое спасибо. всё очень доступно и понятно
единственный вопрос. похоже что х1 (элемент, стоящий в ячейке Е21) вычисляется неправильно. уже несколько матриц забивал, всё вычисляется верно, кроме первого неизвестного
Странно, при беглом осмотре формула кажется правильной. В ближайшее время прорешаю еще раз и прикреплю решенный пример в xls файле к инструкции.
Если есть возможность, напишите, какая матрица забивалась, какие ответы получили, выполнив все действия по инструкции, и какие ответы должны были получиться.
Странно, матрица в вашем примере решена правильно
моя матрицы состоит из десятичных дробей:
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.
Понял, спасибо за комментарий, до выходных посмотрю, где ошибка и Ваш пример прикреплю в xls файле.
очень вам благодарен
жду новостей
нашёл ошибку, ваша программа работает корректно
настолько глупая, что даже говорить не хочется
по невнимательности
ещё раз большое спасибо
Ну вот все и решилось
Но на будущее, чтоб проблем у читателей не было все-таки придется прорешать и прикрепить файл.
Удачи в освоении Excel!
Спасибо большое, формулы очень помогли, сама бы долго разбиралась:) только есть небольшая ошибка в п.5:
«в ячейки A19:E19 введем формулу {=A14:E14-$A$13:$E$13*(B14/$B$13)}» неверно, правильно:
«в ячейки A19:E19 введем формулу {=A14:E14-$A$13:$E$13*(С14/$С$13)}»
Еще раз спасибо:)
Надежда, огромное спасибо, действительно закралась ошибка в описание, уже исправил.
а как решить с з-мя неизвестными
Ваше сообщение потерялось в спаме
Восстановил — отвечаю: решение для трех неизвестных точно такое же как и для четырех, немного будут отличаться формулы, но основные действия будут те же — приводим матрицу к ступенчатому виду, после — к диагональной матрице.
У меня вот такой вопрос в моей матрице имеется 0, когда вычитаю по формулам у меня пишет на 0 делить нельзя. Как мне всё это посчитать правильно в эксель.
Помогите пожалуйста
0 1 -13 4 -5
А=1 0 -2 3 b= -4
3 21 0 -5 2
4 3 -5 0 3
Спасибо, уже сообразила что нужно сделать))
Просто поменяйте местами строки, чтоб на первом месте в первой строке был не ноль.
Спасибо за замечательный гайд.
У меня есть вопрос. В моем задании коэффициенты системы получены с помощью генератора случайных чисел, разумеется при расчетах могут возникать ошибки и такую систему нужно решать методом Гаусса, но с выбором главного элемента.
Возможно ли запрограммировать строки на поиск наибольшего по модулю коэффициента и перемещении столбца с этим элементом на диагональ?
Конечно, можно, ведь есть встроенный VisualBasic for Application, но, мне кажется, для такой ситуации, особенно когда придется считать много разных систем проще сделать программу на Pascal/Delphi или C++, которая будет заниматься решением системы уравнения методом Гаусса с выбором главного элемента.
Проблема в том, что это нужно выполнить именно в Excel
Ну я же написал, что в принципе это сделать можно, но не совсем удобно. Копать Вам нужно в сторону Visual Basic for Application, обычными формулами это будет сделать сложно, придется писать программу.
Благодарю.
Спасибо огромное за такую помощь!!! Дай Бог Вам здоровья!!!
Спасибо за алгоритм.
Почему у вас в скриншотах свободный член в ячейках E7, E8, E9, и E12 не изменился по сравнению с теми свободными членами, которые в условии, а в ячейках E13, E14 уже меняется?
Почему не меняется? Видимо, когда делал скриншоты что-то пошло не так
Конечно, они также должны меняться, ведь вся строка делится на коэффициент. Меня больше удивило то, что за два года Вы первый нашли эту ошибку. Спасибо, сделаю правильно и исправлю скриншоты.
Все перепроверил, все-таки решено было правильно. Файл с решением системы линейных уравнений методом Гаусса в Excel прикрепил в конце статьи.
Не изменялись свободные члены после первого этапа потому что свободный член в первой строке равен нулю, а мы эту первую строку несколько раз отнимали от второй, третьей и четвертой. А, как известно, сколько раз ноль не отнимай — результат не изменится.
Скриншоты не пришлось переделывать — уже проще
А файлом можно пользоваться. У кого система линейных уравнений с четырьмя неизвестными практически ничего не придется делать.
Точно, это я не досмотрел, что там в первом уравнении ноль в качестве свободного члена. Извиняюсь. Спасибо за вашу работу.
Здравствуйте! Помогите пожалуйста разобраться. Никогда проблнм небыло в Гауссе, а тут матрица с 0 попалась и не получается решить( не считается коэффиуиент, на 0 не делиться.
Матрица 4 1 0 =2
0-1-1=5
1 0-1 =10
Когда находим коэффициент для строки 2 например, -а11/а21, то есть первую строку делим на вторую
то есть во второй строке 0 я делю 4 коэффициент не считается или равен нулю, следовательно не занулится первый элемент. И матрица вся не правильная будет. В общем как считать то с этим 0?
В пункте 1 я уже писал как нужно делать: Если в ячейке A1 находится 0, необходимо поменять строки местами так, чтоб в этой ячейке было отличное от ноля значение.
.
Также и в Вашем случае — можно поменять местами 2-ю и 3-ю строки, а можно на первом этапе не делить на коэффициент вторую строку — она ведь уже имеет «правильный» вид. Просто находите коэффициент для 3-ей строки а 2-ю не трогайте на первом этапе — она уже «занулена»
Спасибо Вам за оперативный ответ)
А вы случайно В Access не разбираетесь? Не получается выполнить задание: Создать форму для поиска по цене (Форма звязанная с запросом) В поле вводим цену, при нажатии формируется запрос, который выводит все записи не более указанной цены. Даны две таблицы Производители программного обеспечения и программное обеспечение. Как правильно задать условие отбора чтобы выводились се записи не более указанной цены? Если я выбираю в поле со списком цену, то он и формирует запрос только по этой цене.
Круто, все понятно. по коментам и по решению. очень хорошо все объясняется, спасибо))))