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.

BUSCARV (VLOOKUP) con texto dentro de texto

Uno de mis clientes implementó una aplicación para la administración de la planta de producción. En la planta hay una cierta cantidad de máquinas, que fueron asignadas a "centros". Cada centro puede tener una o más máquinas. Para crear la base de datos el ingeniero de producción preparó una tabla con las máquinas asignadas a cada centro

El departamento de computación pidió recibir una tabla con una línea para cada máquina

por lo que hubo que transformar la tabla de nuestro buen ingeniero en la tabla requerida por el departamento de computación (en la realidad hay en la planta 75 máquinas asignadas a 15 centros).

No podemos usar BUSCARV o INDICE con COINCIDIR ya que en una misma celda de la primer tabla hay más de una máquina. De hecho tenemos que extraer el valor correspondiente a un "sub-texto" dentro del texto.

Podemos hacerlo usando funciones nativas de Excel o con una UDF (función definida por el usuario). Con funciones nativas de Excel usamos esta fórmula matricial

 =INDICE(Hoja1!$A$4:$A$9;COINCIDIR(VERDADERO;ESNUMERO(HALLAR(E4;Hoja1!$B$4:$B$9));0))

Esta es una fórmula matricial y debe ser introducida apretando simultáneamente Ctrl+Mayúsculas+Enter

La función HALLAR busca el número de máquina (E4) en el rango de máquinas de la tabla de ingeniero (B4:B9). De hecho, HALLAR busca el "texto" 251 dentro del texto de las celdas del rango B4:B9. Si lo halla, el resultado es un número (la posición donde comienza el texto buscado dentro del texto de la celda de búsqueda).
La expresión ESNUMERO(HALLAR… genera un vector de resultados VERDADERO o FALSO. COINCIDIR calcula la posición del primer valor VERDADERO dentro de ese vector. Este resultado, a su vez, es utilizado en la función INDICE para calcular el centro correspondiente.

También podemos crear esta UDF, usando la función InStr de Vba. La función calcula la posición de la máquina en la lista, es decir, reemplaza la expresión

COINCIDIR(VERDADERO;ESNUMERO(HALLAR…

en la fórmula anterior. El resultado de la función lo usamos como argumento en la función INDICE, sin necesidad de usarla en forma matricial

 El código de la función match_sub_string es el siguiente:

 Function match_sub_string(SearchValue, rngSearchRange As Range)

    Dim lRangeRows As Long 'numero de filas en el rango
    Dim iR As Long
  
    lRangeRows = rngSearchRange.Cells.Count
  
    For iR = 1 To lRangeRows
        If InStr(rngSearchRange.Item(iR), SearchValue) > 0 Then
            match_sub_string = iR
            Exit Function
        Else
            'do nothing
        End If
    Next iR
  
       
End Function