Mantener siempre actualizada una Tabla Dinámica en Excel (2)

Hace más de cinco años, en este mismo blog, escribí un artículo donde explicaba la forma de "Mantener actualizada constantemente una tabla dinámica". En esa ocasión, el artículo estaba referido a que la tabla refleje constantemente los datos de la tabla origen, pero se asumía que el rango de datos no cambiaba de tamaño. Pero, ¿qué ocurre si el rango de datos de la tabla - origen de datos crece o decrece?. Esta es una pregunta que me han hecho muchas veces últimamente y, además, desde varios ámbitos (empresarial y académico). Así pues, aquí propongo un par de soluciones para este problema, pero para casos distintos.

CASO 1: la tabla origen de datos puede tener formato de Tabla de Excel.

Si la tabla donde están los datos de origen puede tener formato de Tabla de Excel y esto no causa ningún inconveniente, el problema ya no lo es, puesto que tan solo aplicando ese formato a la tabla-origen (y haciendo que nuestra tabla dinámica está basada en ella), la tabla dinámica siempre tendrá como origen de datos la totalidad de datos, aunque hayan crecido o disminuido.

Para aplicar un formato de Tabla de Excel a un rango de celdas donde hay datos, tenemos que asegurarnos de que no hay celdas combinadas y de que cada columna tiene un título de encabezado. Después, desde la ficha Inicio, hacemos clic en "Dar formato como tabla":

Ahora, veremos que aparece una galería de estilos donde tenemos que elegir el que más nos guste. Posteriormente, debemos confirmar los datos del cuadro de diálogo que aparece... y .... ya está.

Por último, sería recomendable dar un nombre a la nueva Tabla de Excel. Lo podemos hacer desde la ficha Diseño (la ficha contextual que aparece cuando tenemos seleccionada una celda de la nueva Tabla). Podemos ver a la izquierda un campo para editar el nombre de la Tabla de Excel.

Una vez hecho todo esto, debemos tener en cuenta que...

• Si ya teníamos la tabla dinámica construida y basada en el antiguo rango de datos, debemos decirle a Excel ahora que la tabla dinámica debe basarse en nuestra nueva Tabla de Excel. Para ello, debemos ir a la ficha Analizar (teniendo alguna celda de la tabla dinámica seleccionada), hacer clic en "Cambiar origen de datos..." y seleccionar la nueva tabla completamente.

 

• Si tenemos que construir "desde cero" nuestra tabla dinámica, tan solo tenemos que hacerlo normalmente, seleccionando como origen de datos nuestra Tabla de Excel.

CASO 2: la tabla origen de datos NO puede tener formato de Tabla de Excel.

Si nuestra tabla - origen de datos no puede tener formato de Excel (por cualquier motivo), la solución debe ser radicalmente distinta y debemos recurrir a las macros. Aunque... ¡tranquilos!, tan solo son unas líneas de código.

El proceso sería el siguiente:

• Hacemos clic con el botón derecho en la pestaña de la hoja donde está la tabla dinámica que debe estar actualizada constantemente. Acto seguido, se abrirá el editor de VBA con una ventana grande a la derecha. Ahí debemos escribir lo siguiente:

       Private Sub Worksheet_Activate()
          ActiveSheet.PivotTables("MiTD").ChangePivotCache _
          ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
          SourceData:=Sheets("MiHoja").Range("B8").CurrentRegion)
      End Sub

      Donde MiTD debe ser sustituido por el nombre de la tabla dinámcia (el nombre de una TD lo podemos ver si tenemos una celda de la misma seleccionada, hacemos clic en la ficha Analizar y miramos a la izquierda).

     Donde MiHoja debe ser sustituido por el nombre de la hoja donde está la tabla - origen de datos.

      Y donde B8 es un ejemplo, pero cada usuario debe escribir ahí la referencia de la primera celda de la tabla - origen de datos (por ejemplo, el primer título de los encabezados). Una condición a tener en cuenta es que la tabla - origen de datos no debe tener filas completas vacías.

•  Cerramos el editor de VBA, cerramos Excel y aceptamos Guardar los cambios.

Y ya lo tenemos.

La clave de este código la expresión "CurrentRegion", que es la que, de forma automática, detecta cuál es el rango de datos de la tabla, siempre que la referencia de ejemplo B8 sea una celda de la tabla. Por otro lado, comentar que el código se ha introducido en el evento WorkSheet_Activate, por lo tanto, la actualización del rango se realiza cada vez que seleccionamos la hoja de la tabla dinámica.


José Manuel Pomares Medrano