Генерирование случайных чисел в Excel.

Получить набор случайных чисел в среде табличного процессора можно несколькими способами. Первый способ заключается в последовательной генерации равномерно распределенной случайной величины в диапазоне от 0 до 100 с последующим преобразованием в дискретно распределенную случайную величину с параметрами, заданными таблицей (например, таблицей 2). Второй способ использует процедуру генерации команды Анализ данных пункта Сервис главного меню. Рассмотрим более подробно эти способы.

Способ 1. Разместим на рабочем листе дополнительную таблицу, задающую параметры дискретного распределения (рис.3). Верхняя строка этой таблицы будет содержать значения генерируемой величины, а нижняя будет определять верхнюю границу частотного диапазона.

	А	В	С	D	E
	Объем
	Верхн
	Час	Случ. чис.	Объем

Рис.3.

Поместим в ячейки В5 иС5 следующие формулы:

В5: = СЛЧИС()*100 или =СЛУЧМЕЖДУ(0;100)

С5: = ЕСЛИ(В5<=$B$2;$B$1;ЕСЛИ(B5<=$C$2;$C$1;ЕСЛИ(B5<=$D$2;$D$1;$E$1)

Скопируем эти формулы на требуемое число строк (соответствующее длительности рабочего дня в данном примере). На рис.3 приведен пример одной реализации случайного процесса. Обновление данных происходит при нажатии клавиши F9.

Способ 2.

А) На рабочем листе формируют два столбца данных, задающих параметры распределения. В левом столбце размещают значения моделируемой переменной, а в правом – вероятности их появления (соответствует таблице 1). Необходимо следить, чтобы сумма вероятностей равнялась единице.

Б) Далее в пункте меню Сервис выполняют команду Анализ данных. В открывшемся окне соответствующего диалога выбирают инструмент Генерация случайных чисел. В поля открывшегося диалогового окна вводят следующие параметры:

§ Число переменных – количество столбцов, в которые необходимо разместить случайные числа (в рассматриваемом промере – 1);

§ Число случайных чисел – количество чисел в каждом столбце;

§ Распределение – закон распределения (в нашем примере – дискретное);

§ Входной интервал значений и вероятностей: - ссылка на область, описанную в п.А.

Далее определяется область вывода сгенерированных случайных чисел, задаваемая положением верхней левой ячейки выходного блока. Однако для генерации новой последовательности чисел данным способом потребуется написание специального макроса и привязка его к какому-либо элементу управления.

Когда в ходе моделирования значения получены, далее их можно исполь­зовать при анализе переменных, например нормы выработки, требуемых склад­ских площадей, скорости проведения упаковочных работ и требуемых средств перевозки. Такого рода примеры будут рассмотрены далее.Метод использования случайных чисел для моделирования заданной ситу­ации (описанной с точки зрения вероятности) можно использовать в различ­ных случаях при анализе задач и альтернативных решений.

5. Моделирование спроса

Рассмотрим пример, связанный с хранением электротоваров на складе. В таблице ниже показан спрос на некую модель телевизора:

Таблица 3
Ежедневный спрос
Кол-во проданных телевизоров
Процентная частота

С помощью случайных чисел мы можем смоделировать спрос на эти теле­визоры, исходя из ранее наблюдаемой процентной частоты.

Как и в предыдущим примере, можно взять двузначные случайные числа. Первые 10% случайных чисел (00—09) показывают нулевой спрос, следую­щие 22% — спрос на 1 телевизор и т. д. В таблице ниже показаны случайные числа, которые будут использоваться при моделировании спроса на телеви­зоры:

Таблица 4
Ежедневный спрос
Кол-во телевизоров:
Случайные числа:	00 - 09	10-31	32 - 68	69 - 96	97-99

С помощью таблицы случайных чисел можно смоделировать спрос на те­левизоры в течение определенного периода. В таблице показана модель спроса в течение 15 дней:

		А	В	С
	День	Случ. числа	Спрос

Рис.4.

Модель спроса, представленная в этой таблице, может быть использована при определении требуемых складских площадей и разработке политики размещения заказов на конкретные товары с целью оптимизации критических факторов успеха, в частности затрат, рентабельности или объема выручки от peaлизации.

Дата добавления: 2015-09-03