Реализация расчетов в Excel

Пусть нам дана следующая система уравнений:

 

(2.4)

 

В матричном виде уравнение (4) выглядит следующим образом:

 

(2.4а)

 

Процесс решения уравнения (2.4) или (2.4а) состоит в последовательном вычислении компонентов уравнения (2.3).

1) В ячейках D5:F7 разместим матрицу коэффициентов;

2) В ячейках H5:H7 разместим вектор–столбец Y;

 

	C	D	E	F	G	H
		A		Y
			–3
		–2	–4

3) Получим транспонированную матрицу A^T.

Для этого:

– копируем ячейки D5:F7 в буфер;

– устанавливаем курсор в ячейку D11;

– выполняем команды Правка > Специальная вставка > Транспонировать > Ok.

 

	C	D	E	F	G	H
		AТ
				–2
			–3	–4

4) Вычислим матрицу А^Т Y.

Для этого:

– курсор устанавливаем ячейку D16;

– вызываем функцию МУМНОЖ (из категории «Математические»);

– в качестве «Массив1» указать адрес транспонированной матрицы D11:F13;

– в качестве «Массив2» указать адрес матрицы Y (H5:H7);

– после «Ok» в D16 появится только первый элемент массива (число 31);

– для того, чтобы увидеть остальные числа:

выделяем ячейки D16:D18;

нажимаем F2;

выполняем тройное нажатие Ctrl + Shift + Enter.

 

	C	D	E	F	G	H
		AТY

5) Вычислим матрицу А^ТА.

Для этого:

– курсор устанавливаем ячейку D21;

– вызываем функцию МУМНОЖ;

– в качестве «Массив1» указать адрес транспонированной матрицы D11:F13;

– в качестве «Массив2» указать адрес матрицы А (D5:F7);

– после «Ok» в D21 появится только первый элемент результирующей матрицы (число 9);

– для того чтобы увидеть остальные числа:

выделяем ячейки D21:F23;

нажимаем F2;

выполняем тройное нажатие Ctrl + Shift + Enter.

 

	C	D	E	F	G	H
		AТA
				–5
			–5

 

6) Вычислим матрицу ( A^TA)^–1.

Для этого:

– курсор устанавливаем ячейку D21;

– вызываем функцию МОБР;

– в качестве аргумента указать адрес матрицы A^TA (D21:F23);

– после «Ok» в D26 появится только первый элемент результирующей матрицы (число 0,150);

– для того чтобы увидеть остальные числа:

выделяем ячейки D26:F28;

нажимаем F2;

выполняем тройное нажатие Ctrl + Shift + Enter.

 

	C	D	E	F	G	H
		(AТA)–1
		0,150156	–0,02734	–0,03578
		–0,02734	0,035156	0,011719
		–0,03578	0,011719	0,043906

 

7) Вычислим вектор–столбец неизвестных.

Для этого:

– курсор устанавливаем ячейку D31;

– вызываем функцию МУМНОЖ;

– в качестве «Массив1» указать адрес матрицы (А^ТА)^–1 (D26:F28);

– в качестве «Массив2» указать адрес матрицы А^ТY (D16:D18);

– после «Ok» в D31 появится только первый элемент результирующей матрицы (число –1);

– для того чтобы увидеть остальные числа:

выделяем ячейки D31:F33;

нажимаем F2;

выполняем тройное нажатие Ctrl + Shift + Enter.

 

	C	D	E
		x
		–1

 

Таким образом, корни системы (4) равны: x₁ = –1; x₂ = 2; x₃ = 5.

 

Варианты заданий

Решить систему уравнений заданных в виде матриц коэффициентов и свободных членов.

1.

A		Y
		–3		–4
–5	–2	–2	–2	–5		–12
	–5	–2		–3		–14
–4	–4	–2	–4	–3		–12
		–1	–1	–3

 

2.

A		Y
		–3		–4
				–2		–9
	–3					–14
–4	–3	–2	–5
–5	–5			–3		–12

 

3.

A		Y
	–4	–5		–2
–3		–1	–3			–8
–3		–1	–1			–8
			–5	–3
–2	–4			–2		–17

 

4.

A		Y
–2		–3				–16
	–2	–1		–3
–2	–2	–1		–5
		–2	–2	–3		–22

 

5.

A		Y
	–2		–3			–10
–1	–3
–5	–5			–5
		–1		–3		–31
		–5		–5		–24

 

6.

A		Y
	–1	–1	–2	–1
	–2	–1	–1			–2
–2		–3				–1
	–3			–4		–30
	–5	–1	–2	–5		–9

 

7.

A		Y
	–4		–5	–1		–11
–1		–1		–3
–3	–1	–3	–1	–4
–5	–1	–3	–1
	–4	–2	–3	–5

 

 

8.

A		Y
	–5		–5	–3
–2				–3
	–5	–5		–2
		–1	–2			–18
–2	–1	–1

 

9.

A		Y
–1	–2	–3
–3		–4	–1			–13
–4			–3			–21
	–4		–2	–3
	–1	–3

10.

A		Y
–4	–1	–3	–4			–9
–5	–1	–1	–4	–2		–15
–5	–3		–1	–1		–3
	–1	–1				–7
–5	–3	–1	–1			–4

 

11.

A		Y
–2		–1	–2	–2		–17
	–2		–2	–1		–12
	–1			–5		–11
–3	–1	–5	–1
	–1	–2		–3

 

12.

A		Y
		–5		–5
			–5
			–5	–4
	–2	–1	–4
–5		–4

 

13.

A		Y
–5		–5	–5	–5
–1			–4
–1	–3			–2		–44
–5	–5	–1				–28
	–5					–24

 

14.

A		Y
	–5			–5
		–4	–5	–5		–7
	–5	–4		–3
	–1	–1		–5
	–4			–2

 

15.

A		Y
–3			–3			–18
		–5				–33
–5		–1	–5	–4		–5
	–2	–2		–1
			–3