У нас есть последовательность чисел, состоящая из практически независимых элементов, которые подчиняются заданному распределению. Как правило, равномерному распределению.
Сгенерировать случайные числа в Excel можно разными путями и способами. Рассмотрим только лучше из них.
Функция случайного числа в Excel
- Функция СЛЧИС возвращает случайное равномерно распределенное вещественное число. Оно будет меньше 1, больше или равно 0.
- Функция СЛУЧМЕЖДУ возвращает случайное целое число.
Рассмотрим их использование на примерах.
Выборка случайных чисел с помощью СЛЧИС
Данная функция аргументов не требует (СЛЧИС()).
Чтобы сгенерировать случайное вещественное число в диапазоне от 1 до 5, например, применяем следующую формулу: =СЛЧИС()*(5-1)+1.
Возвращаемое случайное число распределено равномерно на интервале [1,10].
При каждом вычислении листа или при изменении значения в любой ячейке листа возвращается новое случайное число. Если нужно сохранить сгенерированную совокупность, можно заменить формулу на ее значение.
- Щелкаем по ячейке со случайным числом.
- В строке формул выделяем формулу.
- Нажимаем F9. И ВВОД.
Проверим равномерность распределения случайных чисел из первой выборки с помощью гистограммы распределения.
- Сформируем «карманы». Диапазоны, в пределах которых будут находиться значения. Первый такой диапазон – 0-0,1. Для следующих – формула =C2+$C$2.
- Определим частоту для случайных чисел в каждом диапазоне. Используем формулу массива <=ЧАСТОТА(A2:A201;C2:C11)>.
- Сформируем диапазоны с помощью знака «сцепления» (="[0,0-"&C2&"]").
- Строим гистограмму распределения 200 значений, полученных с помощью функции СЛЧИС ().
Диапазон вертикальных значений – частота. Горизонтальных – «карманы».
Функция СЛУЧМЕЖДУ
Синтаксис функции СЛУЧМЕЖДУ – (нижняя граница; верхняя граница). Первый аргумент должен быть меньше второго. В противном случае функция выдаст ошибку. Предполагается, что границы – целые числа. Дробную часть формула отбрасывает.
Пример использования функции:
Случайные числа с точностью 0,1 и 0,01:
Как сделать генератор случайных чисел в Excel
Сделаем генератор случайных чисел с генерацией значения из определенного диапазона. Используем формулу вида: =ИНДЕКС(A1:A10;ЦЕЛОЕ(СЛЧИС()*10)+1).
Сделаем генератор случайных чисел в диапазоне от 0 до 100 с шагом 10.
Из списка текстовых значений нужно выбрать 2 случайных. С помощью функции СЛЧИС сопоставим текстовые значения в диапазоне А1:А7 со случайными числами.
Воспользуемся функцией ИНДЕКС для выбора двух случайных текстовых значений из исходного списка.
Чтобы выбрать одно случайное значение из списка, применим такую формулу: =ИНДЕКС(A1:A7;СЛУЧМЕЖДУ(1;СЧЁТЗ(A1:A7))).
Генератор случайных чисел нормального распределения
Функции СЛЧИС и СЛУЧМЕЖДУ выдают случайные числа с единым распределением. Любое значение с одинаковой долей вероятности может попасть в нижнюю границу запрашиваемого диапазона и в верхнюю. Получается огромный разброс от целевого значения.
Нормальное распределение подразумевает близкое положение большей части сгенерированных чисел к целевому. Подкорректируем формулу СЛУЧМЕЖДУ и создадим массив данных с нормальным распределением.
Себестоимость товара Х – 100 рублей. Вся произведенная партия подчиняется нормальному распределению. Случайная переменная тоже подчиняется нормальному распределению вероятностей.
При таких условиях среднее значение диапазона – 100 рублей. Сгенерируем массив и построим график с нормальным распределением при стандартном отклонении 1,5 рубля.
Используем функцию: =НОРМОБР(СЛЧИС();100;1,5).
Программа Excel посчитала, какие значения находятся в диапазоне вероятностей. Так как вероятность производства товара с себестоимостью 100 рублей максимальная, формула показывает значения близкие к 100 чаще, чем остальные.
Перейдем к построению графика. Сначала нужно составить таблицу с категориями. Для этого разобьем массив на периоды:
- Определим минимальное и максимальное значение в диапазоне с помощью функций МИН и МАКС.
- Укажем величину каждого периода либо шаг. В нашем примере – 1.
- Количество категорий – 10.
- Нижняя граница таблицы с категориями – округленное вниз ближайшее кратное число. В ячейку Н1 вводим формулу =ОКРВНИЗ(E1;E5).
- В ячейке Н2 и последующих формула будет выглядеть следующим образом: =ЕСЛИ(G2;H1+$E$5;""). То есть каждое последующее значение будет увеличено на величину шага.
- Посчитаем количество переменных в заданном промежутке. Используем функцию ЧАСТОТА. Формула будет выглядеть так:
На основе полученных данных сможем сформировать диаграмму с нормальным распределением. Ось значений – число переменных в промежутке, ось категорий – периоды.
График с нормальным распределением готов. Как и должно быть, по форме он напоминает колокол.
Сделать то же самое можно гораздо проще. С помощью пакета «Анализ данных». Выбираем «Генерацию случайных чисел».
О том как подключить стандартную настройку «Анализ данных» читайте здесь.
Заполняем параметры для генерации. Распределение – «нормальное».
Жмем ОК. Получаем набор случайных чисел. Снова вызываем инструмент «Анализ данных». Выбираем «Гистограмма». Настраиваем параметры. Обязательно ставим галочку «Вывод графика».
График с нормальным распределением в Excel построен.
Калькулятор генерирует заданное количество нормально распределенных случайных чисел, используя преобразование Бокса-Мюллера над равномерно распределенными случайными числами, полученными из встроенного в Javascript генератора случайных чисел.
Калькулятор генерирует заданное количество нормально распределенных случайных чисел из равномерно распределенных случайных чисел, используя преобразование Бокса-Мюллера. Для получения равномерно распределенных случайных чисел используется встроенный в Javascript генератор случайных чисел.
Вы можете задать количество генерируемых чисел, матожидание и среднеквадратическое отклонение нужного нормального (гауссова) распределения. По умолчанию матожидание равно 0 и среднеквадратическое отклонение равно 1.
Хорошее описание преобразования Бокса-Мюллера можно найти здесь. Там же можно посмотреть и реализацию генератора на Javascript.
text-align:center;line-height:normal;text-autospace:none">ИССЛЕДОВАНИЕ ГЕНЕРАТОРА СЛУЧАЙНЫХ ЧИСЕЛ, РАСПРЕДЕЛЕННЫХ ПО НОРМАЛЬНОМУ ЗАКОНУ
text-align:right;line-height:normal;text-autospace:none">Захарикова Елена Борисовна
text-align:right;line-height:normal;text-autospace:none">аспирант, Пензенский государственный университет, г. Пенза
text-align:center;line-height:normal;text-autospace:none">THE RESEARCH OF RANDOM NUMBER GENERATORS, DISTRIBUTED NORMALLY
text-align:right;line-height:normal;text-autospace:none"> postgraduate student of Penza State University, Penza
justify;text-indent:1.0cm;line-height:150%;text-autospace:none">В данной работе проводится исследование разработанного автором генератора случайных чисел, распределенных по нормальному закону, с помощью критерия согласия «— квадрат».
justify;text-indent:1.0cm;line-height:150%;text-autospace:none">In this work the research of the random numbers distributed normally developed by the author with the goodness of fit «» is performed.
justify;text-indent:1.0cm;line-height:150%;text-autospace:none">Ключевые слова: генераторы случайных чисел; закон распределения; имитационное моделирование; критерий согласия; достоверность; точность; относительная погрешность; выборка; генеральная совокупность.
justify;text-indent:1.0cm;line-height:150%;text-autospace:none">Keywords: random number generators; the distribution; simulation; goodness of fit; the reliability; accuracy; relative error; sample; population.
justify;text-indent:1.0cm;line-height:150%;text-autospace:none">Автором разработан пакет прикладных программ имитационного моделирования на языке С++, включающий программы для генерирования случайных величин с заданными законами распределения. Достоверность и точность результатов имитационного моделирования в значительной степени определяется качеством используемых в моделях программных генераторов псевдослучайных последовательностей.
justify;text-indent:1.0cm;line-height:150%;text-autospace:none">Проверка генераторов распределенных псевдослучайных чисел предполагает формирование большой совокупности или представительной выборки случайных чисел и выполнение оценок соответствия по определенным критериям. Для статистической оценки гипотезы о том, что совокупность выборочных данных незначительно отличается от той, которую можно ожидать при некотором теоретическом законе распределения, воспользуемся критерием согласия «— квадрат» [2, с. 153]. Этот критерий позволяет проверить гипотезу, удовлетворяет ли рассматриваемая случайная величина заданному закону распределения При этом выполняются следующие этапы:
text-align:justify;text-indent:1.0cm;line-height:150%;text-autospace:none">1. Определение выборки с помощью генератора для конкретных параметров.
text-align:justify;text-indent:1.0cm;line-height:150%;text-autospace:none">2. Разбиение всего диапазона значений времени на равные интервалы (не менее 20).
text-align:justify;text-indent:1.0cm;line-height:150%;text-autospace:none">3. Определение частоты попаданий значений случайной величины в каждый интервал.
text-align:justify;text-indent:1.0cm;line-height:150%;text-autospace:none">4. Определение наблюдаемых вероятностей попаданий значений случайной величины в каждый интервал.
text-align:justify;text-indent:1.0cm;line-height:150%;text-autospace:none">5. Вычисление ожидаемых вероятностей попаданий значений случайной величины в каждый интервал.
text-align:justify;text-indent:1.0cm;line-height:150%;text-autospace:none">6. Вычисление величины по следующей формуле:
justify;text-indent:1.0cm;line-height:150%;text-autospace:none">
justify;line-height:150%;text-autospace:none">где — полный объем выборки, — наблюдаемая вероятность попадания значения в -й интервал, — ожидаемая вероятность попадания значения в -й интервал.
justify;text-indent:1.0cm;line-height:150%;text-autospace:none">Если , то практические и теоретические значения частот совпадают. Если , то полного совпадения нет, сравниваются расчетные значения с табличными значениями. Значения статистики табулированы для различных чисел степеней свободы и различных уровней доверительной вероятности . При практическом использовании этой статистики высказывается так называемая нулевая гипотеза о том, что между практическим и теоретическим распределением с теми же параметрами нет значительных расхождений. Если при проверке этой гипотезы расчетная величина оказывается больше критического табличного значения (для данного уровня доверительной вероятности и соответствующего числа степеней свободы), то можно заключить, что при данном уровне доверительной вероятности наблюдаемые частоты значительно отличаются от ожидаемых, и гипотеза отвергается.
justify;text-indent:1.0cm;line-height:150%;text-autospace:none">В данной работе производится оценка функционирования разработанного генератора случайных чисел, распределенных по нормальному закону, по критерию согласия «— квадрат».
justify;text-indent:1.0cm;line-height:150%;text-autospace:none">Параметры выборки: математическое ожидание случайной величины средеквадратическое отклонение , время моделирования Рассматриваются значения случайной величины в интервале
text-align:center;line-height:normal;text-autospace:none">Вычисление ожидаемых вероятностей попаданий значений случайной величины в каждый интервал для нормального распределения
text-align:center;line-height:normal;text-autospace:none">Нижняя граница интервала
text-align:center;line-height:normal;text-autospace:none">Верхняя граница интервала
text-align:center;line-height:normal;text-autospace:none">Вероятность попадания
text-align:center;line-height:normal;text-autospace:none">
text-align:center;line-height:normal;text-autospace:none">
text-align:center;line-height:normal;text-autospace:none">