Решение слау в эксель. Решение системы уравнений в Microsoft Excel. Продолжаем решать системы методом Крамера вместе
Решение систем линейных уравнений в Excel
1. Введение
Многие задачи организации строительного производства сводятся к решению систем линейных уравнений вида:
a 11x 1a 12x 2a 1n x n b 1, | |||||||||
a2 n xn | |||||||||
a 21x 1a 22x 2 |
|||||||||
n 1 1 |
называемой системой n линейных алгебраических уравнений(СЛАУ ) с n
неизвестными.
При этом произвольные числа a ij (i = 1, 2,…,n ;j = 1, 2,…,n ) называются
коэффициентами при неизвестных, а числа b i (i = 1, 2,…, n ) – свободными
членами.
Систему(1) можно записать в матричной форме
A X = B,
где A – матрица коэффициентов при неизвестных:
a2 n |
||||||
an 1 | an 1 | |||||
an 1 | an 1 |
X – вектор- столбец неизвестных X= (x1 , x2 , …, xn ) T :
B – вектор-столбец свободных членов:
b 2B ,
или B = (b 1 ,b 2 ,...,b n )T .
2. Операции с матрицами в Excel
В Excel для операций с матрицами служат функции из категории «Математические»:
1) МОПРЕД(матрица) – вычисление определителя матрицы, 2)МОБР(матрица) – вычисление обратной матрицы, 3)МУМНОЖ(матрица1;матрица2) – произведение матриц, 4)ТРАНСП(матрица) – транспонирование матрицы.
Первая из этих функций в качестве результатавозвращает число (определитель матрицы ), поэтомувводится как обычная формула (ENTER ).
Последние три возвращают блок ячеек, поэтому должны вводиться как формулы массива (CTRL+SHIFT+ENTER ).
Рассмотрим задачурешения СЛАУ на следующем примере
8x 1 2x 2 8x 3 24,
2x 1 2x 2 10x 3 48,
2x 1 4x 2 8x 3 18.
Матрица коэффициентов при неизвестных A (3) имеет вид
а вектор-столбец свободных членов (5)B = (–24, –48, 18)T .
Решим СЛАУ (7) в среде MS Excel тремя различными способами.
Матричный способ решения (обратной матрицы)
Обе части матричного равенства (2) умножим на обратную матрицу А -1 . Получим A –1 A X =A –1 B . Так как A –1 A =E , гдеE – единичная матрица (диагональная матрица, у которой по главной диагонали расположены единицы). Тогда решение системы (2) запишется в следующем виде
МУМНОЖ(матрица1;матрица2), завершая в каждом случае ввод комбинацией
CTRL+SHIFT+ENTER.
Метод Крамера
Решение СЛАУ находится по формулам Крамера
det A | ||||||
det A | ||||||
det A 2 | ||||||
det A | ||||||
det A | ||||||
det A |
где det A =A – определитель матрицы (3) системы (главный определитель), detA i =A i (i = 1, 2, …,n )– определители матрицA i (вспомогательные определители), которые получаются изA заменой i -го столбца на столбец свободных членовB (5).
Для рассматриваемой СЛАУ (7) вспомогательные матрицы имеют следующий вид
A 148 | |||||||||||||||
Разместим их на рабочем листе (рис. 1).
Аналогичная формула (=МОПРЕД(A3:C5) ) для вычисления определителя матрицыA записана в ячейкуE8 . Осталось найти решение системы. Соответствующие формулы Excel запишем в интервал решенияB7:B9 (рис. 3), в котором и увидим результат (рис. 4).
Обратите внимание на то (рис. 3), что при вычислении x i (i = 1, 2, 3)
анализируется значение определителя матрицы системы A , вычисленное в ячейке E8, и, если оно равно нулю, то в B7 помещается текст« Решения нет», а в ячейки B8 и B9 – пустые строки.
3. Решение СЛАУ с использованием инструмента Поиск решения
Широкий класс производственных задач составляют задачи оптимизации. Задачи оптимизации предполагают поиск значений аргументов, доставляющих функции, которую называют целевой , минимальное или максимальное значение при наличии каких-либо дополнительных ограничений. Excel располагает мощным средством для решения оптимизационных задач.
Это инструмент-надстройка, который называетсяПоиск решения (Solver )
(доступен через менюСервис Поиск решения ) .
Задачу решения СЛАУ можно свести к оптимизационной задаче.
Для чего одно из уравнений (например, первое) взять в качестве целевой функции, а оставшиеся n -1 рассматривать в качестве ограничений.
Запишем систему(1) в виде
a 11x 1a 12x 2a 1n x n b 10,
a2 n xn | ||||||||
a 21x 1a 22x 2 |
||||||||
b 0. |
||||||||
n 1 1 |
Для решения этой задачи необходимо записать выражения (формулы) для вычисления значений функций, стоящих слева в уравнениях системы (12). Отведем для примера под эти формулы интервал C7:C9 . В ячейкуC7 введем формулу =A3*$B$7+B3*$B$8+C3*$B$9-D3 и скопируем ее в оставшиесяC8 иC9 . В них появятся соответственно =A4*$B$7+B4*$B$8+C4*$B$9-D4 и =A5*$B$7+B5*$B$8+C5*$B$9-D5 .
В окне диалога Поиск решения (рис. 5) задать параметры поиска (установить целевую ячейкуC7 равной нулю, решение в изменяемых ячейкахB7:B9 , ограничения заданы формулами в ячейкахC8 и С9 ). После щелчка по кнопкеВыполнить в
интервале B7:B9 получим результат (рис. 6) – решение СЛАУ.
Метод Крамера применяется для решения систем линейных алгебраических уравнений (СЛАУ), в которых число неизвестных переменных равно числу уравнений и определитель основной матрицы отличен от нуля. В этой статье мы разберем как по методу Крамера находятся неизвестные переменные и получим формулы. После этого перейдем к примерам и подробно опишем решение систем линейных алгебраических уравнений методом Крамера.
Навигация по странице.
Метод Крамера - вывод формул.
Пусть нам требуется решить систему линейных уравнений вида
Где x 1 , x 2 , …, x n – неизвестные переменные, a i j , i = 1, 2, …, n, j = 1, 2, …, n – числовые коэффициенты, b 1 , b 2 , …, b n - свободные члены. Решением СЛАУ называется такой набор значений x 1 , x 2 , …, x n при которых все уравнения системы обращаются в тождества.
В матричном виде эта система может быть записана как A ⋅ X = B , где - основная матрица системы, ее элементами являются коэффициенты при неизвестных переменных, - матрица – столбец свободных членов, а - матрица – столбец неизвестных переменных. После нахождения неизвестных переменных x 1 , x 2 , …, x n , матрица становится решением системы уравнений и равенство A ⋅ X = B обращается в тождество .
Будем считать, что матрица А – невырожденная, то есть, ее определитель отличен от нуля. В этом случае система линейных алгебраических уравнений имеет единственное решение, которое может быть найдено методом Крамера. (Методы решения систем при разобраны в разделе решение систем линейных алгебраических уравнений).
Метод Крамера основывается на двух свойствах определителя матрицы:
Итак, приступим к нахождению неизвестной переменной x 1
. Для этого умножим обе части первого уравнения системы на А 1 1
, обе части второго уравнения – на А 2 1
, и так далее, обе части n-ого
уравнения – на А n 1
(то есть, уравнения системы умножаем на соответствующие алгебраические дополнения первого столбца матрицы А
):
Сложим все левые части уравнения системы, сгруппировав слагаемые при неизвестных переменных x 1 , x 2 , …, x n
, и приравняем эту сумму к сумме всех правых частей уравнений:
Если обратиться к озвученным ранее свойствам определителя, то имеем
и предыдущее равенство примет вид
откуда
Аналогично находим x 2
. Для этого умножаем обе части уравнений системы на алгебраические дополнения второго столбца матрицы А
:
Складываем все уравнения системы, группируем слагаемые при неизвестных переменных x 1 , x 2 , …, x n
и применяем свойства определителя:
Откуда
.
Аналогично находятся оставшиеся неизвестные переменные.
Если обозначить
То получаем формулы для нахождения неизвестных переменных по методу Крамера .
Замечание.
Если система линейных алгебраических уравнений однородная, то есть , то она имеет лишь тривиальное решение (при ). Действительно, при нулевых свободных членах все определители будут равны нулю, так как будут содержать столбец нулевых элементов. Следовательно, формулы дадут .
Алгоритм решения систем линейных алгебраических уравнений методом Крамера.
Запишем алгоритм решения систем линейных алгебраических уравнений методом Крамера .
Примеры решения систем линейных алгебраических уравнений методом Крамера.
Разберем решения нескольких примеров.
Пример.
Найдите решение неоднородной системы линейных алгебраических уравнений методом Крамера .
Решение.
Основная матрица системы имеет вид . Вычислим ее определитель по формуле :
Так как определитель основной матрицы системы отличен от нуля, то СЛАУ имеет единственное решение, и оно может быть найдено методом Крамера. Запишем определители и . Заменяем первый столбец основной матрицы системы на столбец свободных членов, и получаем определитель . Аналогично заменяем второй столбец основной матрицы на столбец свободных членов, и получаем .
Вычисляем эти определители:
Находим неизвестные переменные x 1
и x 2
по формулам :
Выполним проверку. Подставим полученные значения x 1
и x 2
в исходную систему уравнений:
Оба уравнения системы обращаются в тождества, следовательно, решение найдено верно.
Ответ:
.
Некоторые элементы основной матрицы СЛАУ могут быть равны нулю. В этом случае в уравнениях системы будут отсутствовать соответствующие неизвестные переменные. Разберем пример.
Пример.
Найдите решение системы линейных уравнений методом Крамера .
Решение.
Перепишем систему в виде , чтобы стало видно основную матрицу системы . Найдем ее определитель по формуле
Имеем
Определитель основной матрицы отличен от нуля, следовательно, система линейных уравнений имеет единственное решение. Найдем его методом Крамера. Вычислим определители :
Таким образом,
Ответ:
Обозначения неизвестных переменных в уравнениях системы могут отличаться от x 1 , x 2 , …, x n . Это не влияет на процесс решения. А вот порядок следования неизвестных переменных в уравнениях системы очень важен при составлении основной матрицы и необходимых определителей метода Крамера. Поясним этот момент на примере.
Пример.
Используя метод Крамера, найдите решение системы трех линейных алгебраических уравнений с тремя неизвестными .
Решение.
В данном примере неизвестные переменные имеют другое обозначение (x
, y
и z
вместо x 1
, x 2
и x 3
). Это не влияет на ход решения, но будьте внимательны с обозначениями переменных. В качестве основной матрицы системы НЕЛЬЗЯ брать . Необходимо сначала упорядочить неизвестные переменные во всех уравнениях системы. Для этого перепишем систему уравнений как . Теперь основную матрицу системы хорошо видно . Вычислим ее определитель:
Определитель основной матрицы отличен от нуля, следовательно, система уравнений имеет единственное решение. Найдем его методом Крамера. Запишем определители (обратите внимание на обозначения) и вычислим их:
Осталось найти неизвестные переменные по формулам :
Выполним проверку. Для этого умножим основную матрицу на полученное решение (при необходимости смотрите раздел ):
В результате получили столбец свободных членов исходной системы уравнений, поэтому решение найдено верно.
Ответ:
x = 0, y = -2, z = 3 .
Пример.
Решите методом Крамера систему линейных уравнений , где a и b – некоторые действительные числа.
Решение.
Ответ:
Пример.
Найдите решение системы уравнений методом Крамера, - некоторое действительное число.
Решение.
Вычислим определитель основной матрицы системы: . выражения есть интервал , поэтому при любых действительных значениях . Следовательно, система уравнений имеет единственное решение, которое может быть найдено методом Крамера. Вычисляем и :
Сначала рассмотрим решение системы линейных уравнений методом Крамера . Для этого используем уже решенный пример 8 .
В EXCEL реализована функция вычисления определителей (см. п.7). Запишем матрицу коэффициентов и матрицы, полученные из нее заменой по очереди всех столбцов на столбец свободных членов. Листинг вычислений представлен на рис. 8:
Матрицы записаны в диапазонах
А значения определителей – в ячейках . Столбец свободных членов – в G2:G6. Решение системы – в I2:I6.
Тот же пример решим с помощью обратной матрицы . В EXCEL реализованы функции для нахождения обратных матриц и перемножения матриц (см. п.7). Листинг решения представлен на рис. 9. В диапазоне записана матрица коэффициентов, в ячейках – вектор свободных членов, в диапазоне обратная матрица, в ячейках – решение системы, полеченное как результат умножения матрицы на матрицу .
Предложим еще один способ решения линейных систем в EXCELL. Возможно, для систем он не покажется эффективным, однако знакомство с ним полезно для решения задач оптимизации, в частности задач линейного программирования. Инструментом для этого метода служит процедура Поиск решения, которая находится в Надстройках. После вызова процедуры появляется окно, представленное на рис. 11.
Покажем решение системы на примере.
Пример 12. Решить систему
В ячейки введена матрица коэффициентов уравнений системы, в – коэффициенты последнего уравнения, в ячейки G3:G6 - столбец свободных членов. Ячейки B1:E1 отведем для значений неизвестных. В ячейках F3:F6 сосчитаем сумму произведений коэффициентов каждого уравнения на неизвестные (для этого воспользуемся встроенной функцией СУММПРОИЗВ). Выберем ячейку F6 в качестве целевой и вызовем процедуру Поиск решения . В окошке установим, что целевая ячейка должна быть равной свободному члену последнего уравнения, и заполним поля. В поле «изменяя ячейки» введем B1:E1. В поле «ограничения» будем вводить первые уравнения. А именно, значение в ячейке F3 должно равняться заданному значению в ячейке G3 (1-е уравнение). Аналогично добавляем два других уравнения. После заполнения всех полей нажимаем .
В программе Excel имеется обширный инструментарий для решения различных видов уравнений разными методами.
Рассмотрим на примерах некоторые варианты решений.
Решение уравнений методом подбора параметров Excel
Инструмент «Подбор параметра» применяется в ситуации, когда известен результат, но неизвестны аргументы. Excel подбирает значения до тех пор, пока вычисление не даст нужный итог.
Путь к команде: «Данные» - «Работа с данными» - «Анализ «что-если»» - «Подбор параметра».
Рассмотрим на примере решение квадратного уравнения х 2 + 3х + 2 = 0. Порядок нахождения корня средствами Excel:
Для подбора параметра программа использует циклический процесс. Чтобы изменить число итераций и погрешность, нужно зайти в параметры Excel. На вкладке «Формулы» установить предельное количество итераций, относительную погрешность. Поставить галочку «включить итеративные вычисления».
Как решить систему уравнений матричным методом в Excel
Дана система уравнений:
Получены корни уравнений.
Решение системы уравнений методом Крамера в Excel
Возьмем систему уравнений из предыдущего примера:
Для их решения методом Крамера вычислим определители матриц, полученных заменой одного столбца в матрице А на столбец-матрицу В.
Для расчета определителей используем функцию МОПРЕД. Аргумент – диапазон с соответствующей матрицей.
Рассчитаем также определитель матрицы А (массив – диапазон матрицы А).
Определитель системы больше 0 – решение можно найти по формуле Крамера (D x / |A|).
Для расчета Х 1: =U2/$U$1, где U2 – D1. Для расчета Х 2: =U3/$U$1. И т.д. Получим корни уравнений:
Решение систем уравнений методом Гаусса в Excel
Для примера возьмем простейшую систему уравнений:
3а + 2в – 5с = -1
2а – в – 3с = 13
а + 2в – с = 9
Коэффициенты запишем в матрицу А. Свободные члены – в матрицу В.
Для наглядности свободные члены выделим заливкой. Если в первой ячейке матрицы А оказался 0, нужно поменять местами строки, чтобы здесь оказалось отличное от 0 значение.
Примеры решения уравнений методом итераций в Excel
Вычисления в книге должны быть настроены следующим образом:
Делается это на вкладке «Формулы» в «Параметрах Excel». Найдем корень уравнения х – х 3 + 1 = 0 (а = 1, b = 2) методом итерации с применением циклических ссылок. Формула:
Х n+1 = X n – F (X n) / M, n = 0, 1, 2, … .
M – максимальное значение производной по модулю. Чтобы найти М, произведем вычисления:
f’ (1) = -2 * f’ (2) = -11.
Полученное значение меньше 0. Поэтому функция будет с противоположным знаком: f (х) = -х + х 3 – 1. М = 11.
В ячейку А3 введем значение: а = 1. Точность – три знака после запятой. Для расчета текущего значения х в соседнюю ячейку (В3) введем формулу: =ЕСЛИ(B3=0;A3;B3-(-B3+СТЕПЕНЬ(B3;3)-1/11)).
В ячейке С3 проконтролируем значение f (x): с помощью формулы =B3-СТЕПЕНЬ(B3;3)+1.
Корень уравнения – 1,179. Введем в ячейку А3 значение 2. Получим тот же результат:
Корень на заданном промежутке один.
В этой статье мы расскажем, как использовать формулы для решения систем линейных уравнений.
Вот пример системы линейных уравнений:
3x + 4y = 8
4x + 8y = 1
Решение состоит в нахождении таких значений х
и у
, которые удовлетворяют обоим уравнениям. Эта система уравнений имеет одно решение:
x = 7,5
y = -3,625
Количество переменных в системе уравнений должно быть равно количеству уравнений. Предыдущий пример использует два уравнения с двумя переменными. Три уравнения требуются для того, чтобы найти значения трех переменных (х ,у и z ). Общие действия по решению систем уравнений следующие (рис. 128.1).
- Выразите уравнения в стандартной форме. Если это необходимо, используйте основы алгебры и перепишите уравнение так, чтобы все переменные отображались по левую сторону от знака равенства. Следующие два уравнения идентичны, но второе приведено в стандартном виде:
3x - 8 = -4y
3x + 4y = 8 . - Разместите коэффициенты в диапазоне ячеек размером n x n , где n представляет собой количество уравнений. На рис. 128.1 коэффициенты находятся в диапазоне I2:J3 .
- Разместите константы (числа с правой стороны от знака равенства) в вертикальном диапазоне ячеек. На рис. 128.1 константы находятся в диапазоне L2:L3 .
- Используйте массив формул для расчета обратной матрицы коэффициентов. На рис. 128.1 следующая формула массива введена в диапазон I6:J7 (не забудьте нажать Ctrl+Shift+Enter , чтобы ввести формулу массива): =МОБР(I2:J3) .
- Используйте формулу массива для умножения обратной матрицы коэффициентов на матрицу констант. На рис. 128.1 следующая формула массива введена в диапазон J10:JJ11 , который содержит решение (x = 7,5 и у = -3,625): =МУМНОЖ(I6:J7;L2:L3) . На рис. 128.2 показан лист, настроенный для решения системы из трех уравнений.