domingo, febrero 08, 2009

Tipo de gráfico intercambiable en Excel

Como bien sabemos es muy fácil crear gráficos para representar datos en Excel. Crear buenos gráficos o gráficos relevantes es una tarea mucho más ardua, pero ese tema lo dejaremos para alguna otra oportunidad. A veces dudamos que tipo de gráfico usar y un lector me comenta que sería bueno tener la posibilidad de intercambiar el tipo de gráfico en forma dinámica. La idea es generar una lista desplegable donde aparezcan distintos tipo de gráficos que podamos aplicar a los datos. Al elegir uno de los tipos, el gráfico en la hoja cambia automáticamente.

Mi primer impulso fue resolver la cuestión con macros. Pero luego decidí que sería más interesante buscar alguna forma de hacerlo sin macros. Y dicho esto, manos a la obra.
Empezamos por construir una tabla de datos la cual queremos representar con un gráfico




Lo que buscamos crear es lo siguiente




Creamos el primer gráfico, de tipo Columnas Agrupadas y lo pegamos en una hoja distinta de la que nos servirá más tarde para mostrar los datos con el gráfico. En nuestro caso ponemos la tabla de datos en la Hoja1 y luego de crear el gráfico lo cortamos y pegamos en la Hoja2.



Como pueden observar, nos aseguramos que el área del gráfico coincida exactamente con un rango de celdas, en nuestro caso A1:F16. Para lograr que los bordes del área se “peguen” a las líneas de división movemos y cambiamos el tamaño del área manteniendo apretada la tecla Alt.
Una vez hecho esto, copiamos el gráfico y lo pegamos en otro rango de la Hoja2, por ejemplo A18:F33

Nuevamente, nos aseguramos que el área coincida con el rango de celdas. Seguidamente cambiamos el tipo de gráfico de Columnas a Líneas



Repetimos el proceso para crear un tercer gráfico de tipo Área.
Ahora creamos un nombre para cada rango sobre el cual se encuentran los gráficos

Columnas = Hoja2!$A$1:$F$16

Líneas = Hoja2!$A$18:$F$33

Area = Hoja2!$A$35:$F$50

En la misma Hoja2 creamos una lista en el rango I1:I3 poniendo en cada celda respectivamente: Columnas, Líneas, Area. Esta lista nos servirá más adelante para crear la lista desplegable.

Ahora volvemos a la Hoja 1. Elegimos una celda vacía cualquiera, por ejemplo H1 y la copiamos (Ctrl+C). Seleccionamos la celda D1, que es el vértice superior izquierdo de la zona donde queremos que aparezca el gráfico intercambiable, y manteniendo apretada la tecla Mayúsculas abrimos el menú Edición-Pegar vínculos de imagen



El resultado es una imagen vacía (pueden verse los señaladores del área de la imagen) y en la barra de fórmulas vemos la referencia a la celda que hemos copiado ($H$1)



Reemplazamos $H$1 en la barra de las fórmulas por el nombre Columnas que hemos creado sobre el rango que contiene ese tipo de gráfico en la Hoja2



Vemos que el gráfico de columnas aparece en la Hoja1. Si cambiamos la referencia en la barra de columnas a Lineas, veremos aparecer el gráfico tipo líneas.

Ahora necesitamos un método para hacer esto eligiendo el tipo de gráfico de una lista desplegable. Esta lista desplegable la creamos usando el control Cuadro Combinado de la barra de formularios (no de los controles ActiveX)

Creaos el control y lo ubicamos sobre la celda A12. El rango de entrada es el que hemos definido en el rango I1:I3 de la Hoja2, y lo vinculamos con la celda A12 (de manera que el control oculte el valor de la celda).


Al elegir Columnas, la celda A12 recibe el valor 1 (la posición del valor en la lista), al elegir Lineas A12 recibe el valor 2 y al elegir Área el valor 3.

Creamos el nombre “tipo3” que contiene una fórmula con la función ELEGIR
tipo3 = ELEGIR(Hoja1!$A$12,columnas,lineas,area)

Seleccionamos la celda D1 y en la barra de fórmulas ponemos esta referencia: =tipo3


A partir de este momento el tipo de gráfico cambiará de acuerdo a la selección que hagamos en la lista desplegable.

El archivo con el ejemplo se puede descargar aquí

9 comentarios:

Edwin Reyes dijo...

Gracias Jorge....

Realmente esta interesante la forma dinamica de presentar los datos, sin duda alguna sera de mucha ayuda para muchos de nosotros.

Eres GRANDE.....

Saludos

Jean dijo...

Es muy interesante, sin embargo no he podido realizarlo en mi PC porque tengo Excel 2002 y la barra Formularios muestra el botón Cuadro combinado inhabilitado, he guardado el archivo como Excel 95 pero tampoco se deshabilita el botón.
Alguna sugerencia?
Saludos
Jean

Jorge Dunkelman dijo...

Lamentablemente no he guardado una copia de XL2002 y no puedo investigar tu problema. Pero puedes intentar usar el cuadro combinado de la barra de Cuadro de Controles (ActiveX).

Anónimo dijo...

Esta muy interesante el proceso de las graficas pero tengo una duda cuando escribes =Hoja2!$A$1:$F$16 Excel me marca error #!VALOR! PORQUE??? hay algun formato que darle a la celda?

Jorge Dunkelman dijo...

Aparentemente una de las celdas en ese rango de tu cuaderno contiene un error.

Anónimo dijo...

Hola:
he llegado hasta el punto donde creo el nombre tipo3 con la formula ELEGIR y a la hora de introducirlo en una celda para vincularlo a la lista desplegable me sale valor 0 cuando elijo columna, y en el caso de lineas y área sale el error #¡VALOR!. Por qué?

Jorge Dunkelman dijo...

La fórmula con la función ELEGIR va en un nombre definido, no en una celda.

Espinoza dijo...

Holaa...
gracias por compartir tus conocimientos, acabo de realizar este ejercicio en Excel 2007. todo perpecto.

saludos

Unknown dijo...

Hola Jorge
Para felicitarte por compartir con todos tus conocimientos, no hago mucho uso de graficas, pero me encanto la forma de como aplicas tus conceptos para hacer mas facil las tareas, realmente lo aplique me costo un poco (donde dices crea un nombre "tipo3" pero al final logre interpretar que era pegar la funcion al mismo nombre (similar aun DESREF)

Lo aplique en 2007 y quedo listo

Saludos desde Honduras