Решение экономических задач с помощью VBA
МИНИСТЕРСТВО ОБРАЗОВАНИЯ УКРАИНЫ
КАФЕДРА ПРОГРАМНОГО ОБЕСПЕЧЕНИЯ ЭВМ
1РЕШЕНИЕ ЭКОНОМИЧЕСКИХ ЗАДАЧ С ПОМОЩЬЮ 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 функция пользователя
Функция пользователя создается на рабочем листе с названием модуль VBA, после чего с ними можно работать с помощью мастера функций. Для создания модуля VBA необходимо щелкнуть правой кнопкой мыши на ярлык листа, перед которым нужно разместить данный модуль, и из раскрывающегося контекстного меню выбрать команду “Вставка” (Insert). На вкладке “Общие”(General) необходимо выбрать тип листа – Модуль (Module). После нажатия кнопки OK произойдет вставка листа модуля. Теперь на листе модуля можно ввести текст программы
Если нужно создать макрос в программе нужно зайти в среду Microsoft Visual Basic по средствам нажатия комбинации клавиш Alt+F11, затем зайти в меню “Сервис”, далее в подменю “Макросы” и ввести имя макроса, если во время выполнения был выделен какой-то модуль то программый код созданного макроса появится именно в нем
Sub New_Macros()
; in Module 1
End Sub
1.2 Переенные и постоянные
Все переменные в VBA имеют тип. Тип указывает что может хранить переменная: целое число, строку, дату и т.д.
Тип данных | Занимаемый размер памяти | Диапазон |
Boolean Integer Long Date Array Object String Currency Variant | 2 bytes 2 bytes 4 bytes 8 bytes Зависит от размера и типа эл-тов 4 10+длина строки 8 Зависит от содержимого переменной | True/False -32768 до32768 -2,147,483,648 до 2,147,483,648 От 1 января до 31 дакабря Любой определенный Обьект От 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.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.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
Месяц | М а г а з и н ы | ||
1 | 2 | 3 | |
Ноябрь | 100 | 100 | 120 |
Декабрь | 300 | 150 | 650 |
Январь | 1000 | 130 | 250 |
Февраль | 1000 | 120 | 50 |
Март | 0 | 100 | 760 |
Апрель | 100 | 100 | 0 |
Май | 310 | 600 | 500 |
Всего | 2810 | 1300 | 2330 |
Премиальные! | 168,6 | 0 | 93,2 |
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
Полученная таблица выглядит следующим образом:
Месяц | М а г а з и н ы | ||
1 | 2 | 3 | |
Ноябрь | 50 | 100 | 120 |
Декабрь | 50 | 150 | 650 |
Январь | 100 | 130 | 250 |
Февраль | 100 | 120 | 50 |
Март | 120 | 100 | 760 |
Апрель | 100 | 100 | 1000 |
Май | 50 | 600 | 500 |
Всего | 570 | 1300 | 3330 |
Премиальные! | 5,7 | 19,5 | 83,25 |
2.3.3 Составление ведомости расчета прибыли от товара
Заполняем таблицу значениями, как указано в условии т.е 5 разновидностей комплектующих расположенных в ячейках B2:F2, и 9 вариантов стоимостей комплектующих в ячейках A3:A11. В ячейках B3:F12 будет располагаться значения стоимостей комплектующих и стоимости работы до комплектации.
В ячейках G3 по формуле =СУММ(B3:F3) считается общая стоимость всех комплектующих, растягиваем маркер ячейки G3 до ячейки G11, и получаем стоимость всех комплектующих для всех вариантов стоимостей.
В программе определяется какая деталь в каком месте самая дешовая, если не учитывать транспортные затраты и задаться целью купить детали по минимальным ценам. Для этого в программе определяются минимальные стоимости по 5-ти деталям.
Полученная ведомость будет выглядеть следующим образом:
Варианты | В и д ы к о м п л е к т у ю щ и х | MIN / MAX | |||||
Стоимости | 1-я деталь | 2-я деталь | 3-я деталь | 4-я деталь | 5-я деталь | Всего | |
1-й | 20 | 90 | 5 | 50 | 60 | 225 | |
2-й | 19 | 85 | 4 | 55 | 50 | 213 | |
3-й | 20 | 81 | 4 | 50 | 56 | 211 | Миним. Цена на товар |
4-й | 25 | 87 | 8 | 57 | 58 | 235 | |
5-й | 29 | 87 | 5 | 55 | 60 | 236 | |
6-й | 18 | 88 | 4 | 40 | 61 | 211 | |
7-й | 30 | 99 | 9 | 66 | 60 | 264 | |
8-й | 30 | 99 | 9 | 66 | 64 | 268 | Макс. Цена на товар |
9-й | 21 | 90 | 6 | 54 | 55 | 226 | |
До комплектации | 15 | 75 | 3 | 40 | 50 | 183 |
2.3.4 Модель управления запасами
Вводим исходные значения, т.е. значения покупки продавцом журналов, продажи этих журналов и возврата в типографию в случае не реализации товара. Ввод всего этого производится в диалоговом окне, которое создается как UserForm со специальными кнопками и полями ввода покупки журналов, продажи, и возврата к типографию. Окно ввода выглядит так:
Составляем таблицу состоящую из обьема реализации, числа событий, и вероятности этих событий, первые два нам даны по условию а вероятность этих событий нужно посчитать. Вводим в ячейку D7 следующую формулу вычисления вероятностей {=D6/СУММ($D$5:$I$5)}
и растягиваем маркер до ячейки I7.
В ячейках C10:H15 спомощью ф-ции пользователя CALC Вычисляем финансовые исходы при всевозможных вариантых событий покупки журналов и их реализации
Function CALC(buy As Variant) As Variant
Dim Цена_продажы, Цена_покупки, Цена_возврата, NRows, i, j As Integer, Result() As Integer
NRows = buy.Rows.Count
Цена_продажы = Range("a2").Value
Цена_покупки = Range("b2").Value
Цена_возврата = Range("c2").Value
ReDim Result(NRows, NRows)
For i = 1 To NRows
For j = 1 To NRows
If i <= j Then Result(i, j) = buy(i) * (Цена_продажы - Цена_покупки)
If i > j Then Result(i, j) = buy(j) * (Цена_продажы - Цена_покупки) - (buy(i) - buy(j)) * (Цена_покупки - Цена_возврата)
Next j
Next i
CALC = Result
End Function
В ячейках J11:J16 с помощью формулы {=МУМНОЖ(C10:H15;ТРАНСП(D7:I7))} находим ожидаемую прибыль, соответсввующую различным вариантам покупки журналов.
В ячейке F16 спомощью формулы =НАИБОЛЬШИЙ(J11:J16;1)
вычисляем максимальную прибыль. Ее также можно найти воспользовавшись ф-цией МАКС, находящей максимальный эл-т из списка
=Макс(J11:J16)
В ячейке F17 по формуле =(ПОИСКПОЗ(НАИБОЛЬШИЙ(J11:J16;1);J11:J16;0)-1)*5
соответствующий оптимальный обьем покупок газет. Затем функция CALC выводит эти оптимальные значения в окне сообщений.
Ф-ция наибольший возвращает К-е наибольшее значение из множества данных. Эта ф-ция используется для того чтобы выбрать значение по его относительному местоположению. Например, фунуцию НАИБОЛЬШИЙ можно использовать для того чтобы определить наилучший, второй, третий результат в балах, показанный при тестировании. Систаксис программы такой:
НАИБОЛЬШИЙ(массив;К) где Массив – это массив или диапазон ячеек где определяется наибольшее значение, к – позиция (начиная с наибольшей) в массиве или диапазоне.
Все результаты занесенные в таблицу будут выглядеть следующим образом:
П р о д а ж а | |||||||||
П | 0 | 4 | 8 | 12 | 14 | 18 | |||
о | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Покупка | Прибыль |
к | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | - р. |
у | 8 | 0 | -20 | 16 | 16 | 16 | 16 | 4 | - р. |
п | 12 | 0 | -40 | -4 | 32 | 32 | 32 | 8 | 12,94р. |
к | 14 | 0 | -60 | -24 | 12 | 48 | 48 | 12 | 16,88р. |
а | 18 | 0 | -70 | -34 | 2 | 38 | 56 | 14 | 9,00р. |
Максимальная прибыль | 16,88р. | 18 | 0,28р. | ||||||
Оптимальный обьем | 15 |
2.3.5 Определение оптимальных капиталовложений
Создаём исходную таблицу и заполняем ее мат. ожиданиями прибылей в состветствии с условием.
Ф и л и а л ы | ||||||
Млн. грв | 1 | 2 | 3 | 4 | 5 | 6 |
0 | 0 | 0 | 0 | 0 | 0 | 0 |
1 | 0,11 | 0,12 | 0,18 | 0,2 | 0,17 | 0,12 |
2 | 0,11 | 0,13 | 0,18 | 0,22 | 0,17 | 0,23 |
3 | 0,12 | 0,13 | 0,19 | 0,24 | 0,18 | 0,24 |
4 | 0,12 | 0,13 | 0,19 | 0,26 | 0,18 | 0,24 |
5 | 0,13 | 0,13 | 0,2 | 0,29 | 0,19 | 0,25 |
6 | 0,13 | 0,13 | 0,2 | 0,31 | 0,19 | 0,25 |
7 | 0,14 | 0,13 | 0,2 | 0,33 | 0,2 | 0,26 |
Для дальнейшего решения задачи, вводим следующие обозначения:
Пусть R(i,j) – прибыль получаемая от вложения i млн. грв. В j-тый филиал, где в соотв. С вариантом i от (0,7), а j от (0,6)
F(A,1,2) – оптимальное распределение средств, когда А млн. грв. вкладываются в 1,2 филиалы вместе
F(A,1,2,3) – оптимальное распределение средств, когда А млн. грв. вкладываются в 1,2,3 филиалы вместе
F(A,1,2,3,4) – оптимальное распределение средств, когда А млн. грв. вкладываются в 1,2,3,4 филиалы вместе.
F(A,1,2,3,4,5) – оптимальное распределение средств, когда А млн. грв. вкладываются в 1,2,3,4,5 филиалы вместе.
F(A,1,2,3,4,5,6) – оптимальное распределение средств, когда А млн. грв. вкладываются в 1,2,3,4,5 филиалы вместе.
Значения I при которых достигается максимум определяют оптимальные капиталовложения в филиалы.
Максимальные значения ожидаемых прибылей вычисляется в программе и заносится в ячейки H4:L11 и будет выглядеть следующим образом:
М а к с и м у м ы | ||||
1 и 2 | 1,2 и 3 | 1,2,3 и 4 | 1,2,3,4 и 5 | 1,2,3,4,5 и 6 |
0 | 0 | 0 | 0 | 0 |
0,12 | 0,18 | 0,2 | 0,2 | 0,2 |
0,23 | 0,3 | 0,38 | 0,38 | 0,38 |
0,24 | 0,41 | 0,5 | 0,55 | 0,55 |
0,24 | 0,42 | 0,61 | 0,67 | 0,67 |
0,25 | 0,42 | 0,63 | 0,78 | 0,79 |
0,25 | 0,43 | 0,65 | 0,8 | 0,9 |
0,26 | 0,43 | 0,67 | 0,82 | 1,01 |
В программе переменной К – присваиваем значение равное обьему капиталовложений. В массив R с рабочего листа капиталовложения вводим ожидаемую прибыль, распределенную по филиалам.
В диапазон ячеек (B14:K22) выводится оптимальное распределение капиталовложений по филиалам. После вычислений можно увидеть что максимальныя ожидаемая прибыль составляет 1,01 млн. грв., из таблицы видны следующие рез-ты:
6 филиал – 2 млн.
5 филиал – 1 млн.
4 филиал – 1 млн.
3 филиал – 1 млн.
2 филиал – 1 млн.
1 филиал – 1 млн.
Сама таблица выглядит следующим образом:
Ф и л и а л ы | ||||||||||
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
1 | 0 | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | 0 |
2 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 0 | 2 | 0 |
3 | 1 | 2 | 2 | 1 | 2 | 1 | 2 | 1 | 3 | 0 |
4 | 1 | 3 | 3 | 1 | 3 | 1 | 3 | 1 | 3 | 1 |
5 | 3 | 2 | 2 | 3 | 3 | 2 | 4 | 1 | 4 | 1 |
6 | 3 | 3 | 3 | 3 | 3 | 3 | 5 | 1 | 4 | 2 |
7 | 5 | 2 | 2 | 5 | 3 | 4 | 6 | 1 | 5 | 2 |
Млн. грв. | 1 | 2 | 1,2 | 3 | 1,2,3 | 4 | 1,2,3 и 4 | 5 | 1,2,3,4 и 5 | 6 |
2.3.6 Задание на нахождение оптимального раскроя
Составляем таблицу в которой будут приведены остатки от раскроя на заказ при различных вариантах раскроя.
Например по условию в соответствии с вариантом стандартная длина раскроя равна 28 метров,
т.е. первый вариант раскроя будет сосотавлять 0 рулон дляной 4 м, 0 рулонов длиной 6м и 4 рулона длиной 9 м, рулонов длиной 11 м. не будет, что в сумме даст 27, следовательно отходы будут составлять 1 метр. Второй вариант когда 1 рулон по 6 м и два по 11 м, в этом случае остатков не будет и т.д. Всего получается 19 вариантов раскроя.
В программе это будет выглядеть таким образом:
l = 28
a1 = 4: a2 = 6
a3 = 9: a4 = 11
r = 4
m = Application.Min(a1, a2, a3, a4)
t = Application.Floor(l / m, 1)
For i1 = 0 To t
For i2 = 0 To t
For i3 = 0 To t
For i4 = 0 To t
s = 28 - a1 * i1 - a2 * i2 - a3 * i3 - a4 * i4
If s >= 0 And s < m Then
Cells(r, 1).Value = r - 3
Cells(r, 2).Value = i1
Cells(r, 3).Value = i2
Cells(r, 4).Value = i3
Cells(r, 5).Value = i4
Cells(r, 6).Value = s
r = r + 1
End If
Next i4
Next i3
Next i2
Next i1
На листе это будет выглядеть так:
Д л и н ы р у л о н о в н а з а к а з | |||||
Варианты | Остаток | ||||
раскройки | 4 | 6 | 9 | 11 | от расктоя |
1 | 0 | 0 | 3 | 0 | 1 |
2 | 0 | 1 | 0 | 2 | 0 |
3 | 0 | 1 | 1 | 1 | 2 |
4 | 0 | 3 | 1 | 0 | 1 |
5 | 1 | 0 | 0 | 2 | 2 |
6 | 1 | 1 | 2 | 0 | 0 |
7 | 1 | 2 | 0 | 1 | 1 |
8 | 1 | 2 | 1 | 0 | 3 |
9 | 1 | 4 | 0 | 0 | 0 |
10 | 2 | 0 | 1 | 1 | 0 |
11 | 2 | 0 | 2 | 0 | 2 |
12 | 2 | 1 | 0 | 1 | 3 |
13 | 2 | 3 | 0 | 0 | 2 |
14 | 3 | 1 | 1 | 0 | 1 |
15 | 4 | 0 | 0 | 1 | 1 |
16 | 4 | 0 | 1 | 0 | 3 |
17 | 4 | 2 | 0 | 0 | 0 |
18 | 5 | 1 | 0 | 0 | 2 |
19 | 7 | 0 | 0 | 0 | 0 |
Пусть Xj – кол-во стандартных рулонов, разрезанных по варианту j, где j[1..19]. Ограничения налагаемые на переменные Xj связаны с требованием обеспечить изготовление заказанного кол-ва нестандартных рулонов. Ф-ция цели учитывает суммарные отходы, получаемые при выполнении заказа. Таким образом имеем следующую мат. модель:
Минимизировать:
Z=x1+2x3+x4+2x5+x7+3x8+2x11+2x12+2x13+x14+x15+3x16+
+2x18 + 4(x5+x6+x7+x8+x9+2x10+2x11+2x12+2x13+3x14+4x15+4x16+4x17+5ч18+7x19-220)+ 6(...-210)+9(...-350)+
+11(...-380)
Отведем диапазон ячеек (i4:i22) под переменные. Введем в диапазон ячеек (j3:m3) левые части ограничений, определенные слежующими формулами:
=СУММПРОИЗВ($I$4:$I$22;B4:B22)
=СУММПРОИЗВ($I$4:$I$22;c4:c22)
=СУММПРОИЗВ($I$4:$I$22;d4:d22)
=СУММПРОИЗВ($I$4:$I$22;e4:e22)
В ячейку N4 введем ф-цию цели:
=СУММПРОИЗВ($I$4:$I$22;F4:F22)+B3*(СУММПРОИЗВ($I$4:$I$22;B4:B22)-J3)+C3*(СУММПРОИЗВ($I$4:$I$22;C4:C22)-K3)+D3*(СУММПРОИЗВ($I$4:$I$22;D4:D22)-L3)+E3*(СУММПРОИЗВ($I$4:$I$22;E4:E22)-M3)
где в ячейки B3:E3 введены длины, а в ячейки J3:M3 – кол-ва заказанных рулонов
Выберем команду сервис – Поиск решения и заполним открывшееся диалоговое окно Поиск решения (Solver):
- Установим целевую ячейку – N4
- Изменяя ячейки I4:I22
- Ограничения $I$4:$I$22=целое
$I$4:$I$22>=0
$j$4:$m$4>=$j$3:$m$3
- Ф-ция = минимизация
К о л - в а з а к а з а н н ы х р у л о н о в | ||||
220 | 210 | 350 | 380 | Отходы |
220 | 210 | 350 | 380 | 49,99996 |
2.3.7 База данных
Создадим поля базы данных, и занесем их в таблицу. База данных будет заполняться программой, программе не требуются названия полей, но для облегчения ориентации в первой строке введем данные соответствующие полям БД
Создадим кнопку “Добавление” для добавления записей в БД, делается это так: Вызываем панель инструментов на которой расположены примитивы, т.е. окна ввода, кнопки и т.д. Создаем на форме кнопку, и спомощью св-ва Caption присваиваем ей название “Добавление”
Создадим макрос который будет отвечать за обработку событий по нажатию этой кнопки. Перейдем в среду Visual Basic for Application и в меню «Вставка» выберем UserForm, на эту форму и поместим все обьекты оговоренные в условии(m раскрывающихся списков, n полей ввода,...).
В макросе отвечающем за событие кнопки «Добавление» введем процедуру которая будет активизировать форму UserForm1, и заносить все данные из окна ввода в ячейки листа A4:L4, A5:L5 и т.д.
По нажатию кнопки “OK” выполнится следующий код программы:
Окно ввода выглядит следующим образом:
СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ
1. А.Гарнаев. Использование MS Excel и VBA в экономике и финансах
2. С. Браун, Visual Basic 5.0 с самого начала, Москва 1999, издательство “Питер”
3. Microsoft Visual Basic – on-Line HELP
ПРИЛОЖЕНИЕ 1