Como Borrar elementos antiguos de una lista de filtros de tabla dinámica

Es una pregunta que todo usuario de tabla dinámica se ha tenido que hacer en algún momento: ¿Cómo borrar elementos antiguos de una lista de filtros de tabla dinámica?

Mire la captura de pantalla a continuación, podemos ver Delta en el cuadro de filtro de la tabla dinámica y la segmentación de datos, pero no hay ningún Delta en la tabla de datos de origen. ¿Cómo puede suceder eso?

Tabla de contenido

• ¿Por qué pasó esto?
• Borrar elementos antiguos de una tabla dinámica
• Cambiar la configuración predeterminada de la tabla dinámica
• Cambiar la configuración de la tabla dinámica con VBA
• Borrar elementos antiguos de la tabla dinámica seleccionada
• Borrar elementos antiguos de todas las tablas dinámicas del libro
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0065 Borrar elementos antiguos en PivotTable.zip

Tener acceso

¿Por qué pasó esto?

Cada vez que actualizamos una tabla dinámica, se actualiza la caché dinámica. Pivot Cache es una parte invisible de Excel que almacena una copia de los datos de origen.

Tener una copia de los datos garantiza que los cálculos de la tabla dinámica sean rápidos. Entonces, cuando actualizamos una tabla dinámica, en realidad estamos actualizando los datos contenidos en Pivot Cache.

Incluso después de eliminar registros de los datos de origen, los elementos antiguos aún pueden aparecer en los menús desplegables y en las segmentaciones de la tabla dinámica. Pivot Cache contiene una copia de los datos y tiene su propia configuración que rige cómo se utilizan esos datos.

Uno de los comportamientos predeterminados de Pivot Cache es que conserva una lista de valores únicos anteriores.

Pero ya basta de por qué…. veamos cómo solucionarlo.

Borrar elementos antiguos de una tabla dinámica

Para borrar los elementos antiguos de una lista desplegable o segmentación de tabla dinámica, debemos cambiar una de las opciones de la tabla dinámica:

1. Haga clic derecho en una celda de la tabla dinámica
2. En el menú contextual, seleccione Opciones de tabla dinámica...
   
3. En el cuadro de diálogo Opciones de tabla dinámica , seleccione la pestaña Datos
4. Cambie la opción Número de elementos a conservar por campo a Ninguno .
   
5. Haga clic en Aceptar para cerrar el cuadro de diálogo Opciones de tabla dinámica.
6. Finalmente, actualice la tabla dinámica .

¡Ta-dah! Todos esos elementos históricos redundantes ahora han desaparecido.

Notas:
Esta configuración se aplica a la caché dinámica, por lo que el cambio se aplicará a todas las tablas dinámicas creadas a partir de esa caché.
Si se utiliza el modelo de datos como origen de una tabla dinámica, no se utiliza la caché dinámica; y no sufre con este problema.

Cambiar la configuración predeterminada de la tabla dinámica

Si tiene Excel 365, Excel 2019 o posterior, es posible cambiar la configuración predeterminada de la tabla dinámica. Esto garantiza que a cada tabla dinámica que creemos en el futuro se le aplicará la configuración anterior automáticamente.

1. Desde la cinta en la parte superior, haga clic en Opciones de archivo
2. En el cuadro de diálogo Opciones de Excel , seleccione la sección Datos
3. Haga clic en el botón Editar diseño predeterminado...
   
4. En el cuadro de diálogo Editar diseño predeterminado , haga clic en el botón Opciones de tabla dinámica...
   
5. Aparece el cuadro de diálogo Opciones de tabla dinámica , seleccione la pestaña Datos
6. Cambie la opción Número de elementos a conservar por campo a Ninguno .
   
7. Haga clic en Aceptar tres veces para cerrar cada una de las ventanas.

Las próximas tablas dinámicas que cree en el futuro ya no conservarán los elementos faltantes.

Cambiar la configuración de la tabla dinámica con VBA

La configuración modificada anteriormente también se puede aplicar con VBA. Estos fragmentos de código se pueden ejecutar desde un módulo de código estándar.

Borrar elementos antiguos de la tabla dinámica seleccionada

La siguiente macro aplica los mismos pasos anteriores a la tabla dinámica seleccionada.

Sub ClearOldPtItemsActive()'Crear la variable para PivotTableDim pt como tabla dinámica'Encontrar la tabla dinámica activa para cada pt en ActiveSheet.PivotTables Si no Intersect(Selection, pt.TableRange2) no es nada, entonces 'Cambiar la configuración pt.PivotCache.MissingItemsLimit = xlMissingItemsNone End SiSiguiente ptEnd Sub

Hay tres opciones disponibles para la propiedad Límite de elementos faltantes. Solo hemos aplicado la opción xlMissingItemsNone en esta publicación; A continuación se enumeran todas las opciones disponibles para esta propiedad.

'El número predeterminado de elementos únicos por campo dinámico permitidopt.PivotCache.MissingItemsLimit = xlMissingItemsDefault'Tablas dinámicas anteriores a Excel 2007 que tienen 32,500 como máximo de elementos únicospt.PivotCache.MissingItemsLimit = xlMissingItemsMax'Las tablas dinámicas de Excel 2007+ tienen 1,048,576 como máximo de único itemspt.PivotCache.MissingItemsLimit = xlMissingItemsMax2'No se permiten elementos únicos por campo dinámicopt.PivotCache.MissingItemsLimit = xlMissingItemsNone

Borrar elementos antiguos de todas las tablas dinámicas del libro

La siguiente macro aplica la misma configuración que la anterior pero a todas las tablas dinámicas del libro activo.

Sub ClearOldPtItemsWb()'Crear las variablesDim ws como hoja de trabajoDim pt como tabla dinámica'Recorrer todas las hojas de trabajo para cada ws en ActiveWorkbook.Worksheets 'Recorrer todas las tablas dinámicas para cada pt en ActiveSheet.PivotTables 'Cambiar la configuración pt.PivotCache.MissingItemsLimit = xlMissingItemsNone Siguiente ptSiguiente wsEnd Sub

Notas: En el momento de redactar este artículo, no hay códigos equivalentes disponibles en Office Scripts.

Conclusión

Entonces, resulta que es solo una configuración molesta la que hace que aparezcan todos esos elementos redundantes. Pero afortunadamente, ahora sabes cómo borrar elementos antiguos de una tabla dinámica, por lo que ya no será un problema para ti.

---