El blog de PlanillaExcel

Gráficos con actualización automática en Excel

En este video-tutorial te explico como hacer que un gráfico se actualice de manera automática tomando los nuevos datos.

Descarga el archivo aquí

 

Explicación

Cuando realizamos gráficos en Excel es frecuente que tengamos una serie de datos que se vaya incrementando en el tiempo. Esto hace necesario ir actualizando el gráfico a medida que ingresamos datos. 
 

 

En lugar de ir tomando la nueva información manualmente para que el gráfico refleje estos datos que se agregaron, podemos optar por hacerlo de manera automática a través de 2 métodos:

1.- Insertando una tabla

Este método consta en insertar una tabla de Excel en nuestra plantilla y a partir de allí, insertar un gráfico.
Para insertar una tabla, selecciono la información y en la pestaña Insertar elijo "insertar tabla".

Insertar una tabla en excel
 

Una vez hecho esto, insertamos el gráfico.

Insertar grafico dinamico

Cada vez que quiero agregar un dato, simplemente con el mouse te ubicas en el margen inferior derecho de la tabla y cuando se hace una doble flechita, haz click y sin dejar de oprimir, muevete hacia abajo y agregas el dato. Veras que así aparece de manera automática en el gráfico.


 

 

2.- Utilizando las funciones DESREF y CONTARA

Si por alguna razón no quieres insertar una tabla, puedes crear un rango dinámico para que el gráfico se actualice cada vez que se agregan datos.

Fórmula 

=DESREF($A$1;1;0;CONTARA($A$2:$A$500))

 

DESREF devuelve una celda (o un rango de celdas) de acuerdo a punto de partida, especificando la cantidad de filas y la cantidad de columnas que debe moverse. La función genérica es:

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

 

Por ejemplo, si escribo:

 =DESREF(A1;2;1;2;0)

 
Le estoy diciendo que, partiendo desde la celda A1, se mueva 2 filas hacia abajo y una columna a la derecha y me devuelva el rango que abarca 2 filas . Esto dará como resultado 150 y 430

 

Desref
 

Lo que tiene que hacer DESREF es que a medida que se agregan datos, tanto en la columna de "Mes" como en la columna "Ventas", la función DESREF vaya agregando esa nueva información.

Y para eso vamos a usar la función CONTARA. La función CONTARA cuenta el número de celdas no vacías en un rango.  Por ejemplo si aplicamos

=CONTARA(A2:A500)

El resultado es 4, ya que hay 4 celdas no vacías. Cuando agrego un valor, actualizará el número a 5 y así sucesivamente con cada valor que agregue.

graficos dinamicos

Nota: elijo 500 porque supongo que como mucho tendré esa cantidad de filas.


Esta función la utilizaré como argumento de "alto" en DESREF.  Así, para la columna donde están los meses, la fórmula sería:

=DESREF($A$1;1;0;CONTARA($A$2:$A$500))

Y para la columna donde están las ventas, la fórmula sería:

=DESREF($B$1;1;0;CONTARA($B$2:$B$500))

Estas fórmulas las vamos a usar para crear nombres y posteriormente las utilizaremos para insertar un gráfico. 
Para crear nombres, debemos ir a pestaña Fórmulas>Administrador de nombres>Nuevo>
Y en nombre escribimos "Fecha" y en la parte donde dice “se refiere a ” escribimos la fórmula que creamos. Lo mismo para la información de "Ventas".

graficos dinamicos desref

Una vez hecho esto, el paso que sigue es simple: inserto el grafico seleccionando los datos.  Luego, con el gráfico seleccionado, iré a la pestaña Diseño>Seleccionar datos> Modificar 


grafico dinamico desref

 

Allí en la ventana emergente selecciono la celda que tiene el título (observa que al lado debería aparecer el nombre que creaste: en este caso ventas) y hago click en "Aceptar"

Edito nombre grafico excel

Y listo, ahora si, a medida que agreguemos datos, se actualizan las referencias

Espero que lo pongas en práctica pronto.

Saludos.

Cecilia - www.planillaexcel.com

Pd: si quieres aprender más de Excel, dicto cursos online desde el nivel básico a avanzado.
Haz click aquí para ver los cursos.