lunes, abril 28, 2008

Gráfico interactivo a partir de un valor de celda

Supongamos que tenemos este cuadro de ventas por año y por departamento de una empresa imaginaria



El gráfico al pie de la tabla muestra las ventas por año del Departamento 1.

Nuestro objetivo es convertir este gráfico en un gráfico interactivo, de manera que cuando seleccionamos una celda del rango A3:A12, el gráfico muestre las ventas del departamento seleccionado.

Para lograr esto usaremos las siguientes técnicas:

1 - Uso de nombres en la fórmula SERIES del gráfico, como mostramos en la nota sobre gráficos Excel dinámicos;

2 - Programación de eventos de la hoja que contiene la tabla de datos y el gráfico. Quien no esté familiarizado con la programación de eventos en Excel puede leer mis notas sobre el tema en mi blog sobre Excel.

Empezamos por definir los nombres

Departamento =DESREF(Hoja1!$A3;0;0)

Datos =DESREF(Hoja1!$A3;0;1;1;4)


Al definir los nombres, la celda activa tiene que ser la celda A3 (en nuestro ejemplo, por supuesto). Además debemos prestar atención al hecho que la dirección de la celda es semi-variable (absoluta para la columna y variable para la fila).



Una vez definidos los nombres, reemplazamos el rango del título y el rango de los datos con los nombre. Antes de reemplazar los rango, la fórmula SERIES se ve así



Para reemplazar los rangos, seleccionamos la series de datos en el gráfico y abrimos el diálogo de Datos de origen



En la casilla Nombre, dejamos la referencia Hoja1! Y reemplazamos A3 por el nombre "Departamento". En la casilla Valores hacemos lo mismo con "Datos"



Ahora, si seleccionamos un departamento en el rango A3:A12 y apretamos F9 para forzar a Excel a recalcular la hoja, veremos que el gráfico muestra los datos del departamento elegido.
Nuestro gráfico, si bien es interactivo, es "semiautomático" ya que debemos apretar F9 para que se produzcan los cambios.
Para automatizar completamente nuestro gráfico programamos un evento para la Hoja1 (en nuestro ejemplo).
En la pestaña de la hoja apretamos el botón derecho del mouse y elegimos la opción "ver código"



Esto nos lleva al módulo de la hoja en el editor de Visual Basic. Aquí ponemos este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Union(Range("A3:A12"), Target).Address = Range("A3:A12").Address _
Then ActiveSheet.Calculate
End Sub

Este código programa el evento SelectionChange, es decir, cada vez que seleccionamos una celda, si ésta está dentro del rango A3:A12, la hoja será recalculada.

Con esto nuestro gráfico es totalmente automático y cada vez que seleccionemos un departamento, los datos relevantes aparecerán representados en el gráfico.

El archivo con el ejemplo se puede descargar aquí.

8 comentarios:

Anónimo dijo...

Recién hoy he descubierto el blog y me parece excelente. Voy a tener que ponerme a tiro.
Quiero hacer la siguiente consulta: ¿Es posible en Excel modificar los gráficos, o sea, en lugar de aparecer una barra o una piramide, que aparezca la imagen que yo quiera?

Jorge L. Dunkelman dijo...

Hola, si se puede hacer usando el menú de formato de series de datos, en la pestaña Tramas, efecto de relleno, Imagen.
También puedes usar Autoformas, como he mostrado en algunas notas, como ésta por ejemplo.

RODRIGO dijo...

Estimado, primero que nada lo felicito por este gran trabajo, este sitio es magnífico. En segundo lugar, tengo grandes dudas sobre un modelo de graficos, que es analogo a un velocímetro (para tableros de comando) a saber, son un semicirculo y el eje se mueve en grados segun el valor, o bien lo negativo o positivo del mismo, podria indicarme como es el proceso para componer uno de ellos? Gracias por todo, Atte: Bernasconi

Anónimo dijo...

Muchas Gracias

Anónimo dijo...

En el excel 2007 no puedo visualizar el grafico,he realizado todos los pasos y no lo he logrado, podrian ayudarme al respecto
gracias

Jorge L. Dunkelman dijo...

Efectivamente, este modelo no funciona en XL2007. En cuanto tenga una sólución la publicaré

Anónimo dijo...

buenas tardes

en primer lugar jorge dejame felicitarte por tan buenos aportes que nos compartes, y que aparte me han servido bastante para mi trabajo, tengo un problema con este grafico y es que no se me actualiza hago paso apaso lo que nombras, y nada solo me coloca las mismas columnas creo que puede ser en el uso de la funcion series, puedes ser un poco mas explicito de como aplicar esta,

Gracias

Jorge L. Dunkelman dijo...

¿Qué versión de Excel estás usando?