Acumular el saldo en PIVOTARPOR: una función de agregación distinta por columna

Nuevo caso que empieza con una suposición razonable y acaba en una contraprueba del propio autor.

Juan trae un libro mayor en bruto y quiere pivotarlo por cuenta y por mes. El detalle que lo complica es que las tres columnas no se agregan igual: debe y haber son importes del periodo, así que se suman, pero el saldo es acumulado, así que en cada mes tiene que arrastrar todo lo anterior. Su primera intuición es que PIVOTARPOR acumula de forma nativa.

No lo hace, y Leo lo explica sin rodeos:

> el PIVOTARPOR no viene con opción de acumular. Lo que tú intentabas hacer en el primer ejemplo era un SUBTOTALES y para que haya subtotales se deben tener al menos 2 campos en FILAS y/o COLUMNAS

La pieza que sí existe, y que es el hallazgo transversal de todo el hilo, es que el cuarto argumento de PIVOTARPOR acepta un vector de funciones, una por cada columna de valores. Con APILARH(SUMA;SUMA;<lo que sea>) puedes sumar dos columnas y tratar la tercera de otra manera dentro de la misma tabla dinámica.

La primera tanda, y por qué se cayó

Con esa idea salen dos soluciones rápidas. La de Nacho pone la acumulación en la propia función de agregación:

``
=+PIVOTARPOR(D7:D34;TEXTO(A7:A34;"e-mm");E7:H34;APILARH(SUMA;SUMA;SUMA;LAMBDA(X;INDICE(X;CONTAR(X);1))))
`

Y la de Óscar acumula antes de entrar, con SCAN:

`
=ELEGIRFILAS(PIVOTARPOR(D.:.D;TEXTO(A.:.A;"e-mm");APILARH(E.:.F;SCAN(0;G.:.G;SUMA));APILARH(SUMA;SUMA;LAMBDA(j;MAX(TOMAR(j;-1))));3;0;;0);2;4;5)
`

Juan las prueba y las da por buenas: "es perfecto... es muy top". Seis minutos después las tumba él mismo, añadiendo una segunda cuenta al fichero: "si añado otra cuenta, no me lo hace bien".

Ahí está el nudo del caso. Las dos acumulaban globalmente, sobre toda la columna, lo cual funciona de milagro mientras solo hay una cuenta. Con dos cuentas, el saldo de la segunda arrastra los importes de la primera. SCAN recorre el vector entero y no sabe nada de grupos.

Lo que sobrevive: acumular por cuenta

A partir de la contraprueba, las soluciones que llegan resuelven todas lo mismo: para cada fila, sumar solo los importes de su misma cuenta hasta esa fila.

Rommel Alemán va por la vía más directa y legible, sin depender de que las filas estén ordenadas: un SUMAR.SI.CONJUNTO con dos criterios, la cuenta de la fila y la fecha de la fila como tope superior. Tal como llegó al hilo tiene dos trampas, y merece la pena verlas porque ninguna avisa:

- MAX se queda con el acumulado más alto del mes, no con el de cierre. Si dentro del mes hay un abono, el saldo baja y MAX no se entera: en la cuenta 430001 da 2977,19 en enero en vez de 2253,38.
- El "<=" de SUMAR.SI.CONJUNTO solo compara fechas reales. En el fichero del caso las fechas son texto, y la fórmula devuelve 0 en todas las celdas sin dar ningún error.

La primera se arregla sin tocar su idea: si el tope es el último día del mes de cada fila (FIN.MES), todas las filas de un mes llevan ya el saldo de cierre y el MAX devuelve el correcto:

`
=PIVOTARPOR(D7:D34;TEXTO(A7:A34;"aaaa-mm");MAP(A7:A34;D7:D34;LAMBDA(fec;cta;SUMAR.SI.CONJUNTO(G7:G34;D7:D34;cta;A7:A34;"<="&FIN.MES(fec;0))));MAX;;0;;0)
`

Y si las fechas pueden venir como texto, basta cambiar el SUMAR.SI.CONJUNTO por una SUMA de booleanos, que convierte el texto en fecha al operar (A7:A34+0):

`
=PIVOTARPOR(D7:D34;TEXTO(A7:A34;"aaaa-mm");MAP(A7:A34;D7:D34;LAMBDA(fec;cta;SUMA((D7:D34=cta)(A7:A34+0<=FIN.MES(fec;0))G7:G34)));MAX;;0;;0)
`

Las dos empiezan en la fila 7. Con el rango desde la fila 6, que es la de cabeceras, la tabla sale con una fila y una columna de más.

Leo aporta el enfoque más elegante, y el que más juego da fuera de este problema: usa el parámetro de la LAMBDA como extremo de un rango expansivo. $D$7:d es el rango que crece fila a fila, así que el SUMAR.SI solo ve lo que hay por encima.

`
=PIVOTARPOR(D7:D34;TEXTO(A7:A34;"e-mm");APILARH(E7:F34;MAP(D7:D34;G7:G34;LAMBDA(d;g;SUMAR.SI($D$7:d;d;$G$7:g))));APILARV(APILARH(SUMA;SUMA;LAMBDA(x;@TOMAR(x;-1)));E6:G6);;0;;0)
`

Óscar vuelve con la misma idea de rango expansivo, esta vez con las referencias recortadas y un SI para saltar las celdas vacías:

`
=PIVOTARPOR(D.:.D;TEXTO(A.:.A;"e-mm");APILARH(E.:.F;MAP(G.:.G;LAMBDA(g;SI(g<"";SUMAR.SI(TOMAR(D7:g;;1);@+D34:g;G7:g);g))));APILARV(APILARH(SUMA;SUMA;LAMBDA(j;MAX(TOMAR(j;-1))));E6:G6);1;0;;0)
`

Nacho cambia de estrategia: en vez de rango expansivo, ordena primero por cuenta y fecha y luego acumula con SUMAPRODUCTO sobre la comparación de grupos.

`
=LET(
t; ORDENAR(A7:G34;{4;2});
g; ELEGIRCOLS(t;4);
v; ELEGIRCOLS(t;7);
n; FILAS(t);
acum; MAP(SECUENCIA(n);LAMBDA(i;SUMAPRODUCTO((TOMAR(g;i)=INDICE(g;i))TOMAR(v;i))));
PIVOTARPOR(g;TEXTO(ELEGIRCOLS(t;1);"e-mm");APILARH(ELEGIRCOLS(t;5;6);acum);APILARH(SUMA;SUMA;LAMBDA(X;INDICE(X;CONTAR(X);1)));;0;;0)
)
`

La parte que acumula (MAP sobre SECUENCIA con SUMAPRODUCTO) está ahora en la biblioteca de LAMBDAs del complemento de Excel de Influexcel como ACUMULADOPORGRUPO(grupos;valores). Acumula en el orden de las filas, y por eso aquí se ordena antes por cuenta y fecha.

Y ya al cierre del hilo, Gerson deja una quinta variante que resuelve el rango expansivo con aritmética de booleanos en lugar de SUMAR.SI:

`
=PIVOTARPOR(D7:D34;TEXTO(A7:A34;"e-mm");APILARH(E7:F34;MAP(G7:G34;LAMBDA(x;SUMA((D7:x=@+x:D34)G7:x))));APILARV(APILARH(SUMA;SUMA;LAMBDA(x;@TOMAR(x;-1)));E6:G6);;0;;0)
`

Cómo acaba

Juan las prueba y cierra el hilo él mismo:

> Recomiendo las soluciones dadas al mundo

Lo que hace útil este caso no son las cinco fórmulas, es el orden en que aparecen. La validación inicial era prematura, y quien la desmontó fue el propio autor con un fichero de dos cuentas. Si el ejemplo de prueba no contiene el caso que discrimina, una solución equivocada se ve exactamente igual que una correcta. Y el hilo lo demuestra dos veces: sin los abonos de enero y marzo de la cuenta 430001, el MAX de Rommel también habría pasado por bueno.

---

Es la segunda vuelta de Juan al mismo terreno. En mayo planteó Saldo acumulado por mes, pero allí partía de una matriz ya pivotada con una sola métrica, y las soluciones iban por REDUCE+BYROW, AGRUPARPOR y MMULT. Aquí el punto de partida es el libro en bruto y el problema es tener agregaciones distintas conviviendo en el mismo PIVOTARPOR`, así que ninguno de aquellos enfoques sirve.

El fichero adjunto lleva el planteamiento con las dos cuentas (el contraejemplo que partió el hilo en dos) y el libro de Leo con varias soluciones montadas.

Cuando cada columna necesita agregarse de otra manera

Pivotar un libro mayor parece un ejercicio resuelto: agrupas por cuenta, agrupas por mes y sumas. El problema aparece cuando no todas las columnas se suman igual. Debe y haber son importes del periodo, así que la suma es correcta. Pero el saldo es acumulado: el saldo de marzo no es lo que pasó en marzo, es todo lo que ha pasado hasta marzo.

Eso obliga a tener dos agregaciones distintas conviviendo dentro de la misma tabla. Y es exactamente el punto donde mucha gente asume que PIVOTARPOR acumula por su cuenta.

El problema

Un miembro de la comunidad de Influexcel llegó con un libro mayor en bruto (fecha, cuenta, debe, haber, saldo) y la expectativa de que PIVOTARPOR le devolviera el saldo acumulado mes a mes. No lo hace, y la explicación de Leo es la correcta: lo que se estaba buscando es un comportamiento de subtotales, y para que haya subtotales hace falta al menos dos campos en filas o en columnas.

Lo que sí existe, y es la pieza que abre todo el caso, es que el cuarto argumento de PIVOTARPOR acepta un vector de funciones en lugar de una sola. Con APILARH(SUMA;SUMA;algo_distinto) sumas las dos primeras columnas y tratas la tercera de otra forma, todo en la misma llamada.

La trampa: acumular no es lo mismo que acumular por grupo

Las dos primeras soluciones del hilo aprovechaban ese vector de funciones y funcionaban. El autor las probó y las dio por buenas.

Seis minutos después las tumbó él mismo, añadiendo una segunda cuenta al fichero de prueba.

El motivo es sutil y merece entenderse bien: las dos acumulaban sobre la columna entera. Una con SCAN, que recorre el vector de principio a fin, y otra tomando el último valor del grupo con INDICE. Mientras el libro tiene una sola cuenta, acumular globalmente y acumular por cuenta dan el mismo número, así que el fallo es invisible. En cuanto entra una segunda cuenta, el saldo de la segunda arrastra los importes de la primera.

SCAN no sabe nada de grupos. Recorre lo que le des, en el orden en que se lo des.

Solución paso a paso: cuatro formas de acumular por cuenta

A partir de la contraprueba, todas las soluciones que llegaron persiguen lo mismo: para cada fila, sumar solo los importes de su misma cuenta hasta esa fila.

1. Con dos criterios explícitos. La vía más legible y la que no depende del orden de las filas: un SUMAR.SI.CONJUNTO donde el primer criterio es la cuenta de la fila y el segundo es la fecha usada como tope superior. Tiene dos trampas que no avisan. Si el tope es la fecha de la propia fila y luego se agrega el mes con MAX, sale el acumulado más alto del mes y no el de cierre, que no es lo mismo en cuanto hay un abono; se arregla usando como tope el último día del mes, con FIN.MES. Y la comparación "<=" solo funciona con fechas reales: si las fechas vienen como texto, la fórmula devuelve 0 sin dar ningún error. Para ese caso sirve una SUMA de booleanos, que convierte el texto en fecha al operar.

2. Con el parámetro de la LAMBDA como extremo de rango. El truco más reutilizable del caso. Dentro de un MAP, el parámetro de la LAMBDA puede usarse como extremo de un rango: $D$7:d crece fila a fila, así que el SUMAR.SI que se apoya en él solo ve lo que hay por encima. No necesita columna auxiliar ni ordenación previa, y sirve para cualquier acumulado condicional, no solo para saldos contables.

3. Con rango expansivo y referencias recortadas. Misma idea que la anterior, con la sintaxis de referencia recortada y un SI que salta las celdas vacías para que no ensucien el resultado.

4. Ordenando primero. El enfoque opuesto: en lugar de esquivar el orden, imponerlo. Se ordena el rango por cuenta y fecha con ORDENAR, y después se acumula comparando cada fila contra las anteriores de su mismo grupo. Cambia elegancia por control. Esa acumulación por grupo está también en la biblioteca de LAMBDAs del complemento de Excel de Influexcel, como ACUMULADOPORGRUPO.

Una quinta variante llegada al cierre resuelve el rango expansivo con aritmética de booleanos en vez de SUMAR.SI, multiplicando la comparación de cuentas por los importes.

Funciones clave

  • PIVOTARPOR: la tabla dinámica por fórmula. Lo importante aquí es su cuarto argumento, que admite un vector de funciones de agregación, una por columna.
  • APILARH: apila en horizontal. Es lo que permite construir ese vector de funciones y también juntar las columnas de valores originales con la columna acumulada calculada.
  • MAP: recorre uno o varios rangos aplicando una LAMBDA celda a celda. Es el motor de los cinco enfoques.
  • SUMAR.SI.CONJUNTO: suma con varios criterios. Con "<="&fecha se convierte en un acumulado hasta una fecha dada, siempre que las fechas sean fechas reales y no texto.
  • FIN.MES: devuelve el último día del mes de una fecha. Usado como tope del acumulado, hace que todas las filas de un mes lleven el saldo de cierre.
  • SCAN: acumula a lo largo de un vector. Útil, pero sin noción de grupos, que es justo lo que hizo caer las primeras soluciones.
  • ORDENAR y ELEGIRCOLS: para la vía que impone el orden antes de acumular.

Conclusión

El valor de este caso no está en tener cinco fórmulas para el mismo problema, sino en el orden en que aparecieron. Hubo una validación, y luego una refutación del mismo autor con un fichero un poco más grande.

Si el ejemplo con el que pruebas no contiene el caso que discrimina, una solución equivocada se ve exactamente igual que una correcta. Un libro mayor de una sola cuenta no distingue entre acumular globalmente y acumular por cuenta, y esa es precisamente la diferencia que importa. Y pasa lo mismo con los abonos: sin ellos, agregar el mes con MAX da el saldo de cierre por casualidad.

Este caso salió del grupo de WhatsApp de la comunidad de Influexcel, donde alguien plantea un problema real y en unas horas hay cuatro o cinco enfoques distintos sobre la mesa, discutidos y probados por quien los propone.

Más casos con estas funciones

Más contenido de Excel en InflueXcel