МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ РОССИЙСКИЙ ЭКОНОМИЧЕСКИЙ УНИВЕРСИТЕТ им. Г.В. ПЛЕХАНОВА

Курсовая работа по дисциплине «Информационные технологии в экономике»

На тему: «Решение задач линейной алгебры в Ms Excel»

Выполнил студент 1 курса

Группы № «414» / дневное отделение

Факультета математической

экономики и информатики

Цопанова Зарина Игоревна

Научный руководитель:

к.э.н., доцент кафедры ИТ

Антоненкова Альбина Викторовна

Москва 2015

Введение

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

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

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

(E-A)*X=Y

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

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

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

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

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

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

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

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

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

(1)

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

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

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

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

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

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

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

На s шаге исключается из всех уравнений, начиная сs+1

(s=1,…,n-1).

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

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

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

(2)

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

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

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

. 1.0.

Причем, если условиться считать, что значение суммы, в которой нижний индекс суммирования больше верхнего (пустая сумма), равно нулю, в формуле 1.0. можно считать, что индекс s принимает натуральные значения от n до 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:

, ,

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

Х=АХ + У.

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

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

Y = (Е - А)Х (2).

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

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

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

X= ВY.

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

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

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

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

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

Решение.

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

   1. Находим матрицу (Е-А)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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