jueves, 29 de octubre de 2020

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...
Desglosando pares combinatorios

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...
Desglosando pares combinatorios
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
Desglosando pares combinatorios


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)));
"")

Desglosando pares combinatorios


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.
Desglosando pares combinatorios

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.

martes, 27 de octubre de 2020

Fórmulas desbordadas: Pasar de Matriz a Vector

Hace ya algunos años ya expuse la forma, con funciones estándar, de pasar valores de una matriz n filas x m columnas a un vector de (n x m) filas y 1 columna (ver aquí).
Hoy mejoraremos aquella fórmula empleando la función Desbordada SECUENCIA
Fórmulas desbordadas: Pasar de Matriz a Vector

Comprobamos que sobre una matriz, en nuestro ejemplo, de 5 filas x 3 columnas (rango B2:D6) con un nombre definido asignado 'ndCiudades' podemos insertar en F2:
=INDICE(ndCiudades; TRUNCAR(SECUENCIA(CONTARA(ndCiudades);1;0;1/3))+1; RESIDUO(SECUENCIA(CONTARA(ndCiudades);1;0;1);3)+1)

Hubiera sido con el método tradicional:
=INDICE(ndCiudades; 1+ENTERO((FILA(A1)-1)/COLUMNAS(ndCiudades)); RESIDUO(FILA(A1)-1+COLUMNAS(ndCiudades);COLUMNAS(ndCiudades))+1)
La mejora viene en el desbordamiento que aparece al emplear SECUENCIA, la cual genera una lista de valores para recuperar datos por fila:
SECUENCIA(CONTARA(ndCiudades);1;0;1/3)
De 15 filas de alto y 1 una columna de ancho
Comenzando desde cero!!
Y con un paso de 1/3 (1 entre el número de columnas). Si lo prefieres: 1/COLUMNAS(ndCiudades)

Por otra parte para recuperar datos por columnas:
SECUENCIA(CONTARA(ndCiudades);1;0;1)
que genera una lista de 15 filas y una columna
Comenzando desde cero!!
Y con un paso de 1.
Ambas secuencias se ajustan, como se exponía en el post previo, con las funciones TRUNCAR (o ENTERO) y RESIDUO:
Fórmulas desbordadas: Pasar de Matriz a Vector

Estos vectores 'virtuales' conseguidos con SECUENCIA nos sirven para listar y recuperar con INDICE cada uno de los elementos de nuestro rango 'ndCiudades': TRUNCAR(SECUENCIA(CONTARA(ndCiudades);1;0;1/3))+1
con TRUNCAR nos quedamos con la parte entera de esos valores incrementados de 1/3 en 1/3... a cuya parte entera incrementamos con +1
RESIDUO(SECUENCIA(CONTARA(ndCiudades);1;0;1);3)+1
con RESIDUO (euqivale al resto de un cociente), al dividir entre el número de columnas del rango obtenemos la secuencia 1,2,3 correspondiente a nuestras columnas...

La ventaja de emplear SECUENCIA frente a la forma clásica es que automáticamente se desborda, ajustándose a las dimensiones de nuestra matriz.

Llegados a este punto, y como has podido comprobar en la imagen existen ciudades repetidas...
Así pués para obtener un listado de ciudades únicas ordenadas por repeticiones de mayor a menor... aplicaremos la siguiente fórmula (basada en la anterior).
Para facilitar el uso, la fórmula anterior la incluimos dentro de un nombre definido:
Fórmulas desbordadas: Pasar de Matriz a Vector

Tenemos en estos momentos dos nombres definidos:
ndCiudades =Hoja1!$B$2:$D$6
ndVector =INDICE(ndCiudades; TRUNCAR(SECUENCIA(CONTARA(ndCiudades);1;0;1/3))+1; RESIDUO(SECUENCIA(CONTARA(ndCiudades);1;0;1);3)+1)
Para nuestro objetivo de ordenar ciudades por apariciones necesitaremos llamar a UNICOS y ORDENARPOR.

Obviamente =UNICOS(ndVector) genera el listado de ciudades únicas... pero ordenadas según estaban en el origen.
Nosotros queremos llegar un poco más allá... queremos ordenarlas por número de veces que aparezcan.
Así nuestra fórmula deseada sería (en la celda F2):
=ORDENARPOR(UNICOS(ndVector);CONTAR.SI(ndCiudades;UNICOS(ndVector));-1)
Fórmulas desbordadas: Pasar de Matriz a Vector

Falta solo añadir un cálculo de conteo sencillo con CONTAR.SI sobre el rango anterior para verificar que el listado es el correcto... Así en G2 añadimos:
=CONTAR.SI(ndCiudades;F2#)
Fórmulas desbordadas: Pasar de Matriz a Vector


Alternativamente a esta técnica podemos emplear Power Query (como no). En este caso una herramienta ya vista en varias ocasiones en este blog como es Anular dinamización de columnas (Unpivot)
Como siempre, puesto que ya hemos asignado un nombre al rango de celdas, iremos a la ficha Datos > grupo Obtener y transformar > botón Desde tabla o rango; y una vez cargado los datos y ya en el Editor de Power Query nos moveremos hasta el menú Transformar.
Fórmulas desbordadas: Pasar de Matriz a Vector

Suelo recomendar eliminar el paso creado 'Tipo cambiado' que habrá asignado un type text a las columnas...
Usaremos el asistente de grabación de pasos... muy sencillos.
Primero seleccionamos las tres columnas y presionamos Anular dinamización de columnas (Unpivot), consiguiendo el listado de quince ciudades tal cual aparecen en el rango de celdas (en su caso repetidas)
Fórmulas desbordadas: Pasar de Matriz a Vector

A continuación seleccionaremos la columna 'Valor' y agruparemos desde el menú de Inicio > grupo Transformar > botón Agrupar por y en la ventana de agrupación indicaremos como 'operación' el Recuento de filas
Fórmulas desbordadas: Pasar de Matriz a Vector

El resultado es el listado de ciudades únicas con el número de veces que aparecen repetidas a su lado (en la columna 'Recuento')
Fórmulas desbordadas: Pasar de Matriz a Vector

Obvio el último paso...Aplicar un orden descendente sobre la columna 'Recuento' y Cargar y cerrar en la hoja de cálculo...
Fórmulas desbordadas: Pasar de Matriz a Vector


Muy potentes herramientas nuevas de las que disponer ;-)

jueves, 22 de octubre de 2020

Fórmulas desbordadas: Añadir Totales Generales

Ya hemos hablado en varios post de estas magníficas fórmulas y funciones desbordadas. Sin duda una experiencia en Excel ;-)
Hoy combinaremos y aplciaremos este comportamiento desbordado sobre unas funciones clásicas: la función SI y SUMAR.SI.CONJUNTO.
Nuestra meta es lograr incorporar unos Totales generales por filas y columnas a una tabla de referencia cruzada de forma automática...
Fórmulas desbordadas: Añadir Totales Generales


Partimos de una tabla 'TblDatos' en nuestra hoja de cálculo con campos: Año, País, Uds. A partir de dicha tabla queremos resumir o agrupar por Año y País las Uds vendidas, añadiendo además los Totales Generales por filas o columnas.
Fórmulas desbordadas: Añadir Totales Generales

Comenzaremos incorporando los encabezados de la tabla resumen... para lo cual haremos uso de las funciones desbordadas UNICOS y ORDENAR.
Así en la celda I3 introducimos la siguiente fórmula:
=TRANSPONER(ORDENAR(UNICOS(TblDatos[Año])))
donde obtenemos los elementos únicos del campo 'Año', ordenados en Ascendente, y cuya matriz resultante transponemos de vertical a horizontal.
Fórmulas desbordadas: Añadir Totales Generales

De forma similar obtenemos los encabezados para los países en la celda H4:
=ORDENAR(UNICOS(TblDatos[País]))
donde conseguimos en una matriz vertical los elementos únicos del campo 'País' ordenada en sentido Ascendente.
Fórmulas desbordadas: Añadir Totales Generales

Finalmente, apoyándonos en estos rangos desbordados anteriores, construiremos nuestro resumen de uds vendidas por Año y País.
Entonces en I4 insertamos la fórmula buscada:
=SI.ERROR( SI(SECUENCIA(CONTARA(UNICOS(TblDatos[País]))+1;1)>CONTARA(UNICOS(TblDatos[País])); SUMAR.SI.CONJUNTO(TblDatos[Uds];TblDatos[Año];I3#); SI(SECUENCIA(1;CONTARA(TRANSPONER(UNICOS(TblDatos[Año])))+1)>CONTARA(TRANSPONER(UNICOS(TblDatos[Año]))); SUMAR.SI.CONJUNTO(TblDatos[Uds];TblDatos[País];H4#); SUMAR.SI.CONJUNTO(TblDatos[Uds];TblDatos[Año];I3#;TblDatos[País];H4#))); SUMA(TblDatos[Uds]))
Fórmulas desbordadas: Añadir Totales Generales

Una fórmula algo larga, sin duda, pero sencilla de comprender... pero con una gran ventaja, y es que no necesita de ningún refresco ni actualización (como una tabla dinámica o una consulta de Power Query) para mostrar siempre los datos reales.
La explicamos.

Tenemos un primer uso de la función SI con una prueba lógica:
SI(SECUENCIA(CONTARA(UNICOS(TblDatos[País]))+1;1)>CONTARA(UNICOS(TblDatos[País]));
donde generamos una lista de números 1,2,3,.. hasta llegar al número que sobrepase el número de elementos únicos del campo 'País'; en nuestro ejemplo 1,2,3,4,5,6.
Comparando con el número de elementos únicos de 'País'.
Vemos la idea en la siguiente imagen
Fórmulas desbordadas: Añadir Totales Generales

Esta prueba lógica nos dice, entonces, en qué fila nos encontramos... y si estuvieramos en la última fila (en nuestro ejemplo, la 6) aplicaría la opción de 'si_verdadero'
SUMAR.SI.CONJUNTO(TblDatos[Uds];TblDatos[Año];I3#)
esto es, la suma acumulada para cada 'Año'.
De manera semejante aplicamos en nuestra fórmula un segundo condicional:
SI(SECUENCIA(1;CONTARA(TRANSPONER(UNICOS(TblDatos[Año])))+1)>CONTARA(TRANSPONER(UNICOS(TblDatos[Año])));
donde comparamos una lista de números 1,2,3,4,5,6,7 con el número total de elementos únicos del campo 'Año'
Fórmulas desbordadas: Añadir Totales Generales

Identificando columna a columna si aplicamos un parcial o el Total General en la columna 7...
Del segundo condicional la opción de 'si_verdadero' que aplica cuando llegamos a esa columna 7 es:
SUMAR.SI.CONJUNTO(TblDatos[Uds];TblDatos[País];H4#)
esto es, la suma acumulada para cada 'País'.

La última opción de nuestras condiciones nos sitúa en los casos existentes de Año-País, con un SUMAR.SI.CONJUNTO basado en dos criterios (obviamente Año y País):
SUMAR.SI.CONJUNTO(TblDatos[Uds];TblDatos[Año];I3#;TblDatos[País];H4#)

La fórmula acaba, o empieza según se mire, con un SI.ERROR para corregir el fallo que aparecería en el Total Absoluto (fila 6 y columna 7 de nuestro ejemplo), donde decimos que nos devuelva la suma de todas las unidades:
SUMA(TblDatos[Uds]).

En la primera imagen, a modo de comprobación veíamos una tabla dinámica donde por supuesto de forma más sencilla llegamos al mismo resultado... LLegando al conflicto de dos partes: uso tablas dinámicas - uso fórmulas desbordadas.
Comentaré ventajas e inconvenientes de ambas:
1- uso de tabla dinámica: mínimo esfuerzo en su construcción versus posibilidad de olvidar refrescar los datos y trabajar con información obsoleta no actualizada.
2- uso de fórmulas desbordadas: mayor trabajo en su elaboración versus datos constantemente actualizados.