Agregando líneas dinámicas a gráficos de Excel

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.




Categorías: Graficos_

Technorati Tags: Excel, Graficos en Excel

Formato de Números en Gráficos de Excel

En notas anteriores hemos hablado sobre formato de números en Excel, en especial la posibilidad de crear formatos personalizados.
En varias de las últimas entradas he hablado sobre gráficos en Excel. Ahora podemos combinar ambos temas y hablar 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.

Categorías: Graficos_

Technorati Tags: Excel, Graficos en Excel

Gráficos Excel – Líneas o XY (Dispersión).

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


Categorías: Graficos_

Technorati Tags: Excel, Graficos en Excel