Решение экономических задач с помощью VBA

Решение экономических задач с помощью VBA

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

КАФЕДРА ПРОГРАМНОГО ОБЕСПЕЧЕНИЯ ЭВМ

РЕШЕНИЕ ЭКОНОМИЧЕСКИХ ЗАДАЧ С ПОМОЩЬЮ VBA

Курсовая работа

по дисциплине «Матем. моделирование информационных систем »

Пояснительная записка

РЕФЕРАТ

Данная курсовая работа носит название “Решение экономических задач с

помощью Visual Basic for Application”.

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

цель получения практических навыков решения экономических задач спомощью

VBA. Если говорить подробнее то был описаны способы решения следующих

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

начисление премиальных по определенным условиям, модель управления

запасами, задача об оптимальных капиталовложениях, задачи на построение

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

Пояснительная записка к курсовой работе состоит из листов, и

разделяется на 4 основные части:

1) теоретические сведения в применении к решению 8 поставленных задач

курсовой работы

2) описание алгоритмов решения поставденных задач

3) программа созданная в среде Microsoft Excel 97 SR-1 в макросах на

обьектно-ориентированом языке высокого уровня Microsoft Visual Basic

4)подробное описание модулей и макросов состовляющих в совокупности

единый документ Kurs размером 202,101 байт.

СОДЕРЖАНИЕ

Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

1. Теоретические сведения . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . .

1.1 Функция пользователя . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

1.2 Переменные и постоянные . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

1.3 Стандартные ф-ции пользователя для работы с массивами и матрицами

1.4 Обьекты, св-ва и методы VBA . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

1.5 Операторы цикла . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

2. Описание программы . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . .

2.1 Описание переменных . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

2.2 Входные и выходные данные . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

2.3 Подробное описание задач . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . .

2.3.1 Начисление премии в виде коммисионных и дополнительной

оплаты . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

2.3.2 Начисление премиальных по определенным условиям . . . . . . . . . .

. .

2.3.3 Составление ведомости расчета прибыли то товара . . . . . . . . . .

. . . . .

2.3.4 Модель управления запасами . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . .

2.3.5 Определение оптимальных капиталовложенией . . . . . . . . . . . . .

. . .

2.3.6 Нахождение оптимального раскроя . . . . . . . . . . . . . . . . .

. . . . . . . . . .

2.3.7 База данных . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . .

Список используемых источников . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . .

ПРИЛОЖЕНИЕ 1: Программа на языке Microsoft Visual Basic . . . . . . . . .

. .

ВВЕДЕНИЕ

Задачей данной курсовой работы является составление документа в среде

Microsoft Excel включающего в себя программы написанные на языке Microsoft

Visual Basic решающие 8 поставленных задач, а именно начисление премии

в виде коммисионных и дополнительной оплаты, начисление премиальных по

определенным условиям, модель управления запасами, задача об

оптимальных капиталовложениях, задачи на построение матрицы и

вычисление суммы, нахождение бумажного раскроя и база данных.

В пояснительной записке описаны алгоритмы решения задач, подробное

описание всех макросов и модулей используемых в программе, другими словами

приведены описания входных и выходных данных документа, описание наиболее

важных переменных используемых в программе.

Если говорить о среде написания программ для решения поставленных

задач - Microsoft Visual Basic for Application – то это сочетание одного из

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

такой многогранной системы как Excel. С помощью VBA можно легко и быстро

создавать разнообразные приложения даже не являясь специалистом в области

программирования. VBA содержит относительно мощную графическую среду,

позволяющую наглядно конструировать экранные формы и управляющие эл-ты. В

общем Visual Basic for Application позволяет с легкостью решать многие

задачи.

1. ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ В ПРИМЕНЕНИИ К РЕШЕНИЮ

ПОСТАВЛЕННЫХ ЗАДАЧ

1.1 функция пользователя

Функция пользователя создается на рабочем листе с названием модуль

VBA, после чего с ними можно работать с помощью мастера функций. Для

создания модуля VBA необходимо щелкнуть правой кнопкой мыши на ярлык листа,

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

контекстного меню выбрать команду “Вставка” (Insert). На вкладке

“Общие”(General) необходимо выбрать тип листа – Модуль (Module). После

нажатия кнопки OK произойдет вставка листа модуля. Теперь на листе модуля

можно ввести текст программы

Если нужно создать макрос в программе нужно зайти в среду Microsoft

Visual Basic по средствам нажатия комбинации клавиш Alt+F11, затем зайти в

меню “Сервис”, далее в подменю “Макросы” и ввести имя макроса, если во

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

макроса появится именно в нем

Sub New_Macros()

; in Module 1

End Sub

1.2 Переенные и постоянные

Все переменные в VBA имеют тип. Тип указывает что может хранить

переменная: целое число, строку, дату и т.д.

| Тип данных |Занимаемый размер памяти |Диапазон |

| Boolean | 2 bytes |True/False |

|Integer |2 bytes |-32768 до32768 |

|Long |4 bytes |-2,147,483,648 до |

| | |2,147,483,648 |

|Date |8 bytes | |

| | |От 1 января до 31 |

|Array |Зависит от размера и типа эл-тов |дакабря |

|Object |4 | |

|String |10+длина строки |Любой определенный |

|Currency |8 |Обьект |

|Variant |Зависит от содержимого переменной |От 0 да 2Х10^9 |

1.3 Стандартные ф-ции пользователя для работы с массивами и матрицами

Используются следующие ф-ции рабочего листа для работы с массивами:

Count Кол-во чисел в массиве

CountA Кол-во эл-тов массива

Sum Сумма эл-тов массива

SumProduct Сумма произведений эл-тов массивов

SumSQ Сумма квадратов эл-тов массива

SumVmY2 Сумма квадратов разностей эл-тов 2-х

массивов

SumX2mY2 Сумма разностей квадратов эл-тов 2-х

массивов

Используются следующие ф-ции рабочего листа для работы с матрицами:

Mmult – произведение 2-х матриц

Minverse – обратная матрица

Transpose – транспонированная матрица

MdeTerm – определитель матрицы

1.4 Обьекты, св-ва и методы VBA

Одним из основных понятий VBA является – обьект.

Обьект содержит список методов которые к нему применимы, а методы – это то

, что можно с обьектом делать, таким образом обьект – это программный эл-т

который имеет свое отображение на экране, содержит некоторые переменные,

определяющие его св-ва, и некоторые методы для управления обьектом. В VBA

существует множество встроенных обьектов, например:

Range – диапазон ячеек (может включать только 1 ячейку)

Cells – координаты ячейки

Sheet – лист

WorkSheets – рабочий лист

DialogSheets – диалоговое окно

Большинство обьектов принадлнжит к группе подобных обьектов. Эти группы

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

набор называемый WorkSheets

Синтаксис установки значения св-ва обьекта имеет следующий вид:

Обьект.Свойство=Выражение

Например,

WorkSheets(”List1”).Range(“W1”).Value=999

1.5 Операторы цикла

1) Для многократного выполнения одного оператора или группы операторов

служит оператор цикла FOR..NEXT

Синтаксис:

For Счетчик = Нач. знач. TO конечное знач. STEP

. . .

NEXT Счетчик

2) Цикл Do..While

Do While условие

Loop

3) Цикл Do..Until

Do

Loop Until условие

2. ОПИСАНИЕ ПРОГРАММЫ

2.1 Описание переменных

1) Задания на вычисление коммисионных, иполучения

премий (1-е и 2-е задания)

MAS1(3) – массив из 3-х эл-тов в который заносятся значения доходов

магазинов за указанные месяцы

MAS2(3) – массив в который заносятся доходы только тех магазинов доход

которых превышает 1490,00 грв.

MAS_I1(3) – массив индексов

B,C,D - вспомгательные переменные

I – переменная цикла

Max – переменная при помощи которой находится максимальное значение в

массиве доходов(сначала этой переменной специально присваивается очень

маленькое значение)

Indm, Indm2, Indm3 – индексы эл-тов массива которые являются максимумами

если исключить предыдущий найденный максимум, например сначала максимумом

является Indm, затем находится максимум из оставшихся и т.д.

2)Составление ведомости расчета прибыли от

товара.

Для определения максимумов для каждого варианта стоимости товара

полученные суммы находящиеся в ячейках (b3:f11) заносятся в массив АА_2(5),

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

Массивы MM_1(9) .. MM_6(9) используются для отображения минимальных цен

на каждый товар по всем вариантам продаж.

3) Модель управления запасами

Ф-ция пользователя CALC вычисляющая финансовые исходы при всевозможных

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

следующие переменные: Цена_покупки, Цена_продажи, Цена_Возврата; i,j

–переменные циклов. NROWS,ROWS – переменные типа Integer. Res() – массив

того же типа переменной длины.

4) Задача об оптимальных капиталовложениях

К – переменная типа Integer, отвечает за количество капиталовложений в

млн. грв.

R() – массив переменной длины, в программе используется как прибыль от

вложения i млр. в j-тый филиал, где в R[i,j] - i(1..7), а j(1..6)

I,j,p – переменные циклов.

5) Задание на нахождение оптимального раскроя

Для построения математической модели в программе производится перебор

всевозможных вариантов раскроя рулона стандартной длины на рулоны требуемой

для этого в программе используются следующие переменные:

i1,i2,i3,i4 – переменные циклов

a1,a2,a3,a4 – переменные которым присваиваются значения заказанных длин

рулонов.

t – переменная которой ф-ция Floor возвращает наибольшее целое число, не

превышающее данное.

M – минимум среди заказанных длин

6) База данных

i – переменная цикла используется в добавлении записей в БД при поиске

пустого поля

l1 – метка на которую ссылается оператор Goto

В программе происходит переход на метку в том случае если при заполнении

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

определяется пуста запись или нет.

2.2 Входные и выходные данные

1) Задания на вычисление коммисионных, иполучения

премий (1-е и 2-е задания)

Входными данными в этом типе заданий являются исходные таблицы с

данными о доходах магазинов за указанные месяцы, а также сумма доходов по

месяцам. В программе эти значения присваиваются различным переменным при

помощи обьекта

WorkSheets(“”).Range(“”).Value

Выходными данными являются премиальные полученные

в результате вычислений в соответствии с условием задания.

2)Составление ведомости расчета прибыли от

товара.

Входными данными являются 9 различных видов цен на 5 комплектующих в

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

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

на товар, а также определение максимальной и минимальной цены на товар

определяемого не только по стоимости на товар в целом, но и по стоимости

комплектующих по отдельности.

3) Модель управления запасами

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

издательства продавцом, и

цена возврата в случае нереализации товара продавцом, также известно

количество реализуемых за раз продавцом пачек, и число событий за отчетный

период времени.

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

оптимального обьема продаж, которые в прогремме выводятся при помощи

диалогового окна.

4) Задача об оптимальных капиталовложениях

В этой задаче входными данными являются значения мат. ожиданией прибыли

как ф-ций капиталовложений, в соств. с условием 6 филиалов и 7 млн. грв.

Выходными данными являются таблица где производится: оптимальное

распределение средств, когда А млн. вкладываются в 1-й и 2-й филиалы

вмесле, оптимальное распределение средств, когда А млн. вкладываются в 1-

й,2-й и 3-й филиалы вмесле, оптимальное распределение средств, когда А млн.

вкладываются в 1-й,2-й,3-й,4-й филиалы вмесле,и оптимальное распределение

средств, когда А млн. вкладываются в 1-й,2-й,3-й,4-й и 5-й филиалы вмесле и

1-й,2-й,3-й,4-й,5-й и 6-й.

Из этой таблицы находится максимальная ожидаемая прибыль в данном

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

сколько млн. и в какие филиалы нужно вложить:

6 филиал – 2 млн.

5 филиал – 1 млн.

6 филиал – 1 млн.

6 филиал – 1 млн.

6 филиал – 1 млн.

6 филиал – 1 млн.

5) Задание на нахождение оптимального раскроя

Входными данными являются значения заказанных длин и их кол-ва, а также

значение стандартной длины.

Выходными данными будут значения отходов при всех вариантах раскроя

рассмотренных при решении задачи, и суммарное кол-во отходов которые по

условию задачи необходимо минимизировать.

6) База данных

Сдесь входные данные являются выходными, т.е что пользователь ввел то

и отобразится в таблице. База данных содержит слежующие поля:

2 поля ввода: Фамилия, имя

3 раскрывающихся списка: Работа,Стаж,Рабочий день (час)

2. Группы по 2 флажка: Кредитная карточка, загран. Паспорт

2 группы по 2 переключателя: Пол,Семейное положение

3. поля ввода со счетчиками: Возвраст,Оклад,отпуск

2.3 Подробное описание задач

2.3.1 Начисление премии в виде коммисионных и

дополнительной оплаты.

Создаем таблицу начисления премий, в ячейки B4:D10 заносим значения

доходов магазинов за указанные месяцы, сбоку в ячейках A4:A10 будут

располагаться названия месяцев, согласно варианту – с ноября по май, а в

B3:D3 – магазины, таким образом на пересечении будет показана величина

дохода магизина который находится в этом столбце и месяца который

расположен в этой строке.

В ячейке B11 считаем доход 1-го магазина за все месяцы по формуле

=СУММ(B4:B10), и растягиваем маркер чертежа до ячейки D10, таким образом

производится подсчет доходов всех магазинов за все месяцы.

Определяем какие же из доходов магазинов превышают 1490.00 грв. , для

которых входят в это число премиальные будут составлять в соответствии с

условием 2% от дохода за эти месяцы, остальные эл-ты в массиве специально

заполняются нулями.

Do

k = mas1(i)

If k > 1490 Then mas2(i) = mas1(i) Else mas2(i) = 0

i = i + 1

Loop Until i = 4

В этом цикле в массив заносятся только те значения которые превышают

заданное по условию значение допустимости, в данном случае это 1490,00 руб.

Теперь доходы среди оставшихся магазинов нужно распределить по убыванию,

для того, чтобы в соответствии с условием начислить магазинам

дополнительные премиальные за 1-е, 2-е, 3-е место. Для этого выбираем

следующий алгоритм: находим максимум среди этих доходов и назначаем этому

магазину соответствующую 1-му месту премию, замем выбираем максимум из

доходов не учитывая уже сужествующий (т.е. не учитывая первый максимум), и

назначает этому магазину соотв. 2-му месту премию и т.д.

Do

i = i + 1

If mas2(i) > Max Then

Max = mas2(i)

indm = i

End If

Loop Until i = 3

Складываем полученные 2% с теми что начисляются дополнительно за 1,2,3

и т.д места, и заносим резельтаты в таблицу в строку “Премиальные”

Worksheets([лист]).Cells([координаты ячеек]).Value = Max * 0.02 + Max *

Страницы: 1, 2