jueves, 1 de septiembre de 2022

Replica de tabla dinámica solo con funciones

Como segundo ejercicio empleando 'las catorce nuevas funciones' he optado por replicar y construir una tabla de referencia cruzada incluyendo encabezados y filas de totales generales por filas y columnas.
Otra de esas fórmulas 'un poco largas' (que seguro se pueden mejorar/optimizar) que aportan otro punto de vista en el mundo de las funciones de hoja de cálculo.

Partiremos de una tabla de información (llamada 'TblDATOS'), y con una sola fórmula lograremos:
Replica de tabla dinámica solo con funciones


A efectos de comprobación, en la parte inferior de la tabla resultante, una tabla dinámica que verifica que el resultado obtenido es correcto...

La fórmula desarrollada en J2 es:
=LET(encFilas;APILARV("";ORDENAR(UNICOS(TblDATOS[Delegación])));
encCols;TRANSPONER(ORDENAR(UNICOS(TblDATOS[Cliente])));
numFilas;CONTARA(encFilas);
numCols;CONTARA(encCols);
valores;EXCLUIR(ARCHIVOMAKEARRAY(numFilas;numCols;
    LAMBDA(a;b;SUMAR.SI.CONJUNTO(TblDATOS[Ventas];
                                 TblDATOS[Delegación];INDICE(encFilas;a);
                                 TblDATOS[Cliente];INDICE(encCols;1;b))));1);
TotalCols;BYCOL(valores;LAMBDA(col;SUMA(col)));
TotalFilas;BYROW(valores;LAMBDA(fila;SUMA(fila)));
TotalAbs;SUMA(TblDATOS[Ventas]);
SI.ERROR(APILARH(APILARV(encFilas;"Total general");
                 APILARV(encCols;valores;TotalCols);
                 APILARV("Total general";TotalFilas));TotalAbs))


Veamos los pasos dados...
En primer lugar obtenemos dos vectores (futuros encabezados) a partir de los campos de 'Delegación' y 'Cliente'. Dentro de la función LET definimos las variables:
encFilas;APILARV("";ORDENAR(UNICOS(TblDATOS[Delegación])));
encCols;TRANSPONER(ORDENAR(UNICOS(TblDATOS[Cliente])));

Conseguimos unos listados de elementos únicos y además ordenados...
Nota que los elementos 'clientes' quedan transpuestos para distribuirse en la primera fila de la futura tabla; y que a las 'delegaciones', mediante APILARV, le unimos una primera posición vacía (""), que corresponderá a la celda 1-1 de la tabla (donde cruzan encabezados de filas y columnas...

En un segundo paso realizamos un conteo de elementos de ambos encabezdos:
numFilas;CONTARA(encFilas);
numCols;CONTARA(encCols);

Este conteo nos servirá para definir el tamaño del 'área de valores' futuro.

En la etapa siguiente obtenemos los valores cruzados por 'delegación' y 'cliente'...
valores;EXCLUIR(ARCHIVOMAKEARRAY(numFilas;numCols;LAMBDA(a;b;SUMAR.SI.CONJUNTO(TblDATOS[Ventas];TblDATOS[Delegación];INDICE(encFilas;a);TblDATOS[Cliente];INDICE(encCols;1;b))));1);
La función ARCHIVOMAKEARRAY se encarga de recorrer una matriz ficticia de la dimensión (filas x columnas) adecuada... sustituyendo cada elemento de la matriz por la suma acumulada (SUMAR.SI.CONJUNTO) del cruce de 'delegación' y 'cliente' correspondiente.
La función EXCLUIR nos facilita eliminar una primera fila innecesaria.

A continuación construimos los dos vectores de valores para los totales generales (filas y columnas):
TotalCols;BYCOL(valores;LAMBDA(col;SUMA(col)));
TotalFilas;BYROW(valores;LAMBDA(fila;SUMA(fila)));

Las funciones BYROW y BYCOL nos permiten recorrer cada fila/columna de nuestra recién creada matriz de valores para acumular por cada elemento...

Una última definición de variable corresponde al Total absoluto, la suma total, cruce de los Totales 'generales':
TotalAbs;SUMA(TblDATOS[Ventas]);
simplemente sumamos el campo de 'Ventas' de la tabla fuente...

Con todas las variables definidas estamos en disposición de proceder al montaje de nuestra tabla de referencia cruzada dinámica. Así, concluimos nuestra función LET con el cálculo:
SI.ERROR(APILARH(APILARV(encFilas;"Total general");APILARV(encCols;valores;TotalCols);APILARV("Total general";TotalFilas));TotalAbs)
conde con una secuencia adecuada de APILARH y APILARV podemos construir y montar nuestro pequeño Frankstein.

Quizá existan formas más elegantes, pero creo que como lógica de trabajo es una manera válida de conseguir nuestra meta.

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.