Usar rangos con nombre para crear gráficos dinámicos en Excel

Se aplica a
Microsoft Office Excel 2003
Microsoft Excel 2002

Cubierta del libro


Este artículo es un pasaje seleccionado del libro Microsoft Office Excel 2003 Inside Out de Craig Stinson y Mark Dodge (disponible en inglés).

Si selecciona la serie de un gráfico y examina la línea de fórmulas de Excel, verá que una serie se genera mediante una fórmula que utiliza la función SERIES. SERIES es un tipo especial de función que se utiliza sólo en este contexto para definir una serie de un gráfico. No se puede utilizar en la hoja de cálculo y no se pueden agregar funciones o fórmulas de la hoja de cálculo a sus argumentos.

Acerca de los argumentos de la función SERIES

Para todos los tipos de gráficos salvo los gráficos de burbujas, la función SERIES tiene los argumentos que se muestran en la tabla siguiente. En los gráficos de burbujas, la función SERIES tiene un argumento adicional, que especifica el tamaño de las burbujas.

Argumento Obligatorio/Opcional Especifica
nombre Opcional El nombre que aparece en la leyenda
etiquetas_categoría Opcional Las etiquetas que aparecen en el eje de categorías (si se omite, Excel utiliza enteros consecutivos como etiquetas)
valores Obligatorio Los valores que generará Excel para la serie
orden Obligatorio El orden de trazado de la serie

Cada uno de estos argumentos de la función SERIES se corresponde con los datos específicos indicados en la ficha Serie del cuadro de diálogo Datos de origen (menú Gráfico, comando Datos de origen). En la imagen siguiente se ilustran estas relaciones.

Imagen de la ficha Serie del cuadro de diálogo Datos de origen

La fórmula siguiente aparece en la barra de fórmulas:

=SERIES(Hoja1!$B$1,Hoja1!$A$2:$A$1624,Hoja1!$B$2:$B$1624,1)

Los argumentos de esta fórmula se relacionan con las entradas del cuadro de diálogo Datos de origen del modo siguiente:

  • Argumento nombre     El argumento Hoja1!$B$1 aparece en el cuadro Nombre. Como Hoja1!$B$1 contiene la etiqueta "Precio", la serie se identifica como Precio en el cuadro Series.
  • Argumento etiquetas de categoría     El argumento Hoja1!$A$2:$A$1624 aparece en el cuadro Rótulos del eje de categorías (X).
  • Argumento valores     El argumento Hoja1!$B$2:$B$1624 aparece en el cuadro Valores.
  • Argumento orden     Como el gráfico sólo tiene una serie, el argumento orden es 1. El orden de trazado predeterminado se indica mediante la posición de la serie Precio en el cuadro de lista Series.

¿Qué importancia tiene todo esto?

La fórmula SERIES es importante porque es posible — y en ocasiones deseable — aplicar nombres de rango a algunos de estos argumentos de SERIES. Mediante los nombres de rangos, resulta más sencillo alternar entre el trazado de un conjunto de datos en un gráfico y el trazado de un conjunto totalmente diferente. Y lo que es más importante, al crear un nombre de rango dinámico y utilizarlo como argumento de SERIES, puede crear un gráfico dinámico. Todos los gráficos son dinámicos en el sentido de que cambian para reflejar las modificaciones realizadas en sus datos de origen. Pero mediante los nombres de rango dinámicos, puede conseguir también que un gráfico trace automáticamente nuevos datos conforme se agregan a la hoja de cálculo, o que cree un trazado automáticamente de un subconjunto de los datos de la hoja de cálculo (por ejemplo, los 30 puntos más recientes).

Como probablemente sepa, todos los nombres en Excel son nombres de fórmula y no nombres de rango. Normalmente, las fórmulas se resuelven en referencias de rangos. Por ejemplo, si selecciona A1:A10 en Hoja1 y utiliza el comando Definir (menú Insertar, comando Nombre) para crear un nombre para la selección del rango, Excel define el nombre como:

=Hoja1!$A$1:$A$10

Al incorporar determinadas funciones en la fórmula que define un nombre, puede hacer que ese nombre haga referencia a rangos diferentes de la hoja de cálculo, según las condiciones de la hoja.

Trazar nuevos datos automáticamente

El gráfico mostrado en la imagen anterior traza Hoja1!$A$2:$A$1624 como rótulos del eje de categorías y Hoja1!$B$2:$B$1624 como valores de la serie Precio. Para que este gráfico incorpore automáticamente nuevos puntos de datos agregados en las columnas A y B, debe crear los nombres siguientes en Hoja1:

Nombre Definición
Fecha =DESREF(Hoja1!$A$1,1,0,CONTARA($A:$A)-1)
Precio =DESREF(Hoja1!$B$1,1,0,CONTARA($B:$B)-1)

Lo que estas fórmulas indican en realidad es "Dame un rango que empiece en una fila debajo de A1 (o B1) y cuyo número de filas sea menor en uno que el número de filas rellenas en la columna A (o la columna B)". Cuando se rellenen más celdas en las columnas A y B, la fórmula se resolverá automáticamente en un rango más amplio. El rango sólo incorpora los nuevos datos y éstos aparecen en celdas situadas justo debajo de los datos existentes. Obviamente, para que esta funcionalidad proporcione los resultados deseados, las demás celdas de las columnas A y B deben estar vacías.

Una vez definidos estos nombres dinámicos, puede aplicarlos a la fórmula SERIES del gráfico de alguna de estas dos formas:

  • Mediante el método de barra de fórmulas     Sólo tiene que seleccionar la serie del gráfico que desea modificar y, a continuación, modificar la fórmula SERIES en la barra de fórmulas.
  • Mediante el método de cuadro de diálogo     Seleccione el gráfico y, después, haga clic en Origen de datos en el menú Gráfico. En la ficha Series, modifique las entradas de datos correspondientes.

 Nota   no puede aplicar los nombres mediante el comando del menú que sirve para aplicar nombres (menú Insertar, comando Nombre, comando Aplicar), ya que no funciona en este contexto.

Trabajar en el cuadro de diálogo puede resultar más sencillo ya que puede ver exactamente los cambios realizados, pero con cualquiera de los dos métodos los cambios efectuados en un lugar se reflejan en el otro. En ambos métodos, debe dejar las referencias de la hoja tal como están. Por ejemplo, si el cuadro Valores contiene =Hoja1!$B$2:$B:1624, deje aparte el elemento Hoja1! y sustituya solamente la dirección de rango absoluta por el nuevo nombre de rango. Si el nombre es exclusivo en el libro, Excel lo mostrará como un nombre de nivel de libro, como se muestra en la siguiente imagen.

Imagen de un nombre de nivel de libro.

Trazar sólo los puntos más recientes

Si sólo desea trazar los 30 puntos más recientes en el gráfico de precios de ejemplo, puede modificar los nombres de forma que adopten el siguiente aspecto:

Nombre Definición
Fecha =DESREF(Hoja1!$A$1,CONTARA($A:$A)-30,0,30
Precio =DESREF(Hoja1!$B$1,CONTARA($B:$B)-30,0,30)

Estas fórmulas indican a Excel que empiece en la fila número 30 desde el final del área rellena y cree un rango que abarque 30 filas y 1 columna.

 Nota   si elimina un nombre que se ha aplicado a una serie del gráfico, la serie dejará de ser válida. Excel no restaura la referencia de rango que se ha utilizado como equivalente del nombre eliminado.

 
 
Corresponde a:
Excel 2003