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

El problema: un resumen mensual ordenado y con totales

Un miembro de la comunidad lleva la contabilidad de un Club Social en Excel. En una hoja registra todos los movimientos —fecha, concepto, tipo (ingreso o gasto) e importe— con los meses en catalán (Gen, Feb, Mar...). El objetivo: un resumen mensual por concepto, ordenado cronológicamente, separado por tipo de movimiento y rematado con una fila de totales.

Su versión original mezclaba SUMAR.SI.CONJUNTO, UNICOS y FILTRAR en varias fórmulas coordinadas. Funciona, pero es frágil y difícil de mantener. La comunidad lo condensó en una sola fórmula, y de paso resolvió el problema clásico de que los meses de texto salen en orden alfabético en vez de cronológico.

El núcleo: PIVOTARPOR con orden cronológico (John)

La clave es forzar el orden usando COINCIDIRX contra la tabla auxiliar de meses:

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

PIVOTARPOR cruza conceptos (filas) contra meses (columnas) sumando importes. El truco está en las columnas: en vez de pasar solo el mes, se apila con APILARH el número de posición del mes (COINCIDIRX(m; Data!C3:C14)) delante del nombre. Como ese número respeta el orden de la tabla auxiliar, las columnas salen de enero a diciembre y no alfabéticamente. El último argumento (Tabla3[IoD]=B3) filtra por tipo (ingreso o gasto) según una celda.

Quitar el índice y añadir totales

El usuario pidió eliminar los números de mes visibles y sumar una fila de totales. John refinó:

=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)
)

EXCLUIR(...; 1) elimina la primera columna, que eran los índices numéricos usados solo para ordenar. Y APILARV(BYCOL(p; SUMA); p) calcula el total de cada columna con BYCOL y lo antepone como fila de cabecera. Alejandro aportó una variante equivalente con el tipo de movimiento fijado directamente en la fórmula.

El paso final: compatible con segmentadores (Leo)

Leo dio la vuelta más avanzada: que la fórmula reaccione a los 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 que un segmentador ha ocultado. Así PIVOTARPOR solo procesa lo filtrado, y el resumen se recalcula al mover el slicer. Este patrón es reutilizable con cualquier agregación dinámica.

Funciones clave

  • PIVOTARPOR: monta la tabla resumen concepto por mes en una sola fórmula.
  • COINCIDIRX + APILARH: inyectan un índice de orden para forzar la secuencia cronológica de los meses.
  • EXCLUIR: retira la columna de índices auxiliar del resultado.
  • APILARV + BYCOL: anteponen la fila de totales.
  • MAP + SUBTOTALES: hacen la fórmula sensible a los segmentadores.

Conclusión

Este caso recorre toda la evolución de un informe: de varias fórmulas sueltas a una sola, con orden cronológico real, totales y compatibilidad con segmentadores. Las dos joyas para llevarte son el índice con COINCIDIRX para ordenar meses de texto y el filtro MAP + SUBTOTALES para respetar los slicers. Retos contables como este se resuelven cada semana en la comunidad de InflueXcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel