Метод Гаусса за допомогою Excel

Задача 1. Розв’язати СЛР .

Розв’язання. Запишемо розширену матрицю даної СЛР у діапазон А2:С4

 

	A	B	C	D
	x1	x2	x3	b
	3,9	1,25	-0,98	4,905
	0,74	3,45	-0,84	6,031
	-0,65	1,18	2,38	10,134

 

Тут і надалі доцільно підкреслити всі границі цієї таблиці, натиснувши кнопку Границы → Все границы на панелі Форматирование: це дасть змогу не рахувати потім номер рядка чи стовпця при подальших обчисленнях. На першому кроці алгоритму наша мета – досягти того, щоби в результаті послідовності елементарних операцій над матрицею під рядком 2 у стовпці А значення чарунок дорівнювали нулю. Це і реалізує така електронна таблиця:

 

	A	B	C	D
	= A2/$A$2	→	→	→
	= A3 – A6*$A$3	→	→	→
	= A4 – A6*$A$4	→	→	→

 

Тут формули для матричних операцій над рядком (ті ж самі, що й у попередньому прикладі) записуються в одну чарунку, а потім розповсюджуються на весь рядок, просто перетягнувши маркер заповнення згідно з правилами Excel. Ф ормули треба виконувати спочатку у першому стовпці (тобто у стовпці 6), а потім у стовпцях 7 і 8. В результаті дістанемо:

	A	B	C	D
		0,320513	-0,25128	1,257692
		3,212821	-0,65405	5,100308
		1,388333	2,216667	10,9515

 

На другому кроці треба отримати 1 для елемента матриці, відповідного В7 і 0 для елементів, відповідних В6 і В8. Це реалізує така електронна таблиця:

 

	A	B	C	D
	←	= B6 – B11*$B$6	→	→
	←	= B7/$B$7	→	→
	←	= B8 – B11*$B$8	→	→

 

Тут формули для матричних операцій записуються у чарунки стовпця В, а потім копіюються на відповідні рядки. Ф ормули треба виконувати спочатку у другому стовпці (тобто у стовпці 11), а потім у стовпцях 10 і 12. В результаті дістанемо:

 

	A	B	C	D
			-0,18603	0,748883
			-0,20358	1,587486
			2,499297	8,74754

 

Нарешті, на третьому кроці треба отримати 1 для елемента матриці, відповідного С12 і 0 для елементів, відповідних С10 і С11.

 

	A	B	C	D
	←	←	= C10 – C16*$C$10	→
	←	←	= C11 – C16*$C$11	→
	←	←	= C12/$C$12	→

Ф ормули треба виконувати спочатку у стовпці 12, а потім у 10 і 11. В результаті:

 

	A	B	C	D
				1,4
				2,3
				3,5

Матриця приведена до діагональної форми. Отже, розв’язок даної СЛР: .

Похибка методу Гаусса дорівнює нулю (і в такому сенсі це точний метод), обчислювальна похибка отриманого розв’язку не перевищує мінімальної можливої похибки Excel, помноженої на кількість зроблених операцій, тобто насправді майже не відрізняється від цієї мінімальної похибки. Проте переконаємося у правильності отриманого розв’язку безпосередньо, підставивши його у всі рівняння даної СЛР згідно з означенням розв’язку.

Для цього зручно буде мати цей розв’язок у горизонтальному діапазоні. Тому скопіюємо чарунки D14:D15, виділимо чарунку А18, клацнімо по ній правою кнопкою миші і в контекстному меню обираємо Специальная вставка і у наступному діалозі клацніть по значения і транспонировать (знизу вікна). Розв’язок з’явиться у діапазоні А18:С18. У чарунці D18 задамо формулу = СУММПРОИЗВ($A$18:$C$18; A2:C2), яка підраховує результат підстановки розв’язку у перше рівняння системи. Після цього перетягнемо маркер заповнення з D18 на дві чарунки вниз – там з’являться підрахунки результатів підстановки у друге та третє рівняння розв’язку згідно з правилами копіювання формул в Excel:

	A	B	C	D
	1,4	2,3	3,5	4,905
				6,031
				10,134

 

Значення в діапазоні D18:D20 точно співпадають з вільними членами даної СЛР у D2:D4, що є безпосереднім доведенням правильності отриманого розв’язку.

Задача 2. Розв’язати СЛР х₁ – 2х₂ + 3х₃ – 2х₄ = 3

2х₁ – 3х₂ + х₃ – 2х₄ = – 2

4х₁ – х₂ + 2х₃ – х₄ = 0.

Розв’язання. Запишемо розширену матрицю даної СЛР у діапазон А2:Е4

 

	A	B	C	D	Е
	x1	x2	x3	x4	b
		-2		-2
		-3		-2	-2
		-1		-1

 

– це система трьох лінійних рівнянь з чотирма змінними. Проте метод Гаусса – алгоритм, який дозволяє розв’язати будь – яку СЛР. Послідовність дій фактично не відрізняється від попереднього прикладу. Перший крок:

	A	B	C	D	E
	= A2	→	→	→	→
	= A3 – A6*$A$3	→	→	→	→
	= A4 – A6*$A$4	→	→	→	→

В результаті:

	A	B	C	D	Е
		-2		-2
			-5		-8
			-10		-12

 

Другий крок:

	A	B	C	D	E
	←	= B6 + B7*2	→	→	→
	←	= В7	→	→	→
	←	= B6 – B7*7	→	→	→

 

Тут елементи матриці – цілі числа, тому зручно у формулах замість адреси чарунки використати саме значення. В результаті:

 

	A	B	C	D	Е
			-7		-13
			-5		-8
				-7

Третій крок:

	A	B	C	D	E
	←	←	= C10 – C16*$C$10	→	→
	←	←	= C11 – C16*$C$11	→	→
	←	←	= C12/$C$12	→	→

звідки

	A	B	C	D	Е
				0,04	-0,68
				0,6	0,8
				-0,28	1,76

Матриця у діапазоні А14:С16 зведена до діагонального вигляду, що завершує алгоритм. Отриманій матриці відповідає така СЛР: . Тут за означенням змінні х₁, х₂, х₃, яким відповідають діагональні елементи матриці – це основні (базові) змінні, всі інші (тут тільки х₄) – неосновні (вільні). Переносимо у рівняннях СЛР вільні змінні зліва направо: і отримаємо загальний розв’язок: кожному значенню вільної змінної відповідає свій (частковий) розв’язок і всі розв’язки СЛР мають такий вигляд. В електронній таблиці їх можна отримувати так:

	A	B	C	D	Е	F
				0,04	– 0,68	= E14 – D14*$F$17
				0,6	0,8	↓
				– 0,28	1,76	↓
						*

 

У чарунку F17 замість * можна занести довільне значення змінної х₄ і в діапазоні F14:F17 дістанемо відповідний частковий розв’язок даної СЛР. Наприклад,

 

	A	B	C	D	Е	F
				0,04	-0,68	-0,72
				0,6	0,8	0,2
				-0,28	1,76	2,04

або

	A	B	C	D	Е	F
				0,04	-0,68	-0,6
				0,6	0,8
				-0,28	1,76	1,2
						-2

 

Перевірку правильності отриманих розв’язків можна провести, як і раніше. А саме скопіюємо чарунки F14:F17 у горизонтальний діапазон А19:D19, задамо формулу = СУММПРОИЗВ($A$19:$D$19;A2:D2), яка підраховує значення результату підстановки розв’язку у перше рівняння і перетягнемо маркер заповнення на дві чарунки вниз – там з’являться підрахунки результатів підстановки у друге та третє рівняння розв’язку згідно з правилами копіювання формул в Excel:

	A	B	C	D	Е
	-0,6		1,2	-2
					-2
					-4,4E-16

 

У чарунці Е14 тут знаходиться значення, яке з точки зору Excel не відрізняється від нуля. Отже, відповідь: – це загальний розв’язок СЛР, або – це приклади часткових розв’язків.

Задача 3. Розв’язати СЛР 3х₁ – 2х₂ + х₃ = 2

2х₁ – 3х₂ + 2х₃ = –1

х₁ – х₂ + 3х₃ = 0

2х₁ – 4х₂ + 2х₃ = 3.

Розв’язання. Запишемо розширену матрицю даної СЛР у діапазон А2:D5

 

	A	B	C	D
	x1	x2	x3	b
		-2
		-3		-1
		-1
		-4

 

– це система чотирьох лінійних рівнянь з трьома змінними. Але метод Гаусса – алгоритм, послідовність дій така ж, як у попередньому прикладі. Перший крок:

 

	A	B	C	D
	= A2/$A$2	→	→	→
	= A3 – A7*$A$3	→	→	→
	= A4 – A7*$A$4	→	→	→
	= A5 – A7*$A$5	→	→	→

В результаті:

	A	B	C	D
		- 2/3	1/3	2/3
		-1 2/3	1 1/3	-2 1/3
		- 1/3	2 2/3	- 2/3
		-2 2/3	1 1/3	1 2/3

 

Оскільки елементи початкової матриці цілі числа, то обрано дрібний формат чарунок.

Другий крок:

	A	B	C	D
	←	= B7 – B13*$B$7	→	→
	←	= B8/$B$8	→	→
	←	= B9 – B13*$B$9	→	→
	←	= B10 – B13*$B$10	→	→

звідки

	A	B	C	D
			- 1/5	1 3/5
			- 4/5	1 2/5
			2 2/5	- 1/5
			- 4/5	5 2/5

Третій крок:

	A	B	C	D
	←	←	= C12 – C19*$C$12	→
	←	←	= C13 – C19*$C$13	→
	←	←	= C14/$C$14	→
	←	←	= C15 – C19*$C$15	→

звідки

	A	B	C	D
				1 7/12
				1 1/3
				- 1/12
				5 1/3

 

Матриця у діапазоні А17:С19 зведена до діагонального вигляду, що завершує алгоритм. Отриманій матриці відповідає така СЛР: . Останнє рівняння виписано із всіма нульовими коефіцієнтами. Яким би не був набір значень х₁, х₂ , х₃, в результаті їх підстановки в останнє рівняння отримаємо протиріччя: 0 = 5 1/3. Отже, це рівняння, а тому і вся система не мають розв’язків. Але звідси несумісною є й початкова СЛР, еквівалентна кінцевій. Відповідь: розв’язків не існує.

Три наведених приклада у деякому сенсі вичерпують всі випадки, які можуть зустрітися при розв’язанні будь – якої системи m лінійних рівнянь з n змінними. Справді, нехай дана система m лінійних рівнянь з n змінними уже зведена до діагональної форми максимально можливого розміру p. Тоді р £ m, p £ n. В такій формі всі елементи m – p рядків матриці системи під діагональною матрицею дорівнюють нулю (рисунок 1). Справді, елементи

 

n

p

1 *

1 0 *

p 1 р *

0 … * *

………………. * * * * * * *

m 0 0 0 0 … 1 *

0 0 0 0 … 0 0 0 0 0 0 0 *

 

……………………………………. *

 

0 0 0 0 … 0 0 0 0 0 0 0 *

 

Рисунок 1.

 

під діагональною матрицею у перших р стовпцях дорівнюють нулю за алгоритмом. Якщо б серед інших елементів цих рядків залишився елемент, відмінний від нуля, то алгоритм мав би продовження і в результаті отримали б діагональну матрицю порядку > p. Отже, 1) якщо є принаймні один елемент стовпця n + 1 (стовпця вільних членів) b_j ¹ 0 з j > p, то такому стовпцю j відповідає рівняння 0 = b_j. Звідси отримана СЛР не має розв’язків (несумісна), а тому несумісна й рівносильна їй початкова СЛР (як у задачі 3). Якщо ж при j > p всі b_j = 0, то всі рядки розширеної матриці системи під діагональною можна просто відкинути, не порушуючи еквівалентності, оскільки відповідні рівняння перетворюються у тотожності 0 = 0. В результаті отримуємо p ´ n матрицю (рисунок 2):

 

 

n

p

1 *

1 0 * *

p 1 р *

0 … *

………………. * * * * * * *

0 0 0 0 … 1 *

 

 

Рисунок 2.

 

2) Якщо деякий стовпець такої матриці системи з і > p складаються лише з елементів, рівних нулю, то такий стовпець (і відповідну йому змінну х_і) можна просто відкинути, не порушуючи еквівалентності. Отже, можна вважати, що всі стовпці матриці такої системи мають елементи, відмінні від нуля. В такому разі, якщо n > p, то відповідна СЛР сумісна і невизначена (як у задачі 2): всі змінні х_і з і > p є вільними і кожному набору вільних змінних відповідає розв’язок даної СЛР.

3) Якщо n = p, то матриця системи є квадратною та діагональною і отже елементи b_j стовпця р + 1 (стовпця вільних членів) утворюють єдиний розв’язок даної СЛР: система сумісна та визначена (як у задачі 1).

Зауваження. З попереднього ясно: якщо число рівнянь m перевищує число змінних n, то, як правило, така СЛР несумісна (як у задачі 3). Якщо m < n, то, як правило, така СЛР сумісна і невизначена (як у задачі 2). Якщо m = n, то така СЛР, як правило, сумісна та визначена (як у задачі 1). Тут сказано «як правило», бо спеціальний підбір коефіцієнтів може це правило порушити. Розглянемо такий приклад.

Задача 4. Розв’язати наступну СЛР методом Гаусса.

2х₂ – х₃ = – 3

х₁ + 3х₂ – 2х₃ = 2

2х₁ +10х₂ – 6х₃ = – 2.

 

Розв’язання. Зведемо розширену матрицю даної СЛР до діагональної форми так само, як і в попередніх прикладах. В результаті отримуємо:

 

	A	B	C	D
	x1	x2	x3	b
			-1	-3
			-2
			-6	-2
			-2
			-1	-3
			-6	-2
			-2
			-1	-3
			-2	-6
			-0,5	6,5
			-0,5	-1,5

 

Тут ми 1) переставляємо місцями перший і другий рядки, 2) віднімаємо з третього рядка перший, помножений на 2, 3) ділимо другий рядок на 2 і віднімаємо від першого отриманий рядок, помножений на 3, і від третього цей рядок, помножений на 4. Останній рядок складається з нулів, тому відкидаємо його і отримуємо загальний розв’язок:

х₁ = 6,5 + 0,5х₃

х₂ = - 1,5 + 0,5х₃.

Отже, дана система трьох лінійних рівнянь з трьома змінними сумісна та невизначена.

 

Матричні операції в Excel

 

Звичайно, матриці використовуються не лише для компактного запису розв’язування СЛР методом Гаусса. Насправді це один з найбільш важливих математичних об’єктів. Над матрицями визначені операції подібні операціям над числами.

1. Множення матриці на число:

 

а₁₁…..а_1n α×а₁₁ ... α×а_1n

α ∙ ………... = ………..

а_{m1 …} а_{m n} α×а_{m1 …} α×а_{m n,}

 

де α – дійсне число. Наприклад, для множення матриці на число – 3 складемо таку таблицю

	А	B	C
	- 2
	- 1	- 5
	=A1*-3	®	®
	¯	¯	¯

В результаті дістанемо

	А	B	C
	-2
	-1	-5
			-9
			-6

 

2. Додавання та віднімання m n – матриць (тобто матриць з m строками та n стовпцями).

а₁₁…..а_1n b₁₁... b_1n а₁₁ b₁₁ а_1n b_1n

………... ……….. = …… ………..

а_{m1 …} а_{m n} b_m1… b_{m n} а_m1 b_{m 1} а_{m n} b_{m n} .

 

Наприклад, для підрахунку матриці, що дорівнює складемо таку таблицю

	A	B	C	D	E	F	G	H
		-2		-4	-7		= A1 – D1	®
			–			=	¯	¯
	-1			-6			¯	¯

 

В результаті дістанемо

 

	A	B	C	D	E	F	G	H
		-2		-4				-9
			–			=
	-1			-6	-9

Для матриць, у яких не співпадають число рядків або число стовпців операції додавання та віднімання не визначені. Так не існує матриці, що дорівнює

2 -3 1 2 1

4 0 -2 + 0 -1.

 

Задача 1. Знайти матрицю X = .

 

Розв’язання. Матриця Х існує, оскільки всі матриці у її визначенні мають один розмір. Для підрахунків використаємо таблицю

 

	A	B	C
		-5
	-5
	-3	-8	-5
			-2
	-2
		-2
	=
	=3*A2-5*A5-2*A8	®	®
	¯	®	®

 

У чарунці А11 замість наведеної у таблиці можна задати також формулу =$А$1*А2+$А$4*А5+$А$7*А8. В обох випадках дістанемо Х в діапазоні А11:С12:

 

	A	B	C
		-5	-4
	-5
	-3	-8	-5
			-2
	-2
		-2
	=
		-41

 

3. Множення матриць.

Добуток m n – матриці А на n p матрицю В – це така m р – матриця С = А×В, елемент с_ij якої є скалярним добутком вектор – рядка і матриці А на вектор – стовпець j матриці В. Добуток 2 ´ 3 матриці А = на 2 ´ 2 матрицю В = С = А×В не існує, оскільки число стовпців матриці А дорівнює 3 і не дорівнює числу рядків матриці В.

Для множення матриць є спеціальний вбудований оператор Excel МУМНОЖ. Наприклад, у наступній електронній таблиці

 

	A	B	C	D	E	F
		-5				-4
	-7

 

задані 3 ´ 2 матриця А = і 2 ´ 3 матриця В = . Для знаходження 3 ´ 3 матриці С = А×В виділимо діапазон А5:С7 і (можливо за допомогою вікна цього оператора) введемо в нього формулу =МУМНОЖ(А1:В3;D1:F2).

 

	A	B	C	D	E	F
		-5				-4
	-7
	=МУМНОЖ(А1:В3;D1:F2)

 

Після цього, притримуючи кнопки Alt i Shift, треба натиснути не Enter. В результаті отримаємо матрицю С:

 

	A	B	C	D	E	F
		-5				-4
	-7
		-5	-47
			-16
	-29

 

Якщо спробувати помножити матриці А і В з нерівними числами стовпців у А та рядків у В, то отримаємо повідомлення про помилку #ЗНАЧ!.

4. Матриця, обернена до квадратної матриці А порядку n – це така квадратна матриця А^-1 того ж порядку, що А А^-1 = А^-1 А = Е, де Е – одинична матриця. Якщо А має обернену, то її можна знайти методом Гаусса.

Розглянемо n ´ 2n матрицю АЕ, яку можна отримати, дописавши праворуч від матриці А одиничну матрицю того ж порядку. Наприклад, якщо А = , то АЕ = . Зведемо матрицю АЕ до діагональної форми методом Гаусса. Тоді вона отримає вигляд ЕВ, де В – деяка квадратна матриця порядку n. Наприклад при n = 3 ЕВ = . Стверджується, що така матриця В і є обернена до А, тобто В = А^-1.

Справді, як ми бачили вище, якщо звести до діагональної форми СЛР з матрицею системи А, то стовпець вільних членів у розширеній матриці цієї системи є розв’язком даної СЛР. Тому перший стовпець матриці В є розв’язком СЛР Ах = , наприклад, = , тобто = . Аналогічно другий стовпець матриці В є розв’язком СЛР Ах = , наприклад, = , тобто = , …. Але за означенням множення матриць це означає, що АВ = Е, тобто В = А^-1.

Задача 2. Знайти матрицю, обернену до матриці А = методом Гаусса.

Розв’язання. Задамо в електронній таблиці матрицю АЕ:

 

	A	B	C	D	E	F
	4,05	-0,93	-0,41
	1,18	3,78	-0,87
	-0,96	-1,02	3,68

 

Зведемо її до діагональної так само, як і в попередньому параграфі:

 

	A	B	C	D	E	F
	4,05	-0,93	-0,41
	1,18	3,78	-0,87
	-0,96	-1,02	3,68
		-0,22963	-0,10123	0,246914
		4,050963	-0,75054	-0,29136
		-1,24044	3,582815	0,237037
			-0,14378	0,230398	0,056685
			-0,18528	-0,07192	0,246855
			3,352991	0,14782	0,30621
				0,236737	0,069816	0,042881
				-0,06376	0,263775	0,055257
				0,044086	0,091324	0,298241

 

0,236737 0,069816 0,042881

Отже, матриця В = –0,06376 0,263775 0,055257 є оберненою до матриці А. Це

0,044086 0,091324 0,298241

можна перевірити безпосередньо за означенням за допомогою вбудованого оператора МУМНОЖ. Результат множення А на В, тобто результат дії оператору МУМНОЖ(A1:C3; D13:F15) дорівнює

	-3,46945E-17	-1,38778E-17
6,25E-17		-5,55112E-17
	-5,55112E-17

Оскільки числа ~ 10^-17 з точки зору Excel не відрізняються від нуля, то отримана матриця є одиничною. Якщо є сумнів, то можна змінити формат чарунки з общий на числовой. Тоді дістанемо

1,00	0,00	0,00
0,00	1,00	0,00
0,00	0,00	1,00

Знайти обернену матрицю можна також і за допомогою спеціального вбудованого оператора Excel МОБР. Треба виділити квадратний діапазон того ж розміру, що й у даної матриці, (в цьому прикладі з 9 чарунок), задати в ньому цей оператор, єдиним аргументом якого є діапазон даної матриці (тут це МОБР(А1:С3)) і притримуючи кнопки Alt i Shift, натиснути не Enter. Результат в даному випадку просто співпадає з отриманим вище методом Гаусса.

Звичайно, не кожна квадратна матриця А має звернену, тільки та, визначник якої не дорівнює нулю: ôАô ¹ 0. Тому перед безпосереднім розв’язуванням задачі 2 було б доцільно знайти визначник даної матриці. Це можна зробити за допомогою відповідного вбудованого оператора Excel МОПРЕД. Треба виділити чарунку і задати там цей оператор, єдиним аргументом якого є діапазон заданої матриці. Після цього можна натиснути ОК або Enter: на відміну від МОБР та МУМНОЖ результатом дії цього оператора є числове значення, а не масив чарунок. В даному прикладі формула МОПРЕД(А1:С3) дає значення визначника ôАô = 55,01051.

Якщо значення визначника матриці відмінне від нуля, але досить мале порівняно з її елементами, то відповідна СЛР погано обумовлена. СЛР називають добре обумовленою, якщо малі збурення коефіцієнтів мало збурюють її розв’язки і в противному разі погано обумовленою. Наприклад, СЛР , розв’язком якої є пара (1; 0) погано обумовлена. Справді, якщо число 6,1 у правій частині першого рівняння змінити на 0,02, то СЛР матиме розв’язком пару (5,1; - 7,35). Якщо коефіцієнт 6,1 у лівій частині першого рівняння змінити на 0,01, то розв’язком СЛР буде пара (0,36730972; 1,204918). Причиною поганої обумовленості цієї системи є її майже виродженість: її визначник дорівнює 0,04 і становить лише 0,27% значення найбільшого з коефіцієнтів системи 14,7.