martes, septiembre 12, 2006

Gráficos en Excel – Como crear un gráfico tipo velocímetro

Uno de mis lectores me pregunta como se pueden diseñar gráficos en Excel del tipo "velocímetros". Se trata de esos gráficos que se ven como taquígrafos, por ejemplo como este, tomado del sitio de Jon Peltier





La galería de gráficos de Excel no incluye este tipo de diagramas, pero su realización es posible usando algunos trucos.

En esta nota daremos una explicación de las técnicas para lograr este tipo de gráficos en Excel, en sus formas más básicas. Gráficos con agujas, como el de la imagen, requieren técnicas más elaboradas y ciertos conocimientos de trigonometría. Estos gráficos serán tratados en una futura nota.

Las fuentes de esta nota son el sitio ya mencionado de
Jon Peltier y partes del libro de John Walkenbach "Excel Charts".

La forma más sencilla es un gráfico que muestra el porcentaje de lo cumplido en relación al total planeado. Supongamos esta sencilla tabla



Nuestro objetivo es generar este gráfico



Este gráfico es, de hecho, uno de tipo "circular" (pie, en inglés), de tres datos (porciones), donde hemos ocultado la tercer porción que representa la mitad de el gráfico.
Para lograr este efecto, usamos una tabla auxiliar donde dividimos el dato "cumplido" por la mitad y calculamos la mitad del restante. La mitad restante es, necesariamente, la mitad que ocultaremos en el gráfico.




Para lograr todos lo efectos los pasos son los siguientes:

1 – definir de los datos

Las fórmulas son:


en la celda B7: =B1/2
en la celda B8: =B2/2-B7
en la celda B9: =B2/2

2 – girar el gráfico de acuerdo a la necesidad (en este caso 270 grados)




3 - seleccionar el punto 3 de los datos y marcar el valor del área y del borde como "ninguno"



4 – El rótulo que muestra el porcentaje de lo cumplido es un cuadro de texto que hemos ligado a la celda B4



para hacer esto, seleccionamos el cuadro con un clic y en la barra de fórmulas cramos el vínculo a la celda.

Nota: este gráfico presupone que los valores sean siempre positivos.

Un gráfico más elaborado se puede lograr usando la variante "anillos". En este ejemplo, clasificamos los resultamos de un examen, de acuerdo a la nota obtenida, en "reprobado", "regular", "bueno" y "excelente". El resultado de cada examen lo exhibimos en un gráfico como este:




Para lograrlo usamos una técnica similar a la anterior, pero con el gráficos "anillos".
El primer paso consiste en elaborar una tabla auxiliar que nos permita representar los datos:




Las fórmulas son las siguientes



En la columna "Auxiliar" usamos la misma técnica que en el gráfico anterior, dividiendo los valores por la mitad.
En la columna "Valor Límite" establecemos los límites de las calificaciones, también en este caso dividido por la mitad. En nuestro ejemplo, el límite superior de "Reprobado" es 40 puntos, por lo tanto en la celda C4 ponemos un valor de 20, "regular" es hasta 70 puntos, es decir 30 puntos más a partir de "reprobado". Por lo tanto anotamos 15 (la mitad de 30) en la celda C5. Y así sucesivamente.
Con estos datos, más los rótulos de la columna B podemos construir nuestro gráfico.

Sin seleccionar ninguna celda activamos el asistente para gráficos y definimos las series de esta manera:

Serie 1: =Anillos!$A$4:$A$6
Serie 2: =Anillos!$C$4:$C$8, para esta serie definimos también los rótulos de las categorías, =Anillos!$B$4:$B$7




Abrimos el diálogo de Formato de Series de Datos y giramos el gráfico 270 grados



Seleccionamos el punto 5 de la serie 2 (valor límite) y en el diálogo de Formato de punto de datos ponemos los valores de "borde" y "área" en "ninguno"



Hacemos lo mismo con el tercer punto de la serie 1 (Auxiliar)

Cambiamos el área de los puntos de la serie 1 al color blanco y a los puntos de las serie 2 les damos colores apropiados.

Todo lo que nos queda por hacer es agregar dos cuadros de textos, uno ligado a la celda A1, que será el título del gráfico y el otro ligado a la celda B1, que mostrará el puntaje obtenido en el examen.
También la damos un fondo al área del gráfico y voila!

En la próxima nota veremos algunos ejemplos más elaborados y la construcción de gráficos "velocímetro" con agujas.




Categorías: Graficos_

Technorati Tags: ,

sábado, septiembre 09, 2006

Gráficos Excel con Autoformas.

Excel provee una extensa galería de gráficos, que cubren casi cualquier necesidad de presentación de datos. Pero podemos ampliar las posibilidades combinando otros elementos en los gráficos, como imágenes.

Una posibilidad interesante es utilizar autoformas para representar los puntos de los datos. Autoformas es una colección de elementos gráficos a los cuales podemos acceder desde la barra de herramientas "Dibujo"





Supongamos esta tabla que presenta la situación de un proyecto imaginario



La celda C16 muestra el estado de avance del proyecto. Podemos representar la situación del proyecto usando un gráfico de barras con un único punto de datos.

Para esto seleccionamos la celda C16 y activamos el icono de asistente para gráficos. Elegimos la opción "columnas" y apretamos el botón "siguiente. En la ventanilla "rango de datos" reemplazamos la selección automática de Excel haciendo un clic a la celda C16, de manera que el rango de datos contenga sólo la celda C16.




En los siguientes pasos eliminamos el eje de categorías y borramos la leyenda. Luego seleccionamos el eje de valores y ponemos 1 como valor máximo del eje.

Después de estas operaciones "cosméticas" obtenemos este gráfico




Convengamos que no es el gráfico más "sexy" que han visto hasta hoy. Vamos a mejorarlo con autoformas.

Creamos el mismo gráfico, pero en lugar de usar "columnas" usamos "líneas". El resultado es




El segundo paso es crear la autoforma para representar los datos. Lo que queremos es una flecha que apunte al valor correspondiente en el eje de valores. Abrimos el menú de autoformas y elegimos de la flechas de bloque la que apunta a la izquierda.



Dimensionamos la flecha de manera que coincida con el tamaño del gráfico. El truco comienza aquí:

1 – Copiamos la flecha (con la flecha seleccionada apretamos Ctrl+C)

2 – Seleccionamos la serie. Este punto es crítico. Para estar seguros que seleccionamos el punto de la serie, tenemos que ver en la barra de fórmulas la función SERIES con los argumentos del punto




La selección se puede hacer con el mouse, o si queremos más precisión, hacemos clic en el área del gráfico y luego pasamos de un objeto al otro pulsando alguna de las flechas del teclado.

3 – Una vez seleccionado el punto de la serie, pegamos la autoforma (Ctrl+V o edición-pegado).

4 - Damos los "últimos toques" al formato del rótulo de datos (tamaño de la fuente, alineación) y este es el resultado




Ahora que conocemos la técnica de usar autoformas con gráficos de Excel, podemos mejorar aún más nuestro gráfico. Lo que haremos es usar una flecha con dos puntas en un gráfico con dos ejes de valores.

Los pasos son similares al gráfico anterior, pero aquí el truco consiste en definir dos series de valores para la misma celda C16, ya que no podemos tener dos ejes con sólo una serie.
En lugar de "lineas" usamos "líneas en dos ejes" de la pestaña "Personalizados". En el diálogo "datos de origen", elegimos la pestaña "Serie" y agregamos una segunda serie apuntando a la celda C16




Ambos ejes deben ser idénticos, por lo tanto hay que asegurarse de definir la escala de ambos de manera que el valor máximo sea 1 y que las fuentes sean idénticas.

Seleccionamos la flecha con dos puntas en el menú de autoformas y lo adaptamos a nuestras necesidades. Copiamos la flecha y elegimos el punto de una de las series (nuevamente, verificando que la función SERIES aparece en la barra de las fórmulas). Pegamos la flecha y este es el resultado




Las flechas se moverán de acuerdo a los cambios en los datos de la tabla.

Los invito a bajar el archivo con los ejemplos de uso de autoformas en gráficos Excel





Categorías: Graficos_

Technorati Tags: ,

viernes, septiembre 08, 2006

Paleta de colores de Excel – Como cambiar colores

Para dar color a los distintos objetos Excel viene provisto con una paleta de colores. De acuerdo al contexto de nuestra actividad en la hoja de cálculos podemos ver una paleta de 40 colores o de 56 colores.
Por ejemplo, si activamos el icono "color de relleno" de la barra de herramientas "formato"



veremos la paleta con 40 colores.
Pero si activamos el menú "Formato de celda"--Tramas" veremos la paleta con 56 colores



Como podemos ver, los colores son los mismos.

Excel no está limitado a estos 56 colores. Existe la posibilidad de definir distintas paletas, de acuerdo a los gustos o necesidades el usuario.
Estas paletas definidas por el usuario son guardadas con el cuaderno donde las creamos. Si queremos usar esta nueva paleta en otros cuadernos, podemos guardarla en una plantilla (template).

Los pasos para crear una nueva paleta de colores en Excel son:

1 – Activamos el menú "Herramientas---Opciones" y abrimos la pestaña "color"



Como vemos, esta paleta cuenta con 56 colores, 40 "estándar", 8 para relleno de gráficos y 8 para líneas de gráficos.

2 – Seleccionamos el cuadrado del color que queremos reemplazar y apretamos el botón "Modificar Color". El diálogo que se abre posee dos pestañas: "estándar" y "personalizado"




3 – Digamos que queremos cambiar el tono del azul oscuro. Seleccionamos el color en la paleta y abrimos el diálogo "modificar color" en la pestaña "personalizado"




Moviendo la flecha en la barra angosta de la derecha vamos cambiando el color a más oscuro o más claro. En el ángulo inferior derecho del diálogo podemos la comparación del color actual (espantosa traducción del inglés en mi opinión; tendría que decir "presente") con el nuevo que vamos generando.
Para mover la flecha podemos usar el mouse, o las flechas del teclado.
Al apretar "Aceptar" el color será reemplazado.

De esta manera podemos cambiar cada uno de los colores. Para volver a los colores originales basta con apretar el botón "Reestablecer".




Categorías: Graficos_, Varios_

Technorati Tags: ,