Cómo generar números aleatorios con Excel
martes, febrero 14, 2006
La función ALEATORIO (RAND en la versión inglesa) de Excel genera números aleatorios. Esta función no tiene argumentos y es recalculada nuevamente cada vez que se produce un cambio en la hoja, excepto que el modo de cálculo sea "manual".
Los números que produce ALEATORIO (RAND) van de del 0 al 1.
En esta hoja usamos la fórmula =ALEATORIO() en el rango A2:A11
Si queremos producir una serie de números aleatorios enteros, debemos combinar la función ALEATORIO con la función REDONDEAR (ROUND), o con alguna otra función de este tipo como REDONDEAR.MAS o REDONDEAR.MENOS.
En esta hoja, usamos la fórmula combinada =REDONDEAR(ALEATORIO()*100,0) para producir números aleatorios entre 0 y 100
Si queremos producir números aleatorios que se encuentren en un rango entre dos números, digamos entre 12 y 88, podemos usar la fórmula
=REDONDEAR(ALEATORIO()*(88-12)+12,0)
Con esta técnica podemos generar, por ejemplo, números aleatorios para la lotería. Aquí, por ejemplo, el Loto sortea 6 números entre el 1 y el 36. El problema de esta fórmula es que puede generar números duplicados, como en este ejemplo
Supongo que hay más de una técnica para superar este problema. Mencionaré aquí dos de ellas.
La primera es, simplemente, recalcular la hoja (presionar F9) hasta lograr una serie sin números repetidos.
La otra consiste en condicionar la creación de los números aleatorios. Esta técnica consiste en generar una referencia circular intencional, para lo cual es necesario que la opción Iteración en Opciones/Cálculo esté activada (pueden bajar el archivo del ejemplo con las fórmulas 
aqui)
La fórmula que usamos en el rango A2:A11 es
=SI(SUMA($B$2:$B$11)<>10,REDONDEAR(ALEATORIO()*(36-1)+1,0),REDONDEAR(ALEATORIO()*(36-1)+1,0))
en el rango B2:B11 usamos la fórmula =CONTAR.SI($A$2:$A$11,A2). Esta fórmula controla si hay números duplicados. Si el resultado es 1, el número aparece una sola vez.
La fórmula en el rango A2:A11 controla si todos los resultados en el rango B2:B11 no suman 10, es decir que hay algún numero duplicado; en ese caso calcula un número aleatorio. Si la condición lógica no se cumple, vuelve a calcular. Como ven hay aquí una referencia circular. Como hemos activado la opción "Iteración", Excel intentará encontrar la serie de 10 números que cumplan con la condición. Si no la encuentra, deberemos pulsar nuevamente F9 (recalcular) hasta encontrar la solución.
Esta técnica fue sugerida por John Walkenbach en su libro "Microsoft Excel 2000 Formulas".
Categorías: Funciones&Formulas_, Varios_
52 comments:
Antes de nada, enhorabuena por tu blog. Es de los más útiles y prácticos que he encontrado desde hace tiempo por internet.
He probado el ejercicio que comentas sobre la generación de números aleatorios usando una referencia circular y no me acaba de funcionar. Es más, el control de números repetidos "tontea" de vez en cuando indicando con un "2" repeticiones que no existen. Además el proceso NUNCA se termina hasta que no acaban todas las iteraciones.
He probado a descargar tu archivo por si había cometido un error pero con el tuyo me pasa lo mismo.
Gracias por el blog y espero que nos puedas aclarar si hay realmente un problema o es que a mi se me escapa alguna cosa por inexperto.
Saludos
Hola Suri, para que el modelo funcione es imprescindible que el modo iteración este seleccionado (en el menú Herramientas--Opciones--Calcular), tal como lo muestro en la nota.
He notado que al descargar el archivo esta opción no está activa, y de ahí el problema.
Todo lo que tienes que hacer es abrir el menú Herramientas y señalar la opción Iteración.
Gracias por los conceptos.
Hola de nuevo,
No me gustaría ser pesado ;-) pero me tiene muy intrigado el tema este. Ya había activado la iteración con el número max. de iteraciones a 1000. No sé si tendrás un par de minutos para entretenerte, pero si puedes prueba pulsando varias veces con el F9 y verás que de vez en cuando el control da resultados falsos. Ejemplo:
Aleatorio: 10-27-31-31-15-25
y en el control aparece:
Control: 02-01-01-02-01-01
Como se ve en el ejemplo al número 10 le asigna un control 2 cuando no está repetido y a uno de los números 31 (que esté repetido) le asigna un 1 como si no lo estuviese.
Un saludo Jorge, y espero que entiendas mi curiosidad.
Por cierto, trabajo con la versión 2003.... lo digo por si puede tener algo que ver.
Hola Suri, no hay ningún problema. El modelo no está construido, y no puede, generar una solución en cada cálculo.
Simplemente no siempre 1000 iteraciones son suficientes para encontrar un resultado. Después de 1000 iteraciones Excel interrumpe el cálculo, y este es motivo que a veces aparece un 2 en la columna de control y no hay ningún duplicado (o como tu encontrastes, los 2 del control no coinciden con los duplicados).
Lo que puedes hacer es aumentar el número de iteraciones en el menú Herramientas--Opciones.
Otro punto a tomar en cuenta es la relación entre la cantidad de números únicos buscados y el pool de números (en nuestro caso 6 - 36). Si buscáramos 6 números únicos de un pool de 100, tendríamos mejores resultados.
Hola, Jorge,
Primero, quiero felicitarte y agradecerte por el contenido de este blog, la verdad es que hace mucho tiempo venía buscando algo así, y lo estoy leyendo de principio a fin. Hasta ahora, me ha ayudado muchísimo... cuántos de los problemas que comentas acá se me presentaron alguna vez y los tuve que resolver con macros...
Sólo quería hacer una observación respecto a este post: me parece que es más correcto utilizar la función ENTERO que REDONDEAR, porque la primera desfavorece a los valores extremos. Por ejemplo, si queremos simular un dado, y usamos la función:
=REDONDEAR(ALEATORIO()*6)
El resultado de ALEATORIO()*6 está acotado entre 0 y 6. Para que el redondeado diera 0, el interior del paréntesis debiera dar entre 0 y 0,4999; en cambio, para que diera 1, tendría que caer entre 0,5 y 1,4999... es decir, el 1 es el doble de probable que el cero usando esta función... y el 6 tendría el mismo problema que el 0.
Para que el dado fuera equiprobable, una solución sería usar la siguiente función:
=ENTERO(ALEATORIO()*6+1)
Me parece que la función ALEATORIO() nunca entrega un 1, pero si ese riesgo existe, ahí se puede solucionar usando la función dentro de un SI() con referencia circular, como en el ejemplo que das tú.
Espero que te sirva de algo este aporte, saludos y de nuevo muchas gracias por compartir tus trucos de Excel!
O más fácil, usando la misma función REDONDEAR(), se puede hacer equiprobable el dado sumando 0,5:
=REDONDEAR(ALEATORIO()*6+.5)
En el ejemplo que puse en el post anterior había un pequeño error, estaba trabajando con un dado que entregaba valores entre 0 y 7, pero la idea de fondo de que los valores no son equiprobables es lo que me interesaba transmitir.
Saludos!
hola bastante interesante el blok, Disculpa pero tenia una duda, que tipo de congruencia es la que uliliza excel,o las funciones q mensionaban, me imagino que se generan distintas cantidades aleatorias en la hoja de excel porque el programa selecciona distinta semilla, pero q metodo utliza? gracias y espero no ocacionar un dolor de cabeza con mi duda ok! saludos!
Hola Juanjo,
puedes leer sobre el tema en la base de datos de conocimientos de Microsoft, en esta nota y tambien en esta
Hola Jorge,
Sería una simple pregunta... ¿sabrías si hay alguna fórmula que se pueda utilizar para sumar números aleatoriamente entre sí?
Me explico, tengo varios números y necesito saber cuales, sumados entre sí aleatoriamente, dan un mismo resultado.
Espero haberme explicado bien porque ni yo misma me entendí.
Saludos.
Hola,
siempre son así de simples tus preguntas?
La única forma posible de hacerlo, en mi opinión, es con Vba (macros).
Tal vez se puede hacer algo más bien elemental usando al función ALEATORIO y recalculando la hoja (F9) hasta obtener el resultado deseado.
hola,
no entiendo el comportamiento de la formula que tienes iterando, porque la forma en que la planteas mo asigna ningun criterio de parada, siempre que se recalcula la hoja, se cumple el número máximo de iteraciones, y no se cumple la condicion inicial que quieres
Hola,
el criterio de parada está definido por el cálculo circular. Las fórmula en la celda de la columna A se refiere a sí misma ya que la fórmula en la celda de la columna B se refiere a la cuenta de valores de A. El criterio de parada es que se cumpla =CONTAR.SI($A$2:$A$11,A2) = 10
Si esto se cumple antes del número de iteraciones que hayas definido, el cálculo se detiene. Si no se cumple hay que correr el cálculo nuevamente.
Jorge sería bueno mencionar que la función en cuestión genera número aleatorios según una distribución normal de media 0.5, por lo que simular una lotería, que se distribuye como una uniforme, daría resultados sesgados.
Hola Jorge,
queria saber por favor si hay alguna manera de decirle a la funcion aleatorio el numero de numeros a generar (sin copiar la formula n veces). Por ejemplo yo quisiera generar 19 numeros aleatorios entre 5 y 25, como hago?
Gracias
Tu mejor opción es usar el complemento de Laurent Longre que menciono en esta nota sobre número aleatorios.
Hola Jorge, Te felicito por tu blog, Vine aquí buscando algo de números aleatorios pero me encontré algo muy interesante sobre manejo de fechas...
Pero bueno lo que busco es una fórmula que utilice métodos congruenciales (aditivo, multiplicativo y Lineal) para generar los números aleatorios. Tengo entendido que se puede hacer así por celdas y arrastrando la fórmula tantas veces como números se deseen, sin necesidad de hacer macros. Pero la verdad no tengo idea de cómo se hace...
Qué sabes tu????
Hola Claudi
lo único que se me ocurre sugerirte es que investigues las posibilidades de la herramienta Random Number Generation del complemento Analysis Toolpak.
Si tienes instalado el complemento, verás en el menú Herramientas la opción Data Analysis. Al abrir esta opción verás la herramienta de genración de números aleatorios.
hola espero que estes bien primero que nada en realidad no se si mi pregnta tenga algo que ver con el tema de tu blog. que esta excelente pero ahi va, quiero saber si es posible predecir en exccel triples de loteria pueden salir al dia siguiente si se insertan resultados de dias anteriores y de ser asi como lo consigo, espero una respuesta gracias y que pases unas felices pascuas
Hola Esteban
si se pudiera predecir con Excel los números de la lotería hace rato que sería millonario y no me pasaría el tiempo escribiendo este blog!
No hay ninguna ley que permita predecir qué números serán sorteados.
He leido todo lo que has comentado sobre numero aleatorios. Te expongo mi caso, si tengo una muestra de 50 y quiero escoger al azar sólo 12 de esa muestra, como podria hacerlo. Si hago un aleatorio de los 50 números (p.ej. entre 0 y 1, y este 1 seria coger la muestra) en la mayoria de las veces los 1 que salen son mayores que las muestras que yo quiero coger.
Podrías poner en un rango de 12 celdas una fórmula con ALEATORIO.ENTRE (RANDBETWEEN) usando como argumento la cota inferior y superior de los números aleatorios que quieres usar.
QUE TAL!
PUES MUY BUEN BLOG, MUY UTIL.
HACE ALGÚN TIEMPO ANDO TRATANDO DE CREAR NUMEROS ALEATORIOS SIN REPETICIÓN CON EXCEL 2007 PERO NO HE PODIDO, NO SE QUE ME HAGA FALTA.
BASICAMENTE LO QUE QUIERO ES ASIGNARLE UN NUMERO ALEATORIO A UNA LISTA DE NOMBRES.
Fijate en esta nota
Hola Jorge!
Muy interesante el blog. como podria hacer para extraer 40 números aleatorios sin repetición de nímeros que van del 1 al 80?
Desde ya muchas gracias!!
Andrea,
te sugiero fijarte en esta nota
hola jorge graias por tu blog interesantisimo,, sobre la pregunta del sorteo de la loto, mi pregunta es por supuesto, no esperar los numeros que van a ser sorteados, pero si, combinaciones posibles a salir en la loto a partir del metodo aleatorio, en otras palabras posibles probabilidades de numeros a salir, ojala ,me entiendas soy novato en esto, gracias
Victor,
dependiendo de la cantidad de números a acertar del conjunto de números posibles, las combinaciones posibles van de millones a decenas de millones.
Los resultados de cada sorteo son independientes de los sorteos anteriores, por lo que no hay forma de decidir que números tienen mayor o menor probabilidad de salir.
Gracias, busque en tooodos lados y solo aqui lo encontre, gracias, gracias gracias, no pense que fuera tan facil generar numeros aleatoris en excel!!!!!!!!!
EXCELENTE PAGINA, OS FELICITO!!!!!!
Hola Jorge, antes que nada t felicito por tu Blog, esta muy bueno...
Pero yo tengo un dilema y me gustaria saber si me puedes ayudar, quiero listar todas las posibles combinaciones que se puedan crear al extraer una muestra de una población, más precisamente: en una población de tamaño 10 selecciono 3 muestras aleatorias.. quiero listar todas esas 120 combinaciones posibles, sabes como?
Sólo se puede hacer con macros.
Hola Jorge, excelente blog! Quisiera saber si se puede hacer combinaciones del numero 5481 por ejemplo, cuantos numeros combinando esos digitos puedo formar y cuales serian, Gracias de antemano por su ayuda y guia.
Juan,
no me queda claro si se trata de combinaciones o permutaciones pero Excel te ofrece funciones para ambos cálculos PERMUTACIONES y COMBINAT.
Lo que no se puede hacer con una función es generar los números en si mismos. Para eso tendrría que usar Vba (macros)
Buenas tardes, me gustaría saber como debo proceder para realizar un aleatorio de fechas:Por ejemplo fechas aleatorias entre 10/10/1990 y 20/03/2010.
Desde ya muchas gracias
En Excel las fechas son números enteros que pertenecen a una serie donde el 1 representa la fecha 01-enero-1900, el 2 el 02-enero-1900 y así sucesivamente. Lo que hace que estos números aparezcan como fechas es el formato de la celda.
Así que en tu caso la fórmula sería
=ALEATORIO.ENTRE(33156,40257)
Buenos días Jorge!
Sobre los números aleatorios, tengo una duda que no he encontrado en tu blog.
Lo que quiero hacer es obtener en una columna una cantidad de resultados concreta por tipo de resultado, pero de forma aleatoria para los participantes, me explico.
Tengo 163 participantes y quiero darle aleatoriamente un 1 a 9 personas (a las que sea), un 2 a 12 personas, un 3 a 22 personas y así sucesivamente hasta conseguir una cantidad concreta para cada valor en el total de la columna.
Es posible hacer esto?
Muchisimas gracias por tu ayuda!
La única forma que se me ocurre es con una macro.
Tienes algun ejemplo publicado de dicha macro?
Gracias!!
No, habría que programarla en particular para tu caso.
hola que tal Jorge.
Quisiera saber si es posible usar la funcion (aleatorio) pero que me de numeros de una lista, por ejem.
tengo en una lista 560, 1050, 130, y quiero que aleatoriamente seleccione de esos tres numeros.
Gracias.
Con Excel 2007/10 puedes usar
=ELEGIR(ALEATORIO.ENTRE(1,3),560,1050,130)
Con Excel 97-2003
=ELEGIR(REDONDEAR((ALEATORIO()*2+1),0),560,1050,130)
tengo 5 celdas, en cada celda un nombre de persona y quiero que en una celda aparte me salga al azar uno de esos nombres. tengo el excel 2003.
Suponiendo que los nombres están en el rango A1:A5,
en Excel 2003 usarías
=INDICE(A1:A5;ENTERO(ALEATORIO()*(5-1)+1))
En Excel 2007/10 podemos usar
=INDICE(A1:A5;ALEATORIO.ENTRE(1;5))
Felicidades jorge, por este blog. Tengo un problema, no sé que formula aplicar para optimizar barras de aluminio, teniendo distintas medidas.
Recibe un saludo.
¿No te parece que la consulta "cómo optimizar barras de aluminio" es un poco vaga? ¿Optimizar qué? ¿Costo, cantidad producida, superficie de alamcenamiento, costo de tranporte...?
De todas maneras, Excel cuenta con la herramienta Solver para solucionar problemas de optimización.
SALUDOS JORGE,EXCELENTE BLOG TE FELICITO,SOY AGRONOMO Y NORMALMENTE USO HOJAS DE CALCULOS PARA MIS BASES DE DATOS,DENTRO DE LAS CUALES USO LA FORMULA SUMAR SI CON EL CRITERIO >=;<=; =; PERO AHORA NECESITO SABER QUE FORMULA DEBO USAR PARA SUMAR ENTRE RANGOS DETERMINADOS.POR LO MENOS MI RANGO ES UN INVENTARIO DE PRODUCTOS DE DIFERENTES PRESENTACIONES(A1:A20),CRITERIO SERIA PRODUCTOS ENTRE 4 10KG; Y MI RANGO DE SUMA SERIA LA CANTIDAD EN INVENTARIO DE CADA UNA DE LAS PRESENTACIONES(B1:B20)...EXITOS Y ESPERO QUE ME ACLARES LA DUDA
Hola Robert,
normalmente hubiera eliminado tu comentario (por favor, fijate en lo que pongo en el enlace Ayuda, en la parte superior de la plantilla).
Pero dejando la etiqueta de lado, fijate en esta entrada del blog donde muestro como hacer cálculos con varias condiciones. La nota fue escrita en los años del reinado de Excel 2003. Si bien la técnica es aplicable también hoy en Excel 2007 y 2010, éstas versiones tienen también la función SUMAR.SI.CONJUNTO
Una sugerencia, para calcular valores aleatorios entre un rango de números como el del ejemplo (entre 12 y 88) es mas fácil usar aleatorio.entre(12;88)
Hola Jorge.
Primero felicitarte porque esta excelente tu blog y esta recomendación me ha ayudado mucho, ahora una consulta estoy haciendo una lista de 500 personas de un total 1200 que formula puedo utilizar para sacar esos 500 números de forma aleatoria sin que se repitan. Gracias
Fijate en la técnica que muestro en esta nota.
Otras ténicas en esta otra nota.
Me salvaste la vida, gracias a tu post ya se como hacer la tabla que tanto dolor de cabeza me ha ocasionado. Saludos
Hola Jorge, felicidades por el blog.
Queria preguntarte como se puede hacer una tabla de excel con numeros aleatorios del 1 al 49 escogiendo 6 , pero teniendo en cuenta los 6 numeros aparecidos en los sorteos anteriores, es decir que a la hora de elegir esos 6 numeros tenga en cuenta todo el historico de los numeros que han salido y poder actuarlizarlo cada semana con los resultados que vayan saliendo.
Se puede hacer, pero la forma de hacerlo no puede exponerse en el marco de un comentario. Pero como sospecho que estamos hablando de la lotería (en sus distintas variantes), me permito hacer tres observaciones:
1 - en la Internet hay un sinnúmero de programas y modelos que tratan de "adivinar" los resultados de la lotería en base a todo tipo de sistemas (números calientes, fríos, tibios, etc.)
2 - ninguno funciona. De funcionar el "descubridor" no lo compartiría con nadie ya que eso reduciría sus ganancias.
3 - Hasta donde recuerdo las clases de estadística, cada sorteo es independiente de los anteriores. En mi país se dio el caso que en dos semanas consecutivas se sortearon exactamente los mismos seis números!
Publicar un comentario