Задание 2. Генерация случайных чисел
Необходимо создать модель потока заказов мелкооптового магазина - сгенерировать количество заказов за день. Сотрудники магазина ежедневно обзванивают около 200 клиентов, что дает, в среднем, 20 заказов в день.
Число заказов колеблется в интервале от 10 до 30 и равномерно распределяется в этом интервале. Для генерирования последовательности заказов:
На рабочем листе определитесь, куда можно будет поместить последовательность.
Выполните команду Сервис / Анализ данных. Появится диалоговое окно Анализ данных (рис. 2.15).
В списке выделите Генерация случайных чисел. Нажмите кнопку ОК. Появится диалоговое окно Генерация случайных чисел
В списке распределение выберите элемент Равномерное распределение.
Рис. 2. 18. Задание параметров для генерации случайных чисел для равномерного распределения
В поле Число переменных введите 1 (равное количеству столбцов, которые будут заполнены последовательностью).
В поле Число случайных чисел введите 30, что соответствует периоду в один месяц.
В поля Между и введите интервал распределения количества заказов в день =10 и 30.
Если достаточно одной последовательности, то поле Случайное рассеивание оставьте пустым.
В полях группы Параметры вывода щелкните на Выходной интервал и укажите ссылку на первую ячейку диапазона вывода данных - А3.
Нажмите кнопку ОК.
Полученные данные округлите до целых чисел.
Excel создаст в столбце последовательность случайных чисел. Как на ее основе построить гистограмму и получить таблицу частот рассмотрим далее. Присвойте листу имя Равном распред.
Другие распределения, возможно, будут лучше моделировать реальную ситуацию. Очень многие модели, построенные с помощью нормального распределения, хорошо соответствуют действительности.
Чтобы построить последовательность значений нормально распределенной случайной величины, необходимо задать математическое ожидание (Среднее, например, =20) и дисперсию (Стандартное отклонение, например, = 5).
Переименуйте имеющийся или создайте новый рабочий лист и присвойте ему имя Норм распред. Далее выполните операции как для равномерного распределения (рис. 2.16).
Рис. 2. 19. Задание параметров для генерации случайных чисел для нормального распределения
Число переменных = 1.
Число случайн. чисел = 30.
Распределение = Нормальное.
Среднее = 20.
Стандартное отклонен = 5.
Выходной интервал = $A$3.
Полученные данные округлите до целых чисел.
В случае моделирования заказов больше подойдет биноминальное распределение, описывающее процесс случайных испытаний с двумя исходами.
Каждый день мелкооптовый магазин проводит 200 испытаний (делает звонки), при этом 10% испытаний заканчивается успехом (10% покупок). Чтобы сгенерировать последовательность из 30 случайных чисел с биноминальным распределением и вероятностью успеха 0,1:
Переименуйте имеющийся или создайте новый рабочий лист и присвойте ему имя Бином распределение.
Рис. 2. 20. Задание параметров для генерации случайных чисел для биноминального распределения
Далее выполните операции как для равномерного распределения.
Число переменных = 1.
Число случайных чисел = 30.
Распределение = Биноминальное.
Значение Р= 10%.
Число испытаний = 200.
Выходной интервал = $A$3.
Создание гистограмм (частотных распределений)
Продолжение задания 2
Гистограммы (частотные распределения) служат для оценивания распределения этой случайной величины. Чтобы по случайной выборке (для любого распределения) построить гистограмму:
На листе Равном распред разбейте промежуток, в который попадает случайная величина, на интервалы от 12 до 30 с шагом 2, в диапазоне В3:В12 (рис. 2.18).
Рис. 2. 21. Задание диапазона интервалов
Скопируйте ячейки В2:В12 на листы Норм распред и Бином распред.
Вернитесь на лист Равном распред.
Выделите команду Сервис / Анализ данных.
В списке Инструменты анализа выберите пункт Гистограмма, нажмите кнопку ОК. Появится окно Гистограмма (рис. 2.19).
Рис. 2. 22. Задание параметров для создания гистограммы
В поле Входной интервал введите $A3:$A32 (вручную или с помощью мыши).
В поле Интервал карманов введите $В$3:$В$12.
Выберите переключатель Выходной интервал и введите ссылку на левую верхнюю ячейку -С2.
Установите флажок Вывод графика.
Остальные поля и флажки оставьте без изменения и нажмите ОК.
Может появиться следующее сообщение, показанное на рис. 2.20, но смело щелкайте на кнопке ОК.
Рис. 2. 23. Указание на перезапись данных
В результате вы увидите следующие изменения рабочего листа и диаграмму (рис. 2.21).
Рис. 2. 24. Результат создания диаграммы частотного распределения на листе Равном распред
Внимание. Если в поле Интервал карманов ничего не указывать, то будет создан равномерно распределенный диапазон.
Флажки в диалоговом окне Гистограмма предоставляют дополнительные возможности:
Парето - создается дополнительная копия результатов, где интервалы разбиения отсортированы по возрастанию количества значений случайной величины, попавших в интервал.
Интегральный процент - результаты будут содержать дополнительный столбец, отражающий процент попаданий в каждый интервал разбиения.
Перейдите на лист Норм распред.
Создайте гистограмму с включением всех трех флажков.
Полученные статистические результаты и диаграмма и (после ее форматирования) приведена на рис. 2.22.
Рис. 2. 25. Результат создания диаграммы частотного распределения на листе Норм распределение со всеми включенными флажками
Перейдите на лист Бином распределение и постройте диаграмму с включенным флажком Парето или Интегральный процент (по вашему желанию – рис. 2.23).
Рис. 2. 26. Результат создания диаграммы частотного распределения на листе Бином распределение с включенным флажком Парето
Продолжение задания 2. Получение связанных данных
Описанный способ очень прост в применении, но получаемые при этом результаты автоматически не связаны с исходными данными. Для получения связанных данных:
Создайте новый лист Частота.
Выполните копирование в него интервала ячеек А1:В32 с листа Бином распределение.
Выделите диапазон ячеек А2:А32.
Выполните команду Вставка / Имя / Создать.
Установите флажок В строке выше. Щелкните на кнопке ОК.
Создайте имя для диапазона В2:В12.
Для вставки функции в ячейку С2 введите Частота, выделите диапазон С3:С12.
Выполните команду Вставка / Функция.
В списке категорий функций выберите Статистические (рис. 2.24).
Внимание. Более подробно о статистических функциях будет рассказано в третьей главе.
В списке имен функций выделите Частота.
Рис. 2. 27. Выбор функции ЧАСТОТА
Щелкните по кнопке ОК. В строке формул появится имя функции ЧАСТОТА с текстовыми полями.
В поле Массив_данных введите имя диапазона данных. Выделите команду Вставка / Имя / Вставить / Данные. Щелкните по кнопке ОК. Если данная процедура не получается, то просто с помощью мышки выделите область ячеек, где находятся данные А3:А32.
Рис. 2. 28. Окно задания параметров функции ЧАСТОТА
В поле Массив_интервалов введите имя диапазона данных. Выделите команду Вставка / Имя / Вставить / Интервалы. Нажмите кнопку ОК. Если данная процедура не получается, то просто с помощью мышки выделите область ячеек, где находятся интервалы В3:В12
Нажмите сочетание клавиш Shift + Ctrl + Enter, чтобы функция была введена как массив.
Диапазон результатов будет заполнен значениями функции Частота. Полученные значения будут связаны с исходными данными.
Полученные частоты совпадают с теми, что были вычислены на листе Бином распред.
К сожалению, диаграмму частотного распределения вам придется построить самим:
Выделите интервал В2:С12.
Нажмите кнопку Мастер диаграмм.
В окне Типы диаграмм выберите Гисторграммы и вид Обычные. Нажмите кнопку Далее.
Полученная диаграмма не совсем верна, так как в нее включены столбцы Интервалы В2:В12. Для его удаления из диаграммы перейдите на вкладку Ряд. В окне Ряд выделите Интервал и нажмите кнопку Удалить.
В поле Подписи по оси Х вставьте координаты области В3:В12. Нажмите кнопку Далее (рис. 2.26).
Рис. 2. 29. Задание областей исходных данных Мастера диаграмм
В третьем диалоговом окне Мастера диаграмм в поле Название диаграммы введите текст: «Гистограмма распределения частот, полученная с помощью функции Частота». В поле Оси Х (категорий) - текст: «Карман», а в поле Оси У - «Частота».
Щелкните на вкладке Легенда и снимите флажок Добавить легенду. Нажмите кнопку Далее.
Нажмите кнопку Готово в четвертом диалогом окне.
Переместите диаграмму и измените ее размеры.
Щелкните правой кнопкой мыши в Области диаграммы и выберите команду Формат области построения диаграммы.
Выберите заливку белого цвета. Нажмите кнопку ОК.
Результат приведен на рис. 2.27.
Рис. 2. 30. Результат вычислений функции ЧАСТОТА и график данной функции