Мопред в экселе что это

Обновлено: 06.07.2024

Редактор электронных таблиц MS Excel.
Матрицы.

Матрицы. Действия с матрицами. Решение систем линейных уравнений.

Матрицы. Действия с матрицами.

Матрица в Excel’е это некий диапазон чисел. Самые распространенные действия с матрицами следующие:

Размеры матриц

Функция в Excel’е

Размер того, что получится

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

мумнож(матрица1;матрица2)

Сложение матриц

матрица1+матрица2

Вычисление определителя

мопред(матрица)

Вычисление обратной

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

трансп(матрица)

Часто бывает удобно присваивать матрицам имена. Напомним, как это делается.

  1. Выделить матрицу.
  2. Меню=>Вставка=>Имя=>Присвоить…
  3. В графе “Имя” пишем имя матрицы. Например, “А”.
  4. ОК.

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

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

Пример 1. Подсчет определителя матрицы.

  1. Присваиваем матрице имя “A”. Матрица должна быть квадратной, иначе ничего не получится.
  2. Встаем в ту ячейку, где должен быть определитель.
  3. Вводим формулу “=мопред(A)”.
  4. Нажимаем Ctrl+Shift+Enter.

Пример 2. Вычисление обратной матрицы.

  1. Присваиваем матрице имя “A”. Матрица должна быть квадратной, иначе ничего не получится.
  2. Выделяем диапазон ячеек по размеру такой же, как сама матрица.
  3. Вводим формулу “=мобр(A)”.
  4. Нажимаем Ctrl+Shift+Enter.

Решение систем линейных уравнений.

Столбец x надо искать по формуле x=A -1 b. Т.е. алгоритм решения системы линейных уравнений такой:

  1. Присваиваем данной матрице имя “A”.
  2. Присваиваем данному столбцу имя “b”.
  3. Выделяем диапазон ячеек по размеру такой же, как вектор b.
  4. Вводим формулу “=мумнож(мобр(A);b)”.
  5. Нажимаем Ctrl+Shift+Enter.

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

Для вычисления определителя в MS EXCEL есть специальная функция МОПРЕД() . В аргументе функции необходимо указать ссылку на диапазон ячеек (массив), содержащий элементы матрицы (см. файл примера ).


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

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


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

Свойства определителя

Теперь о некоторых свойствах определителя (см. файл примера ):

  • Определитель транспонированной матрицы равен определителю исходной матрицы
  • Если в матрице все элементы хотя бы одной из строк (или столбцов) нулевые, определитель такой матрицы равен нулю
  • Если переставить местами две любые строки (столбца), то определитель полученной матрицы будет противоположен исходному (то есть, изменится знак)
  • Если все элементы одной из строк (столбца) умножить на одно и тоже число k, то определитель полученной матрицы будет равен определителю исходной матрицы, умноженному на k
  • Если матрица содержит строки (столбцы), являющиеся линейной комбинацией других строк (столбцов), то определитель =0
  • det(А)=1/det(А -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). См. статью Перестановки без повторений: Комбинаторика в MS EXCEL

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

СОВЕТ : Для матрицы 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 слагаемых! Т.е. без использования МОПРЕД() не обойтись (ну, или можно вычислить определитель вручную методом Гаусса).

Программированию нельзя научить, можно только научится

Вычисление определителя с помощью Excel

В Excel для вычисления определителя квадратной матрицы используется функция МОПРЕД. Функция имеет вид
МОПРЕД(массив).
Здесь массив – это числовой массив, в котором хранится квадратная матрица. При этом массив может быть задан как интервал ячеек, например, A1:C3, или как массив констант, например, .

Нахождение обратной матрицы с помощью Excel

В Excel для нахождения обратной матрицы используется функция МОБР, которая вычисляет обратную матрицу для матрицы, хранящейся в таблице в виде массива.
Функция имеет вид
МОБР(массив).
Здесь массив – это числовой массив, в котором хранится квадратная матрица. При этом массив может быть задан как интервал ячеек, например, A1:C3, или как массив констант, например, .

Уровень 1


Задание 1. Найти определитель матрицы .

Ход решения:



Рисунок 1. Матрица А

  1. Курсор поставьте в ячейку, в которой необходимо получить значение определителя, например в A4.
  2. Вызовите Мастер функции и в категории «Полный алфавитный перечень» найдите функцию МОПРЕД. После этого щелкните ОК.
  3. В появившемся окне укажите диапазон (A1:C3), в котором находится матрица, и нажмите ОК.
  4. В результате в ячейке появится значение определителя матрицы = 6.


Задание 2. Пусть дана матрица А в диапазоне A1:C3. Необходимо получить обратную матрицу.
.
Ход решения:

  1. Выделите блок ячеек A5:C7 (здесь будет обратная матрица).
  2. Вызовите Мастер функций и в категории «Полный алфавитный перечень» найдите функцию МОБР. После этого щелкните ОК.
  3. В появившемся окне укажите диапазон A1:C3 (исходная матрица) и нажмите сочетание клавиш CTRL+SHIFT+ENTER.
  4. В результате в диапазоне A5:C7 появится обратная матрица (рис.1):



Рисунок 1

Уровень 2

Задание 3: вычислить с помощью Excel определитель матрицы В.

Задание 4: вычислить с помощью Excel определитель из семестрового задания по своему варианту.

Уровень 3

Задание 5. Найдите матрицы, обратные матрицам А, B, C.



Вопросы преподавателя по пройденной теме.

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

Для вычисления определителя в MS EXCEL есть специальная функция МОПРЕД() . В аргументе функции необходимо указать ссылку на диапазон ячеек (массив), содержащий элементы матрицы (см. файл примера ).


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

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


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

Свойства определителя

Теперь о некоторых свойствах определителя (см. файл примера ):


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

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

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

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


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

Для матрицы 3 х 3, согласно определения, число слагаемых равно 3!=6, а каждое слагаемое состоит из произведения 3-х элементов матрицы. Ниже приведены все 6 слагаемых, необходимых для вычисления определителя матрицы 3х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). См. статью Перестановки без повторений: Комбинаторика в MS EXCEL

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

СОВЕТ : Для матрицы 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 слагаемых! Т.е. без использования МОПРЕД() не обойтись (ну, или можно вычислить определитель вручную методом Гаусса).

Функция МОПРЕД для нахождения детерминанта матрицы в Excel

Функция МОПРЕД в Excel используется для работы с прямоугольными матрицами. Задаваемыми в качестве статических массивов или диапазонов ячеек с числовыми данными, и вычисляет детерминант (определитель) исследуемой матрицы.

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

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

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

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

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


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

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

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


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

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


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


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

Решение системы линейных уравнений по методу Крамера в Excel

Пример 2. Решить систему линейных уравнений с использованием метода Крамера. Для расчета необходимо найти определители нескольких матриц.


Вид таблицы данных:


Для нахождения решений методом Крамера выделим три матрицы.

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


Так как детерминант основной матрицы (Матрица 1) не равен нулю, система имеет единственное решение. Для нахождения значения переменных X и Y используем формулы:


Принцип работы функции МОПРЕД в Excel

Функция МОПРЕД имеет следующую синтаксическую запись:

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

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

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

Функция возвращает определитель матрицы (матрица хранится в массиве).

МОПРЕД(массив)

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

Решение примера 1.2. в Microsoft Excel:

Найдем определитель матрицы .

1. Запускаем Microsoft Excel, если запущен, то переходим на новый лист.

2. Вводим матрицу А в диапазон ячеек А2:С4.

3. Выделяем свободную ячейку В6 куда будет выведен результат.

4. В меню Вставка>Функция выбираем функцию МОПРЕД. Вводим в поле массив диапазон ячеек А2:С4. Нажимаем ОК.

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

3. НАХОЖДЕНИЕ ОБРАТНОЙ МАТРИЦЫ.

Матрица называется обратной по отношению к квадратной матрице А, если при умножении этой матрицы на данную как справа, так и слева получается единичная матрица:

Алгоритм вычисления обратной матрицы:

1) Находим определитель исходной матрицы. Если , то матрица А – вырожденная и обратной матрицы не существует. Если , то матрица А – невырожденная и обратная матрица существует.

2) Находим матрицу , транспонированную к А.

3) Находим алгебраические дополнения элементов транспонированной матрицы и составляем из них присоединенную матрицу .

4) Вычисляем обратную матрицу по формуле:

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

Пример 1.3.Найти матрицу, обратную к данной:

Для нахождения обратной матрицы воспользуемся описанным выше алгоритмом.

1) Определитель матрицы (см. пример 2), т.е. матрица А – невырожденная и обратная матрица существует.

2) Находим матрицу , транспонированную к А:

3) Находим алгебраические дополнения элементов матрицы и составляем из них присоединенную матрицу :

4) Вычисляем обратную матрицу :

Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет

Нахождение обратной матрицы в Excel

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

Примечание: Найти обратную матрицу можно только при условии, что исходная является квадратной (количество столбцов и строк одинаковое). К тому же, ее определитель не должен равняться цифре 0.

Этап 1. Вычисляем определитель

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

  1. Переходим в свободную ячейку, в которой планируем производить расчеты, после чего кликаем по кнопке “Вставить функцию” (fx) слева от строки формул.
  2. В открывшемся окне вставки функций выбираем категорию “Математические”, в которой кликаем по оператору “МОПРЕД”, затем – по кнопке OK.
  3. В следующем окне нужно заполнить единственный аргумент функции – “Массив”, в значении которого указываем координаты нашей матрицы. Сделать это можно вручную, прописав адреса ячеек, используя клавиши клавиатуры. Либо можно сначала кликнуть внутри области ввода информации, затем зажав левую кнопку мыши выделить диапазон ячеек непосредственно в самой таблице. Когда все готово, нажимаем кнопку OK.
  4. В выбранной ячейке отобразился результат, а именно, определитель матрицы. С учетом наших данных получилось число 157894, что значит, что у нашей матрицы обратная матрица существует, так как определитель не равен нулю.

Этап 2. Находим обратную матрицу

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

  1. Встаем в ячейку, которая станет самым верхним левым элементом новой обратной матрицы. Заходим в окно Вставки функции, нажав на соответствующую кнопку.
  2. В категории “Математические” выбираем функцию “МОБР”, после чего щелкаем по кнопке OK.
  3. Аналогично заполнению значения аргумента “Массив” для функции МОПРЕД, рассмотренной в первом разделе, указываем координаты первичной матрицы, после чего нажимаем OK.
  4. Получаем требуемый результат в выбранной ячейке.
  5. Чтобы скопировать функцию в другие ячейки, выделяем область, которая совпадает по количеству столбцов и строк с начальной матрицей. Затем нажимаем клавишу F2 на клавиатуре, после чего жмем комбинацию Ctrl+Shift+Enter.
  6. Все готово. В результате выполненных действий нам удалось найти обратную матрицу.

Заключение

Читайте также: