Generar número aleatorio dado un promedio y desviación estándar en Excel

Generar un conjunto de números aleatorios con una media y desviación estándar especificadas es un requisito común en áreas como la simulación estadística, prueba de algoritmos o modelado de procesos en campos como finanzas, ingeniería y educación. Sin embargo, Excel no proporciona una función incorporada directa para generar instantáneamente dicha lista de números aleatorios adaptada tanto a una media como a una desviación estándar dadas. Si necesita frecuentemente crear datos de prueba aleatorizados que coincidan estadísticamente con características particulares, saber cómo lograr esto puede mejorar enormemente la eficiencia de su flujo de trabajo y la calidad de los datos.

En este tutorial, presentaremos formas prácticas de generar números aleatorios basados en la media y la desviación estándar que especifique, con instrucciones detalladas paso a paso, explicaciones de los parámetros de las fórmulas y consejos expertos para la prevención de errores y solución de problemas. Además, proporcionamos una solución de macro VBA para usuarios que necesitan automatizar este proceso o generar grandes conjuntos de datos de manera eficiente.

Generar número aleatorio dado un promedio y desviación estándar

Código VBA - Generar números aleatorios con media y desviación estándar especificadas

---

Generar número aleatorio dado un promedio y desviación estándar

En Excel, puede crear un conjunto de números aleatorios que se ajusten a su media y desviación estándar deseadas aplicando una combinación de funciones estándar. Siga estos pasos para obtener una solución adecuada para conjuntos de datos pequeños o medianos, o para necesidades rápidas y ad hoc:

1. Primero, introduzca su media objetivo y su desviación estándar en dos celdas vacías separadas. Para mayor claridad y organización, digamos que utiliza la celda B1 para la media requerida y la celda B2 para la desviación estándar requerida. Consulte la captura de pantalla:

2. Para crear los datos aleatorios iniciales, vaya a la celda B3 e ingrese la siguiente fórmula:

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

Después de ingresar la fórmula, arrastre el controlador de relleno hacia abajo para llenar tantas filas como necesite para su conjunto de datos aleatorios. Cada celda generará un valor basado en la media y la desviación estándar especificadas.

Consejo:Dentro de la fórmula =NORMINV(RAND(),$B$1,$B$2):

• RAND() genera una probabilidad aleatoria diferente entre 0 y 1 cada vez que la hoja de cálculo recalcula.
• $B$1 se refiere al valor medio que ha especificado.
• $B$2 se refiere a la desviación estándar deseada.

Para versiones modernas de Excel (2010 y posteriores), considere usar =NORM.INV(RAND(),$B$1,$B$2), que es funcionalmente lo mismo pero refleja nombres de funciones actualizadas.

3. Para verificar que sus números generados se asemejen estadísticamente a su media y desviación estándar pretendidos, use las siguientes fórmulas para calcular los valores reales de su muestra generada. En la celda D1, calcule la media de la muestra con:

=AVERAGE(B3:B16)

En D2, calcule la desviación estándar de la muestra con:

=STDEV.P(B3:B16)

Consejo:

• B3:B16 es solo un rango de ejemplo. Ajuste según la cantidad de valores aleatorios que generó en el Paso 2.
• Una muestra aleatoria más grande produce una media y desviación estándar real más cercanas a sus valores especificados, debido a la ley de los grandes números.

4. Para ajustar aún más su serie para que coincida exactamente con su media y desviación estándar pretendidas, normalice sus valores aleatorios iniciales. En la celda D3, ingrese la siguiente fórmula:

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

Arrastre el controlador de relleno hacia abajo a través de tantas filas como tenga números aleatorios. Esta fórmula estandariza sus valores iniciales y los escala precisamente para cumplir con la media y la desviación estándar en B1 y B2.

Consejo:

• B1 es su media requerida.
• B2 es su desviación estándar requerida.
• B3 es el valor aleatorio original.
• D1 es la media de esos valores aleatorios originales.
• D2 es la desviación estándar de esos valores aleatorios originales.

Ahora puede confirmar que el conjunto final de valores cumple con sus requisitos recalculando su media y desviación estándar para fines de aseguramiento de calidad y documentación.

5. En la celda D17, calcule la media de su conjunto final de números aleatorios con la siguiente fórmula:

=AVERAGE(D3:D16)

Luego, en la celda D18, calcule la desviación estándar con la siguiente fórmula:

=STDEV.P(D3:D16)

Consejo: D3:D16 hace referencia al rango de sus números aleatorios finalizados.

Solución de problemas:

• Si ve un error #VALOR!, verifique nuevamente todos los rangos de celdas referenciados y asegúrese de que ninguna fórmula haga referencia a celdas en blanco o inválidas.
• Si la fórmula sigue cambiando cada vez que recalcula, seleccione los números aleatorios finales, cópielos y use Pegado especial > Valores para evitar más actualizaciones.
• Recuerde que los generadores de números aleatorios en Excel dependen del recálculo, por lo que guardar resultados estáticos es necesario cuando la consistencia es crítica.

---

Código VBA - Generar números aleatorios con media y desviación estándar especificadas

Para escenarios en los que necesite producir rápidamente una gran cantidad de datos aleatorios que coincidan con una media y desviación estándar especificadas, especialmente en casos repetitivos, automatizados o de alto volumen, una macro VBA ofrece una solución que ahorra tiempo. Con una sola ejecución, puede crear un conjunto de datos completo directamente en su libro, reduciendo la repetición manual y minimizando errores de copiar fórmulas.

Este enfoque es adecuado para:

• Generar automáticamente conjuntos de datos aleatorios para simulaciones, pruebas de estrés o demostraciones educativas.
• Situaciones en las que desea estandarizar el formato de salida con mínima intervención manual.
• Usuarios familiarizados con el uso del Editor de VBA en Excel.

En comparación con métodos de fórmulas, VBA también permite ajustes dinámicos o integración con flujos de trabajo más complejos, pero tenga en cuenta que las macros deben estar habilitadas en su libro de trabajo y pueden requerir ser guardadas explícitamente en formato .xlsm "habilitado para macros".

1. En la barra de herramientas de Excel, haga clic en Herramientas de Desarrollo (si no es visible, habilítelas mediante Archivo > Opciones > Personalizar cinta), luego seleccione Visual Basic. En la ventana de Visual Basic para Aplicaciones, haga clic en Insertar > Módulo y copie el siguiente código en la ventana de módulo vacía:

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. Haga clic en el Ejecutar botón (o presione F5) para iniciar la macro. Aparecerá un cuadro de diálogo que le pedirá que seleccione el rango donde desea generar los números aleatorios (por ejemplo, seleccione A1:A100 para 100 valores). Luego, se le pedirá que ingrese la media y la desviación estándar deseadas. La macro llenará el rango con números aleatorios que coincidan con sus especificaciones.

Consejos y Solución de Problemas:

• VBA utiliza la función NormInv de Excel para generar números distribuidos normalmente; siempre verifique si su versión lo soporta; para versiones anteriores de Excel, la función podría necesitar ser NORMINV.
• La semilla aleatoria se establece con Randomize para obtener resultados variados en cada ejecución.
• Si desea resultados reproducibles, comente o elimine la línea Randomize.
• La macro sobrescribirá cualquier dato existente en el rango de salida seleccionado, así que asegúrese de elegir un área vacía si es necesario.
• Si introduce valores inapropiados (por ejemplo, una desviación estándar negativa o cero), la macro no procederá y mostrará un mensaje de advertencia.

---

Artículos relacionados:

• Generar números aleatorios sin repetición en Excel
• Generar números aleatorios positivos o negativos en Excel
• Detener el cambio de números aleatorios en Excel
• Generar sí o no aleatorios en Excel