Gráfico Big Mac dinámico en Excel

domingo, enero 29, 2012

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

6 comments:

Claudio,  31 enero, 2012 17:08  

Muy buen ejemplo para aplicar a nuestros mandos de control.

Anónimo,  09 febrero, 2012 17:35  

Se que no tiene nada que ver pero no sabia que mexico fuera parte de suramerica.

Muy buen ejemplo.

manolo 17 febrero, 2012 17:17  

Don Jorge.
Muy bueno el tipo de gráfico. Mi consulta es si se pueden incorporar y seleccionar más variables, ya que en el ejemplo sólo se trabaja con 1.
Desde ya, muchas gracias,

Manuel.

Jorge L. Dunkelman 17 febrero, 2012 21:01  

Si, se puede, pero habría que ver si desde el punto de vista de la presentación de los datos este tipo de gráfico es el adecuado (para el caso en que hay más de una variable).

manolo 20 febrero, 2012 22:45  

Don Jorge.
La presentación de los datos es 1 matríz de 7c x 54f, (4 columnas más que su ejemplo). La idea es poder seleccionar siempre las 2 primeras, y al menos 1 de las otras 5. Me imagino que el problema puede ser la presentación de los datos, y me gustaría saber si se puede modificar la macro para evitar crear una matríz gigante.
Nuevamente, muchas gracias,
Manuel.

Jorge L. Dunkelman 22 febrero, 2012 18:50  

Mi sugerencia es que en lugar de crear un gráfico que muestre todas las series de datos, hagas 4 o 5 gráficos idénticos en tamaño y escala, uno para cada serie. Luego puedes ordernarlos poniendo uno al lado del otro o en grupos para facilitar la comparación (que en definitiva es el objetivo de la presentación gráfica de los datos).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP