Gráficos en Excel – Crear gráficos tipo velocímetro - 1ra. nota

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.

Graficos Excel - Agregar lineas dinamicas

En mi nota sobre formato de números en un gráfico de Excel, mostraba como agregar líneas horizontales para señalar, por ejemplo, límites de valores.
El problema con el método propuesto es que las líneas que creamos no comienzan en el eje de las Y, lo cual hace que la intención de estas líneas no sea clara





La solución que dábamos era quitar la marca de la opción "Eje de valores (Y) cruza entre categorías" en el menú de formato del eje de las X.
Esto crea un nuevo problema, ya que el rótulo del primer punto de la serie aparece debajo del punto de intersección de los ejes.

En esta nota mostraré un método más avanzado, para agregar líneas a un gráfico. Nuestro objetivo es que estas líneas sean dinámicas. Primero describiremos la técnica y luego daremos algunas explicaciones.

Supongamos la tabla de datos del ejemplo anterior



Como en ese caso, queremos crear una línea que marque el límite superior aceptable (valor = 6,000) y una que marque el límite inferior aceptable (valor = -6,000). Los pasos a dar son los siguientes:

1 - Creamos dos tablas auxiliares, una para el límite superior y otra para el inferior




2 - Seleccionamos el rango G1:H3 y lo copiamos (Ctrl+C).
3 – Seleccionamos el gráfico con un clic, y abrimos el menú Edición—Pegado Especial. En el diálogo que se abre, seleccionamos las opciones "Nueva Serie" y "Columnas"




4 – Seleccionamos la nueva serie de datos, abrimos el menú de gráficos y en el menú Tipo de Gráficos seleccionamos XY (Dispersión) con datos conectados por líneas, sin marcadores de datos



5 – Excel agrega dos eje secundarios, a la izquierda y en la parte superior del gráfico.



Seleccionamos uno de los ejes, y definimos la escala con un máximo de 100, un mínimo de 0, y en la pestaña "Tramas" elegimos la opción "ninguno" para todas las marcas de graduación y para "Líneas" (con lo cual ocultamos el eje).

6 – Seleccionamos el otro eje definimos la escala y los formatos como en el caso anterior. También podemos apretamos F4 para copiar los formatos.



El resultado es el siguiente



Para introducir el límite inferior seleccionamos el rango K1:L3 y volvemos sobres lo indicado en el paso 3. Luego formamos las líneas con el color y el grosor adecuados



Ahora explicaremos el método, es decir, cómo calculamos los valores 75.00 y 25.00.
Hacemos reaparecer por un momento el eje secundario de la Y




Aquí podemos ver que el valor 6,000 en el eje de la izquierda coincide con el valor 75 del eje de la derecha. Y lo mismo respecto a -6,000 y 25.
En ambos ejes los valores están distribuidos a intervalos regulares. Por lo tanto tenemos que calcular en qué posición está ubicado el valor del límite en el eje principal teniendo en cuenta que el valor máximo de la escala (en nuestro caso 12,000) ocupa la posición 0 (cero), el segundo valor (10,000) la posición 1 y así sucesivamente.
En nuestro caso 6,000 ocupa la posición 3 (-6,000 ocupa la posición 9). Para calcular la posición equivalente en el eje secundario dividimos 100 (el valor máximo del eje) por la cantidad de puntos en el eje (sin tomar en cuenta el 0), des decir 100/12 = 8.33
Este es el intervalo entre los puntos del eje. Para calcular el valor multiplicamos este factor por el número de posición y lo restamos de 100; en nuestro caso 100 – (8.33*3) = 75
Construimos una tabla auxiliar




En el rango F3:F5 ponemos las definiciones de la escala del eje.
En la celda F6 calculamos el número de posiciones en el eje con la fórmula =SUMA(ABS(F3),ABS(F4))/F5
En la celda F7 calculamos el valor del intervalo entre cada posición con la fórmula =100/F6
Y en la celda F8 calculamos la posición (número de orden) del punto buscado con la fórmula =(F3-F2)/F5

De esta manera nuestro gráfico es totalmente dinámico. Lo único a tener en cuenta es que si cambiamos la escala del eje principal, debemos cambiar los valores correspondientes en la tabla auxiliar.


Un método más sencillo, usando barras de error, se puede ver en la nota sobre cómo agregar líneas dinámicas en gráficos Excel con barras de error


Esta nota fue originalmente publicada en JLD Excel

Graficos Excel - Formato de Numeros

En mi blog sobre Excel formato de números en Excel, en especial la posibilidad de crear formatos personalizados.
Como este blog trata sobre gráficos y presentación de datos, combinaremos ambos temas y hablaremos de formato de números en gráficos de Excel.

Supongamos esta tabla que nos muestra el saldo de una cuenta imaginaria al final de cada mes

Creamos un gráfico de tipo Líneas



Los valores del eje de las Y son numéricos, y por lo tanto podemos aplicar formato de números, tal como lo hacemos con los rangos de los valores numéricos en las hojas de cálculo.
Por ejemplo, si queremos resaltar los valores negativos, haciéndolos aparecer en rojo, podemos aplicar el siguiente formato: #,##0;[Rojo]-#,##0

Los pasos a seguir son sencillos:

1 – Apuntando al eje de las Y abrimos el menú de formato del eje



2 – En la pestaña "Número" elegimos la opción Personalizado y seleccionamos el formato deseado



Podemos enriquecer un poco más la representación de los valores usando la opción de formato condicional en el patrón de números. Excel permite un formato condicional básico en el formato personalizado de acuerdo al siguiente patrón:

[condición]número positivo;[condición]número negativo;cero

Por ejemplo, si queremos que los números negativos aparezcan en rojo, los números positivos hasta 5,000 aparezcan en negro y los números mayores de 5,000 aparezcan en verde, aplicamos este formato personalizado:

[Verde][>5000] #,##0; [Rojo][<0]-#,##0>

Ahora supongamos que el límite inferior deseado del saldo es -6,000 y el máximo 6,000. Podemos agregar dos líneas que marquen estos límites

Para esto agregamos una columna de valores para cada límite en nuestra tabla para crear dos nuevas series



Seleccionamos el rango C2:D7, aplicamos Ctrl+C, seleccionamos el gráfico y en el menú de pegado especial marcamos "nueva serie" y "columnas", cambiamos un poco el formato estándar de Excel y obtenemos

El toque final que podemos dar es colorear el área definida por las líneas de límite, para enfatizar los puntos que caen en la "zona permitida".

Para hacer esto agregamos dos nuevas series, usando los datos ya existentes en el rango C2:D7 y les cambiamos el tipo de gráfico a "columnas apiladas".

Los pasos son los siguientes:

1 - Seleccionamos el rango C2:D7 y pulsamos Ctrl+C

2 - Seleccionamos el gráfico y en el menú Edición activamos Pegado Especial. En el diálogo marcamos "nueva series" y "columnas".

3 - Las nuevas series aparecen como líneas que se superponen a las ya existentes. Para seleccionarlas apuntamos a los marcadores, pulsamos el botón derecho y en el menu que se abre activamos "tipo de gráfico". Elegimos "columnas apiladas". Hacemos lo mismo con la segunda serie y obtenemos este gráfico

4 - Seleccionamos una de las series y abrimos el menú de formato de series de datos. En la pestaña "opciones" fijamos el ancho del rango en 0 (cero),



y en la pestaña "Tramas" seleccionamos la opción "ninguna" para Bordes y cambiamos el color del fondo de manera que coincida con el de la otra serie



Elegimos la otra serie y en el menú de formato de datos de serie, seleccionamos la opción "ninguna" para Bordes.



5 - Ahora tenemos que corregir la definición del eje de la X, para evitar el espacio en blanco entre el 0 y el primer valor de las categorías. Para esto, señalamos el eje de las X y on el botón derecho del mouse abrimos el menú de formato de ejes. En la pestaña "Escala" quitamos la marca de la opción "Eje de valores (Y) cruza entre categorías"



Finalmente obtenemos este gráfico.



Los valores de los límites, y el área comprendida entre ellos, son dinámicos. A medida que cambiamos los valores de los límites, las líneas y el área se irán adaptando.

Excel - Graficos tipo Lineas o Dispersion (XY)

Entre los tipos de gráficos de Excel se encuentran el tipo Líneas y el XY (Dispersión).




Ambos tipos, que parecen similares y pueden producir gráficos idénticos en apariencia son, sin embargo, muy distintos.

La diferencia entre ambos tipos reside en que en el gráfico de tipo Líneas los valores del eje de las X (las categorías) son consideradas como cadenas de texto, mientras que en el gráfico de tipo XY, los valores del eje de las X son considerados números.
Existe una excepción a esta regla, que consideraremos más adelante.

Resaltaremos las diferencias entre estos tipos de gráficos con un ejemplo.

Supongamos esta tabla



Construimos un gráfico de tipo Líneas, seleccionando el rango A2:B7, borramos la leyenda y el fondo



Si prestan atención verán que los valores en el eje de las X están ordenados por orden de aparición en la tabla y que las distancias entre ellos son constantes y equivalentes.
Es decir, Excel trata a estos datos como texto, a pesar de ser números.

Ahora volvamos a usar nuestra tabla, pero esta vez construimos un gráfico de tipo XY (Dispersión) con puntos de datos conectados por líneas



Como pueden ver, obtenemos un gráfico completamente distinto. En este tipo de gráficos, Excel considera los valores de X como números y los ordena en forma creciente a partir del origen. Los valores del eje de las X son números calculados por Excel de acuerdo a los valores de la tabla y no rótulos, como en el caso anterior.

En resumen, cuando los valores del eje de las X sean categorías (cadenas de texto, aún cuando tengan forma de número, como los números de catálogo por ejemplo), debemos usar el gráfico de tipo Líneas. Cuando los valores del eje de las X son números, usaremos el gráfico de tipo XY.

Como decíamos al principio, hay una excepción a esta regla en lo que hace a los gráficos de tipo Líneas. Cuando Excel reconoce los valores de las X como fechas, aplica al gráfico de Líneas un tipo especial de eje X, el eje de tiempo, un eje numérico que representa fechas.

Como ya hemos explicado, Excel representa las medidas de tiempo como una serie de números, donde la parte entera representa los días y la fracción decimal las horas, minutos y segundos de ese día.
Cambiemos los valores de nuestra tabla por números que Excel pueda reconocer como fechas:



Los valores de X son fechas con formato [$-C0A]d-mmm;@

El gráfico de Líneas se convierte ahora a:



Como pueden ver, Excel está tratando a los valores del eje de las X como fechas, es decir, como valores numéricos. Para estudiar esto un poco más de cerca, abrimos el menú de formato del eje de las X (categorías)



En la pestaña Escala, podemos ver que Excel llama ahora al eje "eje del tiempo". Los valores "mínimo" y "máximo" aparecen con formato de fecha, y en las definiciones de unidad principal y secundaria tenemos una ventanilla que nos permite elegir entre "días", "meses" y "años".

De esto se desprende una limitación del eje de tiempo: no podemos representar intervalos menores a un día completo.
Por ejemplo, si tenemos esta tabla, donde los valores de X son intervalos de 6 horas dentro de un mismo día(los valores de X tienen formato [$-C0A]d-mmm hh:mm;@)



Todos los puntos aparecerán sobrepuestos sobre el mismo punto de la X (la misma fecha).



En cambio, la misma tabla representada en un gráfico de tipo XY, dara




Fuente para esta nota: Jon Peltier en Tech Trax


Esta nota fue publicada originalmente en JLD Excel

Excel - Graficos con listas desplegables

En una entrada en JLD Excel mostraba como usar controles en hojas de cálculo Excel.

Uno de los usos de estos controles es generar listas desplegables (combo boxes). Cuando de hojas de cálculo se trata, podemos usar la funcionalidad Validación de Datos para generar estas listas desplegables. Pero cómo hacemos para poner una lista desplegable en un gráfico de Excel?
La respuesta: con el control cuadro combinado de la barra de herramientas "Formularios"

Supongamos que tenemos esta lista de ventas por mes y por línea de productos



Nuestro objetivo es generar un gráfico como este:



Cada vez que elegimos un mes de la lista desplegable, los datos del gráfico se adaptan a nuestra elección.

Los pasos a seguir son los siguientes:

1 – Preparamos el rango A1:D2 para que contenga los datos del gráfico




en el rango B2:D2 escribimos esta fórmula

=INDICE($B$7:$D$18,$A$3,COINCIDIR(B$1,$B$6:$D$6,0))

y en la celda A2 escribimos esta: =INDICE(A7:A18,A3)

Por ahora las fórmulas dan un resultado #¡VALOR!, cosa que será corregida escribiendo en la celda A3 el número 1 (o cualquier número entre 1 y 12).

2 – Creamos un gráfico de columnas con el rango A1:D2

borramos la leyenda y le damos un color distinto a cada serie (línea de productos).

3 – Activamos la barra de herramientas "Formularios", y cliqueamos el control "cuadro combinado". Lo ubicamos dentro del gráfico y abrimos el menú de formato del control



4 – En la ventanilla "rango de entrada" señalamos el rango que contiene los meses en la tabla de datos (A7:A18); en la ventanilla "vincular con la celda" señalamos A3; en "líneas de unión verticales" escribimos 12. También podemos marcas "sombreado 3D" para obtener este efecto en el control.

Al apretar "aceptar" las fórmulas darán #¡VALOR!, ya que el valor de la celda vinculada (A3) pasa a ser 0. Todo lo que hay que hacer es elegir un mes en la lista desplegable y veremos los resultados en el gráfico.

Otro truco es cambiar el color de fuente de la celda A3 a blanco, para "hacerla desparecer". Otra variante, más elegante tal vez, es ubicar este argumento en una celda que se encuentra "debajo" del gráfico, por ejemplo H10.