игральные кубики

Надстройка "Моделирование Монте-Карло"

Метод Монте - Карло придуман почти одновременно с компьютерами. С тех пор он стал популярным инструментом при изучении случайных процессов.
Суть метода: случайный процесс описывается математической моделью с использованием генератора случайных чисел, затем производится многократное моделирование, после чего на основе полученных данных вычисляются вероятностные характеристики рассматриваемого процесса.
Метод используется для оценки результатов воздействия на выбранные параметры одного или нескольких случайных факторов, прекрасно демонстрирует тенденции и позволяет оценить среднеожидаемый результат, характерный разброс результатов и риски наступления тех или иных событий. Характерная для метода точность оценки (средне стандартное отклонение 1-2% при быстрой оценке) вполе достаточна для решения практических вопросов управления. Для использования метода Монте-Карло пользователь может не обладать высокой квалификацией, поскольку сам метод позволяет легко получить верный результат даже в сложных случаях. А надстройка "Моделирование Монте - Карло" без проблем устанавливается на любой компьютер, на котором уже установлен MS Excel. Примеры использования надстройки можно посмотреть здесь.

Описание надстройки "Моделирование Монте-Карло"

После запуска файла Monte Carlo 20.12.xlam в MS Excel появится панель управления надстройкой:

Панель надстройки Моделирование Монте-Карло

Блок "Задать цели"

Здесь необходимо указать ячейки, которые содержат формулы, зависящие от генераторов случайных чисел. Это может быть стандартный генератор Excel =СЛЧИС( ), или генераторы из данной надстройки, или другие генераторы случайных чисел, доступные для страницы Excel.
Целевые ячейки можно вводить, указывая целый диапазон, напр. A1:D1, или несвязанные ячейки, напр. A1; D8; F11. Для ввода целевых ячеек выделите нужные ячеки на рабочем листе Excel и щелкните по значку справа от поля Целевые ячейки. После этого нужные ячейки появятся в поле.

Your browser does not support the HTML5 canvas tag.

Необязательное поле. В это поле вносят адреса ячеек, содержащие комментарии к целевым ячейкам. Если указать адреса ячеек, то эти комментарии будут использованы на диаграммах и в таблицах в файле результатов. Если это поле оставить пустым, то в качестве подписей будут использованы только адреса ячеек.

Это поле показывает, сколько раз будет проведено моделирование для оценки среднего значения целевых ячеек. По умолчанию задано значение 10 тыс. реализаций, но это число неограничено. Однако, для большого числа реализаций (1 млн. и более) время моделирования может оказаться значительным (десятки минут и часы). Начать лучше с 10 тысяч испытаний, а если понадобится уточнить результат, можно по времени счета, указанному на странице "Итоги", оценить, сколько будет продолжаться расчет при увеличении статистики в 100 и более раз.

Блок "Показывать"

Для поиска минимума или максимума целевых ячеек можно использовать моделирование с параметром, для этого нужно отметить чекбокс "Задать переменную решения", и на панели надстройки появится блок "Задать переменную":

Блок Задать переменную надстройки Моделирование Монте-Карло

Блок "Задать переменную"

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

Your browser does not support the HTML5 canvas tag.

В поле … из диапазона ячеек следует указать список ячеек, содержащих значения параметра, которые принимает переменная при моделировании. Список может быть любым. Однако задавать больше 10-15 значений нерационально. Разумная тактика состоит в том, чтобы сначала определить, имеется ли максимум (минимум) на предполагаемом интервале. Поэтому сначала лучше взять короткий список значений параметра (5-7 значений) и сделать расчет при статистике по умолчанию (10 000). Если экстремума на данном интервале нет, то исправить значения параметра и повторить моделирование. При обнаружении примерного значения параметра, отвечающего экстремуму целевой ячейки, можно скорректировать и диапазон вариации параметра и статистику и выполниь более длительное моделирование уже с уверенностью, что нужный результат будет получен.

Блок "Разное"

Блок разное надстройки Монте-Карло
    В этом блоке можно:
  • переключить язык интерфейса и вывода данных;
  • вывести справку по надстройке на отдельный лист в рабочей книге MS Excel;
  • очистить установленные параметры, тогда ячейки примут значения по умолчанию;
  • исправить ячейки, содержащие функции генераторов случайных чисел с помощью команды Исправить.

Команда Исправить предназначена для исправления ячеек, содержащих функции генераторов случайных чисел. Так как функции генераторов случайых чисел не являются стандартными функциями MS Excel, то при сохранении, вместо короткого имени вроде "fmc_Normal(...)", будет записывать еще и полный адрес книги, из которой взяты данные функции. На другом компьютере или при не запущенной надстройке "Monte Carlo" ячейки будут показаны с ошибкой "#ИМЯ\NAME". Если после запуска надстройки формулы в ячейках не поправились автоматически, следует щелкнуть кнопку "Исправить" и все ссылки на генераторы случайных чисел автоматически исправятся во всей книге Excel.

Запуск надстройки "Монте-Карло"

После того, как все значения установлены, запустите надстройку, нажав:

Кнопка запуска надстройки Моделирование Монте-Карло
Замечания
  1. Так как при моделировании используется команда Пересчитать, то Excel много раз пересчитывает ВСЕ ОТКРЫТЫЕ книги. Чтобы ускорить расчет лучше оставить открытой только книгу с построенной задачей для моделирования.
  2. Надстройка позволяет вводить данные только с одной и той же страницы книги Excel. Если удобнее организовывать данные так, чтобы они располагались на нескольких страницах, то можно использовать дополнительную общую страницу, на которую вынести ссылки на все нужные целевые и прочие ячейки, расположенные на других страницах книги.
Скачать надстройку "Моделирование Монте-Карло"
Скачать надстройку Скачать надстройку "Моделирование Монте - Карло" (примерно 200 кб)
Основная версия надстройки работает в 32-bit и 64-bit версиях MS Excel начиная с MS Office 2007. Более старые версии MS Excel не поддерживаются.
Файл надстройки имеет название "Monte Carlo xx.xx.xlam". Его можно запустить как обычный файл MS Excel (при этом нужно согласиться с запуском макросов надстройки), либо подключить как стандартную надстройку MS Excel.
О том, как подключить настройку, читайте здесь.