Generar números aleatorios con una media y una desviación típica dadas en Excel

Generar un conjunto de números aleatorios con una media y una desviación típica específicas es una necesidad habitual en áreas como la simulación estadística, la prueba de algoritmos o la modelización de procesos en sectores como las finanzas, la ingeniería y la educación. Sin embargo, Excel no incluye una función integrada que permita generar directamente una lista de números aleatorios ajustada simultáneamente a una media y una desviación típica determinadas. Si necesita crear con frecuencia datos de prueba aleatorios que coincidan estadísticamente con características concretas, dominar este proceso puede mejorar notablemente la eficiencia de su flujo de trabajo y la calidad de sus datos.

En este tutorial presentamos métodos prácticos para generar números aleatorios a partir de la media y la desviación típica que usted especifique, con instrucciones paso a paso detalladas, explicaciones claras de los parámetros de las fórmulas y consejos expertos para evitar errores y resolver problemas. Además, incluimos una solución mediante macro VBA pensada para usuarios que deseen automatizar este proceso o generar grandes conjuntos de datos de forma eficiente.

Generar números aleatorios con una media y una desviación típica dadas

Código VBA: generar números aleatorios con media y desviación típica especificadas

---

Generar números aleatorios con una media y una desviación típica dadas

En Excel, puede generar un conjunto de números aleatorios que se ajusten a la media y la desviación típica deseadas combinando funciones estándar. Siga estos pasos para obtener una solución ideal en conjuntos de datos pequeños o medianos, o para necesidades puntuales:

1. En primer lugar, introduzca su media objetivo y su desviación típica en dos celdas vacías distintas. Para mayor claridad y organización, supongamos que utiliza la celda B1 para la media requerida y la celda B2 para la desviación típica requerida. Consulte la captura de pantalla:

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

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

Tras introducir la fórmula, arrastre el controlador de relleno hacia abajo para completar tantas filas como necesite en su conjunto de datos aleatorios. Cada celda generará un valor basado en la media y la desviación típica que haya especificado.

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

• RAND() genera un número aleatorio distinto entre 0 y 1 cada vez que la hoja de cálculo se recalcula.
• $B$1 hace referencia al valor medio que ha especificado.
• $B$2 hace referencia a la desviación típica deseada.

En versiones modernas de Excel (2010 y posteriores), considere usar =NORM.INV(RAND(),$B$1,$B$2), que es funcionalmente equivalente pero refleja los nombres actualizados de las funciones.

3. Para verificar que los números generados se asemejan estadísticamente a la media y la desviación típica deseadas, utilice las siguientes fórmulas para calcular el valor actual de su muestra generada. En la celda D1, calcule la media muestral con:

=AVERAGE(B3:B16)

En D2, calcule la desviación típica muestral con:

=STDEV.P(B3:B16)

Consejo:

• B3:B16 es solo un rango de ejemplo. Ajústelo según la cantidad de valores aleatorios que haya generado en el paso 2.
• Una muestra aleatoria más grande produce una media y una desviación típica que se acercan más a los valores especificados, gracias a la ley de los grandes números.

4. Para ajustar aún más su serie y lograr que coincida exactamente con la media y la desviación típica deseadas, normalice sus valores aleatorios iniciales. En la celda D3, introduzca 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 normaliza sus valores iniciales y los escala con precisión para ajustarse a la media y la desviación típica especificadas en B1 y B2.

Consejo:

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

Ahora puede confirmar que el conjunto final de valores cumple sus requisitos al recalcular su media y su desviación típica para garantizar el control de calidad y la documentación.

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

=AVERAGE(D3:D16)

Luego, en la celda D18, calcule la desviación típica con la siguiente fórmula:

=STDEV.P(D3:D16)

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

Resolución de problemas:

• Si ve el error #¡VALOR!, revise detenidamente todos los rangos de celdas a los que hace referencia y asegúrese de que ninguna fórmula incluya celdas vacías o no válidas.
• Si la fórmula sigue cambiando cada vez que se recalcula, seleccione los números aleatorios finales, cópielos y use Pegado Especial > Valores para evitar actualizaciones posteriores.
• Recuerde que los generadores aleatorios en Excel dependen del recálculo, por lo que debe guardar los resultados como valores estáticos siempre que la coherencia sea fundamental.

---

Código VBA: generar números aleatorios con media y desviación típica especificadas

Para escenarios en los que necesite generar rápidamente una gran cantidad de datos aleatorios que se ajusten a una media y una desviación típica específicas —especialmente en procesos repetitivos, automatizados o de alto volumen—, una macro de VBA ofrece una solución eficaz y que ahorra tiempo. Con una sola ejecución, podrá crear un conjunto de datos completo directamente en su libro de trabajo, eliminando la repetición manual y reduciendo al mínimo los errores derivados de copiar fórmulas.

Este enfoque es adecuado para:

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

En comparación con los métodos basados en fórmulas, VBA permite además ajustes dinámicos e integración en flujos de trabajo más complejos; no obstante, recuerde que las macros deben estar habilitadas en su libro de trabajo y que es posible que deba guardarlo explícitamente en formato «habilitado para macros» (.xlsm).

1. En la Cinta de opciones de Excel, haga clic en Herramientas para desarrolladores(si no es visible, habilítela desde)Archivo > Opciones > Personalizar Cinta de opciones). A continuación, 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 vacía del módulo:

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 botón Ejecutar(o pulse)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 obtener 100 valores). A continuación, se le solicitará que introduzca la media y la desviación típica deseadas. La macro rellenará automáticamente el rango seleccionado con números aleatorios que se ajusten a sus especificaciones.

Consejos y resolución de problemas:

• VBA utiliza la función NormInv de Excel para generar números distribuidos normalmente; asegúrese siempre de que su versión la admita. En versiones anteriores de Excel, es posible que deba escribirse como NORMINV.
• La semilla aleatoria se establece con Randomize para obtener resultados variados en cada ejecución.
• Si desea obtener resultados reproducibles, comente o elimine la línea Randomize.
• La macro sobrescribirá cualquier dato existente en el área de ubicación de la lista seleccionada, así que asegúrese de elegir un área vacía si es necesario.
• Si introduce valores inapropiados (por ejemplo, una desviación típica negativa o cero), la macro no continuará 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
• Evitar que los números aleatorios cambien en Excel
• Generar aleatoriamente sí o no en Excel