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
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
¿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
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
ForEach ptC In ActiveWorkbook.PivotCaches ptC.MissingItemsLimit = xlMissingItemsNone ptC.Refresh Next ptC
EndSub
Después de correr esta macro, los valores borrados de la base de datos ya no aparecerán en las tablas dinámicas.
Vimos también que el control de la barra de Formularios tiene un serio inconveniente: no podemos establecer los parámetros dinámicamente. En nuestro ejemplo vimos que al agregar líneas a la base de datos tenemos que modificar manualmente la definición de valor máximo del control.
Para crear un modelo que se adapte automáticamente a los cambios en la base de datos tendremos que usar otro tipo de controles, los controles ActiveX.
Empezamos por reemplazar la barra de desplazamiento que pusimos ayer por el mismo control pero de la barra de herramienta Cuadro de Controles (ActiveX). Para eso activamos la barra (Herramientas-Personalizar-Barra de Herramientas o mejor haciendo un clic con el botón derecho del mouse sobre el área de la barras de herramientas)
Dibujamos el control en la posición deseada (tip: mantenemos apretada la tecla ALT para que los bordes del control encuadren con los bordes de las celdas).
Al dibujar el control Excel activa automáticamente el modo de Diseño (veremos que el icono con la escuadra y la regla aparece “apretado”). Estando en modo Diseño apretamos el icono Propiedades
Establecemos estas propiedades:
LinkedCell = inicio (el rango control!C3 definido con ese nombre)
Min = 0
SmallChange = 1 (esto siginifica que cada vez que apretamos la flecha superior o inferior movemos una fila)
LargeChange = 12 (esto hace que si apretamos en algún punto de la barra se muevan 12 filas)
Max = por defecto aparece 32767. Este número lo manejaremos con una macro para evitar tener que editarlo con cada cambio en la base de datos.
Con el control seleccionado apretamos el icono “ver código”
Esta acción abre un módulo de VBa en la hoja pertinente (la hoja Informe en nuestro caso) .
Antes de poder usar esta macro para controlar el valor de la propiedad Max tenemos que hacer algunos agregados a nuestro modelo:
# definimos el nombre Periodos como rango dinámico con esta fórmula
=DESREF('base de datos'!$B$3;0;0;CONTARA('base de datos'!$B:$B)-1;1)
Esto nos permitirá saber en todo momento cuantas líneas de datos hay en nuestra tabla
# en la hoja Control definimos tres celdas con nombres que contendrán las siguientes fórmulas:
B3 (control_meses) : =CONTARA(periodos)
C3 (inicio): 0 o en blanco; su valor es establecido por el control (LinkedCell)
D3(max_periodo): =control_meses-12
Ahora podemos ver que el valor Max está determinado por la cantidad de períodos en la base de datos menos 12 (la cantidad de líneas que aparecen en el dashboard).
La rutina ScrollBar1_Change es una macro (evento) que es disparada cada vez que accionamos la barra de desplazamiento.
Nuestro modelo se adapta ahora automáticamente a medida que vamos agregando o quitando datos en la base de datos.
