Excel_k (Решение экономических задач с помощью VBA), страница 3

Составляем таблицу состоящую из обьема реализации, числа событий, и вероятности этих событий, первые два нам даны по условию а вероятность этих событий нужно посчитать. Вводим в ячейку 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 выводит эти оптимальные значения в окне сообщений.

Ф-ция наибольший возвращает К-е наибольшее значение из множества данных . Эта ф-ция используется для того чтобы выбрать значение по его относительному местоположению. Например, фунуцию НАИБОЛЬШИЙ можно использовать для того чтобы определить наилучший, второй, третий результат в балах, показанный при тестировании. Систаксис программы такой:

НАИБОЛЬШИЙ(массив;К) где Массив – это массив или диапазон ячеек где определяется наибольшее значение, к – позиция (начиная с наибольшей) в массиве или диапазоне.

Все результаты занесенные в таблицу будут выглядеть следующим образом:

2.3.5 Определение оптимальных капиталовложений

Создаём исходную таблицу и заполняем ее мат. ожиданиями прибылей в состветствии с условием.

Для дальнейшего решения задачи, вводим следующие обозначения:

Пусть 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 и будет выглядеть следующим образом:

В программе переменной К – присваиваем значение равное обьему капиталовложений. В массив R с рабочего листа капиталовложения вводим ожидаемую прибыль , распределенную по филиалам.

В диапазон ячеек (B14:K22) выводится оптимальное распределение капиталовложений по филиалам. После вычислений можно увидеть что максимальныя ожидаемая прибыль составляет 1,01 млн. грв. , из таблицы видны следующие рез-ты:

6 филиал – 2 млн.

5 филиал – 1 млн.

4 филиал – 1 млн.

3 филиал – 1 млн.

2 филиал – 1 млн.

1 филиал – 1 млн.

Сама таблица выглядит следующим образом:

2.3.6 Задание на нахождение оптимального раскроя