AGRUPARPOR y PIVOTARPOR en Excel: resúmenes con fórmulas

Thornbury Freight mueve paletería desde seis delegaciones — Avonmouth, Grangemouth, Immingham, Teesport, Felixstowe y Dagenham — y paga a cada jefe de delegación una prima mensual del 4% del margen bruto de su delegación. Es el incentivo más sencillo de la empresa: un porcentaje, un número por delegación, pagado en la nómina del mes siguiente.

El número sale de una hoja llamada Resumen, y Resumen es una sola fórmula en A2:

=AGRUPARPOR(Transporte[Delegación];Transporte[Margen Bruto];APILARH(SUMA;PORCENTAJEDE);0;1;-2)

Delegación en el lateral, margen bruto sumado, el peso de cada delegación sobre el total al lado y — porque es una clasificación y una clasificación se lee de arriba abajo — ordenada de mayor a menor margen. Eso es lo que hace el -2. El resultado se desborda en A2:C8: seis delegaciones y luego una fila de total general.

La hoja Nóminas tiene los seis nombres escritos en la columna A, en el orden en que salieron en julio, y la columna B trae el margen:

B2:  =Resumen!B2      Avonmouth
B3:  =Resumen!B3      Grangemouth
B4:  =Resumen!B4      Immingham
...
C2:  =REDONDEAR(0,04*B2;2)

En julio eso era cierto. En agosto, Immingham le ganó a Grangemouth el contrato de Sunderland, y las tres filas centrales de la clasificación cambiaron de sitio.

Nadie tocó la hoja de nóminas, porque en la hoja de nóminas no había cambiado nada. B3 seguía diciendo =Resumen!B3. Resumen!B3 seguía teniendo un número. El número era el de Immingham.

Lo Que Salió por la Puerta

Tres de seis personas cobraron la cantidad equivocada y la nómina cuadraba al céntimo, porque reordenar seis números no cambia lo que suman. 26.480,00 £ es el 4% de 662.000,00 £ lleve el nombre que lleve cada línea.

Salió a la luz el 8 de septiembre, cuando el jefe de Immingham preguntó por qué su prima había bajado un 14,33% en el mes en que el margen de su delegación había subido un 30,27%. La pregunta simétrica no la hizo nadie: la prima de Grangemouth subió un 4,14% en un mes en que su margen cayó un 36,67%, y nadie reclama un cobro que es demasiado grande.

Los 2.486,00 £ de más no se recuperaron. Los dos pagos de menos se corrigieron en la nómina de septiembre. Lo caro no fue ninguna de las dos cosas: la misma hoja Resumen alimentó la revisión de capacidad del tercer trimestre, donde Grangemouth aparecía segunda con 158.600,00 £ e Immingham tercera con 104.300,00 £, y el trunk nocturno extra y dos conductores de ETT fueron a la delegación que acababa de perder el contrato, durante un trimestre.

1) Una Nómina, Correcta en el Total, Equivocada en la Mitad de sus Líneas

Lee juntas las dos últimas filas. El total de control y la reconstrucción por nombre dan los mismos 26.480,00 £, y solo una de las dos paga a las personas correctas. Un total es una propiedad del conjunto de números; una prima es una propiedad del emparejamiento entre números y nombres, y la una no dice nada de la otra.

🎯 Escenario: Busca en tu libro una celda que lea un resumen desbordado por dirección — =Resumen!B3, =INDICE(A2#;3;2), cualquier cosa con un número de fila dentro. Hazte una pregunta: ¿qué hace que la fila 3 sea esa fila? Si la respuesta es «era la fila 3 cuando escribí esto», la fórmula es una apuesta sobre los datos del mes que viene.

2) Qué Devuelve AGRUPARPOR y Dónde Viven sus Filas

AGRUPARPOR coge las filas de un rango, las reparte en grupos según uno o varios campos y devuelve un resumen como matriz dinámica: un bloque desbordado, escrito por una fórmula en una celda.

=AGRUPARPOR(campos_fila; valores; función; [encabezados]; [profundidad_total]; [orden]; [matriz_filtro]; [relación_campos])

• campos_fila — la columna (o columnas) por las que agrupar.
• valores — la columna (o columnas) que se agregan.
• función — la agregación, escrita como nombre de función desnudo: SUMA, PROMEDIO, CONTAR, CONTARA, MAX, MIN, MEDIANA, PRODUCTO, DESVEST.M, DESVEST.P, CONCAT, VALORATEXTO, PORCENTAJEDE, o una LAMBDA tuya. Sin paréntesis detrás: se está entregando como función, no llamando. APILARH(SUMA;PORCENTAJEDE) pide dos columnas de respuestas.
• encabezados — si la entrada los tiene y si la salida los muestra: 0 no y no, 1 sí pero ocultos, 2 no pero mostrados, 3 sí y mostrados.
• profundidad_total — 0 sin totales, 1 total general abajo (el valor por defecto), 2 total general y subtotales, -1 total general arriba, -2 totales arriba.
• orden — qué columna del resultado ordena, en negativo para descendente. -2 es «descendente por la segunda columna del resultado».
• matriz_filtro — una columna de VERDADERO/FALSO de la misma altura que campos_fila, que decide qué filas del origen participan.
• relación_campos — 0 jerarquía (por defecto) o 1 tabla, y solo importa con más de un campo de fila.

La frase importante no está en esa lista. La forma del resultado es un dato. El número de filas es el número de valores distintos en campos_fila; su orden es lo que la ordenación haga con los números de hoy. Las dos cosas se recalculan desde el origen en cada edición, como cualquier otro resultado de fórmula — que es el sentido de la función y todo su peligro.

🎯 Escenario: Pon =FILAS(Resumen!A2#) en una celda libre. Esa es la altura de tu resumen hoy, totales incluidos. Todo lo que haya aguas abajo suponiendo otra altura ya está mal; todo lo que suponga esta altura estará mal la primera vez que aparezca un grupo.

3) Por Qué se Movieron las Filas

El resumen de Thornbury se movió porque estaba ordenado explícitamente por valor, y el valor es justo lo que cambia. No es un mal uso — «la mejor delegación primero» es el diseño correcto para un informe que lee una persona — pero garantiza que la fila en la que está una delegación es función de cómo haya ido el mes.

Quitar la ordenación no elimina el problema, lo vuelve más silencioso:

• Si omites orden, los grupos vuelven ordenados por el propio campo de fila, de forma ascendente: alfabético para texto. Avonmouth, Dagenham, Felixstowe, Grangemouth, Immingham, Teesport. Estable de mes a mes, hasta que una delegación abre, cierra, se renombra o no factura nada ese mes y desaparece de la lista.
• Un grupo sin filas no está en el resultado. AGRUPARPOR lista los grupos que encontró, no los que tú tienes. Dagenham cierra dos semanas en agosto, no factura nada, y no hay fila de Dagenham: todas las delegaciones de debajo suben una posición y la última referencia posicional cae sobre la fila de total.

Así que la regla es corta: lo único estable de una fila de AGRUPARPOR es su etiqueta. No su posición, ni su distancia al principio, ni el número de filas que tiene encima.

🎯 Escenario: Coge el resumen del que dependes y ordena la tabla de origen de otra manera — por fecha descendente, por ejemplo — y recalcula. Si cambia algo aguas abajo, has encontrado una lectura posicional. Los datos no han cambiado; solo el orden en que Excel se los encontró.

4) La Comprobación Que No Podía Verlo

Thornbury hacía una comprobación sobre el fichero de primas, y era razonable: la suma de la columna de primas contra el 4% del margen total. 26.480,00 £ contra 26.480,00 £. Cuadra todos los meses, y va a seguir cuadrando siempre, incluidos los meses en que cada línea se paga a la persona equivocada.

Merece la pena enunciarlo como regla, porque no es propio de este libro: un total de control es ciego a una permutación. Cualquier comprobación que sume la columna antes de comparar ha tirado el emparejamiento entre nombre y número, y el emparejamiento es lo que se había roto.

Tres comprobaciones que sí lo ven:

=SUMAPRODUCTO(--(B2:B7<>BUSCARX(A2:A7;ELEGIRCOLS(Resumen!$A$2#;1);ELEGIRCOLS(Resumen!$A$2#;2))))
                                         → 3    filas donde la lectura posicional y la lectura por nombre discrepan
=UNIRCADENAS(", ";1;ELEGIRCOLS(EXCLUIR(Resumen!A2#;-1);1))
                                         → "Avonmouth, Immingham, Teesport, Grangemouth, Felixstowe, Dagenham"
=SUMAPRODUCTO(--(A2:A7<>ELEGIRCOLS(EXCLUIR(Resumen!$A$2#;-1);1)))
                                         → 3    filas donde el orden de la nómina y el del resumen discrepan

La primera es la forma general y la que hay que conservar: lee la misma magnitud dos veces, por dos caminos distintos, y cuenta las discrepancias. La segunda es la más barata: una celda con el orden actual en texto, de modo que un cambio de orden se ve de un vistazo y aparece en una comparación de ficheros.

🎯 Escenario: Añade la primera comprobación a cualquier hoja que lea un resumen por posición, ponla al lado de la salida y dale un formato que ponga en rojo cualquier valor distinto de cero. Cuesta una celda. En el fichero de agosto de Thornbury habría puesto 3 antes de que saliera la nómina.

5) Cómo Leer un Resumen Dinámico sin Riesgo

Hay dos respuestas correctas y la segunda suele ser mejor.

Léelo por nombre. La columna de etiquetas es la parte estable, así que busca la etiqueta:

=REDONDEAR(0,04*BUSCARX($A2;ELEGIRCOLS(Resumen!$A$2#;1);ELEGIRCOLS(Resumen!$A$2#;2));2)

Resumen!A2# es el bloque desbordado entero — el operador de desbordamiento devuelve todas las columnas, no solo la A — así que ELEGIRCOLS saca de él la columna de etiquetas y la de valores. La búsqueda sigue entonces a Grangemouth a la fila 5, o a la 2, o a donde la ponga el mes que viene. Una delegación que no esté en el resumen devuelve #N/D, y un #N/D en una columna de primas detiene una nómina, que es exactamente lo que debe pasar cuando falta la paga de alguien.

O no lo leas. El resumen es una representación del origen; el origen sigue ahí:

=REDONDEAR(0,04*SUMAR.SI.CONJUNTO(Transporte[Margen Bruto];Transporte[Delegación];$A2);2)

Esta es la versión que acabó usando Thornbury. No toca Resumen para nada, así que reordenar el informe, añadirle una columna o borrarlo entero no cambia nada. La regla que hay debajo es la que hay que llevarse:

Un AGRUPARPOR es un informe para una persona. Un SUMAR.SI.CONJUNTO es un valor para una fórmula. En el momento en que una segunda fórmula lee tu resumen por dirección, has convertido una maquetación en una interfaz.

Antes de elegir, conviene conocer una diferencia. Para una delegación que no existe en los datos, BUSCARX devuelve #N/D y SUMAR.SI.CONJUNTO devuelve 0,00. Cuando la salida es dinero pagado a una persona con nombre, un error es mejor que un cero: nadie reclama una prima de 0,00 £ hasta el mes siguiente.

🎯 Escenario: Cuenta las fórmulas de tu libro que apuntan a un rango desbordado con un número de fila. Sustitúyelas por BUSCARX contra la columna de etiquetas, o por SUMAR.SI.CONJUNTO contra el origen. Las dos ediciones son mecánicas; ninguna cambia un solo número hoy, y por eso es tan fácil posponerlas.

6) La Fila de Total Está Dentro del Desbordamiento

profundidad_total vale 1 por defecto, así que, salvo que digas otra cosa, tu resumen termina en una fila de total general — y esa fila es parte de la matriz desbordada, no una raya que Excel haya pintado debajo.

=SUMA(ELEGIRCOLS(Resumen!A2#;2))      → 1.324.000,00
=SUMA(Transporte[Margen Bruto])       → 662.000,00

Exactamente el doble, porque el cuerpo y su propio total están los dos en el bloque. La misma trampa atrapa a =CONTARA(A2#) (siete delegaciones, no seis), a =MAX(ELEGIRCOLS(A2#;2)) (que devuelve la empresa, no Avonmouth) y a =PROMEDIO(...) (que sale casi el doble de la media real sobre seis filas).

Tres salidas, por orden de preferencia:

1. profundidad_total a 0 cuando una fórmula vaya a leer el bloque. Pon el total en otro sitio, en su propia celda, donde nada pueda leerlo por accidente.
2. EXCLUIR(A2#;-1) para quitar la última fila donde necesites el cuerpo, y TOMAR(A2#;-1) donde quieras el total a propósito.
3. -1 para subir el total arriba, si lo que proteges es a un lector humano y no a una fórmula. No hace que el bloque se pueda sumar sin peligro; hace que el total sea lo primero que encuentre una referencia posicional.

Ojo: 2 y -2 añaden también filas de subtotal, una por grupo exterior, así que un AGRUPARPOR de dos campos con profundidad_total a 2 contiene tres tipos distintos de fila. Sumar esa columna te da el cuerpo, más todos los subtotales, más el total general.

🎯 Escenario: =SUMA(ELEGIRCOLS(A2#;2))/SUMA(origen). Si sale 2,00, tu suma se está comiendo la fila de total. Si sale 3,00 o 4,00, además tienes subtotales dentro.

7) Cuando los Grupos Aparecen y Desaparecen

Un bloque de AGRUPARPOR crece y mengua solo, y cuando crece pasan dos cosas.

Se desborda sobre lo que haya debajo. Si hay algo — una nota, una segunda tabla, las cifras del año pasado — la fórmula entera devuelve #¡DESBORDAMIENTO! y el resumen desaparece. No la fila nueva: todo. Un informe que ayer estaba bien hoy es una sola celda de error, lo cual al menos se oye.

Y todo lo direccionado por debajo está direccionando otra cosa. Esta es la silenciosa. Abre una séptima delegación, el bloque baja una fila más y todas las referencias de debajo — incluida la celda donde alguien dejó el total, o una nota, o un segundo resumen — están leyendo ahora la salida del propio resumen.

Dos defensas, las dos baratas:

=FILAS(Resumen!A2#)-1                       → 6    grupos en el resumen de hoy
=CONTARA(UNICOS(Transporte[Delegación]))    → 6    delegaciones distintas en el origen

Tenlas una al lado de la otra con un = en medio. Discrepan cuando aparece una delegación, cuando una deja de facturar y cuando un problema de clave parte una delegación en dos: "Teesport" y "Teesport " son dos grupos, porque la agrupación compara el texto que recibe y un espacio final es texto. Deja una columna libre y varias filas libres bajo cualquier resumen desbordado; el espacio no cuesta nada y un #¡DESBORDAMIENTO! cuesta una mañana.

🎯 Escenario: Escribe un valor en la celda justo debajo de la última fila de tu resumen y mira cómo se derrumba entero a #¡DESBORDAMIENTO!. Deshaz. Ese es el margen que tiene tu informe: ninguno.

8) PIVOTARPOR y el Mismo Fallo Girado un Eje

PIVOTARPOR es AGRUPARPOR con una segunda dimensión: grupos en el lateral y grupos en la cabecera.

=PIVOTARPOR(Transporte[Delegación];Transporte[Mes];Transporte[Margen Bruto];SUMA;0;1;-2;1;1)

Campos de fila, campos de columna, valores, función, y después encabezados, profundidad_total_filas, orden_filas, profundidad_total_columnas, orden_columnas, y opcionalmente matriz_filtro y relativo_a.

Todo lo de este artículo se le aplica dos veces. Las delegaciones se mueven por el lateral según cambian los márgenes; los meses se mueven por la cabecera según avanza el año, y una fórmula que lea Resumen!D2 está leyendo el mes que hoy va tercero. Un mes nuevo inserta una columna, un mes flojo quita otra, y el total de columna está dentro del bloque igual que el de fila.

El eje de meses añade una trampa propia. Si el campo de columna es texto — "ene", "feb", "mar" — las columnas vuelven en orden alfabético: abr, ago, dic, ene, feb, jul, jun, mar, may, nov, oct, sep. Parece un error de Excel y es una propiedad de tus datos: ordenar texto ordena texto. Construye el campo de columna sobre una fecha real — una columna de inicio de mes, =FIN.MES([@Fecha];-1)+1, con formato mmm-aa — y las columnas vuelven en orden cronológico porque ahora son fechas y las fechas tienen orden.

Para leer un PIVOTARPOR desde fuera, el consejo se simplifica en vez de complicarse: no lo hagas. Una lectura a dos bandas sobre un bloque que se mueve en los dos ejes es una búsqueda contra una fila de encabezados y una columna de etiquetas, en una sola fórmula, reevaluada cada mes. =SUMAR.SI.CONJUNTO(Transporte[Margen Bruto];Transporte[Delegación];$A2;Transporte[Mes];B$1) dice lo mismo con las dos claves escritas, y nada en ella depende de la maquetación del informe.

🎯 Escenario: Añade un mes a tu origen y mira qué le ha hecho tu PIVOTARPOR a las columnas de su derecha. Después mira todo lo que referenciaba esas columnas. Ese es tu enero.

9) Lo Que AGRUPARPOR No Hace

Cuatro comportamientos que sorprenden, y los cuatro salen del mismo hecho: es una fórmula sobre un rango, no una vista de una hoja.

• Ignora los filtros y las filas ocultas. Filtra el origen por un cliente y el resumen no se mueve. Es lo contrario de SUBTOTALES y AGREGAR, que existen precisamente para seguir al filtro. Si quieres el resumen filtrado, dilo en la fórmula: para eso está matriz_filtro, =AGRUPARPOR(Transporte[Delegación];Transporte[Margen Bruto];SUMA;0;1;-2;(Transporte[Servicio]="24 h")). La matriz debe tener exactamente la altura de campos_fila o sale #¡VALOR!, y si lo excluye todo sale #¡CALC!.
• No agrupa fechas por meses. Una tabla dinámica se ofrece a agrupar fechas en meses, trimestres y años; AGRUPARPOR agrupa los valores que le des, así que 31 fechas distintas son 31 filas. Dale una columna de inicio de mes desde el origen.
• No hay nada que pulsar. Ni detalle de las filas que hay detrás de una celda, ni segmentaciones, ni «Mostrar valores como», ni lista de campos, ni Actualizar — porque no hay nada que actualizar. Recalcula como una fórmula, que es una ventaja real frente a una tabla dinámica caducada y no sirve de nada cuando alguien quiere hacer doble clic en 96.450,00 £ y ver los envíos.
• Solo existe en Microsoft 365. AGRUPARPOR y PIVOTARPOR llegaron en 2024 a Microsoft 365 y a Excel para la web. Abre el libro en Excel 2024, 2021 o 2019 y la fórmula se lee como _xlfn.GROUPBY y evalúa a #¿NOMBRE? — así que en la máquina de un compañero el informe no está desactualizado, no está, y todo lo que lo lea también es un error. Pega como valores una copia antes de que el fichero salga de casa, o manda la versión con tabla dinámica.

🎯 Escenario: Filtra tu tabla de origen por un solo cliente con el resumen a la vista. Si los números no se mueven, acabas de demostrar que el resumen lee la tabla entera — que es lo que quieres, siempre que quien mira la pantalla lo sepa.

10) AGRUPARPOR, Tabla Dinámica o SUMAR.SI.CONJUNTO

El resumen honesto es que AGRUPARPOR sustituye a la tabla dinámica que reconstruyes todos los meses y no sustituye a la tabla dinámica con la que exploras — y no sustituye nunca a SUMAR.SI.CONJUNTO para nada que consuma otra fórmula.

🎯 Escenario: Para cada resumen de tu libro principal, apunta en qué columna de esta tabla estás de verdad. Aquellos en los que «esto lo lee otra cosa» sea cierto son los que hay que cambiar, los haya construido la herramienta que los haya construido.

11) Seis Comprobaciones de una Celda

=FILAS(Resumen!A2#)-1                                         → 6       grupos, sin contar la fila de total
=CONTARA(UNICOS(Transporte[Delegación]))                      → 6       claves distintas en el origen
=SUMA(Transporte[Margen Bruto])-SUMA(ELEGIRCOLS(EXCLUIR(Resumen!A2#;-1);2))
                                                              → 0,00    el cuerpo concilia con el origen
=SUMA(ELEGIRCOLS(Resumen!A2#;2))/SUMA(Transporte[Margen Bruto])
                                                              → 2,00    significa que la fila de total está dentro de tu suma
=SUMAPRODUCTO(--(A2:A7<>ELEGIRCOLS(EXCLUIR(Resumen!$A$2#;-1);1)))
                                                              → 3       el orden que supone la hoja no es el que tiene
=UNIRCADENAS(", ";1;ELEGIRCOLS(EXCLUIR(Resumen!A2#;-1);1))     → el orden actual, en una celda, visible en una comparación

La tercera y la quinta son la pareja que hay que conservar. La tercera demuestra la aritmética; la quinta demuestra la alineación; y el fichero de agosto de Thornbury pasaba la tercera y habría fallado la quinta.

🎯 Escenario: Pon las seis en un bloque de la hoja de resumen, con una etiqueta al lado de cada una, una vez, hoy. Todas ocupan una celda, y juntas cubren todos los fallos de este artículo menos el #¿NOMBRE?.

12) Doce Trampas

1. Leer un resumen ordenado por referencia de celda. El artículo entero. Una posición no es una clave.
2. Suponer que «sin ordenar» es «estable». Sin orden, las filas vuelven ascendentes por la etiqueta del grupo, que también se mueve cuando un grupo se añade, se renombra o falta.
3. Un grupo sin filas simplemente no está. El resumen lista lo que encontró. Todo lo que hay bajo el hueco sube.
4. Sumar un bloque que contiene su propio total general. Exactamente el doble, que parece un error de unidades y es un error de estructura.
5. Y también los subtotales. profundidad_total a 2 añade una fila de subtotal por grupo exterior, y también están en el bloque.
6. #¡DESBORDAMIENTO! por una celda en medio. El informe no crece una fila: desaparece y deja un error.
7. El crecimiento pisa lo que pusiste debajo. Deja varias filas libres bajo cualquier resumen desbordado, y no aparques nunca un total justo debajo de uno.
8. Esperar que siga al filtro. No lo hace. SUBTOTALES y AGREGAR sí; AGRUPARPOR necesita matriz_filtro, y esa matriz debe coincidir exactamente con la altura de campos_fila.
9. Meses en texto. Abr, ago, dic. Agrupa por una fecha de inicio de mes con formato, no por el nombre del mes.
10. Claves que se diferencian en un espacio o un carácter suelto. "Teesport " es su propio grupo, con su propia fila, y se lleva su margen con ella.
11. Llamar a la función en vez de nombrarla. SUMA es la agregación; SUMA() es un error de sintaxis. Varias a la vez van en APILARH(SUMA;PORCENTAJEDE).
12. Mandar el fichero fuera de Microsoft 365. _xlfn.GROUPBY y #¿NOMBRE? en Excel 2024, 2021 y 2019: el resumen y todo lo que lo lee, perdidos al abrir.

Lo Que Hay Que Llevarse

AGRUPARPOR y PIVOTARPOR son lo mejor que le ha pasado a los informes rutinarios de Excel en años. Una fórmula, sin actualizar, sin un objeto que mantener, un resumen que sencillamente está siempre al día. La tentación que traen es la misma que trajeron las matrices dinámicas con FILTRAR y ORDENAR: la salida parece una tabla, está en celdas que tienen dirección, y direccionarla queda a un clic.

No es una tabla. Es una fotografía de los datos tomada desde un ángulo concreto, redibujada desde cero cada vez que los datos se mueven, y el ángulo es parte de lo que pediste. Thornbury pidió la mejor delegación primero, la obtuvo, y luego leyó la fila 3 como si «fila 3» fuera un sitio y no una clasificación.

Tres hábitos lo cubren todo. Separa los resúmenes de las fuentes de datos: AGRUPARPOR para los ojos, SUMAR.SI.CONJUNTO para todo lo que vaya a consumir una fórmula. Cuando tengas que leer un resumen desde otro sitio, léelo por etiqueta, con BUSCARX contra ELEGIRCOLS del desbordamiento, para que la búsqueda siga a la fila. Y comprueba la alineación, no solo el total: un SUMAPRODUCTO que cuente las filas en las que dos caminos al mismo número discrepan, puesto al lado de la salida, habría marcado 3 el ocho de septiembre y habría ahorrado todo lo que vino después.