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

Generar un conjunto de números aleatorios con un promedio y una desviación estándar especificados es un requisito común en áreas como la simulación estadística, pruebas de algoritmos o modelado de procesos en campos como las finanzas, la ingeniería y la educación. Sin embargo, Excel no proporciona una función incorporada directa para generar instantáneamente una lista de números aleatorios adaptada tanto a un promedio como a una desviación estándar dados. Si necesita frecuentemente crear datos de prueba aleatorios que coincidan estadísticamente con características particulares, saber cómo lograrlo 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 el promedio 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 de 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 el promedio y la desviación estándar

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

---

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

En Excel, puede crear un conjunto de números aleatorios que se ajusten al promedio y la desviación estándar deseada 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, ingrese su promedio objetivo y su desviación estándar en dos celdas vacías separadas. Para mayor claridad y organización, digamos que usa la celda B1 para el promedio requerido 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 el promedio y la desviación estándar especificados.

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

• RAND() produce una probabilidad aleatoria diferente entre 0 y 1 cada vez que la hoja de cálculo se 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 actualizados.

3. Para verificar que sus números generados se asemejen estadísticamente al promedio y la desviación estándar deseados, use las siguientes fórmulas para calcular los valores reales de su muestra generada. En la celda D1, calcule el promedio 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 haya generado en el Paso 2.
• Una muestra aleatoria más grande da como resultado un promedio y una desviación estándar reales más cercanos a los 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 promedio y desviación estándar deseados, 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 el promedio y la desviación estándar en B1 y B2.

Consejo:

• B1 es su promedio requerido.
• B2 es su desviación estándar requerida.
• B3 es el valor aleatorio original.
• D1 es el promedio 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 promedio y desviación estándar para fines de aseguramiento de calidad y documentación.

5. En la celda D17, calcule el promedio 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 se refiere al rango de sus números aleatorios finalizados.

Solución de problemas:

• Si ve un error #VALOR!, verifique 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 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 promedio y desviación estándar especificados

Para escenarios en los que necesita producir rápidamente una gran cantidad de datos aleatorios que coincidan con un promedio y una desviación estándar especificados, 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 de trabajo, reduciendo la repetición manual y minimizando errores de copia de 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 los métodos de fórmulas, VBA también puede permitir 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 un guardado explícito en formato .xlsm "habilitado para macros".

1. En la barra de herramientas de Excel, haga clic en Herramientas de Desarrollo (si no está visible, habilítela 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 del módulo vacío:

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. Un cuadro de diálogo 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 el promedio deseado y la desviación estándar. La macro llenará el rango con números aleatorios que coinciden 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 la 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 ingresa 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 aleatoriamente en Excel