Матрица в Excel

Excel – это созданная корпорацией Microsof программа, предназначенная для работы с электронными таблицами.

Введение

Формулы массива

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

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

Операции с матрицами

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

1. Нужно выполнить выделение матрицы и сделать её копию.
2. Выполнить выделение диапазона ячеек для вставки транспонируемого диапазона.
3. Открыть окно «Специальная вставка».
4. Выбрать кнопку «Транспонировать» и нажать ОК.

Готовые работы на аналогичную тему

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

Рисунок 1. Окно программы. Автор24 — интернет-биржа студенческих работ

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

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

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

Рисунок 2. Пример. Автор24 — интернет-биржа студенческих работ

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

= Начальный компонент первой матрицы + Начальный компонент второй матрицы

Затем следует подтвердить задание формулы клавишей Enter и применить функцию авто заполнения (квадрат в нижнем правом углу) для копирования всех величин в новую матрицу. Итог приведён на рисунке ниже:

Рисунок 3. Итог. Автор24 — интернет-биржа студенческих работ

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

Рисунок 4. Таблица. Автор24 — интернет-биржа студенческих работ

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

Рисунок 5. Результирующая матрица. Автор24 — интернет-биржа студенческих работ

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

Рисунок 6. Перемножение матриц. Автор24 — интернет-биржа студенческих работ

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

Далее следует нажать комбинацию клавиш Ctrl + Shift + Enter, чтобы увидеть итог:

Рисунок 7. Итог. Автор24 — интернет-биржа студенческих работ

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

В качестве аргумента нужно указать диапазон, для которого следует сформировать обратную матрицу. Далее нужно использовать комбинацию клавиш Ctrl + Shift + Enter.

Рисунок 8. Окно программы. Автор24 — интернет-биржа студенческих работ

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

Далее рассмотрим ещё один пример вычислений. Имеется матрица А, размером три на четыре. Есть, так же, некоторое число k, записанное вне матрицы. Когда будет выполнена операция умножения матрицы на это число, возникнет диапазон величин, который имеет такие же размеры, но все его компоненты умножены на k:

Рисунок 9. Окно программы. Автор24 — интернет-биржа студенческих работ

Диапазон B3:E5 является исходной матрицей, подлежащей умножению на число k, расположенному в клетке H4. Итоговая матрица будет располагаться в диапазоне K3:N5. Исходная матрица обозначается как А, а итоговая как В. Итоговая матрица В будет образована умножением матрицы А на величину k. Формула для вычислений записывается в ячейку К3:

[expert_bq id=»1570″]Следовательно, ожидаемое значение может быть рассчитано как сумма всех значений, умноженная на обратную величину количества значений. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Ковариационная матрица предоставляет полезный инструмент для разделения структурированных отношений в матрице случайных величин Это можно использовать для декорреляции переменных или применять в качестве преобразования к другим переменным. Это ключевой элемент, используемый в методе сокращения данных анализа основных компонентов, или сокращенно PCA.

Как посчитать матрицу в экселе

1. Нужно выполнить выделение матрицы и сделать её копию.
2. Выполнить выделение диапазона ячеек для вставки транспонируемого диапазона.
3. Открыть окно «Специальная вставка».
4. Выбрать кнопку «Транспонировать» и нажать ОК.

Далее рассмотрим ещё один пример вычислений. Имеется матрица А, размером три на четыре. Есть, так же, некоторое число k, записанное вне матрицы. Когда будет выполнена операция умножения матрицы на это число, возникнет диапазон величин, который имеет такие же размеры, но все его компоненты умножены на k:

учимся
программировать

Программированию нельзя научить, можно только научится

Главная » Уроки по Численным методам » Урок 12. Ранг матрицы. Вычисление ранга матрицы. Норма матриц

Урок №12. Ранг матрицы. Вычисление ранга матрицы. Норма матриц.

Уровень 1

Задание 1. Найдите ранг матрицы А с помощью Excel.

Рисунок 1. Исходная матрица

1. Первую строку оставляем без изменений. Скопируйте первую строку в ячейки B6:E6.
2. Чтобы избежать появления дробей, умножим вторую, третью и четвертую строки на 2. Для этого введите в ячейку B7 формулу =2*B2. Скопируйте эту форму с помощью маркера заполнения в ячейки B7:E9 (Маркер заполнения — небольшой черный квадрат в правом нижнем углу ячейки. При наведении на него курсор принимает вид черного креста.). В результате должна получиться матрица А1 (рис.2).

Рисунок 2. 2-я,3-я,4-я строки умножены на 2.

1. Первую строку оставим без изменения (скопируйте в ячейки B11:E11).
2. В ячейку B12 внесите формулу =B7+B$6*(-$B7/$B$6). Скопируйте эту формулу в ячейки C12:E14 с помощью маркера заполнения. В итоге имеем матрицу А2 (рис.3)

1. Переходим к вычислению матрицы А3. Первую и вторую строки оставляем без изменения. Для этого выделите ячейки B11:E12, нажмите кнопку «Копировать», далее выделите ячейку B16 и в контекстном меню ячейки выберите «Специальная вставка». В открывшемся окне выберите пункт «значения» (рис.4) и нажмите ОК. В результате будут скопированы только значения ячеек, без формул.

Рисунок 4. Специальная вставка

Рисунок 6. Матрица А4 — результирующая
Ответ: Базисный минор матрицы А4 стоит в первых трех столбцах и первых трех строках, . Следовательно, r(A)=3.

Рисунок 7. Общий вид листа вычисления

Рисунок 8. Для проверки формул

Уровень 2.

Задание 2. Найти первую норму матрицы А.

1. Далее нужно взять по модулю все значения матрицы. Для вычисления модуля в Excel используется функция ABS. Запишем результат в матрицу A1. Для этого в ячейку В5 внесите формулу =ABS(B1) и скопируйте ее в диапазон B5:C7 (рис.12)

Рисунок 13. Сумма по столбцам

Рисунок 14. Результат
Задание 3: исправляя на листе вычислений, найдите норму матрицы А (рис.15)

Рисунок 15.
Ответ: норма равная 15.

Уровень 3.

Задание 4. Самостоятельно в Excel выполнить вычисления второй и третьей нормы матрицы А (рис.11).
Подсказка: для вычисления корня квадратного используется
Задание 5. Самостоятельно выполнить вычисление ранга матрицы А:

Рисунок 9. Матрица А
Самопроверка: Ответ: ранг матрицы равен 4.

[expert_bq id=»1570″]Вместо этого у него есть функция для вычисления ковариационной матрицы с именем cov , которую мы можем использовать для получения ковариации. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Знак ковариации можно интерпретировать как то, увеличиваются ли две переменные вместе (положительно) или уменьшаются вместе (отрицательно). Величина ковариации не легко интерпретируется. Нулевое значение ковариации указывает, что обе переменные полностью независимы.

Нежное введение в ожидаемую стоимость, дисперсию и ковариацию с NumPy

1. Первую строку оставим без изменения (скопируйте в ячейки B11:E11).
2. В ячейку B12 внесите формулу =B7+B$6*(-$B7/$B$6). Скопируйте эту формулу в ячейки C12:E14 с помощью маркера заполнения. В итоге имеем матрицу А2 (рис.3)

6. В «Имя ряда» устанавливаем ячейку «Наименование». «Значения Х» будут подтягиваться со столбца «Относительная доля рынка», в «Значения Y» — «Темп роста рынка». «Размеры пузырьков» будут браться со диапазона «Текущий период». На этом ввод значений завершаем и сформировать диаграмму.

Как посчитать матрицу в экселе

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

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

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

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

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

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

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

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

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

В программе 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.

Вычислим определитель (детерминант) матрицы с помощью функции МОПРЕД() или англ. MDETERM, разложением по строке/столбцу (для 3 х 3) и по определению (до 6 порядка).

Определитель матрицы (det) можно вычислить только для квадратных матриц, т.е. у которых количество строк равно количеству столбцов.

Для вычисления определителя в MS EXCEL есть специальная функция МОПРЕД() . В аргументе функции необходимо указать ссылку на диапазон ячеек (массив), содержащий элементы матрицы (см. файл примера ).

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

Для матриц порядка 2 можно определитель можно вычислить без использования функции МОПРЕД() . Например, для вышеуказанной матрицы выражение =A7*B8-B7*A8 вернет тот же результат.

Для матрицы порядка 3, например размещенной в диапазоне A16:C18, выражение усложняется =A16*(B17*C18-C17*B18)-B16*(A17*C18-C17*A18)+C16*(A17*B18-B17*A18) (разложение по строке).

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

Свойства определителя

Теперь о некоторых свойствах определителя (см. файл примера ):

• Определитель транспонированной матрицы равен определителю исходной матрицы
• Если в матрице все элементы хотя бы одной из строк (или столбцов) нулевые, определитель такой матрицы равен нулю
• Если переставить местами две любые строки (столбца), то определитель полученной матрицы будет противоположен исходному (то есть, изменится знак)
• Если все элементы одной из строк (столбца) умножить на одно и тоже число k, то определитель полученной матрицы будет равен определителю исходной матрицы, умноженному на k
• Если матрица содержит строки (столбцы), являющиеся линейной комбинацией других строк (столбцов), то определитель =0
• det(А)=1/det(А -1 ), где А -1 — матрица обратная матрице А (А — квадратная невырожденная матрица).

Вычисление определителя матрицы по определению (до 6 порядка включительно)

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

Как было показано выше для вычисления матриц порядка 2 и 3 существуют достаточно простые формулы и правила. Для вычисления определителя матриц более высокого порядка (без использования функции МОПРЕД() ) придется вспомнить определение:

где ( α ₁, α ₂. α_n ) — перестановка чисел от 1 до n , N( α ₁, α ₂. α_n ) — число инверсий в перестановке, суммирование идёт по всем возможным перестановкам порядка n .

Попытаемся разобраться в этом непростом определении на примере матрицы 3х3.

Для матрицы 3 х 3, согласно определения, число слагаемых равно 3!=6, а каждое слагаемое состоит из произведения 3-х элементов матрицы. Ниже приведены все 6 слагаемых, необходимых для вычисления определителя матрицы 3х3:

а21, а12 и т.д. — это элементы матрицы. Теперь поясним, как были сформированы индексы у элементов, т.е. почему, например, есть слагаемое а11*а22*а33, а нет а11*а22*а13.

Теперь понятно, почему среди слагаемых нет а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

СОВЕТ: Для матрицы 4 порядка существует 4! перестановок, т.е. 26, что соответствует 26 слагаемым, каждое из которых является произведением различных 4-х элементов матрицы. Все 26 перестановок можно найти в статье Перебор всех возможных Перестановок в MS EXCEL.

Теперь, когда разобрались со слагаемыми, определим множитель перед каждым слагаемым (он может быть +1 или -1). Множитель определяется через четность числа инверсий соответствующей перестановки.

Примечание: Об инверсиях перестановок (и четности числа инверсий) можно почитать, например, в статье Перестановки без повторений: Комбинаторика в MS EXCEL

Например, первому слагаемому соответствует перестановка (2, 1, 3), у которой 1 инверсия (нечетное число) и, соответственно, -1 в степени 1 равно -1. Второму слагаемому соответствует перестановка (2, 3, 1), у которой 2 инверсии (четное число) и, соответственно, -1 в степени 2 равно 1 и т.д.

Сложив все слагаемые: (-1)*(а21*а12*а33)+(+1)*(а21*а32*а13)+(-1)*(а11*а32*а23)+(+1)*(а11*а22*а33)+(-1)*(а31*а22*а13)+(+1)*(а31*а12*а23) получим значение определителя.

В файле примера на листе 4+, и зменяя порядок матрицы с помощью элемента управления Счетчик, можно вычислить определитель матрицы до 6 порядка включительно.

Следует учитывать, что при вычислении матрицы 6-го порядка в выражении используется уже 720 слагаемых (6!). Для 7-го порядка пришлось бы сделать таблицу для 5040 перестановок и, соответственно, вычислить 5040 слагаемых! Т.е. без использования МОПРЕД() не обойтись (ну, или можно вычислить определитель вручную методом Гаусса).

[expert_bq id=»1570″]Для этого нужно разделить по каждому наименованию товара величину продаж за текущий период на величину продаж за предыдущий период. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Есть еще много интересных вариантов использования и свойств, не описанных в этой статье: 1) связь между ковариацией и корреляцией 2) нахождениеближайшая корреляционная матрица3) применения ковариационной матрицы в фильтрах Калмана, расстоянии Махаланобиса и анализе главных компонент 4) как рассчитать собственные векторы ковариационной матрицы и собственные значения 5) как оптимизируются модели гауссовой смеси.

Интересные свойства ковариационной матрицы

• Определитель транспонированной матрицы равен определителю исходной матрицы
• Если в матрице все элементы хотя бы одной из строк (или столбцов) нулевые, определитель такой матрицы равен нулю
• Если переставить местами две любые строки (столбца), то определитель полученной матрицы будет противоположен исходному (то есть, изменится знак)
• Если все элементы одной из строк (столбца) умножить на одно и тоже число k, то определитель полученной матрицы будет равен определителю исходной матрицы, умноженному на k
• Если матрица содержит строки (столбцы), являющиеся линейной комбинацией других строк (столбцов), то определитель =0
• det(А)=1/det(А -1 ), где А -1 — матрица обратная матрице А (А — квадратная невырожденная матрица).

Чтобы изменить или удалить формулу массива, нужно выделить весь диапазон и выполнить соответствующие действия. Для введения изменений применяется та же комбинация (Ctrl + Shift + Enter). Часть массива изменить невозможно.