Функция рандом в excel

При решении задач в Microsoft Office Excel с помощью макросов иногда необходимо сгенерировать произвольные числа для проверки правильности работы

Функция случайного числа в Excel

  1. Функция СЛЧИС возвращает случайное равномерно распределенное вещественное число. Оно будет меньше 1, больше или равно 0.
  2. Функция СЛУЧМЕЖДУ возвращает случайное целое число.

Рассмотрим их использование на примерах.

Выборка случайных чисел с помощью СЛЧИС

Данная функция аргументов не требует (СЛЧИС()).

Чтобы сгенерировать случайное вещественное число в диапазоне от 1 до 5, например, применяем следующую формулу: =СЛЧИС()*(5-1)+1.

СЛЧИС.

Возвращаемое случайное число распределено равномерно на интервале [1,10].

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

  1. Щелкаем по ячейке со случайным числом.
  2. В строке формул выделяем формулу.
  3. Нажимаем F9. И ВВОД.

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

  1. Сформируем «карманы». Диапазоны, в пределах которых будут находиться значения. Первый такой диапазон – 0-0,1. Для следующих – формула =C2+$C$2.Карманы.
  2. Определим частоту для случайных чисел в каждом диапазоне. Используем формулу массива {=ЧАСТОТА(A2:A201;C2:C11)}.ЧАСТОТА.
  3. Сформируем диапазоны с помощью знака «сцепления» (=”[0,0-“&C2&”]”).Диапазоны.
  4. Строим гистограмму распределения 200 значений, полученных с помощью функции СЛЧИС ().

Гистограмма.

Диапазон вертикальных значений – частота. Горизонтальных – «карманы».

Функция СЛУЧМЕЖДУ

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

Пример использования функции:

СЛУЧМЕЖДУ.

Случайные числа с точностью 0,1 и 0,01:

Случайные числа.

Свойства нормального распределения

Изменение параметра нормального распределенияmxприводит к сдвигу кривой по осиx (см.рис. 25.2).

[ Рис. 25.2. Влияние параметра «математическое ожидание» на вид закона нормального распределения случайной величины х ]
Рис. 25.2. Влияние параметра «математическое ожидание»
на вид закона нормального распределения случайной величины х

Изменение параметра нормального распределенияσxприводит к масштабированию формы по осиx(напоминаем, в любом случае всегда площадь под кривой плотности вероятностинеизменна и равна 1).

Чем более не случаен процесс, тем меньше его среднеквадратичное отклонение, темуже и выше колокол на графике. Изменение параметра нормального распределенияσxприводит к масштабированию формы (см.рис. 25.3)по осиx(напоминаем, в любом случае всегда площадь под кривой плотности вероятностинеизменна и равна 1).

[ Рис. 25.3. Влияние параметра «среднеквадратичное отклонение» на вид закона нормального распределения случайной величины х ]
Рис. 25.3. Влияние параметра «среднеквадратичное
отклонение» на вид закона нормального
распределения случайной величины х

Чем более не случаен процесс, тем меньше его среднеквадратичное отклонение, темуже и выше колокол на графике. Действительно, разброс случайности относительноматематического ожидания становится все более минимальным. В пределедетерминированный процесс имеет вид, показанный нарис. 25.4.

[ Рис. 25.4. Вид закона нормального распределения вероятности при переходе его к детерминированному случаю в пределе (σx = 0). Случайное событие становится детерминированным: x = mx ± 0 (разброса нет) ]
Рис. 25.4. Вид закона нормального распределения вероятности
при переходе его к детерминированному случаю в пределе (σx = 0).
Случайное событие становится детерминированным: x = mx ± 0 (разброса нет)

Изучать детерминированные процессы проще. Чем больше величинаσx,тем менее закономерно поведение изучаемого объекта, так как возможны любыезначения характеризующих его параметров, разброс величин относительно среднейожидаемой увеличивается. Прогнозирование и управление поведением объекта в этомслучае затрудняется.

Рассмотрим вид интегральной кривой плотности распределения случайной величины,распределенной по нормального закону. Вид ее приведен нарис. 25.5.F интегральная функция Лапласа. Смысл интегральной функции вероятность того, что случайная величина примет значения из диапазона отдоx.Например, записьF(170) = 0.5для нашего примера означает: вероятность того, что случайно выбранный изаудитории человек будет ростом не выше 170 см, составляет 0.5 (то есть каждыйвторой).

[ Рис. 25.5. Вид интегральной функции Лапласа F(x) ]
Рис. 25.5. Вид интегральной функции Лапласа F(x)

Данная функция задана интегралом от плотности вероятности нормальногораспределения:

[ Формула 02 ]

К сожалению, этот интеграл не берется в общем виде, поэтому функция Лапласазадана в видетаблицыдляmx = 0иσx = 1.Поскольку функция Лапласа симметрична относительно точки(x = 0, y = 0.5)(как и функция самого нормального распределения),F(x) = 1  F(x),то в таблице содержится только одна из ее симметричных частей.

Если задается интервал интегрирования функции Лапласа[ab],то:

[ Формула 03 ]

Вероятность попаданияXв интервал, симметричный относительноmx:

[ Формула 04 ]

Например, для правила «трех сигм»:P(|x  mx| < 3σ) = 2 · F(3)  1 = 2 · 0.9987  1 = 0.9973(как ранее мы и указывали). ЧислоF(3) = 0.9987взято из таблицы Лапласа.

Пример.Найти вероятность изготовления детали с ошибкой в ее размерах не более 15 мм,если известно, что изготовление детали с ошибкой распределено по нормальномузаконуm = 0иσ = 10 мм.

P(|x| < 15) = P(15 < x < 15) = F((15  0)/10)  F((15  0)/10) = F(1.5)  F(1.5) = F(1.5)  (1 
 F(1.5)) = 2 · F(1.5)  1 = 2 · 0.9332  1 = 0.8664
.То есть 8664 деталей из 10000 будут иметь ошибку в размерах не более 15 мм.

Важным свойством закона является то, что нормальное распределение являетсяпределом для различного вида распределений, вытекающее из центральной предельнойтеоремы: «Для большого числаNслучайных величинXс любым законом распределения их сумма есть случайное число с нормальным закономраспределения».

[ Формула 05 ]

гдеa математическое ожидание в законе распределения случайной величиныX;σ среднеквадратичное отклонение в законе распределения случайной величиныX;N количество случайных чисел.

Вспомним опыт «Доска Гальтона» из физики (см.рис. 25.6).

[ Рис. 25.6. Доска Гальтона. Шарики, падающие сверху в сосуд случайно распределяются в нем в согласии с нормальным законом распределения ]
Рис. 25.6. Доска Гальтона. Шарики, падающие сверху в сосуд случайно
распределяются в нем в согласии с нормальным законом распределения

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

Для пытливого ума опыт порождает очень интересные вопросы. Почему малыеотклонения в поведении элементов системы (шариков) ведут к большим разбросам вих координатах? Почему случайности не компенсируются? Насколько они некомпенсируются?

Обозначим черезDрасстояние между стержнями доски Гальтона,r диаметр стержня (см.рис. 25.7, а, б).Очевидно, что отклонениеβшарика от отвесной траектории при малом углеα(изменение направления вследствие удара о стержень) можно посчитать какtg(α1) = β1/rилиα1 ≈ β1/r(при малых углахα1).То есть, далее шарик летит под угломα1к траектории свободного падения на расстояниеD,пока не встретит новый стержень. За время перелета по прямой от стержня кстержню шарик отклонится от отвесной вертикальной линии на величинуβ2 = α1 · D.Далее снова, испытав соударение со стержнями, шарик отклонится на уголα2 ≈ β2/rиβ3 ≈ α2 · D = β1 · (D/r)2и так далее.

[ Рис. 25.7. Взаимодействие шариков со стержнями доски (детализация) ]
Рис. 25.7. Взаимодействие шариков со стержнями доски (детализация)

Если шарик испытаетnсоударений, то он отклонится от отвесной траектории на расстояниеβn ≈ β1 · (D/r)n  1(конечно, если ему «повезет» и он будет отклоняться все время водну сторону).

Теперь посмотрим, сколько это означает в цифрах, например,D = 20r,пусть стержней, от которых отклонился шарик в полете (соударений),n = 10,β1 = 1нм (расстояние меньше атома!!), то тогда легко вычислить, чтоβ10 = 10.24км. То есть, видно, что сверхмалые отклонения (1 нм и число соударенийn = 10),фактически случайности, приводят к макроэффектам. На самом деле такой разброснаступает гораздо быстрее, так как шарики сталкиваются еще и между собой. Кстати,удаление какого-то шарика на 10 км это тоже невероятное событие, так каквероятность, что шарику «повезет» 20 раз подряд, составляет крайнемаловероятное числоP = 0.520 = 10 6.

Рассмотрим еще один эксперимент «Распределение Максвелла». В сосуденаходится 40 синих и 40 красных шаров, каждые в своей половинке сосуда.Половинки сосуда разделены перегородкой с отверстием, через которое могутпроникать шары из одной части сосуда в другую (см.рис. 25.8).Количество шаров, как синих, так и красных, в каждой из частей сосудаподсчитываются.

[ Рис. 25.8. Схема эксперимента, демонстрирующего диффузию (появление хаоса) в сложных системах ]
Рис. 25.8. Схема эксперимента, демонстрирующего
диффузию (появление хаоса) в сложных системах

Шары имитируют броуновское движение молекул, соударяясь друг с другом упруго,обмениваясь при ударе энергиями, импульсами, направлениями скорости и такжевзаимодействуя со стенками сосуда. При смоделированном в опыте упругом удареусредняется средняя кинетическая энергия встречающихся частиц. Несмотря на то,и в этом суть диффузии, что шары находились каждый цвет строго в своей половине,со временем примерно половина (50%) красных шаров окажется в первой половинесосуда, а половина (50%) во второй его части, то же касается и синих шаров(50% : 50%). Употребленное здесь слово «примерно» означает,что время от времени совокупность шаров будет делиться не ровно на половины, а впропорции (50% ± h : 50% ± h),так как случайное количество шаров будет переходить в произвольные моментывремени из одной части сосуда в другую, и наоборот. Обнаруженная в наших опытахотносительная флуктуация средних энергий достаточно велика, порядка 20%.Несмотря на то, что шары каждого цвета находились строго в своей половине(в этом и состоит суть диффузии), со временем примерно половина (50%) красныхшаров окажется в первой половине сосуда, а половина (50%) во второй егочасти, то же касается и синих шаров (50%:50%) (см.рис. 25.9).

[ Рис. 25.9. Типичный вид поведения характеристик в системах с диффузией. На рисунке показано изменение количества синих и красных молекул в одной из половинок сосуда со временем ]
Рис. 25.9. Типичный вид поведения характеристик в системах с диффузией.
На рисунке показано изменение количества синих и красных
молекул в одной из половинок сосуда со временем

Особенность 1. Можете продолжать наблюдение сколь угодно долго, но при этомникогда первоначального распределения этих шаров достигнуто не будет. И,наоборот, при любом начальном порядке шаров система будет переходить к одному итому же хаотическому состоянию. Почему? Объяснение этого надо искать в том,что время необратимо. Операция усреднения также необратима. Если я вам сообщу,что сумма двух чисел равна 8, то вряд ли вы сможете сказать, какие я выбрал дляэтого слагаемые. И множество вариантов слагаемых ведут к одной и той же сумме.

Хаотическая система не помнит свое прошлое. А именно: состояние такой системыпо прошествии времени не зависит от того, как и из какого состояния системапришла в хаотическое. Из любого начального состояния наша система довольнобыстро переходит в это хаотическое состояние, и находится в нем далее сколькоугодно долго. То есть хаотическое состояние устойчивое состояние.Интересно, что в этом устойчивом состоянии можно отыскать такой ряд параметров,который будет неизменен со временем, такие параметры описывают систему в целоми называютсямакропараметрами.Например, в нашем примере устойчивым макропараметром является распределениеколичества молекулnпо модулю скоростиv,которое демонстрирует закон Максвеллаn(v) = r · v2 · ec · v2/T(r,c коэффициенты,T температура совокупности молекул).

Особенность 2. Если разделить сосуд на большее число частей, то шарыраспределятся между ними примерно в равных долях.

Особенность 3. Слово «примерно» пополам, говорит нам о том, что вответе 50% : 50% всегда есть разброс, дисперсия, флуктуация. Этотразброс уменьшается, если увеличить число молекул в сосуде.

Особенность 4. Если запустить модель снова и попробовать шары сначала упорядочить,например, установить их ровно рядами в одной половине сосуда, или вверху, или водном из углов, то очень быстро шары «примерно» равномерно распределятсяпо сосуду, и наступит хаос, то есть порядок исчезнет и, что интересно, никогда сноване наступит. Откуда возникает хаос, как система переходит от порядка к хаосу?Причина хаоса, как мы уже ранее показали в опыте «Доска Гальтона»,появляется в момент небольших отклонений шаров (пусть, например,109 м) от идеальной теоретической траектории, но это небольшоеотклонение на пролете расстоянияDприводит к существенному отклонению траектории от идеала и столкновению шаров,что резко меняет траектории соседей. Мгновенно появляется лавинасоударений, и хаос наступает очень быстро.

Особенность 5. Ранее, считалось, что системы с малым числом элементов неспособны проявлять случайность, хаотическое поведение. Преобладало рассуждениефизиков о газе из миллиардов молекул. Проведите этот же эксперимент с пятьюшарами, потом с тремя и, наконец, одним шаром. Распределение Максвелла врезультате эксперимента проявляется достаточно упрямо, правда, для этоготребуется ждать в несколько раз дольше. (Исключение составит опыт с однимшаром!) Опыт показывает, что усреднение по ансамблю молекул и по временипроводит к одинаковому результату. Так что хаос может возникнуть и на небольшихмножествах.

Итак, и опыт «Доска Гальтона» и экспериментальное снятиераспределения Максвелла приводят нас эмпирически к тому, что нормальноераспределение широко распространено в окружающем нас мире. Суммированиеслучайных величин, сумма действий, совместное наложение эффектов на микроуровнечасто приводит, усреднившись, к появлению нормального распределения намакроуровне.

Рассмотрим вопрос имитации случайных величин, заданных нормальным закономраспределения.

random.random¶

random.random() — возвращает псевдослучайное число от 0.0 до 1.0

random.random()0.07500815468466127

Описание

Функция СЛЧИС возвращает равномерно распределенное случайное вещественное число, большее или равное 0, но меньшее 1. При каждом пересчете листа возвращается новое случайное вещественное число.

Примечание: начиная с версии Excel 2010, в Excel для генерации случайных чисел используется алгоритм “вихрь Мерсенна” (MT19937).  

word-image-138.png

Где СЧЕТЗ возвращает количество непустых ячеек, а ИНДЕКС позволяет вывести значение ячейки, которая находится на пересечении конкретной строки и столбца.

Генератор случайных дат в Excel

Чтобы вернуть список случайных дат между данными двумя датами, используйте функцию СЛУЧМЕЖДУ в сочетании с ДАТА:

=СЛУЧМЕЖДУ (ДАТА (дата начала); ДАТА (дата окончания))

Например, чтобы получить список дат между 1 сентября 2017 и 20 ноября 2017 включительно, введите следующую формулу на листе:

=СЛУЧМЕЖДУ(ДАТА(2017;9;1);ДАТА(2017;11;21))

Не забудьте применить формат даты к ячейке (ячейкам), и вы получите список случайных дат, подобных этому:

6-generator-sluchainykh-chisel-v-excel

Генератор случайных чисел в Excel – Генерация случайных дат

Генератор случайных чисел нормального распределения

Функции СЛЧИС и СЛУЧМЕЖДУ выдают случайные числа с единым распределением. Любое значение с одинаковой долей вероятности может попасть в нижнюю границу запрашиваемого диапазона и в верхнюю. Получается огромный разброс от целевого значения.

Нормальное распределение подразумевает близкое положение большей части сгенерированных чисел к целевому. Подкорректируем формулу СЛУЧМЕЖДУ и создадим массив данных с нормальным распределением.

Себестоимость товара Х – 100 рублей. Вся произведенная партия подчиняется нормальному распределению. Случайная переменная тоже подчиняется нормальному распределению вероятностей.

При таких условиях среднее значение диапазона – 100 рублей. Сгенерируем массив и построим график с нормальным распределением при стандартном отклонении 1,5 рубля.

Используем функцию: =НОРМОБР(СЛЧИС();100;1,5).

НОРМОБР.

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

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

  1. Определим минимальное и максимальное значение в диапазоне с помощью функций МИН и МАКС.
  2. Укажем величину каждого периода либо шаг. В нашем примере – 1.
  3. Количество категорий – 10.Таблица.
  4. Нижняя граница таблицы с категориями – округленное вниз ближайшее кратное число. В ячейку Н1 вводим формулу =ОКРВНИЗ(E1;E5).
  5. В ячейке Н2 и последующих формула будет выглядеть следующим образом: =ЕСЛИ(G2;H1+$E$5;””). То есть каждое последующее значение будет увеличено на величину шага.ЕСЛИ.
  6. Посчитаем количество переменных в заданном промежутке. Используем функцию ЧАСТОТА. Формула будет выглядеть так:

Формула.

На основе полученных данных сможем сформировать диаграмму с нормальным распределением. Ось значений – число переменных в промежутке, ось категорий – периоды.

График.

График с нормальным распределением готов. Как и должно быть, по форме он напоминает колокол.

Сделать то же самое можно гораздо проще. С помощью пакета «Анализ данных». Выбираем «Генерацию случайных чисел».

Анализ данных.

О том как подключить стандартную настройку «Анализ данных» читайте здесь.

Заполняем параметры для генерации. Распределение – «нормальное».

Параметры.

Жмем ОК. Получаем набор случайных чисел. Снова вызываем инструмент «Анализ данных». Выбираем «Гистограмма». Настраиваем параметры. Обязательно ставим галочку «Вывод графика».

Вывод графика.

Получаем результат:

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

Пример.

График с нормальным распределением в Excel построен.

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

1. Построить график нормальнойфункции плотности распределения f(x) при x, меняющемся от 20 до 40 сшагом 1 при

f1.gif

=   3.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...