Использование матричных функций MS Excel

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ

ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ

ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

Кафедра информатики

КУРСОВАЯ РАБОТА

по дисциплине: информатика

на тему: «ИСПОЛЬЗОВАНИЕ МАТРИЧНЫХ ФУНКЦИЙ MS Excel»

ВВЕДЕНИЕ

Для моделирования и решения экономико-математических задач необходим определенный объем информации. Это информация о ресурсах и их наличии, процессах производства, распределения, обмена и потребления продукции. Разнообразие форм воплощения экономической информации в совокупности называют экономическими данными (планы, отчеты, наряды, сведения и др.). Экономическая информация подразделяется на первичную и вторичную. Носителями первичной информации служат технологические, нормативные и другие документы. Носителями вторичной информации являются результаты обработки первичных документов. Используется как первичная, так и вторичная информация, хотя вторичная информация зачастую применяется чаще.

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

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

В курсовой работе мне необходимо провести анализ взаимосвязей между тремя смежными отраслями текстильной промышленности. Для расчетов и получения результата я использую экономико-математическую модель межотраслевого баланса (МОБ).

Межотраслевой баланс в экономике - это метод анализа взаимосвязей между различными секторами экономической системы.

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

Итак, МОБ относят к балансовым моделям. Под балансовой моделью понимается система уравнений, каждое из которых выражает требование баланса между произведённым отдельными экономическими объектами количеством продукции и совокупной потребностью в этой продукции. В данном случае рассматривается система экономических объектов, которые выпускают некоторый продукт, часть его потребляется другими объектами системы, а другая часть выводиться за пределы системы в качестве её конечного продукта.

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

МЕЖОТРАСЛЕВОЙ БАЛАНС (МЕЖОТРАСЛЕВОЙ БАЛАНС ПРОИЗВОДСТВА И РАСПРЕДЕЛЕНИЯ ОБЩЕСТВЕННОГО ПРОДУКТА) -экономико-математическая модель, характеризующая систему связей между выпуском продукции в одной отрасли и затратами всех других отраслей, участвующих в выпуске данной продукции. Первые межотраслевые балансы были составлены в СССР при построении баланса народного хозяйства на 1923- 1924гг. В нем имелись показатели и таблицы, характеризующие межотраслевые связи в народном хозяйстве. За рубежом межотраслевой баланс был применен в 30-е гг. XX в. американским экономистом В. Леонтьевым для изучения структуры американской экономики. Применяемый им метод получил название «затраты - выпуск» (input - output).

Суть межотраслевого баланса состоит в построении таблицы, в которой по вертикали показываются материальные затраты на производство продукции отдельной отрасли, а также прибыль. Данные по горизонтали показывают, на какую сумму (или какое количество продукции) передано продукта в другие отрасли народного хозяйства на производственные нужды (промежуточный продукт), а также конечное потребление продукции отрасли, на накопление, возмещение, выбытия и капитальный ремонт и также экспортно-импортное сальдо. Межотраслевой баланс детально отражает производственные и хозяйственные связи отраслей. Составляется в денежной и натуральной форме. Главными показателями межотраслевого баланса являются: коэффициенты полных затрат, характеризующие затраты какого-либо продукта на производство единицы другого продукта по всей цепочке взаимосвязанных отраслей; коэффициенты прямых затрат (средняя величина затрат по отрасли в целом).

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

Леонтьев, по всеобщему признанию, один из самых выдающихся ученых-экономистов 20-го столетия. Международная Энциклопедия общественных наук сравнивает его вклад с той ролью, какую в теории экономики сыграли Адам Смит и Джон Мейнард Кейнс, а этих гигантов можно, пожалуй, назвать соответственно Ньютоном и Эйнштейном этой науки.

Первую статью о методе «затраты-выпуск» В. Леонтьев издал только в 1936 г. («Количественные соотношения «затраты-выпуск» в экономической системе Соединенных Штатов»); главной частью статьи был анализ балансовой таблицы за 1919 г.

Далее темп исследований и их обобщений заметно ускорился. Вместе с группой сотрудников В. Леонтьев завершил работу над балансом США за 1929 г. и в 1941 г. выпустил книгу «Структура американской экономики, 1919 - 1929», признанную впоследствии классической.

В 1948 г. В. Леонтьев основал Гарвардскую лабораторию экономических исследований, которая стала научным центром по дальнейшей разработке и практическому применению метода «затраты-выпуск». В. Леонтьев оставался директором лаборатории вплоть до ее закрытия в 1973 г.

Когда с 1969 г. началось присуждение Нобелевских премий по экономике, Леонтьев закономерно оказался одним из первых кандидатов. Он стал лауреатом в 1973 г. с такой формулировкой научных заслуг: за развитие метода затраты-выпуск и за его применение к важным экономическим проблемам.

В. Леонтьев в числе первых экономистов, использовал новинки вычислительной техники. Со смехом он вспоминает работу на механической вычислительной машине (кажется, уже во время войны). Она напоминала большой пресс и, производя вычисления, вибрировала, как старый трактор. Вокруг все было залито маслом, от которого надо было защищать себя (и машинные результаты!) спецодеждой. В 1980 г. корпорация «Контрол Дэйта» предоставила В. Леонтьеву свой новейший суперкомпьютер для выполнения детальных межотраслевых прогнозов.

В своей курсовой работе я использую наиболее простой вариант МОБ, основанный на модели Леонтьева, или модели «затраты-выпуск».

1.КРАТКИЕ ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

.1Функции MS Excel, необходимые для решения задачи

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

Основное назначение Excel - выполнение расчетов с данными. Обработка данных происходит в ячейках содержащих формулы.

Формулой в EXCEL называется последовательность символов, начинающихся со знака равенства «=». В ту последовательность символов могут входить постоянные ссылки на ячейки, имена, функции и операторы.

Правила ввода формул

Ввод любой формулы всегда нужно начинать со знака равенства «=». В формуле могут находиться:

oзнаки арифметических действий: «+», «-», «*», «/», «^» (знак возведения числа в степень), знак «%»;

oчисла, строки (они берутся в кавычки);

oссылки на ячейки и диапазоны ячеек (как на текущем листе, так и на других листах книги) для определения порядка вычислений, скобки;

oвстроенные функции.

В Excel имеется большое количество функций, с помощью которых можно проводить вычисления и другие действия, относящиеся к самым разнообразным областям знаний. При использовании встроенной функции после знака «=» следует ввести ее имя, а затем в скобках аргументы функции - данные, которые используются в расчетах. Аргументами функции могут быть числа, ссылки на ячейки или диапазоны ячеек, а также другие встроенные функции (они называются вложенными).

Многие задачи технико-экономического характера сводятся к решению систем линейных уравнений. MS Excel располагает рядом возможностей для работы с системой n линейных алгебраических уравнений (СЛАУ) с n неизвестными.

Табличные формулы или формулы массива - очень мощное вычислительное средство Excel, позволяющее работать с блоками рабочего листа как с отдельными ячейками. Табличные формулы в качестве результата возвращают массив значений. Поэтому перед вводом такой формулы необходимо выделить диапазон ячеек, куда будут помещены результаты. Потом набирается сама формула. Ввод ее в выделенный диапазон ячеек осуществляется нажатием комбинации клавиш Ctrl+Shift+Enter. Это принципиально. Формула вводится во все ячейки выделенного интервала. При активизации любой ячейки из интервала, содержащего формулу массива, в строке формул отображается введенная формула, заключенная в фигурные скобки. Именно фигурные скобки являются признаком табличной формулы. Для выделения всего блока, содержащего табличную формулу, необходимо выделить одну из его ячеек, после чего нажать комбинацию клавиш Ctrl+/. Невозможно редактировать содержимое только одной ячейки из интервала с табличной формулой. Изменить можно только весь блок целиком, для чего он и должен быть предварительно выделен.

При выполнении задания использовались простейшие операции с матрицами:

·вычитание матриц;

·умножение матрицы на число;

·перемножение матриц;

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

Умножение (деление) матрицы на число, сложение (вычитание) матриц в Excel реализуются достаточно просто: с помощью обычных формул (поэлементное сложение или вычитание, умножение или деление на число), либо с использованием табличных формул, как это было описано выше.

Использовались функции рабочего листа из категорий «Математические» и «Ссылки и массивы»:

·СУММ(А1:А5) - суммирование значений из диапазона ячеек А1:А5. Это пример использования встроенной функции. Здесь СУММ - имя функции, А1:А5 - диапазон ячеек, ее единственный аргумент, заключенный в скобки;

·МОБР(матрица) - вычисление обратной матрицы (обратные матрицы, как и определители, обычно используются для решения систем уравнений с несколькими неизвестными. Произведение матрицы на ее обратную - это единичная матрица, т. е. квадратный массив, у которого диагональные элементы равны 1, а все остальные - 0);

·МУМНОЖ(B1:B2;B7:C7) - вычисление произведения матриц B1:B2 и B7:C7. Как видно, данная функция имеет два аргумента, которые являются массивами данных из выделенных диапазонов.

Если функция имеет несколько аргументов, они отделяются друг от друга точкой с запятой. В качестве аргументов функций можно использовать ссылки на ячейки и диапазоны на текущем листе и на других листах. В последнем случае перед адресом ячейки или диапазона следует ввести название листа, отделенное штрихами, и поставить разделитель «!», например Лист1!В2, Лист 3!А1:С4. Штрих можно ввести, нажав клавишу Э при активной английской раскладке.

Эти функции возвращают блок ячеек, поэтому должны вводиться как табличные формулы (Ctrl+Shift+Enter).

1.2Межотраслевой баланс

Алгебраическая теория анализа «затраты-выпуск» сводится к системе линейных уравнений, в которых параметрами являются коэффициенты затрат на производство продукции.

Пусть весь производственный сектор народного хозяйства разбит на n чистых отраслей. Чистая отрасль (это условное понятие) - некоторая часть народного хозяйства, более или менее цельная (например, текстильная, машиностроение, сельское хозяйство и т.п.).

Пусть xij - количество продукции i-й отрасли, расходуемое в j-й отрасли; Xi - объем производства i-й отрасли за данный промежуток времени, так называемый валовой выпуск продукции i; yi - объем потребления продукции i-й отрасли в непроизводственной сфере, объем конечного потребления; Zj - условно чистая продукция, которая включает оплату труда, чистый доход и амортизацию.

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

В таблице 1 отражена принципиальная схема межотраслевого баланса в стоимостном выражении.

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

j = 1, 2, …, n.(1)

Напомним, что величина условно чистой продукции Zj равна сумме амортизации, оплаты труда и чистого дохода j-й отрасли. Соотношение (1) охватывает систему из n уравнений, отражающих стоимостной состав продукции всех отраслей материальной сферы.

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

i = 1, 2, …, n.(2)

Формулы (2) описывает систему из n уравнений, которые называются уравнениями распределения продукции отраслей материального производства по направлениям использования.

Таблица 1

Таблица межотраслевого баланса

Производящие отраслиПотребляющие отраслиКонечный продукт, yВаловой продукт, x12…n1X11X12…X1ny1X12X21X22…X2ny2X2…………………NXn1Xn2…XnnynXnУсловно чистая продукция, ZZ1Z2…ZnВаловой продукт, XX1X2…Xn

Балансовый характер таблицы выражается в том, что

Основу экономико-математической модели МОБ составляет матрица коэффициентов прямых затрат A = (aij).

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

аij = xij / Xj , i, j = 1, 2, …, n. (3)

Для дальнейшего рассмотрения модели Леонтьева сделаем два важных предположения.

Первое состоит в том, что сложившуюся технологию производства считаем неизвестной. Таким образом, матрица A = (aij) постоянна.

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

xij = aij * Xj .(4)

Подставляя (4) в балансовое соотношение (2), получаем:

(5)

или в матричной форме:

X = AX + Y(6)

С помощью этой модели можно выполнять три вида плановых расчетов.

• Задав в модели величины валовой продукции каждой отрасли (Xi), можно определить объемы конечной продукции каждой отрасли (Yi):
• Y = (E - A)*X(7)
• Задав величины конечной продукции всех отраслей (Yi), можно определить величины валовой продукции каждой отрасли (Xi):
• X = (E - A)-1*Y(8)
• Для ряда отраслей задав величины валовой продукции, а для всех остальных - объемы конечной продукции, можно найти величины конечной продукции первых отраслей и объемы валовой продукции вторых.

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

тогда систему уравнений в матричной форме (8) можно записать в виде:

X = B * Y

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

Плановые расчеты по модели Леонтьева можно выполнять, если выполняется условие продуктивности.

Будем называть неотрицательную матрицу А продуктивной, если существует такой неотрицательный вектор Х ? 0, что:

Х > A ? X .(9)

Очевидно, что условие (9) означает существование положительного вектора конечной продукции Y > 0 для модели межотраслевого баланса.

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

1. Матрица (Е - А) неотрицательно обратима, т.е. существует обратная матрица (Е - А)-1 ? 0;

Матричный ряд Е + А + А2 + А3 + … = сходится, причем его сумма равна обратной матрице (Е - А)-1;

1. Все главные миноры матрицы (Е - А), т.е. определители матриц, образованные элементами первых строк и первых столбцов этой матрицы порядка от 1 до n, положительны.

Более простым, но только достаточным признаком продуктивности матрицы А является ограничение на величину ее нормы, т.е. на величину наибольшей из сумм элементов матрицы А в каждом столбце. Если норма матрицы А строго меньше единицы, то эта матрица продуктивна; повторим, что данное условие является только достаточным, и матрица А может оказаться продуктивной и в случае, когда ее норма больше единицы.

2. ЗАДАНИЕ ДЛЯ ВЫПОЛНЕНИЯ КУРСОВОЙ РАБОТЫ

Для модели Леонтьева межотраслевого баланса ( 3 отрасли ) заданы: матрица прямых затрат А и вектор конечного продукта Y.

Требуется определить:

1. матрицу полных затрат (Е - А)-1 ;
2. вектор валового продукта Х ;
3. межотраслевые поставки продукции;
4. проверить продуктивность матрицы А ;
5. проверить выполнение балансового матричного уравнения для найденного вектора Х.

Расчеты вести с точностью до 0,0001 .

А = a * , где а = 0,1 - 0,0005 * N ,

Y = b * , где b = 1 + 0.005 * N ,

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

межотраслевой баланс матричный еxcel

3. РЕЗУЛЬТАТЫ ВЫПОЛНЕНИЯ

Предварительные расчеты:

если номер зачётки:

N = 51, тогда:

а = 0,1 - 0,0005 * Nа = 0,0745

b = 1 + 0.005 * Nb = 1,2550

Матрица прямых затрат А принимает вид:

0,07450,14900,0000A = 0,14900,07450,00000,52150,44700,6705

а вектор конечного продукта Y :

125,5000Y =251,0000376,5000

Использую единичную матрицу 3-го порядкаЕ:

100Е =010001

Для дальнейшего решения задачи использую функции MS Excel
(см. п.п. 1.2 раздела 1). Таблица 1

Исходные данные и результаты по этапам решения

ABCDEFG1 20,07450,14900,0000 3A0,14900,07450,0000 40,52150,44700,6705 5 60,9255-0,14900,0000 7E-A-0,14900,92550,0000 8 -0,5215-0,44700,3295 91) 101,10920,17860,0000125,500011B0,17861,10920,0000Y251,0000121,99791,78743,0349376,500013 142) 15184,0347 16X300,8333 171842,0230 18 193) 2013,710644,82420,0000 21X(ij)27,421222,41210,0000 2295,9741134,47251235,0764 23

В таблице 1 приведены результаты решения задачи по указанным трем пунктам.

. В ячейки В6:D8 запишем элементы матрицы Е - А. Массив Е - А задан как диапазон ячеек. Выделим диапазон B10:D12 для размещения обратной матрицы В = (Е - А)-1 и введем формулу для вычислений МОБР(B6:D8). Затем следует нажать клавиши CTRL+SHIFT+ENTER. Все элементы матрицы коэффициентов полных затрат В неотрицательны, следовательно, матрица А продуктивна (ответ на п.1 и 4 задания).

. В ячейки G10:G12 запишем элементы вектора конечного продукта Y. Выделим диапазон В15:В17 для размещения вектора валового выпуска Х, вычисляемого по формуле Х = (Е - А)-1 * Y. Затем вводим формулу для вычислений МУМНОЖ(B10:D12,G10:G12). Затем следует нажать клавиши CTRL+SHIFT+ENTER.

. Межотраслевые поставки Xij вычисляем по формуле xij = aij * Xj.

. Заполняем схему МОБ.

Таблица 2

Результаты решения задачи МОБ

Производящие отраслиПотребляющие отраслиКонечный продукт, yВаловой продукт, xЖивотно-водствоМашино-стронениеТорговляЖивотноводство11,903940,04670,0000137,0000188,9506Машиностроение23,807820,02340,0000274,0000317,8311Торговля83,3272120,1402804,6259411,00001419,0933Условно чистая продукция, Z69,9117137,6209614,4674822,0000Валовой продукт, X188,9506317,83111419,09331925,8750

КРАТКИЕ ВЫВОДЫ

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

Балансовый характер таблицы выражается в том, что

188,9506317,83111419,0933=188,9506317,83111419,0933

137,0000274,0000411,0000=69,9117137,6209614,4674

Из таблицы 2 видно, что при используемых исходных данных достигнут МОБ:

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

СПИСОК ЛИТЕРАТУРЫ

1.Златопольский Г.М. 1700 заданий по Microsoft Excel. - Санкт-Петербург.: БХВ-Петербург, 2003. - 546с.

2.Джон Уокенбах. Microsoft Office Excel 2007. Библия пользователя. - М. - С-Петербург - Киев: Диалектика, 2008. - 816 с.

.Лавренов С.М. Excel: Сборник примеров и задач.- М.: Финансы и статистика, 2002.- 336 с.

.Гельман В.Я. Решение математических задач средствами Excel: Практикум.- СПб.: Питер, 2003.- 237 с.

.Шикин Е.В., Чхартищвили А.Г., Математические методы и модели в управлении: Учеб. пособие. - 2-е издание, испр. - М.: Дело, 2002. - 440 с.

.Малыхин В.И. Математическое моделирование экономики: Учебно-практическое пособие. - М.: Изд-во УРАО, 1998. - 160 с.

.www.dvgups.ru/METDOC/EEMEN/ETEOR/EKTEOR/ - математические модели

8.www.math.omsu.omskreg.ru/info/learn/pprimer/afterword.htm <#"center">Приложение