por Ya llevo un tiempo sin colgar ninguna entrada … el motivo es que estoy bastante hasta arriba de trabajo y no tengo mucho tiempo libre para dedicárselo al blog.
Pero bueno, hoy voy a mostrar un pequeña aplicación que sirve para generar números aleatorios y que seguro os parece una buena idea a la hora de tener que pensar, por ejemplo, los números de la lotería.
Para realizarlo utilizaremos la hoja de cálculo como base para generar los números y un userform para mostrarlos como si fuese un programa.
Lo primero que vamos a hacer es confeccionar la hoja con los números, por lo que vamos a utilizar tres funciones:
«Aleatorio», «Aleatorio.entre» y «Jerarquía»
Como siempre, una explicación de cada una de las funciones y luego el caso práctico:
- Función Aleatorio: Devuelve un número real aleatorio mayor o igual a 0 y menor que 1, distribuido uniformemente. Cada vez que se calcula la hoja de cálculo, se devuelve un número real aleatorio nuevo.
Sintaxis: ALEATORIO( ) - Función Aleatorio.entre: Devuelve un número entero aleatorio entre los números que especifique. Devuelve un nuevo número entero aleatorio cada vez que se calcula la hoja de cálculo.
Sintaxis: ALEATORIO.ENTRE(inferior; superior)
La sintaxis de la función ALEATORIO.ENTRE tiene los siguientes argumentos:
Inferior Obligatorio. El menor número entero que la función ALEATORIO.ENTRE puede devolver.
Superior Obligatorio. El mayor número entero que la función ALEATORIO.ENTRE puede devolver. - Función Jerarquía: Devuelve la jerarquía de un número en una lista de números. La jerarquía de un número es su tamaño en comparación con otros valores de la lista.
Sintaxis: JERARQUIA(número;referencia;[orden])
La sintaxis de la función JERARQUÍA tiene los siguientes argumentos:
Número Obligatorio. El número cuya jerarquía (clasificación) desea conocer.
Referencia Obligatorio. Una matriz de una lista de números o una referencia a una lista de números. Los valores no numéricos se pasan por alto.
Orden Opcional. Un número que especifica cómo clasificar el argumento número
Después de definir las funciones a utilizar, vamos a pensar en el juego del que queremos que Excel nos genere números aleatorios. Por ejemplo, un juego que tiene 49 casillas para apostar 6 números del 1 al 49. Pues bien creamos una serie de número aleatorios hasta del 1 al 49 en una columna de Excel, y de la siguiente forma: =ALEATORIO()*100, es necesario multiplicarlo por 100 dado que el resultado es del 0 al 1, por lo tanto de esta forma tendremos número enteros.
Una vez que tengamos la columna con los números aleatorios generados, ahora tendremos que idear la forma de que esos números NO se repitan, pues entonces no nos serviría el modelo. Para ello vamos a utilizar la función Jerarquía. En la columna de al lado escribimos la siguiente fórmula:
=JERARQUIA($A3;$A$3:$A$51)
Os debería quedar así:
En rojo está la función jerarquía que contiene las primeras posiciones no repetidas del listado de aleatorios de la izquierda, así aseguramos que no saldrán repetidos los números.
Y podemos seguir haciéndolo con diferentes tipos de juegos, con estrellas, con más o menos números, etc. Pero hay un detalle que os quiero comentar, si lo que tenemos que generar son números de 0 a cualquier valor, tendremos que utilizar la función aleatorio.entre (). De hecho, en este ejemplo utilizo esa función para generar aleatorios entre 0 y 9 (de el Gordo de la primitiva 😉 Pero esta fórmula solo es válida para Excel 2007 y superiores, así que si tenéis 2003 os va a generar un error el programa.
Os muestro ahora los cálculos y los números aleatorios para los tres juegos propuestos.
Los número en rojo serán los números que vamos mostrar a través del userform. Ahora solo queda mostrar los resultados en un formato que sea interactivo y a la vez proporcione una usabilidad agradable, para ello he usado un userform, aunque hay otras formas de hacerlo.
Primero debemos tener en cuenta que los datos de la hoja1 son la base de la información, por lo tanto debemos ocultarla para que no pueda ser manipulada. Vamos al editor de VBA y en el módulo «Thisworkbook» ponemos la escribimos lo siguiente.
Sub workbook_OPEN()
'al abrir el generador de núemros la hoja1 se ocultará
Worksheets("Hoja1").Visible = xlSheetVeryHidden
'y mostrará el userform1
UserForm1.Show
End Sub
Ahora tenemos que crear el userform1 que ofrecerá la información, hay muchas formas de hacerlo, yo he optado por varias solapas, cada una con un juego diferente:
Con la información de la hoja1 y los textbox del userform vamos introduciendo esta macro, que os voy a ir comentando en verde (explico el primer juego solamente, los otros son iguales y siguen la misma lógica):
Al pulsar en el botón «Suerte» de la primera solapa se realizan las siguientes instrucciones:
Private Sub CommandButton1_Click()
'PRIMITIVA
'desactivamos la actualización de pantalla para que no parpadee con cada cálculo.
Application.ScreenUpdating = False
'Mostramos la hoja1 que anteriormente habíamos ocultado.
Worksheets("Hoja1").Visible = xlSheetVisible
'seleccionamos la hoja1.
Worksheets("Hoja1").Select
'activamos la celda “a1? de la hoja1 para que la función aleatorio muestre los números.
Worksheets("Hoja1").Range("A1").Select
ActiveCell.FormulaR1C1 = " "
'mostramos los números de la hoja1 en cada uno de los textbox.
TextBox1 = Worksheets("Hoja1").Range("B3").Value
TextBox4 = Worksheets("Hoja1").Range("B4").Value
TextBox3 = Worksheets("Hoja1").Range("B5").Value
TextBox2 = Worksheets("Hoja1").Range("B6").Value
TextBox6 = Worksheets("Hoja1").Range("B7").Value
TextBox5 = Worksheets("Hoja1").Range("B8").Value
'volvemos a ocultar la hoja1.
Worksheets("Hoja1").Visible = xlSheetVeryHidden
End Sub
Private Sub CommandButton2_Click()
'EUROMILLONES
Application.ScreenUpdating = False
Worksheets("Hoja1").Visible = xlSheetVisible
Worksheets("Hoja1").Select
Worksheets("Hoja1").Range("A1").Select
ActiveCell.FormulaR1C1 = " "
TextBox7 = Worksheets("Hoja1").Range("F3").Value
TextBox10 = Worksheets("Hoja1").Range("F4").Value
TextBox9 = Worksheets("Hoja1").Range("F5").Value
TextBox8 = Worksheets("Hoja1").Range("F6").Value
TextBox12 = Worksheets("Hoja1").Range("F7").Value
'ESTRELLAS
TextBox19 = Worksheets("Hoja1").Range("F10").Value
TextBox11 = Worksheets("Hoja1").Range("F11").Value
Worksheets("Hoja1").Visible = xlSheetVeryHidden
End Sub
Private Sub CommandButton3_Click()
'EL GORDO
Application.ScreenUpdating = False
Worksheets("Hoja1").Visible = xlSheetVisible
Worksheets("Hoja1").Select
Worksheets("Hoja1").Range("A1").Select
ActiveCell.FormulaR1C1 = " "
TextBox14 = Worksheets("Hoja1").Range("J3").Value
TextBox17 = Worksheets("Hoja1").Range("J4").Value
TextBox16 = Worksheets("Hoja1").Range("J5").Value
TextBox15 = Worksheets("Hoja1").Range("J6").Value
TextBox18 = Worksheets("Hoja1").Range("J7").Value
'MATRIZ
TextBox20 = Worksheets("Hoja1").Range("I3").Value
Worksheets("Hoja1").Visible = xlSheetVeryHidden
End Sub
Por último, en cada botón de Salir ponemos el siguiente código.
Private Sub CommandButton4_Click()
Application.DisplayAlerts = False
Application.Quit
End Sub
Private Sub CommandButton5_Click()
Application.DisplayAlerts = False
Application.Quit
End Sub
Private Sub CommandButton6_Click()
Application.DisplayAlerts = False
Application.Quit
End Sub
Esto sería lo relativo al userform1, pero nos quedaría un pequeño detalle, tenemos que limpiar los datos del userform cuando salgamos de la aplicación, aunque el comando «Application.DisplayAlerts = False» ya impide que se guarde el trabajo, siempre es conveniente dejar en blanco los textbox. Para ello añadimos al modulo del libro «Thisworkbook» el código para limpiar todos los textbox:
Sub workbook_CLOSE()
'cuando cerramos el libro los datos mostrados en los textbox se borran.
'PRIMITIVA
TextBox1 = Empty
TextBox4 = Empty
TextBox3 = Empty
TextBox2 = Empty
TextBox6 = Empty
TextBox5 = Empty
'EUROMILLONES
TextBox7 = Empty
TextBox10 = Empty
TextBox9 = Empty
TextBox8 = Empty
TextBox12 = Empty
'ESTRELLAS
TextBox19 = Empty
TextBox11 = Empty
'EL GORDO
TextBox14 = Empty
TextBox17 = Empty
TextBox16 = Empty
TextBox15 = Empty
TextBox18 = Empty
'MATRIZ
TextBox20 = Empty
End Sub
El resultado es el siguiente:
Como siempre os dejo el enlace para descargar la aplicación. Importante, la hoja1 la podéis ver entrando por el programador y pulsando encima de la hoja y en propiedades Visible, elegís la opción «-1 xlsheetvisible». El programa funciona con Excel 2007 o superior, en versiones anteriores, el juego 3 (el gordo), no funcionará y dará un error.
Descarga el archivo pulsando en: GENERADOR NÚMEROS ALEATORIOS LOTERÍA
Hola Segu, soy Suso un amigo de tu cuñado Luis, él me habló de ti y de tu blog. Al cual entré ya un par de veces y puedo comprobar que tienes habilidades y cualidades tecnológicas que por lo que me comenta Luis las utilizas como ocio.Segu como bien sabes los momentos que estamos viviendo mal llamados como " CRISIS " que para mí lo que realmente es " UNA REVOLUCIÓN TECNOLÓGICA " que por primera vez en la historia los sistemas de producción y de transmisión de la información mutan al mismo tiempo, lo están cambiando todo poniendo el sistema patas arriba y a los políticos con el culo al aire sin saber que hacer y aplicando recetas antiguas que ya no funcionan, todo esto aún durará unos años más hasta que el nuevo sistema quede totalmente implantado y más de una generación obsoleta.Segu te digo todo esto porque tus habilidades tecnológicas están en el momento perfecto de la historia, si algún día quieres o te interesa lanzarte a este mundo o desarrollar una idea con base tecnológica, escalable y global yo puedo ayudarte ya que tengo contactos en ese mundillo.Saludos.
Muchas gracias Suso, pues sí todo está en cambio permanente y cada vez lo hace de forma más intensa. Este blog es un proyecto que tenía ganas de hacer, me gusta Excel y me gusta también mostrar la forma de hacer las cosas desde mi experiencia. Muchas gracias por el comentario y la ayuda, alguna idea tengo en mente y cuando empiece a trabajar en ella ya te comentaré. Saludos!! y gracias por la visita 🙂
hola soy Raquel te felicito esto esta buenísimo……gracias!
Muchas gracias Raquel 🙂 Espero que te sea de utilidad y sobre todo que tengas suerte con la combinación y salga ganadora. Saludos!!
hola soy fernando el articulo esta muy bien, pero la pregunta es: se puede hacer una formula para una reducción en la primitiva, por ejemplo para eligiendo 12 números te haga un desarrollo a 4 aciertos por ejemplo.
gracias en antemano
Hola Fernando;
Sobre la pregunta que trasladas, supongo que te refieres a que en el boleto solo hay un máximo de 12 números y solo puedes acertar cuatro de ellos. No se deben repetir ni sobrepasar el 12.
Echa un vistazo al archivo pinchando en el siguiente enlace:
https://drive.google.com/file/d/0B2MZVcPxjhyPcUxFS0d0YXVuc0k/view?usp=sharing
Espero que te sirva de ayuda. Saludos 🙂
gracias por tu amabilidad y pronta respuesta, pero no es exactamente lo que busco.
intentare explicarlo mejor. De lo que se trata es de que eligiendo 12 números me haga un desarrollo de 4 aciertos seguros (teniendo en cuenta que que tengo que tener los 6 de la combinación ganadora dentro de los 12), ya se que esto se puede hacer online, en cualquier web de la primitiva, pero lo que a mi me interesa es saber como se hace el desarrollo y si en excel se puede hacer.
gracias
Hola Fernando;
Aún no tengo claro que es lo que necesitas. Por lo que comentas para que quieres que desde un formulario puedes elegir hasta un máximo de 12 números y que te muestre 4, pero no entiendo lo de 4 aciertos seguros … te propongo que me envíes un archivo con el ejemplo, es decir, en varios pasos donde me expliques el punto de partida y el resultado final. Puedes enviarlo aquí: excelsignum@yahoo.es
Así podré verificar realmente los requerimientos y el desarrollo posterior.
Un saludo!!
ok, en cuento tenga un hueco lo preparo y te lo paso con ejemplo, es normal que así a priori resulte un poco confuso.
grcias y un saludo
ok, en cuento tenga un hueco lo preparo y te lo paso con ejemplo, es normal que así a priori resulte un poco confuso.
gracias y un saludo
Muy buen aporte, sólo una duda ¿cómo puedo cambiar el rango de números? por ejemplo yo necesito de 1 a 35
Hola Enrique,
Es sencillo, solo tienes que modificar en la «hoja1» el rango que contiene la fórmula aleatorio() y dejar números solo hasta la fila 37 (que en realidad son 35). De esta forma la fórmula «jerarquía» llegará como máximo hasta los 35.
Solo hay que recortar el rango de números de cada columna (Primitiva, Euromillones y El Gordo) y así limitarás el máximo a 35.
Saludos.
Buen artículo y Gracias por la aportación. Pero quiero ir un paso más allá. YO quiero generar números aleatorios, pero de entre unos números preestablecidos. Es decir, yo no quiero que sean entre el 1 y el 50 por ejemplo. Sino que me gustaría generar combinaciones aleatorias de 5 números de entre los números que tengo preestablecidos en una matriz. Por poner un ejemplo. Imaginemos que tengo los números: 3, 12, 15, 22, 1, 44, 4, 2, 6, 40, 8, 36 y de estos números, quiero generar combinaciones aleatorias sin repetición, pero sólo de estos doce números concretos. ¿Me he explicado? ¿Sabrías ayudarme? MIL GRACIAS!
Hola Rafa,
Pues no sería excesivamente complicado lo que indicas (siempre que la matriz de 12 números que comentas no tenga duplicados), sobre el ejemplo actual (la plantilla donde se calculan los aleatorios) habría que utilizar unas columnas adicionales y se podría realizar sin problema:
En este enlace te puedes descargar el ejemplo:
https://drive.google.com/file/d/0B2MZVcPxjhyPNkVpWnJuMnhyX2M/view?usp=sharing
Aprovechamos los códigos aleatorios pero modificamos el rango de jerarquía (en este caso hasta los 12 números) y aplicamos la fórmula en las 5 celdas siguientes, con esto ya podemos obtener una sucesión aleatoria de 5 números del 1 al 12 y que no se repiten.
Luego en una columna auxiliar numeramos del 1 al 12 (CLAVE) y en la columna siguiente añadimos los números que indicas (MATRIZ): 3, 12, 15, 22, 1, 44, 4, 2, 6, 40, 8, 36. Ahora tan solo nos hace falta aplicar la función buscarv para obtener lo que quieres (RESULTADO).
En rojo tendrás los 5 números únicos a partir de la matriz que habías difinido
Esto también se puede hacer en VBA, pero creo que así es mucho más sencillo.
Saludos.
Hola Segu, muchas gracias por tu ayuda! Me ha servido de mucho. No había caído en la solución de usar la función de Buscarv. Muy hábil e ingenioso. Eso me pasa por trasnochar tanto jejejejeje. Muchas gracias y si estoý de acuerdo, a veces para cuestiones sencillas mejor usar las funciones de excel y no complicarse con VBA, aunque en otros casos no queda más remedio que hacerlo. Hoy cuentas con un seguidor más de tu blog! Gracias por contenidos de Calidad y mantener el contacto con tu audiencia.
Saludos y aunque mis temáticas son otras, cualquier cosa en la que pueda servirte de ayuda o enlace no dudes en contar conmigo. http://www.rafaorts.com allí encontrarás mis Redes y Perfil LinkEdin por si quieres que estemos en contacto profesional. Ya te sigo a través de tu FanPage de Face!
Un saludo y que estés bien. Gracias!
Hola Rafa,
Me alegro que te haya servido el recurso!. Sí, la función buscarv en estos casos viene muy bien y resulta muy útil. Visitaré tu página web tan pronto como tenga tiempo, últimamente lo tengo bastante complicado 🙁 .
Saludos y cualquier asunto o necesidad, ya sabes!
Simplemente no hay palabra para agradecerte….
Si me escribe a mi correo te muestro que hice!!!..
Me gusta disfrutar y visitar blogs, aprecio mucho el contenido, el trabajo y el tiempo que ponéis en vuestro blog. Buscando en Google he encontrado tu web. Ya he disfrutado de varios artículos, pero este es muy ameno, es unos de mis temas favoritos, y por su calidad he disfrutado mucho. He puesto tu blog en mis favoritos pues creo que todos tus artículos son interesantes y seguro que voy a pasar muy buenos momentos leyendolos.
excelente mi amigo..
Amigo, he leido tu articulo y me parece muy interesante, a mi me gustaria saber si esta aplicacion que has hecho, podria darme como resultado 6 numeros «pronosticados» a partir de una base de datos con el historico de numeros ganadores de esta loteria.. no se si me di a entender
Para hacer lo que indicas es necesario programar un algoritmo específico para eso y desgraciadamente no tengo tiempo de hacerlo. Saludos.