Курсовая работа: Решение экономических задач с помощью VBA
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 млн.