Un gráfico que crece solo a partir de un PIVOTARPOR, sin DESREF (y sin depender del orden alfabético)

Luis lleva el control de unas certificaciones en una tabla: cada cliente tiene un aliado asignado (o ninguno todavía) y un estado, que puede ser Pendiente, Procesado o estar vacío. Con PIVOTARPOR monta el resumen por aliado y estado, cambiando los vacíos por «Sin Aliado» y «Sin Procesar»:

``
=LET(
tabla; PIVOTARPOR(
SI(T_W8[Aliado]=""; "Sin Aliado"; T_W8[Aliado]);
SI(T_W8[Status]=""; "Sin Procesar"; T_W8[Status]);
SI(T_W8[Status]=""; "Sin Procesar"; T_W8[Status]);
CONTARA
);
APILARV(
APILARH("Aliado"; EXCLUIR(ELEGIRFILAS(tabla; 1); ; 1));
EXCLUIR(tabla; 1)
)
)
`

Lo que quiere es un gráfico que se actualice solo: cuando entre un aliado nuevo, que aparezca sin tocar nada, y que deje fuera la fila «Sin Aliado», la columna «Sin Procesar» y los totales.

La primera versión: nombres con DESREF

Luis lo consigue por su cuenta con nombres definidos que miden el alto de la tabla desbordada y se lo pasan a DESREF:

`
Grafico_aliados =DESREF(Estadística!$A$4;0;0;FILAS(Estadística!$A$3#)-3;1)
Grafico_Pendiente =DESREF(Estadística!$B$4;0;0;FILAS(Estadística!$A$3#)-3;1)
`

Funciona, pero John le avisa enseguida: DESREF es volátil, se recalcula con cada cambio en cualquier celda del libro. Con pocos datos no se nota; en un libro grande, sí.

La de Raúl: nombres sobre el rango desbordado

Raúl le manda una propuesta sin DESREF. Primero le pone nombre a la tabla entera, con el operador #, y luego saca las tres columnas que necesita el gráfico quitando la cabecera y las dos últimas filas («Sin Aliado» y «Total»):

`
_tbl =Estadística!$A$3#
_Aliado =ELEGIRCOLS(EXCLUIR(EXCLUIR(_tbl;1);-2);1)
_pendiente =ELEGIRCOLS(EXCLUIR(EXCLUIR(_tbl;1);-2);2)
_procesado =ELEGIRCOLS(EXCLUIR(EXCLUIR(_tbl;1);-2);3)
`

En el gráfico, las series apuntan a esos nombres. Para demostrar que crece, Raúl añade un aliado de prueba y el gráfico lo recoge solo. Luis lo adopta: «lo aplicaré y eliminaré el desref».

Un detalle que conviene saber: son nombres de ámbito Libro y funcionan como serie. En otro caso de la comunidad vimos que un nombre de ámbito Libro que es solo =Hoja!$B$7# no lo aceptaba el gráfico y había que pasarlo a ámbito Hoja. Aquí el # va dentro de EXCLUIR y ELEGIRCOLS, y el gráfico lo acepta.

La trampa del orden alfabético

John ve el punto débil: EXCLUIR(;-2) da por hecho que «Sin Aliado» es siempre la penúltima fila. PIVOTARPOR ordena alfabéticamente, así que en cuanto haya un aliado cuyo nombre vaya detrás de «Sin Aliado» (una Teresa, una Yolanda), ese aliado desaparece del gráfico y «Sin Aliado» se cuela en su lugar.

Lo hemos comprobado en Excel: con una aliada que empieza por Y, el gráfico enseña la barra «Sin Aliado» (vacía) y se come la de Yolanda.

John propone obligar a «Sin Aliado» a ir siempre al final poniéndole delante zz y quitándolo después con SUSTITUIR. Ojo con dónde va ese SUSTITUIR: si envuelve la tabla entera, como en su mensaje, convierte también los recuentos en texto, y el gráfico pinta todas las barras a cero. Hay que aplicarlo solo a la primera columna:

`
=LET(
val; SI(T_W8[Status]=""; "Sin Procesar"; T_W8[Status]);
p; SI.ND(EXPANDIR("Aliado"; 2; 2);
PIVOTARPOR(SI(T_W8[Aliado]=""; "zzSin Aliado"; T_W8[Aliado]); val; val; FILAS));
APILARH(SUSTITUIR(TOMAR(p; ; 1); "zz"; ); EXCLUIR(p; ; 1))
)
`

De paso, la fórmula de John es más corta: la cabecera la pone SI.ND(EXPANDIR("Aliado";2;2);…), que rellena con «Aliado» la esquina que PIVOTARPOR deja vacía.

Otra salida: filtrar por la etiqueta, no por la posición

En lugar de forzar el orden, los nombres pueden buscar lo que quieren quitar por su texto. Así da igual dónde caigan «Sin Aliado» o «Total», y la columna de cada estado se busca por su encabezado:

`
g_filtro =ESNOD(COINCIDIRX(TOMAR(EXCLUIR(_tbl;1);;1);APILARV("Sin Aliado";"Total")))
g_aliado =FILTRAR(TOMAR(EXCLUIR(_tbl;1);;1);g_filtro)
g_pendiente =FILTRAR(ELEGIRCOLS(EXCLUIR(_tbl;1);COINCIDIRX("Pendiente";TOMAR(_tbl;1)));g_filtro)
g_procesado =FILTRAR(ELEGIRCOLS(EXCLUIR(_tbl;1);COINCIDIRX("Procesado";TOMAR(_tbl;1)));g_filtro)
`

g_filtro devuelve VERDADERO en las filas que no están en la lista de exclusiones; si mañana hay que quitar otra fila, se añade a ese APILARV. Con esto se puede dejar la fórmula original de Luis tal cual.

El gráfico de totales

El libro tenía además un gráfico circular de Pendiente frente a Procesado que leía la fila de totales con un rango fijo. En cuanto el aliado de prueba añadió una fila, ese rango pasó a apuntar a «Sin Aliado» y el gráfico se quedó en blanco. El mismo arreglo vale para él: un nombre que lea la última fila de la tabla, esté donde esté:

`
g_estados =APILARH("Pendiente";"Procesado")
g_totales =ELEGIRCOLS(TOMAR(_tbl;-1);COINCIDIRX(g_estados;TOMAR(_tbl;1)))
`

Funciones clave

- PIVOTARPOR para el resumen por aliado y estado, ordenado alfabéticamente.
- El operador # dentro de un nombre definido, para que el nombre siga al rango desbordado.
- EXCLUIR, ELEGIRCOLS y TOMAR para recortar filas y columnas sin DESREF.
- COINCIDIRX y ESNOD para elegir filas y columnas por su texto en vez de por su posición.
- SI.ND(EXPANDIR(…)) para poner la cabecera en la esquina vacía de PIVOTARPOR.

El fichero adjunto lleva datos inventados, con una aliada «Yolanda» puesta a propósito: el primer gráfico usa los nombres por posición y enseña la trampa; el segundo, los nombres por etiqueta; el tercero, los totales. En la celda H3 está la variante de John con el SUSTITUIR` en su sitio.

Un gráfico que no hay que volver a tocar

PIVOTARPOR resuelve en una fórmula el resumen que antes pedía una tabla dinámica, y además se actualiza solo. El problema llega cuando quieres graficar ese resumen: el gráfico de Excel no sabe de rangos que crecen, y la tabla que devuelve PIVOTARPOR trae cosas que no quieres ver en él, como la fila de totales o la de «sin asignar». Este caso de la comunidad InflueXcel recorre tres maneras de hacerlo, de la más frágil a la más robusta, y una trampa que no se ve hasta que entra un dato nuevo.

El problema

Luis controla unas certificaciones por cliente: cada una tiene un aliado asignado (o ninguno) y un estado, Pendiente o Procesado (o vacío). Con PIVOTARPOR cuenta las certificaciones por aliado y estado, cambiando los vacíos por «Sin Aliado» y «Sin Procesar». Quiere un gráfico de barras por aliado que:

  • recoja solo a los aliados nuevos cuando aparezcan;
  • deje fuera la fila «Sin Aliado», la columna «Sin Procesar» y los totales;
  • no use DESREF, que es una función volátil.

Solución paso a paso

1. Nombres con DESREF (funciona, pero es volátil)

La primera versión de Luis define nombres con DESREF que miden el alto de la tabla con FILAS sobre el rango desbordado. El gráfico crece, pero cada cambio en cualquier celda del libro obliga a recalcular esos nombres. Con pocos datos no importa; con muchos, se nota.

2. Nombres sobre el rango desbordado (la propuesta de Raúl)

Raúl pone nombre a la tabla entera con el operador # y recorta con funciones normales:

_tbl       =Estadística!$A$3#
_Aliado    =ELEGIRCOLS(EXCLUIR(EXCLUIR(_tbl;1);-2);1)
_pendiente =ELEGIRCOLS(EXCLUIR(EXCLUIR(_tbl;1);-2);2)

EXCLUIR(_tbl;1) quita la cabecera, EXCLUIR(…;-2) quita las dos últimas filas y ELEGIRCOLS se queda con la columna que toca. Las series del gráfico apuntan a esos nombres, que son de ámbito Libro, y el gráfico crece solo sin nada volátil.

3. La trampa: el orden alfabético

John señala el punto débil: PIVOTARPOR ordena las filas alfabéticamente, así que «Sin Aliado» solo es la penúltima mientras ningún aliado empiece por una letra posterior. Con una aliada llamada Yolanda, el gráfico pierde su barra y enseña la de «Sin Aliado». Lo comprobamos en Excel: pasa exactamente eso, sin ningún error que avise.

Su arreglo es forzar el orden: escribir «zzSin Aliado» dentro de PIVOTARPOR, para que siempre caiga al final, y quitar el prefijo después con SUSTITUIR. Al probarlo apareció un matiz: si SUSTITUIR se aplica a toda la tabla, los recuentos se convierten en texto y el gráfico pinta las barras a cero. Hay que aplicarlo solo a la columna de nombres, con APILARH(SUSTITUIR(TOMAR(p;;1);"zz";);EXCLUIR(p;;1)).

4. Filtrar por etiqueta en vez de por posición

La alternativa que no depende del orden es buscar por su texto lo que hay que quitar:

g_filtro =ESNOD(COINCIDIRX(TOMAR(EXCLUIR(_tbl;1);;1);APILARV("Sin Aliado";"Total")))
g_aliado =FILTRAR(TOMAR(EXCLUIR(_tbl;1);;1);g_filtro)

Y cada columna de valores se localiza por su encabezado con COINCIDIRX("Pendiente";TOMAR(_tbl;1)) dentro de ELEGIRCOLS. Da igual dónde caiga «Sin Aliado», si aparece un estado nuevo o si cambia el orden de las columnas.

El mismo enfoque arregla el gráfico circular de totales, que leía la fila Total con un rango fijo y se quedó en blanco en cuanto la tabla creció una fila: TOMAR(_tbl;-1) lee la última fila esté donde esté.

Funciones clave

  • PIVOTARPOR: resumen por filas y columnas en una sola fórmula, ordenado alfabéticamente por defecto.
  • Operador # en un nombre definido: el nombre sigue al rango desbordado aunque cambie de tamaño.
  • EXCLUIR, TOMAR y ELEGIRCOLS: recortan filas y columnas sin funciones volátiles.
  • COINCIDIRX con ESNOD: decide qué filas quedan fuera según su texto.
  • SI.ND(EXPANDIR("Aliado";2;2);…): rellena la esquina vacía que deja PIVOTARPOR.

Conclusión

Un gráfico dinámico sobre PIVOTARPOR pide nombres definidos sobre el rango desbordado, no DESREF. Y conviene que esos nombres elijan las filas por lo que son y no por dónde están, porque el orden alfabético cambia con los datos. El caso salió del grupo de WhatsApp de InflueXcel: lo planteó Luis Pérez y lo resolvieron Raúl Ramos y John, cada uno con una pieza. El fichero de ejemplo, con datos inventados, enseña la trampa y las dos salidas lado a lado.

Más casos con estas funciones

Más contenido de Excel en InflueXcel