Мопред в экселе что это
Функция МОПРЕД
В этой статье описаны синтаксис формулы и использование MDETERM в Microsoft Excel.
Описание
Возвращает определитель матрицы (матрица хранится в массиве).
Синтаксис
Аргументы функции МОПРЕД описаны ниже.
Массив Обязательный. Числовой массив с равным количеством строк и столбцов.
Замечания
Массив может быть задан как интервал ячеек, например A1:C3, как массив констант, например <1;2;3:4;5;6:7;8;9>, как имя для интервала или массива.
Функция МОПРЕД возвращает значение ошибки #ЗНАЧ! в случаях, указанных ниже.
Если какая-либо ячейка в массиве пуста или содержит текст.
Если количество строк в массиве не равно количеству столбцов.
Определитель матрицы — это число, вычисляемое на основе значений элементов массива. Для массива A1:C3, состоящего из трех строк и трех столбцов, определитель вычисляется следующим образом:
МОПРЕД(A1:C3)
равно A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*C1)
Определители матриц обычно используются при решении систем уравнений с несколькими неизвестными.
Функция МОПРЕД производит вычисления с точностью примерно 16 значащих цифр, что может в некоторых случаях приводить к незначительным ошибкам. Например, определитель сингулярной матрицы отличается от нуля на 1E-16.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Вычисление определителя матрицы в EXCEL
history 30 ноября 2015 г.
Вычислим определитель (детерминант) матрицы с помощью функции МОПРЕД() или англ. MDETERM, разложением по строке/столбцу (для 3 х 3) и по определению (до 6 порядка).
Определитель матрицы (det) можно вычислить только для квадратных матриц, т.е. у которых количество строк равно количеству столбцов.
В файле примера для матрицы 3 х 3 определитель также вычислен через разложение по столбцу и по правилу Саррюса.
Свойства определителя
Теперь о некоторых свойствах определителя (см. файл примера ):
Вычисление определителя матрицы по определению (до 6 порядка включительно)
Как было показано выше для вычисления матриц порядка 2 и 3 существуют достаточно простые формулы и правила. Для вычисления определителя матриц более высокого порядка (без использования функции МОПРЕД() ) придется вспомнить определение:
Попытаемся разобраться в этом непростом определении на примере матрицы 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).
Примечание : Об инверсиях перестановок (и четности числа инверсий) можно почитать, например, в статье Перестановки без повторений: Комбинаторика в MS EXCEL
Сложив все слагаемые: (-1)*(а21*а12*а33)+(+1)*(а21*а32*а13)+(-1)*(а11*а32*а23)+(+1)*(а11*а22*а33)+(-1)*(а31*а22*а13)+(+1)*(а31*а12*а23) получим значение определителя.
Следует учитывать, что при вычислении матрицы 6-го порядка в выражении используется уже 720 слагаемых (6!). Для 7-го порядка пришлось бы сделать таблицу для 5040 перестановок и, соответственно, вычислить 5040 слагаемых! Т.е. без использования МОПРЕД() не обойтись (ну, или можно вычислить определитель вручную методом Гаусса).
Примеры использования функции МОБР в Excel матрицах
Функция МОБР – это вычислительное определение матрицы. Она возвращает обратную матрицу для матрицы, хранящейся в массиве. Обратные матрицы, как и определители, обычно используются для решения систем уравнений с несколькими неизвестными. Некоторые квадратные матрицы не могут быть обращены: в таких случаях функция МОБР возвращает значение ошибки #ЧИСЛО!. Определитель такой матрицы равен = 0.
Описание использования функции МОБР в Excel
Как использовать функцию МОБР в Excel рассмотрим ниже на примерах. Но сначала ознакомимся как устроена данная функции.
Аргумент функции МОБР – это массив. Он может быть задан как диапазон ячеек, например A1:C3 как массив констант, например <1;2;3:4;5;6:7;8;9>или как имя диапазона или массива. В случае, если хотя бы одна из ячеек массива пуста или содержит текст, функция возвращает значение ошибки #ЗНАЧ!
Массив должен иметь равное количество строк и столбцов. В случае, если они не равны, то функция МОБР также возвращает значение ошибки #ЗНАЧ!
Формулы, которые возвращают массивы, должны быть введены как формулы массива.
Для вывода обратного массива необходимо после выбора диапазона для данной функции нажать комбинацию клавиш Ctrl+Shift+Enter, а не просто Enter.
Функция МОБР производит вычисления с точностью до 16 значащих цифр, что может привести к незначительным ошибкам округления. Рассмотрим применение данной функции на конкретных примерах.
Поиск обратной матрицы в Excel с помощью функции МОБР
Пример 1. Используя пакет Excel, найти обратную матрицу для матрицы, приведенной в таблице 1.
| Исходные данные | ||
| 1 | 2 | |
| 3 | 4 | |
Для решения данной задачи открывает пакет Excel, в произвольной ячейке вводим исходные данные, дальше выбираем функцию МОБР. В качестве массива выбираем диапазон с введенными данными и контролируем полученный результат. В Excel общий вид функции выглядит следующим образом:
Рисунок 1 – Результат расчета.
Как найти валовый показатель по матрице взаимосвязей?
Пример 2. Связь между тремя отраслями представлена матрицей прямых затрат А. Спрос (конечный продукт) задан вектором X. Найти валовой выпуск продукции отраслей Х. Описать используемые формулы, представить распечатку со значениями и с формулами.
Исходные данные приведены на рисунке 2:
Рисунок 2 – Исходные данные.
Данная задача связана с определением объема производства каждой из N отраслей, чтобы удовлетворить все потребности в продукции данной отрасли. При этом каждая отрасль выступает и как производитель некоторой продукции и как потребитель своей и произведенной другими отраслями продукции. Задача межотраслевого баланса – отыскание такого вектора валового выпуска X, который при известной матрице прямых затрат обеспечивает заданный вектор конечного продукта Y.
Матричное решение данной задачи:
где Е – единична матрица.
Для решения задачи в примере используем следующие 4 функции для работы с матрицами в Excel:
Результаты приведены на рисунке 3:
Рисунок 3 – Результат вычислений.
Функция МОБР возвращает ошибку #ЧИСЛО!
Пример 3. Найти обратную матрицу для матрицы, приведенной в таблице 2.
| Исходные данные | ||
| 1 | 2 | 4 |
| 2 | 4 | 8 |
| 4 | 8 | 16 |
Результат решения приведен на рисунке 4. Видно, что определитель данной матрицы равен 0, поэтому функция МОБР выводит в результате значение #ЧИСЛО!.
Функция МОПРЕД для нахождения детерминанта матрицы в Excel
Функция МОПРЕД в Excel используется для работы с прямоугольными матрицами. Задаваемыми в качестве статических массивов или диапазонов ячеек с числовыми данными, и вычисляет детерминант (определитель) исследуемой матрицы.
Матрица – математический объект, состоящий из совокупности строк из столбцов, каждый элемент которых содержит определенное числовое значение. Детерминант – один из основных вычисляемых параметров матрицы, характеризующих ее ключевые свойства.
Пример функции МОПРЕД для вычисления детерминанта матрицы в Excel
Примеры использования функции МОПРЕД в Excel.
Пример 1. Одним из свойств матриц является то, что определитель (детерминант) исходной матрицы соответствует определителю транспонированной матрицы. Доказать справедливость этого суждения с использованием средств Excel.
Вид таблицы с данными:
Для получения транспонированной матрицы выделим соответствующий по количеству строк и столбцов диапазон ячеек и используем следующую формулу (формула массива CTRL+SHIFT+Enter):
В результате получим:
Рассчитаем детерминант для каждой матрицы отдельно:
Во избежание промежуточных вычислений можно было использовать формулу массива CTRL+SHIFT+Enter:
В результате вычислений формул Excel детерминант – доказан!
Решение системы линейных уравнений по методу Крамера в Excel
Пример 2. Решить систему линейных уравнений с использованием метода Крамера. Для расчета необходимо найти определители нескольких матриц.
Вид таблицы данных:
Для нахождения решений методом Крамера выделим три матрицы.
Если детерминант первой матрицы равен нулю, исходная система уравнений имеет бесконечное число решений. Проверим это условие с помощью формулы:
Так как детерминант основной матрицы (Матрица 1) не равен нулю, система имеет единственное решение. Для нахождения значения переменных X и Y используем формулы:
Принцип работы функции МОПРЕД в Excel
Функция МОПРЕД имеет следующую синтаксическую запись:
Единственным аргументом рассматриваемой функции является массив, который обязателен для заполнения. Он может быть указан в виде статического массива или ссылки на диапазон ячеек.
Определитель матрицы в Excel
Для того, чтобы вычислить определитель матрицы в Excel совершенно необязательно производить сложные вычисления суммируя различные произведения элементов матрицы. Рассмотрим простой способ вычисления определителя матрицы.
Вычислить определитель матрицы второго порядка достаточно просто, третьего порядка уже куда сложнее, а с расчетом определителя четвертого порядка точно справится не каждый.
Однако с помощью Excel эта задача легко решается вне зависимости от порядка матрицы.
Как найти определитель матрицы в Excel
Предположим нам необходимо найти определитель следующей матрицы:
Если искать определитель вручную, то нам потребуется произвести расчет для 24 (в общем случае в определитель матрицы порядка n входит n! слагаемых) элементов определителя, поэтому прибегнем к решению с помощью инструментов Excel.
В Excel есть стандартная функция МОПРЕД, которая позволяет вычислить определитель матрицы.
Описание функции МОПРЕД
МОПРЕД([массив])
Возвращает определитель матрицы (матрица хранится в массиве).
Пример вычисления определителя матрицы в Excel
В качестве аргумента функции МОПРЕД выделяем диапазон ячеек A2:D5 и в результате получаем: