Функция мобр в excel

Рассмотрим подробнее особенности вычисления обратной матрицы в Excel и примеры использования функции MOBR.

Для начала освежим в памяти, что обратная матрица - это матрица (записывается как A -1 ), умножение которой на исходную матрицу (A) дает единичную матрицу (E), другими словами, формула удовлетворяется:

 

 

 

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

 

Как найти обратную матрицу в Excel?

В отличие от транспонированной матрицы, вычисление обратной матрицы технически немного сложнее.
Обратную матрицу можно вычислить, построив алгебраическое дополнение и детерминант матрицы исходной матрицы.
Однако вычислительная сложность при использовании этого алгоритма имеет квадратичную зависимость от порядка матрицы.
Например, чтобы обработать квадратную матрицу 3-го порядка, нам придется дополнительно выполнить 9 матриц алгебраических членов, транспонировать полученную матрицу и разделить на определитель исходной матрицы элемент за элементом; это затрудняет работу в Excel.
Поэтому для нахождения обратной матрицы мы будем использовать стандартную функцию MOBR:

Функция МОБР

Синтаксис и описание функции MOBR в Excel:

MOBR(массив)
Возвращает обратную матрицу (массив, хранящийся в массиве).

• Array(обязательный аргумент) - это числовой массив, который содержит массив с одинаковым количеством столбцов и строк.

В качестве конкретного примера рассмотрим вычисление инверсного массива с помощью функции MOBR.
Предположим, что у нас есть следующая квадратная матрица порядка 3:

 

 

 

Выбираем диапазон пустых ячеек E2:G4, в которые поместим обратную матрицу.
Не снимая выделения с ячеек, введите формулу =MOBR(A2:C4) и нажмите Ctrl + Shift + Enter, чтобы вычислить формулу массива для этого диапазона:

 

 

 

 

При работе с функцией MOBR могут возникнуть следующие ошибки:

 

• Если исходный массив является вырожденным (детерминант равен нулю), функция вернет ошибку #Число!
• Если количество строк и столбцов в матрице не совпадает, функция вернет ошибку #ЧИСЛО!
• Функция также вернет ошибку #ЧИСЛО!, если хотя бы один из элементов матрицы пуст или записан в текстовом виде.

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

 

 

 

 

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

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

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

Подробнее: Лекция по информатике в Excel

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

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

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

 

 

 

 

При этом запускается мастер функций. В представленном списке записей найдите 'MOPRED', выделите этот элемент и нажмите кнопку 'OK'.

 

 

 

 

Откроется окно Аргументы. Установите курсор в поле "Массив". Выберите весь диапазон ячеек, в котором находится массив. Диапазон появится в поле и нажмите кнопку "OK".

 

 

 

 

 

 

 

 

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

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

 

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

 

 

 

 

В открывшемся списке выберите функцию MOBR. Нажмите на кнопку OK.

 

 

 

 

 

 

 

 

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

 

 

 

 

• Как мы видим, после этих шагов в выделенных ячейках вычисляется инверсный массив.

 

 

 

 

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

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

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

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

Вычисление обратной матрицы в MS EXCEL можно выполнить с помощью специальной функции MOBR() или MINVERSE.

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

СОВЕТ: Чтобы найти определитель матрицы, прочитайте раздел Вычисление определителя матрицы в MS EXCEL.

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

В MS EXCEL существует специальная функция MOBR() для вычисления обратной матрицы.

 

 

 

 

Если элементы исходной матрицы 2 x 2 находятся в диапазоне A8:B9, то для получения транспонированной матрицы (см. пример файла)

• выберите диапазон 2 x 2, который не перекрывается с исходным диапазоном A8:B9, например, E8:F9
• введите формулу = MOBR (A8:B9) в строке формул и нажмите CTRL+SHIFT+ENTER, т.е. введите ее как формулу массива (можно ввести формулу непосредственно в ячейку, предварительно нажав F2)

Подробнее: Метод секущей в excel

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

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

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

Некоторые квадратные массивы не могут быть инвертированы: в таких случаях MOBR() вернет ошибку #NUMBER! Массивы, детерминант которых равен 0, не могут быть инвертированы.

Если функция MOBR() возвращает ошибку #ЧИСЛО!, то либо количество строк в массиве не равно количеству столбцов, либо какая-то ячейка в массиве пуста или содержит текст. Другими словами, функция MOBR() считывает пустую ячейку не как содержащую 0 (как это делает SUMM()), а как недопустимое значение.

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

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

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

 

 

 

 

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

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

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

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

Функция MOBR - это вычислительное определение матрицы. Возвращает обратную матрицу для матрицы, хранящейся в массиве. Обратные матрицы, как и определители, обычно используются для решения систем уравнений с несколькими неизвестными. Некоторые квадратные матрицы невозможно инвертировать: в таких случаях функция MOBR возвращает значение ошибки #Number! Знаменатель такой матрицы равен = 0.

Описание использования функции МОБР в Excel

В следующих примерах мы опишем, как использовать функцию SOBR в Excel. Однако сначала давайте рассмотрим, как устроена функция.

Подробнее: Как написать текст в электронной таблице Excel

Аргументом функции MOBR является массив. Он может быть задан как диапазон ячеек, например A1:C3, как массив констант или как имя диапазона или массива. Если хотя бы одна ячейка в массиве пуста или содержит текст, функция вернет ошибку #NOTE!

Массив должен иметь одинаковое количество строк и столбцов. Если они не равны, функция MOBR также вернет ошибку #BELOW!

Формулы, возвращающие массивы, должны быть введены как формулы массивов.

Чтобы вывести инверсный массив, нажмите Ctrl+Shift+Enter после выбора диапазона функций, а не просто Enter.

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

Поиск обратной матрицы в Excel с помощью функции МОБР

Пример 1 Используя Excel, найдите обратную матрицу для матрицы в таблице 1.

Исходные данные
1	2
3	4

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

 

 

 

Рисунок 1 - Результат расчета.

Как найти валовый показатель по матрице взаимосвязей?

Пример 2. Взаимосвязь между тремя отраслями представлена матрицей прямых затрат A. Спрос (конечный продукт) задан вектором X. Найдите валовый выпуск отраслей X. Опишите используемые формулы, приведите распечатку со значениями и с формулами.

Исходные данные показаны на рисунке 2:

 

 

 

Рисунок 2 - Входные данные.

Данная задача связана с определением объема производства каждой из N отраслей промышленности для удовлетворения всех потребностей данной отрасли. Каждая отрасль данной промышленности выступает как производитель и как потребитель своей собственной продукции и продукции других отраслей. Задача межотраслевого баланса - найти вектор валового выпуска X, который при известной матрице прямых затрат дает заданный вектор конечного продукта Y.

Матричное решение этой проблемы:

Где E - единичная матрица.

Для решения задачи в примере мы используем следующие 4 матричные функции в Excel:

1. MOBR - нахождение обратной величины матрицы.
2. МОМНАЖ - умножение матрицы.
3. MOPRED - нахождение определителя матрицы.
4. MEDIN - нахождение единичной матрицы.

Результаты показаны на рисунке 3:

 

 

 

Рисунок 3 - Результат расчетов.