Случайное число в Excel: как сгенерировать и использовать

Excel - удобный инструмент для генерации случайных чисел. Эта функция пригодится во многих сферах: от тестирования сотрудников до имитационного моделирования.

Основы работы со случайными числами в Excel

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

• СЛЧИС() - возвращает случайное число больше нуля и меньше единицы;
• СЛУЧМЕЖДУ() - возвращает случайное целое число в заданном диапазоне.

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

Настройка диапазона значений

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

=СЛЧИС()*(верхняя граница-нижняя граница)+нижняя граница

Например, чтобы получить случайное число от 1 до 5:

=СЛЧИС()*(5-1)+1

Функция СЛУЧМЕЖДУ() позволяет указать границы диапазона напрямую:

=СЛУЧМЕЖДУ(1;5)

Сохранение сгенерированных значений

По умолчанию случайные числа в Excel генерируются заново при каждом пересчете листа. Чтобы сохранить сгенерированное значение, можно преобразовать формулу в число. Для этого выделите ячейку и нажмите комбинацию клавиш Ctrl+C и Ctrl+V.

Расширенные возможности

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

Получение нормального распределения

Функции СЛЧИС() и СЛУЧМЕЖДУ() генерируют равновероятное распределение чисел. Чтобы получить нормальное распределение со средним значением и дисперсией, используется функция НОРМОБР():

=НОРМОБР(СЛЧИС();среднее значение;стандартное отклонение)

Например, чтобы смоделировать распределение себестоимости партии товаров со средним значением 100 рублей и стандартным отклонением 1.5:

=НОРМОБР(СЛЧИС();100;1.5)

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

Генерация уникальных случайных чисел

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

Простой способ - использовать функцию РАНГ():

1. В одном столбце генерируем случайные числа от 0 до 1 функцией СЛЧИС();
2. Во втором столбце к этим числам применяем функцию РАНГ();
3. Получаем уникальные порядковые номера.

Еще один метод - применение формулы массива со случайным сдвигом индексов:

=ИНДЕКС(диапазон значений;МОД(СЛЧИС()*СЧЕТ(диапазон)+1;СЧЕТ(диапазон))

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

Генерация уникальных случайных чисел

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

Пример кода такой функции:

Function Lotto(count As Integer, min As Integer, max As Integer) As Variant Dim arr As Variant arr = Array() For i = 1 To count Randomize num = Int((max - min + 1) * Rnd() + min) If IsError(Application.Match(num, arr, 0)) Then arr(i) = num Else i = i - 1 End If Next i Lotto = Application.Transpose(arr) End Function 

Эту функцию затем можно использовать в листе Excel так:

=Lotto(10;1;50)

Она вернет массив из 10 уникальных случайных чисел от 1 до 50.

Способы визуализации случайных чисел

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

Гистограмма

Классический вариант - построение гистограммы частот. Для этого:

1. Разбиваем данные на классы/интервалы;
2. Подсчитываем частоту попадания значений в каждый класс;
3. Строим столбчатую диаграмму с классами по оси X и частотами по оси Y.

Такая гистограмма наглядно покажет плотность распределения чисел в различных диапазонах.

Другие способы визуализации

Также можно построить:

• Линейный график ряда случайных чисел;
• График рассеивания со случайными X и Y;
• Круговую диаграмму для отображения частот или долей.

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

Расширенные примеры использования

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

Случайная выборка сотрудников

Допустим, в компании 100 сотрудников, и нужно выбрать случайным образом 10 человек для опроса. В Excel это можно сделать так:

1. В столбце A разместить список всех 100 фамилий сотрудников;
2. В столбце B с помощью функции СЛУЧМЕЖДУ() сгенерировать 100 случайных чисел от 1 до 100;
3. Отсортировать данные по столбцу B;
4. Выбрать первые 10 записей - это и будет случайная выборка сотрудников.

Моделирование временных рядов

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

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

1. Рассчитать трендовую составляющую как линейный рост выручки;
2. Добавить сезонную составляющую - фиксированные коэффициенты для каждого месяца исходя из истории;
3. С помощью НОРМОБР() смоделировать ошибку прогноза с заданным среднеквадратичным отклонением.

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

Рекомендации по применению

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

Выбор подходящей функции

В зависимости от поставленной задачи, лучше использовать СЛЧИС(), СЛУЧМЕЖДУ() или НОРМОБР(). Первая функция проще и быстрее, но менее гибкая. А НОРМОБР() позволяет получить более реалистичное распределение.

Проверка качества генерации

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