Un rango desbordado como serie de un gráfico: el nombre tiene que ser de ámbito Hoja

Miki llega al grupo con un caso de esos que no dan error, simplemente no funcionan. Quiere un gráfico que crezca solo: si mañana hay más datos, que la serie se estire sin tocar nada. La receta es la de siempre, un nombre definido que apunte al rango, solo que aquí el rango lo genera una fórmula que desborda.

El libro es mínimo, y merece la pena verlo porque es el escenario moderno de este problema:

``
B7: =SECUENCIA(31;;FECHA(2026;1;1))
C7: =ALEATORIO.MATRIZ(FILAS(B7#);;5000;10000;1)
`

Una columna de fechas y otra de valores del mismo alto, atada a la primera con FILAS(B7#). En el administrador de nombres define los dos nombres apuntando a los rangos desbordados con el operador #:

`
x → =Prueba!$B$7#
y → =Prueba!$C$7#
`

Y al intentar usarlos como serie del gráfico, nada. Los nombres están bien escritos, las fórmulas calculan, el # funciona en cualquier celda de la hoja. Pero el gráfico no los acepta.

Dos explicaciones sobre la mesa, y una prueba que descarta una

La primera hipótesis la lanza Hector: que el problema sean los nombres cortos. Llamar x o y a un nombre definido choca con algo que ya parece una referencia de celda, así que sugiere probar con varX y varY.

Es una sospecha razonable, y además es un problema real en otros contextos. Pero aquí se puede comprobar, y Miki la comprueba: renombra y vuelve a intentarlo. "Nada, me pasa lo mismo". Hipótesis descartada.

La que sobrevive es la otra, y Miki ya la había encontrado por su cuenta un rato antes: "me funciona cuando cambio el ámbito". El nombre creado con ámbito Libro no sirve para la serie del gráfico; creado con ámbito Hoja, sí, y entonces la serie se redimensiona sola como se esperaba.

Conviene quedarse con el método tanto como con la respuesta: había dos causas candidatas y una prueba de treinta segundos que las separa. Cambiar una cosa cada vez es lo que convierte una sospecha en un diagnóstico.

Por qué pasa

Hector da la explicación de fondo: el operador # (internamente ANCHORARRAY) no es una referencia cualquiera, es una instrucción de "todo lo que se desborde desde aquí", y para resolverla hace falta saber desde qué hoja se desborda. Un nombre de ámbito Libro no lleva esa hoja de referencia consigo, así que en el momento de dibujar la serie no hay desde dónde desbordar. Con ámbito Hoja, la hoja está implícita en el propio nombre y la referencia se resuelve.

Es una explicación que encaja con todo lo observado, pero conviene decir que lo verificado es el comportamiento, no el mecanismo: nadie del hilo abrió el motor de Excel para confirmarlo. Si trabajas con esto, lo que puedes dar por bueno es la regla práctica, no necesariamente el porqué.

Si necesitas ámbito Libro de todas formas

A veces el ámbito Hoja no es una opción, porque el nombre se usa desde varias hojas. Para ese caso Hector propone volver al clásico anterior al operador #, midiendo el alto con CONTARA en lugar de dejar que Excel lo deduzca del desbordamiento:

`
=DESREF(Prueba!$B$7; 0; 0; CONTARA(Prueba!$B:$B)-1; 1)
`

Dos avisos sobre esta alternativa, porque en el hilo no llegó a probarla nadie. El primero, que DESREF es una función volátil: se recalcula cada vez que tocas cualquier celda del libro, y en un libro grande eso se nota. El segundo, que el -1 está descontando la fila de encabezado del ejemplo concreto, así que hay que ajustarlo a cada caso y vigilar que no haya celdas sueltas más abajo en la columna, porque CONTARA` las contaría igual.

La derivada

Del hilo salió además una petición de producto: poder elegir el ámbito al crear un nombre desde el add-in de Influexcel, que hasta ahora los creaba siempre con ámbito Libro. O sea, justo el que rompe este escenario. Queda anotada.

El fichero adjunto es el caso mínimo de reproducción de Miki, con los dos nombres ya definidos en ámbito Libro para que puedas ver el fallo y arreglarlo tú mismo cambiando el ámbito.

Un gráfico que crece solo, y un nombre definido que no colabora

Uno de los usos más agradecidos de las fórmulas que desbordan es alimentar un gráfico dinámico: escribes una fórmula, se despliega sola hasta donde haga falta, y el gráfico se estira con ella sin que tengas que rehacer la serie cada mes. La receta clásica pasa por un nombre definido, y ahí es donde a Miki se le atascó.

El detalle que lo desbloquea no aparece en ningún mensaje de error: el nombre tiene que crearse con ámbito Hoja, no con ámbito Libro.

El problema

El libro de partida es mínimo. Una columna de fechas generada con SECUENCIA y otra de valores atada al mismo alto:

B7: =SECUENCIA(31;;FECHA(2026;1;1))
C7: =ALEATORIO.MATRIZ(FILAS(B7#);;5000;10000;1)

FILAS(B7#) es lo que mantiene las dos columnas sincronizadas: si mañana la primera desborda 45 filas en vez de 31, la segunda las sigue automáticamente.

Después, dos nombres definidos que apuntan a esos rangos desbordados usando el operador #:

x  →  =Prueba!$B$7#
y  →  =Prueba!$C$7#

Todo correcto sobre el papel. Las fórmulas calculan, los nombres están bien escritos y el operador # funciona sin problemas desde cualquier celda de la hoja. Pero al usarlos como serie del gráfico, no hay manera.

Solución paso a paso

1. Descartar la hipótesis de los nombres cortos

La primera sospecha del hilo fue que llamar x o y a un nombre definido chocara con algo que Excel ya interpreta como referencia de celda. Es una causa plausible y da problemas en otros contextos, así que merecía una prueba: renombrar a varX y varY y volver a intentarlo.

El fallo se mantuvo idéntico. Hipótesis descartada, y treinta segundos bien invertidos: cambiar una sola cosa cada vez es lo que separa una sospecha de un diagnóstico.

2. Cambiar el ámbito del nombre

Al crear un nombre en el administrador de nombres, Excel ofrece un desplegable de ámbito con dos opciones: el libro entero o una hoja concreta. Por defecto se queda en Libro, que es lo que casi siempre interesa.

Aquí es justo al revés. Con el nombre recreado en ámbito Hoja, el gráfico lo acepta y la serie se redimensiona sola en cuanto la fórmula desborda más filas.

La razón de fondo es que el operador # no es una referencia estática, sino una instrucción del tipo "todo lo que se desborde desde esta celda". Para resolverla hace falta saber desde qué hoja se desborda, y un nombre de ámbito Libro no lleva esa hoja consigo. Con ámbito Hoja, el contexto está implícito en el propio nombre.

3. Si el ámbito Libro es obligatorio

Cuando el nombre tiene que ser accesible desde varias hojas, queda el recurso anterior a las matrices dinámicas: medir el alto a mano en lugar de dejar que Excel lo deduzca del desbordamiento.

=DESREF(Prueba!$B$7; 0; 0; CONTARA(Prueba!$B:$B)-1; 1)

Dos precauciones antes de adoptarla. DESREF es volátil, es decir, se recalcula cada vez que tocas cualquier celda del libro, y en un archivo grande el coste se nota. Y el -1 está descontando la fila de encabezado de este ejemplo concreto, así que hay que ajustarlo y asegurarse de que no haya celdas sueltas más abajo en la columna, porque CONTARA las sumaría igual.

Funciones clave

  • SECUENCIA: genera una lista de valores correlativos que desborda sola. Con FECHA como valor inicial produce una serie temporal completa desde una única celda.
  • ALEATORIO.MATRIZ: devuelve una matriz de números aleatorios con las dimensiones que le indiques. Aquí sirve para simular datos de prueba del mismo alto que la columna de fechas.
  • FILAS: cuenta las filas de un rango. Aplicada a un rango desbordado con #, ata el alto de una fórmula al de otra.
  • El operador #: se pone detrás de la celda origen y significa el rango completo que esa fórmula desborda, sea cual sea su tamaño hoy.
  • DESREF y CONTARA: la pareja clásica para construir rangos dinámicos antes de que existieran las matrices dinámicas. Sigue siendo la salida cuando el ámbito Hoja no es viable.

Conclusión

Es un caso pequeño que deja dos cosas. La práctica: si un nombre definido con # no funciona donde debería, mira el ámbito antes que nada. Y la de método: había dos explicaciones posibles y una de ellas se podía refutar con una prueba de medio minuto, así que se refutó en lugar de discutirla.

Este tipo de detalles rara vez están documentados, y aparecen porque alguien se topa con ellos y lo cuenta. Así funciona la comunidad de Influexcel: un problema real, varias hipótesis y quien tiene el fichero delante haciendo la prueba que las separa.

Más casos con estas funciones

Más contenido de Excel en InflueXcel