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

Actualización automática de tablas dinámicas

Ha pasado casi un mes desde que publiqué mi última nota. Antes que mis lectores (y Google!) piensen que he abandonado el blog, publico una nota breve sobre actualización automática de tablas dinámicas en Excel.

Excel no tiene un método incorporado para actualizar tablas dinámicas en forma automática. Cambios en la base de datos se reflejarán en el reporte dinámico sólo cuando apretemos el icono Actualizar (o Alt+F5) o Actualizar Todo (Ctrl+Alt+F5) si queremos actualizar todas las tablas dinámicas del cuaderno

Para evitar tener que actualizar manualmente las tablas dinámicas de nuestro cuaderno (pensemos en un dashboard donde hacemos uso intensivo de tablas dinámicas) podemos crear un evento. El código es muy sencillo

Private Sub Worksheet_Deactivate()
    ActiveWorkbook.RefreshAll
End Sub

En este caso usamos el evento Worksheet_Deactivate de la hoja “datos” (la que contiene la base de datos del reporte dinámico). De esta manera nos aseguramos que cada actualizamos la base de datos, al pasar a otra hoja las tablas dinámicas se actualizan.

Esta técnica supone que la base de datos se encuentra en una hoja del cuaderno que contiene los reportes dinámicos. En el caso en que las tablas dinámicas se basen en fuentes de datos externas, usamos el mismo código pero lo disparamos usando el evento Worksheet_Activate de alguna de las hojas que contienen los reportes dinámicos.