Xreferat.com » Рефераты по информатике и программированию » Решение экономических задач с помощью VBA

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

(час)

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

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

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


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


ПРОГРАММА НА ЯЗЫКЕ MICROSOFT VISUAL BASIC


Модуль 1:

Sub Return_To_MainMenu()

Worksheets("Содержание").Activate

End Sub


Модуль 2:

Sub Task1()

Worksheets("Задание1").Activate

End Sub

Sub Task2()

Worksheets("Задание2").Activate

End Sub

Sub Task3()

Worksheets("Задание3").Activate

End Sub

Sub Task4()

Worksheets("Задание4").Activate

End Sub

Sub Task1_Evrica()

Dim mas1(3) As Integer

Dim mas2(3) As Integer

Dim Mas_I1(3) As Integer

B = Worksheets("Задание1").Range("B11").Value

c = Worksheets("Задание1").Range("C11").Value

D = Worksheets("Задание1").Range("D11").Value

mas1(1) = B

mas1(2) = c

mas1(3) = D

i = 1

l = 0

Do

k = mas1(i)

''''' Занесение в массив Mas2 эл-тов >1490

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

i = i + 1

Loop Until i = 4


Max = -1

i = 0

Do

i = i + 1

If mas2(i) > Max Then

Max = mas2(i)

indm = i

End If

Loop Until i = 3

Worksheets("Задание1").Cells(12, indm + 1).Value = Max * 0.02 + Max * 0.04

'Worksheets("Задание1").Range("f15").Value = r

'GoTo l

''''' Находим MAx эл-т из оставшихся,

''''' и запоминаем его индеск

Max = -1

i = 0

Do

i = i + 1

If i <> indm And mas2(i) > Max Then

Max = mas2(i)

indm2 = i

Если Вам нужна помощь с академической работой (курсовая, контрольная, диплом, реферат и т.д.), обратитесь к нашим специалистам. Более 90000 специалистов готовы Вам помочь.
Бесплатные корректировки и доработки. Бесплатная оценка стоимости работы.

Поможем написать работу на аналогичную тему

Получить выполненную работу или консультацию специалиста по вашему учебному проекту
Нужна помощь в написании работы?
Мы - биржа профессиональных авторов (преподавателей и доцентов вузов). Пишем статьи РИНЦ, ВАК, Scopus. Помогаем в публикации. Правки вносим бесплатно.

Похожие рефераты: