Contador de aciertos en Euromillón.

Un lector del blog me planteó un problema curioso, cómo descubrir el número de aciertos en una plantilla respecto a una combinación de números. Hablaba del juego del Euromillón.
Lo que llamó mi atención es la manera curiosa de seleccionar de los números, y es que sobre una plantilla marcaban con un color determinado los dígitos elegidos:

...Con unos amigos del trabajo hemos creado una peña para jugar a Euromillones. El problema viene a la hora de comprobar los aciertos, ya que somos muchos y es muy tedioso ir mirando una a una cada apuesta. Es por ello que me he tomado la libertad de enviarle una hoja de calculo en la que podrá ver las combinaciones de cada apuesta (marcadas en amarillo)...

Aunque nuestro amigo envió una hoja de cálculo con varias 'apuestas', para simplificar la explicación lo resolveré para una sóla.
Veamos nuestra hoja de cálculo ('datos) y nuestra plantilla:

haz click en la imagen

Observemos como la elección de números se hace coloreando el fondo de la celda de un color amarillo.
Para resolver esta cuestión emplearé algunos conceptos ya conocidos, como son Asignar nombres a rangos y Definir funciones personalizadas.
Lo más importante es determinar cuáles son los números elegidos para poder trabajar sobre ellos, puesto que lo único que define un dígito elegido es el color de fondo de la celda, crearemos una UDF o función personalizada que transforme una celda amarillo en un número. La función creada se llama colorcelda, y el código VBA que la genera es:

'Función personalizada que devuelve el valor en la celda
'sólo si el color de fondo de la celda es amarillo.
Function colorcelda(celda As Range)

colorcelda = celda.Interior.ColorIndex
If celda.Interior.ColorIndex = 6 Then
colorcelda = celda.Value
Else: colorcelda = 0
End If
End Function

En una hoja diferente ('datos2) replicamos la plantilla original, pero en lugar de los dígitos del 1 al 50 o del 1 al 9 para los números estrella, introducimos la nueva función colorcelda:

Observamos como sólo tenemos valores para los números elegidos, para el resto el valor es cero.
El paso final es el más sencillo. En una tercera hoja ('conteo) disponemos una lista de números del 1 al 50 y un segundo grupo del 1 al 9.
Como paso previo debemos asignar un nombre a algunos rangos de nuestras hojas ya creadas:
cuadro1 =datos2!$C$10:$M$26
estrella1 =datos2!$C$28:$K$32
ganador2 =datos!$BT$4:$CD$6
ganador5 =datos!$AE$4:$BF$6

En la columna B, siguiente al listado ordenado de dígitos, añadimos la siguiente función:
=SI(CONTAR.SI(INDIRECTO(A$1);A2)>=1;A2;0)
que devuelve la combinación de números elegidos. En la columna C con una nueva función:
=CONTAR.SI(ganador5;B2)
que encuentra y cuenta las coincidencias con la combinación ganadora.

Sumamos las coincidencias o aciertos de ambos grupos:

Y para visualizar en la hoja principal 'datos los aciertos vinculamos con esta última celda:
="Aciertos "&INDIRECTO(DIRECCION(64;COINCIDIR("Cuadro"&B8;conteo!$A$1:$BX$1;0)+2;;;"conteo"))

haz click en la imagen

Adjunto fichero para mejor análisis.

AYUDA PE�A EUROMIL...

Hosted by eSnips