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

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

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

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

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

1. Выделяем любую пустую ячейку на листе, куда будут выводиться результаты вычислений. Жмем на кнопку «Вставить функцию», размещенную около строки формул.
2. Запускается Мастер функций. В перечне записей, который он представляет, ищем «МОПРЕД», выделяем этот элемент и жмем на кнопку «OK».
3. Открывается окно аргументов. Ставим курсор в поле «Массив». Выделяем весь диапазон ячеек, в котором расположена матрица. После того, как его адрес появился в поле, жмем на кнопку «OK».
4. Программа производит расчет определителя. Как видим, для нашего конкретного случая он равен – 59, то есть не тождественен нулю. Это позволяет сказать, что у данной матрицы существует обратная.

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

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

1. Выделяем ячейку, которая должна стать верхней левой ячейкой обратной матрицы. Переходим в Мастер функций, кликнув по значку слева от строки формул.
2. В открывшемся списке выбираем функцию МОБР. Жмем на кнопку «OK».
3. В поле «Массив», открывшегося окна аргументов функции, устанавливаем курсор. Выделяем весь первичный диапазон. После появления его адреса в поле, жмем на кнопку «OK».
4. Как видим, появилось значение только в одной ячейке, в которой была формула. Но нам нужна полноценная обратная функция, поэтому следует скопировать формулу в другие ячейки. Выделяем диапазон, равнозначный по горизонтали и вертикали исходному массиву данных. Жмем на функциональную клавишу F2, а затем набираем комбинацию Ctrl+Shift+Enter. Именно последняя комбинация предназначена для обработки массивов.
5. Как видим, после этих действий обратная матрица вычислена в выделенных ячейках.

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

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

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

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

Помогла ли вам эта статья?

Да Нет

8 июня 2013 Автор: Бакытжан

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

Прежде всего, уясним одно правило:  Матрица имеет обратную только тогда, когда ее определитель не равен нулю.  А вот и задание: найдите матрицу, обратную к матрице А, где

 

Вычислять определитель этой матрицы мы умеем. Я его уже вычислил.

Он оказался равен -4, а  это значит, что у нашей матрицы есть обратная (если бы определитель оказался равен нулю, то мы сказали бы что матрица не имеет обратную и немедленно прекратили все вычисления). Теперь отметим ячейку, с которой начнем записывать ответ. Я отметил ячейку E1.  Нажимаем Формулы, затем Математические и в появившемся окне находим  МОБР

После нажатия появляется вот такое окно, в котором надо вписать адреса ячеек, в которых находятся элементы матрицы  в Массив

У нас элементы записаны в ячейки начиная с А1 и заканчивая в С3 , поэтому так и записываем (смотрите картинку)

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

Чтобы виден был весь ответ, выполняем следующие действия: Начиная с  ячейки Е1 выделяем три строчки и три столбца (именно столько было у исходной матрицы и столько же будет у обратной)

нажимаем клавишу F2,  а затем на одновременно на три клавиши  Ctrl+Shift+Enter.

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

На этом все. Крепких вам знаний.

Рубрика: EXCEL в помощь, Статьи.
Метки: EXCEL, ИКТ, матрица, обратная матрица

Навигация по записям

Предыдущий пост:    

Следующий пост:    

Как вычислить определитель при помощи MS EXCEL

→

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

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

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

Построение матрицы средствами Excel в большинстве случаев требует использование формулы массива. Основное их отличие – результатом становится не одно значение, а массив данных (диапазон чисел).

Порядок применения формулы массива:

1. Выделить диапазон, где должен появиться результат действия формулы.
2. Ввести формулу (как и положено, со знака «=»).
3. Нажать сочетание кнопок Ctrl + Shift + Ввод.

В строке формул отобразится формула массива в фигурных скобках.

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

Решение матриц в 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:

Нахождение определителя матрицы

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

Ставим курсор в любой ячейке открытого листа. Вводим формулу: =МОПРЕД(A1:D4).

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

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

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

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

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

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

Функция МОБР

Синтаксис и описание функции МОБР в Excel:

МОБР(массив)
Возвращает обратную матрицу (матрица хранится в массиве).

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

Рассмотрим расчет обратной матрицы посредством функции МОБР на конкретном примере.
Предположим у нас имеется следующая квадратная матрица 3-го порядка:

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

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

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

Удачи вам и до скорой встречи на страницах блога Tutorexcel.ru!