Часть 12. Операции с матрицами

 

Матрицы широко используются для математического описания объектов и процессов. Excel в полной мере поддерживает операции и арифметические действия над матрицами. Это позволяет использовать Excel для решения систем линейных уравнений, о чем будет рассказано в следующем разделе. Здесь же рассмотрим основные сведения о матрицах и функциях Excel для выполнения операций над матрицами.

Математическая запись прямоугольной матрицы A размером m х n выглядит так:

 

	a11	a12	…	a1n
	a21	a22	…	a2n
A=(	a31	a32	…	a3n	)=aij
	…	…	…	…
	am1	am2	…	amn

 

Матрица, состоящая из одной строки (столбца), называется вектором:

 

A= (a₁₁, a₁₂, …, a_1n)

 

Если m=n, то матрица называется квадратной n-го порядка. Если в квадратной матрице элементы равны 0, а диагональные элементы равны 1, то матрица называется единичной и диагональной. Единичная матрица E 3-го порядка выглядит так:

 

	1	0	0
E=(	0	1	0	)
	0	0	1

 

Основные операции над матрицами и соответствующие функции Excel следующие:

- транспонирование матрицы - функция ТРАНС из группы Ссылки и массивы;

- вычисление определителя матрицы - функция МОПРЕД из группы Математические;

- нахождение обратной матрицы - функция МОБР из группы Математические;

- сложение, вычитание;

- умножение - функция МУМНОЖ из группы Математические.

 

Рассмотрим каждую операцию и ее применение в Excel в отдельности.

Транспонирование. В транспонированной матрице A^T (или А’) столбцы исходной матрицы А заменяются строками, т.е. если А=(a_ij), то A^T=(a_ji). Транспонируем матрицу A1:D2 в матрицу A4:B7, как показано ниже:

 

Как видно, функция ТРАНСП возвращает горизонтальный диапазон ячеек в виде вертикального и наоборот. Проделайте следующее:

1. Выделите диапазон ячеек A4:B7 и вызовите мастер функций кнопкой fx.

2. В окне мастера выберите функцию ТРАНСП и укажите диапазон исходного массива A1:D2.

3. После нажатия ОК встаньте на строку формул и нажмите Ctrl+Shift+Enter.

Окно мастера функций обычно закрывает необходимый диапазон ячеек. Его можно отодвинуть указателем мыши, удерживая левую кнопку. Это даст Вам возможность выделить нужный диапазон и нажать Enter – диапазон сам попадет в окно мастера.

 

Вычисление определителя. Определитель | A | это числовая характеристика квадратной матрицы и вычисляется из значений ее элементов. Если | A |=0, то матрица называется вырожденной. Для матрицы 1-го порядка A=(a₁₁) определитель равен | A |= a₁₁. Для матрицы 2-го порядка определитель равен | A |= a₁₁ a₂₂ – a₁₂ a₂₁. Определитель матрицы 3-го порядка содержит 6 слагаемых, 4-го порядка 24 и т.д. – для матрицы n-го порядка число слагаемых равно n!. Функция Excel МОПРЕД облегчает вычисления.

Вычислите определитель матрицы А1:С3, показанной ниже:

 

 

Для этого в ячейку В5 запишите функцию МОПРЕД и укажите диапазон А1:С3. Значение определителя равно 6.

 

Нахождение обратной матрицы. Обратные матрицы используются для решения систем уравнений с несколькими неизвестными. Обратная матрица А^-1 существует только для невырожденной матрицы А. Для матрицы 2-го порядка

 

	a	b
А=(	c	d	)

обратная матрица вычисляется так:

	d/(ad – bc)	b/(bc – ad)
А^-1 =(	c/(bc – ad)	a/(ad –b c)	)

 

Функция Excel МОБР облегчает подобные вычисления. Ниже приведена матрица А1:С3, а в А5:С7 обратная матрица.

 

Выполните самостоятельно: выделите диапазон для обратной матрицы А5:С7, вызовите мастер функций кнопкой fx, выберите функцию МОБР и введите диапазон исходной матрицы А1:С3, после нажатия ОК встаньте на строку формул и нажмите Ctrl+Shift+Enter.

 

Сложение и вычитание матриц не представляет трудностей. Для этого не существует специальных функций – следует выполнить поэлементное сложение (вычитание) матриц. Складывать (вычитать) можно матрицы одного размера.

Умножение матрицы на число также выполняется поэлементным умножением элементов на это число.

Умножение двух матриц возможно, когда число столбцов первой матрицы равно числу строк второй. Правило перемножения продемонстрируем на примере:

 

	a11	a12	a13		в11	в12
С=А*В=(	a21	a22	a23	)*(	в21	в22	)=
	a31	a32	a33		в31	в32

 

	a11*в11+a12*в21+a13*в31		a11*в12+a12*в22+a13*в32		с11	с12
=(	а21*в11+a22*в21+a23*в31		а21*в12+a22*в22+a23*в32	)=(	с21	с22	)
	a31*в11+a32*в21+a33*в31		a31*в12+a32*в22+a33*в32		с31	с32

 

Как видно, элемент с_ij произведения равен сумме произведений элементов i-ой строки первой матрицы на j-ый столбец второй матрицы. При использовании функции МУМНОЖ следует точно определять размерность матрицы произведения: если размер первой матрицы m*n, второй матрицы n*p, то размер произведения будет m*p.

Умножение матриц используем при решении следующего примера. Ателье шьет комбинезоны трех типов К1, К2, К3 и использует ткани двух типов Т1, Т2. Нормы расхода ткани каждого типа на каждый комбинезон, план производства (штук) и стоимость (руб.) единицы тканей заданы матрицами:

 

 

Необходимо определить а) затраты (метраж) тканей каждого типа для выполнения плана и б) стоимость затрат на один комбинезон каждого типа. Результат решения показан ниже:

 

 

Воспроизведите это решение. Для определения затрат перемножьте матрицу B2:D3 на матрицу F2:F4 (число столбцов первой совпадает с числом строк второй и равно 3). Размерность произведения будет 2*1. Вычислите его в ячейках J2:J3. Ткани Т1 требуется 450м, а ткани Т2 850м.

Для определения стоимости единицы продукции вначале транспонируйте матрицу B2:D3 в матрицу B6:C8, затем перемножьте ее на матрицу H2:H3 (число столбцов первой совпадает с числом строк второй и равно 2). Размерность произведения будет 3*1. Вычислите его в ячейках F6:F8. Себестоимость ткани на один комбинезон К1 равна 117 руб., на К2 равна 131,5 руб., на К3 равна 158 руб.