НАИБОЛЬШИЙ(массив;К) где Массив – это массив или диапазон ячеек где определяется наибольшее значение, к – позиция (начиная с наибольшей) в массиве или диапазоне.
Все результаты занесенные в таблицу будут выглядеть следующим образом:
| | |П р о | | | | | | |д а ж | | | | | | |а | | | | |П | |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р.| | | |Максимальная | | | |18 | | | | |прибыль |16,88| | | |0,28р.| | | | |р. | | | | | | | |Оптимальный |15 | | | | | | | |обьем | | | | | |
2.3.5 Определение оптимальных капиталовложений
Создаём исходную таблицу и заполняем ее мат. ожиданиями прибылей в состветствии с условием.
| |Ф и л | | | |и а л | | | |ы | | |Млн.|1 |2 |3 |4 |5 |6 | |грв | | | | | | | |0 |0 |0 |0 |0 |0 |0 | |1 |0,|0,1|0,1|0,2|0,1|0,1| | |11|2 |8 | |7 |2 | |2 |0,|0,1|0,1|0,2|0,1|0,2| | |11|3 |8 |2 |7 |3 | |3 |0,|0,1|0,1|0,2|0,1|0,2| | |12|3 |9 |4 |8 |4 | |4 |0,|0,1|0,1|0,2|0,1|0,2| | |12|3 |9 |6 |8 |4 | |5 |0,|0,1|0,2|0,2|0,1|0,2| | |13|3 | |9 |9 |5 | |6 |0,|0,1|0,2|0,3|0,1|0,2| | |13|3 | |1 |9 |5 | |7 |0,|0,1|0,2|0,3|0,2|0,2| | |14|3 | |3 | |6 |
Для дальнейшего решения задачи, вводим следующие обозначения:
Пусть 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 и |1,2|1,2,3|1,2,3|1,2,3,| |2 |и 3|и 4 |,4 и |4,5 и | | | | |5 |6 | |0 |0 |0 |0 |0 | |0,12|0,1|0,2 |0,2 |0,2 | | |8 | | | | |0,23|0,3|0,38 |0,38 |0,38 | |0,24|0,4|0,5 |0,55 |0,55 | | |1 | | | | |0,24|0,4|0,61 |0,67 |0,67 | | |2 | | | | |0,25|0,4|0,63 |0,78 |0,79 | | |2 | | | | |0,25|0,4|0,65 |0,8 |0,9 | | |3 | | | | |0,26|0,4|0,67 |0,82 |1,01 | | |3 | | | |
В программе переменной К – присваиваем значение равное обьему капиталовложений. В массив 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|4 |1,2,|5 |1,2,3|6 | |грв.| | | | |,3 | |3 и | |,4 и | | | | | | | | | |4 | |5 | |
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|35|38|Отходы | | | |0 |0 | | |220|210|35|38|49,99996 | | | |0 |0 | |
2.3.7 База данных
Создадим поля базы данных, и занесем их в таблицу. База данных будет заполняться программой, программе не требуются названия полей, но для облегчения ориентации в первой строке введем данные соответствующие полям БД
Создадим кнопку “Добавление” для добавления записей в БД, делается это так: Вызываем панель инструментов на которой расположены примитивы, т.е. окна ввода, кнопки и т.д. Создаем на форме кнопку, и спомощью св-ва Caption присваиваем ей название “Добавление”
Создадим макрос который будет отвечать за обработку событий по нажатию этой кнопки. Перейдем в среду Visual Basic for Application и в меню «Вставка» выберем UserForm, на эту форму и поместим все обьекты оговоренные в условии(m раскрывающихся списков, n полей ввода, ...).
В макросе отвечающем за событие кнопки «Добавление» введем процедуру которая будет активизировать форму UserForm1, и заносить все данные из окна ввода в ячейки листа A4:L4, A5:L5 и т.д. По нажатию кнопки “OK” выполнится следующий код программы:
Окно ввода выглядит следующим образом:
[pic]
СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ
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
End If Loop Until i = 3 Worksheets("Задание1").Cells(12, indm2 + 1).Value = Max * 0.02 + Max * 0.02 ''''' Находим MAx эл-т из оставшихся, ''''' и запоминаем его индеск Max = -1 i = 0 Do i = i + 1
Страницы: 1, 2, 3, 4
