Haciendo un gráfico con un rango dinámico en Excel

Hola. Supongo que todo usuario de Excel debe a estas alturas saber que para hacer un gráfico hay que especificar un rango de valores como entrada.

Pero y si nuestro rango es dinámico y continúa creciendo o disminuyendo? 

Se puede editar la tabla de datos de entrada cada vez que se agrega una fila, pero esto se hace muy pesado cuando se ha de hacer repetidamente ¿No sería genial si los rangos son dinámicos y los gráficos se actualizan automáticamente cuando se agregan (o eliminan) filas?

Bueno, pues se puede hacer  muy fácilmente utilizando las fórmulas de Excel y el administrador de nombres.

Hoy vamos a ver 2 maneras de hacer esto.

1. Usando Tablas para gráficos dinámicos

La manera más sencilla de lograr este efecto es utilizando Tablas. Gracias a ella es fácil obtener este efecto. Mira el siguiente Gif:

2. Usando DESREF para gráficos dinámicos

Si por alguna razón no puedes utilizar Tablas, el siguiente método es utilizar la fórmula DESREF en combinación del Administrador de nombres.

Ya sabemos que la función DESREF en Excel nos devuelve una referencia a un rango de celdas que ha sido desplazado respecto a otra referencia que hemos especificado. Ya se que suena confuso, pero sigue leyendo y verás cómo se entiende.

Pasos para la creación de gráficos rangos dinámicos utilizando la función DESREF

1. Identificar los datos de los cuáles se desea realizar rango dinámico

En nuestro caso, los datos deben ser agregados en la siguiente tabla. A medida que el usuario continúa añadiendo nuevas filas tendremos que actualizar los datos de origen del gráfico.

Asumamos la tabla de datos está en el rango de celdas: $F$6:$G$14

2. Escribe una fórmula con DESREF y utiliza el administrador de nombres en ellas

Bien, el problema es que a medida que añadimos una fila al final (o eliminamos una fila), debemos actualizar el rango de datos del gráfico. Para ello, podemos utilizar la fórmula DESREF.

------------------------------------------------------------------------------------------------------------------------

RECUERDA QUE:

Tú puedes colaborar para seguir manteniendo este blog de una manera gratuita haciendo varios clics en los anuncios de la página. Como referencia, 2 clics por artículo. Muchas gracias por querer colaborar.

------------------------------------------------------------------------------------------------------------------------

Veamos un repaso sobre cómo utilizar la función DESREF, para ello veamos unos cuantos ejemplos:

=DESREF(A1,3,1) devuelve el valor de la celda B4, ya que partiendo de A1, le indicamos que se mueva 3 filas hacia abajo, y una columna hacia la derecha.

=DESREF(A1,3,1,2) devuelve el rango B4:B5, ya que como antes, nos desplazamos a la celda B4, pero ahora indicamos que añada una fila hacia abajo.

=DESREF(A1,3,1,2,2) devuelve el rango B4:C5, ya que como antes, nos desplazamos a la celda B4, pero ahora indicamos que añada una fila hacia abajo y una columna hacia la derecha.

Al obtenerse un rango, se puede hacer otras operaciones con ese rango. Por ejemplo.

=SUMA(DESREF(A1,3,1,2)) devolvería la suma de los valores en el rango B4:B5.

3. Crear un nuevo rango con nombre y escriba la fórmula DESREF

Ahora asignamos un nuevo nombre y en donde pone "se refiere a:" introducimos la fórmula DESREF, que generaría un rango dinámico de valores basada en los valores de ventas escritos en la columna G. 

He utilizado la siguiente fórmula.

=DESREF($G$6,0,0,CONTARA($G$6:$G$14),1)

Establecer el nombre del rango denominado como "Ventas_datos" o algo por el estilo.

La función CONTARA es la que hace la fórmula anterior dinámica al incrementar o reducir la 'altura' de la función DESREF cada vez que se añade o se elimina una fila.

Para explicar rápido la fórmula anterior vemos, la sintaxis de la función DESREF:

DESREF(ref, filas, columnas, [alto], [ancho])

• Ref (Obligatorio): Es la referencia en la que desea basar la desviación. La referencia debe referirse a una celda o un rango de celdas adyacentes; en caso contrario, DESREF devuelve el valor de error #¡VALOR!.

• Filas (Obligatorio): Es el número de filas, hacia arriba o hacia abajo, al que desea que haga referencia la celda superior izquierda. Si el argumento filas es 5, la celda superior izquierda de la referencia pasa a estar cinco filas más abajo que la referencia. Filas puede ser positivo (lo que significa que está por debajo de la referencia de inicio) o negativo (por encima).

• Columnas (Obligatorio): Es el número de columnas, hacia la derecha o izquierda, al que desea que haga referencia la celda superior izquierda del resultado. Si el argumento columnas es 5, la celda superior izquierda de la referencia pasa a estar cinco columnas hacia la derecha de la referencia. Columnas puede ser positivo (lo que significa a la derecha de la referencia de inicio) o negativo (a la izquierda).

• Alto (opcional): El número de filas (alto) que se desea que tenga la referencia devuelta.

• Ancho (opcional): El número de columnas (ancho) que se desea que tenga la referencia devuelta

Con lo cual nuestra fórmula refleja lo siguiente, empezando desde la celda G6, le decimos que se desplace cuantas celdas no esten vacías en el rango, y lo hacemos por una columna sólo. Así si la CONTARA es 2, la fórmula sólo devolvería el rango G6:G7.

Ahora repetimosel paso anteriorpara la columna Años y lo llaman "Años_datos".

4. Crear un gráfico de columnas y establecer los datos de origen de estos rangos con nombre

Para los datos de origen utilizan los rangos con nombre que acabamos de crear.

Vemos primero la serie ventas:

Y ahora el rótulo del eje:

Importante: Se debe utilizar el rango con nombre junto con el nombre de la hoja, si no aceptará el rango con nombre de datos de origen del gráfico.




Y ya está! Ahí teneis a todo un Gráfico dinámico!!!

Te ha gustado el artículo?

No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 3 siguientes acciones:

1. Puedes invitarme a un café pinchando en la siguiente imagen.

2. Puedes hacer clic en los anuncios mostrados en la página

3. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.

Descarga:

Para descargarse el archivo con el ejemplo ve al siguiente enlace:

Gráfico dinámico

Quieres aprender más en Excel o tienes algún problema que necesita solución?

Puedo ofrecer servicios de consultoría así como cursos personalizados online. Mírate el siguiente enlace si estás interesado/a.

Consultoría Excel / Cursos de Excel

Subscríbete y recibe todos las entradas por email

Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.

O sígueme por las redes sociales

Facebook, Twitter, Youtube y Google+