Решение экономических задач с помощью VBA (стр. 3 из 4)
В ячейке 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” выполнится следующий код программы:
Окно ввода выглядит следующим образом:
