Gráfico Big Mac dinámico en Excel

La publicación británica The Economist publica desde hace varios años el índice Big Mac. El Big Mac Index (o Índice Big Mac, en español) es un índice elaborado a partir de una investigación no científica, que permite comparar el poder adquisitivo de distintos países donde se vende la hamburguesa Big Mac de McDonald's (citado de Wikipedia).

El diario argentino La Nación publicó este gráfico que muestra la diferencia del precio del Big Mac en relación al valor en los Estados Unidos

Este gráfico fue construido con la aplicación Tableau.

Como ya habrán intuido, la pregunta es: ¿se puede hacer con Excel?

Veamos qué elementos incluye:

• Gráfico de barras por país
• Controles que permiten visualizar los países por continente
• Los colores de las barras representan el valor (diferencias positivas en rojo, diferencias negativas en verde)

La tabla de datos es la siguiente

Empezamos por ordenar la tabla en orden ascendente según el campo Porcentaje; seleccionamos los campos “País” y “Porcentaje” para construir este gráfico de barras

Este es el gráfico de barras estándar de Excel con unas pocas modificaciones: quitamos las líneas de cuadrícula, fijamos las etiquetas del eje vertical en “bajo” y en formato de series de datos—relleno marcamos la opción “variar colores entre puntos”.

Este gráfico es una primera aproximación. Para poder agregar los elementos dinámicos y los calores del gráfico original tendremos que hacer algunas transformaciones.

Una segunda aproximación es usar una tabla dinámica para generar un gráfico dinámico que muestre sólo los países de los continentes elegidos



Lo que hemos hecho es generar una tabla dinámica y un gráfico dinámico basada en ella; la tabla está en la filas 3 a 27 que hemos ocultado, dejando visible sólo el campo de filtro del informe dinámico. Además hemos agregado una segmentación de datos para mostrar cuáles son los continentes elegidos

Pero para crear un gráfico como el publicado en La Nación tendremos que usar Vba (macros) y controles.
Esto es lo que queremos crear:



La anatomía del modelo es la siguiente:

1 – Controles: insertamos 7 casillas de verificación (de la colección de controles de hoja, no ActiveX) y los ligamos a las celdas en el rango B3:B9. Cuando se señala el control, la celda correspondiente muestra VERDADERO; en caso contrario mostrará FALSO. Usaremos estos valores en las macros que controlan los puntos mostrados en el gráfico.

2 – Celda de control: la celda B11 (el nombre Dimension_del_array se refiere a esta celda), cuenta cuantos controles han sido señalados, es decir, cuantos continente queremos mostrar en el gráfico. Cuando el valor es 6, significa que hemos elegido todos los continentes. También esta celda la usaremos en nuestras macros.

3 – Macros: programamos dos macros para ocultar o mostrar datos con Autofiltro en la tabla delos datos, una que responde a las elecciones de los controles excepto el control “Todos”, la segunda para el caso que el usuario señale la casilla “Todos”

El código de la primer macro es

Sub select_series()

    Dim strContinentes() As String
    Dim iR As Integer
    Dim iCounter As Integer
  
    If Range("Dimension_del_array") = 6 Then
        Range("Todos") = True
    Else
        Range("Todos") = False
    End If

    'redimensionar el array
    ReDim strContinentes(Range("Dimension_del_array"))
  
    iCounter = 0
  
    With Sheets("grafico dinamico")
    For iR = 4 To 9
        If .Cells(iR, 2) Then
                strContinentes(iCounter) = .Cells(iR, 1)
                iCounter = iCounter + 1
        End If
    Next iR
  
    Sheets("datos").Range("$A$1:$C$24").AutoFilter Field:=1, _
                Criteria1:=strContinentes, Operator:=xlFilterValues
  
    End With
    
End Sub


La segunda macro, para el caso que se haya elegido la casilla “Todos” es

Sub all_Continents()
    Dim iR As Integer
  
    'si se elige Todos
    With Sheets("grafico dinamico")
    If Range("Todos") Then
        For iR = 4 To 9
            .Cells(iR, 2).Formula = True
        Next iR
    Else
        For iR = 4 To 9
            .Cells(iR, 2).Formula = False
        Next iR
    End If
    End With

    Call select_series
        
End Sub


En este modelo usamos una única serie de valores, por eso para ocultar algunos de los puntos de la serie con Autofiltro (los países de los continentes que no hemos elegido) nos aprovechamos de la propiedad de los gráficos de no mostrar los valores de celdas ocultas.

El control “Todos” está asociado a la macro “all_Continents”

A los demás controles les hemos asignado la macro “select_series”.

Este modelo puede adaptarse a muchos escenarios.

El cuaderno puede descargarse, sin cargo, aquí (usar el enlace Descargar en la parte inferior de la página). Quien esté interesado en una explicación detallada del modelo y la contraseña para acceder a los códigos, puede descargar el manual (tiene un costo de 5 Euros).