Как вставить матрицу в excel
Матрица в Excel
Вы будете перенаправлены на Автор24
Excel – это созданная корпорацией Microsof программа, предназначенная для работы с электронными таблицами.
Введение
Матрица представляет собой комплект ячеек, которые располагаются последовательно одна за другой и отображаются как прямоугольник. Операции с матрицами в Excel выполняются аналогично работе со стандартным диапазоном памяти. Все матрицы обладают отдельным адресом, который записывается так же, как и диапазон. Сначала указывается адрес первой ячейки диапазона, которая располагается вверху в левом углу. Далее записывается адрес последней ячейки, расположенной в нижнем правом углу.
Формулы массива
Матрица, по сути, является массивом и для работы с ними применяются соответствующие формулы. Главным их отличием от стандартных формул считается то, что обычные стандартные формулы могут вывести только одну величину. Чтобы использовать формулы работы с массивами, следует выполнить такой набор операций:
По завершению этих процедур в поле для ввода отобразится формула массива. Она отличается от стандартной формулы наличием фигурных скобок. Чтобы отредактировать или удалить формулу работы с массивом, нужно сделать выделение нужного диапазона и выполнить коррекцию. Для редактирования самой матрицы применяются те же комбинации клавиш, что и при её формировании.
Операции с матрицами
Операция замены местами строк и столбцов называется транспонированием. Перед началом этой процедуры, надо выполнить выделение отдельной зоны, имеющей число строк равное числу столбцов преобразуемой матрицы, и то же самое относительно столбцов. Существует два способа выполнения транспонирования. Согласно первому способу надо выполнить следующие действия:
Готовые работы на аналогичную тему
Второй способ заключается в следующем. Нужно выполнить выделение ячейки, находящейся в левом верхнем углу диапазона, выделенного для транспонируемой матрицы. Далее следует открыть диалоговое окно с набором функций и выбрать функцию ТРАНСП.
Рисунок 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:
Работа с матрицей в Power View
Важно: В Excel для Microsoft 365 Excel 2021 Power View удаляется 12 октября 2021 г. В качестве альтернативы вы можете использовать интерактивный визуальный эффект, предоставляемый Power BI Desktop,который можно скачать бесплатно. Вы также можете легко импортировать книги Excel в Power BI Desktop.
Матрица — это тип зрительного образа, который похож на таблицы тем, что состоит из строк и столбцов. Однако матрицу можно свертывать и развертывать по строкам или столбцам. Если она содержит иерархию, можно выполнять детализацию и укрупнение. В матрице могут выводиться итоги и промежуточные итоги по столбцам и строкам. Кроме того, в ней можно представлять данные без повторяющихся значений. Ниже приведен пример визуализации данных об олимпийских видах спорта, дисциплинах и соревнованиях.
Слева в таблице перечислены виды спорта и дисциплины для каждого соревнования.
Справа в матрице каждый вид спорта и дисциплина указаны только один раз.
Чтобы создать матрицу, нужно сначала создать таблицу, а затем преобразовать ее в матрицу.
На вкладке Конструктор в группе Представление переключателя щелкните Таблица > Матрица.
По умолчанию матрица содержит итоги и подытоги для групп, но их можно отключить.
На вкладке Конструктор щелкните Параметры > Итоги.
Чтобы добавить группы столбцов, перетащите поле в область Группы столбцов.
Совет: Если область Группы столбцов не отображена, на вкладке Конструктор выберите пункт Матрица.
Учите КОМПЬЮТЕР вместе с нами!
Учимся работать в Excel, Word, VBA. Финансовая математика, статистика, полезные подсказки
Страницы
пятница, 26 октября 2018 г.
Действия с матрицами в Excel
Этот же прием мы будем неоднократно использовать ниже, во время умножения матриц друг на друга, а также нахождения обратной матрицы.
И, как обещал, еще один, очень быстрый способ транспонирования с помощью буфера обмена. Сначала выделяем диапазон ячеек В2:Н5 с исходной матрицей и во вкладке «Главная» нажимаем кнопку «Копировать». Затем устанавливаем курсор мыши в ячейку, начиная с которой мы хотим получить транспонированную матрицу. В нашем случае это ячейка В17.
Во вкладке «Главная» нажимаем кнопку «Вставить», «Специальная вставка». В открывшемся окне выделяем флаг «Транспонировать», как показано на рисунке, и нажимаем кнопку «ОК».
В результате диапазон ячеек В17:Е23 сразу же заполнится транспонированной матрицей!
Конечный результат матричных преобразований имеет вид:
Если исходная матрица в ячейках В2:Н5 изменится, то транспонированная матрица в ячейках В8:Е14 пересчитается автоматически, так как сработает функция =ТРАНСП(). А вот в ячейках В17:Е23 матрица останется без изменений, обратите на это внимание!
2. Сложение матриц. Здесь нет никакой хитрости, все очень просто. Сложение выполняется для двух матриц одинаковой размерности. Каждый элемент суммарной матрицы равен сумме соответствующих элементов двух исходных матриц.
На данном рисунке в ячейках В2:D6 и F2:H6 приведены две исходные матрицы размерности 5х3, которые необходимо сложить.
Затем выделяем ячейку J2 еще раз, наводим острие курсора мыши на ее правый нижний угол, чтобы он принял вид крестика, и удерживая левую кнопку мыши, растягиваем формулу до ячейки L6.
3. Умножение матриц. Как было сказано выше, мы можем умножать матрицу на число или перемножать матрицы между собой.
В случае умножения исходной матрицы на число, мы должны каждый ее элемент умножить на это число, как показано на рисунке:
Исходная матрица находится в ячейках D4:F8. Умножим ее на число, которое записано в ячейке В6, то есть, на 12.
Для этого в ячейку Н4 я ввел формулу =D4*$B$6 и растянул ее за правый нижний угол до ячейки J8.
Умножение двух матриц выполняется встроенной функцией Excel =МУМНОЖ(). Здесь нужно обратить внимание:
Не все матрицы можно перемножать между собой. Для этого они должны соответствовать определенному правилу: количество столбцов первой матрицы должно равняться количеству строк второй матрицы. Если это равенство не соблюдается, такие матрицы перемножаться не могут.
В результате перемножения мы тоже должны получить матрицу, число строк которой равно числу строк первой матрицы, а число столбцов равно числу столбцов второй матрицы.
Учитывая все вышесказанное, получим:
В результате мы должны получить матрицу размерностью: 5х4. То есть, она должна иметь 5 строк, так как первая матрица тоже имеет 5 строк и должна иметь 4 столбца, так как вторая матрица имеет 4 столбца.
В результате ячейки K14:N18 будут содержать результат умножения исходных матриц друг на друга.
4. Обратная матрица. Нахождение обратной матрицы связано с использованием встроенной функции =МОБР() и также имеет ограничение:
Нахождение обратной матрицы возможно только тогда, когда число строк матрицы равняется числу ее столбцов. То есть, только когда матрица квадратная. Кроме того, исходная матрица не должна быть вырожденной. То есть, ее определитель не должен быть равен нулю.
5. Определитель матрицы. Определитель матрицы будем находить с помощью встроенной функции =МОПРЕД(). Как и в случае с обратной матрицей, определитель мы будем находить только для квадратной матрицы.
По аналогии с предыдущим примером, пусть в ячейках В2:F6 содержится исходная квадратная не вырожденная матрица. Тогда, для нахождения ее определителя введем в ячейку В9 формулу =МОПРЕД(В2:F6).
В данном случае функция возвращает единственное число, а не массив значений, поэтому никаких дополнительных действий не требуется.
Как сообщалось на нашем сайте ранее, с помощью встроенной функции Excel ТЕНДЕНЦИЯ() вы можете с легкостью строить линейные прогнозы для исходных показателей динамики.
Операции с матрицами в Excel
Под матрицей подразумевается набор ячеек, расположенных непосредственно друг возле друга и которые образуют вместе прямоугольник. Не требуется особых навыков, чтобы выполнять различные действия с матрицей, достаточно тех же, какие используются во время работы с классическим диапазоном.
Каждая матрица имеет свой адрес, записывающийся аналогичным диапазону способом. Первая составная часть – первая ячейка диапазона (расположенная в верхнем левом углу), а второй – последняя ячейка, которая находится в нижнем правом углу.
Формулы массива
В подавляющем количестве задач при работе с массивами (а матрицы и являются таковыми) используются формулы соответствующего типа. Базовое их отличие от обычных заключается в том, что последние выводят всего одно значение. Для применения формулы массива необходимо осуществить несколько действий:
После осуществления этих простых действий в поле ввода отображается формула массива. Ее можно отличить от обычной по фигурным скобкам.
Для редактирования, удаления формул массива, надо выделить требуемый диапазон и сделать то, что нужно. Чтобы редактировать матрицу, нужно использовать ту же комбинацию, что и для ее создания. При этом нет возможности редактировать отдельный элемент массива.
Что можно делать с матрицами
В целом, есть огромное количество действий, применение которых возможно для матриц. Давайте каждое из них рассмотрим более подробно.
Транспонирование
Многие люди не понимают значения этого термина. Представьте, что вам нужно поменять строки и колонки местами. Вот это действие и называется транспонированием.
Перед тем, как это осуществить, необходимо выделить отдельную область, которая имеет такое же количество строчек, сколько столбцов есть у исходной матрицы и такое же количество столбцов. Чтобы более наглядно понять, как это работает, посмотрите на этот скриншот.
Далее есть несколько методов, как можно осуществить транспонирование.
Первый способ следующий. Для начала нужно выделить матрицу, после чего скопировать ее. Далее выделяется диапазон ячеек, куда должен быть вставлен транспонированный диапазон. Далее открывается окно «Специальная вставка».
Там есть множество операций, но нам нужно найти радиокнопку «Транспонировать». После совершения этого действия нужно подтвердить его нажатием клавиши ОК. 
После нажатия кнопки ОК сначала будет показано, что вы допустили ошибку. Ничего в этом страшного нет. Все потому, что вставленная нами функция не определена, как формула массива. Поэтому нам нужно совершить такие действия:
Главное достоинство метода заключается в способности транспонированной матрицы сразу корректировать содержащуюся в ней информацию, как только вносятся данные в изначальную. Поэтому рекомендуется использовать именно данный способ.
Сложение
Эта операция возможна лишь применительно к тем диапазонам, количество элементов которых такое же самое. Проще говоря, у каждой из матриц, с которыми пользователь собирается работать, должны быть одинаковые размеры. И приводим скриншот для наглядности. 
В матрице, которая должна получиться, нужно выделить первую ячейку и ввести такую формулу.
=Первый элемент первой матрицы + Первый элемент второй матрицы
Далее подтверждаем ввод формулы с помощью клавиши Enter и используем автозаполнение (квадратик в правом нижнем углу), чтобы скопировать все значения на новую матрицу. 
Умножение
Предположим, у нас есть такая таблица, которую следует умножить на 12. 
Догадливый читатель может легко понять, что метод очень похож на предыдущий. То есть, каждая из ячеек матрицы 1 должна умножаться на 12, чтобы в итоговой матрице каждая ячейка содержала значение, умноженное на этот коэффициент.
При этом важно указывать абсолютные ссылки на ячейки.
Итого, получится такая формула.
=A1*$E$3 
Дальше методика аналогична предыдущей. Нужно это значение растянуть на необходимое количество ячеек.
Предположим, что необходимо перемножить матрицы между собой. Но есть лишь одно условие, при котором это возможно. Надо, чтобы количество столбцов и строк у двух диапазонов было зеркально одинаковое. То есть, сколько столбцов, столько и строк. 
Чтобы было более удобно, нами выделен диапазон с результирующей матрицей. Надо переместить курсор на ячейку в верхнем левом углу и ввести такую формулу =МУМНОЖ(А9:С13;Е9:H11). Не стоит забыть нажать Ctrl + Shift + Enter. 
Обратная матрица
Поиск определителя матрицы
Для начала ставится курсор в какой-угодно ячейке. Далее мы вводим =МОПРЕД(A1:D4)
Несколько примеров
Давайте для наглядности рассмотрим некоторые примеры операций, которые можно осуществлять с матрицами в Excel.
Умножение и деление
Метод 1
Предположим, у нас есть матрица A, имеющая три ячейки в высоту и четыре – в ширину. Также есть число k, которое записывается в другой ячейке. После выполнения операции умножения матрицы на число появится диапазон значений, имеющий аналогичные размеры, но каждая ее часть умножается на k. 
Диапазон B3:E5 – это исходная матрица, которая будет умножаться на число k, которое в свою очередь расположено в ячейке H4. Результирующая матрица будет находиться в диапазоне K3:N5. Исходная матрица будет называться A, а результирующая – B. Последняя образуется путем умножения матрицы А на число k.
Далее вводится =B3*$H$4 в ячейку K3, где В3 — элемент A11 матрицы А.
Не стоит забывать о том, ячейку H4, где указано число k необходимо вводить в формулу с помощью абсолютной ссылки. Иначе значение будет изменяться при копировании массива, и результирующая матрица потеряет работоспособность. 
Далее маркер автозаполнения (тот самый квадратик в правом нижнем углу) используется для того, чтобы скопировать значение, полученное в ячейке K3, во все другие ячейки этого диапазона. 
Вот у нас и получилось умножить матрицу A на определенное число и получить на выходе матрицу B.
Деление осуществляется аналогичным образом. Только вводить нужно формулу деления. В нашем случае это =B3/$H$4.
Метод 2
Итак, основное отличие этого метода в том, в качетве результата выдается массив данных, поэтому нужно применить формулу массива, чтобы заполнить весь набор ячеек.
Необходимо выделить результирующий диапазон, ввести знак равно (=), выделить набор ячеек, с соответствующими первой матрице размерами, нажать на звездочку. Далее выделяем ячейку с числом k. Ну и чтобы подтвердить свои действия, надо нажать на вышеуказанную комбинацию клавиш. Ура, весь диапазон заполняется. 
Деление осуществляется аналогичным образом, только знак * нужно заменить на /.
Сложение и вычитание
Давайте опишем несколько практических примеров использования методов сложения и вычитания на практике.
Метод 1
Не стоит забывать, что возможно сложение лишь тех матриц, размеры которых одинаковые. В результирующем диапазоне все ячейки заполняются значением, являющим собой сумму аналогичных ячеек исходных матриц.
Предположим, у нас есть две матрицы, имеющие размеры 3х4. Чтобы вычислить сумму, следет в ячейку N3 вставить такую формулу:
Тут каждый элемент являет собой первую ячейку матриц, которые мы собрались складывать. Важно, чтобы ссылки были относительными, поскольку если использовать абсолютные, не будут отображаться правильные данные. 
Далее, аналогично умножению, с помощью маркера автозаполнения распространяем формулу на все ячейки результирующей матрицы. 
Вычитание осуществляется аналогично, за тем лишь исключением, что используется знак вычитания (-), а не сложения.
Метод 2
Аналогично методу сложения и вычитание двух матриц, этот способ подразумевает использование формулы массива. Следовательно, в качестве ее результата будет выдаваться сразу набор значений. Поэтому нельзя редактировать или удалять какие-то элементы.
Сперва надо выделить диапазон, отделенный под результирующую матрицу, а потом нажать на «=». Затем надо указать первый параметр формулы в виде диапазона матрицы А, нажать на знак + и записать второй параметр в виде диапазона, соответствующему матрице B. Подтверждаем свои действия нажатием комбинации Ctrl + Shift + Enter. Все, теперь вся результирующая матрица заполнена значениями. 
Пример транспонирования матрицы
Выделяем диапазон для ячеек матрицы АТ. 
Далее переходим в окно «Аргументы функции», где вводится диапазон B3:E5, который повторяет матрицу А. Далее надо нажать Shift + Ctrl, после чего кликнуть «ОК».
В результате, у нас получается такая транспонированная таблица, которая изменяет свои значения вслед за исходной. 
Поиск обратной матрицы
Теперь опишем, как это делать на практике. Сначала необходимо выделить диапазон G3:I5 (там будет располагаться обратная матрица). Необходимо найти на вкладке «Формулы» пункт «Вставить функцию». 
Выводы
Мы разобрали некоторые примеры, как можно работать с матрицами в Excel, а также описали теорию. Оказывается, что это не так страшно, как может показаться на первый взгляд, не так ли? Это только звучит непонятно, но на деле с матрицами среднестатистическому пользователю приходится иметь дело каждый день. Они могут использоваться почти для любой таблицы, где есть сравнительно небольшое количество данных. И теперь вы знаете, как можно себе упростить жизнь в работе с ними.