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

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

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

Генерация случайных чисел с заданными средним значением и стандартным отклонением

Код VBA — генерация случайных чисел с заданными средним значением и стандартным отклонением

---

Генерация случайных чисел с заданными средним значением и стандартным отклонением

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

1. Сначала введите целевые значения среднего и стандартного отклонения в две отдельные пустые ячейки. Для наглядности и удобства предположим, что вы используете ячейку B1 для требуемого среднего значения и ячейку B2 — для требуемого стандартного отклонения. См. снимок экрана:

2. Чтобы сгенерировать исходные случайные данные, перейдите в ячейку B3 и введите следующую формулу:

=NORMINV(RAND(),$B$1,$B$2)

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

Совет:В формуле =NORMINV(RAND(),$B$1,$B$2):

• RAND() при каждом пересчёте листа возвращает новое случайное число в диапазоне от 0 до 1.
• $B$1 ссылается на заданное вами среднее значение.
• $B$2 содержит ссылку на требуемое стандартное отклонение.

Для современных версий Excel (2010 и новее) рекомендуется использовать =NORM.INV(RAND(),$B$1,$B$2), функционально эквивалентную, но адаптированную под обновлённые названия функций.

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

=AVERAGE(B3:B16)

В ячейке D2 вычислите выборочное стандартное отклонение с помощью:

=STDEV.P(B3:B16)

Совет:

• B3:B16 — это лишь пример диапазона. Измените его в соответствии с количеством случайных значений, сгенерированных на шаге 2.
• Более крупная случайная выборка обеспечивает среднее значение и стандартное отклонение, ближе соответствующие указанным вами, благодаря закону больших чисел.

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

=$B$1+(B3-$D$1)*$B$2/$D$2

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

Совет:

• B1 — это требуемое среднее значение.
• B2 — это требуемое стандартное отклонение.
• B3 — это исходное случайное значение.
• D1 — это среднее значение этих исходных случайных чисел.
• D2 — это стандартное отклонение этих исходных случайных чисел.

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

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

=AVERAGE(D3:D16)

Затем в ячейке D18 вычислите стандартное отклонение с помощью следующей формулы:

=STDEV.P(D3:D16)

Совет: D3:D16 — это диапазон ваших окончательных случайных чисел.

Устранение неполадок:

• Если вы видите ошибку #ЗНАЧ!, внимательно проверьте все ссылки на диапазоны ячеек и убедитесь, что формулы не ссылаются на пустые или недопустимые ячейки.
• Если формула продолжает изменяться при каждом пересчёте, выделите итоговые случайные числа, скопируйте их и используйте Вставить специально > Значения, чтобы остановить дальнейшие обновления.
• Помните: генераторы случайных чисел в Excel обновляются при каждом пересчёте, поэтому для обеспечения согласованности важно сохранять статические результаты.

---

Код VBA — генерация случайных чисел с заданными средним значением и стандартным отклонением

Для сценариев, требующих быстрого генерирования большого объёма случайных данных с заданными средним значением и стандартным отклонением — особенно в повторяющихся, автоматизированных или высоконагруженных процессах — макрос VBA предлагает решение, экономящее время. При однократном запуске он создаёт полный набор данных прямо в книге, устраняя необходимость ручного дублирования и минимизируя ошибки, связанные с копированием формул.

Этот подход подходит для:

• Автоматическая генерация случайных наборов данных для моделирования, стресс-тестирования и учебных демонстраций.
• Ситуации, в которых необходимо стандартизировать формат вывода с минимальным ручным вмешательством.
• Пользователи, уверенно владеющие редактором VBA в Excel.

По сравнению с формульными методами, VBA также позволяет динамически настраивать параметры и интегрировать процесс в более сложные рабочие процессы, но помните: чтобы всё работало корректно, макросы должны быть включены в вашей книге, а файл, возможно, потребуется явно сохранить в формате с поддержкой макросов (.xlsm).

1. На ленте Excel нажмите Инструменты разработчика(если эта вкладка не отображается, включите её через)Файл > Параметры > Настройка ленты), затем выберите Visual Basic. В окне Visual Basic for Applications нажмите Вставка > Модуль и скопируйте следующий код в пустое окно модуля:

Sub GenerateRandomNumbersWithMeanStd()
    Dim outputRange As Range
    Dim meanValue As Double, stdDevValue As Double
    Dim numItems As Long, i As Long
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set outputRange = Application.InputBox("Select the output range", xTitleId, Type:=8)
    meanValue = Application.InputBox("Enter the mean value", xTitleId, "", Type:=1)
    stdDevValue = Application.InputBox("Enter the standard deviation", xTitleId, "", Type:=1)
    
    If outputRange Is Nothing Or meanValue = 0 Or stdDevValue = 0 Then
        MsgBox "Please ensure you have specified all required parameters.", vbExclamation, "KutoolsforExcel"
        Exit Sub
    End If
    
    numItems = outputRange.Count
    Randomize
    
    For i = 1 To numItems
        outputRange.Cells(i).Value = Application.WorksheetFunction.NormInv(Rnd, meanValue, stdDevValue)
    Next i
End Sub

2. Нажмите кнопку Выполнить(или клавишу)F5), чтобы запустить макрос. Появится диалоговое окно с запросом на выбор диапазона для вывода случайных чисел (например, выделите A1:A100 для 100 значений). Затем вам будет предложено ввести требуемые среднее значение и стандартное отклонение. Макрос заполнит выбранный диапазон случайными числами в соответствии с вашими спецификациями.

Советы и устранение неполадок:

• VBA использует функцию Excel NormInv для генерации нормально распределённых чисел — всегда проверяйте, поддерживает ли ваша версия эту функцию; в старых версиях Excel она может называться NORMINV.
• Случайное начальное значение (seed) задаётся с помощью Randomize, чтобы результаты отличались при каждом запуске.
• Если вам нужны воспроизводимые результаты, закомментируйте или удалите строку Randomize.
• Макрос перезапишет все существующие данные в выбранной области размещения списка, поэтому, если необходимо, убедитесь, что вы выбираете пустой диапазон.
• Если вы укажете некорректные значения (например, отрицательное или нулевое стандартное отклонение), макрос не выполнится и отобразит предупреждение.

---

См. также:

• Генерация случайных чисел без повторений в Excel
• Генерация положительных или отрицательных случайных чисел в Excel
• Как остановить изменение случайных чисел в Excel
• Генерация случайных значений «да» или «нет» в Excel