Примеры использования функции мобр в excel матрицах

Как найти валовый показатель по матрице взаимосвязей?

Пример 2. Связь между тремя отраслями представлена матрицей прямых затрат А. Спрос (конечный продукт) задан вектором X. Найти валовой выпуск продукции отраслей Х. Описать используемые формулы, представить распечатку со значениями и с формулами.

Исходные данные приведены на рисунке 2:

Рисунок 2 – Исходные данные.

Данная задача связана с определением объема производства каждой из N отраслей, чтобы удовлетворить все потребности в продукции данной отрасли. При этом каждая отрасль выступает и как производитель некоторой продукции и как потребитель своей и произведенной другими отраслями продукции. Задача межотраслевого баланса – отыскание такого вектора валового выпуска X, который при известной матрице прямых затрат обеспечивает заданный вектор конечного продукта Y.

Матричное решение данной задачи:

где Е – единична матрица.

Для решения задачи в примере используем следующие 4 функции для работы с матрицами в Excel:

1. МОБР – нахождение обратной матрицы.
2. МУМНОЖ – умножение матриц.
3. МОПРЕД – нахождение определителя матрицы.
4. МЕДИН – нахождение единичной матрицы.

Результаты приведены на рисунке 3:

Рисунок 3 – Результат вычислений.

Функция «ЕСЛИ» в Excel.

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

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

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

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

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

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

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

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

Нахождение обратной матрицы методом алгебраических дополнений (союзной матрицы)

Для неособенной квадратной матрицы А обратной является матрица

,  (2)

где —
определитель матрицы А, а

— матрица, союзная с матрицей А.

Разберём ключевые понятия, которые потребуются для решения задач — союзная матрица, алгебраические дополнения и транспонированная матрица.

Пусть существует квадратная матрица A:

Транспонированная относительно матрицы A матрица A’ получается,
если из строк матрицы A сделать столбцы, а из её столбцов — наоборот, строки, то есть заменить строки
столбцами:

Остановимся на минорах и алгебраических дополнениях.

Пусть есть квадратная матрица третьего порядка:

.

Её определитель:

Вычислим алгебраическое дополнение элемента ,
то есть элемента 2, стоящего на пересечении первой строки и второго столбца.

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

.

Алгебраическое дополнение элемента
получим, если умножим ,
где i — номер строки исходного элемента, а k — номер столбца исходного элемента, на
полученный в предыдущем действии минор этого исходного элемента. Получаем алгебраическое дополнение элемента
:

.

По этой инструкции нужно вычислить алгебраические дополнения всех элементов матрицы
A’, транспонированной относительно матрицы матрица A.

И последнее из значимых для нахождение обратной матрицы понятий. Союзной с квадратной матрицей A называется матрица

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

Алгоритм нахождения обратной матрицы методом алгебраических дополнений

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

2. Найти матрицу, транспонированную относительно A.

3. Вычислить элементы союзной матрицы как алгебраические дополнения марицы, найденной на шаге 2.

4. Применить формулу (2): умножить число, обратное определителю матрицы A,
на союзную матрицу, найденную на шаге 4.

5. Проверить полученный на шаге 4 результат, умножив данную матрицу A на
обратную матрицу. Если произведение этих матриц равно единичной матрицы, значит обратная матрица была
найдена верно. В противном случае начать процесс решения снова.

Пример 1. Для матрицы

найти обратную матрицу.

Решение. Для нахождения обратной матрицы необходимо найти определитель матрицы А .
Находим по правилу треугольников:

Следовательно, матрица А – неособенная (невырожденная, несингулярная) и для неё существует обратная.

Найдём матрицу, союзную с данной матрицей А.

Найдём матрицу
,
транспонированную относительно матрицы A:

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

Следовательно, матрица
,
союзная с матрицей A, имеет вид

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

Применяя формулу (2), находим матрицу, обратную матрице А:

Проверить решение можно с помощью онлайн калькулятора
для нахождения обратной матрицы.

Решение систем уравнений методом Гаусса в Excel

Для примера возьмем простейшую систему уравнений:

3а + 2в – 5с = -12а – в – 3с = 13а + 2в – с = 9

Коэффициенты запишем в матрицу А. Свободные члены – в матрицу В.

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

1. Приведем все коэффициенты при а к 0. Кроме первого уравнения. Скопируем значения в первой строке двух матриц в ячейки В6:Е6. В ячейку В7 введем формулу: =B3:Е3-$B$2:$Е$2*(B3/$B$2). Выделим диапазон В7:Е7. Нажмем F2 и сочетание клавиш Ctrl + Shift + Enter. Мы отняли от второй строки первую, умноженную на отношение первых элементов второго и первого уравнения.
2. Копируем введенную формулу на 8 и 9 строки. Так мы избавились от коэффициентов перед а. Сохранили только первое уравнение.
3. Приведем к 0 коэффициенты перед в в третьем и четвертом уравнении. Копируем строки 6 и 7 (только значения). Переносим их ниже, в строки 10 и 11. Эти данные должны остаться неизменными. В ячейку В12 вводим формулу массива.
4. Прямую прогонку по методу Гаусса сделали. В обратном порядке начнем прогонять с последней строки полученной матрицы. Все элементы данной строки нужно разделить на коэффициент при с. Введем в строку формулу массива: <=B12:E12/D12>.
5. В строке 15: отнимем от второй строки третью, умноженную на коэффициент при с второй строки (<=(B11:E11-B16:E16*D11)/C11>). В строке 14: от первой строки отнимаем вторую и третью, умноженные на соответствующие коэффициенты (<=(B10:E10-B15:E15*C10-B16:E16*D10)/B10>). В последнем столбце новой матрицы получаем корни уравнения.

Основные методы решения поставленной проблемы

Среди бесчисленного числа способов убрать большие буквы на компьютере, выделяют самые популярные и простые варианты решения.

Способ 1

Просто воспользуйтесь клавишами “контрл”(Ctrl) и “минус”(-). Данный метод является универсальным для большинства компьютерных программ ОС Windows, да и для стороннего программного обеспечения тоже. Просто нажмите на Ctrl и держите его, а затем жмите на минус — вы тут же увидите, как все уменьшается.

Способ 2

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

Чтобы “поиграться с буквами:

1. Просто зайдите в настройки программы и найдите там параметр, который отвечает за размеры.
2. Установите нужные значения и продолжайте пользоваться интернетом.
3. Если результат вас не удовлетворяет, то можете попробовать изменить масштаб страницы. В настройках будет меню, в котором обычно располагаются клавиши, отвечающие за регулировку размеров отображения в виде “минуса” и “плюса”.

Способ 3

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

Для того чтобы провернуть все эти действия, нажмите ПКМ (правой кнопкой мыши) по пустой области на пространстве рабочего стола и выберите раздел под названием “Разрешение экрана”, в котором необходимо переключиться на более высокие значения разрешения экрана.

Способ 4

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

1. Вам необходимо навести курсор компьютерной мыши на пустую область на рабочем пространстве и нажать ПКМ.
2. Перед вам раскроется контекстное меню, в котором необходимо выбрать раздел с названием “Персонализация”.
3. Теперь перейдите на вкладку “Цвет окна”, а после этого в “Дополнительные параметры оформления”. Там вы должны увидеть элементы, например “Значок” и проставьте те размеры, которые будут удовлетворять вашим потребностям. Здесь же вы сможете менять и визуализацию окон, и величину букв, которые системные окна используют.

Способ 5

Ну и напоследок можно воспользоваться настройкой экрана:

1. Перейдите в панель управления из меню “Пуск” и откройте “Управление и персонализация”.
2. Теперь ищем пункт “Экран”, заходим в него и настраиваем размер в меньшую сторону, если это возможно.

3 .6. Установка надстроек

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

В первой фазе файлы, входящие в пакет надстройки
размещают на компьютере. В некоторых пакетах имеется программа
Setup.exe, которая выполняет это автоматически. В других файлы нужно
размещать самостоятельно. Объясним, как это нужно сделать. В состав
пакета обязательно входит файл с расширением XLA и несколько
вспомогательных файлов с расширениями DLL, HLP и др. Все вспомогательные
файлы должны размещаться в следующих директориях:
C:\Windows
, или C:\Windows\System или
C:\Windows\System32
. Основной файл (с расширением XLA) может, в
принципе, находится в любом месте, но две директории являются
предпочтительными.

Microsoft рекомендует размещать файлы XLA в директории
C:\Documents and Settings\User\Application
Data\Microsoft\AddIns
, где User
– это
имя, под которым происходит вход в систему. Тогда этот файл можно быстро
загрузить на второй фазе установки. Однако, если рабочие книги
используются на нескольких компьютерах, с разными именами
User
, то, при смене компьютера, связи с
основным файлом надстройки теряются и их приходится
обновлять .

Поэтому мы предлагаем поместить файл
Chemometrics.xla
в директорию, которая имеет одно и то же имя на
разных компьютерах, например C:\Program
Files\Chemometrics
. Автоматическая установка надстройки
Chemometrics Add-In

описана .

Вторая фаза проводится из открытой книги Excel. В версии
2003 нужно выполнить последовательность команд
Tools-Add-Ins
, а в версии 2007 последовательность:
Office Button-Excel Options-Add-Ins-Go
. В
появившемся окне (см Рис. 45
) нужно нажать Browse
и найти в компьютере нужный файл XLA.

Рис.45
Установка надстройки

После того, как надстройка установлена, ее можно
активировать и деактивировать устанавливая отметку напротив имени. Для
удаления надстройки нужно снять галочку против ее имени в окне
Add-Ins
, закрыть Excel и удалить все ранее
установленные файлы с компьютера.

Заключение

Мы рассмотрели основные приемы работы с матрицами в
системе Excel

За рамками пособия осталось еще много всего важного.
Частично заполнить эти пробелы поможет пособие. В программе 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.

Что можно делать с матрицами

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

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

Многие люди не понимают значения этого термина. Представьте, что вам нужно поменять строки и колонки местами. Вот это действие и называется транспонированием. 

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

Далее есть несколько методов, как можно осуществить транспонирование. 

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

Там есть множество операций, но нам нужно найти радиокнопку «Транспонировать». После совершения этого действия нужно подтвердить его нажатием клавиши ОК.

Есть еще один способ, с помощью которого можно транспонировать матрицу. Сперва надо выделить ячейку, расположенную в верхнем левом углу диапазона, отведенного под транспонированную матрицу. Далее открывается диалоговое окно с функциями, где есть функция ТРАНСП. Ниже в примере вы более подробно узнаете, как это сделать. В качестве параметра функции используется диапазон, соответствующий изначальной матрице.

После нажатия кнопки ОК сначала будет показано, что вы допустили ошибку. Ничего в этом страшного нет. Все потому, что вставленная нами функция не определена, как формула массива. Поэтому нам нужно совершить такие действия:

1. Выделить набор ячеек, отведенных под транспонированную матрицу.
2. Нажать клавишу F2.
3. Нажать на горячие клавиши Ctrl + Shift + Enter.

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

3 .3 . Пример макроса

Второй способ опирается на рекуррентное соотношение,
связывающее два соседних члена в ряду

Для того чтобы воспользоваться этим соотношением, надо
организовать на листе рекуррентную процедуру. Например, так, как
показано на Рис. 42

Рис.42
Вычисление функции E
1 (x
)
итерационным способом

Один шаг итерации – это переход от значений в области
J2:J4
к значениям в области
L2:L4
. Для того, чтобы сделать следующую
итерацию, нужно скопировать значения, получившиеся в области
L2:L4
и вставить их в область
J2:J4
. При этом вставлять нужно только
величины, без формул. Величины в области H2:H4

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

Проще всего начать создание макроса через запись команд,
выполняемых на листе. Для этого идем в раздел меню Tools–Macro–Record
New Macro
. Появляется окно (Рис. 43
), в котором можно указать имя
макроса и где он будет расположен.

Рис.43
Запись макро

После нажатия OK
начинается
запись всех действий, выполняемых на листе. Когда все, что нужно
сохранено в макросе, запись надо остановить командой
Tools–Macro–Stop Recording
. Результат можно увидеть, зайдя в
редактор Visual Basic

.

Рис.44
Редактор Visual Basic

На Рис.

Рис.45
Функция IntExp

На Рис. 45
приведен
код этой функции и пример обращения к
ней.

Мы не будем рассказывать о программировании на VBA, т.к.
это очень большой и сложный вопрос. Самостоятельно его можно изучить по
имеющимся в сети многочисленным пособиям, например,
.

VBA – это довольно медленный язык и он плохо подходит
для больших вычислений. Например, не стоит писать на этом языке
процедуру для PCA декомпозиции – на больших массивах она будет считать
очень долго. Правильнее рассматривать Excel и VBA как интерфейс (front
end) для ввода и вывода данных, которые затем передаются в динамическую
библиотеку (DLL), написанную на быстром языке, таком как C++ (back end).
Именно эта концепция и была реализована в надстройках
Fitter
и
Chemometrics
.

Подробнее об использовании пользовательских функций для
расширения возможностей надстройки Chemometrics

можно прочитать
.

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

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

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

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

1. Выделяем любую пустую ячейку на листе, куда будут выводиться результаты вычислений. Жмем на кнопку «Вставить функцию», размещенную около строки формул.
2. Запускается Мастер функций. В перечне записей, который он представляет, ищем «МОПРЕД», выделяем этот элемент и жмем на кнопку «OK».
3. Открывается окно аргументов. Ставим курсор в поле «Массив». Выделяем весь диапазон ячеек, в котором расположена матрица. После того, как его адрес появился в поле, жмем на кнопку «OK».
4. Программа производит расчет определителя. Как видим, для нашего конкретного случая он равен – 59, то есть не тождественен нулю. Это позволяет сказать, что у данной матрицы существует обратная.

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

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

1. Выделяем ячейку, которая должна стать верхней левой ячейкой обратной матрицы. Переходим в Мастер функций, кликнув по значку слева от строки формул.
2. В открывшемся списке выбираем функцию МОБР. Жмем на кнопку «OK».
3. В поле «Массив», открывшегося окна аргументов функции, устанавливаем курсор. Выделяем весь первичный диапазон. После появления его адреса в поле, жмем на кнопку «OK».
4. Как видим, появилось значение только в одной ячейке, в которой была формула. Но нам нужна полноценная обратная функция, поэтому следует скопировать формулу в другие ячейки. Выделяем диапазон, равнозначный по горизонтали и вертикали исходному массиву данных. Жмем на функциональную клавишу F2, а затем набираем комбинацию Ctrl+Shift+Enter. Именно последняя комбинация предназначена для обработки массивов.
5. Как видим, после этих действий обратная матрица вычислена в выделенных ячейках.

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

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

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

Помогла ли вам эта статья?

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

Прежде всего, уясним одно правило:  Матрица имеет обратную только тогда, когда ее определитель не равен нулю.  А вот и задание: найдите матрицу, обратную к матрице А, где

Вычислять определитель этой матрицы мы умеем. Я его уже вычислил.

Он оказался равен -4, а  это значит, что у нашей матрицы есть обратная (если бы определитель оказался равен нулю, то мы сказали бы что матрица не имеет обратную и немедленно прекратили все вычисления). Теперь отметим ячейку, с которой начнем записывать ответ. Я отметил ячейку E1.  Нажимаем Формулы, затем Математические и в появившемся окне находим  МОБР

После нажатия появляется вот такое окно, в котором надо вписать адреса ячеек, в которых находятся элементы матрицы  в Массив

У нас элементы записаны в ячейки начиная с А1 и заканчивая в С3 , поэтому так и записываем (смотрите картинку)

Если все сделали правильно, то автоматически заполнится место, обведенное красным и запишется ответ, который обведен черным. В таком виде ответ трудно переваривать и поэтому нажимаем ОК.  В ячейке, которую мы застолбили под ответ, появилось число 3,  Это только первый элемент полученной обратной матрицы.

Чтобы виден был весь ответ, выполняем следующие действия: Начиная с  ячейки Е1 выделяем три строчки и три столбца (именно столько было у исходной матрицы и столько же будет у обратной)

нажимаем клавишу F2,  а затем на одновременно на три клавиши  Ctrl+Shift+Enter.

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

На этом все. Крепких вам знаний.

Рубрика: EXCEL в помощь, Статьи. Метки: EXCEL, ИКТ, матрица, обратная матрица

Умножение и деление матрицы на число в Excel

Способ 1

Рассмотрим матрицу А размерностью 3х4. Умножим эту матрицу на число k. При умножении матрицы на число получается матрица такой же размерности, что и исходная, при этом каждый элемент матрицы А умножается на число k.

Введем элементы матрицы в диапазон В3:Е5, а число k — в ячейку Н4. В диапазоне К3:N5 вычислим матрицу В, полученную при умножении матрицы А на число k: В=А*k. Для этого введем формулу =B3*$H$4 в ячейку K3, где В3 — элемент а₁₁ матрицы А.

Примечание: адрес ячейки H4 вводим как абсолютную ссылку, чтобы при копировании формулы ссылка не менялась.

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

Таким образом, мы умножили матрицу А в Excel и получим матрицу В.

Для деления матрицы А на число k в ячейку K3 введем формулу =B3/$H$4 и скопируем её на весь диапазон матрицы В.

Способ 2

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

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

Для выполнения деления в данном примере в диапазон вводим формулу =B3:E5/H4, т.е. знак «*» меняем на «/».

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

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

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

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

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

=ТРАНСП(A2:C4)

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

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

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

=МОПРЕД(A2:C4)

=МОПРЕД(E2:G4)

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

Полученные результаты:

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

=МОПРЕД(ТРАНСП(A2:C4))

Результат вычислений:

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

2.9. Виртуальный массив

При анализе данных часто возникает проблема сохранения
промежуточных результатов, которые нужны не сами по себе, а только для
того, чтобы вычислить по ним другие, полезные значения. Например,
остатки в методе PCA часто нам не интересны, а нужны только для
определения полной объясненной дисперсии, ортогональных расстояний и
т.п. При этом размеры таких промежуточных массивов могут быть очень
велики, да и к тому же их приходится вычислять при различных значениях
числа главных компонент. Все это ведет к заполнению рабочей книги
большим количеством ненужных, промежуточных результатов. Этого можно
избежать, если использовать виртуальные массивы. Поясним их суть на
простом примере. 

Рис.38 Пример использования виртуального
массива

Предположим, что задана матрица A, а
нужно вычислить детерминант матрицы AtA
. На Рис. 38 показаны два способа вычисления. Первый – через
последовательность промежуточных массивов, отмеченных красными
стрелками. Второй – с помощью одной формулы, показанной зеленой
стрелкой. Оба пути ведут к одному и тому же результату, но красный путь
занимает на листе много места, а зеленый последовательно использует
несколько промежуточных виртуальных массивов. Все они, по сути,
совпадают с реальными массивами красного пути, но на лист не выводятся.

Первый массив –  это транспонированная матрица At,
получаемая как результат функции
(A).

Второй виртуальный массив получается тогда, когда первый
виртуальный массив умножается на матрицу A с помощью
функции (TRANSPOSE(A), A).  

И, наконец, к этому, второму виртуальному массиву применяется функция
.

Виртуальные массивы очень полезны при вычислении всяческих
вспомогательных характеристик в анализе многомерных данных: остатков,
собственных значений, и т.п. Подробно об этом рассказывается в пособии
Расширение возможностей Chemometrics Add-In.