Функции для работы с матрицами в Excel
В программе Excel с матрицей можно работать как с диапазоном. То есть совокупностью смежных ячеек, занимающих прямоугольную область.
Адрес матрицы – левая верхняя и правая нижняя ячейка диапазона, указанные черед двоеточие.
Формулы массива
Построение матрицы средствами Excel в большинстве случаев требует использование формулы массива. Основное их отличие – результатом становится не одно значение, а массив данных (диапазон чисел).
Порядок применения формулы массива:
В строке формул отобразится формула массива в фигурных скобках.
Чтобы изменить или удалить формулу массива, нужно выделить весь диапазон и выполнить соответствующие действия. Для введения изменений применяется та же комбинация (Ctrl + Shift + Enter). Часть массива изменить невозможно.
Решение матриц в Excel
С матрицами в Excel выполняются такие операции, как: транспонирование, сложение, умножение на число / матрицу; нахождение обратной матрицы и ее определителя.
Транспонирование
Транспонировать матрицу – поменять строки и столбцы местами.
Сначала отметим пустой диапазон, куда будем транспонировать матрицу. В исходной матрице 4 строки – в диапазоне для транспонирования должно быть 4 столбца. 5 колонок – это пять строк в пустой области.
Нажимаем ОК. Пока функция выдает ошибку. Выделяем весь диапазон, куда нужно транспонировать матрицу. Нажимаем кнопку F2 (переходим в режим редактирования формулы). Нажимаем сочетание клавиш Ctrl + Shift + Enter.
Преимущество второго способа: при внесении изменений в исходную матрицу автоматически меняется транспонированная матрица.
Сложение
Складывать можно матрицы с одинаковым количеством элементов. Число строк и столбцов первого диапазона должно равняться числу строк и столбцов второго диапазона.
В первой ячейке результирующей матрицы нужно ввести формулу вида: = первый элемент первой матрицы + первый элемент второй: (=B2+H2). Нажать Enter и растянуть формулу на весь диапазон.
Умножение матриц в Excel
Чтобы умножить матрицу на число, нужно каждый ее элемент умножить на это число. Формула в Excel: =A1*$E$3 (ссылка на ячейку с числом должна быть абсолютной).
Умножим матрицу на матрицу разных диапазонов. Найти произведение матриц можно только в том случае, если число столбцов первой матрицы равняется числу строк второй.
В результирующей матрице количество строк равняется числу строк первой матрицы, а количество колонок – числу столбцов второй.
Для удобства выделяем диапазон, куда будут помещены результаты умножения. Делаем активной первую ячейку результирующего поля. Вводим формулу: =МУМНОЖ(A9:C13;E9:H11). Вводим как формулу массива.
Обратная матрица в Excel
Ее имеет смысл находить, если мы имеем дело с квадратной матрицей (количество строк и столбцов одинаковое).
Размерность обратной матрицы соответствует размеру исходной. Функция Excel – МОБР.
Выделяем первую ячейку пока пустого диапазона для обратной матрицы. Вводим формулу «=МОБР(A1:D4)» как функцию массива. Единственный аргумент – диапазон с исходной матрицей. Мы получили обратную матрицу в Excel:
Нахождение определителя матрицы
Это одно единственное число, которое находится для квадратной матрицы. Используемая функция – МОПРЕД.
Ставим курсор в любой ячейке открытого листа. Вводим формулу: =МОПРЕД(A1:D4).
Таким образом, мы произвели действия с матрицами с помощью встроенных возможностей Excel.
Работа с матрицей в Power View
Важно: В Excel для Microsoft 365 Excel 2021 Power View удаляется 12 октября 2021 г. В качестве альтернативы вы можете использовать интерактивный визуальный эффект, предоставляемый Power BI Desktop,который можно скачать бесплатно. Вы также можете легко импортировать книги Excel в Power BI Desktop.
Матрица — это тип зрительного образа, который похож на таблицы тем, что состоит из строк и столбцов. Однако матрицу можно свертывать и развертывать по строкам или столбцам. Если она содержит иерархию, можно выполнять детализацию и укрупнение. В матрице могут выводиться итоги и промежуточные итоги по столбцам и строкам. Кроме того, в ней можно представлять данные без повторяющихся значений. Ниже приведен пример визуализации данных об олимпийских видах спорта, дисциплинах и соревнованиях.
Слева в таблице перечислены виды спорта и дисциплины для каждого соревнования.
Справа в матрице каждый вид спорта и дисциплина указаны только один раз.
Чтобы создать матрицу, нужно сначала создать таблицу, а затем преобразовать ее в матрицу.
На вкладке Конструктор в группе Представление переключателя щелкните Таблица > Матрица.
По умолчанию матрица содержит итоги и подытоги для групп, но их можно отключить.
На вкладке Конструктор щелкните Параметры > Итоги.
Чтобы добавить группы столбцов, перетащите поле в область Группы столбцов.
Совет: Если область Группы столбцов не отображена, на вкладке Конструктор выберите пункт Матрица.
Операции с матрицами в Excel
Под матрицей подразумевается набор ячеек, расположенных непосредственно друг возле друга и которые образуют вместе прямоугольник. Не требуется особых навыков, чтобы выполнять различные действия с матрицей, достаточно тех же, какие используются во время работы с классическим диапазоном.
Каждая матрица имеет свой адрес, записывающийся аналогичным диапазону способом. Первая составная часть – первая ячейка диапазона (расположенная в верхнем левом углу), а второй – последняя ячейка, которая находится в нижнем правом углу.
Формулы массива
В подавляющем количестве задач при работе с массивами (а матрицы и являются таковыми) используются формулы соответствующего типа. Базовое их отличие от обычных заключается в том, что последние выводят всего одно значение. Для применения формулы массива необходимо осуществить несколько действий:
После осуществления этих простых действий в поле ввода отображается формула массива. Ее можно отличить от обычной по фигурным скобкам.
Для редактирования, удаления формул массива, надо выделить требуемый диапазон и сделать то, что нужно. Чтобы редактировать матрицу, нужно использовать ту же комбинацию, что и для ее создания. При этом нет возможности редактировать отдельный элемент массива.
Что можно делать с матрицами
В целом, есть огромное количество действий, применение которых возможно для матриц. Давайте каждое из них рассмотрим более подробно.
Транспонирование
Многие люди не понимают значения этого термина. Представьте, что вам нужно поменять строки и колонки местами. Вот это действие и называется транспонированием.
Перед тем, как это осуществить, необходимо выделить отдельную область, которая имеет такое же количество строчек, сколько столбцов есть у исходной матрицы и такое же количество столбцов. Чтобы более наглядно понять, как это работает, посмотрите на этот скриншот.
Далее есть несколько методов, как можно осуществить транспонирование.
Первый способ следующий. Для начала нужно выделить матрицу, после чего скопировать ее. Далее выделяется диапазон ячеек, куда должен быть вставлен транспонированный диапазон. Далее открывается окно «Специальная вставка».
Там есть множество операций, но нам нужно найти радиокнопку «Транспонировать». После совершения этого действия нужно подтвердить его нажатием клавиши ОК. 
После нажатия кнопки ОК сначала будет показано, что вы допустили ошибку. Ничего в этом страшного нет. Все потому, что вставленная нами функция не определена, как формула массива. Поэтому нам нужно совершить такие действия:
Главное достоинство метода заключается в способности транспонированной матрицы сразу корректировать содержащуюся в ней информацию, как только вносятся данные в изначальную. Поэтому рекомендуется использовать именно данный способ.
Сложение
Эта операция возможна лишь применительно к тем диапазонам, количество элементов которых такое же самое. Проще говоря, у каждой из матриц, с которыми пользователь собирается работать, должны быть одинаковые размеры. И приводим скриншот для наглядности. 
В матрице, которая должна получиться, нужно выделить первую ячейку и ввести такую формулу.
=Первый элемент первой матрицы + Первый элемент второй матрицы
Далее подтверждаем ввод формулы с помощью клавиши Enter и используем автозаполнение (квадратик в правом нижнем углу), чтобы скопировать все значения на новую матрицу. 
Умножение
Предположим, у нас есть такая таблица, которую следует умножить на 12. 
Догадливый читатель может легко понять, что метод очень похож на предыдущий. То есть, каждая из ячеек матрицы 1 должна умножаться на 12, чтобы в итоговой матрице каждая ячейка содержала значение, умноженное на этот коэффициент.
При этом важно указывать абсолютные ссылки на ячейки.
Итого, получится такая формула.
=A1*$E$3 
Дальше методика аналогична предыдущей. Нужно это значение растянуть на необходимое количество ячеек.
Предположим, что необходимо перемножить матрицы между собой. Но есть лишь одно условие, при котором это возможно. Надо, чтобы количество столбцов и строк у двух диапазонов было зеркально одинаковое. То есть, сколько столбцов, столько и строк. 
Чтобы было более удобно, нами выделен диапазон с результирующей матрицей. Надо переместить курсор на ячейку в верхнем левом углу и ввести такую формулу =МУМНОЖ(А9:С13;Е9:H11). Не стоит забыть нажать Ctrl + Shift + Enter. 
Обратная матрица
Поиск определителя матрицы
Для начала ставится курсор в какой-угодно ячейке. Далее мы вводим =МОПРЕД(A1:D4)
Несколько примеров
Давайте для наглядности рассмотрим некоторые примеры операций, которые можно осуществлять с матрицами в Excel.
Умножение и деление
Метод 1
Предположим, у нас есть матрица A, имеющая три ячейки в высоту и четыре – в ширину. Также есть число k, которое записывается в другой ячейке. После выполнения операции умножения матрицы на число появится диапазон значений, имеющий аналогичные размеры, но каждая ее часть умножается на k. 
Диапазон B3:E5 – это исходная матрица, которая будет умножаться на число k, которое в свою очередь расположено в ячейке H4. Результирующая матрица будет находиться в диапазоне K3:N5. Исходная матрица будет называться A, а результирующая – B. Последняя образуется путем умножения матрицы А на число k.
Далее вводится =B3*$H$4 в ячейку K3, где В3 — элемент A11 матрицы А.
Не стоит забывать о том, ячейку H4, где указано число k необходимо вводить в формулу с помощью абсолютной ссылки. Иначе значение будет изменяться при копировании массива, и результирующая матрица потеряет работоспособность. 
Далее маркер автозаполнения (тот самый квадратик в правом нижнем углу) используется для того, чтобы скопировать значение, полученное в ячейке K3, во все другие ячейки этого диапазона. 
Вот у нас и получилось умножить матрицу A на определенное число и получить на выходе матрицу B.
Деление осуществляется аналогичным образом. Только вводить нужно формулу деления. В нашем случае это =B3/$H$4.
Метод 2
Итак, основное отличие этого метода в том, в качетве результата выдается массив данных, поэтому нужно применить формулу массива, чтобы заполнить весь набор ячеек.
Необходимо выделить результирующий диапазон, ввести знак равно (=), выделить набор ячеек, с соответствующими первой матрице размерами, нажать на звездочку. Далее выделяем ячейку с числом k. Ну и чтобы подтвердить свои действия, надо нажать на вышеуказанную комбинацию клавиш. Ура, весь диапазон заполняется. 
Деление осуществляется аналогичным образом, только знак * нужно заменить на /.
Сложение и вычитание
Давайте опишем несколько практических примеров использования методов сложения и вычитания на практике.
Метод 1
Не стоит забывать, что возможно сложение лишь тех матриц, размеры которых одинаковые. В результирующем диапазоне все ячейки заполняются значением, являющим собой сумму аналогичных ячеек исходных матриц.
Предположим, у нас есть две матрицы, имеющие размеры 3х4. Чтобы вычислить сумму, следет в ячейку N3 вставить такую формулу:
Тут каждый элемент являет собой первую ячейку матриц, которые мы собрались складывать. Важно, чтобы ссылки были относительными, поскольку если использовать абсолютные, не будут отображаться правильные данные. 
Далее, аналогично умножению, с помощью маркера автозаполнения распространяем формулу на все ячейки результирующей матрицы. 
Вычитание осуществляется аналогично, за тем лишь исключением, что используется знак вычитания (-), а не сложения.
Метод 2
Аналогично методу сложения и вычитание двух матриц, этот способ подразумевает использование формулы массива. Следовательно, в качестве ее результата будет выдаваться сразу набор значений. Поэтому нельзя редактировать или удалять какие-то элементы.
Сперва надо выделить диапазон, отделенный под результирующую матрицу, а потом нажать на «=». Затем надо указать первый параметр формулы в виде диапазона матрицы А, нажать на знак + и записать второй параметр в виде диапазона, соответствующему матрице B. Подтверждаем свои действия нажатием комбинации Ctrl + Shift + Enter. Все, теперь вся результирующая матрица заполнена значениями. 
Пример транспонирования матрицы
Выделяем диапазон для ячеек матрицы АТ. 
Далее переходим в окно «Аргументы функции», где вводится диапазон B3:E5, который повторяет матрицу А. Далее надо нажать Shift + Ctrl, после чего кликнуть «ОК».
В результате, у нас получается такая транспонированная таблица, которая изменяет свои значения вслед за исходной. 
Поиск обратной матрицы
Теперь опишем, как это делать на практике. Сначала необходимо выделить диапазон G3:I5 (там будет располагаться обратная матрица). Необходимо найти на вкладке «Формулы» пункт «Вставить функцию». 
Выводы
Мы разобрали некоторые примеры, как можно работать с матрицами в Excel, а также описали теорию. Оказывается, что это не так страшно, как может показаться на первый взгляд, не так ли? Это только звучит непонятно, но на деле с матрицами среднестатистическому пользователю приходится иметь дело каждый день. Они могут использоваться почти для любой таблицы, где есть сравнительно небольшое количество данных. И теперь вы знаете, как можно себе упростить жизнь в работе с ними.
Матрицы в Excel: операции (умножение, деление, сложение, вычитание, транспонирование, нахождение обратной матрицы, определителя)
Программа Microsoft Office Excel позволяет выполнять операции с матрицами с помощью встроенных функций и формул. Рассмотрим основные операции над матрицами:
Введем условные обозначения. Матрица А размерностью i x j — это прямоугольная таблица чисел, состоящая из i строк и j столбцов, аij — элемент матрицы.
Умножение и деление матрицы на число в Excel
Способ 1
Рассмотрим матрицу А размерностью 3х4. Умножим эту матрицу на число k. При умножении матрицы на число получается матрица такой же размерности, что и исходная, при этом каждый элемент матрицы А умножается на число k.
Введем элементы матрицы в диапазон В3:Е5, а число k — в ячейку Н4. В диапазоне К3:N5 вычислим матрицу В, полученную при умножении матрицы А на число k: В=А*k. Для этого введем формулу =B3*$H$4 в ячейку K3, где В3 — элемент а11 матрицы А.
Примечание: адрес ячейки H4 вводим как абсолютную ссылку, чтобы при копировании формулы ссылка не менялась.
С помощью маркера автозаполнения копируем формулу ячейки К3 вниз и вправо на весь диапазон матрицы В.
Таким образом, мы умножили матрицу А в Excel и получим матрицу В.
Для деления матрицы А на число k в ячейку K3 введем формулу =B3/$H$4 и скопируем её на весь диапазон матрицы В.
Способ 2
Этот способ отличается тем, что результат умножения/деления матрицы на число сам является массивом. В этом случае нельзя удалить элемент массива.
Для деления матрицы на число этим способом выделяем диапазон, в котором будет вычислен результат, вводим знак «=», выделяем диапазон, содержащий исходную матрицу А, нажимаем на клавиатуре знак умножить (*) и выделяем ячейку с числом k. После ввода формулы нажимаем сочетание клавиш Ctrl+Shift+Enter, чтобы значениями заполнился весь диапазон.
Для выполнения деления в данном примере в диапазон вводим формулу =B3:E5/H4, т.е. знак «*» меняем на «/».
Сложение и вычитание матриц в Excel
Способ 1
Следует отметить, что складывать и вычитать можно матрицы одинаковой размерности (одинаковое количество строк и столбцов у каждой из матриц). Причем каждый элемент результирующей матрицы С будет равен сумме соответствующих элементов матриц А и В, т.е. сij = аij + bij.
Рассмотрим матрицы А и В размерностью 3х4. Вычислим сумму этих матриц. Для этого в ячейку N3 введем формулу =B3+H3, где B3 и H3 – первые элементы матриц А и В соответственно. При этом формула содержит относительные ссылки (В3 и H3), чтобы при копировании формулы на весь диапазон матрицы С они могли измениться.
С помощью маркера автозаполнения скопируем формулу из ячейки N3 вниз и вправо на весь диапазон матрицы С.
Способ 2
Этот способ отличается тем, что результат сложения/вычитания матриц сам является массивом. В этом случае нельзя удалить элемент массива.
Для деления матрицы на число этим способом выделяем диапазон, в котором будет вычислен результат, вводим знак «=», выделяем диапазон, содержащий первую матрицу А, нажимаем на клавиатуре знак сложения (+) и выделяем вторую матрицу В. После ввода формулы нажимаем сочетание клавиш Ctrl+Shift+Enter, чтобы значениями заполнился весь диапазон.
Умножение матриц в Excel
Следует отметить, что умножать матрицы можно только в том случае, если количество столбцов первой матрицы А равно количеству строк второй матрицы В.
Рассмотрим матрицы А размерностью 3х4 и В размерностью 4х2. При умножении этих матриц получится матрица С размерностью 3х2.
Вычислим произведение этих матриц С=А*В с помощью встроенной функции =МУМНОЖ(). Для этого выделим диапазон L3:M5 — в нём будут располагаться элементы матрицы С, полученной в результате умножения. На вкладке Формулы выберем Вставить функцию.
В диалоговом окне Вставка функции выберем Категория Математические — функция МУМНОЖ — ОК.
В диалоговом окне Аргументы функции выберем диапазоны, содержащие матрицы А и В. Для этого напротив массива1 щёлкнем по красной стрелке.
Выделим диапазон, содержащий элементы матрицы А (имя диапазона появится в строке аргументов), и щелкнем по красной стрелке.
Для массива2 выполним те же действия. Щёлкнем по стрелке напротив массива2.
Выделим диапазон, содержащий элементы матрицы В, и щелкнем по красной стрелке.
В диалоговом окне рядом со строками ввода диапазонов матриц появятся элементы матриц, а внизу — элементы матрицы С. После ввода значений нажимаем на клавиатуре сочетание клавиш Shift+Ctrl и щелкаем левой кнопкой мыши по кнопке ОК.
ВАЖНО. Если просто нажать ОК, то программа вычислит значение только первой ячейки диапазона матрицы С.
Мы получим результат умножения матриц А и В.
Мы можем изменить значения ячеек матриц А и В, значения матрицы С поменяются автоматически.
Транспонирование матрицы в Excel
Выделим диапазон Н3:J6, в который будут введены значения транспонированной матрицы.
На вкладке Формулы выберем Вставить функцию, выберем категорию Ссылки и массивы — функция ТРАНСП — ОК.
В диалоговом окне Аргументы функции указываем диапазон массива В3:Е5, содержащего элементы матрицы А. Нажимаем на клавиатуре сочетание клавиш Shift+Ctrl и щелкаем левой кнопкой мыши по кнопке ОК.
Нажмите для увеличения
Мы получили транспонированную матрицу.
Нахождение обратной матрицы в Excel
Пусть дана матрица А размерностью 3х3, найдем для неё обратную матрицу с помощью функции =МОБР().
Для этого выделим диапазон G3:I5, который будет содержать элементы обратной матрицы, на вкладке Формулы выберем Вставить функцию.
В диалоговом окне Вставка функции выберем категорию Математические — функция МОБР — ОК.
В диалоговом окне Аргументы функции указываем диапазон массива В3:D5, содержащего элементы матрицы А. Нажимаем на клавиатуре сочетание клавиш Shift+Ctrl и щелкаем левой кнопкой мыши по кнопке ОК.
Нажмите для увеличения
Мы получили обратную матрицу.
Нахождение определителя матрицы в Excel
Определитель матрицы — это число, которое является важной характеристикой квадратной матрицы.
Как найти определить матрицы в Excel
Пусть дана матрица А размерностью 3х3, вычислим для неё определитель с помощью функции =МОПРЕД().
Для этого выделим ячейку Н4, в ней будет вычислен определитель матрицы, на вкладке Формулы выберем Вставить функцию.
В диалоговом окне Вставка функции выберем категорию Математические — функция МОПРЕД — ОК.
В диалоговом окне Аргументы функции указываем диапазон массива В3:D5, содержащего элементы матрицы А. Нажимаем ОК.
Нажмите для увеличения
Мы вычислили определитель матрицы А.
В заключение обратим внимание на важный момент. Он касается тех операций над матрицами, для которых мы использовали встроенные в программу функции, а в результате получали новую матрицу (умножение матриц, нахождение обратной и транспонированной матриц). В матрице, которая получилась в результате операции, нельзя удалить часть элементов. Т.е. если мы выделим, например, один элемент матрицы и нажмём Del, то программа выдаст предупреждение: Нельзя изменять часть массива.
Нажмите для увеличения
Мы можем удалить только все элементы этой матрицы.
Видеоурок
Шамарина Татьяна Николаевна — учитель физики, информатики и ИКТ, МКОУ «СОШ», с. Саволенка Юхновского района Калужской области. Автор и преподаватель дистанционных курсов по основам компьютерной грамотности, офисным программам. Автор статей, видеоуроков и разработок.
Спасибо за Вашу оценку. Если хотите, чтобы Ваше имя
стало известно автору, войдите на сайт как пользователь
и нажмите Спасибо еще раз. Ваше имя появится на этой стрнице.