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

Вычислим определитель (детерминант) матрицы с помощью функции

МОПРЕД()

или англ. MDETERM, разложением по строке/столбцу (для 3 х 3) и по определению (до 6 порядка).

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

Для вычисления определителя в MS EXCEL есть специальная функция

МОПРЕД()

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

файл примера

).

Массив может быть задан не только как интервал ячеек, например

A7:B8

, но и как

массив констант

, например

=МОПРЕД({5;4:3;2})

. Запись с использованием массива констант позволяет не указывать элементы в отдельных ячейках, а разместить их в ячейке вместе с функцией. Массив в этом случае указывается по строкам: например, сначала первая строка 5;4, затем через двоеточие записывается следующая строка 3;2. Элементы отделяются точкой с запятой.

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

именованный диапазон

.

Для матриц порядка 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

х

n

является сумма, содержащая

n!

слагаемых (

=ФАКТР(n)

). Каждое слагаемое представляет собой произведение

n

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

А

. Перед

k-ым

слагаемым появляется коэффициент

(-1)

, если элементы матрицы

А

в произведении упорядочены по номеру строки, а количество инверсий в

k-ой

перестановке множества номеров столбцов нечетно.

где (

α

₁

,

α

₂

,…,

α

_n

) – перестановка чисел от 1 до

n

, N(

α

₁

,

α

₂

,…,

α

_n

) – число

инверсий в перестановке

, суммирование идёт по всем возможным перестановкам порядка

n

.

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

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

• а21*а12*а33
• а21*а32*а13
• а11*а32*а23
• а11*а22*а33
• а31*а22*а13
• а31*а12*а23

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

Посмотрим на формулу выше (см. определение). Предположим, что второй индекс у каждого элемента матрицы (от 1 до n) соответствует номеру столбца матрицы (хотя это может быть номер строки (это не важно т.к. определители матрицы и ее

транспонированной матрицы

равны). Таким образом, второй индекс у первого элемента в произведении всегда равен 1, у второго – 2, у третьего 3. Тогда первые индексы у элементов соответствуют номеру строки и, в соответствии с определением, должны определяться из перестановок чисел от 1 до 3, т.е. из перестановок множества (1, 2, 3).

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

Число перестановок множества из 3-х чисел =3!=6 (что, конечно, равно числу слагаемых в выражении для расчета определителя, т.к. каждому слагаемому соответствует своя перестановка). Для матрицы 3х3 все перестановки приведены в примечании выше. Можно убедиться, что в каждом слагаемом первые индексы у элементов равны соответствующим числам в перестановке. Например, для слагаемого а21*а12*а33 использована перестановка (2, 1, 3).

СОВЕТ

: Для матрицы 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 слагаемых! Т.е. без использования

МОПРЕД()

не обойтись (ну, или можно вычислить определитель вручную методом Гаусса).

В этой статье описаны синтаксис формулы и использование MDETERM
 в Microsoft Excel.

Описание

Возвращает определитель матрицы (матрица хранится в массиве).

Синтаксис

МОПРЕД(массив)

Аргументы функции МОПРЕД описаны ниже.

• Массив    Обязательный. Числовой массив с равным количеством строк и столбцов.

Замечания

• Массив может быть задан как интервал ячеек, например A1:C3, как массив констант, например {1;2;3:4;5;6:7;8;9}, как имя для интервала или массива.

• Функция МОПРЕД возвращает значение ошибки #ЗНАЧ! в случаях, указанных ниже.

  • Если какая-либо ячейка в массиве пуста или содержит текст.

  • Если количество строк в массиве не равно количеству столбцов.

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

МОПРЕД(A1:C3)
равно A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*C1)

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

• Функция МОПРЕД производит вычисления с точностью примерно 16 значащих цифр, что может в некоторых случаях приводить к незначительным ошибкам. Например, определитель сингулярной матрицы отличается от нуля на 1E-16.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Данные	Данные	Данные	Данные
1	3	8	5
1	3	6	1
1	1	1	0
7	3	10	2
Формула	Описание	Результат
=МОПРЕД(A2:D5)	Определитель приведенной выше матрицы.	88
=МОПРЕД({3;6;1:1;1;0:3;10;2})	Определитель матрицы, представленной в виде массива констант.	1
=МОПРЕД({3;6:1;1})	Определитель матрицы в массиве констант.	-3
=МОПРЕД({1;3;8;5:1;3;6;1})	Возвращает сообщение об ошибке, так как массив имеет разное количество строк и столбцов.	#ЗНАЧ!

Функция МОПРЕД в Excel используется для работы с прямоугольными матрицами. Задаваемыми в качестве статических массивов или диапазонов ячеек с числовыми данными, и вычисляет детерминант (определитель) исследуемой матрицы.

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

Пример функции МОПРЕД для вычисления детерминанта матрицы в 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 детерминант – доказан!



Решение системы линейных уравнений по методу Крамера в Excel

Пример 2. Решить систему линейных уравнений с использованием метода Крамера. Для расчета необходимо найти определители нескольких матриц.

Вид таблицы данных:

Для нахождения решений методом Крамера выделим три матрицы.

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

=МОПРЕД(A6:B7)

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

Так как детерминант основной матрицы (Матрица 1) не равен нулю, система имеет единственное решение. Для нахождения значения переменных X и Y используем формулы:

=МОПРЕД(C6:D7)/B8

=МОПРЕД(E6:F7)/B8

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

Принцип работы функции МОПРЕД в Excel

Функция МОПРЕД имеет следующую синтаксическую запись:

=МОПРЕД(массив)

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

Примечания:

1. Диапазон ячеек или статический массив должен иметь равное количество строк и столбцов, иначе результатом работы функции МОПРЕД будет код ошибки #ЗНАЧ!.
2. Если диапазон ячеек или массив, переданные в качестве аргумента рассматриваемой функции, содержат текстовые данные или пустые значения, в результате будет возвращен код ошибки #ЗНАЧ!.
3. Функция МОПРЕД значительно упрощает процесс расчета детерминанта матрицы. Пользователь Excel может выполнить расчеты самостоятельно. Например, для прямоугольной матрицы, значения которой находятся в диапазоне A1:C3 рассчитать детерминант можно следующим способом: A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*C1).
4. Точность расчетов функции МОПРЕД составляет примерно 1E-16, то есть до 16 знаков после запятой. Для более точных расчетов (что требуется крайне редко) используют другие методы определения детерминанта матрицы.
5. Значение детерминанта используют для поиска решений системы линейных уравнений.

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

Источник

Матрица в Excel

Эксперт по предмету «Информатика»

С нами работают 108 689 преподавателей из 185 областей знаний. Мы публикуем только качественные материалы

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

Введение

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

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

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

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

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

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

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

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

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

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

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

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

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

Рисунок 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:

Источник

Создание матрицы в 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.

Программа 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 — элемент а₁₁ матрицы А.

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

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

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

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

Способ 2

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

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

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

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

Способ 1

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

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

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

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

Способ 2

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Транспонирование матрицы в Excel

Транспонирование матрицы — операция над матрицей, при которой столбцы заменяются строками с соответствующими номерами. Обозначим транспонированную матрицу А Т .

Пусть дана матрица А размерностью 3х4, с помощью функции =ТРАНСП() вычислим транспонированную матрицу А Т , причем размерность этой матрицы будет 4х3.

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

На вкладке Формулы выберем Вставить функцию, выберем категорию Ссылки и массивы — функция ТРАНСП — ОК.

В диалоговом окне Аргументы функции указываем диапазон массива В3:Е5, содержащего элементы матрицы А. Нажимаем на клавиатуре сочетание клавиш Shift+Ctrl и щелкаем левой кнопкой мыши по кнопке ОК.

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

Нажмите для увеличения

Мы получили транспонированную матрицу.

Нахождение обратной матрицы в Excel

Матрица А -1 называется обратной для матрицы А, если АžА -1 =А -1 žА=Е, где Е — единичная матрица. Следует отметить, что обратную матрицу можно найти только для квадратной матрицы (одинаковое количество строк и столбцов).

Пусть дана матрица А размерностью 3х3, найдем для неё обратную матрицу с помощью функции =МОБР().

Для этого выделим диапазон G3:I5, который будет содержать элементы обратной матрицы, на вкладке Формулы выберем Вставить функцию.

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

В диалоговом окне Аргументы функции указываем диапазон массива В3:D5, содержащего элементы матрицы А. Нажимаем на клавиатуре сочетание клавиш Shift+Ctrl и щелкаем левой кнопкой мыши по кнопке ОК.

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

Нажмите для увеличения

Мы получили обратную матрицу.

Нахождение определителя матрицы в Excel

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

Как найти определить матрицы в Excel

Пусть дана матрица А размерностью 3х3, вычислим для неё определитель с помощью функции =МОПРЕД().

Для этого выделим ячейку Н4, в ней будет вычислен определитель матрицы, на вкладке Формулы выберем Вставить функцию.

В диалоговом окне Вставка функции выберем категорию Математические — функция МОПРЕД — ОК.

В диалоговом окне Аргументы функции указываем диапазон массива В3:D5, содержащего элементы матрицы А. Нажимаем ОК.

Нажмите для увеличения

Мы вычислили определитель матрицы А.

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

Нажмите для увеличения

Мы можем удалить только все элементы этой матрицы.

Видеоурок

Кратко об авторе:

Шамарина Татьяна Николаевна — учитель физики, информатики и ИКТ, МКОУ «СОШ», с. Саволенка Юхновского района Калужской области. Автор и преподаватель дистанционных курсов по основам компьютерной грамотности, офисным программам. Автор статей, видеоуроков и разработок.

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

Есть мнение?
Оставьте комментарий

Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями

Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст

Матрица БКГ – уникальная матрица, которая помогает на основе исходных данных построить диаграмму и провести анализ всех сегментов рынка. Создана была матрица Бостонской консалтинговой группой, откуда и получила свое название.

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

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

· «собаки» – товары, которые представляют незначительную долю рынка в своем сегменте низкого темпа роста; эти товары являются менее перспективными, поэтому производство данного сегмента не будет иметь успеха;

· «трудные дети» – товары, которые могут быстро выбиться в перспективный сегмент, но при этом еще занимают малую часть всего рынка; товары с хорошими темпами развития, но требующие финансовых и инвестиций;

· «дойные коровы» – сегмент рынка с постоянным, но незначительным доходом, которые при этом не требуют никаких инвестиций; их доля значительная, но в слабо растущем сегменте рынка;

· «звезды» – товары с существенной доле быстроразвивающегося рынка, которые имеют наибольший успех; с первых дней приносят хороший доход, а будущие вложения в данный сегмент смогут лишь увеличить прибыль.

Отношение темпа роста относительно сегментов рынка можно отобразить:

Суть массива БКГ – найти определенный сегмент рыка, к которой можно отнести исходную группу или единичный товар.

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

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

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

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

4. Выберем необходимые вводные. Откроем функции и укажем на пункт «Выбрать данные».

5. В окне выбора данных нажимаем на «Изменить» и начинаем заполнять изменения ряда пузырьковой диаграммы.

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

7. Проведем подобные действия для всех групп и получаем итоговую пузырьковую диаграмму. Осталось лишь корректно настроить оси.

8. Нужно немного подкорректировать оси. Для начала в горизонтальных осях изменяем «Минимальное значение» на «0», «Максимальное» – на «2», а «Деления» на «1».

9. В настройках вертикальных осей устанавливаем «Минимальное» на 0, «Максимальное» на «2.18», а деления на «1.09». Эти показатели высчитываются из среднего показателя относительной доли рынка, который необходимо умножить на 2. «Деления» также устанавливаем «1.09». Последнее что укажем – «Значение оси» – «1.09» соответственно.

10. Осталось подписать наши оси и можно приступать к непосредственному анализу матрицы БКГ.

Матрица БКГ дает возможность провести быстрый и корректный анализ сегментов рынка.

В нашем случае мы видим, что:

«Товар 2» и «Товар 5» относятся к группе товаров «Собаки» – они не приносят прибыль. Они не имеют популярности на рынке, поэтому они в дальнейшем стратегии продаж нам больше не интересны.

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

«Товар 3» и «Товар 4» – «Дойные коровы» – отличная выручка дает возможность развивать другие категории, при этом не вкладывая инвестиции в данный сегмент.

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

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

Источник