Cuando tienes varias tablas dinámicas y necesitas aplicar un filtro a todas ellas al mismo tiempo en base al valor de una celda te das cuenta que no existe una manera fácil de hacerlo en Excel. Hoy analizaremos una alternativa para filtrar una tabla dinámica según el valor de una celda.
Antes de continuar, debo aclarar que los filtros de informe son la opción más sencilla para filtrar una tabla dinámica ya que podemos elegir los datos que deseamos visualizar. Sin embargo, hay ocasiones en las que una fórmula de Excel inserta un valor en una celda por el cual queremos filtrar una tabla dinámica o simplemente porque el usuario desea ingresar el texto manualmente. Cualquiera que sea el caso, será necesario utilizar código VBA para filtrar la tabla dinámica en base al valor de dicha celda.
Como primer ejemplo utilizaremos una tabla dinámica que muestra la lista de estados con sus respectivos montos de ventas de los últimos dos años. Cada estado pertenece a una región (Norte, Sur, Este, Oeste) que está presente como el filtro de informe de la tabla dinámica, pero no utilizaremos dicho filtro de informe sino que aplicaremos el filtro a la tabla dinámica en base al valor de la celda F1.
Filtrar tabla dinámica según valor de celda
El código que utilizaremos detecta cualquier cambio en la celda F1 y posteriormente se realiza la actualización de los datos de la tabla dinámica.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
'En base al campo Region de la Tabla dinámica1:
With PivotTables("Tabla dinámica1").PivotFields("Region")
'Limpiar todos los filtros
.ClearAllFilters
'Filtrar por el valor de la celda F1
On Error Resume Next
.CurrentPage = Range("F1").Value
End With
End If
End Sub
La colección PivotTables nos permite acceder a los campos de una tabla dinámica. En nuestro ejemplo nos interesa el campo Region para el cual se limpia cualquier filtro antes de aplicar uno nuevo basado en el valor de la celda F1. El resultado de esta macro es el siguiente:
Al ingresar el valor Norte en la celda F1 la macro aplicará el filtro a la tabla dinámica y puedes notar que el filtro de informe también cambia automáticamente al valor Norte.
En cuanto a los posibles errores que se pudieran presentar, nuestra macro considera aquellos casos en los que ingresamos una Region no válida en la celda F1, o si simplemente dejamos la celda en blanco, ya que con la línea de código On Error Resume Next se captura el error y se limpia el filtro.
Macro para filtrar todas las tablas dinámicas
Como segundo ejemplo crearé una tabla dinámica diferente en otra hoja y modificaré la macro anterior para filtrar todas las tablas dinámicas del libro de acuerdo al valor de la celda F1. El código de la macro es el siguiente:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
Dim hoja As Worksheet
Dim td As PivotTable
'Recorrer todas las hojas del libro
For Each hoja In ThisWorkbook.Worksheets
'Recorrer las tablas dinámicas de la hoja
For Each td In hoja.PivotTables
'En base al campo Region de la tabla dinámica:
With td.PivotFields("Region")
'Limpiar todos los filtros
.ClearAllFilters
'Filtrar por el valor de la celda F1
On Error Resume Next
.CurrentPage = Range("F1").Value
End With
Next td
Next
End If
End Sub
A diferencia del primer ejemplo, en esta macro utilizamos la colección ThisWorkbook.Worksheets para recorrer todas las hojas del libro y de esa manera acceder a todas las tablas dinámicas. Al ingresar un valor en la celda F1 se aplicará el filtro correspondiente a la tabla dinámica de la hoja actual:
Además, si cambiamos a la hoja del primer ejemplo, notarás que también se ha aplicado el mismo filtro a la tabla dinámica:
Observa que el filtro de informe muestra el valor Sur aunque la celda F1 esté vacía, pero eso no importa porque el filtro se ha aplicado considerando la celda F1 de la otra hoja. Lo único que es necesario para que esta macro funcione correctamente es que todas las tablas dinámicas deben incluir el campo por el cual se está filtrando, que en nuestro ejemplo es el campo Region.
Las macros que hemos creado te ayudarán a filtrar una tabla dinámica por el valor de una celda e inclusive podrás aplicar dicho filtro a todas las tablas dinámicas de un libro si así lo requieres. Descarga el libro de trabajo utilizado en este artículo y continúa probando los filtros.
Por último debo decir que, si en lugar de utilizar el valor de una celda, deseas filtrar varias tablas dinámicas con un solo filtro de informe, te recomiendo leer el artículo Cómo vincular tablas dinámicas por un filtro de informe donde encontrarás otra alternativa de solución.