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: Gráficos en Excel
Estimado
ResponderBorrarEstâ roto el vinculo al archivo...
Habrîa que arreglarlo para poder verlo
Me da error cuando ejecuto la explicaciôn
Saludos
Acabo de probar el enlace y veo que funciona.
ResponderBorrarHay 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.
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.
ResponderBorrarCamilo Bustamante
camilobusta@hotmail.com
Hola, gracias por los comentarios.
ResponderBorrarEl 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.
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...
ResponderBorrar=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
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).
ResponderBorrarSi esto no soluciona el problema, puedes enviarme el archivo para que le de un vistazo.
En caso de que sean filas y no columnas, se puede usar la misma sintaxis, para actualizar automaticamente la grafica?
ResponderBorrarHola
ResponderBorrarEn 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
Hola Jorge.
ResponderBorrarHe 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.
Hola Xavi
ResponderBorrartendrías que mandarme el archivo para que le de un vistazo. De tu descripción no puedo saber dónde está el problema.
Hola Marko
ResponderBorrarsi, pero tienes que modificar la fórmula con DESREF de acuerdo.
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.
ResponderBorrarSi me surge cualquier otra cosa, lo comentaré.
Gracias de nuevo ;)
Estimado Jorge:
ResponderBorrarInteresante 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
Hola Rafael
ResponderBorrarel 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.
Hola Jorge
ResponderBorrarCon esta nota quiero felicitarte por esta página web tan currada que has hecho. Me ha ayudado mucho en mis tareas laborales.
Felicidades!
albert
Buenos días Jorge,
ResponderBorrarRealmente 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!
Albertinho
ResponderBorrarsi 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.
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é?
ResponderBorrarHola. 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.
ResponderBorrarEstimado Jorge,
ResponderBorrarMuchas 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
Estimado Jorge,
ResponderBorrarMuchas 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
Rapi,
ResponderBorrar¿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.
Jorge, muchas gracias por responder tan rapido.
ResponderBorrarEn 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
Rapi,
ResponderBorrarpublicaré una nota breve sobre el tema.
Estimado Jorge,
ResponderBorrarMillones de gracias. Con tus explicaciones claras y concisas he aprendido más que con muchos manuales comerciales.
Gracias por tu generosidad
Hola Jorge,
ResponderBorrarEl 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
Dos problemas con esa solución:
ResponderBorrar1 - 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.
Dos aclaraciones:
ResponderBorrar1 - 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
Estimado,
ResponderBorrarcuando 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!
Buenas Tardes Jorge,
ResponderBorrarEn 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
José Miguel,
ResponderBorrarno puedo decirte donde está el problema sin ver el cuaderno. Puedes mandarme el archivo siguiendo las instrucciones que pongo en el enlace "Ayuda".
Buenas tardes Jorge,
ResponderBorrarTras aplicar las correcciones, me funciona todo perfectamente.
Muchas Gracias amigo.
Un saludo
Buenas Jorge
ResponderBorrarAl 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
Trini,
ResponderBorrar¿has revisado las definiciones de los nombres? También puedes mandarme tu archivo para que le de un vistazo (fijate en el enlace Ayuda).
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?
ResponderBorrarFijate en esta nota.
ResponderBorrarQue 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.
ResponderBorrarHola Jorge!
ResponderBorrarTenia 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
Este comentario ha sido eliminado por el autor.
ResponderBorrarFijate en esta nota.
ResponderBorrarSi, 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.
ResponderBorrarPero 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!
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.
ResponderBorrarTal vez no haya entendido tu consulta.
Debo ser yo que no lo hago bien. De momento no lo hare, cuando me ponga te consultare jeje. Muchas Gracias!
ResponderBorrarBuenas Jorge,
ResponderBorrarComo 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
En el menú de formato del eje vertical, en "Opciones del eje" existen tres posibilidades para "el eje horizontal cruza:"
ResponderBorrarautomá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).
Me lo he imaginado... Pero no tengo ni idea de los codigos que debo usar para esto. Donde lo podria encontrar?
ResponderBorrarEstaré úblicando una nota sobre el tema.
ResponderBorrarBuenas 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.
ResponderBorrarLo 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.
ResponderBorrarSiempre es recomendable separar entre el almacenamiento de los datos, los cálculos y los gráficos/reportes.
Hola Jorge,
ResponderBorrarIntenté 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
Hola Jorge,
ResponderBorrarSoy 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
Julieta,
ResponderBorrarlos 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).
Hola.
ResponderBorrarHablando 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.
Hola Jorge Muchas gracias por compartir tus habilidades sobre Excel;
ResponderBorrarEstoy 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?
Aquí pedes ver una explicación completa sobre la sintaxis de la función.
ResponderBorrarHola, estoy intentando un grafico dinámico y doy la siguiente instrucción:
ResponderBorrarActiveChart.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
Hola Tania, ¿que error recibes?
ResponderBorrarEn 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
Hola
ResponderBorrarTengo 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
Editando el gráfico en Power Point o usando una imagen ligada.
ResponderBorrar