Si has usado alguna vez una tabla dinámica (o Pivot Table) de Excel, es posible que te hayas encontrado alguna vez en la situación que vamos a comentar en este artículo.
¿Cuál es la situación?
Los datos de origen de una tabla dinámica pueden cambiar y los elementos que antes estaban pero ya no no deberían volver a aparecer en los desplegables que se utilizan para filtrar.
Sin embargo, incluso después de pulsar sobre el botón refrescar, los antiguos nombres siguen apareciendo junto con los nuevos.
¿Y cómo se soluciona?
En este artículo proponemos 3 soluciones para limpiar los valores que permanecen en los desplegables y no hay manera de eliminar:
- Manualmente
- Programáticamente
- Modificando las propiedades de la tabla dinámica (sólo es posible a partir de la versión 2007 de Excel).
Limpiar los valores de forma manual.
La forma manual de eliminar los valores consiste en desagrupar todo lo que esté agrupado. Después de esto hay que eliminar el campo de la tabla dinámica (arrastrándolo fuera de la tabla). A continuación pincharemos sobre la opción "Actualizar" de la tabla y, por último, volvemos a introducir el campo en la misma.
Limpiar los valores mediante una llamada a un procedimiento en Visual Basic.
En versiones de Excel 2002 o posteriores, se pueden modificar las propiedades de una tabla dinámica para prevenir que aparezcan elementos eliminados del origen de datos de la siguiente forma:
Sub DeleteOldItemsV1()
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc
End Sub
Si estamos hablando de versiones de Excel anteriores a la 2002, podemos utilizar el siguiente código para eliminar los elementos antiguos:
Sub DeleteOldItemsV2()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
pt.ManualUpdate = True
For Each pf In pt.VisibleFields
If pf.Name <> "Data" Then
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next pi
End If
Next pf
pt.ManualUpdate = False
pt.RefreshTable
Next pt
Next ws
End Sub
Cambiar las opciones de la tabla dinámica (sólo Excel 2007 y versiones posteriores)
En Excel 2007 y en las versiones posteriores ya podemos modificar las opciones de la tabla dinámica, que modificabamos mediante Visual Basic en el punto anterior, pero a través del menú de opciones.
Pinchamos con el botón derecho sobre una celda de la tabla dinámica y hacemos click sobre "Opciones de la tabla dinámica…". En la pestaña "Datos", nos vamos a la sección "Mantener los elementos eliminados del origen de datos" y seleccionamos "Ninguno" en el desplegable. Por último, guardamos los cambios y refrescamos la tabla dinámica pulsando sobre el botón "Actualizar" de la misma.
