Вопрос 40

Excel позволяет ввести формулу, которая будет выполняться не в одной ячейке, а в прямоугольной области ячеек, вычисляя сразу несколько зависимых значений. Причем в качестве аргументов такая формула может принимать не одно значение, а опять же - массив. Чтобы ввести такую формулу, надо выделить прямоугольный диапазон, в строке формул набрать выражение формулы и нажать клавиши CTRL+SHIFT+ENTER.

ТРАНСП(массив)

Массив — это диапазон ячеек на рабочем листе. Транспонирование массива заключается в том, что первая строка массива становится первым столбцом нового массива, вторая строка массива становится вторым столбцом нового массива и так далее.

Пример 1

Предположим, что в диапазон ячеек А1:Е2 введена матрица размера 2х5

Необходимо получить транспонированную матрицу.

Решение.

Выделите блок ячеек под транспонированную матрицу (5х2), например А4:В8

Нажмите Вставка функции f_x

В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Ссылки и массивы, а в рабочем поле Функция – имя функции ТРАНСП. После этого щелкните на кнопке ОК.

В рабочее поле Массив введите диапазон исходной матрицы А1:Е2.

Нажмите сочетание клавиш CTRL+SHIFT+ENTER.

В результате в диапазоне А4:В8 появится транспонированная матрица

МОПРЕД(массив) ВЫЧИСЛЕНИЕ ОПРЕДЕЛИТЕЛЯ МАТРИЦЫ

Массив — числовой массив с равным количеством строк и столбцов.

Определитель матрицы — это число, вычисляемое на основе значений элементов массива. Для массива A1:C3, состоящего из трех строк и трех столбцов, определитель вычисляется следующим образом: МОПРЕД(A1:C3) равняется A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*C1)

Заметки

Массив может быть задан как интервал ячеек, например A1:C3, или как массив констант, например {1;2;3:4;5;6:7;8;9}, или как имя, именующее интервал или массив.

Если какая-либо ячейка в массиве пуста или содержит текст, то функция МОПРЕД возвращает значение ошибки #ЗНАЧ!.

МОПРЕД также возвращает значение ошибки #ЗНАЧ!, если массив имеет неравное количество строк и столбцов.

Определители матриц обычно используются при решении систем уравнений с несколькими неизвестными.

МОПРЕД производит вычисления с точностью примерно 16 значащих цифр, что может в некоторых случаях приводить к небольшим численным ошибкам.

Пример 2

Предположим, что в диапазон ячеек А1:С3 введена матрица размера 3х3 .

Необходимо получить транспонированную матрицу.

Решение.

Курсор поставьте в ячейку, в которой требуется получить значение определителя, например, в А4.

Нажмите Вставка функции f_x

В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция – имя функции МОПРЕД. После этого щелкните на кнопке ОК.

В рабочее поле Массив введите диапазон исходной матрицы А1:С3.

Нажмите ОК. В ячейке А4 появится значение определителя матрицы – 6.

МОБР(массив) НАХОЖДЕНИЕ ОБРАТНОЙ МАТРИЦЫ- возвращает обратную матрицу для матрицы, хранящейся в массиве.

Массив — числовой массив с равным количеством строк и столбцов.

Обратные матрицы, как и определители, обычно используются для решения систем уравнений с несколькими неизвестными. Произведение матрицы на ее обратную — это единичная матрица, то есть квадратный массив, у которого диагональные элементы равны 1, а все остальные элементы равны 0.

В качестве примера того, как вычисляется обратная матрица, рассмотрим массив из двух строк и двух столбцов A1:B2, который содержит буквы a, b, c и d, представляющие любые четыре числа. В следующей таблице приведена обратная матрица для A1:B2:

	Столбец A	Столбец B
Строка 1	d/(ad-bc)	b/(bc-ad)
Строка 2	c/(bc-ad)	a/(ad-bc)

Некоторые квадратные матрицы не могут быть обращены, в таких случаях функция МОБР возвращает значение ошибки #ЧИСЛО!. Определитель такой матрицы равен 0.

Пример 3

Предположим, что в диапазон ячеек А1:С3 введена матрица размера 3х3

Необходимо получить обратную матрицу.

Решение.

Выделите блок ячеек под обратную матрицу, например блок ячеек А5:С7.

Нажмите Вставка функции f_x

В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция – имя функции МОБР. Щелкните на кнопке ОК.

В рабочее поле Массив введите диапазон исходной матрицы А1:С3.

Нажмите сочетание клавиш CTRL+SHIFT+ENTER.

В результате в диапазоне А5:А7 появится обратная матрица

СЛОЖЕНИЕ И ВЫЧИТАНИЕ МАТРИЦ

Складывать (вычитать) можно матрицы одного размера. Суммой матриц А=(a_ij) и В=(b_ij) размера mхn называется матрица С=А+В, элементы которой c_ij= a_ij+ b_ij для i=1,2, ...,m; j=1,2, ..., n (т.е. матрицы складываются поэлементно).

Пример 4

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

Необходимо найти матрицу С, являющуюся их суммой C=A+B.

Необходимо найти матрицу D, являющуюся их разностью D=A-C.

МУМНОЖ(массив1;массив2)УМНОЖЕНИЕ МАТРИЦ

Возвращает произведение матриц. Результатом является массив с таким же числом строк, как массив1 и с таким же числом столбцов, как массив2.

Массив1, массив2 — перемножаемые массивы.

Пусть А=(a_ij) размерности m?n, B=(b_ij) размерности n?p, тогда размерность произведения А х В равна m х p.

Матрица С называется произведением матриц А и В, если каждый ее элемент с_ij равен сумме произведений элементов i-той строки матрицы А на соответствующие элементы j-того столбца матрицы В

Из правила следует: что всегда можно перемножать две квадратные матрицы одного порядка, в результате получим квадратную матрицу того же порядка. В частности, квадратную матрицу всегда можно умножить саму на себя, т.е. возвести в квадрат.

Другим важным случаем является умножение матрицы–строки на матрицу–столбец, причём ширина первой должна быть равна высоте второй, в результате получим матрицу первого порядка (т.е. один элемент).

Пример 6

Пусть матрица А из рассмотренного примера введена в диапазон A1:D3, а матрица В - в диапазон А4:В7. Необходимо найти произведение этих матриц С.

Решение.

Выделите блок ячеек под результирующую матрицу, для этого требуется найти размер матрицы-произведения.

Нажмите Вставка функции f_x

В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция – имя функции МУМН. Щелкните на кнопке ОК.

В рабочее поле Массив1 введите диапазон исходной матрицы А - А1:D3, а в рабочее поле Массив2 введите диапазон исходной матрицы B – А4:B7

Нажмите сочетание клавиш CTRL+SHIFT+ENTER

Многие свойства, присущие операциям над числами, справедливы и для операций умножения матриц.

А(ВС)=(АВ)С – ассоциативность

А(В+С)=АВ+АС – дистрибутивность

(kA)B=A(kB)=k(AB)

Однако имеются и специфические свойства операций умножения матриц

Умножение матриц некоммутативно - АВ ВА

Если Е – единичная матрица, то ЕА=А; ВЕ=В. Таким образом, единичная матрица играет при умножении ту же роль, что и число 1 при умножении чисел.

Из того, что АхВ=0, не следует, что А=0 или В=0

В алгебре матриц нет действия деления. Выражение А/В не имеет смысла. Его заменяют два различных выражения В^-1 х А и А х В^-1 если существует В^-1. Для квадратных матриц возможна операция возведения в степень. По определению полагают, что А⁰=Е и А¹=А. Целой положительной степенью А^т(т > 1) квадратной матрицы А называется произведение т матриц, равных А, то есть:

РЕШЕНИЕ СИСТЕМ ЛИНЕЙНЫХ УРАВНЕНИИ

Многие прикладные задачи в технике, экономике и других областях сводятся к решению системы линейных уравнений, поэтому особенно важно уметь их решать.

Система п линейных уравнений с п неизвестными.

Пусть дана линейная система п уравнений с п неизвестными, где a_ip b_t (i = 1, 2,..., п; j= 1, 2, ...,n) — произвольные числа, называемые, соответственно, коэффициентами при переменных и свободными членами уравнений.

Такая запись называется системой линейных уравнений в нормальной форме.

Решением системы называется такая совокупность п чисел (х₁ = k₁, x₂ = k₂,..., х_n = k_n), при подстановке которых каждое уравнение системы обращается в верное равенство.

Система уравнений совместна, если она имеет хотя бы одно решение и несовместна, если она не имеет решений.

Если совместная система уравнений имеет единственное решение, она называется определенной; напротив, система уравнений называется неопределенной, если она имеет более одного решения.

Две системы уравнений являются равносильными или эквивалентными, если они имеют одно и то же множество решений. Система, равносильная данной может быть получена с помощью элементарных преобразований данной системы. Систему можно также записать в виде матричного уравнения: А?Х = В,

где А — матрица коэффициентов при переменных, или матрица системы:

 

X — матрица-столбец (вектор) неизвестных:

В — матрица-столбец (вектор) свободных членов:

В развернутом виде систему можно представить следующим образом:

Существует ряд методов решения системы, ориентированных на вычисления вручную: методы Крамера, Гаусса и т. д. Предполагая использование компьютера для проведения вычислений, наиболее целесообразно рассмотреть решение системы в общем виде (метод обратной матрицы). Будем считать, что квадратная матрица системы А_nn является невырожденной, то есть ее определитель |А| 0. В этом случае существует обратная матрица A^-1.

Решением системы методом обратной матрицы будет матрица-столбец.

Пример 8

Пусть необходимо решить систему: 3x+2y=7

4x-5y=40

Решение

Введите матрицу (в данном случае размера 2 х 2) в диапазон А1:В2

Вектор В = (7 40) введите в диапазон С1:С2. Блок ячеек под обратную матрицу АЗ:В4

Найдите обратную матрицу А^-1.

В результате, в диапазоне появится обратная матрица:

Умножением обратной матрицы на вектор В найдите вектор X. Выделите блок ячеек под результирующую матрицу (под вектор X). Ее размерность будет т х р, в данном примере 2 x 1. Например, выделите блок ячеек СЗ:С4;

В результате в диапазоне СЗ:С4 появится вектор X. Причем х = 5 будет находиться в ячейке СЗ, а у = -4 в ячейке С4.

Можно осуществить проверку найденного решения. Для этого найденный вектор Х необходимо подставить в исходное матричное уравнение А x Х=В

Выделите блок ячеек под результирующую матрицу (под вектор В). Ее размерность будет т x р, в данном примере 2 x 1. Например, выделите блок ячеек D1:D2

В результате в диапазоне D1:D2 появится вектор В, и, если система решена правильно, появившийся вектор будет равен исходному В = (7 40).

Пример 9

Ресторан специализируется на выпуске трех видов фирменных блюд: В1, В2, ВЗ, при этом используются ингредиенты трех типов SI, S2, S3. Нормы расхода каждого из них на одно блюдо и объем расхода ингредиентов на 1 день заданы таблицей:

Инградиент	Нормы расходов инградиентов на одно блюдо (у.е.)			Расход инградиентов на 1 день (у.е.)
	В1	В2	ВЗ
S1	5	3	4	2700
S2	2	1	1	900
S3	г	2	2	1600

Нужно найти ежедневный объем выпуска фирменных блюд каждого вида.

Решение. Пусть ежедневно ресторан выпускает х₁ блюд вида В1, х₂ блюд вида В2 и х₃ — блюд вида ВЗ. Тогда в соответствии с расходом ингредиентов каждого типа имеем систему:

Решаем систему аналогично решению примера 8.

Ответ.

x_l = 200, х₂ = 300, х₃ = 200

Рекомендуется сделать проверку, подставив найденные значения в уравнение системы.