Реферат: Решение экономических задач с помощью VBA
В ячейке 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 |