Как перемножать матрицы в эксель

1.4. Область

Матрица занимает на листе область. Пример показан на Рис. 6, где расположена
матрица, которая имеет 9 строк (с 2 по 10) и 3 столбца (с B по D).  

Рис.6 Область на листе

Для ссылки на область используется выражение, в котором адреса
верхней левой и правой нижней ячеек области соединяются двоеточием.
Например, B2:D10 или
$B$2:$D$10.

При работе с матрицами бывает удобно присвоить ее области имя. Для
этого есть два способа. Самый простой – это выделить нужную область на
листе, а затем кликнуть в окно Name Box (см.
), стереть там адрес и вписать имя – например
Data (Рис. 6). Другой способ – это
действовать через меню: Insert-Name-Define.

Имя области может быть глобальным – доступным для любых листов книги,
или локальным – определенным только для одного листа. В последнем
случае, имя следует определять в форме:
ListName!RangeName

Подробности смотри

здесь.

Сложение и вычитание матриц в Excel

Способ 1

Следует отметить, что складывать и вычитать можно матрицы одинаковой размерности (одинаковое количество строк и столбцов у каждой из матриц). Причем каждый элемент результирующей матрицы С будет равен сумме соответствующих элементов матриц А и В, т.е. с_ij = а_ij + b_ij.

Рассмотрим матрицы А и В размерностью 3х4. Вычислим сумму этих матриц. Для этого в ячейку N3 введем формулу =B3+H3, где B3 и H3 – первые элементы матриц А и В соответственно. При этом формула содержит относительные ссылки (В3 и H3), чтобы при копировании формулы на весь диапазон матрицы С они могли измениться.

С помощью маркера автозаполнения скопируем формулу из ячейки N3 вниз и вправо на весь диапазон матрицы С.

Для вычитания матрицы В из матрицы А (С=А — В) в ячейку N3 введем формулу =B3 — H3 и скопируем её на весь диапазон матрицы С.

Способ 2

Этот способ отличается тем, что результат сложения/вычитания матриц сам является массивом. В этом случае нельзя удалить элемент массива.

Для деления матрицы на число этим способом выделяем диапазон, в котором будет вычислен результат, вводим знак «=», выделяем диапазон, содержащий первую матрицу А, нажимаем на клавиатуре знак сложения (+) и выделяем вторую матрицу В. После ввода формулы нажимаем сочетание клавиш Ctrl+Shift+Enter, чтобы значениями заполнился весь диапазон.

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

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

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

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

1. Выделяем любую пустую ячейку на листе, куда будут выводиться результаты вычислений. Жмем на кнопку «Вставить функцию», размещенную около строки формул.

Запускается Мастер функций. В перечне записей, который он представляет, ищем «МОПРЕД», выделяем этот элемент и жмем на кнопку «OK».

Открывается окно аргументов. Ставим курсор в поле «Массив». Выделяем весь диапазон ячеек, в котором расположена матрица. После того, как его адрес появился в поле, жмем на кнопку «OK».

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

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

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

В открывшемся списке выбираем функцию МОБР. Жмем на кнопку «OK».

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

Как видим, после этих действий обратная матрица вычислена в выделенных ячейках.

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

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

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

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

квадратныхневырожденных

1.2. Книга, лист и ячейка

Файл Excel с расширением XLS (XLSX в версии 2007) называется
(рабочей) книгой. Если запустить программу Excel, например, щелкнуть на
рабочем столе иконку
,
то откроется новая пустая книга. . 

Рис. 2 Новая книга Excel

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

Рис. 3
Открытие книги Excel через проводник

Рабочая книга состоит из нескольких листов, имена которых
показаны в нижней части окна. Листы можно удалять, добавлять,
переименовывать. Для этого надо щелкнуть правой клавишей мышки по имени
листа. Появится меню, из которого можно выбрать нужную операцию.

Рис.
4 Операции с листами

Стандартное имя листа – Sheet1, но ему можно дать любое имя, например
Data. На лист можно вставлять рисунки, графики и другие необходимые
объекты.

Каждый лист состоит из ячеек, образующих таблицу, размером 256
столбцов и 65536 строк (В версии 2007 – 16384 столбцов и1048576 строк).
Строки на листе обозначены числами: 1,2, 3…, а столбцы имеют буквенную
кодировку: A, B, …,Z,
AA, AB .., и т.д. до последнего столбца
IV (в 2007
– до XFD). Этот стиль адресации называется A1. Реже применяется
альтернативный стиль R1C1, в котором столбцы также нумеруются. Мы не
будем использовать этот стиль, а прочитать об этом можно

здесь

Строки и столбцы можно удалять, добавлять, прятать, а также менять их размер:
высоту или ширину.

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

здесь.

3.2. Пример

Рассмотрим вопрос о применении средств VBA на примере.

При моделировании неизотермической кинетики (ДСК, ТГА, и
т.п.) необходима
интегральная показательная функция (integral
exponential) E₁(x). По определению,

Для вычисления E₁(x) можно
использовать бесконечный ряд  

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

Рис.41 Вычисление функции E₁(x)
на листе

Нельзя признать такой способ вычисления удачным.
Во-первых, вычисления занимают на листе много места. Но главное, не
понятно, сколько членов в ряду нужно суммировать – иногда хватит и 10, а
иногда и 50 будет мало.

1.4. Область

Матрица занимает на листе область. Пример показан на Рис. 6, где расположена
матрица, которая имеет 9 строк (с 2 по 10) и 3 столбца (с B по D).  

Рис.6 Область на листе

Для ссылки на область используется выражение, в котором адреса
верхней левой и правой нижней ячеек области соединяются двоеточием.
Например, B2:D10 или
$B$2:$D$10.

При работе с матрицами бывает удобно присвоить ее области имя. Для
этого есть два способа. Самый простой – это выделить нужную область на
листе, а затем кликнуть в окно Name Box (см.
), стереть там адрес и вписать имя – например
Data (Рис. 6). Другой способ – это
действовать через меню: Insert-Name-Define.

Имя области может быть глобальным – доступным для любых листов книги,
или локальным – определенным только для одного листа. В последнем
случае, имя следует определять в форме:
ListName!RangeName

Подробности смотри

здесь.

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

Следует отметить, что умножать матрицы можно только в том случае, если количество столбцов первой матрицы А равно количеству строк второй матрицы В.

Рассмотрим матрицы А размерностью 3х4 и В размерностью 4х2. При умножении этих матриц получится матрица С размерностью 3х2.

Вычислим произведение этих матриц С=А*В с помощью встроенной функции =МУМНОЖ(). Для этого выделим диапазон L3:M5 — в нём будут располагаться элементы матрицы С, полученной в результате умножения. На вкладке Формулы выберем Вставить функцию.

В диалоговом окне Вставка функции выберем Категория Математические — функция МУМНОЖ — ОК.

В диалоговом окне Аргументы функции выберем диапазоны, содержащие матрицы А и В. Для этого напротив массива1 щёлкнем по красной стрелке.

Выделим диапазон, содержащий элементы матрицы А (имя диапазона появится в строке аргументов), и щелкнем по красной стрелке.

Для массива2 выполним те же действия. Щёлкнем по стрелке напротив массива2.

Выделим диапазон, содержащий элементы матрицы В, и щелкнем по красной стрелке.

В диалоговом окне рядом со строками ввода диапазонов матриц появятся элементы матриц, а внизу — элементы матрицы С. После ввода значений нажимаем на клавиатуре сочетание клавиш Shift+Ctrl и щелкаем левой кнопкой мыши по кнопке ОК.

ВАЖНО. Если просто нажать ОК, то программа вычислит значение только первой ячейки диапазона матрицы С

Мы получим результат умножения матриц А и В.

Мы можем изменить значения ячеек матриц А и В, значения матрицы С поменяются автоматически.

Функции для работы с матрицами в 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.

Вычисление обратной матрицы в MS EXCEL

Для вычисления обратной матрицы в MS EXCEL существует специальная функция МОБР() или англ. MINVERSE.

• Понятие обратной матрицы определено только для квадратных матриц, определитель которых отличен от нуля.
• СОВЕТ: О нахождении определителя матрицы читайте статью Вычисление определителя матрицы в MS EXCEL
• Матрица А-1 называется обратной для исходной матрицы А порядка n, если справедливы равенства А-1*А=Е и А*А-1=Е, где Е единичная матрица порядка n.
• Для вычисления обратной матрицы в MS EXCEL существует специальная функция МОБР().

Если элементы исходной матрицы 2 х 2 расположены в диапазоне А8:В9, то для получения транспонированной матрицы нужно (см. файл примера):

• выделить диапазон 2 х 2, который не пересекается с исходным диапазоном А8:В9, например, Е8:F9
• в Cтроке формул ввести формулу =МОБР(A8:B9) и нажать комбинацию клавиш CTRL+SHIFT+ENTER, т.е. нужно ввести ее как формулу массива (формулу можно ввести прямо в ячейку, предварительно нажав клавишу F2)

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

Массив может быть задан не только как интервал ячеек, например A8:B9, но и как массив констант, например =МОБР({5;4: 3;2}).

Запись с использованием массива констант позволяет не указывать элементы в отдельных ячейках, а разместить их в ячейке вместе с функцией.

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

Некоторые квадратные матрицы не могут быть обращены: в таких случаях функция МОБР() возвращает значение ошибки #ЧИСЛО!. Матрицы не могут быть обращены, у которых определитель равен 0.

Если функция МОБР() вернула значение ошибки #ЗНАЧ!, то либо число строк в массиве не равно числу столбцов, либо какая-либо из ячеек в массиве пуста или содержит текст. Т.е. функция МОБР() пустую ячейку воспринимает не как содержащую 0 (как например, это делает СУММ()), а как ошибочное значение.

Вычисление обратной матрицы с помощью матрицы из алгебраических дополнений

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

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

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

2.8. Критическая ошибка в Excel 2003

В Excel 2003 функции TREND и
LINEST при определенных
условиях дают неверный результат.

Так происходит когда одновременно:

• среднее значение по каждой переменной в матрице
  предикторов X равно нулю;

• среднее значение отклика Y
  не равно нулю.

На показан как раз такой
случай: средние значения по всем столбцам матрицы Xc
равны нулю, а среднее по столбцу Yc отлично от нуля..

Пример 
 

Рис.37 Ошибка в регрессионных функциях Excel
2003

Ситуацию можно исправить, применяя функцию
TREND к
центрированным значениям отклика, с последующей коррекцией результата.
Для этого можно использовать формулу
=TREND(Yc-ym,
Xc)+ym, применение которой показано на том же рисунке.

Удивительно, но эта ошибка не была замечена
пользователями. Однако в новой версии 2007 она исправлена.  

Перемножение одной матрицы на другую в Microsoft Excel

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

Процедура перемножения матриц

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

Способов перемножить матрицы в Экселе все-таки не так уж и много — всего два. И оба они связаны с применением встроенных функций Excel. Разберем в деталях каждый из данных вариантов.

Способ 1: функция МУМНОЖ

Наиболее простым и популярным вариантом среди пользователей является применение функции МУМНОЖ. Оператор МУМНОЖ относится к математической группе функций. Как раз его непосредственной задачей и является нахождение произведения двух матричных массивов. Синтаксис МУМНОЖ имеет такой вид:

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

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

1. Выделяем диапазон, где будет отображаться результат умножения, начиная с его верхней левой ячейки. Размер данного диапазона должен соответствовать числу строк у первой матрицы и числу столбцов у второй. Клацаем по пиктограмме «Вставить функцию».

Способ 2: использование составной формулы

Кроме того, существует ещё один способ умножения двух матриц. Он более сложный, чем предыдущий, но тоже заслуживает упоминания, как альтернативный вариант. Данный способ предполагает использование составной формулы массива, которая будет состоять из функции СУММПРОИЗВ и вложенного в неё в качестве аргумента оператора ТРАНСП.

1. На этот раз выделяем на листе только левый верхний элемент массива пустых ячеек, который рассчитываем использовать для вывода результата. Щелкаем по значку «Вставить функцию».

В качестве аргументов из группы «Массив» используется ссылка на конкретный диапазон, который нужно перемножить. Всего может быть использовано от двух до 255 таких аргументов. Но в нашем случае, так как мы имеем дело с двумя матрицами, нам понадобится как раз два аргумента.

То есть, единственным аргументом данного оператора является ссылка на тот массив, который следует «перевернуть». Вернее, в нашем случае даже не на весь массив, а только на его первый столбец.

Как видим, несмотря на то, что был получен равнозначный результат, использовать функцию для умножения матриц МУМНОЖ значительно проще, чем применять для этих же целей составную формулу из операторов СУММПРОИЗВ и ТРАНСП. Но все-таки данный альтернативный вариант тоже нельзя оставить без внимания при изучении всех возможностей перемножения матриц в Microsoft Excel.

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

Помимо этой статьи, на сайте еще 12345 инструкций. Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

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

1.9. «Растаскивание» формул

При работе с данными в виде матрицы, часто возникает
необходимость ввести не одну, а целую серию формул. Например, при
выполнении SNV преобразования спектральных данных нужно вычислить
средние значения и среднеквадратичные отклонения по каждой строке. Очень
утомительно было бы повторять одну и ту же формулу многократно, меняя в
ней только аргумент, даже для сильно усеченного примера, показанного на
. А в реальных данных число строк или
столбцов может доходить до десятков тысяч. К счастью это и не нужно,
поскольку можно воспользоваться техникой «растаскивания» формул. 

Поясним эту технику на нашем примере. Начнем с ввода
формулы-образца. В этом случае –  это формула, помещенная в ячейку
J3  .

Рис.21
Маркер заполнения

Формулы в соседние ячейки можно вставить при помощи
маркера заполнения, которым называется небольшой черный квадрат в правом
нижнем углу выделенной области ячеек. При наведении мышки на этот
маркер, указатель принимает вид черного креста. После этого можно
перетащить ячейку, содержащую формулу, в примыкающий диапазон. Тащить
можно по вертикали, как показано на Рис. 22, и по горизонтали.

Рис.22 Растаскивание серии однотипных формул

Формулы можно размножать и другим способом. Сначала
копируем ячейку, содержащую формулу-образец. Потом отмечаем диапазон
ячеек, в которые нужно распространить формулу, и делаем специальную
вставку, выбирая опцию Formulas.

Рис.23
Копирование серии однотипных формул

Отметим, что независимо от способа, ссылки получаются
правильными – на соответствующий диапазон ячеек. Это произошло потому,
что в исходной формуле мы использовали относительную адресацию в
аргументе: B3:I3.

Подробности можно прочитать

здесь. 

5

Рассмотрим правило умножения двух квадратных матриц второго и третьего порядков. Пусть даны две матрицы

Произведением матрицы А на матрицу В называется матрица С= А В, элементы которой составляются следующим образом:

• Как видим, элемент матрицы-произведения, находящийся на пересечении i-й строки и k-го столбца, представляет собой сумму парных произведений элементов i-й строки первой матрицы на элементы k-го столбца второй матрицы.
• Например, элемент, стоящий во второй строке и первом столбце матрицы произведения АВ, равен сумме парных произведений элементов второй строки матрицы А на элементы первого столбца матрицы В.
• Это правило сохраняется для умножения квадратных матриц третьего и более высокого порядка, а также для умножения прямоугольных матриц, в которых число столбцов матрицы-множимого равно числу строк матрицы-множителя.
• Пример1

Пример2

Пример3.

С другой стороны, как установлено выше,

1. Следовательно, произведение двух матриц, вообще говоря, не подчиняется переместительному закону:
2. АВ ВА.
3. Можно проверить, что умножение матриц подчиняется сочетательному закону:

А(ВС) = (АВ)С.

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

Пример 4. Если

то

При умножении матриц второго порядка особое значение имеет квадратная матрица

При умножении любой квадратной матрицы

второго порядка на матрицу Е снова получается матрица А.

Действительно,

Аналогично EA =A.

Матрица Е называется единичной матрицей. Единичная матрица n-го порядка имеет вид

Если в матрице (1), обозначаемой буквой А, сделать все строки столбцами с тем же номером, то получим матрицу

• называемую транспонированной к матрице А.
• Понятие о матрице | Сложение матриц | Вычитание матриц и умножение матриц на число |
• Умножение матриц |   Контакты первого и второго порядков в эпидемиологии | Матрицы и сети |
• Главная