Desglosando pares combinatorios

Hace unos días publiqué como obtener con Power Query un listado de las posibles combinaciones sin repetición a partir de un listado de elementos (ver aquí); hoy desarrollaremos con funciones una fórmula que aplica esas combinaciones generales a un caso concreto.
Supongamos un registro de veinte tiradas o sorteos de bonoloto, primitiva, etc... de seis elementos entre 1 y 49.
El objetivo es determinar cuales son las combinaciones (si es que existen) que más se repiten de entre esos veinte sorteos...

Vemos en el rango B4:G23 el detalle de los veinte sorteos (ordenados de izquierda a a derecha!!). Y pretendemos obtener estadísticas de:
- los números que más aparecen,
- los pares a-b más repetidos,
- las ternas a-b-c-d.
- los grupos de 4 elementos a-b-c-d,
- para finalmente hacer la misma operación con los grupos de 5 elementos a-b-c-d-e
Para ello con nuestra consulta vista en el post comentado obtenemos los listados de posiciones combinadas sin repetición...

Esas cinco tablas de la imagen anterior tienen los siguientes nombres:
- Tbl1en1
- Tbl2en2
- Tbl3en3
- Tbl4en4
- Tbl5en5
Tablas necesarias para automatizar el desarrollo buscado...

En la celda H1 he incluido una validación de datos tipo lista con elementos permitidos: 1 en 1; 2 en 2; 3 en 3; 4 en 4; 5 en 5
Y en H3 insertamos la fórmula desbordada:
=TRANSPONER(INDIRECTO("Tbl"&SUSTITUIR($H$1;" ";"")&"[Combinaciones]"))
que nos permite recuperar dinámicamente los elementos de nuestras combinaciones de 6 elementos tomados de n en n

Con la estructura desarrollada podemos incluir nuestra fórmula para obtener para cada sorteo cuales son esos pares, ternas, etc. de combinaciones.
Así pues en H4 añadimos:
=SI.ERROR(UNIRCADENAS("|";1;
INDICE($B4:$G4;
1;
COINCIDIR(--EXTRAE(SUSTITUIR(H$3;"|";"");SECUENCIA(1;LARGO(SUSTITUIR(H$3;"|";"")));1);$B$3:$G$3;0)));
"")

La clave de esta fórmula reside en la función COINCIDIR empleada:
COINCIDIR(--EXTRAE(SUSTITUIR(O$3;"|";"");SECUENCIA(1;LARGO(SUSTITUIR(O$3;"|";"")));1);$B$3:$G$3;0)
que nos sirve para indicar, basándose en el encabezado de B3:G3, y en la combinación de elementos, a qué números del sorteo corresponden.
Como 'valor buscado' de coincidir tenemos la fórmula:
--EXTRAE(SUSTITUIR(O$3;"|";"");SECUENCIA(1;LARGO(SUSTITUIR(O$3;"|";"")));1)
donde indicamos que recuperaremos cada posición (de nuestras combinaciones) exluyendo el caracter 'barra vertical |'.
El primer argumento de EXTRAE: SUSTITUIR(O$3;"|";"") retorna, a partir de la combinación de posiciones: 1|2|3 la cadena 123 sobre la que trabajar.
El segundo: SECUENCIA(1;LARGO(SUSTITUIR(H$3;"|";""))) devuelve una matriz de columnas del mismo ancho que elementos hayamos combinado, por ejemplo, si la combinación fuera de 3 en 3, la matriz sería siempre: {1\2\3}
El tercer argumento será siempre 1, ya que solo queremos extraer una única posición...
Acabamos convirtiendo en valor el texto devuelto por EXTRAE aplicándolo el doble menos: --EXTRAE(...)
Todo este COINCIDIR(...) nos devuelve una matriz de columnas con las numeraciones representadas en los encabezados... por ejemplo, del texto de la celda con valor 2|4|6 obtendríamos la matriz {2\4\6}

Esta matriz en el contexto de INDICE me permite recuperar los números del sorteo que correspondan a dichas posiciones de columnas...

Copiaremos la fórmula al rango H4:AB23.

Con nuestro detalle de pares desglosado para cada sorteo podemos aplicar alguna de las técnicas explicadas en este post 'Fórmulas desbordadas: Pasar de matriz a vector' (con Power Query o con funciones)... -leer más-
Quedándonos un listado ordenado de mayor a menor por el número de apariciones de esos veinte sorteos.

Para obtener las distintas estadísticas bastará ir cambiando en la celda H1 validada entre las distintas opciones...

Esta operación es algo que constantemente me solicitan los amantes del azar... y los que piensan que con análisis de históricos se puede vencer a la suerte ;-)
Para todos ellos.