Gráficos que se actualizan solos en Excel: orden, etiquetas condicionales y rangos dinámicos

Gráficos que se actualizan solos en Excel: orden, etiquetas condicionales y rangos dinámicos

Tres problemas que aparecen en cuanto un gráfico deja de leer una tabla fija y pasa a leer el resultado de un cálculo. Salieron montando el diagrama de Gantt con ruta crítica, pero las tres soluciones sirven para cualquier gráfico.

1. Que el orden de las barras siga al cálculo, no a la tabla

Cuando cambian las dependencias, el orden real de las tareas cambia. La solución no es VBA: es una matriz intermedia con ORDENAR sobre la columna de inicio, y colgar el gráfico de esa matriz en vez de la tabla original.

2. Que la etiqueta diga una cosa u otra según el estado

Una etiqueta de datos puede apuntar a un rango de celdas, así que la inteligencia se pone en una columna aparte con un condicional: si la tarea ha empezado, muestra el porcentaje de avance; si no, muestra de qué tareas depende.

3. Que el gráfico crezca al añadir filas

Es el precio de colgar de una matriz calculada en lugar de una tabla: se pierde la expansión automática. Se recupera con nombres definidos que usan INDIRECTO para construir el rango según el número de filas que tenga la matriz en cada momento.

Es la continuación de la serie Diagrama de GANTT con Ruta Crítica (CPM), en cuatro partes. Si lo que buscas es montar un Gantt sencillo desde cero, mira Diagrama de Gantt en Excel en 6 minutos con barras apiladas.

Hay un momento, montando cualquier informe, en el que un gráfico deja de leer una tabla y pasa a leer el resultado de un cálculo. A partir de ahí aparecen tres problemas que no tenías antes, y los tres tienen solución sin tocar una línea de VBA.

Estos tres salieron construyendo un diagrama de Gantt con ruta crítica, pero no son problemas de Gantt: son problemas de cualquier gráfico que dependa de datos que se recalculan.

1. El orden de las categorías cambia con el cálculo

En un cronograma con dependencias, cambiar una precedencia reordena las tareas. Si la tarea B pasa a depender de la C, la C tiene que dibujarse antes. Pero el gráfico lee la tabla en el orden en que está escrita, no en el orden en que deben ocurrir las cosas.

La solución es no colgar el gráfico de la tabla original, sino de una matriz intermedia ya ordenada:

=ORDENAR(tabla_tareas;columna_inicio)

ORDENAR recibe la matriz que quieres ordenar y el número de columna que manda. Aquí manda el momento de inicio, que es el que resulta de los cálculos de precedencias. Esa matriz se puede dejar en una zona apartada de la hoja, y es de ella de la que cuelga el gráfico.

El efecto: cambias una dependencia y las barras se recolocan solas, porque el orden ya no es un dato escrito sino un resultado.

2. La etiqueta tiene que decir cosas distintas según el estado

Lo que interesaba mostrar dentro de cada barra no era siempre lo mismo. Si la tarea ya ha empezado, lo útil es el porcentaje de avance. Si no ha empezado, lo útil es saber a qué está esperando.

La pieza que lo hace posible es que una etiqueta de datos puede apuntar a un rango de celdas, con la opción Valor de celdas del formato de etiquetas. Y si puede apuntar a un rango, ese rango puede ser una columna calculada.

Así que la inteligencia se saca del gráfico y se mete en una columna auxiliar, llamada por ejemplo etiqueta:

=SI([@Avance]>0;TEXTO([@Avance];"0%");[@Predecesoras])

El condicional decide, fila a fila, qué texto le toca a cada barra. El gráfico se limita a mostrar esa columna. Es un patrón que se generaliza a cualquier cosa: mostrar el importe solo cuando supera un umbral, marcar con un símbolo las filas fuera de objetivo, etiquetar únicamente los extremos de una serie.

3. El gráfico deja de crecer cuando añades filas

Este es el precio de los dos puntos anteriores. Una tabla de Excel se expande sola y el gráfico la sigue; una matriz calculada en un rango no da esa garantía, así que al añadir una tarea nueva el gráfico se queda como estaba.

La solución pasa por los nombres definidos: en lugar de apuntar a un rango fijo, se define un nombre cuya referencia se construye como texto y se resuelve con INDIRECTO, calculando el final del rango a partir del número de filas que tenga la matriz en ese momento.

La idea es la misma que ya usa cualquier rango dinámico clásico: el nombre no guarda unas coordenadas, guarda una fórmula que devuelve unas coordenadas. Cada vez que Excel recalcula, el rango se ajusta, y el gráfico con él.

Con eso cerrado, añadir una tarea al final de la tabla original basta para que aparezca en el gráfico, aunque en medio haya un paso de ordenación.

Funciones y opciones clave

  • ORDENAR — devuelve la matriz ordenada por la columna que le indiques; es lo que permite que el orden del gráfico dependa del cálculo
  • SI en una columna auxiliar — decide el texto de cada etiqueta según el estado de la fila
  • Valor de celdas en el formato de etiquetas de datos — hace que la etiqueta lea un rango en vez del valor de la serie
  • INDIRECTO dentro de un nombre definido — construye un rango que se estira solo
  • Administrador de nombres — donde viven esos rangos dinámicos

Conclusión

Los tres arreglos comparten una idea: sacar la lógica del gráfico y ponerla en la hoja. El orden lo decide una matriz ordenada, el texto lo decide una columna con un condicional y el tamaño lo decide un nombre calculado. El gráfico se queda como lo que debe ser, una ventana que muestra lo que hay, y deja de ser la pieza que hay que retocar a mano cada vez que cambia un dato.

ORDENAR es una función de Microsoft 365. Los nombres definidos con INDIRECTO y las etiquetas por rango funcionan también en versiones anteriores.

Más casos con estas funciones

Más contenido de Excel en InflueXcel