Gráficos dinámicos según valor de lista desplegable

sábado, diciembre 10, 2011

Los más memoriosos lectores de este blog recordarán seguramente aquella nota que describía la técnica para crear un gráfico interactivo según el valor de la celda activa (la nota completa aparece en mi blog sobre gráficos, actualmente inactivo). También recordarán que esa técnica no funciona en las versiones posteriores a Excel 2003.

En esta nota mostraré una técnica que funciona con todas las versiones de Excel. El modelo es distinto y se basa en los valores de una lista desplegable. En esta nota mostraré un modelo sencillo y señalaremos sus limitaciones. En las próximas notas veremos otras soluciones que superaran esas limitaciones.

Y yendo al grano, supongamos esta matriz que muestra las ventas por trimestres de los vendedores de una firma



Queremos crear un modelo que permita representar en gráfico las ventas por vendedor, eligiéndolos de una lista desplegable. Las técnicas para lograr esto ya han sido expuestas en este blog en el pasado, por lo que haremos una explicación sucinta.

Lo que queremos lograr es esto:



Lo primero es crear un nombre que se refiera al rango que contiene los nombres de los vendedores. Lo hacemos fácilmente usando la opción “crear desde la selección” del grupo “nombres definidos” en el menú “Fórmulas”



Creamos la lista desplegable en la celda C5 y definimos el nombre “grfTitulo” que se refiere a esta celda. Para crearlo usamos el cuadro de nombres (seleccionamos al celda, escribimos el nombre en el cuadro y apretamos Enter)



Ahora definimos un nombre para cada uno de los rangos que contienen las ventas de los distintos vendedores. Esto también lo haremos usando la opción “crear desde la selección”



Ahora podemos usar los nombres de los vendedores para definir los rangos de los valores que queremos ver en el gráfico. Pero aquí se nos presenta un problema. Dado que los espacios no están permitidos en los nombres definidos, Excel crea los nombres poniendo un “_” (underline) entre el nombre y el apellido. El valor que obtenemos de la lista desplegable no contiene el guión. La solución es transformar el valor obtenido de la lista desplegable agregándole el guión. Esto la hacemos con la función SUSTITUIR en una celda oculta (en este ejemplo en la celda A5)



El próximo paso es crear un gráfico, de líneas en nuestro caso, usando la primer fila de la tabla



Para que nuestro gráfico sea dinámico creamos este nombre definido

grfSeriesX =INDIRECTO(reporte!$A$5)

La función INDIRECTO interpreta el texto en la celda A5 y lo convierte en el rango que hemos definido previamente.

Ahora reemplazamos los rangos en la función SERIES del gráfico de la siguiente manera



Para ver los nombres definidos apretamos F3.

Nuestro modelo funciona de la siguiente manera:


  • Elegimos un valor de la lista deplegable
  • El valor es transformado en la celda A5
  • La función INDIRECTO en el nombre grfSeriesX lo transforma en el rango del vendedor elegido
  • El nombre grfTitulo pone el rango que contiene el nombre del vendedor de manera que aparezca en el título del gráfico.


Este modelo tiene varias limitaciones; la más grave es que si agregamos trimestres y/o vendedores tenemos que modificar los nombres definidos. Podemos, por supuesto, crear nombres dinámicos con DESREF o INDICE como ya hemos mostrado en varias oportunidades en este blog. Pero hay soluciones mejores que mostraremos en las próximas notas.

El archivo con el ejemplo se puede descargar aquí.

20 comments:

Luis Waldir Lázaro Apolaya 12 diciembre, 2011 16:59  

Muy buena la explicación pero lo que no me queda claro es lo del grafico dinámico como se crea el nombre definido donde o en que celda ponerlo gracias (es decir donde escribo indirecto(reporte)

Jorge L. Dunkelman 12 diciembre, 2011 22:08  

Hola Luis, los nombres se crean

Excel 2007/10 - Fórmulas--Administrador de nombres

Excel 97-2003 - Insertar-Nombres

En el formulario que se abre pones "grfTitulo" como nombre y en "se refiere a" la fórmula INDIRECTO(reporte!$A$5)

Anónimo,  15 febrero, 2012 23:36  

Excelente Blog Jorge

Ya realice el ejemplo, ahora lo quise intentar hacer con tablas para que al momento de insertar un nuevo vendedor, automaticamente apareciera los datos en el grafico, pero me sale un error.

Pregunta: Es posible realizarlo con tablas?

Gracias

Jorge L. Dunkelman 16 febrero, 2012 07:03  

El error se produce porque el rango definido para el nuevo vendedor no existe. Excel no puede crearlo autmáticamente, por lo que cada vez que agregues un vendedor tienes que definir el rango.

Anónimo,  11 abril, 2012 00:58  

Amigo, disculpa pero no entiendo donde debo escribir:
1) grfSeriesX
2) =INDIRECTO(reporte!$A$5)

Jorge L. Dunkelman 11 abril, 2012 07:25  

Fijate en mi comentario del 12 de diciembre en esta nota.

fernando 09 julio, 2012 12:50  

Hola!
Antes que nada, excelente blog Dunkelman, se agradece mucho!
Quisiera agregar una propuesta para evitar el uso de la celda oculta que sustituye el espacio por el guion bajo.
Si se define el nombre grfSeriesX como grfSeriesX =INDIRECTO(SUSTITUIR(reporte!$C$5;" ";"_")) en lugar de grfSeriesX =INDIRECTO(reporte!$A$5), entonces ya no es necesaria la celda A5.
Saludos!

Jorge L. Dunkelman 09 julio, 2012 14:51  

Gracias por el aporte, Fernando

Anónimo,  20 julio, 2012 01:48  

Hola Jorge, excelente y muy práctico el ejercicio.
Muchas gracias!!

Tengo una consulta: de que forma podría crear un gráfico similar que en lugar de mostrar una lista desplegable yo pueda marcar por ejemplo dos o mas vendedores??

Jorge L. Dunkelman 20 julio, 2012 09:39  

Fijate en la técnica que muestro en esta nota.

alumnos.leonardo 16 enero, 2013 06:38  

Hola Jorge, Grandioso trabajo el que haces en tu blog. Tengo una inquietud, según lo que comentas traté de ejecutarla en la versión 2007 y cuando quiero asignar el nombre que se usa para las Series en el eje X, no lo acepta, resalta el rango adecuado de celdas pero no lo acepta. En Excel 2010 va de maravilla. Pero, si abro el archivo hecho en 2010 con 2007, el efecto funciona. Qué puedo hacer para que al editarlo desde cero en la versión 2007 tenga éxito? Gracias.

Jorge L. Dunkelman 18 enero, 2013 07:45  

Hola Leonardo, no estoy al tanto de ningpun problema en la forma de definir nombres en XL2007. Te sugiero que me mandes el archivo con el problema para que pueda investigarlo.

Anónimo,  12 marzo, 2013 00:10  

Muy buen ejemplo, y mi consulta es como o con que herramienta se realizaría este mismo ejemplo en formato pdf con esa funcionalidad.

Jorge L. Dunkelman 12 marzo, 2013 07:02  

Hasta donde dan mis conocimientos no se puede hacer en PDF.

Aretradeser 21 abril, 2014 17:04  

Extraordinario blog, Dunkelman. Tus exposiciones y ejemplos, además de didácticos, son una maravilla. Lo he llevado a cabo y funciona perfectamente. Ahora bien, cuando intento aplicarlo a un ejemplo, en el que dentro de los trimestres, se subdividan en Zona1, Zona2 y Zona3 me da error; ya que, al parecer, al definir los nombres, concretamente grfSeriesX, no se llevan bien con las celdas combinadas. ¿Cómo podría soucionarlo?
Ejem.: el mismo que presentas en este post, con la única variante que cada columna de los trimestre se subdividen en tres (siempre Zona1, Zona 2 y Zona 3). Sería de gran ayuda para mi, que me pudieras ayudar. En cualquier caso, muchísimas gracias por tus estupendas aportaciones.
Un saludo,
Miguel A.

Jorge Dunkelman 22 abril, 2014 09:52  

Hola Miguel,

nada, pero nada, se lleva bien con celdas combinadas. Fijate en esta nota sobre buenas y malas prácticas en Excel.
Para poder ayudarte necesito que me mandes el archivo.

Aretradeser 01 mayo, 2014 17:17  

Jorge, por favor, confirmame que te llegó el archivo.
Gracias.
Miguel A.

Jorge Dunkelman 02 mayo, 2014 18:23  

Parece que no lo recibí. Por favor mandalo de nuevo señalando el tema en la referencia.

Unknown 06 enero, 2016 21:07  

Hola me gustaria saber como hago para que un gráfico utilice datos a partir de celdas con una lista despegable, es decir un gráfico de 4 datos q una celda de 4 opciones diferentes

Gracias

Jorge Dunkelman 11 enero, 2016 17:33  

No termino de entender la consulta. Te sugiero que me envíes un ejemplo de lo que quieres hacer (fijate en el enlace Ayuda, en la parte superior del blog).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP