Благоустройство и ремонт
679 0

Как сделать генератор случайных чисел в excel. Случайное число в Excel

Доброго времени суток, уважаемый, читатель!

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

Итак, для чего же собственно мы можем использовать этом механизм:

  • во-первых : мы можем для тестировки формул, заполнить нужный нам диапазон случайными числами;
  • во-вторых : для формирования вопросов различных тестов;
  • в-третьих : для любого случайно распределения заранее задач между вашими сотрудниками;
  • в-четвёртых : для симуляции разнообразнейших процессов;

…… да и во многих других ситуациях!

В этой статье я рассмотрю только 3 варианта создания генератора (возможности макроса, я не буду описывать), а именно:

Создаём генератор случайных чисел с помощью функции СЛЧИС

С помощью функции СЛЧИС, мы имеем возможность генерировать любое случайное число в диапазоне от 0 до 1 и эта функция будет выглядеть так:

=СЛЧИС();

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

=СЛЧИС()*100; А вот если вам не нравятся дробные числа или просто нужно использовать целые числа, тогда используйте такую комбинацию функций, это позволит вам после запятой или просто отбросить их:

=ОКРУГЛ((СЛЧИС()*100);0);

=ОТБР((СЛЧИС()*100);0) Когда возникает необходимость использовать генератор случайных чисел в каком-то определённом, конкретном диапазоне, согласно нашим условиям, к примеру, от 1 до 6 надо использовать следующую конструкцию (обязательно закрепите ячейки с помощью ):

=СЛЧИС()*(b-а)+а , где,

  • a – представляет нижнюю границу,
  • b – верхний предел

и полная формула будет выглядеть: =СЛЧИС()*(6-1)+1 , а без дробных частей вам нужно написать: =ОТБР(СЛЧИС()*(6-1)+1;0)

Создаём генератор случайных чисел с помощью функции СЛУЧМЕЖДУ

Эта функция более проста и начала нас радовать в базовой комплектации Excel, после 2007 версии, что значительно облегчило работу с генератором, когда необходимо использовать диапазон. К примеру, для генерации случайного числа в диапазоне от 20 до 50 мы будем использовать конструкцию следующего вида:

=СЛУЧМЕЖДУ(20;50).

Создаём генератор с помощью надстройки AnalysisToolPack

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

Для получения доступа к этой, бесспорно, полезной надстройки, нужно, для начала, с помощью диалогового окна«Надстройки» установить этот пакет. Если у вас он уже установлен, то дело за малым, выбираете пункт меню«Данные» – «Анализ» – «Анализ данных» , выбираете в предложенном программой списке и жмём«ОК» .

В открывшемся окне мы выбираем тип в меню«Распределение» , после указываем дополнительные параметры, которые изменяются, исходя с типа распределения. Ну и финальный шаг, это указание«Выходной интервал» , именно тот интервал где будут храниться, ваши случайные числа.

А на этом у меня всё! Я очень надеюсь, что вопрос по созданию генератора случайных чисел я раскрыл полностью и вам всё понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не додумывай слишком много. Так ты создаешь проблемы, которых изначально не было.

Фридрих Ницше

ФункцияСЛЧИС () возвращает равномерно распределенное случайное число x, где 0 £ x < 1. Вместе с тем путем несложных преобразований с помощью функцииСЛЧИС () можно получить любое случайное вещественное число. Например, чтобы получить случайное число между a и b , достаточно задать в любой ячейке таблицы Excel следующую формулу:=СЛЧИС()*(b -a )+a .

Заметим, что начиная с Excel 2003 функцияСЛЧИС () была улучшена. Теперь она реализует алгоритм Вичмана-Хилла, который проходит все стандартные тесты на случайность и гарантирует, что повторение в комбинации случайных чисел начнётся не ранее, чем через 10 13 генерируемых чисел.

Генератор случайных чисел в STATISTICA

Для генерации случайных чисел в STATISTICA надо дважды щелкнуть в таблице данных (в которой предполагается записать сгенерированные числа) на имени переменной. В окне спецификации переменной нажмите кнопкуFunctions . В открывшемся окне (рис. 1.17) надо выделитьMath и выбрать функциюRnd .

RND (X ) - генерация равномерно распределенных чисел. Эта функция имеет только один параметр -X , который задает правую границу интервала, содержащего случайные числа. При этом 0 является левой границей. Чтобы вписать общий вид функцииRND (X ) в окно спецификации переменной, достаточно дважды щелкнуть на имени функции в окнеFunction Browser . После указания числового значения параметраX надо нажатьОК . Программа выдаст сообщение о правильности написания функции и запросит подтверждение о пересчете значения переменной. После подтверждения соответствующий столбец заполняется случайными числами.

Задание для самостоятельной работы

1. Сгенерировать ряды из 10, 25, 50, 100 случайных чисел.

2. Вычислить описательные статистики

3. Построить гистограммы.

Какие выводы можно сделать относительно вида распределения? Будет ли оно равномерным? Как влияет количество наблюдений на данный вывод?

Занятие 2

Вероятность. Моделирование полной группы событий

Лабораторная работа № 1

Лабораторная работа представляет собой самостоятельное исследование с последующей защитой.

Цели занятия

Формирование навыков стохастического моделирования .

Уяснение сущности и связи понятий «вероятность», «относительная частота», «статистическое определение вероятности» .

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

- Формирование навыков исследования явлений, имеющих вероятностную природу.

Наблюдаемые нами со­бытия (явления) можно подразделить на следующие три вида: достоверные, невозможные и случайные.

Достоверным называют событие, которое обязательно произойдет, если будет осуществлена определенная со­вокупность условий S .

Невозможным называют событие, которое заведомо не произойдет, если будет осуществлена совокупность усло­вий S .

Случайным называют событие, которое при осущест­влении совокупности условий S может либо произойти, либо не произойти.

Предметом теории вероятностей является изу­чение вероятностных закономерностей массовых однород­ных случайных событий.

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

Несколько событий образуютполную группу , если в результате испытания появится хотя бы одно из них. Другими словами, появление хотя бы одного из событий полной группы есть достоверное событие.

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

Каждый из равновозможных результатов испытания называетсяэлементарным исходом .

Классическое определение вероятности: вероятностью события А называют отношение числа благоприятствующих этому событию исходов к общему числу всех равновозможных несовместных элементарных исходов, образующих полную группу.

А определяется формулой,

где m – число элементарных исходов, благоприятствую­щих событию А , n – число всех возможных элементарных исходов испытания.

Одним из недостатков классического определения вероятности является то, что оно неприменимо к испытаниям с бесконечным числом исходов.

Геометрическое определение вероятности обобщает классическое на случай бесконечного числа элементарных исходов и представляет собой вероятность попадания точки в область (отрезок, часть плоскости и т.д.).

Таким образом, вероятность события А определяется формулой, где – мера множества A (длина, площадь, объем); – мера пространства элементарных событий.

Относительная частота, наряду с вероятностью, при­надлежит к основным понятиям теории вероятностей.

Относительной частотой события называют отношение числа испытаний, в которых событие появилось, к общему числу фактически произведенных испытаний.

Таким образом, относительная частота события А определяется формулой, где m – число появлений события, n – общее число испытаний.

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

1. Моделирование случайного события, имеющего вероятность p.

Генерируется случайное число y y ≤ p , то событие A наступило.

2. Моделирование полной группы событий.

Занумеруем события, образующие полную группу, числами от 1 до n (где n – количество событий) и составим таблицу: в первой строке – номер события, во второй – вероятность появления события с указанным номером.

Номер события j n
Вероятность события

Разобьем отрезок на оси Oy точками с координатами p 1, p 1 +p 2, p 1 +p 2 +p 3, …, p 1 +p 2 +…+p n -1 на n частичных интервалов Δ 1, Δ 2, …, Δ n . При этом длина частичного интервала с номером j равна вероятности p j .

Генерируется случайное число y , равномерно распределенное на отрезке. Если y принадлежит интервалу Δ j , то событие A j наступило.

Лабораторная работа № 1. Экспериментальное вычисление вероятности.

Цели работы: моделирование случайных событий,изучение свойств статистической вероятности события в зависимости от количества испытаний.

Лабораторную работу проведем в два этапа.

Этап 1. Моделирование подбрасывания симметричной монеты .

Событие A состоит в выпадении герба. Вероятность p события A равна 0,5.

a) Требуется выяснить, каким должно быть количество испытаний n , чтобы с вероятностью 0,9 отклонение (по абсолютной величине) относительной частоты появления герба m /n от вероятности p = 0,5 не превышало числа ε > 0:.

Расчеты провести для ε = 0,05 и ε = 0,01. Для вычислений воспользуемся следствием из интегральной теоремы Муавра-Лапласа:

Где; q =1-p .

Как связаны между собой значения ε и n ?

b) Провести k = 10 серий по n испытаний в каждой. В скольких сериях неравенство выполнено и в скольких нарушено? Каким будет результат, если k → ∞?

Этап 2. Моделирование реализации исходов случайного эксперимента.

а) Разработать алгоритм моделирования реализации опыта со случайными исходами согласно индивидуальным заданиям (см. прил. 1).

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

в) Составить статистическую таблицу зависимости частоты появления заданного события от числа проведённых опытов.

г) По статистической таблице построить график зависимости частоты события от числа опытов.

д) Составить статистическую таблицу отклонений значений частоты события от вероятности появления этого события.

е) Отразить полученные табличные данные на графиках.

ж) Найти значение n (число испытаний), чтобы и.

Сделать выводы по работе.

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

Использование функции СЛЧИС

Представленная в Excel функцияСЛЧИС генерирует равномерное случайное число в промежутке между 0 и 1. Другими словами, любое число от 0 до 1 имеет равную вероятность быть возвращенным этой функцией. Если вам нужны случайные числа с большими значениями, используйте простую формулу умножения. Следующая формула, например, генерирует равномерное случайное число между 0 и 1000: =СЛЧИС()*1000.

Чтобы ограничить случайное число целыми числами, используйте функциюОКРУГЛ : =ОКРУГЛ((СЛЧИС()*1000);0).

Использование функции СЛУЧМЕЖДУ

Для генерации равномерных случайных чисел между любыми двумя числами вы можете использовать функциюСЛУЧМЕЖДУ . Следующая формула, например, генерирует случайное число между 100 и 200: =СЛУЧМЕЖДУ(100;200).

В версиях, предшествующих Excel 2007, функцияСЛУЧМЕЖДУ доступна только при установке дополнительного пакета анализа. Для совместимости с предыдущими версиями (и чтобы избежать использования этой надстройки) используйте такую формулу, гдеа представляет нижний, ab - верхний предел: =СЛЧИС()*(b-а)+а. Чтобы сгенерировать случайное число между 40 и 50, используйте следующую формулу: =СЛЧИС()*(50-40)+40.

Использование надстройки Analysis ToolPack

Другой способ получения случайных чисел в листе состоит в использовании надстройкиAnalysis ToolPack (которая поставлялась вместе с Excel). Этот инструмент может генерировать неравномерные случайные числа. Они генерируются не формулами, поэтому, если вам нужен новый набор случайных чисел, необходимо перезапустить процедуру.

Получите доступ к пакетуAnalysis ToolPack , выбравДанные Анализ Анализ данных . Если эта команда отсутствует, установите пакетAnalysis ToolPack с помощью диалогового окнаНадстройки . Самый простой способ вызвать его - нажатьAtl+TI . В диалоговом окнеАнализ данных выберитеГенерация случайных чисел и нажмитеОК . Появится окно, показанное на рис. 130.1.

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

В Excel есть функция нахождения случайных чисел =СЛЧИС(). Возможность же найти случайное число в Excel, важная составляющая планирования или анализа, т.к. вы можете спрогнозировать результаты вашей модели на большом количестве данных или просто найти одно рандомное число для проверки своей формулы или опыта.

Чаще всего эта функция применяется для получения большого количества случайных чисел. Т.е. 2-3 числа всегда можно придумать самому, для большого количества проще всего применить функцию. В большинстве языков программирования подобная функция известная как Random (от англ. случайный), поэтому часто можно встретить обрусевшее выражение «в рандомном порядке» и т.п. В английском Excel функция СЛЧИС числится как RAND

Начнем с описания функции =СЛЧИС(). Для этой функции не нужны аргументы.

А работает она следующим образом — выводит случайное число от 0 до 1. Число будет вещественное, т.е. по большому счету любое, как правило это десятичные дроби, например 0,0006.

При каждом сохранении число будет меняться, чтобы обновить число без обновления нажмите F9.

Случайное число в определенном диапазоне. Функция

Что делать если вам не подходит имеющийся диапазон случайных чисел, и нужно набор случайных чисел от 20 до 135. Как это можно сделать?

Нужно записать следующую формулу.

СЛЧИС()*115+20

Т.е. к 20 будет случайным образом прибавляться число от 0 до 115, что позволит получать каждый раз число в нужном диапазоне (см. первую картинку).

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

СЛУЧМЕЖДУ(20;135)

Просто, но очень удобно!

Если нужно множество ячеек случайных чисел просто протяните ячейку ниже.

Случайное число с определенным шагом

Если нам нужно получить рандомное число с шагом, к примеру пять, то мы воспользуемся одной из. Это будет ОКРВВЕРХ()

ОКРВВЕРХ(СЛЧИС()*50;5)

Где мы находим случайное число от 0 до 50, а затем округляем его до ближайшего сверху значения кратного 5. Удобно, когда вы делаете расчет для комплектов по 5 штук.

Как применять рандом для проверки модели?

Проверить придуманную модель можно при помощи большого количества случайных чисел. Например проверить будет ли прибыльным бизнес-план

Решено вынести эту тему в отдельную статью. Следите за обновлениями, будет на этой неделе.

Случайное число в VBA

Если вам необходим записать макрос и вы не знаете как это сделать, то можно прочитать.

В VBA применяется функция Rnd() , при этом она не будет работать без включения команды Randomize для запуска генератора случайных чисел. Рассчитаем случайное число от 20 до 135 макросом.

Sub MacroRand() Randomize Range("A24") = Rnd * 115 + 20 End Sub

Вставьте этот код в редактор VBA (Alt + F11)

Как и всегда, прикладываю пример * со всеми вариантами расчета.

Пишите комментарии, если у вас есть вопросы!

Поделитесь нашей статьей в ваших соцсетях:
Добавить комментарий