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

Надстройка "Моделирование Монте-Карло"
• реализует сбор и обработку информации в книге MS Excel при статистическом моделировании по методу Монте-Карло;
• выдает результаты в численной и графической форме на отдельном листе рабочей книги Excel;
• содержит наиболее употребительные генераторы случайных чисел.

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

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

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

Блок "Показывать" определяет, какую информацию нужно выводить после окончания работы надстройки. Настройки блока позволяют изменить параметры диаграмм:
• отказаться от вывода кумулят;
• отказаться от данных для построения диаграмм;
• отказаться от вывода самих диаграмм (это бывает необходимо, если задано много целевых ячеек и значений параметра. Вряд ли при задании трех целевых ячеек и 10 значений параметра кто-то захочет рассматривать 30 диаграмм.)
• выводить кумуляты, но не запрашивать частотных распределений и снять галочку только с пункта "Гистограммы";
• выводить все гистограммы в одном масштабе. При этом все диаграммы для заданной целевой ячейки будут показаны на одних и тех же отрезках осей координат. Для разных целевых ячеек масштабы, все равно будут разными.
• задать число разрядов в гистограммах, если не устраивает значение по умолчанию. Число разрядов частотных диаграмм по умлчанию устанавливается равным 50 (25 при статистике меньше 3 000).

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

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

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

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

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

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

Замечания

Скачать надстройку "Моделирование Монте-Карло"

Скачать надстройку "Моделирование Монте - Карло" (примерно 200 кб)