У нас есть последовательность чисел, состоящая из практически независимых элементов, которые подчиняются заданному распределению. Как правило, равномерному распределению.

Сгенерировать случайные числа в 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:

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

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

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

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

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

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

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

График с нормальным распределением в 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">