Курсовая работа (т): Решение задач линейной алгебры в Ms Excel

Решение задач линейной алгебры в Ms Excel

Содержание

Введение

1. Метод Гаусса и одно из его приложений в экономике (задача о рационе)

1.1 Простейшая задача о рационе

.2 Метод Гаусса

.3 Метод Гаусса в Excel

2.   Модель Леонтьева межотраслевого баланса

.     Метод наименьших квадратов (МНК)

3.1 Алгебраический метод наименьших квадратов

3.1.1 Анализ данных эксперимента

3.2 МНК в Excel

Заключение

Список литературы

Введение

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

В первом параграфе работы в качестве примера использования систем линейных уравнений в экономике приведена простейшая задача о рационе и её решение методом Гаусса в частном случае, когда количество неизвестных совпадает с количеством уравнений.

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

(E-A)*X=Y

Если у нас имеется матрица (Е-А)^-1 ,то умножая обе части равенства на эту матрицу, получим: Х=(Е-А)^-1*У.

Третий параграф описывает решение задач, сводящихся к решению систем линейных уравнений, при помощи МНК (метода наименьших квадратов).

В каждом параграфе будет приведена реализация в Excel.

.        Метод Гаусса и одно из его приложений в экономике  (задача о рационе)

1.1 Простейшая задача о рационе

Формулировка задачи. Допустим, на ферме занимаются выращиванием телят. Известно, что для хорошего роста теленка в день ему необходимо потреблять m веществ в количестве  ,…, соответственно.

На ферму ежедневно завозится n кормов в количестве,…,. Известно, что доля итогового вещества в j-ом корме равна . Тогда общее количество вещества определяется по формуле

(слагаемое - количество итогового вещества в j корме; i=1,…,n).

В результате получаем систему

 (1)

Если m ≠n ,то система называется прямоугольной и методы её решения рассматриваются в другом параграфе. В данном случае будем считать, что m=n. Такая система является квадратной и к ней применим метод Гаусса.

1.2 Метод Гаусса

Алгоритм Метода Гаусса состоит из двух основных частей: прямой ход и обратный ход.

Прямой ход заключается в том, что система приводится к треугольному виду (верхняя унитреугольная форма). Обратный ход - непосредственное нахождение неизвестных. Причем, корни находятся в обратном порядке: сначала , затем  и т.д.

)        Прямой ход состоит из следующих шагов.

На первом шаге элементарными преобразованиями исключается из всех уравнений, начиная со второго.

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

На s шаге  исключается из всех уравнений, начиная с s+1 (s=1,…,n-1).

При этом каждый шаг начинается с обработки s уравнения: строка под номером sделится на,чтобы коэффициент при  стал равен 1.

Описанный алгоритм носит циклический характер.

После завершения этого процесса получаем систему:

 (2)

)        Обратный ход.

В результате выполнения алгоритма прямого хода система (1) приняла треугольный вид (2). Для нахождения решения остается из системы (2) найти , , …, . Метод нахождения достаточно очевиден: из последнего уравнения находим .

Затем, подставив найденное значение  в (n-1)-ое уравнение, найдем , и т.д. Таким образом, s-ое неизвестное  находим из s-го уравнения:

.         1.0.

Причем, если условиться считать, что значение суммы, в которой нижний индекс суммирования больше верхнего (пустая сумма), равно нулю, в формуле 1.0. можно считать, что индекс s принимает натуральные значения от n до 1.

1.3 Метод Гаусса в Excel

В Excel Метод Гаусса подробно (по шагам) выполняется только в учебных целях, когда нужно показать, что Вы это умеете. Существует более рациональный способ реализации данного метода в Excel.

Решим задачу о рационе в Excel.

Формулировка:    

Допустим, на ферме занимаются выращиванием телят. Известно, что для хорошего роста теленка в день ему необходимо потреблять 4 вещества в количестве ,, , соответственно.

На ферму ежедневно завозится 4 корма в количестве ,…,. Известно, что доля итогового вещества в j-ом корме равна . Тогда общее количество вещества определяется по формуле

=

(слагаемое - количество итогового вещества в j корме; i=1,…,n).

В результате получаем систему

(1)

Введем исходные данные в Excel:

Отображение в режиме формул:

Где А - матрица коэффициентов,

F- вектор свободных членов,

F’ содержит формулу, вычисляющую левую часть уравнения.

Далее для нахождения корней составленной системы линейных уравнений воспользуемся функцией Поиск решения:

Результат вычислений:

2.      Модель Леонтьева межотраслевого баланса

Макроэкономика функционирования многоотраслевого хозяйства требует баланса между отдельными отраслями. Каждая отрасль, с одной стороны, является производителем, а с другой - потребителем продукции, выпускаемой другими отраслями. Возникает довольно непростая задача расчета связи между отраслями через выпуск и потребление продукции разного вида. Впервые эта проблема была сформулирована в виде математической модели в 1936 г. в трудах известного американского экономиста В.В.Леонтьева, который попытался проанализировать причины экономической депрессии США 1929-1932 гг. Эта модель основана на алгебре матриц.

Суть сводится к следующему.

Основу информационного обеспечения модели межотраслевого баланса составляет технологическая матрица, содержащая коэффициенты прямых материальных затрат на производство единицы продукции. Эта матрица является также основой экономико-математической модели межотраслевого баланса. Предполагается, что производствао единицы продукции в j-й отрасли требует определенное количество затрат промежуточной продукции i-й отрасли, равное а_ij. Оно не зависит от объема производства в отрасли и является довольно стабильной величиной во времени. Величины а_ij называются коэффициентами прямых материальных затрат и рассчитываются следующим образом:

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

Систему уравнений баланса можно переписать в виде

Если ввести в рассмотрение матрицу коэффициентов прямых материальных затрат А= (а_ij), вектор-столбец валовой продукции X и вектор-столбец конечной продукции Y:

,                      ,

то система уравнений в матричной форме примет вид:

Х=АХ + У.

Полученная система уравнений называется экономико-математической моделью межотраслевого баланса (моделью Леонтьева, моделью "затраты-выпуск"). С помощью этой модели можно выполнять три варианта расчетов:

o   Задав в модели величины валовой продукции каждой отрасли (X_i), можно определить объемы конечной продукции каждой отрасли (Y_i):

 = (Е - А)Х (2).

o   Задав величины конечной продукции всех отраслей (У_г), можно определить величины валовой продукции каждой отрасли (Х)

o   Для ряда отраслей задав величины валовой продукции, а для всех остальных отраслей задав объемы конечной продукции, можно найти величины конечной продукции первых отраслей и объемы валовой продукции вторых.

В формулах Е обозначает единичную матрицу n-го порядка, а (Е - А)^-1 обозначает матрицу, обратную к матрице (Е - А). Если определитель матрицы (Е - А) не равен нулю, т.е. эта матрица невырожденная, то обратная к ней матрица существует. Обозначим эту обратную матрицу через В=(Е -А)^-1, тогда систему уравнений в матричной форме (2) можно записать в виде

= ВY.

Элементы матрицы В будем обозначать через b_ij, тогда из матричного уравнения для любой i-й отрасли можно получить следующее соотношение:

Из последних соотношений следует, что валовая продукция выступает как взвешенная сумма величин конечной продукции, причем весами являются коэффициенты b_ij, которые показывают, сколько всего нужно произвести продукции i-й отрасли для выпуска в сферу конечного использования единицы продукции j-й отрасли. В отличие от коэффициентов прямых затрат а_ij коэффициенты b_ij называются коэффициентами полных материальных затрат и включают в себя как прямые, так и косвенные затраты всех порядков. Если прямые затраты отражают количество средств производства, израсходованных непосредственно при изготовлении данного продукта, то косвенные относятся к предшествующим стадиям производства и входят в производство продукта не прямо, а через другие (промежуточные) средства производства.

Пример нахождения вектора валовой продукции

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

Найти вектор валовой продукции.

Решение.

1.      Определим матрицу коэффициентов полных материальных затрат.. Находим матрицу (Е-А)

b.      Вычисляем определитель этой матрицы

c.       Транспонируем матрицу (Е-А)

d.      Находим алгебраические дополнения для элементов матрицы (Е-А)’

Таким образом, присоединенная матрица имеет вид:

e.       Находим матрицу коэффициентов полных материальных затрат:

2.      Найдем величины валовой продукции трех отраслей (вектор X),:

Нахождения вектора валовой продукции в Excel.

Модель Леонтьева межотраслевого баланса в режиме формул:

Результаты расчетов представленной модели:

Искомый вектор валового выпуска отраслей занимает диапазон Е12:Е14.

В процессе решения задачи использовались следующие функции:

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

Синтаксис: МОБР (массив).

Массив - числовой массив с равным количеством строк и столбцов.

После введения функции в левую верхнюю ячейку диапазона массива следует выделить массив, начиная с ячейки, содержащей формулу, нажать клавишу F2, а затем нажать клавиши CTRL+SHIFT+ENTER.

. МУМНОЖ - возвращает произведение матриц (матрицы хранятся в массивах). Результатом является массив с таким же числом строк, как массив1 и с таким же числом столбцов, как массив2.

Синтаксис: МУМНОЖ(массив1;массив2).

Массив1, массив2 - перемножаемые массивы.

После введения функции в левую верхнюю ячейку диапазона массива следует выделить массив, начиная с ячейки, содержащей формулу, нажать клавишу F2, а затем нажать клавиши CTRL+SHIFT+ENTER.

3.      Метод наименьших квадратов (МНК)

Система m линейных уравнений с n неизвестными имеет вид:

Возможны три случая: m<n, m=n, m>n. Случай, когда m=n, рассматривался в предыдущих параграфах. При m<n, если система mлинейных уравнений с nнеизвестными является совместной, то она не определена и имеет бесконечно много решений.

В случае, если m>nи система является совместной, то матрица А имеет по крайней мере m - nлинейно зависимых строк. Здесь решение может быть получено отбором n любых линейно независимых уравнений (если они существуют)и применением формулы Х=А^-1×В, то есть, сведением задачи к ранее решенной. При этом полученное решение всегда будет удовлетворять и остальным m - nуравнениям.