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).
