Tablas Dinámicas en Excel – eliminar elementos inexistentes

viernes, noviembre 20, 2009

El problema: después de borrar líneas en una lista (base de datos para una tabla dinámica) los valores eliminados siguen apareciendo en las listas desplegables de la tabla dinámica.

Observación: esta técnica se aplica a Excel Clásico. Excel 2007 tiene un método incorporado para solucionar este problema.

Supongamos este modelo en una hoja de Excel


tablas dinamicas en Excel - eliminar valores inexistentes

Vemos una tabla/lista en el rango A1:C19 que sirve como base de datos para la tabla dinámica que vemos a la derecha en el rango E1:I9


La tabla dinámica muestra todas las ventas de los agentes para todos los meses que aparecen en la base de datos.



Ahora despleguemos la lista del campo Fechas

tablas dinamicas en Excel - eliminar valores inexistentes

¿Por qué aparecen las fechas Julio-09, Agosto-09, Setiembre-09 y Octubre-09, si no están en la base de datos?



El motivo es que estas fechas si estaban en la base datos al momento de construir la tabla dinámica, pero fueron borrados posteriormente.

Todo nos haría pensar que bastaría con apretar el botón “Actualizar Datos” para que estos valores inexistentes desaparezcan, pero no es así.



En el pasado vimos como solucionar esto manualmente. En nuestro ejemplo quitamos el campo Fechas, actualizamos los datos y volvemos a poner el campo Fechas en la tabla.





Sin embargo, hay situaciones en las cuales esta técnica no funciona. Por ejemplo, si tenemos varias tablas dinámicas basadas en la misma base de datos (de hecho, comparten el mismo PivotCache).

Por ejemplo, si creamos una segunda tabla basada que en la base de datos, vemos que los meses inexistentes siguen apareciendo en la lista desplegable

tablas dinamicas en Excel - eliminar valores inexistentes

Volvemos a repetir la técnica indicada al principio, pero los meses inexistentes siguen apareciendo




En este caso tendremos que usar una macro para solucionar el problema. En un módulo común del editor de Vba ponemos este código

Sub eliminarValoresenPTCache()
Dim ptC As PivotCache

For Each ptC In ActiveWorkbook.PivotCaches
ptC.MissingItemsLimit = xlMissingItemsNone
ptC.Refresh
Next ptC

End Sub


Después de correr esta macro, los valores borrados de la base de datos ya no aparecerán en las tablas dinámicas.


Technorati Tags:



11 comments:

Anónimo,  21 noviembre, 2009 02:10  

Jorge:
Una buena previsión para que las tablas dinámicas no preserven registros que ya eliminamos de una base es que al crear la tabla, en la configuración de tabla dinámica, en la pestaña de Datos, se seleccione la opción de ninguno, cuando se define el: “Número de elementos que desea conservar por campo”

Sirva este comentario para agradecer la enorme calidad de tu Blog, por lo que espero con gusto, como muchos, su actualización. Me encantaría que siguieras abordando el tema de las Cuadro de control (dashboard) y de cómo crear y administrar una base de datos. Gracias de nuevo

Jorge L. Dunkelman 21 noviembre, 2009 10:36  

Gracias por el comentario. Esa opción existe en Excel 2007, pero no en la versiones anteriores.
Me olvidé de señalar que la nota se refiere a las versiones clásicas de Excel (97-20003).
Tal vez publique una nota sobre las diferencias en tablas dinámicas entres Excel clásico y Excel 2007.

Anónimo,  22 noviembre, 2010 18:51  

Graciias Men Me SALVASTe La Viida

Anónimo,  08 junio, 2011 13:19  

Buenos dias
en que parte de excel 2007 "tiene un metodo incorporado para solucionar este problema"

trabajo a diario con tablas dinamicas en excel 2007 y tengo este problema.

gracias por anticipado.

Jorge L. Dunkelman 08 junio, 2011 18:01  

Opciones de Tabla Dinámica->Datos->"Mantener los elementos elliminados..."->"Numero de elementos que desea conservar por campos"

Jorge (El Chango),  18 noviembre, 2011 17:26  

Mil Gracias!!
Hoy acabo de solucionar algo que por tiempo me tenia dando vueltas.

Esta Web debe estar en mi lista de favoritos.

Anónimo,  13 abril, 2015 17:52  

puuuuu!!! que chivo muy bueno amigo gracias!!! me ayudo un monton

Anónimo,  11 junio, 2015 00:06  

Pasará el tiempo, y este blog seguirá siendo de los más útiles, sólo ver desde qué año vienen los comentarios. Gracias Jorge!!!

Anónimo,  08 julio, 2015 00:52  

Buenazo, me sirvió bastante. Gracias!!!

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP