Генерирование распределений случайных величин в Excel - пользовательские функции

В статье приведены примеры кода Excel-VBA, задающие пользовательские функции для генерирования случайных величин с нужным распределением. Также разобраны встроенные средства для работы с распределениями.

Нормальное распределение

В Excel достаточно удобно работать с нормальным распределением с помощью формул НОРМ.РАСП (NORM.DIST) и НОРМ.ОБР (NORM.INV). Первая функция позволяет считать доверительные интервалы, а вторая - генерировать нормальные распределения с произвольным мат. ожиданием и стандартным отклонением.

Скачать пример в Excel

Треугольное распределение

Как сгенерировать в 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).