miércoles, 12 de julio de 2017

Gráfico de Ranking en Excel

Fechas atrás me preguntaba un usuario por un tipo de gráfico de moda como es el gráfico de ranking.
En particular pedía reconstruir de la manera más próxima posible el siguiente gráfico:

Gráfico de Ranking en Excel



Nuestra replica de este gráfico de ranking con Excel tendrás este aspecto:

Gráfico de Ranking en Excel



Nuestro trabajo empieza tratando los datos.
Partimos de la siguiente tabla en el rango I3:M7 donde disponemos de unos importes de ventas por conceptos.
A partir de esos importes generamos en el rango C3:G7 una tabla auxiliar (que será la que representaremos gráficamente) a partir de la función JERARQUIZ.EQUIV.
En la celda C3 insertamos:
=JERARQUIA.EQV(I3;I$3:I$7)
y luego arrastramos al resto del rango C3:G7.
Con esta fórmula obtenemos la ordenación/jerarquía por cada año de ventas para los diferentes conceptos.

Gráfico de Ranking en Excel



Como último paso previo a la generación del gráfico, construimos los textos de las etiquetas de datos para los últimos puntos de cada serie.
Para esto en el rango B3:B7 insertamos la fórmula:
=G3&REPETIR(" ";10)&TEXTO(M3/SUMA($M$3:$M$7);"0%")&REPETIR(" ";5)&A3

Que nos permite concatenar en una celda la posición del último año, junto al peso de ese concepto sobre el total del año y la descripción de ese concepto.

Gráfico de Ranking en Excel



Paso 1: Insertamos el gráfico de línea con marcadores
Tras seleccionar el rango B2:G7, y esde la ficha Insertar > grupo Gáficos > desplegable Líneas o Áreas > tipo Líneas con marcadores

Gráfico de Ranking en Excel



Paso 2: Cambiamos el nombre del gráfico por 'GraficoRanking'
Importante para un paso posterior.


Paso 3: Cambiamos el estilo de diseño del gráfico por el de 'Estilo 2'
Con el gráfico seleccionado, y desde las herramientas de gráfico > pestaña Diseño > grupo Estilos de diseño > Estilo 2

Gráfico de Ranking en Excel



Paso 4: Eliminamos Título del gráfico, Líneas de división principales y Leyenda (opcional).

Paso 5: Añadimos al gráfico un Eje Vertical Primario.
A continuación, dicho eje, lo configuramos marcando la opción de eje: Valores en orden inverso

Gráfico de Ranking en Excel



Paso 6: Eliminamos el Eje vertical primario insertado en el paso anterior.
Este es el aspecto ahora mismo de nuestro gráfico, bastante similar ya a lo buscado.

Gráfico de Ranking en Excel



Paso 7 (y último): Ejecutamos la macro siguiente.
Abrimos la ventana de código de nuestro módulo estándar y añadimos el siguiente código:

Sub Ultima_Etiqueta()
Dim serie As Series
Dim punto As Long
Dim MiGrafico As Chart
'controlamos el gráfico sobre el que trabajar
Set MiGrafico = ActiveSheet.ChartObjects("GraficoRanking").Chart
        
For Each serie In MiGrafico.SeriesCollection
    With serie
        'identificamos el último punto de las series
        punto = .Points.Count
        'aplicamos características a ese último punto de la serie
        serie.Points(punto).ApplyDataLabels _
            ShowSeriesName:=True, _
            ShowCategoryName:=False, _
            ShowValue:=False, _
            AutoText:=True, _
            LegendKey:=False
        'damos formato en cuanto a posición y color
        With .Points(punto).DataLabel
            .Position = xlLabelPositionRight
            .Font.Color = vbBlack
        End With
    End With
Next serie

End Sub



Tras ejecutarla comprobaremos como la etiqueta de datos del último punto toma los textos deseados...
Solo haría falta ajustar el área del gráfico para que la etiqueta tome la posición deseada.

Este paso se podría realizar manualmente, evitando el uso de la programación.

En cualquier caso el resultado obtenido es el deseado:

Gráfico de Ranking en Excel

No hay comentarios:

Publicar un comentario