Etiquetas personalizadas en gráficos de Excel - segunda nota

En la nota anterior vimos cómo agregar etiquetas definidas en un rango de celdas. En esta nota veremos un ejemplo más avanzado de lo que podemos hacer con esta técnica. Supongamos esta tabla que analiza las ventas por mes en comparación a un objetivo mensual

Podemos representar todos estos datos en un gráfico combinado. Pero inmediatamente vemos que se nos crea un problema de escala. Si bien podemos poner las ventas en el eje principal de la Y y la diferencia en un eje secundario, no tenemos solución para la diferencia en porcentaje (no podemos agregar un tercer eje).

Mi propuesta en este caso es representar las ventas en un gráfico de columnas y poner los datos de la diferencia, en valor absoluto y en porcentaje, en una etiqueta

El primer paso para crear este gráfico es crear un rango con las etiquetas

Usamos la fórmula

=TEXTO(C5;"#.##0")&CARACTER(10)&" ("&TEXTO(D5;"0,0%")&")"

La función TEXTO es indispensable para obtener el texto combinado con el formato adecuado para los números.

Hemos quitado el eje de las Y, y en su lugar hemos agregado las etiquetas estándar de Excel con los valores de cada uno de los puntos de las serie. Para poder agregar las etiquetas personalizadas necesitamos otra serie.

La "Series2" es de hecho la misma serie de las ventas (el rango B5:B10). Para volver "invisibles" los puntos de la nueva serie definimos la serie en un eje secundario y fijamos la propiedad de relleno a "sin relleno" y la de bordes a "sin línea". Esto hace que no veamos las columnas (los puntos de las series) pero que podamos agregar etiquetas.

Para lograr esto usamos la macro que hemos definido (ver la nota anterior)

Finalmente, agregamos un cuadro de texto y lo ligamos a la celda que contiene el valor "objetivo" (la celda C2 en nuestro ejemplo). Como con las etiquetas, creamos el cuadro y lo ligamos a la celda creando una referencia (ponemos el signo "=" en la barra de las fórmulas y hacemos clic a la celda).

El archivo con los ejemplos y las macros se puede descargar aquí.

Etiquetas personalizadas en gráficos de Excel

Excel permite agregar etiquetas en las series de los gráficos, pero no tiene un método para establecer un rango de celdas que contenga las etiquetas. Veamos este ejemplo

 La tabla en el rango A2:C5 nos permite calificar el resultado de ventas de cada mes en el rango C9:C14. Esto lo hacemos en forma dinámica usando la fórmula

 =INDICE($A$2:$A$5;COINCIDIR(B9;$C$2:$C$5))

en el rango C9:C14.

El gráfico nos muestra las ventas de cada mes.
Queremos poner etiquetas que muestren la calificación de cada mes. Excel nos permite agregar etiquetas con los valores (como en la imagen), con la categoría, con el nombre de la serie o con una combinación de las tres.

Pero nosotros queremos poner como etiquetas las calificaciones, es decir, los valores del rango C9:C14.

La forma más inmediata de hacerlo es manualmente. El proceso es el siguiente

1 – Agregamos etiquetas a la serie

2 – Hacemos un clic para seleccionar todas las etiquetas.
3 – Un segundo clic selecciona una única etiqueta.
4 – Con la etiqueta seleccionada ponemos el signo "=" en la barra de la fórmulas y creamos una referencia a la celda correspondiente en el rango de las etiquetas (en nuestro caso C9 para el primer punto de la serie).

Este video muestro el proceso

 

Este método es ineficiente cuando tenemos una serie con muchos datos. El proceso pasa a ser trabajoso y la posibilidad de error crece con el número de operaciones que debemos realizar. En esos casos una macro es la solución más eficiente.





Como se ve, empezamos por seleccionar el gráfico. Luego seleccionamos el rango de las etiquetas personalizadas y apretamos OK.

En un módulo corriente del editor de Vba (preferentemente en el cuaderno Personal) copiamos este código

  Sub label_data()

    Dim iD As Long
    Dim rCustomLableRange As Range

    If ActiveChart Is Nothing Then
        MsgBox "Debe seleccionar un grafico ", 0
        Exit Sub
    End If

    On Error GoTo errNoData
    Set rCustomLableRange = Application.InputBox(prompt:="Seleccione el rango de etiquetas", _
                                                    Title:="Etiquetas personalizadas", _
                                                    Type:=8)


    For iD = 1 To rCustomLableRange.Count
        With ActiveChart.SeriesCollection(1).Points(iD)
            .HasDataLabel = True
            .DataLabel.Text = rCustomLableRange(iD)
        End With
    Next iD

    Exit Sub

errNoData:
If rCustomLableRange Is Nothing Then Exit Sub

End Sub

Este código aplica las etiquetas sólo a la primera serie de datos (en caso de haber más de una serie). 

Rob Bovey publicó un complemento que ofrece todo tipo de soluciones para el etiquetado de gráficos. 

En la próxima nota mostraré un ejemplo más elaborado.

Totales en gráficos de columnas apiladas

Sin lugar a dudas no es comparable a la invención de la rueda o de la pólvora, pero aquí les traigo un tip sobre como agregar automáticamente totales en gráficos de columnas apiladas en Excel.

Supongamos esta tabla de datos que muestra las ventas del año de las tres sucursales de una empresa imaginaria

Con los datos creamos este gráfico que muestra las ventas por mes

Hemos eliminado el eje de los valores (Y) y puesto etiquetas con los valores (en miles) en las porciones de las columnas. Lo que falta es una etiqueta para cada mes que muestre el total de las ventas.

Podemos, por supuesto, poner cuadros de texto sobre cada columna e inclusive ligarlos a los datos en la tabla. Pero queremos algo más automático.

Empezamos por seleccionar el rango F3:F14 (el total de ventas), lo copiamos y agregamos la serie al gráfico con Pegado Especial

Al hacer esto, Excel agrega la serie a las columnas apiladas deformando nuestro gráfico

Para solucionar esto relacionamos la nueva serie al eje de valores secundario, cambiamos el tipo de gráfico a “línea” y agregamos etiquetas

La línea es innecesaria y la hacemos desaparecer poniendo la opción “color de línea” a “sin línea”

Para que las etiquetas queda inmediatamente por encima de las columnas, usamos la opción “encima” de alineación de las etiquetas

Para quitar “serie 4” de la leyenda, lo seleccionamos y lo borramos (un clic para seleccionar la leyenda y un clic más para seleccionar la etiqueta “serie 4”)

Hacemos desaparecer los ejes de valores poniendo las opciones de marcas de graduación y etiquetas del eje a “ninguno”

Con esto terminamos. Podemos agregar un borde y sombra a las etiquetas del total

Gantt con actividades repetidas en Excel

En el pasado he tocado el tema de construir diagramas Gantt con Excel. Podemos usar, básicamente, dos técnicas: gráficas de barras apiladas o formato condicional (también pueden consultar mi nota sobre diagrama Gantt dinámico).

Las técnicas que muestro en la nota no contemplan el caso en que una tarea se repite a lo largo del proyecto. En esta nota mostraré una técnica sencilla para crear ese tipo de diagrama de Gantt.

Supongamos este ejemplo sencillo

Nuestro proyecto comprende cinco tareas, algunas de las cuales se repiten en la segunda etapa.
Esto es lo que queremos obtener:

Empezamos por construir nuestro diagrama que mostrará la primera etapa del proyecto, seleccionando el rango A2:C7 en nuestro ejemplo e insertando un gráfico de tipo barra apilada

En Excel 2010 obtenemos este resultado, así que tendremos que hacer algunas modificaciones:

Abrimos el asistente Seleccionar origen de datos y:

Editamos el eje de las categorías para que incluya sólo los valores del rango A3:A7

Agregamos la serie Comienzo (el rango B3:B7) y la movemos al primer lugar (usando la flecha indicada en la imagen)

Ocultamos la serie “Comienzo” marcando “Sin relleno” en el asistente de formato de la serie y “Sin línea” en la opción Borde

También el eje de los valores (horizontal) requiere nuestra atención. Cambiamos el valor mínimo del eje de manera que coincida con la primer fecha del proyecto

Finalmente, eliminamos la leyenda y si es necesario cambiamos la orientación de los ejes de manera que la primer tarea quede en la parte superior del diagrama. Para esto seleccionamos el eje de las categorías (el vertical) y cambiamos las definiciones

El resultado es el siguiente

Para poder representar la segunda etapa de la tarea tendremos que crear una columna auxiliar. Podemos incluirla en la tabla, pero mi inclinación personal es ocultarla ya que no aporta ninguna información útil al usuario.

Insertamos una columna a la izquierda de la tabla (o mejor aún dos, Ay B) y creamos la columna auxiliar “Intervalo”

Intervalo cuenta los días transcurridos entre el final de la primer etapa de la tarea y el comienzo de la segunda etapa.

Ahora agregamos dos nueva series a la gráfica: Intervalo y Duración (de la etapa 2)

Dado que vamos a ocultar la columna A, tenemos que cambiar la forma en que Excel representa los datos de columna ocultas apretando el botón “Celdas ocultas y vacías” y marcando “Mostrar datos en filas…”

Ahora tenemos que volver la serie Intervalos invisible, tal como hicimos con la serie Comienzo, cambiando las definiciones de relleno y borde

El último detalle que nos queda son los valores 00/01/1900 (rango I5:I7), Esto se debe a que los valores son ceros, pero el formato es de fecha. Lo que hacemos es cambiar levemente el formato usando formato personalizado “dd/mm/aa;;” (agregamos dos veces punto y coma)

El resultado final

El archivo con el ejemplo se puede descargar aquí (versiones Excel 2003 y Excel 2010).

Mostrar y ocultar series en gráficos de Excel dinámicamente

¿Cómo podemos hacer para mostrar u ocultar series de datos en un gráfico de Excel dinámicamente? Con controles de la barra de formularios, que son sencillos de usar y no requieren programación.
Por ejemplo, supongamos este gráfico

Nuestro objetivo es añadir un elemento que nos permita con un clic mostrar u ocultar cada una de las series.

Veamos los pasos a dar:

Agregamos tres casillas de verificación de la barra de formularios, una para cada serie

Reemplazamos el texto de cada casilla por el nombre de la serie.

Definimos la celda vinculada al control

En este caso he elegido una celda visible, para mostrar el funcionamiento del control. En una implementación real, usaremos una celda oculta.

Hacemos lo mismo con los restantes controles.

Cuando la casilla está activada, el valor de la celda vinculada pasa a ser VERDADERO; cuando no lo está el valor será FALSO. Vamos a aprovechar estos valores en nuestro mecanismo.

El próximo paso consiste en crear cuatro nombres definidos, uno para cada serie y uno para los valores de las categorías (el eje de las X, los meses en nuestro ejemplo).

Para los valores del eje de las X

Mes=dinamico!$B$3:$B$14

Para las series

Ventas =SI(dinamico!$G$20,dinamico!$C$3:$C$14,dinamico!$F$3:$F$14)
Costos=SI(dinamico!$H$20,dinamico!$D$3:$D$14,dinamico!$F$3:$F$14)
Ganancia=SI(dinamico!$I$20,dinamico!$E$3:$E$14,dinamico!$F$2:$F$14)

Como puede verse, estos nombres contienen fórmulas condicionales. Si el valor de la casilla de verificación es VERDADERO, la condición se cumple y el rango es el definido en la tabla de datos; si el valor es FALSO, el rango elegido es F3:F14 que no contiene ningún valor (y por lo tanto no tiene representación en el gráfico).

Ahora tenemos que reemplazar los rangos relevantes en la función SERIES de cada una de las series con los nombres definidos. Esto puede hacerse de varias maneras, por ejemplo:

Seleccionamos la serie VENTAS para que en la barra de las fórmulas aparezca la fórmula SERIES

Reemplazamos los rangos relevantes por los nombres tomando en cuenta no eliminar el signo de admiración (!) que aparece a la izquierda del rango

Apretamos Enter. En lugar del nombre de la hoja aparecerá ahora el nombre del cuaderno

También podemos usar el formulario Modificar Serie de la opción Seleccionar datos

La ventaja de esta técnica es que podemos usar Pegar Nombres (F3) en lugar de tener que escribirlos manualmente. La otra ventaja es que podemos reemplazar el rango de la categorías en el formulario de Editar Rótulos de Eje, sin necesidad de tener que reemplazarlo en la fórmula SERIES de cada serie.

Finalmente, ocultamos las celdas vinculadas, agregamos un cuadro de grupo para agrupar los controles (sólo por cuestiones estéticas) y ya hemos completado nuestro modelo



Descargar el ejemplo.

Gráfico dinámico que muestra los últimos n puntos de la serie

El origen de esta nota es la consulta de un lector que me pregunta cómo hacer un gráfico que muestre siempre los 12 últimos puntos de una serie.

Como en casi todo gráfico dinámico, el truco está en usar rangos dinámicos definidos en nombres. Por lo general, y en esta nota, lo haremos con la ayuda de la función DESREF.

Supongamos esta tabla de ventas que muestra los 12 meses de año 2009 y que hemos representado con un gráfico.

Queremos que el gráfico muestre siempre los últimos n meses, siendo n un número que el usuario defina. Por ejemplo, si agregamos los enero y febrero del 2010 queremos ver los datos desde marzo del 2009 a febrero del 2010.

Como ya hemos mostrado, vamos a reemplazar los rangos estáticos de la serie de datos y de los valores del eje de las categorías, por rangos dinámicos

Antes de definir los rangos dinámicos, definimos un nombre que cuente cuántos períodos (datos) hay en la tabla. Lo hacemos basándonos en la columna de los períodos (la columna B en nuestro ejemplo) ya que puede haber períodos sin datos

cntPeriodos = =CONTARA(dinamico!$B:$B)-1

Restamos 1 del resultado de CONTARA para eliminar la cuenta del título de la columna.

El próximo paso es elegir una celda donde ponemos el número de meses que queremos mostrar (en nuestro ejemplo H2, y no por capricho como ser verá luego). También a H2 le ponemos nombre, pmtMeses

Ahora definimos nombres dinámicos para el eje de la Y, grfY y para el eje de la X, grfX

grfX =DESREF(dinamico!$B$3;cntPeriodos-pmtMeses;0;pmtMeses;1)

grfY =DESREF(dinamico!$C$3;cntPeriodos-pmtMeses;0;pmtMeses;1)

Una vez definidos los nombres reemplazamos los rangos estáticos en la fórmula SERIES, cuidándonos de dejar el nombre del cuaderno incluido el símbolo !

La explicación de estas fórmulas es la siguiente:

dinamico!$B$3;cntPeriodos-pmtMeses calcula desde donde comienza el rango. Por ejemplo si tenemos 14 períodos en la tabla (calculados por cntPeriodos) y queremos mostrar sólo los últimos 12 (pmtMeses), cntPeriodos – pmtMeses da 2; por lo tanto nuestro rango comienza en B5 y se entiende por 12 celdas hasta B16.

Ahora podemos aprovechar la ubicación de H2 para crear un título dinámico para el grafico

Cuando reemplazamos el valor de la celda H2, el título cambia de acuerdo al contenido del gráfico.

El archivo se puede descargar aquí.

Gráficos cascada (Waterfall charts) con Excel.

El gráfico de tipo cascada (waterfall) es un tipo especial de gráfico de columnas flotantes. Según Wikipedia:

El gráfico de tipo cascada es una forma de visualización de datos que ayuda a determinar el efecto acumulado de valores positivos o negativos en una secuencia. El gráfico de cascada es también conocido como “Flying Bricks” debido a la suspensión aparente de las columnas (de ladrillo) en el aire.

Un ejemplo de este tipo de gráficos sería el siguiente



¿En qué situaciones queremos usar este tipo de gráfico? Cuando queremos mostrar cómo se ve afectado un valor inicial por valores intermedios hasta llegar al resultado final.

El gráfico del ejemplo muestra la influencia de cada sucursal en el cambio del total de ventas ocurrido entre 2008 y 2009.


Excel no cuenta con una plantilla para este tipo de gráficos así que tendremos que deberemos maniobrar un tanto con los datos originales.

Los datos de origen son:



Organizamos los datos en la siguiente tabla



Las fórmulas utilizadas en la tabla auxiliar son



Nuestro próximo paso es crear un gráfico de columnas apiladas, para lo cual seleccionamos la tabla auxiliar (el rango G1:L7 en nuestro ejemplo) y usamos insertar –columna apilada (Excel 2007)



Este el primer resultado




Ahora seleccionamos la serie “Corriente”



y la volvemos invisible abriendo el menú de formato de serie de datos y estableciendo “sin relleno” y “sin línea” para el borde




Quitamos la leyenda y cambiamos el color de la serie “Negativo” a rojo y el de la serie “Positivo” a verde



Ahora podemos agregar algunos detalles. Por ejemplo, podemos eliminar el espacio entre las columnas, lo que nos permite enfatizar la influencia de los cambios




También podemos usar Autoformas para enfatizar el sentido del cambio

Technorati Tags: MS Excel