Formato dinámico AGRUPARPOR PIVOTARPOR

Formato dinámico AGRUPARPOR PIVOTARPOR

Aplicar formato condicional a las matrices calculadas es fundamental para conseguir que su legibilidad sea óptima

Las funciones AGRUPARPOR y PIVOTARPOR aportan muchísima capacidad y flexibilidad a la hora de plantear cálculos en Excel, pero tienen una pata coja: el formato con el que devuelven los resultados se queda un poco raro. Para localizar dónde están los totales y subtotales, normalmente toca recurrir a formatos condicionales que, cada vez que cambia algo, hay que ir ajustando manualmente (color del total, fondos, tipo de letra). Lo mismo pasa con los agregados: decimales, símbolo de euro o fechas que se muestran como número en lugar de conservar su formato. El objetivo de este tutorial es conseguir un efecto en el que, cambie lo que cambie, la forma de visualizar los datos se ajuste automáticamente.

El punto de partida con AGRUPARPOR

Partimos de una tabla con varios datos: un valor de fecha que usaremos como totalizador, unos ingresos anuales y tres segmentos para agrupar: cliente, producto y grupo de compra. Empezamos con AGRUPARPOR, eligiendo de la tabla las columnas por las que queremos agrupar (cliente, producto y grupo de compra) y luego los valores. Por ejemplo, elegimos unidades y le pedimos que haga la SUMA.

Entre los parámetros de la función encontramos:

  • Cabeceras: indicamos si queremos o no encabezados.
  • Total de detalle: define el nivel de agrupación que queremos totalizar. Con 1 solo el total general; con 2 añade el subtotal del primer grupo; con 3 la siguiente agrupación, y así sucesivamente.

El problema es que, al ir añadiendo subtotales, el resultado empieza a quedar confuso: a diferencia de una tabla dinámica, no hay un formato diferenciado para total, subtotal o acumulado. Esa distinción visual es justo lo que perdemos al trabajar con AGRUPARPOR o PIVOTARPOR.

Apilar varios agregados sobre columnas distintas

Dentro de la función podemos apilar horizontalmente varios agregados. Si en lugar de solo SUMA queremos también el promedio, apilamos SUMA y PROMEDIO, lo que genera dos columnas. El problema surge cuando un agregado debe aplicarse a otra columna: si pedimos el máximo de la fecha, por defecto Excel lo calcula sobre la misma matriz de valores (unidades).

La solución es igualar las dos matrices: la de valores y la de agregados. Apilamos en el parámetro de valores tantas columnas como funciones agregadoras tengamos, haciendo que cada una se corresponda con su agregado:

  • SUMA → columna unidades
  • PROMEDIO → columna unidades (repetida)
  • MÁXIMO → columna fecha

El número de columnas apiladas en valores debe coincidir con el número de medidas o agregados.

Etiquetar cada fila para aplicar formato dinámico

Para destacar dinámicamente totales y subtotales, generamos columnas auxiliares que etiquetan cada fila (detalle, total o cabecera). Así, aplicar formato condicional resulta mucho más sencillo.

  1. Creamos una SECUENCIA que numere todas las filas del resultado de AGRUPARPOR, usando el # para tratarlo como matriz. Esto incluye cabeceras y la fila de total general.
  2. Contamos los espacios vacíos de cada fila con BYROW: recorre fila a fila la matriz y, mediante un LAMBDA, comprueba si cada celda es igual a "". El resultado verdadero/falso se convierte a número con N y se suma, devolviendo cuántos vacíos hay por fila.

Con la posición y el número de blancos, usamos MAP para recorrer ambas columnas y construir un condicional con SI.CONJUNTO:

  • Si la posición es 1 → cabecera.
  • Si la posición coincide con el MÁXIMO de la secuencia → total (última línea).
  • En el resto, según el número de blancos, concatenamos "detalle" con su nivel: el nivel 0 es el detalle máximo y los niveles 1 y 2 representan los distintos agrupados.

Formato condicional sobre rango dinámico

Con cada fila etiquetada, aplicamos formato condicional usando una fórmula sobre la columna de etiquetas. Por ejemplo, con $C6 fijando la columna, indicamos un formato para cada caso:

  • Cabecera: relleno verde oscuro, fuente blanca en negrita.
  • Total: relleno azul oscuro.
  • Detalle 1: gris suave con fuente gris oscuro.
  • Detalle 2: gris más oscuro con letra blanca.

Duplicando reglas y cambiando solo el valor a detectar, generamos tantas como niveles tengamos. Lo mejor es que todo se adapta solo: si cambiamos el nivel de agrupación o añadimos un nuevo producto a la tabla original, las columnas dinámicas y el formato condicional se reajustan sin tocar nada manualmente.

Fijar el formato de los resultados con TEXTO

Queda pendiente fijar el formato de los propios agregados (suma, promedio, máximo) sin depender del formato de la celda. Para ello, en lugar de pasar directamente la función eta-lambda (por ejemplo, solo MÁXIMO), generamos nuestra propia función con LAMBDA:

  • LAMBDA(x, MÁXIMO(x)) reproduce el mismo resultado, pero ahora x representa los valores de cada agrupado.
  • Sobre ese resultado aplicamos TEXTO, por ejemplo TEXTO(MÁXIMO(x), "aaaa-mm-dd"), para que la fecha salga con el formato deseado directamente desde la función.

Lo mismo se puede aplicar a la suma y al promedio. Así, aunque añadamos o quitemos columnas agrupadas o medidas, el formato se mantiene sin tener que ajustarlo a mano.

Conclusión

Combinando AGRUPARPOR con columnas auxiliares que etiquetan cada fila (SECUENCIA, BYROW, MAP, SI.CONJUNTO), formato condicional sobre rangos dinámicos y la función TEXTO dentro de un LAMBDA, conseguimos que la visualización dependa del resultado y no del formato manual de las celdas. El resultado es una tabla que se adapta sola a cualquier cambio en los datos o en los niveles de agrupación, eliminando muchísimo trabajo repetitivo. Y aún quedan posibilidades por explorar, como las cabeceras dinámicas o la segmentación de datos.

Más contenido de Excel en InflueXcel