Как транспонировать матрицу в excel

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

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

• Главная»
• Численные методы»
• Лабораторные работы»
• Практические работы 2, 3. Действия над матрицами в Excel

Практическая работа 2. Действия над матрицами в Excel

Цель: научится применять возможности программы MS Excel для выполнения действий над матрицами.

Каждое задание выполнять на отдельном листе рабочей книги Excel

Уровень 1

1.Транспонирование матриц

Заполните ячейки таблицы значениями элементов матрицы (рис.1).

Рисунок 1.

Рисунок 2.

Рисунок 3.

Рисунок 4.

Рисунок 5.

2. Умножение матрицы на число
Задание 2. Дана матрица А (рис.6). Получить матрицу B=3*А.
Ход работы:

1. Введите матрицу (рис.6).
2. Выделите ячейку E1 и введите формулу =3*A1.
3. Скопируйте введенную формулу в остальные ячейки результирующей матрицы: для этого наведите курсор на точку в правом нижнем углу ячейки, так, чтобы курсор изменился на тонкий крестик, нажмите на левую кнопку мыши и протяните до ячейки G1. Таким же образом протяните указатель до ячейки G2.
4. В результате должна получиться матрица B (рис.7):

Рисунок 6. Матрица A

Рисунок 7. Матрица B

3. Сложение матриц
Задание 3. Сложить две матрицы A и B (даны на рис.8).

Рисунок 8.

Ход работы:

1. Введите две матрицы A и B (рис.8).
2. Выделите первую ячейку результирующей матрицы D5 и внесите формулу =B1+F1.
3. Скопируйте формулу на оставшиеся ячейки матрицы C.

Рисунок 9. Результат

Уровень 2

4.Умножение матриц
Задание 4. Даны матрицы А и В (рис.10). Найти их произведение С=А*В.

Рисунок 10.

Ход работы:

1. Выделяем мышкой при нажатой левой кнопке соответствующий диапазон ячеек D5:E7 (строк такое же количество как в матрице А, а столбцов такое же количество как в матрице В).
2. Вызываем мастер функций и в категории «Полный алфавитный перечень находим функцию «МУМНОЖ» и нажимаем ОК.

1. В появившемся окне вводим диапазон значений исходных матриц А и В (рис.11).

Рисунок 11.

1. Для получения результата нажимаем сочетание клавиш «Shift»+«Ctrl»+«Enter».

Рисунок 12
Задание 5. Самостоятельно с помощью функции ТРАНС транспонировать следующую матрицу.

Рисунок 13.

Уровень 3

Задание 6. Самостоятельно выполнить с помощью Excel умножение матриц А и В. Даны А и В. В результате вычислений должна получиться матрица C (рис.14)

Рисунок 14.

Задание 7. Даны матрицы А, В, С и число a=2. Найти

Подсказка: Все вычисления выполнять на одном листе. Сначала вычислить, затем умножить матрицы , далее умножить матрицу С на число a, затем сложить матрицы и aС.
Тест: результат
Задание 8. Даны матрицы А, В, С и число a=2. Найти

Тест: результат

Практическая работа 3. Действия над матрицами.

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

1. Какая функция в Excel используется для транспонирования матрицы?
2. Какая функция в Excel используется для умножения матриц?

Уровень 1

Задание 1: найти произведение матриц AB, где

Задание 2: найти произведение матриц BA, где

Задание 3: Даны матрицы А, В. Найти

Тест:

Уровень 2

Задание 4. Предприятие выпускает продукцию трех видов: P1, P2, P3 и использует сырье двух типов S1 и S2. Нормы расхода сырья характеризуются матрицей

,

где каждый элемент показывает, сколько единиц сырья j-го типа расходуется на производство единицы продукции. План выпуска продукции задан матрицей-строкой B=(100, 130, 90). Необходимо определить затраты сырья для планового выпуска продукции.
Подсказка: для нахождения затрат сырья необходимо вычислить произведение матриц B*A.
Тест: в результате появятся затраты сырья для планового выпуска продукции B*A=(880,900). Таким образом, для выполнения плана необходимо S1=880 единиц сырья первого типа и S2=900 единиц сырья второго типа.

Задание 5. Предприятие выпускает продукцию трех видов: P1, P2, P3 и использует сырье двух типов S1 и S2. Нормы расхода сырья характеризуются матрицей

,

где каждый элемент показывает, сколько единиц сырья j-го типа расходуется на производство единицы продукции. Стоимость единицы каждого типа сырья задана матрицей-столбцом

Определите стоимость затрат сырья на единицу продукции.

Уровень 3

Задание 6. Какие из матриц можно перемножить? Найдите эти произведения.

Задание 7. Вычислите (A*B)*C, A*(B*C).

Задание 8. Покажите вычислением, что для указанных матриц верно утверждение: (A+B)C=AC+BC.

Составитель: Салий Н.А.

Функции для работы с матрицами в 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 Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

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

Если матрица A имеет размер n × m , то транспонированная матрица A t имеет размер m × n.

В MS EXCEL существует специальная функция ТРАНСП() для нахождения транспонированной матрицы.

Если элементы исходной матрицы 2 х 2 расположены в диапазоне А7:В8 , то для получения транспонированной матрицы нужно:

• выделить диапазон 2 х 2, который не пересекается с исходным диапазоном А7:В8
• в строке формул ввести формулу =ТРАНСП(A7:B8) и нажать комбинацию клавиш CTRL+SHIFT+ENTER , т.е. нужно ввести ее как формулу массива (формулу можно ввести прямо в ячейку, предварительно нажав клавишу F2 )

Если исходная матрица не квадратная, например, 2 строки х 3 столбца, то для получения транспонированной матрицы нужно выделить диапазон из 3 строк и 2 столбцов. В принципе можно выделить и заведомо больший диапазон, в этом случае лишние ячейки будут заполнены ошибкой #Н/Д.

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

Напомним некоторые свойства транспонированных матриц (см. файл примера ).

(A t ) t = A( k · A) t = k · A t (про умножение матриц на число и сложение матриц см. статью Сложение и вычитание матриц, умножение матриц на число в MS EXCEL )(A + B) t = A t + B t (A · B) t = B t · A t (про умножение матриц см. статью Умножение матриц в MS EXCEL )

Транспоны данных из строк в столбцы (или наоборот) в Excel для Mac

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

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

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

Ниже рассказывается, как это сделать.

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

на вкладке Главная или нажмите control+C.

Примечание: Обязательно скопируйте данные. Это не получится сделать с помощью команды Вырезать или клавиш CONTROL+X.

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

Советы по транспонированию данных

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