Задачи для самостоятельного решения

Федеральное государственное образовательное бюджетное учреждениевысшего образования

«ФИНАН

«ФИНАНСОВЫЙ УНИВЕРСИТЕТ ПРИ ПРАВИТЕЛЬСТВЕ

РОССИЙСКОЙ ФЕДЕРАЦИИ»

Департамент анализа данных, принятия решений и финансовых технологий

А.В. Потемкин

Компьютерный практикум:

Матричный анализ в Excel

Учебно-методические рекомендации для проведения

компьютерного практикума для студентов заочной формы обучения

Для бакалавров направлений

38.03.01 «Экономика» и 38.03.02 Менеджмент

2018 г.

Содержание

Введение в матричный анализ в Excel………………………………3

Основныеоперации над матрицами…………………………………5

Решение систем линейных уравнений……………………………..16

Экономико-математическая модель межотраслевого баланса……19

Введение в матричный анализ в Excel

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

• МУМНОЖ - умножение матриц,

• ТРАНСП- транспонирование матрицы,

• МОПРЕД - вычисление определителя матрицы,

• МОБР - вычисление обратной матрицы.

Рассмотрим эти функции более подробно.

► МУМНОЖ–функцияумножения матриц.

Синтаксис:

МУМНОЖ(массив 1;массив 2)

Массив 1имассив 2– перемножаемые массивы данных, представляющие собой матрицы. Число столбцов массива 1 должно совпадать с числомстрок массива 2. Оба массива должны содержать только числа, расположенные в смежных ячейках.

Массив 1 и массив 2могут быть заданы как интервалы, массивы чисел, разделенные между собой запятойили ссылки.

Если хотя бы одна ячейка в заданных диапазонах данныхпуста или содержит текст или если число столбцов в аргументе массиве 1 отличается от числа строк вмассиве 2, то функция МУМНОЖ возвращает значение ошибки #ЗНАЧ!.

►ТРАНСП –функциятранспонирования матрицы.

Синтаксис:

ТРАНСП(массив)

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

Формула массива – это формула, выполняющая несколько вычислений над одним или несколькими наборами значений, а затем возвращающая один или несколько результатов. Формулы массива заключены в фигурные скобки { } и вводятся нажатием клавиш CTRL+SHIFT+ВВОД.

Диапазон – этодве или более ячеек листа. Ячейки диапазона могут быть как смежными, так и несмежными.

Функция ТРАНСП используется для изменения ориентации массива или диапазона на листе с вертикальной на горизонтальную и наоборот.

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

Чтобы функция ТРАНСП работала правильно, формулу в этом примере необходимо ввести как формулу массива. Если формула не будет введена как формула массива, то результатом будет единственное значение.

►МОПРЕД– вычисление определителя матрицы,

Синтаксис:

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

Массив– это числовой массив с равным количеством строк истолбцов. Массив может быть задан как интервал ячеек, например, А1:СЗили как массив констант, например, {1;2;3 : 4;5,6 : 7;8;9), иликак имя, называющее интервал или массив.

Если какая-либо ячейка в массиве пуста или содержит текст, тофункция МОПРЕД возвращает значение ошибки #ЗНАЧ!.

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

► МОБР– вычисление обратной матрицы.Возвращает обратную матрицу для матрицы, хранящейся в массиве.

Синтаксис:

МОБР(массив)

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

Если какая-либо из ячеек в массиве пуста или содержит текст, тофункция МОБР возвращает значение ошибки #ЗНАЧ!.

МОБР также возвращает значение ошибки #ЗНАЧ!, если массивимеет неравное число строк и столбцов.

Основные операции над матрицами

Прежде чем приступать к рассмотрению конкретных операций над матрицами проделаем следующую предварительную работу. Запустим приложение MicrosoftExcel и создадим новую рабочую книгу, в которой будем проводить все операции, связанные с матрицами. Желательно, чтобы каждый лист книги Excel имел свое содержательное название, соответствующее выполняемому пункту задания. Для переименования листа достаточно щелкнуть правой кнопкой мыши на соответствующем ярлычке листа и в раскрывшемся контекстном меню выбрать пункт «Переименовать», а далее ввести новое имя листа и нажать клавишу Enter.Переименуем «Лист 1» в «Операции над матрицами».

Далее в ячейки А1:А2вводим информацию о выполняющем работу: Студент – Ф.И.О. и номер группы. В ячейке D4представим тему выполняемого задания – «Операции над матрицами».При необходимости и для наглядности можно поменять размер и цвет шрифта в заполненных ячейках. Далее приступаем к рассмотрению основных операций над матрицами.

Задание 1.Заданы две матрицы:

Найти:

а) сумму матриц

б) разность матриц

в) произведение матрицыА на число 5, то есть и произведение матрицы В на число –10, то есть

г) произведение матриц

д) матрицу А² и А³;

е)транспонированную матрицу

ж) определитель матрицыА;

з) обратную матрицу А^-1.

Решение.Введем исходные данные на лист Excel. для этого разместим элементы матрицыА в ячейках B5:D7, а элементы матрицы Вв ячейках G5:I7как показано на рисунке 9.

а) Найдемматрицу

Определение. Суммой двух матрицА и В одинаковой размерности называется матрица элементы которой равны сумме соответствующих элементов матриц А и В.

Так как у нас суммируются две матрицы одинаковой размерности то в результате также получиться матрица размерности Для получения этой матрицы проделаем следующие действия:

- выделим ячейки B12:D14;

-нажмем «=»;

- выделим элементы матрицыАB5:D7;

-нажмем«+»;

- выделим элементы матрицыВG5:I7;

-одновременно нажимаем клавиши «Ctrl+Shift+Enter»[1].

Рис.9. Исходные данные к заданию 1.

Замечание 1.Если последнее действие было выполнено неверно (например, нажали только «Enter»), то компьютер выдаст ошибку: в ячейке B12 будет информация типа «#ЗНАЧ!», а остальные ячейки будут пустыми. Исправить эту ошибку можно двумя способами: выделивдиапазон B12:D14, нажимаем клавишу F2, а затем «Ctrl+Shift+Enter», либо повторяем ввод заново.

Замечание 2.При попытке очистить одну из ячеек, занятую созданной табличной формулой (выделив, например, ячейку В12, и нажав затем клавишу Del), появится сообщение: «Нельзя изменять часть массива». Удалить блок можно только целиком. Отредактировать введенную формулу можно следующим образом:

1) выделить блок с формулой;

2) нажать функциональную клавишу <F2>;

3) внести изменения в формулу;

4) нажать сочетание клавиш <Ctrl+Shift+Enter>.

б) Найдемматрицу

Определение. Разностью двух матрицА и В одинаковой размерности называется матрица элементы которой равны разности соответствующих элементов матриц А и В.

Разность двух матриц найдем аналогично нахождению их суммы. Для этого проделаем следующие действия:

- выделим ячейки G12:I14;

-нажмем «=»;

- выделим элементы матрицыАB5:D7;

-нажмем«-»;

- выделим элементы матрицыВG5:I7;

-одновременно нажимаем клавиши «Ctrl+Shift+Enter».

в) Найдем матрицы и

Определение. Произведением матрицыА на число a называется матрица элементы которой равны произведениюсоответствующих элементов матриц Ана число a.

Для получения матриц и проделаем следующие действия:

- выделим ячейки B18:D20;

- нажмем «=»;

- введем число«5» и знак умножения «*»;

- выделим элементы матрицыА в ячейкахB5:D7;

-одновременно нажимаем клавиши «Ctrl+Shift+Enter».

Аналогично, выделив ячейки B18:D20,нажмем «=»,введем число«-10» и «*», выделяем элементы матрицы ВG5:I7и одновременно нажимаем клавиши «Ctrl+Shift+Enter».

Результаты расчетов представлены на Рисунке 10.

Рис.10. Сложение и вычитание матриц. Умножение матриц на число.

г) Найдем матрицу

Определение.Произведением матрицыА порядка на матрицу В порядка называется матрица порядка элементы которой равны сумме произведений соответствующих элементов i-ой строки матрица А на элементы j-ого столбца матрицы В.

Очевидно, что произведение матриц существует не всегда. Из определения вытекает, что число столбцовматрицыА должно совпадать с числом строк матрицы В. В общем случае произведение двух матриц не коммутативно. Убедимся в этом на нашем примере.

Будем находить произведение двух матриц с использованием встроенной функции МУМНОЖ.

Так как матрицаА и В имеют одинаковую размерность то и их произведение и будут иметь размерность Результаты вычисленийбудем получать в ячейках B24:D26иG24:I26,соответственно.

Для получения этих матриц проделаем следующиедействия.Сначаланайдем АВ. Для этого выделим ячейки B24:D26, а в строке формул щелкнем мышью по значку , в результате чего появится диалоговое окно (Рис.11).

Рис.11. Диалоговое окно выбора встроенной функции

В категории «Полный алфавитный перечень» либо в категории «Математические»выбираем функцию МУМНОЖ и нажимаем «ОК». В результате раскрывается второе диалоговое окно (Рис.12).

Рис.12. Диалоговое окно, позволяющее ввести аргументы

Функции МУМНОЖ

- в качестве массива 1 вводим элементы матрицыА, выделяя ячейкиB5:D7;

- в качестве массива 2 вводим элементы матрицыВ,выделяя ячейкиG5:I7;

- одновременно нажимаем клавиши «Ctrl+Shift+Enter».

Аналогично, выделяя ячейки G24:I26и проделывая те же самые действия, получим произведение ВА. Рис.13.

Как нетрудно заметить, что получены разные результаты для АВ и ВА, что еще раз подтверждает слова о том, что произведение матриц не коммутативно, т.е. не перестановочно.

Замечание 3. Если хотя бы одна ячейка в аргументах пуста или содержит текст или если число столбцов в аргументе массив 1 отличается от числа строк в аргументе массив 2, то функция МУМНОЖ возвращает значение ошибки #ЗНАЧ!

Рис.13. Умножение матриц с использованиемфункции МУМНОЖ. Возведениематрицы в натуральную степень

д) Найдем матрицы и

Возведение квадратной матрицы в степень – частный случай умножения матриц.

Для того, чтобы найти квадрат матрицыА, достаточно ее перемножить саму с собой. Для этого выделим ячейки B31:D33и воспользуемся формулой МУМНОЖ. Встроке формул щелкнем мышью по значку , в результате чего появится диалоговое окно (Рис.11).Выбрав функцию МУМНОЖ, приходим к следующему диалоговому окну, в котором вкачествепервого и второго массивоввыбереммассив сэлементами матрицыА, расположенный в ячейках B5:D7. Нажимаем «Ctrl+Shift+Enter».

Для нахождения третьей степениматрицыА, достаточно ее перемножить саму сполученнымее квадратом в любой последовательности. Для этого выделим ячейки G31:I33иопять воспользуемся формулой МУМНОЖ.Встроке формул щелкнем мышью по значку , в результате чего появится диалоговое окно (Рис.11). Выбрав функцию МНОЖ, далее вкачествепервого выбереммассив сэлементами матрицыА, расположенный в ячейках B5:D7, а второймассив – массив в ячейках B31:D33. Нажимаем «Ctrl+Shift+Enter». Результаты расчетов представлены на Рис. 13.

е)Найдем матрицу

Определение.Транспонирование матрицы – операция, когда строки и столбцы меняются местами с сохранением порядка следования элементов, то есть i-ая строка матрицыАстановитсяi-ым столбцом матрицы А^Ти,наоборот – j-ый столбец матрицы Астановитсяj-ойстрокой.

Так как матрицаА у нас квадратная третьего порядка, то транспонированная матрица тоже будет квадратной третьего порядка. Для еенахождения транспонированной матрицы необходимовыделить, например, ячейки B37:D39и воспользуемся формулой ТРАНСП.Далее встроке формул щелкнем мышью по значку , в результате чего появится диалоговое окно, в котором выбираем функциюТРАНСПи нажимаем «Enter». В появившемсядиалоговом окне вводимадрес массива сэлементами матрицыА,расположенный в ячейках B5:D7. Нажимаем «Ctrl+Shift+Enter». Результат расчета представлен на Рис.14.

Если в качестве исходной матрицы взять матрицу ,то результатомее транспонированиябудет матрица . Получим ее.Элементы матрицыС введем в ячейки G37:H39. Выделяя ячейки К37:М38,щелкнем мышью по значку , в результате чего появится диалоговое окно, в котором в котором выбираем функциюТРАНСПи нажимаем «Enter».Далеенадо ввести адрес массива сэлементами матрицыС,расположенный в ячейках G37:H39. Нажимаем «Ctrl+Shift+Enter».Результат расчета представлен на Рис.14.

Рис.14. Транспонирование матрицсиспользованиемфункции ТРАНСП и нахождение определителя матриц с помощью функции МОПР

ж)Найдем определители матрицА и В.

Определение.Определителем квадратной матрицы называется число, определяемое по некоторому правилу. Для определения определителя матрицы воспользуемся формулой МОПР.

Найдем определители матрицА и В. Выделим ячейку В44,щелкнем мышью по значку ,в результате чего появится диалоговое окно выбора функции,в котором выберем функцию МОПР,а затемв появившемся диалоговом окне введем адрес массива сэлементами матрицы А,расположенный в ячейках В5:D7. Нажимаем «Enter». Аналогично вычисляем определитель матрицыВв ячейке D44. Результат расчета представлен на Рис.14.

з)Найдем матрицу A^-1.

Определение.Матрица A^-1 называется обратной по отношению к квадратной матрицеА, если при умножении этой матрицы на данную как слева, так и справа получается единичная матрица, то есть

A*A^-1= A^-1*A = E.

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

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

В MS Excel для нахождения обратной матрицы используется функция МОБР.

Вычислим обратную матрицу для матрицы А. Перед эти мы убедились, что ее определитель не равен нулю и, следовательно,она является невырожденнойобратная матрицасуществует.

Выделите блок ячеек под обратную матрицу. Размерность этого блока должна быть такой же, как и у матрицы А.Например, выделим ячейки В48:D50. На панели инструментов Стандартная выбираемкнопку вставить функцию . В появившемся диалоговом окне Вставка функции в рабочем поле Категория выберите Математические, а в рабочем полеВыберитефункцию — имя функции МОБР. После этого щелкните на кнопке ОК.

В появившемся диалоговом окне МОПРЕД вводим элементыисходной матрицыА,расположенные в ячейках В5:D7 и нажимаемодновременно сочетание клавиш «CTRL+SHIFT+ENTER».

Аналогично получаем матрицу, обратную матрицеВ (Рис.15).

Рис.15. Вычисление обратной матрицысиспользованием встроеннойфункции МОБР

Решение систем линейных уравнений

Задание 2.Решить систему линейных уравнений

а) методом Крамера;

б) методом обратной матрицы.

Решение.а)Откроем новый лист рабочей книги и переименуем его в «Системы линейных уравнений». В ячейки B6:D8 введем коэффициенты матрицы системы А, а в ячейкиG6:G8столбец свободных членовВ(Рис.16).Убедимся, что исходная система определена, то есть имеет единственное решение. Для этого вычислим определитель матрицы А. Используя функциюМОПР, находим, что определитель матрицы А равен -2, то есть не равен нулю (Рис.16).

Рис.16. Решение систем линейных уравнений

Далее получим матрицы А₁, А₂ и А₃, в которых соответственно первый, второй и третий столбцы матрицыА заменяются столбцом свободных членов. Для этого копируем элементы матрицыА(диапазон ячеек B6:D8)соответственнов ячейкиB12:D14,B16:D18и B20:D22. Далее копируем столбец свободных членов В, расположенный в ячейках G6:G8,в ячейки B12:В14,С16:С18и D20:D22. В ячейках G13,G17 иG21 вычисляем определители полученных матрицс использованием функции МОПР. Согласно правилу Крамера, искомое решениесистемыx_i(i=1,2,3) найдем как отношениеопределителя матриц А_iк определителю матрицы А.Результаты будем получать в ячейках J13,J17 иJ21. Для этого в ячейку введем формулу «=G13/$G$10», а затем скопируем ее в ячейки J17 иJ21. Результаты расчетовпредставлены на Рис. 17.

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

б) Решим ту же самую систему методом обратной матрицы. Ранее мы убедились в том, что определитель матрицы системы отличен от нуля. Следовательно, обратная матрица существует.Для ее определенияв ячейках B26:D28воспользуемся алгоритмом вычисленияобратнойматрицы А^-1,рассмотренным выше.Затем умножим полученную матрицу А^-1 настолбец свободных членовВ, расположенный в ячейках G6:G8. Выделяя ячейки G26:G28, и применяя функцию матричного умножения МУМН, получим результат, совпадающий сполученным ранее по правилу Крамера (Рис.18).Еслирезультаты вычислений отличаются, то необходимо выяснить, где произошла ошибка и повторить проделанные вычисления.

Рис. 18.Решение системы линейных уравнений методом обратной матрицы

Экономико-математическая модель межотраслевого баланса

(модель «затраты-выпуск»)

Задание 3.Предприятие производит продукцию четырех видов (Р₁, Р₂, Р₃, Р₄). При этом используется сырье трех типов (S₁, S₂, S₃). Нормырасходасырья на единицу продукции каждого вида, себестоимость одной условной единицыкаждого вида сырья и стоимость его доставки приведены в таблице:

Показатель	Норма расходасырья на единицу продукции, у.е.
Показатель	S₁	S₂	S₃
Вид продукции
Р₁	4	5	2
Р₂	3	1	2
Р₃ Р₄	1 2	3 2	5 4
Себестоимость единицы сырья, ден. ед.	2	5	3
Стоимость доставки сырья, ден. ед.	3	5	4

Дневной план выпуска продукциисоставляет 100 единицпродукции Р₁, 75 единицпродукции Р₂,50 единиц продукции Р₃и 40 единиц продукции Р₄. Определить общиезатратыпредприятияза день работы.

Решение. Составим экономико-математическую модель данной задачи.

Нормы расхода сырья на единицу продукции представим в виде матрицы А, адневной план выпуска продукциив виде вектора столбца Р. Очевидно, что

Произведение матрицыА на столбец Р представляет собой столбец S, элементы которого численно равны количеству соответствующего сырья, необходимого дляосуществленияуказанногодневного плана выпуска продукции, то есть .

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

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

Реализоватьвсе вычисления в MSExcel.

Ответ. 17145 ден.ед..

Задание 4.Предприятие производит продукцию трех видов (Р₁, Р₂, Р₃). При этом используется сырье трех типов (S₁, S₂, S₃). Нормырасходасырья на единицу продукции каждого видаиобъем расходасырья за один день приведены в таблице:

Показатель	Норма расходасырья на единицу продукции, у.е.			Расход сырья за 1 день,у.е.
Показатель	Р₁	Р₂	Р₃	Расход сырья за 1 день,у.е.

S₁	4	5	2	3400
S₂	3	1	2	1600
S₃	1	3	5	2900

Найти ежедневный объем выпуска каждого вида продукции.

Решение. Составим экономико-математическую модель данной задачи.Введем следующие неизвестные: х₁ – количество единиц продукции первоговида, х₂ – количество единиц продукции второговида и х₃ – количество единиц продукции третьеговида выпускаемого предприятием.

Рассмотрим расход сырья первого типа. Согласно исходным данным, на одну единицу продукции первого видарасходуется 4условных единиц сырья первого типа. Следовательно, на производство х₁ единиц продукции первого видабудет расходоваться 4х₁ единиц сырья первого типа. Аналогичные рассуждения справедливы и для продукции второго и третьего вида. Тем самым, зная дневной расход сырья первого типа (3400 условных единиц), можно составить уравнение вида:

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

Другими словами говоря, наша задача свелась к решению системылинейных уравнений.

Ответ Х=(200,400,300).

Задачи для самостоятельного решения

5.Предприятие производит продукцию трех видов. При этом используется сырье трех типов. Нормы затрат сырья на единицу продукции каждого вида, себестоимость каждого вида сырья и стоимость его доставки приведены в таблице:

Показатель	Норма расходасырья на единицу продукции, у.е.
Показатель	Р₁	Р₂	Р₃
Вид продукции
S₁	5	4	2
S₂	3	1	0
S₃	1	3	7
Себестоимость единицы сырья, усл.ед.	3	4	3
Стоимость доставки сырья, усл.ед.	1	2	2

Каковы общие затраты предприятия на производство 100 у.е. продукции S₁, 75 у.е.продукции S₂ и 50 у.е. продукции S₃?

6.Предприятие производит 3 типа продукции, используя 2 вида ресурсов. Нормы затрат ресурса i-го вида на производство единицы продукции j -го типа заданы матрицей затрат В. Пусть за определённый отрезок времени предприятие выпустило количество продукции каждого типа x_j, записанное матрицей X . Определите: а) S матрицу полных затрат ресурсов трёх видов на производство месячной продукции. б) стоимость всех затраченных ресурсов, если матрицей P задана стоимость единицы каждого ресурса

7.Предприятие производит продукцию трех видов (Р₁, Р₂, Р₃). При этом используется сырье трех типов (S₁, S₂, S₃). Нормырасходасырья на единицу продукции каждого видаиобъем расходасырья за один день приведены в таблице:

Показатель	Норма расходасырья на единицу продукции, у.е.			Расход сырья за 1 день,у.е.
Показатель	Р₁	Р₂	Р₃	Расход сырья за 1 день,у.е.

S₁	5	3	4	6100
S₂	2	7	5	6800
S₃	4	8	7	9400

Найти ежедневный объем выпуска каждого вида продукции.

[1] Для этого достаточно удерживая нажатыми «Ctrl+Shift»,нажать «Enter».

Дата добавления: 2018-05-12; просмотров: 172; Мы поможем в написании вашей работы!

Поделиться с друзьями:

Мы поможем в написании ваших работ!