Содержание:
Решим Систему Линейных Алгебраических Уравнений (СЛАУ) методом обратной матрицы в MS EXCEL. В этой статье нет теории, объяснено только как выполнить расчеты, используя MS EXCEL.
Решим систему из 3-х линейных алгебраических уравнений с помощью обратной матрицы (матричным методом).
Запишем в ячейки основную матрицу системы и столбец свободных членов.
Систему n линейных алгебраических уравнений с n неизвестными можно решать матричным методом только тогда, когда определитель основной матрицы системы отличен от нуля (в противном случае мы имеем линейно зависимые уравнения и соответственно решение систем не единственное). В нашем случае определитель =12.
Для этого выделите ячейки A18:C20, а в Строке формул введите =МОБР(A11:C13) , затем нажмите CTRL+SHIFT+ENTER.
Решение системы уравнений получим умножением обратной матрицы и столбца свободных членов. Перемножить матрицы можно с помощью формулы массива =МУМНОЖ() .
Для этого выделите ячейки F18:F20, а в Строке формул введите =МУМНОЖ(A18:C20;F11:F13) , затем нажмите CTRL+SHIFT+ENTER.
В файле примера также приведено решение системы 4-х и 5-и уравнений.
КВАДРАТИЧНЫЕ ФОРМЫ
Целью лабораторной работы “ Квадратичные формы ” является освоение матричных функций Excel и VBA.
Выполнение лабораторной работы предполагает знание элементов векторного анализа, в частности, матричных операций.
Лабораторная работа “Квадратичные формы” состоит из двух частей, в первую часть входят:
– решение матричного уравнения в Excel;
– решение матричного уравнения в Excel с предварительным преобразованием;
– вычисление квадратичной формы в Excel;
– вычисление квадратичной формы с использованием матричных функций Excel;
– вычисление квадратичной формы с использованием матричных функций VBA,
– операции над массивами в Excel;
– операции над массивами в VBA.
Критерием правильности решений матричных уравнений являются результаты проверки подстановкой найденных значений корней в исходные уравнения.
Критерием правильности вычислений квадратичной формы является совпадение полученных значений трех способов вычислений.
Выполнение лабораторной работы начинается с внесения исходных данных задания в соответствующие ячейки таблицы Excel,в выбранной адресации:
– значения матрицы А – А8:D11;
– значения вектора B – F8:F11;
– значения вектора Y – H8:H11, рис. 6.1.
Часть первая
Решение матричного уравнения в Excel.
Так как решением матричного уравнения АХ=В является вектор Х=А -1 В, то необходимо сформировать обратную матрицу А -1 , это выполняется в ячейках A14:D17, для чего:
— через мастер функций fx вызвать матричную операцию МОБР, в окне “массив” которой указать адрес исходной матрицы А – А8:D11;
— затем одновременно нажать клавиши Ctrl-Shift-Enter.
В результате в ячейках A14:D17 появятся значения элементов обратной матрицы А -1 , рис. 6.2., а для любой ячейки диапазона A14:D17 в строке состояний подтверждение выполнения матричной операции <=МОБР(A8:D11)>.
Так как результатом произведения исходной матрицы А на обратную матрицу А -1 является единичная матрица E, то для проверки правильности значений элементов полученной обратной матрицы необходимо:
— через мастер функций fx вызвать матричную операцию МУМНОЖ, в окне рис. 6.3 “Массив1” которой указать адрес исходной матрицы А – А8:D11, а в окне “Массив2” которой указать адрес обратной матрицы А -1 – А14:D17;
— затем одновременно нажать клавиши Ctrl-Shift-Enter.
В результате в ячейках F14:I17 появятся значения элементов единичной матрицы E, рис. 6.2., а для любой ячейки диапазона F14:I17 в строке состояний подтверждение выполнения матричной операции
Рис. 6.1.
Рис. 6.2.
Рис. 6.3.
Для получения вектора решений Х=А -1 В необходимо:
— через мастер функций fx вызвать матричную операцию МУМНОЖ, в окне “Массив1” которой указать адрес обратной матрицы А -1 – F14:I17, а в окне “Массив2” которой указать адрес вектора свободных членов B – F8:F11;
— затем одновременно нажать клавиши Ctrl-Shift-Enter.
В результате в ячейках J8:J11 появятся значения элементов вектора решений X , рис. 6.2., а для любой ячейки диапазона J8:J11 в строке состояний подтверждение выполнения матричной операции <=МУМНОЖ(F14:I17; F8:F11)>.
Для проверки истинности значений элементов полученного вектора решений X необходимо подставить полученные значения в исходное уравнение АХ=В, для чего следует:
— через мастер функций fx вызвать матричную операцию МУМНОЖ, в окне “Массив1” которой указать адрес исходной матрицы А – А8:D11, а в окне “Массив2” которой указать адрес полученного вектора решений X – J8:J11;
— затем одновременно нажать клавиши Ctrl-Shift-Enter.
В результате в ячейках K8:K11 появятся значения элементов вектора свободных членов B, рис. 6.2., совпадающие со значениями элементов исходного вектора B –F8:F11, а для любой ячейки диапазона K8:K11 в строке состояний подтверждение выполнения матричной операции <=МУМНОЖ(A8:D11; J8:J11)>.
Программа 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 — элемент а11 матрицы А.
Примечание: адрес ячейки H4 вводим как абсолютную ссылку, чтобы при копировании формулы ссылка не менялась.
С помощью маркера автозаполнения копируем формулу ячейки К3 вниз и вправо на весь диапазон матрицы В.
Таким образом, мы умножили матрицу А в Excel и получим матрицу В.
Для деления матрицы А на число k в ячейку K3 введем формулу =B3/$H$4 и скопируем её на весь диапазон матрицы В.
Способ 2
Этот способ отличается тем, что результат умножения/деления матрицы на число сам является массивом. В этом случае нельзя удалить элемент массива.
Для деления матрицы на число этим способом выделяем диапазон, в котором будет вычислен результат, вводим знак «=», выделяем диапазон, содержащий исходную матрицу А, нажимаем на клавиатуре знак умножить (*) и выделяем ячейку с числом k. После ввода формулы нажимаем сочетание клавиш Ctrl+Shift+Enter, чтобы значениями заполнился весь диапазон.
Для выполнения деления в данном примере в диапазон вводим формулу =B3:E5/H4, т.е. знак «*» меняем на «/».
Сложение и вычитание матриц в Excel
Способ 1
Следует отметить, что складывать и вычитать можно матрицы одинаковой размерности (одинаковое количество строк и столбцов у каждой из матриц). Причем каждый элемент результирующей матрицы С будет равен сумме соответствующих элементов матриц А и В, т.е. сij = аij + bij.
Рассмотрим матрицы А и В размерностью 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, то программа выдаст предупреждение: Нельзя изменять часть массива.
Нажмите для увеличения
Мы можем удалить только все элементы этой матрицы.
Видеоурок
Кратко об авторе:
Шамарина Татьяна Николаевна — учитель физики, информатики и ИКТ, МКОУ "СОШ", с. Саволенка Юхновского района Калужской области. Автор и преподаватель дистанционных курсов по основам компьютерной грамотности, офисным программам. Автор статей, видеоуроков и разработок.
Спасибо за Вашу оценку. Если хотите, чтобы Ваше имя
стало известно автору, войдите на сайт как пользователь
и нажмите Спасибо еще раз. Ваше имя появится на этой стрнице.
Есть мнение?
Оставьте комментарий
Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями
Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст