Функции для работы с матрицами в Excel
В программе Excel с матрицей можно работать как с диапазоном. То есть совокупностью смежных ячеек, занимающих прямоугольную область.
Адрес матрицы – левая верхняя и правая нижняя ячейка диапазона, указанные черед двоеточие.
Формулы массива
Построение матрицы средствами Excel в большинстве случаев требует использование формулы массива. Основное их отличие – результатом становится не одно значение, а массив данных (диапазон чисел).
Порядок применения формулы массива:
- Выделить диапазон, где должен появиться результат действия формулы.
- Ввести формулу (как и положено, со знака «=»).
- Нажать сочетание кнопок 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 Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Excel function for Matrix Multiplication with examples
You can work with a matrix in the Excel just like with a range. That is, a set of adjacent cells occupying a rectangular area.
The matrix address is the upper left and right lower cell of the range indicated by the colon.
Array formulas
Building a matrix of Excel tools in most cases requires the use of appropriate arrays. Their main difference is that the result is not one value but a data set (a range of numbers).
The procedure for applying the data set formula:
- Select the range where the result of the formula action should appear.
- Enter the formula (starting with the sign «=»).
- Press the kea combination Ctrl + Shift + Enter.
The data set formula in curly brackets is displayed in the formula bar.
Select the entire range and perform the appropriate actions to change or delete a data set formula. The same combination is used (Ctrl + Shift + Enter) to enter changes. Part of the data set cannot be changed.
Matrix operations in Excel
You can perform such operations with matrices in Excel as transposition, addition, multiplication by number/array; finding the inverse matrix and its determinant.
Transpose the matrices
Transposing the matrix is an act of changing the rows and columns in places.
First, note the empty range where we transpose the matrix. There are 4 lines in the original table and the range for the transposition should have 4 columns. For 5 columns there are should be five lines in an empty area, etc.
- Method #1. Select the original data array. Click «copy». Select an empty range. Expand the Paste button. Open the «Paste Special» menu (CTRL+ALT+V). Mark «Transpose» operation. Close the dialog by clicking OK.
- Method #2. Select the range B7:E11 with active cell B7 in the upper left corner of the empty range. Select function: «FORMULAS»-«Lookup and Reference»-«TRANSPOSE». The argument is a range with the original array data. Click OK. Now the function produces an error. Select the entire range where you want to transpose the matrix. Press the F2 (go to the formula editing mode). Press the key combination Ctrl + Shift + Enter.
The advantage of the second method: the transposed matrix automatically changes while making changes to the original.
Addition of a matrices
You can sum up matrices with the same number of elements. The number of rows and columns of the first range should be equal to the number of rows and columns of the second range.
In the first cell of the resulting matrix you need to enter a formula of the next form: = the first element of the first array + the first element of the second: (=A1+E1). Press Enter and stretch the formula to the full range.
Matrices multiplication in Excel
The task is next:
To multiply a matrix by a number you need to multiply each of its elements by this number. The formula in Excel: =A1*$E$3 ( a reference to a cell with a number must be absolute).
Let’s multiply the matrix with different ranges. It’s possible only to find the product of matrices if the number of columns of the first matrix is equal to the number of rows of the second one.
In the resulting matrix, the number of rows is equal to the number of rows of the first array, and the number of columns is equal to the number of columns of the second.
For convenience, we select the range where the multiplication results will be placed. We make the first cell of the resulting field active. Then select: «FORMULAS»-«Math and Trig»-«MMULT» We introduce the formula: = =MMULT(A1:C5,E1:H3). Enter as an array formula (CTRL+SHIFT+Enter).
The inverse matrices in Excel
It makes sense to find it if we are dealing with a square matrix (the number of rows and columns is the same).
The dimension of the inverse matrix corresponds to the size of the original. We use: «FORMULAS»-«Math and Trig»-«MINVERSE» function in Excel.
Select the first cell of the empty range for the inverse matrix. We introduce the formula «= MINVERSE(A1:D4)» as a data set function. The only argument is the range with the original. We got the inverse array in Excel:
Finding the matrices determinant
This is one single number that is found for a square matrix. We use: «FORMULAS»-«Math and Trig»-«MDETERM» function.
We put the cursor in any cell of the open sheet. Enter the formula: =MOPRED(A1:D4).
Thus, we performed actions with the matrixes using the built-in Excel features.
Примеры использования функции МОБР в Excel матрицах
Функция МОБР – это вычислительное определение матрицы. Она возвращает обратную матрицу для матрицы, хранящейся в массиве. Обратные матрицы, как и определители, обычно используются для решения систем уравнений с несколькими неизвестными. Некоторые квадратные матрицы не могут быть обращены: в таких случаях функция МОБР возвращает значение ошибки #ЧИСЛО!. Определитель такой матрицы равен = 0.
Описание использования функции МОБР в Excel
Как использовать функцию МОБР в Excel рассмотрим ниже на примерах. Но сначала ознакомимся как устроена данная функции.
Аргумент функции МОБР – это массив. Он может быть задан как диапазон ячеек, например A1:C3 как массив констант, например или как имя диапазона или массива. В случае, если хотя бы одна из ячеек массива пуста или содержит текст, функция возвращает значение ошибки #ЗНАЧ!
Массив должен иметь равное количество строк и столбцов. В случае, если они не равны, то функция МОБР также возвращает значение ошибки #ЗНАЧ!
Формулы, которые возвращают массивы, должны быть введены как формулы массива.
Для вывода обратного массива необходимо после выбора диапазона для данной функции нажать комбинацию клавиш 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, поэтому функция МОБР выводит в результате значение #ЧИСЛО!.
Рисунок 4 – Окончательный результат.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Матрицы в Excel: операции (умножение, деление, сложение, вычитание, транспонирование, нахождение обратной матрицы, определителя)
Пусть дана матрица А размерностью 3х4, с помощью функции =ТРАНСП() вычислим транспонированную матрицу А Т , причем размерность этой матрицы будет 4х3.
Выделим диапазон Н3:J6, в который будут введены значения транспонированной матрицы.
На вкладке Формулы выберем Вставить функцию, выберем категорию Ссылки и массивы — функция ТРАНСП — ОК.
В диалоговом окне Аргументы функции указываем диапазон массива В3:Е5, содержащего элементы матрицы А. Нажимаем на клавиатуре сочетание клавиш Shift+Ctrl и щелкаем левой кнопкой мыши по кнопке ОК.
ВАЖНО. Если просто нажать ОК, то программа вычислит значение только первой ячейки диапазона матрицы А Т .
Нажмите для увеличения
Мы получили транспонированную матрицу.
Нахождение обратной матрицы в Excel
Матрица А -1 называется обратной для матрицы А, если АА -1 =А -1 А=Е, где Е — единичная матрица. Следует отметить, что обратную матрицу можно найти только для квадратной матрицы (одинаковое количество строк и столбцов).
Пусть дана матрица А размерностью 3х3, найдем для неё обратную матрицу с помощью функции =МОБР().
Для этого выделим диапазон G3:I5, который будет содержать элементы обратной матрицы, на вкладке Формулы выберем Вставить функцию.
В диалоговом окне Вставка функции выберем категорию Математические — функция МОБР — ОК.
В диалоговом окне Аргументы функции указываем диапазон массива В3:D5, содержащего элементы матрицы А. Нажимаем на клавиатуре сочетание клавиш Shift+Ctrl и щелкаем левой кнопкой мыши по кнопке ОК.
ВАЖНО. Если просто нажать ОК, то программа вычислит значение только первой ячейки диапазона матрицы А -1 .
Нажмите для увеличения
Мы получили обратную матрицу.
Нахождение определителя матрицы в Excel
Определитель матрицы — это число, которое является важной характеристикой квадратной матрицы.
Как найти определить матрицы в Excel
Пусть дана матрица А размерностью 3х3, вычислим для неё определитель с помощью функции =МОПРЕД().
Для этого выделим ячейку Н4, в ней будет вычислен определитель матрицы, на вкладке Формулы выберем Вставить функцию.
В диалоговом окне Вставка функции выберем категорию Математические — функция МОПРЕД — ОК.
В диалоговом окне Аргументы функции указываем диапазон массива В3:D5, содержащего элементы матрицы А. Нажимаем ОК.
Нажмите для увеличения
Мы вычислили определитель матрицы А.
В заключение обратим внимание на важный момент. Он касается тех операций над матрицами, для которых мы использовали встроенные в программу функции, а в результате получали новую матрицу (умножение матриц, нахождение обратной и транспонированной матриц). В матрице, которая получилась в результате операции, нельзя удалить часть элементов. Т.е. если мы выделим, например, один элемент матрицы и нажмём Del, то программа выдаст предупреждение: Нельзя изменять часть массива.
Нажмите для увеличения
Мы можем удалить только все элементы этой матрицы.
Видеоурок
Кратко об авторе:
Шамарина Татьяна Николаевна — учитель физики, информатики и ИКТ, МКОУ «СОШ», с. Саволенка Юхновского района Калужской области. Автор и преподаватель дистанционных курсов по основам компьютерной грамотности, офисным программам. Автор статей, видеоуроков и разработок.
Спасибо за Вашу оценку. Если хотите, чтобы Ваше имя
стало известно автору, войдите на сайт как пользователь
и нажмите Спасибо еще раз. Ваше имя появится на этой стрнице.