Resumen contable mensual con PIVOTARPOR: totales, filtros y compatibilidad con segmentadores

Un miembro de la comunidad gestiona la contabilidad de un Club Social en Excel. En la hoja "Imputacions" registra todos los movimientos con fecha, concepto, tipo (ingresos "Ingrés" o gastos "Despesa") e importe. Los meses están en catalán (Gen, Feb, Mar...) y la hoja "Data" contiene la tabla auxiliar de referencia con el listado de conceptos y meses.

El reto es generar un resumen mensual por concepto, ordenado cronológicamente, separado por tipo de movimiento y con totales. Su solución original usaba SUMIFS con ANCHORARRAY y fórmulas auxiliares de UNIQUE + FILTER, lo cual funciona pero requiere varias fórmulas coordinadas.

John propone condensarlo todo en una sola fórmula con PIVOTARPOR. La clave es usar COINCIDIRX contra la tabla auxiliar de meses para forzar el orden cronológico (en vez del orden alfabético por defecto):

``
=LET(
m; Tabla3[MES];
PIVOTARPOR(
Tabla3[CONCEPTE];
APILARH(COINCIDIRX(m; Data!C3:C14); m);
Tabla3[IMPORT];
SUMA;;;;;;
Tabla3[IoD]=B3
)
)
`

El usuario pide eliminar los números de mes y añadir una fila de totales. John refina:

`
=LET(
m; Tabla3[MES];
p; EXCLUIR(
PIVOTARPOR(
Tabla3[CONCEPTE];
APILARH(COINCIDIRX(m; Data!C3:C14); m);
Tabla3[IMPORT]; SUMA;;0;;;;
Tabla3[IoD]=B3
); 1
);
APILARV(BYCOL(p; SUMA); p)
)
`

El truco de EXCLUIR(…; 1) quita la primera columna (los índices numéricos usados para ordenar) y APILARV(BYCOL(p; SUMA); p) antepone la fila de totales.

Alejandro aporta una variante equivalente con el filtro de tipo fijo directamente en la fórmula:

`
=EXCLUIR(
PIVOTARPOR(
Tabla3[CONCEPTE];
APILARH(COINCIDIRX(Tabla3[MES]; Data!C3:C14); Tabla3[MES]);
Tabla3[IMPORT]; SUMA; 0; -1;;;;
Tabla3[IoD]="Despesa"
); 1
)
`

Leo da el paso final: adaptar la fórmula para que sea sensible a segmentadores (slicers), usando el patrón MAP + SUBTOTALES como filtro dinámico:

`
=LET(
m; Tabla3[MES];
p; EXCLUIR(
PIVOTARPOR(
Tabla3[CONCEPTE];
APILARH(COINCIDIRX(m; Data!C3:C14); m);
Tabla3[IMPORT]; SUMA;;0;;;;
MAP(Tabla3[CONCEPTE]; LAMBDA(x; SUBTOTALES(3; x)))
); 1
);
APILARV(BYCOL(p; SUMA); p)
)
`

El filtro MAP(…; LAMBDA(x; SUBTOTALES(3; x))) devuelve 1 para las filas visibles y 0 para las ocultas por un segmentador, haciendo que PIVOTARPOR solo procese los datos filtrados. Este patrón es reutilizable con cualquier fórmula de agregación dinámica.

Técnicas destacadas:

- PIVOTARPOR con COINCIDIRX para forzar orden cronológico de meses
- EXCLUIR para quitar columnas auxiliares del resultado
- APILARV(BYCOL(…; SUMA); datos) para anteponer fila de totales
- MAP + SUBTOTALES` para compatibilidad con segmentadores