Перейти к содержимому

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

Author: Sun Last Modified: 2025-08-07

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

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

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

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


arrow blue right bubble Генерация случайного числа с заданным средним значением и стандартным отклонением

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

1. Сначала введите целевое среднее значение и стандартное отклонение в две отдельные пустые ячейки. Для удобства и организации давайте предположим, что вы используете ячейку B1 для требуемого среднего значения и ячейку B2 для требуемого стандартного отклонения. См. скриншот:
 type mean and standard deviation into two empty cells

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

=NORMINV(RAND(),$B$1,$B$2)
После ввода формулы протяните маркер заполнения вниз, чтобы заполнить столько строк, сколько вам требуется для вашего случайного набора данных. Каждая ячейка будет генерировать значение на основе указанного среднего значения и стандартного отклонения.
enter a formula and fill to other cells

Совет: В формуле =НОРМ.ОБР(СЛЧИС();$B$1;$B$2):

  • СЛЧИС() генерирует другое случайное число между 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)
apply this AVERAGE function to calculate the mean
apply this STDEV.P function to calculate the standard deviation

Совет:

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

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

=$B$1+(B3-$D$1)*$B$2/$D$2
Протяните маркер заполнения вниз через столько строк, сколько у вас случайных чисел. Эта формула стандартизирует ваши начальные значения и масштабирует их точно в соответствии со средним значением и стандартным отклонением в B1 и B2.
enter a fromula to generate the real random numbers

Совет:

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

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

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

=AVERAGE(D3:D16)
Затем в ячейке D18 рассчитайте стандартное отклонение с помощью следующей формулы:
=STDEV.P(D3:D16)
check the mean and standard deviation of the final random number series with formulas

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

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

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

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

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

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

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

По сравнению с методами формул, VBA также позволяет динамически настраивать или интегрироваться с более сложными рабочими процессами, но помните, что макросы должны быть включены в вашей книге и могут потребовать явного сохранения в формате «macro-enabled» .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. Нажмите кнопку Run button Выполнить (или нажмите F5) для запуска макроса. Диалоговое окно предложит вам выбрать диапазон, куда вы хотите вывести случайные числа (например, выберите A1:A100 для 100 значений). Далее вас попросят ввести желаемое среднее значение и стандартное отклонение. Макрос заполнит диапазон случайными числами, соответствующими вашим спецификациям.

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

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

Связанные статьи:

Лучшие инструменты для повышения продуктивности в Office

🤖 Kutools AI Aide: Переворот в анализе данных на основе: Интеллектуальное выполнение   |  Сгенерировать код  |  Создать Пользовательские Формулы  |  Анализ данных и генерация диаграмм  |  Вызов Kutools Functions
Популярные функции: Найти, выделить или отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячейки без потери данных   |   Округлить...
Супер ПОИСК: VLookup с несколькими критериями    VLookup с несколькими значениями  |   Многолистовой поиск   |   Распознавание нечетких соответствий ....
Расширенный раскрывающийся список: Быстро создать раскр. список   |  Зависимый раскрывающийся список   |  Множественный выбор в раскрывающемся списке ....
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка фокусировки   |  Дизайн листа   |   Улучшенная строка формулы    Управление книгой и листами   |  Библиотека автотекста (Auto Text)   |  Выбор даты   |  Объединить данные   |  Шифрование/Расшифровка ячеек    Отправить письмо по списку   |  Супер фильтр   |   Специальный фильтр (фильтр по жирному/курсиву/зачеркиванию...) ...
Топ–15 наборов инструментов: 12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...)   |   50+ типов диаграмм (Диаграмма Ганта, ...)   |   40+ практических формул (Расчет возраста на основе даты рождения, ...)   |   19 инструментов вставки (Вставить QR-код, Вставить изображение из пути, ...)   |  12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...)   |  7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...)   |   ... и многое другое
Используйте Kutools на вашем предпочитаемом языке – поддерживаются английский, испанский, немецкий, французский, китайский и более40 других языков!

Повысьте свои навыки работы в Excel с Kutools для Excel и ощутите новую эффективность. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени.  Щелкните здесь, чтобы получить наиболее нужную вам функцию...


Office Tab добавляет вкладочный интерфейс в Office, делая вашу работу значительно проще

  • Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна, а не в новых окнах.
  • Увеличьте свою продуктивность на50% и сократите сотни кликов мышью ежедневно!