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

video


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í

viernes, junio 27, 2008

Colorear área entre dos líneas en gráfico de Excel

Si queremos colorear el área comprendida entre dos líneas en un gráfico de Excel, podemos hacerlo usando algunos trucos sencillos.
La idea es construir este gráfico



Empezamos por construir un gráfico de líneas con, por ejemplo, estas dos series de datos



El gráfico resultante, después de hacer algunos arreglos cosméticos, es este



El primer paso es construir una columna auxiliar que calcula la diferencia entre los valores de las series



Nuestro próximo paso es agregar la Serie 2 al gráfico, pero con el tipo Área. Seleccionamos el rango B2:B13, lo copiamos (Ctrl+C); seleccionamos el gráfico y abrimos el menú Edición-Pegado Especial



La nueva serie es agregada como línea. Seleccionamos la serie y cambiamos el tipo de gráfico Área



Hacemos lo mismo con la serie de valores Auxiliar (C2:C13)



Finalmente, seleccionamos el primer área que hemos agregado al gráfico (con los valores de la Serie 2) y abrimos el menú de formato de serie de datos. En la pestaña Trama cambiamos las definiciones de Área y Borde a "ninguno"



El resultado es el siguiente