¿Puede Excel generar números aleatorios?

¿Sabe que puede generar números, incluso listas de números en milisegundos a través de Excel?

De hecho, es bastante fácil hacer experimentos, una especie de cálculos hipotéticos con escenarios a través de excel gracias a dos funciones de excel que dan números aleatorios:

=R A N D ( )

y

=RANDBETWEEN ( abajo, arriba )

Generar números en Excel

Supongamos que necesita asignar números aleatorios a un grupo de personas. Puede ser porque va a darles números simbólicos en una carrera. O

• Seleccione al azar un número de personas de una lista de correo,
• Elija a algunos empleados de una lista para hacerles una encuesta aleatoria porque no puede hacerlo a todo el universo de sujetos,
• Empareje dos clases diferentes de un colegio para que compitan en un concurso de conocimientos
• Haga parejas de clubes de fútbol para que jueguen un partido entre sí con una selección aleatoria entre los clubes del mismo nivel.

En todos estos ejemplos, puede asignar números aleatorios a los elementos/grupos pertinentes y luego formar escenarios Y si… y otros cálculos para organizar una relación de causa y efecto entre ellos.

Empecemos con la función RAND:

La función RAND nos proporciona números aleatorios distribuidos uniformemente (técnicamente números pseudoaleatorios) que son mayores o iguales que 0 y menores que 1.

La sintaxis de la función RAND es
=RAND ( )

Por ejemplo, si observa la 1ª hoja de ejemplo, verá el número aleatorio de la celda A2 generado mediante la función RAND.

Observe que cada vez que escriba algo más en cualquier celda (puede ser un número, una letra o un símbolo, cualquier cosa), o borre un valor de otra celda, o actualice la página mediante F9 o pulse intro cuando el ratón esté en la celda A2, el valor de la celda A2 cambiará automáticamente. Esto se debe a que las celdas con funciones RAND son dinámicas, de modo que la hoja de cálculo se recalcula y cada vez se da un nuevo número aleatorio. Sin embargo, esto puede ser molesto y también puede estar perturbando su trabajo si desea números estables para realizar sus cálculos. Por ello, es posible desactivar el recálculo automático. Por favor, siga estas instrucciones:

Archivo > Opciones > Fórmulas y cambie Cálculo del Libro de Trabajo a Manual

(Esto es válido también para la función RANDBETWEEN)

Tenga en cuenta que debe tener mucho cuidado al desactivar esta automatización porque sus otras fórmulas pueden verse perjudicadas en consecuencia.

También puede deshacerse del recálculo automático en su tabla de otra manera. Puede Copiar la columna que tiene los números aleatorios que acaba de generar mediante la función RAND, hacer clic con el botón derecho, elegir Pegado especial y luego Valores. De esta forma, como ya no tendrá la fórmula, los números no cambiarán automáticamente.

(Esto también es válido para la función RANDBETWEEN)

Si desea no sólo un único número aleatorio, sino una lista de números aleatorios, puede crear 1 número aleatorio mediante la función RAND en una celda y luego utilizar el tirador de relleno para arrastrar la celda hacia abajo. Consulte la 2ª hoja de ejemplo.

(Esto también es válido para la función RANDBETWEEN)

Si desea obtener una lista de números aleatorios en una sola fórmula, seleccionar las celdas de la columna y escribir la fórmula no es suficiente, difícil. Primero; necesita seleccionar las celdas dentro de las cuales desea que aparezca la lista, luego necesita escribir la fórmula =RAND() , después de eso necesita hacer clic en CTRL + Enter en lugar de sólo ENTER.

(Esto es válido también para la función RANDBETWEEN)

Para más explicaciones sobre la función RAND, puede visitar nuestra entrada en el blog.

Si decide que necesita números aleatorios, pero no en el rango entre 0 y 1 , sino en cualquier otro rango, puede utilizar la función RANDBETWEEN. Por lo tanto, ¡la función RANDBETWEEN le da aún más opciones que la función RAND a la hora de generar números aleatorios!

La sintaxis de la función RAND es
=RANDBETWEEN (abajo, arriba)

Inferior: El número más pequeño que puede dar la función

Arriba: El número más alto que puede dar la función

Tanto el número inferior como el superior pueden aparecer en la lista.

Tenga en cuenta que la función RAND nos da un número decimal (porque tiene que estar entre 0 y 1), mientras que la RANDBETWEEN nos da un número entero. Por favor, mire la 3ª hoja de ejemplo para ver la fórmula en detalle. Supongamos que desea obtener un número aleatorio entre 1 y 100:

Sin embargo, si escribe la misma fórmula y no pulsa INTRO, sino que pulsa el botón F9, verá que la fórmula se convierte en un número estable y ya no cambia. Por lo tanto, este es un atajo para hacer un copy-paste especial a los valores. Por favor, vea el ejemplo de la 4ª hoja y pruebe por su cuenta:

Veamos un ejemplo

Usted tiene un grupo de empleados y quiere sortear entre 5 de ellos quién ha sido el que más ha vendido y luego dará un premio al ganador del sorteo.

Este es el grupo:

Si ordena sus puntuaciones de ventas de mayor a menor mediante Datos -> Filtro -> Ordenar de mayor a menor, obtendrá;

Elijamos a alguien al azar entre los 5 que tenga las mayores ventas utilizando las funciones MIN y MAX de excel y también la función RANDBETWEEN (por favor, mire la hoja de ejemplo 5 para entenderlo en detalle) :

La fórmula eligió a Mike como ganador dándonos el empleado número 2.

Tenga en cuenta que colocamos otra fórmula llamada VLOOKUP en las celdas F y G para recuperar el nombre del empleado y la puntuación de ventas correspondientes:

Si quiere saber más sobre cómo utilizar la función VLOOKUP, consulte esta guía.

Nota importante: Si el rango utilizado en la función RANDBETWEEN es mayor que el rango de su lista, puede obtener números duplicados o errores. Por ejemplo, digamos que en el último ejercicio, usted eligió el rango de la función RANDBETWEEN de 1 a 10, sin embargo, en su lista principal no tiene los empleados cuyos números son 8, 9 o 10. Por eso cuando arrastra la fórmula por las celdas, y la fórmula da el número 9, obtiene errores en la función VLOOKUP.

Para una explicación más detallada de la función RANDBETWEEN, puede visitar la entrada de nuestro blog.

Ejecución de simulaciones en Excel

Las simulaciones se utilizan para imitar una situación que tiene varias variables y determinados escenarios. En la mayoría de los casos, es necesario un modelo matemático para imitar un sistema, una situación o un proceso. De este modo, se hace visible cómo funciona el sistema, qué variable afecta y, por supuesto, los resultados de determinadas acciones.

En la vida empresarial, puede utilizar simulaciones para resolver diversos problemas, como decidir qué vendedor debe recibir cuánta bonificación en función de sus ventas y cómo afecta la cantidad de bonificación concedida a las finanzas de la empresa, o puede calcular las cantidades de ingredientes de un producto que va a fabricar y cómo afectan las cantidades añadidas al coste de las mercancías vendidas y, en consecuencia, cuánto beneficio obtendrá en función de las diferentes previsiones de ventas. También puede calcular nuestro riesgo financiero en función de la cantidad de producción y ventas mediante simulaciones creando una fórmula con las variables de la situación y para imitar las probabilidades del futuro.

Creación de insumos

Para establecer una simulación en Excel hay que empezar por crear las entradas adecuadas, de modo que cada vez que se ejecute la simulación se obtengan nuevas variables aleatorias que se utilizarán como entradas del modelo de cálculo. Si ejecutamos las simulaciones un número suficiente de veces, el cálculo adquiere más sentido porque la aleatoriedad entre los resultados se hace menor. Como ya sabe, utilizamos las funciones RAND o RANDBETWEEN de Excel para generar variables aleatorias.

Sabemos que podemos crear números aleatorios mediante estas dos funciones y que podemos renovarlos simplemente pulsando F9, sin embargo, para formar un escenario de la vida real, necesitamos generar números de acuerdo con una distribución de probabilidad. Así, el sistema computa un resultado diferente que es probable que aparezca según las variables y las entradas.

Excel dispone de funciones para calcular distribuciones de probabilidad fácilmente. Puede encontrar una lista de ellas aquí:

• Normal: DIST, INV
• Normal estándar: DIST, NORM.S.INV
• Distribución t: DIST, INV
• Distribución F: DIST, INV
• Chi-cuadrado: DIST, INV
• Lognormal: DIST, INV
• Binomial: DIST, INV
• Hipergeométrica: DIST
• Beta DIST, INV
• Gamma: DIST, GAMMA.INV
• Exponencial: DIST
• Weibull: DIST
• Poisson: DIST
• Binomial negativa: DIST

Una de las distribuciones más populares es la distribución normal. Para ello puede utilizar la función NORM.INV. La sintaxis de la función es

= NORM.INV(probabilidad,media,desv_estandar)

No olvide que, para aleatorizar los resultados, incluimos la función RAND en la función NORM.

Por ejemplo
= NORM.INV(RAND(),media,desv_estandar)

La media y la desviación estándar deben ser significativas según nuestros datos de entrada. Por ejemplo, si estamos calculando el coste de un nuevo bien e incluimos datos de entrada como la cantidad de producción o la cantidad de materias primas utilizadas, debemos basar nuestra media y desviación estándar en las de un producto similar producido el año anterior, por ejemplo.

Para recordárselo, la sintaxis de la función Media es :

=MEDIA(números)

la sintaxis para la función Desviación estándar es

• S(números)
• P(números)
• STDDEVA(números)
• STDDEVPA(números)

Eche un vistazo a nuestro simulador de lotería y vea otras funciones de las plantillas de Excel. Ahora es más fácil tener loterías seguras gracias a Excel y puede preparar loterías cómodamente con nuestras plantillas.

Simulación Monte Carlo en Excel:

John von Neumann y Stanislaw Ulam acuñaron el término Simulación de Montecarlo en los años 40 haciendo referencia a Montecarlo (en Monako) donde es un lugar popular como punto de juego de las élites europeas.

La Simulación de Montecarlo es un método para calcular una enorme cantidad de sorteos aleatorios y resolver problemas muy complejos. La economía, las finanzas, la física, la química, la ingeniería y la cadena de suministro son algunos de los campos en los que se está haciendo un amplio uso de la Simulación de Montecarlo. Como puede observar, todos estos campos pueden tener casos que tengan diversas variables a considerar.

Por ejemplo, en los negocios, los métodos aleatorios y probabilísticos también pueden utilizarse para examinar las opciones complejas o evaluar los riesgos de que una empresa no pague sus deudas.

A veces tenemos un problema complejo y es imposible resolverlo mediante el cálculo directo. Formamos un método de cálculo matemático que incluye un número de iteraciones que conduce a una simulación de distribución normal. Especialmente los sistemas de cálculo de riesgos y los modelos de previsión utilizan mucho este método.

Para crear una simulación de Montecarlo, necesitamos tomar las variables clave de un problema y formar una distribución de probabilidad tras alcanzar un número de muestras. Como ejemplo, veamos un modelo de juego de dados en 6 fases.

Supongamos que tiramos 3 dados 3 veces y que todos los dados tienen 6 caras.

Digamos que las reglas son

• Si el total de dados es 7 u 11; el jugador gana la partida.
• Si el total de dados es: 3, 4, 5, 16, 17 o 18; el jugador pierde la partida.
• Si el total de dados es cualquier otro que estos: el jugador tiene un derecho a tirar los dados de nuevo.

(Importante: No olvide que necesitamos 5.000 resultados para formar una simulación Monte Carlo. Además, una tabla de datos es muy útil mientras se generan los resultados).

Fase 1 : Tirada de dados

Lo primero que tenemos que hacer es formar un rango de datos digamos para 50 tiradas. Tenemos que utilizar la función RANDBETWEEN (1,6) para obtener nuevos resultados aleatorios cada vez que pulsemos F9 o actualicemos la página.

Necesitamos calcular la suma de los resultados totales como «resultado» en otra celda.

Fase 2: Rango de resultados

Para desarrollar los posibles resultados, necesitamos formar un rango de datos. Así, desarrollamos un rango de datos de 3 columnas. En la primera columna, tenemos los números del 3 al 18 que representan la suma de los dados totales de lanzarlos 3 veces.

3 es el número mínimo porque; 1 + 1 + 1 = 3

18 es el número máximo porque; 6 + 6 + 6 = 18

Para las casillas 1 y 2, debemos obtener los resultados como N/A porque obtener 1 ó 2 es imposible en estos cálculos.

En la segunda columna, deberíamos ver los posibles resultados después de^{la 1ª} ronda como Ganar, Perder o Volver a tirar dependiendo del resultado en la primera columna (suma del total de dados).

En la tercera columna, deberíamos encontrar los posibles resultados tras las siguientes rondas. Para ello, podemos utilizar la función IF de excel. Esto asegura el resultado final como ganamos o volvemos a tirar, etc.

Ganar o perder son los resultados finales. Tenemos que volver a tirar una y otra vez hasta obtener estos resultados.

Fase 3: Conclusiones

Ahora, debemos encontrar los resultados de 50 tiradas. Para alcanzarlo, podemos utilizar la función ÍNDICE. Gracias a ella, los resultados se alinean entre sí; por ejemplo, si sacamos un 8, tenemos que volver a tirar.

Podemos hacer uso de la función OR de excel y de la función IF incluida con en la función INDEX para crear una situación condicional. Por ejemplo, si el resultado de la ronda anterior es Ganar o Perder, el sistema debe dejar de tirar.

Fase 4: Número de tiradas de dados

Para asegurarnos de que Excel requiere una nueva tirada y añade el número de la ronda extra, podemos hacer uso de su función COUNTIF. De esta forma, podemos calcular el número de tiradas de dados necesario para llegar a una conclusión como Ganar o Perder.

Fase 5: Simulación

Ahora, vamos a calcular los resultados de diferentes simulaciones. Recuerde que necesita 5000 simulaciones para una simulación Monte Carlo.

Necesitamos 3 columnas. En la primera, tenemos la cifra 5000. En la segunda columna, necesitamos los resultados después de 50 tiradas. Por último, en la tercera columna, necesitamos el número de tiradas de dados antes de obtener los resultados finales Ganar o Perder.

Así que ahora, necesitamos crear una tabla de análisis de sensibilidad. Podemos generarla utilizando una tabla de características o una tabla de datos. En esta tabla de análisis de sensibilidad, pondremos los números de eventos de 1 a 5000. Podemos poner estos resultados en cualquier celda que tenga suficientes celdas vacías a continuación. Hacemos esto para no dañar ninguna fórmula en otras celdas.

Fase 6: Probabilidad

Ahora, ¡nos merecíamos obtener los resultados definitivos! Esta es la fase para calcular los resultados de Ganar o Perder. Utilizamos la función COUNTIF de excel para asegurarnos de que la fórmula cuenta el número de Ganar o Perder y luego lo divide por el número toal de evento, que es 5000. Por lo tanto, obtenemos el resultado de que la probabilidad de obtener un resultado de Ganar es del 73,2% y la probabilidad de obtener un resultado de Perder es del 26,8%.

Capítulo 3: Generador de números aleatorios Plantilla Excel:

Como ya sabrá, Excel dispone de funciones estadísticas para las distribuciones de probabilidad. Estas funciones se pueden utilizar en combinación con la función RAND; como S.DIST , T.INV o GAMMA.IMV etc.

Si prefiere utilizar nuestras plantillas de generador de números aleatorios, podrá generar números según el tipo de distribución que haya seleccionado en una hoja preparada previamente. Puede crear la lista fácilmente según las características que desee que tenga y también puede ordenarla o aleatorizarla tantas veces como desee, también puede barajarla con los caracteres que desee como letras, números, palabras, etc. ¡Un sistema muy sencillo, fácil y bastante útil!

Para conocer nuestra plantilla Generador aleatorio lista para usar

¿No cree que estas plantillas se adapten a sus necesidades? Puede hacer uso de nuestros servicios personalizados para que le preparemos plantillas o informes más complejos.