Лабораторная работа №11 Тема: Функции для работы с матрицами

Простые диапазоны (массивы) в Excel называются матрицами. Встроенные функции матричных действий можно вводить как из библиотеки, так и с клавиатуры. Ввод формул, возвращающих матрицы, всегда завершается нажатием одновременно трёх кнопок: Ctrl+Shift+Enter. Формула автоматически заключается в фигурные скобки – признак массива.

Задание 1. Получение транспонированной матрицы. В диапазон А1:С5 введите матрицу размера 3х5 (рис. 1):

	А	В	С
1	январь	февраль	март
2	1	2	3
3	86	76	91
4	43	28	35
5	79	71	109

Рис. 1. Матрица 3х5

Необходимо получить транспонированную матрицу: строки должны стать столбцами, а столбцы – строками.

1. Выделите блок ячеек, куда будет вставляться изменённая матрица. Например А8:Е10.

2. Вызовите окно Мастера построения функций и выберите функцию ТРАНСП.

3. В рабочее поле Массив введите диапазон исходной матрицы А1:Е2. Нажмите сочетание клавиш Ctrl+Shift+Enter.

Задание 2. Вычислить определитель матрицы

А=

1. Введите матрицу в диапазон G1:J4

2. Курсор поместите в ячейку, в которую будет записано значение, например J6.

3. Вызовите окно Мастера построения функций, в поле Категория выберите Математические и далее функцию МОПРЕД. Нажмите кнопку ОК.

4. В следующем окне в поле Массив введите диапазон исходной матрицы, нажмите ОК. В ячейке J6 появится значение определителя матрицы.

Ответ: 0.

Задание 3. Получение обратной матрицы. Для этого задания в любой диапазон введите матрицу

А=

1. Выделите блок ячеек 3х3, в который будет занесена обратная матрица.

2. Вызовите окно Мастера построения функций и в поле Категория выберите Математические и далее функцию МОБР. Нажмите кнопку ОК.

3. В следующем окне в поле Массив введите диапазон исходной матрицы, нажмите сочетание клавиш Ctrl+Shift+Enter.

Если обратная матрица не появилась, то указатель мыши поместите в строку формул и повторите сочетание клавиш Ctrl+Shift+Enter.

Ответ:

Задание 4. Найти сумму двух матриц.

1. Введите две произвольные матрицы А и В размером 2х3 каждая (одинаково расположенные).

2. Выделите блок ячеек 2х3, в который будет занесена результирующая матрица.

3. Введите формулу для вычисления суммы: диапазон 1-й матрицы + диапазон 2-й матрицы.

4. Нажмите сочетание клавиш Ctrl+Shift+Enter. В результате в выделенном диапазоне появится матрица, равная сумме исходных матриц. Аналогично вычисли-те разность исходных матриц.

Задание 5. Умножение матрицы на число.

1. Введите произвольную матрицу А размером 2х3.

2. Выделите блок ячеек 2х3, в который будет занесена результирующая матрица.

3. Введите формулу для вычисления результатов умножения матрицы на 3: диапазон исходной матрицы*3.

4. Нажмите сочетание клавиш Ctrl+Shift+Enter. В результате в выделенном диапазоне появится матрица, равная произведению исходной матрицы на постоянную 3.

Задание 6. Произведение двух матриц.

1. Введите две произвольные матрицы А и В размером 3х3 каждая.

2. Выделите блок ячеек 3х3, в который будет записана матрица-произведение.

3. Вызовите окно Мастера построения функций, в поле Категория выберите Математические и далее функцию МУМНОЖ. Нажмите кнопку ОК.

4. В следующем окне в поле Массив1 введите диапазон первой исходной матрицы, а в рабочее поле Массив2 введите диапазон второй исходной матрицы.

5. Нажмите сочетание клавиш Ctrl+Shift+Enter. В результате в выделенном диапазоне появится матрица, равная произведению исходных матриц.

Задание 7. Используя функцию МУМНОЖ рассчитать затраты сырья для планового выпуска продукции. Предприятие выпускает продукцию трёх типов из двух видов сырья. Известен расход сырья каждого типа на единицу продукции. Необходимо рассчитать затраты сырья по видам для планового выпуска всей продукции (рис. 2).

расход на единицу	сырьё 1	сырьё 2		план выпуска продукции
продукция 1	4	8		продукция 1	продукция 2	продукция 3
продукция 2	3	2		130	250	160
продукция 3	1	5

Рис. 2. Таблицы данных для расчёта затрат сырья

1. Введите обе матрицы.

2. Выделите блок ячеек 1х2 (например, В6:С6), в который будет записана матрица, показывающая, какое количество сырья необходимо для выпуска плановой продукции.

3. Вызовите окно Мастера построения функций и в поле Категория выберите Математические и далее функцию МУМНОЖ. Нажмите кнопку ОК.

4. В следующем окне в поле Массив1 введите диапазон исходной матрицы, показывающей план выпуска продукции. В рабочее поле Массив2 введите диапазон исходной матрицы, показывающей расход сырья на единицу продукции.

5. Нажмите сочетание клавиш Ctrl+Shift+Enter. В результате в выделенном диапазоне появится матрица, показывающая необходимое количество сырья для производства запланированной продукции.

Использование матриц для решения систем уравнений. С помощью функций работы с матрицами удобно решать системы линейных уравнений вида:

Такую систему в матричном виде можно записать как АХ=В,

где А= Решением такой системы будет: Х=А^-1В,

где А^-1 – обратная матрица

Задание 8. Решить систему уравнений

1. Введите А матрицу в диапазон А1:В2 А= Вектор В введите в диапазон С1:С2 В=

2. Найдите обратную матрицу. Для этого:

- выделите блок ячеек 2х2 (например, А5:В6), в который будет записана матрица;

- вызовите окно Мастера построения функций, в поле Категория выберите Математические и далее функцию МОБР. Нажмите кнопку ОК;

- в следующем окне в поле Массив введите диапазон исходной матрицы;

- нажмите сочетание клавиш Ctrl+Shift+Enter.

3. Умножьте обратную матрицу на вектор В. Диапазон ячеек, в который будет записываться результат, должен быть аналогичен диапазону матрицы В. В первой ячейке будет записано значение для х (6), во второй – для у (12). Сделайте проверку решения системы уравнений.

Задание 9. Решить систему уравнений

1. Введите матрицу А в диапазон А1:В3. Вектор В введите в диапазон С1:С3 (аналогично предыдущему заданию).

2. Найдите транспонированную матрицу А^т, её размер будет 2х3, например, А4:С5.

3. Найдите произведение матрицы А^т и вектора В. Размерность результирующей матрицы будет 2х1, например, Е4:Е5.

4. Найдите произведение матриц А^т и А. Размерность результирующей матрицы будет 2х2, например, А7:В8.

5. Найдите обратную матрицу полученной в пункте 4.

6. Найдите произведение полученной обратной матрицы на вектор А^тВ (то, что получили в пункте 3). Размерность результирующей матрицы будет 2х1, например С6:С7.

В результате получится вектор, показывающий значения х (2) и значение у (-4).

7. Выполните проверку.