В статье приведены примеры кода Excel-VBA, задающие пользовательские функции для генерирования случайных величин с нужным распределением. Также разобраны встроенные средства для работы с распределениями.
Нормальное распределение
В Excel достаточно удобно работать с нормальным распределением с помощью формул НОРМ.РАСП (NORM.DIST) и НОРМ.ОБР (NORM.INV). Первая функция позволяет считать доверительные интервалы, а вторая - генерировать нормальные распределения с произвольным мат. ожиданием и стандартным отклонением.
Треугольное распределение
Как сгенерировать в Excel
Первый пример - треугольное распределение. В Excel отсутствует функция для работы с треугольным распределением, но его можно получить из простого равномерного распределения с помощью данной пользовательской функции:
Function TRIDIST(random As Double, min As Double, max As Double, mean As Double)
If mean < min Or max < mean Then
TRIDIST = CVErr(xlErrValue)
Else
If random <= (mean - min) / (max - min) Then
TRIDIST = min + Sqr((max - min) * (mean - min) * random)
Else
TRIDIST = max - Sqr((max - min) * (max - mean) * (1 - random))
End If
End If
End Function
После добавления данного кода в Excel появится возможность написать формулу =TRDIST(random,min,max,mean)
Первый аргумент - random - случайная величина распределенная равномерно от 0 до 1. (функция СЛЧИС() либо СЛЧИСМЕЖДУ(0,1)).
Второй и третий аргументы - min. max - минимум и максимум функции распределения.
Третий аргумент - mean - мат. ожидание.
Таким образом данная функция позволяет работать как с симметричными так и с асимметричными треугольными распределениями.
В каких случаях применяется
При моделировании случайных процессов чаще всего используется нормальное или log-нормальное распределения, однако в некоторых случаях оправдано использование треугольного распределения. Один из примеров - вариативность случайной величины строго ограничена определённым диапазоном. Когда такое бывает? Допустим, что мы строим модель DCF для оценки денежного потока компании и для симуляции монте-карло нам необходимо задать распределение EBIT margin. Очевидно, что в теории данная величина может принимать значения от -1 до 1, но на практике для большинства здоровых компаний она находится в диапазоне от 5% до 50% и здесь-то нам и может помочь треугольное распределение и пошльзовательская функция TRDIST.
Пример работы функции:
В результате мы получили асимметричное треугольное распределение с мат. ожиданием 0.2, минимумом 0,05 и максимумом 0,25 (стандартное отклонение оказалось равным 0,041344).