Как возвести матрицу в степень в excel

Решение матриц в Excel

С матрицами в Excel выполняются такие операции, как: транспонирование, сложение, умножение на число / матрицу; нахождение обратной матрицы и ее определителя.

Транспонирование

Транспонировать матрицу – поменять строки и столбцы местами.

Сначала отметим пустой диапазон, куда будем транспонировать матрицу. В исходной матрице 4 строки – в диапазоне для транспонирования должно быть 4 столбца. 5 колонок – это пять строк в пустой области.

• 1 способ. Выделить исходную матрицу. Нажать «копировать». Выделить пустой диапазон. «Развернуть» клавишу «Вставить». Открыть меню «Специальной вставки». Отметить операцию «Транспонировать». Закрыть диалоговое окно нажатием кнопки ОК.
• 2 способ. Выделить ячейку в левом верхнем углу пустого диапазона. Вызвать «Мастер функций». Функция ТРАНСП. Аргумент – диапазон с исходной матрицей.

Нажимаем ОК. Пока функция выдает ошибку. Выделяем весь диапазон, куда нужно транспонировать матрицу. Нажимаем кнопку F2 (переходим в режим редактирования формулы). Нажимаем сочетание клавиш Ctrl + Shift + Enter.

Преимущество второго способа: при внесении изменений в исходную матрицу автоматически меняется транспонированная матрица.

Сложение

Складывать можно матрицы с одинаковым количеством элементов. Число строк и столбцов первого диапазона должно равняться числу строк и столбцов второго диапазона.

В первой ячейке результирующей матрицы нужно ввести формулу вида: = первый элемент первой матрицы + первый элемент второй: (=B2+H2). Нажать Enter и растянуть формулу на весь диапазон.

Умножение матриц в Excel

Условие задачи:

Чтобы умножить матрицу на число, нужно каждый ее элемент умножить на это число. Формула в Excel: =A1*$E$3 (ссылка на ячейку с числом должна быть абсолютной).

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

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

Для удобства выделяем диапазон, куда будут помещены результаты умножения. Делаем активной первую ячейку результирующего поля. Вводим формулу: =МУМНОЖ(A9:C13;E9:H11). Вводим как формулу массива.

Обратная матрица в Excel

Ее имеет смысл находить, если мы имеем дело с квадратной матрицей (количество строк и столбцов одинаковое).

Размерность обратной матрицы соответствует размеру исходной. Функция Excel – МОБР.

Выделяем первую ячейку пока пустого диапазона для обратной матрицы. Вводим формулу «=МОБР(A1:D4)» как функцию массива. Единственный аргумент – диапазон с исходной матрицей. Мы получили обратную матрицу в Excel:

Геометрическое определение

Прежде всего хотел бы отметить, что определитель существует только для квадратных матриц вида $\left$. Определитель — это число, которое cчитается по определённым правилам и является одной из характеристик этой матрицы (есть другие характеристики: ранг, собственные вектора, но об этом в других уроках).

Ну и что это за характеристика? Что он означает? Всё просто:

На первый взгляд это определение может показаться совершенно неадекватным. Но давайте не будем спешить с выводами — глянем на примеры. На самом деле всё элементарно, Ватсон:

Небольшое замечание по поводу системы обозначений. Кому-то наверняка не понравится, что я игнорирую «стрелочки» над векторами. Якобы так можно спутать вектор с точкой или ещё с чем.

Но давайте серьёзно: мы с вами уже взрослые мальчики и девочки, поэтому из контекста прекрасно понимаем, когда речь идёт о векторе, а когда — о точке. Стрелки лишь засоряют повествование, и без того под завязку напичканное математическими формулами.

И ещё. В принципе, ничто не мешает рассмотреть и определитель матрицы 1×1 — такая матрица представляет собой просто одну клетку, а число, записанное в этой клетке, и будет определителем

Но тут есть важное замечание:

И если вы хотите получить объём в классическом смысле этого слова, придётся взять модуль определителя, но сейчас не стоит париться об этом — всё равно через несколько секунд мы научимся считать любой определитель с любыми знаками, размерами и т.д.:)

вычислить определитель матрицы

Разложение определителя по строке или столбцу

Для вычисления определителя n-го порядка необходимо знать и использовать следующую теорему.

Теорема Лапласа. Определитель равен сумме произведений элементов какой-либо строки на их алгебраические дополнения, т.е.

Определение. Если в определителе n-го порядка выбрать произвольно p строк и p столбцов (p < n), то элементы, находящиеся на пересечении этих строк и столбцов, образуют матрицу порядка
.

Определитель этой матрицы называется минором исходного определителя. Например, рассмотрим определитель :

Из строк и столбцов с чётными номерами построим матрицу:

Определитель

называется минором определителя . Получили минор второго порядка. Ясно, что из  
можно построить различные миноры первого, второго и третьего порядка.

Если взять элемент
и вычеркнуть в определителе строку и столбец, на пересечении которых он стоит, то получим минор, называемый минором элемента
,
который обозначим через
:

.

Если минор

умножить на
 , где 3 + 2 – сумма номеров строки и столбца, на пересечении которых стоит элемент

то полученное произведение называется алгебраическим дополнением элемента

и обозначается
,

т.е.

Вообще, минор элемента

будем обозначать
,
а алгебраическое дополнение
 ,

причём

                  (4)

Для примера вычислим алгебраические дополнения элементов

и

определителя третьего порядка
:

По формуле (4) получим

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

Пример 3

здесь разложение проведено по элементам первой строки.

Пример 4.

Предварительно вычтем из первой и третьей строк элементы четвёртой строки, тогда будем иметь

В четвёртом столбце полученного определителя три элемента – нули. Поэтому выгоднее разложить этот определитель по элементам четвёртого столбца, так как три первых произведения будут нулями. Поэтому

Проверить решение можно с помощью калькулятора определителей
онлайн.

А в следующем примере показано, как вычисление определителя любого (в данном случае — четвёртого) порядка
можно свести к вычислению определителя второго порядка.

Пример 5. Вычислить определитель:

Вычтем из третьей строки элементы первой строки, а к элементам четвёртой строки прибавим
элементы первой строки, тогда будем иметь

В первом столбце все элементы, кроме первого, — нули. То есть, определитель можно уже разложить по
первому столбцу. Но нам очень не хочется вычислять определитель третьего порядка. Поэтому произведём ещё преобразования:
к элементам третьей строки прибавим элементы второй строки, умноженные на 2, а из элементов четвёртой строки вычтем элементы
второй строки. В результате определитель, являющийся алгебраическим дополнением, сам может быть разложен по первому столбцу
и нам останется только вычислить определитель второго порядка и не запутаться в знаках:

Вычисление обратной матрицы в EXCEL

history 7 декабря 2015 г.

Матрицы

Для вычисления обратной матрицы в MS EXCEL существует специальная функция МОБР() или англ. MINVERSE .

Понятие обратной матрицы определено только для квадратных матриц, определитель которых отличен от нуля.

СОВЕТ : О нахождении определителя матрицы читайте статью Вычисление определителя матрицы в MS EXCEL

Матрица А -1 называется обратной для исходной матрицы А порядка n, если справедливы равенства А -1 *А=Е и А*А -1 =Е, где Е единичная матрица порядка n.

Для вычисления обратной матрицы в MS EXCEL существует специальная функция МОБР() .

Если элементы исходной матрицы 2 х 2 расположены в диапазоне А8:В9 , то для получения транспонированной матрицы нужно (см. файл примера ):

• выделить диапазон 2 х 2, который не пересекается с исходным диапазоном А8:В9 , например, Е8:F9
• в Cтроке формул ввести формулу = МОБР (A8:B9) и нажать комбинацию клавиш CTRL+SHIFT+ENTER , т.е. нужно ввести ее как формулу массива (формулу можно ввести прямо в ячейку, предварительно нажав клавишу F2 )

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

Массив может быть задан не только как интервал ячеек, например A8:B9 , но и как массив констант , например =МОБР(<5;4: 3;2>) . Запись с использованием массива констант позволяет не указывать элементы в отдельных ячейках, а разместить их в ячейке вместе с функцией. Массив в этом случае указывается по строкам: например, сначала первая строка 5;4, затем через двоеточие записывается следующая строка 3;2. Элементы отделяются точкой с запятой.

Ссылка на массив также может быть указана как ссылка на именованный диапазон .

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

Если функция МОБР() вернула значение ошибки #ЗНАЧ!, то либо число строк в массиве не равно числу столбцов, либо какая-либо из ячеек в массиве пуста или содержит текст. Т.е. функция МОБР() пустую ячейку воспринимает не как содержащую 0 (как например, это делает СУММ() ), а как ошибочное значение.

Вычисление обратной матрицы с помощью матрицы из алгебраических дополнений

СОВЕТ : Этот раздел стоит читать только продвинутым пользователям MS EXCEL. Кроме того материал представляет только академический интерес, т.к. есть функция МОБР() .

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

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

• Вычисляем определитель матрицы А (далее — Det(A)) и убеждаемся, что он отличен от нуля (в противном случае матрица А необратима)
• Строим матрицу из алгебраических дополнений элементов исходной матрицы
• Транспонируем матрицу из алгебраических дополнений
• Умножаем каждый элемент транспонированной матрицы из алгебраических дополнений на 1/Det(A) и получаем обратную матрицу

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

Геометрическое определение

Прежде всего хотел бы отметить, что определитель существует только для квадратных матриц вида $\left$. Определитель — это число, которое cчитается по определённым правилам и является одной из характеристик этой матрицы (есть другие характеристики: ранг, собственные вектора, но об этом в других уроках).

Ну и что это за характеристика? Что он означает? Всё просто:

На первый взгляд это определение может показаться совершенно неадекватным. Но давайте не будем спешить с выводами — глянем на примеры. На самом деле всё элементарно, Ватсон:

Небольшое замечание по поводу системы обозначений. Кому-то наверняка не понравится, что я игнорирую «стрелочки» над векторами. Якобы так можно спутать вектор с точкой или ещё с чем.

Но давайте серьёзно: мы с вами уже взрослые мальчики и девочки, поэтому из контекста прекрасно понимаем, когда речь идёт о векторе, а когда — о точке. Стрелки лишь засоряют повествование, и без того под завязку напичканное математическими формулами.

И ещё. В принципе, ничто не мешает рассмотреть и определитель матрицы 1×1 — такая матрица представляет собой просто одну клетку, а число, записанное в этой клетке, и будет определителем

Но тут есть важное замечание:

И если вы хотите получить объём в классическом смысле этого слова, придётся взять модуль определителя, но сейчас не стоит париться об этом — всё равно через несколько секунд мы научимся считать любой определитель с любыми знаками, размерами и т.д.:)

Объем параллелепипеда

Связь между определителем и объемом не очевидна, однако мы можем предположить для начала, что все углы прямые, т. е. грани взаимно перпендикулярны, и мы имеем дело с прямоугольным параллелепипедом. Тогда объем его равен просто произведению длин ребер .

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

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

Извлекая корень, мы и приходим к требуемому соотношению:
определитель равняется объему. Знак при будет зависеть от того, образуют ребра правостороннюю систему координат вида или левостороннюю .

Если область не прямоугольна, то объем уже не равен произведению длин ребер. В плоском случае «объем» параллелограмма равен произведению длины основания на высоту .

Вектор длины есть разность между вектором второй строки и его проекцией на вектор первой строки.

Площадь паралелограмма равна .

Площади квадрата и параллелограмма.

Первый представляет собой единичный квадрат, и его площадь, равна 1. Второй есть параллелограмм с единичными основанием и высотой; его площадь не зависит от «сдвига», даваемого коэффициентом , и равна 1.

Вычисление определителя матрицы по определению (до 6 порядка включительно)

СОВЕТ
: Этот раздел стоит читать только продвинутым пользователям MS EXCEL. Кроме того материал представляет только академический интерес, т.к. есть функция МОПРЕД()
.

Как было показано выше для вычисления матриц порядка 2 и 3 существуют достаточно простые формулы и правила. Для вычисления определителя матриц более высокого порядка (без использования функции МОПРЕД()
) придется вспомнить определение:

Определителем квадратной матрицы порядка n
х n
является сумма, содержащая n!
слагаемых (=ФАКТР(n)
). Каждое слагаемое представляет собой произведение n
элементов матрицы, причем в каждом произведении содержится элемент из каждой строки и из каждого столбца матрицы А
. Перед k-ым
слагаемым появляется коэффициент (-1)
, если элементы матрицы А
в произведении упорядочены по номеру строки, а количество инверсий в k-ой
перестановке множества номеров столбцов нечетно.

где (α
1 ,α
2 ,…,α n
) — перестановка чисел от 1 до n
, N(α
1 ,α
2 ,…,α n
) — число , суммирование идёт по всем возможным перестановкам порядка n
.

Попытаемся разобраться в этом непростом определении на примере матрицы 3х3.

Для матрицы 3 х 3, согласно определения, число слагаемых равно 3!=6, а каждое слагаемое состоит из произведения 3-х элементов матрицы. Ниже приведены все 6 слагаемых, необходимых для вычисления определителя матрицы 3х3:

• а21*а12*а33
• а21*а32*а13
• а11*а32*а23
• а11*а22*а33
• а31*а22*а13
• а31*а12*а23

а21, а12 и т.д. — это элементы матрицы. Теперь поясним, как были сформированы индексы у элементов, т.е. почему, например, есть слагаемое а11*а22*а33, а нет а11*а22*а13.

Посмотрим на формулу выше (см. определение)

Предположим, что второй индекс у каждого элемента матрицы (от 1 до n) соответствует номеру столбца матрицы (хотя это может быть номер строки (это не важно т.к. определители матрицы и ее равны)

Таким образом, второй индекс у первого элемента в произведении всегда равен 1, у второго — 2, у третьего 3. Тогда первые индексы у элементов соответствуют номеру строки и, в соответствии с определением, должны определяться из перестановок чисел от 1 до 3, т.е. из перестановок множества (1, 2, 3).

Теперь понятно, почему среди слагаемых нет а11*а22*а13, т.к. согласно определения (в каждом произведении содержится элемент из каждой строки и из каждого столбца матрицы А

),
а в нашем слагаемом нет элемента из строки 3.

Примечание
: Перестановкой из n чисел множества (без повторов) называется любое упорядочивание данного множества, отличающиеся друг от друга лишь порядком входящих в них элементов. Например, дано множество их 3-х чисел: 1, 2, 3. Из этих чисел можно составить 6 разных перестановок: (1, 2, 3),

(1, 3, 2),

(2, 3, 1),

(2, 1, 3),

(3, 1, 2), (3, 2, 1). См. статью

Число перестановок множества из 3-х чисел =3!=6 (что, конечно, равно числу слагаемых в выражении для расчета определителя, т.к. каждому слагаемому соответствует своя перестановка). Для матрицы 3х3 все перестановки приведены в примечании выше. Можно убедиться, что в каждом слагаемом первые индексы у элементов равны соответствующим числам в перестановке. Например, для слагаемого а21*а12*а33 использована перестановка (2, 1, 3).

СОВЕТ
: Для матрицы 4 порядка существует 4! перестановок, т.е. 26, что соответствует 26 слагаемым, каждое из которых является произведением различных 4-х элементов матрицы. Все 26 перестановок можно найти в статье .

Теперь, когда разобрались со слагаемыми, определим множитель перед каждым слагаемым (он может быть +1 или -1). Множитель определяется через четность числа инверсий соответствующей перестановки.

Примечание
:

Об инверсиях перестановок (и четности числа инверсий) можно почитать, например, в статье

Например, первому слагаемому соответствует перестановка (2, 1, 3), у которой 1 инверсия (нечетное число) и, соответственно, -1 в степени 1 равно -1. Второму слагаемому соответствует перестановка (2, 3, 1), у которой 2 инверсии (четное число) и, соответственно, -1 в степени 2 равно 1 и т.д.

Сложив все слагаемые: (-1)*(а21*а12*а33)+(+1)*(а21*а32*а13)+(-1)*(а11*а32*а23)+(+1)*(а11*а22*а33)+(-1)*(а31*а22*а13)+(+1)*(а31*а12*а23) получим значение определителя.

В файле примера на листе 4+,
и
зменяя порядок матрицы с помощью , можно вычислить определитель матрицы до 6 порядка включительно.

Следует учитывать, что при вычислении матрицы 6-го порядка в выражении используется уже 720 слагаемых (6!). Для 7-го порядка пришлось бы сделать таблицу для 5040 перестановок и, соответственно, вычислить 5040 слагаемых! Т.е. без использования МОПРЕД()
не обойтись (ну, или можно вычислить определитель вручную методом Гаусса).

Пример функции МОПРЕД для вычисления детерминанта матрицы в Excel

Примеры использования функции МОПРЕД в Excel.

Пример 1. Одним из свойств матриц является то, что определитель (детерминант) исходной матрицы соответствует определителю транспонированной матрицы. Доказать справедливость этого суждения с использованием средств Excel.

Вид таблицы с данными:

Для получения транспонированной матрицы выделим соответствующий по количеству строк и столбцов диапазон ячеек и используем следующую формулу (формула массива CTRL+SHIFT+Enter):

A2:A4 – диапазон ячеек со значениями исходной матрицы.

В результате получим:

Рассчитаем детерминант для каждой матрицы отдельно:

A2:C4 и E2:G4 – диапазоны ячеек со значениями исходной и транспонированной матриц соответственно.

Во избежание промежуточных вычислений можно было использовать формулу массива CTRL+SHIFT+Enter:

В результате вычислений формул Excel детерминант – доказан!

Правильная расстановка индексов в матрице

Матрица — это просто таблица, заполненная числами. Нео тут ни при чём.

Одна из ключевых характеристик матрицы — это её размерность, т.е. количество строк и столбцов, из которых она состоит. Обычно говорят, что некая матрица $A$ имеет размер $\left$, если в ней имеется $m$ строк и $n$ столбцов. Записывают это так:

\\]

Или вот так:

\

Бывают и другие обозначения — тут всё зависит от предпочтений лектора/ семинариста/ автора учебника. Но в любом случае со всеми этими $\left$ и ${{a}_{ij}}$ возникает одна и та же проблема:

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

Поэтому давайте разберёмся с этим вопросом раз и навсегда. Для начала вспомним обычную систему координат из школьного курса математики:

Введение системы координат на плоскости

Помните её? У неё есть начало координат (точка $O=\left( 0;0 \right)$) оси $x$и $y$, а каждая точка на плоскости однозначно определяется по координатам: $A=\left( 1;2 \right)$, $B=\left( 3;1 \right)$ и т.д.

А теперь давайте возьмём эту конструкцию и поставим её рядом с матрицей так, чтобы начало координат находилось в левом верхнем углу. Почему именно там? Да потому что открывая книгу, мы начинаем читать именно с левого верхнего угла страницы — запомнить это легче лёгкого.

Но куда направить оси? Мы направим их так, чтобы вся наша виртуальная «страница» была охвачена этими осями. Правда, для этого придётся повернуть нашу систему координат. Единственно возможный вариант такого расположения:

Наложение системы координат на матрицу

Теперь всякая клетка матрицы имеет однозначные координаты $x$ и $y$. Например запись ${{a}_{24}}$ означает, что мы обращаемся к элементу с координатами $x=2$ и $y=4$. Размеры матрицы тоже однозначно задаются парой чисел:

Определение индексов в матрице

Просто всмотритесь в эту картинку внимательно. Поиграйтесь с координатами (особенно когда будете работать с настоящими матрицами и определителями) — и очень скоро поймёте, что даже в самых сложных теоремах и определениях вы прекрасно понимаете, о чём идёт речь.

Разобрались? Что ж, переходим к первому шагу просветления — геометрическому определению определителя.:)

Вычисление определителя матрицы по определению (до 6 порядка включительно)

СОВЕТ: Этот раздел стоит читать только продвинутым пользователям MS EXCEL. Кроме того материал представляет только академический интерес, т.к. есть функция МОПРЕД() .

Как было показано выше для вычисления матриц порядка 2 и 3 существуют достаточно простые формулы и правила. Для вычисления определителя матриц более высокого порядка (без использования функции МОПРЕД() ) придется вспомнить определение:

где ( α ₁, α ₂. α_n ) — перестановка чисел от 1 до n , N( α ₁, α ₂. α_n ) — число инверсий в перестановке, суммирование идёт по всем возможным перестановкам порядка n .

Попытаемся разобраться в этом непростом определении на примере матрицы 3х3.

Для матрицы 3 х 3, согласно определения, число слагаемых равно 3!=6, а каждое слагаемое состоит из произведения 3-х элементов матрицы. Ниже приведены все 6 слагаемых, необходимых для вычисления определителя матрицы 3х3:

а21, а12 и т.д. — это элементы матрицы. Теперь поясним, как были сформированы индексы у элементов, т.е. почему, например, есть слагаемое а11*а22*а33, а нет а11*а22*а13.

Теперь понятно, почему среди слагаемых нет а11*а22*а13, т.к. согласно определения (в каждом произведении содержится элемент из каждой строки и из каждого столбца матрицы А ), а в нашем слагаемом нет элемента из строки 3.

Примечание: Перестановкой из n чисел множества (без повторов) называется любое упорядочивание данного множества, отличающиеся друг от друга лишь порядком входящих в них элементов. Например, дано множество их 3-х чисел: 1, 2, 3. Из этих чисел можно составить 6 разных перестановок: (1, 2, 3), (1, 3, 2), (2, 3, 1), (2, 1, 3), (3, 1, 2), (3, 2, 1). См. статью Перестановки без повторений: Комбинаторика в MS EXCEL

СОВЕТ: Для матрицы 4 порядка существует 4! перестановок, т.е. 26, что соответствует 26 слагаемым, каждое из которых является произведением различных 4-х элементов матрицы. Все 26 перестановок можно найти в статье Перебор всех возможных Перестановок в MS EXCEL.

Теперь, когда разобрались со слагаемыми, определим множитель перед каждым слагаемым (он может быть +1 или -1). Множитель определяется через четность числа инверсий соответствующей перестановки.

Примечание: Об инверсиях перестановок (и четности числа инверсий) можно почитать, например, в статье Перестановки без повторений: Комбинаторика в MS EXCEL

Например, первому слагаемому соответствует перестановка (2, 1, 3), у которой 1 инверсия (нечетное число) и, соответственно, -1 в степени 1 равно -1. Второму слагаемому соответствует перестановка (2, 3, 1), у которой 2 инверсии (четное число) и, соответственно, -1 в степени 2 равно 1 и т.д.

Сложив все слагаемые: (-1)*(а21*а12*а33)+(+1)*(а21*а32*а13)+(-1)*(а11*а32*а23)+(+1)*(а11*а22*а33)+(-1)*(а31*а22*а13)+(+1)*(а31*а12*а23) получим значение определителя.

В файле примера на листе 4+, и зменяя порядок матрицы с помощью элемента управления Счетчик, можно вычислить определитель матрицы до 6 порядка включительно.

Следует учитывать, что при вычислении матрицы 6-го порядка в выражении используется уже 720 слагаемых (6!). Для 7-го порядка пришлось бы сделать таблицу для 5040 перестановок и, соответственно, вычислить 5040 слагаемых! Т.е. без использования МОПРЕД() не обойтись (ну, или можно вычислить определитель вручную методом Гаусса).

Что можно делать с матрицами

Вычисление обратной матрицы в Microsoft Excel