Реферат: Решение экономических задач с помощью 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 имеют тип. Тип указывает что может хранить переменная: целое число, строку, дату и т.д.  

   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 * 0.04

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

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

    Определяем какие из полученных сумм доходов лежат в какой из 4-х указанных в условии областей и заносим рез-ты в таблицу в ячейки B12:D12 которые отображают премиальные

Do

 i = i + 1

 If AA_1(i) < 700 Then Worksheets("Задание2").Cells(12, i + 1).Value = Worksheets("Задание2").Cells(11, i + 1).Value * 0.01

 If AA_1(i) >= 700 And AA_1(i) < 1400 Then Worksheets("Задание2").Cells(12, i + 1).Value = Worksheets("Задание2").Cells(11, i + 1).Value * 0.015

 If AA_1(i) >= 1400 And AA_1(i) < 2800 Then Worksheets("Задание2").Cells(12, i + 1).Value = Worksheets("Задание2").Cells(11, i + 1).Value * 0.023

 If AA_1(i) >= 2800 Then Worksheets("Задание2").Cells(12, i + 1).Value = Worksheets("Задание2").Cells(11, i + 1).Value * 0.025

Loop Until i = 3

Полученная таблица выглядит следующим образом:

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

     Заполняем таблицу значениями, как указано в условии т.е 5 разновидностей комплектующих расположенных в ячейках B2:F2, и 9 вариантов стоимостей комплектующих в ячейках A3:A11. В ячейках B3:F12 будет располагаться значения стоимостей комплектующих и стоимости работы до комплектации.

    В ячейках G3 по формуле =СУММ(B3:F3) считается общая стоимость всех комплектующих, растягиваем маркер ячейки G3 до ячейки G11, и получаем стоимость всех комплектующих для всех вариантов стоимостей.

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

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