Создание случайного числа с заданным средним значением и стандартным отклонением в Excel
Генерация набора случайных чисел с указанным средним значением и стандартным отклонением является распространенной задачей в таких областях, как статистическое моделирование, тестирование алгоритмов или моделирование процессов в финансах, инженерии и образовании. Однако Excel не предоставляет прямой встроенной функции для мгновенного создания такого списка случайных чисел, адаптированного одновременно под заданное среднее значение и стандартное отклонение. Если вам часто нужно создавать рандомизированные тестовые данные, которые статистически соответствуют определенным характеристикам, знание того, как этого достичь, может значительно улучшить эффективность вашей работы и качество данных.
В этом руководстве мы представим практические способы генерации случайных чисел на основе указываемых вами средних значений и стандартных отклонений, с подробными пошаговыми инструкциями, объяснениями параметров формул и экспертными советами по предотвращению ошибок и устранению неполадок. Кроме того, мы предоставляем решение с использованием макроса VBA для пользователей, которым необходимо автоматизировать этот процесс или генерировать большие объемы данных эффективно.
Создание случайного числа с заданным средним значением и стандартным отклонением
Код VBA - Генерация случайных чисел с заданным средним значением и стандартным отклонением
Создание случайного числа с заданным средним значением и стандартным отклонением
В Excel вы можете создать набор случайных чисел, подходящих под желаемые среднее значение и стандартное отклонение, используя комбинацию стандартных функций. Следуйте этим шагам для решения, подходящего для небольших и средних наборов данных или быстрых, разовых потребностей:
1. Сначала введите целевые среднее значение и стандартное отклонение в две отдельные пустые ячейки. Для ясности и организации предположим, что вы используете ячейку B1 для требуемого среднего значения и ячейку B2 для требуемого стандартного отклонения. См. скриншот:
2. Чтобы создать начальные рандомизированные данные, перейдите в ячейку B3 и введите следующую формулу:
=NORMINV(RAND(),$B$1,$B$2)
После ввода формулы протяните маркер заполнения вниз, чтобы заполнить столько строк, сколько требуется для вашего случайного набора данных. Каждая ячейка будет генерировать значение на основе указанного среднего значения и стандартного отклонения.
Совет: В формуле =НОРМОБР(СЛЧИС();$B$1;$B$2):
- СЛЧИС() каждый раз при пересчете листа выдает новое случайное значение между 0 и 1.
- $B$1 ссылается на указанное вами среднее значение.
- $B$2 ссылается на требуемое стандартное отклонение.
=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
НОРМОБР
для генерации нормально распределенных чисел — всегда дважды проверяйте, поддерживает ли ваша версия эту функцию; для старых версий Excel она может называтьсяНОРМИНВ
. - Случайное зерно установлено с помощью
Randomize
для получения различных результатов при каждом запуске. - Если вы хотите воспроизводимые результаты, закомментируйте или удалите строку с
Randomize
. - Макрос перезапишет любые существующие данные в выбранном диапазоне вывода, поэтому, если это необходимо, убедитесь, что вы выбираете пустую область.
- Если вы введете некорректные значения (например, отрицательное или нулевое стандартное отклонение), макрос не продолжит выполнение и покажет предупреждающее сообщение.
Связанные статьи:
- Генерация случайных чисел без повторов в Excel
- Генерация положительных или отрицательных случайных чисел в Excel
- Остановка изменения случайных чисел в Excel
- Генерация случайных значений «да» или «нет» в Excel
Лучшие инструменты для повышения продуктивности работы с Office
Ускорьте работу в Excel с Kutools для Excel и ощутите новую степень эффективности. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени. Нажмите здесь, чтобы выбрать нужную вам функцию...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
- Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!