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).
Sugerencias y ayuda para Excel en español. Ejemplos de funciones y fórmulas, gráficos, automatización con Vba, Power Query y PowerPivot
domingo, enero 29, 2012
jueves, enero 26, 2012
Sincronización de tablas dinámicas en Excel
En el foro EEE (Expertos en Excel en Español), Julio consultaba: “Quiero vincular tres tablas distintas en el mismo archivo, de forma que cuando filtre por mes, se me cambien todos los datos de todas las tablas a la vez”.
Con Excel 2010 podemos hacerlo con facilidad usando la nueva funcionalidad “Segmentación de Datos”. Pero si usamos versiones anteriores de Excel, tendremos que usar macros o, más precisamente, programar un evento.
Veamos el siguiente ejemplo (que se puede descargar aquí): en la hoja “bd” tenemos datos de la facturación de una empresa,
en la hoja “reporte” hemos construido tres tablas dinámicas
La primer tabla a la izquierda muestra las ventas por país, la del centro resume los datos por vendedor y la tabla a la derecha muestra los 10 clientes principales. Las tres tablas están filtradas por año (los datos en “bd” aparecen por fecha, por eso usamos la funcionalidad Agrupar para resumir los datos por año).
Si queremos resumir los datos del año 2008, por ejemplo, tenemos que cambiar el valor del filtro de cada uno de los informes.
Nuestro objetivo es programar un evento de manera que cuando cambiamos el valor del filtro de un informe, los otros dos cambien simultáneamente. En el módulo de Vba de la hoja “reporte” ponemos este código
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim tdfecha As String
Dim pt As PivotTable
tdfecha = Target.PivotFields("Fecha").CurrentPage
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("Fecha")
.ClearAllFilters
.CurrentPage = tdfecha
End With
Next pt
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
El evento Worksheet_PivotTableUpdate se dispara cuando cambiamos algo en la tabla que genera la actualización (en nuestro caso cambiar el valor del filtro).
Para captar el valor del filtro definimos la variable “tdfecha”
tdfecha = Target.PivotFields("Fecha").CurrentPage
Luego usamos Application.EnableEvents= False para evitar que se cree una reacción en cadena de eventos (queremos responder sólo al primer cambio).
El próximo paso es usar for – next para inicializar el valor del filtro de cada una de las tablas dinámicas en la hoja y fijar su valor con la variable “tdfecha”.
El último paso es restablecer los eventos y la actualización de pantalla que habíamos cancelado previamente.
En Excel 2003 tenemos que remplazar la sentencia .ClearAllFilters por .CurrentPage = "All"
El funcionamiento del evento puede verse en este video
Con Excel 2010 podemos hacerlo con facilidad usando la nueva funcionalidad “Segmentación de Datos”. Pero si usamos versiones anteriores de Excel, tendremos que usar macros o, más precisamente, programar un evento.
Veamos el siguiente ejemplo (que se puede descargar aquí): en la hoja “bd” tenemos datos de la facturación de una empresa,
en la hoja “reporte” hemos construido tres tablas dinámicas
La primer tabla a la izquierda muestra las ventas por país, la del centro resume los datos por vendedor y la tabla a la derecha muestra los 10 clientes principales. Las tres tablas están filtradas por año (los datos en “bd” aparecen por fecha, por eso usamos la funcionalidad Agrupar para resumir los datos por año).
Si queremos resumir los datos del año 2008, por ejemplo, tenemos que cambiar el valor del filtro de cada uno de los informes.
Nuestro objetivo es programar un evento de manera que cuando cambiamos el valor del filtro de un informe, los otros dos cambien simultáneamente. En el módulo de Vba de la hoja “reporte” ponemos este código
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim tdfecha As String
Dim pt As PivotTable
tdfecha = Target.PivotFields("Fecha").CurrentPage
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("Fecha")
.ClearAllFilters
.CurrentPage = tdfecha
End With
Next pt
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
El evento Worksheet_PivotTableUpdate se dispara cuando cambiamos algo en la tabla que genera la actualización (en nuestro caso cambiar el valor del filtro).
Para captar el valor del filtro definimos la variable “tdfecha”
tdfecha = Target.PivotFields("Fecha").CurrentPage
Luego usamos Application.EnableEvents= False para evitar que se cree una reacción en cadena de eventos (queremos responder sólo al primer cambio).
El próximo paso es usar for – next para inicializar el valor del filtro de cada una de las tablas dinámicas en la hoja y fijar su valor con la variable “tdfecha”.
El último paso es restablecer los eventos y la actualización de pantalla que habíamos cancelado previamente.
En Excel 2003 tenemos que remplazar la sentencia .ClearAllFilters por .CurrentPage = "All"
El funcionamiento del evento puede verse en este video
sábado, enero 14, 2012
Ordenar listas numéricas con fórmulas en Excel
Nada más sencillo que ordenar listas o tablas en Excel. Un clic al icono correspondiente (ascendente o descendente) y Excel ordena la lista. Pero en ciertas situaciones queremos que la lista se ordene automáticamente al ir agregando o quitando datos.
Podemos programar un evento que se ocupe de ordenar nuestra lista con cada cambio, pero también podemos hacerlo con fórmulas. En esta nota veremos cómo hacerlo en el caso de listas numéricas (cómo hacerlo con listas de texto he mostrado en esta nota).
Supongamos un rango donde vamos agregando fechas (recordemos que la fechas son números en Excel)
Como puede verse estoy usando ALEATORIO.ENTRE para generar fechas en forma aleatoria.
Para ordenar esta lista en orden ascendente usamos esta fórmula
=K.ESIMO.MENOR(lstFechas,FILA()-1)
donde “lstFechas” es un nombre que define el rango de las fechas en forma dinámica, con la fórmula
=fechas!$A$2:INDICE(fechas!$A:$A,CONTARA(fechas!$A:$A))
Para ordenar la lista en orden descendente usamos esa otra fórmula
=K.ESIMO.MENOR(lstFechas,CONTARA(lstFechas)-FILA()+2)
Una desventaja de este método es que cada vez que agregamos un valor a lista debemos copiar las fórmulas. Podemos superar este inconveniente convirtiendo el rango en “tabla”
Al convertir el rango en tabla, las fórmulas son copiadas automáticamente.
Podemos programar un evento que se ocupe de ordenar nuestra lista con cada cambio, pero también podemos hacerlo con fórmulas. En esta nota veremos cómo hacerlo en el caso de listas numéricas (cómo hacerlo con listas de texto he mostrado en esta nota).
Supongamos un rango donde vamos agregando fechas (recordemos que la fechas son números en Excel)
Como puede verse estoy usando ALEATORIO.ENTRE para generar fechas en forma aleatoria.
Para ordenar esta lista en orden ascendente usamos esta fórmula
=K.ESIMO.MENOR(lstFechas,FILA()-1)
donde “lstFechas” es un nombre que define el rango de las fechas en forma dinámica, con la fórmula
=fechas!$A$2:INDICE(fechas!$A:$A,CONTARA(fechas!$A:$A))
Para ordenar la lista en orden descendente usamos esa otra fórmula
=K.ESIMO.MENOR(lstFechas,CONTARA(lstFechas)-FILA()+2)
Una desventaja de este método es que cada vez que agregamos un valor a lista debemos copiar las fórmulas. Podemos superar este inconveniente convirtiendo el rango en “tabla”
Al convertir el rango en tabla, las fórmulas son copiadas automáticamente.
Suscribirse a:
Entradas (Atom)