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

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

Приложение Excel выполняет целый ряд вычислений, связанных с матричными данными. Программа обрабатывает их, как диапазон ячеек, применяя к ним формулы массива. Одно из таких действий – это нахождение обратной матрицы. Давайте выясним, что представляет собой алгоритм данной процедуры.

Выполнение расчетов

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

Расчет определителя

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

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

Расчет обратной матрицы

Теперь можно преступить к непосредственному расчету обратной матрицы.

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

На этом расчет можно считать завершенным.

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

Мы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12369 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

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

СОВЕТ : О нахождении определителя матрицы читайте статью Вычисление определителя матрицы в 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) и получаем обратную матрицу

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

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

Матрица, для которой вы хотите вычислить обратное значение, должна быть квадратной матрицей. Это означает, что матрица должна иметь одинаковое количество строк и столбцов. Определитель для матрицы не должен быть нулевым. Если оно равно нулю, вы можете найти обратную матрицу. Теоретическая формула для вычисления обратной матрицы A выглядит следующим образом:

| | = Определитель матрицы А.

(прил. A) = присоединенный к матрице A.

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

Функция MINVERSE для вычисления обратной матрицы

Функция Excel MINVERSE позволяет пользователю вычислять инверсию любой квадратной матрицы, которая имеет ненулевой определитель. Обращенная матрица имеет размер, равный размеру исходной матрицы. Рассчитанная обратная матрица имеет такой же размер, как и исходная матрица.

Синтаксис:

Аргумент:

массив — это массив значений, представляющих матрицу.

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

Примеры обратной матрицы в Excel

Давайте разберемся, как создать обратную матрицу в Excel, на нескольких примерах.

Вы можете скачать этот шаблон обратной матрицы Excel здесь — шаблон обратной матрицы Excel

Пример # 1 — Вычислить инверсию матрицы 2X2

Матрица 2X2 имеет две строки и два столбца. Предположим, у нас есть квадратная матрица 2X2, как показано на рисунке ниже.

Шаг 1: Определите диапазон из 4 ячеек (поскольку у нас есть матрица 2X2) в том же листе Excel, который будет содержать обратную матрицу A. Здесь я выбрал ячейки A1: C5 в качестве диапазона для обратной матрицы A.

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

Шаг 2: В ячейке B4 начните вводить формулу для обратной матрицы = MINV . Вы увидите диапазон формул, связанных с ключевым словом. Дважды щелкните мышью, чтобы выбрать МИНВЕРС из них, чтобы можно было вычислить инверсию матрицы А. Обязательно выбрать все ячейки, в которых будет вычисляться ваша инверсия.

Шаг 3: Укажите аргумент массива для функции MINVERSE как B1: C2 и закройте скобки, чтобы завершить формулу. Обратите внимание, что массив, который мы предоставляем в качестве аргумента функции MINVERSE, состоит из ячеек, которые имеют значения для исходной матрицы A.

Шаг 4: Чтобы увидеть результат формулы каждый раз, когда нам нужно нажать клавишу Enter. Но в этом случае вам нужно нажать клавиши Ctrl + Shift + Enter, чтобы формула была преобразована в формулу массива, которая выглядит следующим образом (= MINVERSE (B1: C2)) и работает вместе со всеми ячейками, связанными с инверсией A.

Вы можете видеть по ячейкам B1: C2 матрица, обратная к исходной матрице A.

Мы также можем проверить, правильно ли перехвачено обратное, полученное через функцию MINVERSE, или нет. Способ проверить это — умножить матрицы A и A -1 . Умножение должно привести к единичной матрице.

Мы можем добиться умножения матриц с помощью функции MMULT в Excel. Это умножает матрицы. Смотрите вывод в массиве ячеек B1: C5.

Пример № 2 — Вычислить инверсию матрицы 4X4

Шаг 1: Введите матрицу 4X4 в ячейки A1: E4, как показано на скриншоте ниже. Это матрица, для которой нам нужно вычислить обратную матрицу.

Шаг 2: Выберите ячейки от A6 до E9. Это ячейки, в которых мы будем вычислять обратную матрицу 4X4 с именем A.

Шаг 3: Сохраняя все выбранные ячейки, в ячейке B6 начните вводить формулу для обратной матрицы как = MINV . В списке формул, связанных с ключевым словом, дважды щелкните мышью, чтобы выбрать MINVERSE.

Шаг 4: Используйте ссылку на массив B1: E4 в качестве аргумента массива для этой функции и закройте скобки, чтобы завершить формулу.

Шаг 5: Вместо того чтобы нажимать клавишу Enter, как обычно, одновременно нажмите клавиши Ctrl + Shift + Enter, чтобы вычислить обратные значения для всех ячеек в B1: E4. Если вы этого не сделаете, формула не будет преобразована в формулу массива и будет применена только к текущей ячейке, и если вы попытаетесь перетащить ее для других ячеек, это приведет к ошибке.

Вот как мы можем вычислить обратную матрицу в Excel, используя функцию MINVERSE. Мы также можем проверить с помощью функции MMLUT, правильно ли вычислено обратное или нет.

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

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

То, что нужно запомнить

• Если в данной матрице есть пустая ячейка или нечисловое значение, MINVERSE выдаст вам #VALUE! ошибка.
• В полученной матрице, если вы выберете несколько дополнительных ячеек, вы получите # N / A error.
• Если данная матрица является особой матрицей (для которой не существует обратной), вы получите #NUM! ошибка.
• Рекомендуется использовать MINVERSE в качестве формулы массива. В противном случае вы можете получить странные результаты по всем клеткам. Например, получение ошибок значений при перетаскивании формулы по строкам.
• Если вы не хотите, чтобы она использовалась в качестве формулы массива, вам нужно ввести одну и ту же формулу во все ячейки, чтобы получить результат.

Рекомендуемые статьи

Это руководство по обратной матрице в Excel. Здесь мы обсуждаем Как создать Обратную Матрицу в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

Матрица в Excel

Excel – это созданная корпорацией Microsof программа, предназначенная для работы с электронными таблицами.

Введение

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

Формулы массива

Матрица, по сути, является массивом и для работы с ними применяются соответствующие формулы. Главным их отличием от стандартных формул считается то, что обычные стандартные формулы могут вывести только одну величину. Чтобы использовать формулы работы с массивами, следует выполнить такой набор операций:

1. Осуществить выделение участка ячеек, куда следует вывести значения.
2. Задать требуемую формулу для вычислений.
3. Нажать клавишный набор Ctrl + Shift +Enter.

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

Операции с матрицами

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

1. Нужно выполнить выделение матрицы и сделать её копию.
2. Выполнить выделение диапазона ячеек для вставки транспонируемого диапазона.
3. Открыть окно «Специальная вставка».
4. Выбрать кнопку «Транспонировать» и нажать ОК.

Готовые работы на аналогичную тему

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

Рисунок 1. Окно программы. Автор24 — интернет-биржа студенческих работ

В качестве параметра функции используется диапазон, соответствующий изначальной матрице. Но после того, как будет нажата клавиша ОК, появится сообщение об ошибке, поскольку вставляемая функция не определена в качестве формулы массива. То есть далее надо сделать следующее:

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

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

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

Рисунок 2. Пример. Автор24 — интернет-биржа студенческих работ

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

= Начальный компонент первой матрицы + Начальный компонент второй матрицы

Затем следует подтвердить задание формулы клавишей Enter и применить функцию авто заполнения (квадрат в нижнем правом углу) для копирования всех величин в новую матрицу. Итог приведён на рисунке ниже:

Рисунок 3. Итог. Автор24 — интернет-биржа студенческих работ

Далее рассмотрим операцию умножения. Имеется следующая таблица, все элементы которой необходимо умножить на двенадцать:

Рисунок 4. Таблица. Автор24 — интернет-биржа студенческих работ

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

И результирующую матрицу:

Рисунок 5. Результирующая матрица. Автор24 — интернет-биржа студенческих работ

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

Рисунок 6. Перемножение матриц. Автор24 — интернет-биржа студенческих работ

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

Далее следует нажать комбинацию клавиш Ctrl + Shift + Enter, чтобы увидеть итог:

Рисунок 7. Итог. Автор24 — интернет-биржа студенческих работ

Далее рассмотрим пример обратной матрицы. Если матрица (её диапазон) квадратной формы, то есть число ячеек по вертикали равно числу ячеек по горизонтали, то значит, при необходимости, можно определить обратную матрицу. Это можно сделать при помощи функции МОБР. Сначала нужно сделать выделение первой ячейки матрицы, куда будет вставлена обратная матрица. В неё нужно ввести формулу:

В качестве аргумента нужно указать диапазон, для которого следует сформировать обратную матрицу. Далее нужно использовать комбинацию клавиш Ctrl + Shift + Enter.

Рисунок 8. Окно программы. Автор24 — интернет-биржа студенческих работ

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

Далее рассмотрим ещё один пример вычислений. Имеется матрица А, размером три на четыре. Есть, так же, некоторое число k, записанное вне матрицы. Когда будет выполнена операция умножения матрицы на это число, возникнет диапазон величин, который имеет такие же размеры, но все его компоненты умножены на k:

Рисунок 9. Окно программы. Автор24 — интернет-биржа студенческих работ

Диапазон B3:E5 является исходной матрицей, подлежащей умножению на число k, расположенному в клетке H4. Итоговая матрица будет располагаться в диапазоне K3:N5. Исходная матрица обозначается как А, а итоговая как В. Итоговая матрица В будет образована умножением матрицы А на величину k. Формула для вычислений записывается в ячейку К3: