Gráficos en Excel – Actualización automática de datos.

miércoles, julio 12, 2006

Supongamos un gráfico basado en una tabla de datos como esta:




Excel nos permite generar con facilidad un gráfico que represente el desarrollo de las ventas:




Ahora supongamos que queremos que el gráfico se actualice cada vez que agregamos datos a nuestra tabla.
Una posibilidad es hacerlo manualmente, para lo cual tendremos que activar el gráfico y en el menú Gráfico---Datos de origen y actualizar al rango de los datos



Pero existe la posibilidad de forzar a Excel a actualizar el gráfico en forma automática cada vez que agreguemos datos a nuestra tabla.
Para lograr esto definimos nombres que contienen fórmulas con la función DESREF para crear rangos automáticos, como ya vimos en la entrada sobre referencias dinámicas.
Para crear un gráfico que se actualice automáticamente seguimos estos pasos:
1 – creamos un nombre llamado Mes (Insertar---Nombres---Definir) con esta fórmula: =DESREF(Hoja1!$A$2,0,0,CONTARA(Hoja1!$A:$A)-1,1)
2 – creamos un segundo nombre que contendrá esta fórmula: =DESREF(Hoja1!$B$2,0,0,CONTARA(Hoja1!$B:$B)-1,1)
3 – Contrariamente a lo que parecería lógico, no aplicamos el nombre con el rango dinámico en lugar de el rango definido en la ventana Rango de Datos (ver arriba), sino en una fórmula que define el gráfico, llamada SERIES. Activamos el gráfico y seleccionamos una de las barras. Al hacer esto veremos la fórmula "SERIES" en la barra de fórmulas



En esta fórmula reemplazamos las referencias a los rangos por los nombres que acabamos de definir:

=SERIES(Hoja1!$B$1,grafico_aut_sp.xls!mes,grafico_aut_sp.xls!ventas,1)

Como se puede ver, Excel agrega automáticamente el nombre del cuaderno donde están definidos los nombres.

A partir de este momento, el gráfico se actualizará automáticamente cada vez que agreguemos los datos de un mes de ventas.

Y antes de finalizar la entrada, unas palabras sobre la fórmula SERIES. Esta fórmula es distinta de las que conocemos en Excel. No podemos utilizarla en celdas de una hoja y tampoco podemos combinar en ella funciones que usamos en las hojas de cálculo. La tarea de esta fórmula es definir los datos que se utilizan en un gráfico. Su sintaxis es la siguiente:
=SERIES(título, rango de la categoría, valores, orden)

Como hemos dicho, no podemos usar funciones regulares de Excel en esta fórmula, pero como hemos visto, si podemos usar nombres que contienen fórmulas comunes.


Categorías: Graficos_

Technorati Tags:

59 comments:

Amadeo,  31 agosto, 2006 00:21  

Estimado
Estâ roto el vinculo al archivo...
Habrîa que arreglarlo para poder verlo
Me da error cuando ejecuto la explicaciôn

Saludos

Jorge L. Dunkelman 01 septiembre, 2006 01:21  

Acabo de probar el enlace y veo que funciona.
Hay que cliquear donde aparece el nombre del archivo completo. A veces es un poco dificil de ver por la cantidad de propaganda (es un sitio gratuito).
Si no logras bajarlo te lo puedo enviar por mail.

Camilo Bustamante,  16 abril, 2007 07:31  

Hola Buenas noches, me llamo Camilo Bustamante, el archivo disponible esta muy bueno y funciona correctamente, pero el problema es que en el texto de explicacion de como hacer la formula "(=DESREF(Grafico!$A$2,0,0,CONTARA(Grafico!$A:$A)-1,1)))" estas separando los argumentos con coma (,) no con punto y coma (;), por este motivo al construir la formula Excel no la acepta, creo que deberias corregir la explicacion. Felicitaciones por tu Blogg esta buenisimo, estoy comenzando a escudriñar en Excel y esta pagina esta de primeras en el menu de mis favoritos.

Camilo Bustamante
camilobusta@hotmail.com

Jorge L. Dunkelman 16 abril, 2007 21:02  

Hola, gracias por los comentarios.
El asunto de los "separadores" depende de la configuración del sistema. Por ejemplo, en inglés se usa la como para separar los miles y el punto para los decimales. En la Argentina, exactamente al revés. Lo mismo con los argumentos de las funciones.

Luis Gonzales,  26 noviembre, 2007 17:40  

tengo un error en la formula no se donde esta ya probe todo y no funciona la grafica que tengo es de multiples datos pongo las formulas a ver si de favor me pueden decir donde esta el problema...

=SERIES('Comp.pozos'!$X$1,'COMPORTAMIENTO.xls'!fecha,'COMPORTAMIENTO.xls'!psic_f,3)
fecha=DESREF(comp.pozos!$F$2,0,0,CONTARA(comp.pozos!$F:$F)-1,1)
psic_f=DESREF(comp.pozos!$X$2,0,0,CONTARA(comp.pozos!$X:$X)-1,1

Jorge L. Dunkelman 26 noviembre, 2007 18:33  

Como en el comentario anterior, fijate si estás usando los separadores indicados (punto o punto y coma, dependiendo de las definiciones de tu sistema).
Si esto no soluciona el problema, puedes enviarme el archivo para que le de un vistazo.

marko1118,  24 enero, 2008 23:36  

En caso de que sean filas y no columnas, se puede usar la misma sintaxis, para actualizar automaticamente la grafica?

Marco 24 enero, 2008 23:41  

Hola

En caso de que el rango fuera de filas y no columnas se puede usar la misma sintaxis, el encabezado de los datos estan en A1 y A2, B1,C1, D1, B2, C2, D2 son los son los valores

Xavi Ivars 25 enero, 2008 13:59  

Hola Jorge.

He encontrado hoy tu blog, y la verdad es que está MUY MUY bien, gracias ;)

He hecho una tabla de este estilo, y me aparece un problema de vez en cuando: los NOMBRES cambian - los valores de celda inicial, y alto (los que contienen algun numero de celdas) cambian de vez en cuando, y la tabla no se actualiza correctmente.

Jorge L. Dunkelman 25 enero, 2008 14:22  

Hola Xavi
tendrías que mandarme el archivo para que le de un vistazo. De tu descripción no puedo saber dónde está el problema.

Jorge L. Dunkelman 25 enero, 2008 14:27  

Hola Marko
si, pero tienes que modificar la fórmula con DESREF de acuerdo.

Xavi Ivars 25 enero, 2008 14:28  

Jorge, gracias por responder tan rápido. Creo que lo he encontrado. En la definición de los nombres, tenia los datos de las celdas, filas y columnas sin el $. Al ponerlo (no se porque) ha empezado todo a ir bien.

Si me surge cualquier otra cosa, lo comentaré.

Gracias de nuevo ;)

Rafael Barreto,  14 julio, 2008 15:28  

Estimado Jorge:
Interesante este artículo. Pero tengo un problema:
Cuando intento cambiar el rango por su nombre en SERIES, me dice que es incorrecta la fórmula.
¿Que puede estar pasando?
Saludos Cordiales
Rafael Barreto

Jorge L. Dunkelman 14 julio, 2008 20:51  

Hola Rafael

el tema de los rangos dinámicos en los gráficos es un tanto truculento. Te sugiero que te fijes en esta nota de mi blog sobre gráficos y presentación de datos.

Albert Reiriz,  30 abril, 2009 13:21  

Hola Jorge

Con esta nota quiero felicitarte por esta página web tan currada que has hecho. Me ha ayudado mucho en mis tareas laborales.

Felicidades!

albert

Albertinho 05 octubre, 2009 12:02  

Buenos días Jorge,

Realmente increíble la ayuda que me proporciona tu blog casi diariamente. Referente a este post, tengo el mismo caso pero con un pequeño problema. Mi eje de las X donde aparecen los nombres de las categorías, incluye dos columnas. Ajustando el gráfico a mano como lo hacía anteriormente, aparecía el número de una serie de preguntas (1,2,3,4 etc.) y debajo aparece, una competencia en la que unas cuantas preguntas se agrupan (Liderazgo, Compromiso etc.). Cada competencia incluye 3 o 4 preguntas.
No se como hacerlo para que el gráfico tenga en cuenta automáticamente dos columnas en vez de una y esto aparezca en el eje del gráfico.

Muchas gracias de antemano!

Jorge L. Dunkelman 05 octubre, 2009 19:26  

Albertinho
si entendí tu consulta lo que debés hacer es:
1 - en la segunda celda de la primer columna (en la primer celda está el encabezamiento) ponés "liderazgo".
2 - en las celdas 2 a 5 de la segunda columna ponés 1, 2, 3, 4 (por ejemplo). Las celdas correspondidentes de la primer columna quedan en blanco.
3 -Hacés lo mismo con Compromiso y el resto de las categorías.
Al crear el gráfico, Excel creará automáticamente las dos series de valores para el eje de la X.
Espero haberme explicado.

Anónimo,  19 enero, 2010 02:44  

Hola. Soy principiante y creé una tabla en Excel, pero algo habré hecho mal porque tiene varios subtotales y otros totales que no se actualizan automáticamente e instantáneamente como sería deseable cada vez que ingreso datos en los rangos respectivos. Si la cierro y luego la abro, entonces los subtotales y totales sí están actualizados. ¿Porqué?

Anónimo,  19 enero, 2010 03:02  

Hola. Ya solucioné mi problema de falta de actualización automática instantánea de una tabla. Era una tontería, un botón que yo no sabía que existia: "opciones para el cálculo" y cambiar de "manual" (como estaba) a "automático". gracias. chau.

Rapi 24 mayo, 2010 08:40  

Estimado Jorge,
Muchas gracias por la ayuda que brinda a traves de su blog.
Mi consulta es para el caso de un gráfico que se debe actualizar automáticamente, pero al mismo tiempo que muestra un nuevo dato, debe dejar de mostrar uno antiguo, de manera que siempre se muestre la misma cantidad de datos. Esto lo podía hacer en versiones anteriores de excel, pero no estoy teniendo suerte con el 2007. Desde ya, muy agradecido por la ayuda que me pueda brindar. Saludo Juan

Rapi 24 mayo, 2010 08:44  

Estimado Jorge,
Muchas gracias por la informacion que nos proporciona a los usuarios excel.
Tengo una consulta. Necesito que un grafico se actualice automáticamente, pero al mismo tiempo que incorpora un nuevo dato, deje de mostrar uno antiguo, de manera que siempre muestre la misma cantidad de datos. Lo podía hacer en versiones anteriores de excel, pero no estoy teniendo suerte en el 2007.
Desde ya, muchas gracias por la ayuda que me pueda brindar. UN saludo, Juan

Jorge L. Dunkelman 24 mayo, 2010 10:46  

Rapi,
¿has probado abrir el archivo que funciona (Excel Clásico) en el Excel 2007?
¿Te refieres a que el gráfico muestre siempre los últimos datos? Por ejemplo, los datos de los últimos 12 meses.

Rapi 25 mayo, 2010 06:51  

Jorge, muchas gracias por responder tan rapido.
En realidad, estoy creando un archivo nuevo, tratando de recrear una funcionalidad que recuerdo haber probado en versiones anteriores de excel.
Respecto a la función que me gustría usar, es efectivamente la que Ud. me menciona en el ejemplo, de mantener en el gráfico los últimos doce meses, aunque se realicen actualizaciones periodicas. Agradezco nuevamente las respuestas. Un saludo

Juan

Jorge L. Dunkelman 25 mayo, 2010 18:20  

Rapi,
publicaré una nota breve sobre el tema.

Bernabé García,  17 septiembre, 2010 13:27  

Estimado Jorge,
Millones de gracias. Con tus explicaciones claras y concisas he aprendido más que con muchos manuales comerciales.
Gracias por tu generosidad

Anónimo,  19 octubre, 2010 14:51  

Hola Jorge,

El otro día le estaba explicando a alguien estos gráficos que se actualizan dinámicamente gracias a la función DESREF (que me encanta y que aprendí contigo) y me hizo una sugerencia-pregunta que al comprobarlo me hizo caer en la cuenta de una solución mucho más sencilla.
Si en la función SERIES reemplazamos el $A$4:$A$21 y $B$4:$B$21 por $A$4:$A$1000 y $B$4:$B$1000 (por ejemplo el 1000) también el gráfico va incorporando las series a medida que se van creando nuevas filas.
Aparentemente no veo nada que pueda hacerlo fallar, a no ser que metamos datos de otra naturaleza más abajo en esas columnas . Pero si hacemos una hoja correcta eso no nos debería ocurrir.
No sé cómo lo ves.
Un saludo,
Sergio

Jorge L. Dunkelman 19 octubre, 2010 15:02  

Dos problemas con esa solución:
1 - no es dinámica; cuando lleguemos a la fila 1001 tendremos que volver a reescribir el rango ya que los nuevos datos caen fuera de él. Todo la idea de los rangos dinámicos es que no debemos reescribirlos!
2 - La serie incluye ahora puntos vacíos (todas las celdas que aún no contienen datos), lo que distorsiona nuestro gráfico.

Anónimo,  19 octubre, 2010 22:06  

Dos aclaraciones:
1 - cuando digo 1000 es por no decir 65536 para Excel 2003.
2 - Todas las celdas que aún no contienen datos no distorsionan el gráfico. Eso ya lo había comprobado.
Gracias

Jorge L. Dunkelman 19 octubre, 2010 23:41  

Estimado,
cuando los rangos en la función SERIES se extienden hasta la fila 65536, el eje de las X tiene, suponiendo que le rango empieza en la fila 2, 65535 puntos! Excel proporciona el gráfico de acuerdo a la cantidad de puntos en el eje de las X. Si la serie de datos tiene, digamos, 20 datos, éstos serán prácticamente invisibles en semejante gráfico.
De todas maneras, si la técnica funciona para tus necesidades, OK!

José Miguel,  17 enero, 2011 18:34  

Buenas Tardes Jorge,
En principio darte la enhorabuena por este blog, el cual no es de gran ayuda.
Verás, estoy intentado crear una plantilla de tabla dinámica acorde con lo indicado en el post, pero cuando introduzco la serie no doy con la tecla; me da Error "La referencia no es válida".
He introducido los siguientes datos:
- =SERIES(Hoja1!$B$1;'Prueba.xls'!Date;'Prueba.xls'!VLN1;1)

Donde Date ="DESREF(Hoja1!$A$2,0,0,CONTARA(Hoja1!$A:$A)-1,1)"
Y VLN1 ="DESREF(Hoja1!$C$2,0,0,CONTARA(Hoja1!$C:$C)-1,1)"

Muchas gracias de antemano.

Un saludo

Jorge L. Dunkelman 17 enero, 2011 20:27  

José Miguel,
no puedo decirte donde está el problema sin ver el cuaderno. Puedes mandarme el archivo siguiendo las instrucciones que pongo en el enlace "Ayuda".

José Miguel,  19 enero, 2011 21:10  

Buenas tardes Jorge,
Tras aplicar las correcciones, me funciona todo perfectamente.
Muchas Gracias amigo.
Un saludo

trini 19 febrero, 2011 00:02  

Buenas Jorge
Al introducir la formula de las series me da error
=(Hoja1!$B$1;trini.xls!mes;trini.xls!trini;1)
No veo el fallo comparandolo con tu archivo!!

Gracias

Jorge L. Dunkelman 19 febrero, 2011 07:50  

Trini,

¿has revisado las definiciones de los nombres? También puedes mandarme tu archivo para que le de un vistazo (fijate en el enlace Ayuda).

Anónimo,  31 diciembre, 2011 18:10  

Hola. Gracias por compartir tus conocimientos, no sabes de cuanta ayuda son. Hice el ejercicio que planteas y resulta perfecto. Solo tengo curiosidad de saber porque se coloca -1,1 en las fórmulas desref?

Julian Saldarriaga Blandon 21 junio, 2012 19:55  

Que buen post, pocos con explicaciones tan claras y concisas, fáciles de aprender hasta para personas no muy entendidas en estos temas, antes de hacer cualquier código en Excel verifico en tus aportes. De nuevo muchas gracias sos una persona admirable ojala hubieran más personas como voz con la disponibilidad y la amabilidad de ayudar a otros.

Anónimo,  01 julio, 2012 13:52  

Hola Jorge!
Tenia problemas al usar la funcion que describes ya que me cogia un monton de datos de celdas vacias "" con CONTARA(), pero al usar CONTAR(), que no te recoge datos como "" ha ido perfecta. Por si a alguien le pasa.
Es increible esta formula que describes, muchas gracias!

Sabrias como poder marcar MIN i MAX en un grafico de estos?

Saludos

Jorge L. Dunkelman 01 julio, 2012 20:17  
Este comentario ha sido eliminado por el autor.
Anónimo,  02 julio, 2012 08:58  

Si, esta formula la utilice con un grafico de lineas y haciendo una marca en la serie2 y funciona bien cuando solo añadimos numeros en la tabla.

Pero supongamos que tenemos una tabla con columnas A,B,C,D,E de 100 filas de las cuales solo 10 estan con numeros, las otras 90 en la columna D tienen la formula de este tipo
=SI(ESERROR(C2/B2);"";C2/B2)
Cuando en la columna E ponemos esta =SI(C2=MIN($C:$C);C2;NOD()) a partir de la fila 11 tambien nos aparece N/A entonces ya no puedo hacer el grafico automatico.
Como puedo hacer para que donde solo haya la formula no me aparezca N/A?

No se si me he explicado bien :)

Muchas gracias!

Jorge L. Dunkelman 06 julio, 2012 17:09  

Si usas la segunda fórmula, con NOD() en lugar de "", no tendrías que tener ningún problema Cuando el valor del punto de la serie es #NA, Excel no lo representa.
Tal vez no haya entendido tu consulta.

Anónimo,  19 julio, 2012 22:22  

Debo ser yo que no lo hago bien. De momento no lo hare, cuando me ponga te consultare jeje. Muchas Gracias!

Anónimo,  19 julio, 2012 22:28  

Buenas Jorge,

Como puedo hacer para que, en un grafico, el eje horizontal cruce en un determinado valor que puede variar dependiendo de quien lo use la hoja, con lo que deberia ir referenciado a una celda.

El grafico indica "Total Equity". Pero cada usuario empezara con un capital Inicial distinto y me gustaria que no tuvieran que ir a cambiarlo en formato de ejes.

Muchas gracias por tus conocimientos

Jorge L. Dunkelman 20 julio, 2012 17:32  

En el menú de formato del eje vertical, en "Opciones del eje" existen tres posibilidades para "el eje horizontal cruza:"

automática

valor del eje (aquí se puede introducir el valor deseado)

valor máximo del eje

Para usar la segunda opción dependiendo del valor de una celda, tendrás que usar un evento (macro).

Anónimo,  23 julio, 2012 22:09  

Me lo he imaginado... Pero no tengo ni idea de los codigos que debo usar para esto. Donde lo podria encontrar?

Jorge L. Dunkelman 27 julio, 2012 18:25  

Estaré úblicando una nota sobre el tema.

Anónimo,  24 enero, 2013 00:31  

Buenas tardes Jorge, te hago una consulta, hay alguna posibilidad de arreglar este método de actualización automática de gráficos, para que el rango sólo vaya hasta la celda anterior a la primer celda vacía, ya que el problema es que si tenemos otros datos más abajo en la columna donde están los valores del gráfico, la función contara, los cuenta, lo cual agranda el rango y en realidad no es que haya mas valores del gráfico, y esto empeora si al agranderse el rango, empiezan a entrar valores que no pertenecen al gráfico. Desde ya, muchas gracias.

Jorge L. Dunkelman 24 enero, 2013 09:31  

Lo más sencillo y recomendable desde el punto de vista de manejo de datos, es poner los datos que no pertenecen a ese gráfico en otro área de la hoja.
Siempre es recomendable separar entre el almacenamiento de los datos, los cálculos y los gráficos/reportes.

Anónimo,  04 marzo, 2013 17:38  

Hola Jorge,
Intenté hacer este ejercicio pero con mas de una serie, pero sale un error, creo que es cuando hago referencia al nombre,
Es necesario poner el nobre del archivo? Y en caso de que el archivo cambie de nombre es necesario actualizar manualmente?
Gracias
Atte Julieta

Anónimo,  04 marzo, 2013 17:56  

Hola Jorge,
Soy Julieta de nuevo, de hecho acabo de darme cuenta que lo que necesito no es aumentar columnas a mi grafico; sino series, me gustaria saber si es posible hacerlo de este modo o no.
Gracias

Jorge L. Dunkelman 05 marzo, 2013 07:11  

Julieta,
los nombres son los rangos de las series. Usamos nombres para poder cambiarlos en forma dinámica, sin necesidad de tener que editar las fórmulas. Las columnas son la representación gráfica de las series (que a su vez son el conjunto de datos que representamos en el gráfico).

david trillo 30 agosto, 2013 14:46  

Hola.
Hablando de automatización, te dejo estos enlaces que quizás puedan ser de interés para tus lectores:
http://www.manejandodatos.es/2013/08/dimensiones-de-tablas-en-vba-excel/

Y aprovechando las funciones anteriores, automatizar la creación de gráficas en todas las solapas de un archivo Excel.

http://www.manejandodatos.es/2013/08/creando-graficos-en-excel-automaticamente/

Un saludo.

James Rodríguez Blandón 23 octubre, 2013 17:34  

Hola Jorge Muchas gracias por compartir tus habilidades sobre Excel;
Estoy intentando aplicar este tema de la Función Desref para hacer un gráfico cuyos datos están en filas y no columnas y no he podido lograrlo.
¿Puedes darme una explicación?

Jorge Dunkelman 24 octubre, 2013 07:15  

Aquí pedes ver una explicación completa sobre la sintaxis de la función.

Tania Labastida,  09 octubre, 2014 17:54  

Hola, estoy intentando un grafico dinámico y doy la siguiente instrucción:
ActiveChart.SeriesCollection(1).Formula = "=SERIES(""Markit"", Datos CDS Instituciones!rdate , Datos CDS Instituciones!rmar,1)"
pero me dice error, rdate es el primer rango y r markit el segundo

Jorge Dunkelman 10 octubre, 2014 17:45  

Hola Tania, ¿que error recibes?
En principio me parece que el problema esta que el nombre de la hoja que contiene los rangos, cuando tiene espacios entre las palabras, debe esta limitado por apóstrofes ('). Por ejemplo 'Datos CDS Instituciones'!rmar

Unknown 13 octubre, 2015 20:22  

Hola
Tengo un problema, al intentar actualizar un gráfico en excel me agrega a los datos en la tabla del power point muchos decimales, no se como solucionar esto.
Gracias por su ayuda

Jorge Dunkelman 17 octubre, 2015 22:59  

Editando el gráfico en Power Point o usando una imagen ligada.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP