Абрамченко Н.В. Мещеряков Е.А. Мещерякова Н.А. Ультан А.Е.

Абрамченко Н.В. Мещеряков Е.А. Мещерякова Н.А. Ультан А.Е.

Реализация математических методов и моделей в MS Excel. Часть II.

Учебное пособие

Омск 2019

УДК 004.652

ББК 32.973.26 – 018.2

 

Рецензенты:

д. пед н., профессор, зав. кафедрой «Информационная безопасность» Сибирской автомобильно-дорожной академии Семенова З.В.

к. пед. н., доцент, доцент кафедры «Информационная безопасность» Сибирской автомобильно-дорожной академии Анацкая А.Г.

 

Абрамченко Н.В., Мещеряков Е.А., Мещерякова Н.А., Ультан А.Е.

Прикладные экономические задачи в MS Excel. Компьютерный практикум: Учебное пособие – Омск: Издательский центр КАН, 2019. – 80 c.

 

ISBN  

 

Учебное пособие предназначено для студентов направлений подготовки "Менеджмент" и "Экономика", обучающихся по программам бакалавриата в рамках изучения дисциплин "Математика", "Компьютерный практикум". В пособии рассматриваются вопросы использования MS Excel для решения математических задач, нацеленные на формирование у студентов с помощью компьютерных вычислений практических навыков по реализации математических методов и моделей, применяемых в профессиональных задачах.

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

Рекомендовано Учебно-методическим советом Омского филиала Финуниверситета в качестве учебного пособия по дисциплинам "Математика", "Компьютерный практикум", для студентов, обучающихся по направлениям 38.03.02 "Менеджмент", 38.03.01 "Экономика", (программы подготовки бакалавров). Протокол от 24 января 2019 г. № 1.

 

УДК 004.652

ББК 32.973.26 – 018.2

ISBN

 

© Абрамченко Н.В., 2019

© Мещеряков Е.А., 2019

© Мещерякова Н.А., 2019

© Ультан А.Е., 2019

СОДЕРЖАНИЕ

Введение.. 4

Тема 1....... Нахождение глобальных экстремумов функции (4 ЧАСа)… 5

Тема 2.... исследование числовых характеристик функции (4 часа)… 14

Тема 3. Решение прикладных экономических задач: предельные величины в микроэкономике (4 час.) 22

Тема 4......... Закон убывающей эффективности производства (применение производной) 27

Тема 5. Встроенные функции (продолжение) Решение систем линейных уравнений (4 часа). 30

Тема 6.................... Прикладные задачи оптимизации. Линейное программирование (10 час.) 45

Тема 7... Нелинейное программирование и его применение в экономике.. 66

Тема 8.......................... Встроенные финансовые функции (8 час.) 75

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

Введение

Учебное пособие является продолжением учебного пособия «Финансово-экономические вычисления в MS Excel. Компьютерный практикум» и предназначено для создания прикладной основы использования математического аппарата средствами вычислительных компьютерных технологий, формирования у студентов знаний о вычислительных методах реализации математических объектов и моделей, используемых в экономике и финансах, а также о средствах визуализации математических результатов исследований.

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

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

- проведения расчетов над большими наборами данных;

- автоматизации итоговых вычислений;

- табулирования функций;

- решения задач путём подбора значений параметров;

- проведения поиска оптимальных значений параметров;

- обработки результатов экспериментов;

- построения диаграмм и графиков по имеющимся данным.

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

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

Тема 1.

Тема 1. Нахождение глобальных экстремумов функции (4 ЧАСа)

Матричные функции

Матричные функции используют для решения систем уравнений размерности n x n, т.е. когда число уравнений в системе равно числу неизвестных:

функция МОПРЕД(массив) возвращает определитель матрицы;

функция МОБР(массив) служит для нахождения обратной матрицы;

функция МУМНОЖ(массив1; массив2) возвращает произведение матриц.

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

Для нахождения обратной матрицы выделите пустой диапазон такого же размера, как и исходная матрица, вызовите функцию МОБР, где в качестве массива укажите диапазон ячеек с исходной матрицей, нажмите сочетание клавиш Shift+Ctrl+Enter. В выделенном диапазоне отобразится обратная матрица.

Нахождение произведения матрицы А на матрицу В возможно, если число столбцов матрицы А равно числу строк матрицы В. Для нахождения произведения матриц выделите пустой диапазон такого же размера, как и исходная матрица В, вызовите функцию МУМНОЖ, где в качестве массива1 укажите диапазон ячеек с исходной матрицей А, в качестве массива2 – диапазон ячеек с матрицей В и нажмите сочетание клавиш Shift+Ctrl+Enter. В выделенном диапазоне отобразится результат умножения матриц.

Над диапазонами, также как и над числами, можно производить арифметические операции, только в этом случае завершаться операция должна не нажатием клавиши Enter, а нажатием сочетания клавиш Shift+Ctrl+Enter!

 

Пример 5.3.

Используя матричные функции, решите систему уравнений:

2х₁ – 2х₂ + х₃ = 2

х₁ + 2х₂ + 3х₃ = 4

2х₁ – 4х₂ + 6х₃ = 8

Решение. Для решения задачи на листе Excel введем матрицу А и вектор В (рис. 24). В некоторой пустой ячейке (С6) найдем определитель матрицы А, используя встроенную функцию МОПРЕД.

Рис. 24. Исходные данные задачи и определитель матрицы

Если определитель матрицы отличен от 0, то система имеет решение. В нашем случае определитель равен 40, т.е. система решение имеет. Найдем это решение. Выделим на листе диапазон пустых ячеек размерности 3х3, например, В8: D 10, вызовем встроенную функцию МОБР, где в качестве массива укажем диапазон ячеек В2: D 4, нажмем сочетание клавиш Shift+Ctrl+Enter.

В выделенном диапазоне отобразится обратная матрица. Если Вы не увидели в диапазоне желаемого результата (значение появилось только в одной ячейке, вообще нигде не появилось и др.), нажмите клавишу F2 (в первой ячейке диапазона отобразится =МОБР(B2:D4)), затем Shift+Ctrl+Enter.

Если результат опять Вас не устраивает, значит, Вы не выделили диапазон, равный по размерности обратной матрице!

Для нахождения вектора Х выделим пустой диапазон ячеек размерности 1х3, например, F 8: F 10, вызовем встроенную функцию МУМНОЖ, где в качестве массива1 укажем диапазон ячеек с обратной матрицей В8: D 10, в качестве массива2 – диапазон ячеек F 2: F 4 с вектором В и нажмем сочетание клавиш Shift+Ctrl+Enter. Результат выполнения задания приведен на рисунке 25. В ячейке F9 отображается число 0 в экспоненциальном формате, указывающем на некоторую погрешность вычислений.

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

Пример 5.4.

Решите систему уравнений методом Крамера

2х₁ – 2х₂ + х₃ = 2

х₁ + 2х₂ + 3х₃ = 4

2х₁ – 4х₂ + 6х₃ = 8

Решение. Решим ту же систему, что и в предыдущем примере, методом Крамера, и сравним полученные решения. Для этого на листе Excel скопируем и вставим в свободные диапазоны три раза матрицу А (рис. 26). Последовательно заменим столбцы этих матриц на вектор свободных членов: в первой матрице – первый столбец, во второй матрице – второй столбец, в третьей – третий. В ячейках К12:К14 найдем определители полученных матриц, используя встроенную функцию МОПРЕД. Далее в ячейках G 8: G 10 найдем отношения частных определителей к общему (рис. 26). Полученные разными способами решения совпадают.

Рис. 26. Исходные данные задачи и определитель матрицы

Пример 5.7.

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

Таблица 9.

Вид изделия №	Производительность предприятия (изд. / день)					Затраты видов сырья (ед. веса / изд.)
	1	2	3	4	5	1	2		3
1	4	5	3	6	7	1	2		3
2	0	2	4	3	0	3	5		6
3	8	15	0	4	6	4	4		5
4	3	10	7	5	4	5	8		6
	Количество рабочих дней в году					Цены видов сырья (ден. ед. / ед. веса)
	1	2	3	4	5	1	2	3
	200	150	170	120	140	40	50	60

 

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

Решение. Перенесем исходные данные на лист Excel. Компьютерная модель задачи имеет вид (рис. 31).

Для расчета годовой производительности каждого предприятия необходимо найти произведение количества произведенных изделий в день каждым предприятием на количество рабочих дней в году каждого предприятия. Для этого выделим весь диапазон В16: F 19 и введем формулу: =B4:F7*B11:F11, закончив ее ввод сочетанием клавиш Shift+Ctrl+Enter.

Рис. 31. Компьютерная модель примера 5.5.

Аналогично рассчитаем матрицу финансовых затрат на каждый вид сырья для каждого изделия (диапазон (K 4:М7): {=G4:I7*G11:I11}).

Для расчета годовой потребности каждого предприятия в каждом виде сырья необходимо найти сумму произведений годового выпуска каждого изделия на затраты каждого вида сырья на единицу изделия. Для этого транспонируем матрицу затрат видов сырья. Для первого изделия в диапазоне В24:В26 найдем произведение полученной транспонированной матрицы (диапазон I 23: L 25) на вектор годовой производительности первого предприятия (диапазон В16:В19). Формулу скопируем вправо для расчета годовой потребности в сырье других предприятий (в диалоговом окне функции МУМНОЖ не забудьте закрепить общий для всех диапазон I 23: L 25!).

Аналогично рассчитаем годовое финансирование для каждого предприятия на закупку сырья предварительно транспонировав в диапазоне I 30: L 32 матрицу финансовых затрат на сырье (диапазон К4:М7). Результаты всех расчетов приведены на рисунке 31.

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

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

x_i = x_{i 1} + x_{i 2} + … + x_{i n} + y_i, i = 1, 2, …, n,

где x_i — общий объем выпускаемой продукции i -й отрасли;

x_ij — объем продукции i -й отрасли, потребляемый j -й отраслью при производстве объема продукции x_j;

y_i — объем продукции i-й отрасли конечного потребления (для реализации в непроизводственной сфере). Сюда относятся личное потребление граждан, удовлетворение общественных потребностей, содержание государственных институтов и т.д.

Для производства продукции j -й отрасли объемом x_i нужно использовать продукцию i -й отрасли объемом a_ijx_i, где а_ij — постоянное число, характеризующее прямые затраты.

Это допущение позволяет представить модель многоотраслевой экономики (модель Леонтьева) в виде системы линейных уравнений, которая в матричной форме имеет вид Х = AХ + Y, (А= П / X^T, П{Х_ij} –матрица потребления, т.е. a_ij = Х_ij / Х _i),

где X — вектор валового выпуска;

Y — вектор объема продукции конечного потребления;

A — матрица прямых затрат.

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

(E – A) X = Y, где E — единичная матрица.

Если существует обратная матрица (E – A)^–1 (матрица полных затрат), то существует единственное решение системы X = (E – A)^–1 Y.

В таблице приведены данные об исполнении баланса за отчетный период (у.е.):

Таблица 10.

отрасль	потребление		конечный продукт	валовой выпуск
	I	II
I	7	21	72	100
II	12	15	123	150

Вычислить необходимый объем валового выпуска каждой отрасли, если конечное потребление I-ой отрасли увеличится вдвое, а II-ой сохранится на прежнем уровне.

Решение. На лист Excel введем данные для вектор X _i, матриц X _{i j}, Е, новый вектор Y, а также свободные диапазоны для матриц X _i ^T, А, (E-A), (E-A) ^-1 и результирующего вектора Х (рис. 33). Рассчитаем матрицы X _i ^T, А и Е-А по формулам, представленным на рисунке 32.

Определитель матрицы | Е-А | = 0,8202 ≠ 0, так что обратная матрица и решение указанной системы уравнений существуют. Найдем его с использованием встроенных функций МОБР и МУМНОЖ (рис. 33).

Таким образом, согласно рис. 33, в I-ой отрасли валовой выпуск надо увеличить до 189,5 у.е., а во II-ой — до 153,5 у.е.

 

Рис. 32. Формулы расчетов матриц А и Е-А

Рис. 33. Решение балансовых экономических моделей

Задания для самостоятельного выполнения

Задание 5.1

Предприятие ежесуточно выпускает четыре вида изделий, производственно-экономические показатели которых приведены в таблице:

Таблица 11.

Вид изделия	Количество выпускаемых изделий, шт.	Расход сырья, кг/изд.	Норма времени изготовления, ч/изд.	Стоимость изделия, ден. ед/изд.
	N	P	T	Ц
1	20	5	10	30
2	50	2	5	15
3	30	7	15	45
4	40	4	8	40

 

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

 

Задание 5.2. Найти произведение матриц:

1)

2 -1 1 1

1 3 2 6

1 1 2 -1

9 6 -3 2

                                                            2)

 

3)

3 5 -1 1 -2 0

2 4 -3 0  5 1

2 3 -4 -1 -1 3  1 -2 5

1 2 3 3 -1 1 1 2 2

                                                         4)

 

 

1 -1 3

4 3  2

1 2 -3

3 1 -1 4 -1 3 2 6 0

5)                                                                6)

Задание 5.3

Используя операции с матрицами, решите систему уравнений:

х₁ + 2х₂ + 8х₃ + 22 = 0

х₁ – х₂ + х₃ + 2,5 = 0

10х₁ – 3х₂ – 19 = 0

Задание 5.4

Решите систему уравнений методом Крамера:

5х₁ + 3х₂ – х₃ = 8

2х₁ – 4х₂ + 3х₃ = 3

7х₁ + 4х₂ – 2х₃ = 9

 

Задание 5.5

Решите систему уравнений методом наименьших квадратов:

8х₁ + 5х₂ = 10

5х₁ + 2х₂ = 4

7х₁ + 4х₂ = 8

 

Задание 5.6.

Ресторан специализируется на выпуске трех видов фирменных блюд: В1, В2, ВЗ, при этом используются ингредиенты трех типов S1, S2, S3. Нормы расхода каждого из них на одно блюдо и объем расхода ингредиентов на 1 день заданы таблице 12. Найти расход каждого ингредиента, если выпускается по 250 единиц каждого блюда. Хватит ли имеющихся запасов?

Таблица 12.

Ингредиент	Нормы расхода ингредиентов на 1 блюдо  (у. е.)			Запас ингредиентов на 1 день (у. е.)
	В1	В2	В3
S1	4	6	5	3650
S2	2	3	1	1550
S3	1	4	3	2100

 

Задание 5.7.

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

Таблица 13.

№	Отрасль	Потребление					Конечный продукт	Валовой выпуск (ден.ед)
		1	2	3	4	5
1	Станкостроение	15	12	24	23	16	10	100
2	Энергетика	10	3	35	15	7	30	100
3	Машиностроение	10	5	10	10	10	5	50
4	Автомобильная промышленность	10	5	10	5	5	15	50
5	Добыча и переработка углеводородов	7	15	15	3	3	50	100

 

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

В приведенной таблице в первых пяти столбцах (группа “Потребление”) содержатся значения x_ij, в последнем столбце содержатся элементы вектора валового выпуска x, в предпоследнем столбце – элементы вектора объема конечного потребления y.

Задание 5.8. В таблице приведены данные по балансу за некоторый период времени между тремя отраслями.

Таблица 14.

№	Отрасль	Потребление			Конечный продукт	Валовой выпуск (ден.ед)
		1	2	3
1	Добыча и переработка углеводородов	5	35	20	40	100
2	Энергетика	10	10	20	60	100
3	Машиностроение	20	1	10	10	50

 

Требуется найти объем валового выпуска каждого вида продукции, если конечное потребление по отраслям увеличить, соответственно, до 60, 70 и 30 условных денежных единиц.

III