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

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.

Formato condicional en los últimos n valores de una serie

El lector Willy me consultaba en la nota de ayer cómo aplicar un fondo con formato condicional a las celdas que participan en el cálculo (las últimas n celdas de la serie). La consulta es interesante ya que esto nos permite un control visual efectivo de los valores comprendidos en el cálculo. Si bien le respondí en un comentario en la nota, vale la pena ampliar el tema.

Formato condicional funciona con fórmulas booleanas, es decir, que el resultado debe ser VERDADERO o FALSO. Sólo cuando el resultado es VERDADERO, se aplica el formato elegido.

Dado esto, nuestra táctica será calcular si la fila de la celda evaluada cae dentro del rango de la fórmula. Por ejemplo, si queremos marcar las últimas 10 filas

seleccionamos el rango C5:C160 (siguiendo con nuestro ejemplo) y en “formato condicional-nueva regla-utilice fórmula…” usamos

Y(FILA(C5)>=CONTAR($C$5:$C$160)+5-$C$1,FILA(C5)<=CONTAR($C$5:$C$160)+4)

donde C1 contiene el número de valores a incluir en el cálculo.

La fórmula funciona así: calculamos el número fila de la primer celda en el rango con

 CONTAR($C$5:$C$160)+5-$C$1

 Calculamos el número de fila del última valor en el rango con

 CONTAR($C$5:$C$160)+4

 donde 4 es el número de filas por encima de la primer fila del rango de valores.

 Ahora usamos la función FILA para comparar si el número de fila de la celda evaluada cae dentro del rango relevante. Esto lo hacemos con la función Y, que da VERDADERO sólo si todas las condiciones dentro la fórmula se cumplen.

 Personalmente prefiero una técnica distinta. Por algún motivo que no termino de comprender, muchos usuarios evitan usar columnas auxiliares en los modelos de Excel. Esto nos lleva casi siempre a crear fórmulas complicadas cuyo principal problema reside en la dificultad de controlar los resultados (o recordar que quisimos hacer cuando volvemos a trabajar con el modelo dos semanas más tarde).

 En el caso que estamos analizando, mi propuesta es la siguiente:

 1 – insertamos 3 columnas a la izquierda de la tabla de datos (dos columnas son suficientes)

 2 – En la celda B2 ponemos la fórmula “=CONTAR(E5:E160)+4”, que calcula la última fila con valores en el rango (tal como hicimos en la fórmula anterior)

 3 – En la celda A2 ponemos “=B2-E1+1” que calcula la primer fila del rango (E1 contiene el número de valores que queremos incluir en el cálculo)

 4 – En la celda B5 ponemos la fórmula “=Y(FILA(E5)>=$A$2,FILA(E5)<=$B$2)” y la copiamos a lo largo del rango a evaluar (en nuestro ejemplo B5:B160)

 Como se ve, creamos una serie de valores VERDADERO (cuando la fila cae dentro del rango) o FALSO (cuando está fuera del rango). Ahora usamos esto valores para accionar el formato condicional 

Como puede apreciarse, la fórmula en el formato condicional es obviamente sencilla, lo mismo que las fórmulas en las columnas auxiliares.







El último toque es ocultar las columnas auxiliares

Descarga del archivo del ejemplo