Функции для работы с матрицами в Excel
Содержание
MS Excel — универсальная программа для обработки различных видов данных. В частности, приложение позволяет выполнять вычисления с матрицами — одним из самых сложных видов визуализации числовых массивов. В данной статье рассмотрим основные операции для матрицы в Excel, и разберемся, какие функции предусмотрены в программе для расчетов.
Транспонирование матрицы
Одна из распространенных задач, с которой сталкиваются пользователи — выполнить транспонирование матриц в Excel. Данная операция заключается в том, чтобы поменять строки и столбцы местами. В нашем примере матрица в таблице Эксель состоит из 4 строк и 4 столбцов. Это говорит о том, что она является квадратной. Чтобы транспонировать такую матрицу в Excel:
- Выделяем весь массив с числами. Далее нажимаем правую кнопку мыши и выбираем команду копировать.
Копируем массив - Выделяем диапазон, в который нужно вставить транспонированную матрицу. В нашем примере она квадратная, поэтому число строк и столбцов по 4, как и в исходном варианте. Выделим диапазон, нажмем правую кнопку мыши и найдем кнопку «Специальная вставка». В выпадающем меню также выбираем вариант «Специальная вставка».
Нажимаем специальная вставка - Ставим отметку напротив команды «Транспонировать». Затем жмем .
Нажимаем транспонировать
В результате мы получим транспонированный массив с числами. Недостаток данного способа заключается в том, что при изменении исходного матричного набора, сам транспонированный дубликат изменяться не будет.
В Excel эту проблему можно решать с помощью функции ТРАНСП. Она принимает ссылку на массив в качестве аргумента. Вписываем формулу в первую ячейку диапазона, в который будет вставлена транспонированная матрица. Далее растягиваем формулу на остальные ячейки диапазона.
Появятся ошибки #ЗНАЧ!, так как формула была применена к ячейкам, а не к массиву данных. Чтобы исправить ситуацию, выделяем этот диапазон, нажимаем F2 и далее жмем ++. В результате мы получим транспонированный числовой массив.
Сложение и умножение
Работа с матрицами нередко предусматривает выполнение арифметических операций. Самый простой вариант — сложение. Массивы можно складывать при условии, что число строк и столбцов в них одинаковое. Для этого предусмотрена стандартная функция сложения. Разница в нашем примере заключается только в том, что нам нужно искать сумму массивов:
- Выделяем диапазон, в котором будет записана сумма складываемых матриц. Выделяем первую ячейку массива, а затем записываем ссылки на первые числа в массивах А и В.
Суммируем первые числа массивов - Далее с помощью маркера автозаполнения применяем формулу к остальным ячейкам диапазона. Так можно быстро сложить две матрицы с равным числом столбцов и строк.
Сумма матриц а и b
С умножением дело обстоит несколько сложнее. Чтобы посчитать произведение двух массивов, должно соблюдаться одно важное условие. Количество столбцов первой матрицы должно соответствовать числу строчек второй. Это правило удовлетворяется, если массивы квадратные.
Для того чтобы сделать решение для массива данных, применяем известную формулу МУМНОЖ, которую вводим в первую ячейку диапазона для записи результата. В качестве аргументов указываем ссылки на первую и вторую матрицы.
При растягивании формулы на остальные ячейки, будет отображена ошибка #ЗНАЧ! Как и в случае с формулой ТРАНСП, нажимаем и затем комбинацию ++. В результате получаем произведение двух массивов.
Поиск определителя
Еще один распространенный вопрос у пользователей при работе с массивами данных — как найти определитель матрицы в таблице Excel. Чтобы посчитать детерминант массива, должно соблюдаться одно важное условие — он должен быть квадратным. Если число строк и столбцов неравное, функция выдаст ошибку.
Вводим формулу МОПРЕД. Далее в качестве аргумента указываем ссылку на набор чисел. Чтобы расчет был выполнен правильно, обязательно нажмите сочетание клавиш ++. Так программа поймет, что считается именно массив, а не обычный набор ячеек.
В результате это Excel вернет определитель матрицы. Он может использоваться при решении линейных уравнений или других вычислительных операций в линейной алгебре.
Построение корреляционной матрицы
Матрица корреляции — это двумерный массив, значения которого определяют показатель коэффициента корреляции между переменными. Простыми словами, такая модель позволяет отследить линейную связь между числовыми значениями. Коэффициент варьируется от -1 до 1, где -1 свидетельствует об отрицательной линейной взаимосвязи, 0 говорит об отсутствии корреляции, а 1 является признаком наличия линейной связи. Матрицы используются в случае, если нужно сравнить несколько пар значений.
Рассмотрим способ построения:
- Если вы считаете, что для выполнения таких действий потребуется приобретать какое-то дополнительное ПО, то вы глубоко ошибаетесь. Все есть в самой программе и ничего приобретать не потребуется. В первую очередь нужно установить «Пакет анализа». Он есть по умолчанию в новых версиях Excel, его только нужно включить. Для этого переходим во вкладку «Файл» и нажимаем «Параметры». Далее переходим в раздел «Надстройки». Возле строки «Управление» нажимаем .
Переходим к активации надстроек - Ставим галочку напротив строчки «Пакет анализа» и нажимаем .
Включаем надстройку - Теперь можно приступить к построению матрицы корреляции. Учтите, что создание такого массива возможно только при наличии исходного набора данных. В нашем примере это будет сведения об оценках студентов, количестве посещенных занятий и пропусках.
Таблица для примера - На основе этих данных построим корреляционную матрицу. Открываем вкладку «Данные» и в правой части Панели инструментов нажимаем «Анализ данных». В выпадающем окне выбираем «Корреляция» и жмем .
Нажимаем корреляция в пакете анализ данных - В строке «Входной интервал» указываем ссылку на диапазон с данными, включая все заголовки. Оставляем значение «По столбцу» для параметра «Группирование». Обязательно оставляем отметку возле строчки «Метки в первой строке». В поле «Выходной интервал» указываем ссылку на ячейку, в котором будет начинаться корреляционная матрица. Остальная часть диапазона будет определена автоматически программой. Задав все параметры, нажимаем .
Задаем параметры
В результате мы получим корреляционную матрицу, с помощью которой можно отследить взаимосвязь между переменными. Как видно, число посещений положительно влияет на оценку экзамена, в то время как пропуски негативно. Это один из многочисленных способов применения корреляции для оценки нескольких наборов данных.
Подводим итоги
Построение и обработка двухмерных числовых массивов — одна из многочисленных функций MS Excel. В программе предусмотрен набор инструментов для создания матриц и выполнения различных вычислительных операций. В данном материале мы рассмотрели самые примеры таких расчетов, однако на практике набор инструментов для работы с массивами в Экселе значительно шире.
Загрузка...
