Cómo funciona realmente una tabla dinámica

¿Alguna vez te has preguntado qué hace que una tabla dinámica sea tan rápida cuando trasladas un campo del área de filas al área de columnas? ¿o por qué si eliminas la fuente de datos de la tabla dinámica, ésta sigue funcionando? ¿o por qué cuando modificas los datos de la fuente original, debes actualizar la tabla dinámica? La respuesta es la caché dinámica.

Al crear una tabla dinámica, Excel genera una copia de los datos fuente para trabajar con ella en vez de con los datos originales. La caché dinámica es un objeto que contiene una réplica de los datos originales.

Cuando haces cambios en la tabla dinámica, Excel no utiliza la fuente de datos sino la memoria de caché dinámica. Por esta razón es imprescindible actualizar la tabla dinámica siempre que introduces nuevos registros o campos.

El motivo por el que se crea este “paso intermedio” es para optimizar el funcionamiento de la tabla dinámica y que sea ultra rápida cuando arrastras elementos a las diferentes áreas. Cuando trabajas con miles o millones de filas, la caché dinámica se hace imprescindible.

En este artículo verás cómo usar la caché dinámica para mejorar el rendimiento de tus tablas dinámicas. Pero ¡ojo! Antes de continuar debo decirte que el contenido de este artículo no se aplica a las tablas de Power Pivot.

Ventajas e inconvenientes de la caché dinámica

El uso que hacen las tablas dinámicas de la caché dinámica permite:

• Acelerar el cambio de área de los campos.
• Acelerar la creación de campos y elementos dinámicos.
• Mejorar la creación de gráficos.
• Asegurar la consistencia de la tabla dinámica en caso de no poder acceder a los datos originales.

Pero también existen algunas desventajas: la principal es el aumento del tamaño del libro. Recuerda que Excel guarda una copia de la fuente de datos. Cuando trabajas con gran cantidad de datos, el tamaño puede llegar a ser un gran inconveniente.

Caché dinámica compartida

Desde la versión de Excel 2007, cuando creas una segunda tabla dinámica utilizando la misma fuente de datos que la primera, Excel comparte automáticamente la misma caché dinámica para ambas.

El uso de una misma caché dinámica en varias tablas también tiene algunas ventajas e inconvenientes: la principal ventaja es que se reduce el espacio del archivo en el disco. Pero lamentablemente tiene algunos inconvenientes importantes:

• Al actualizar una tabla dinámica, se actualizan todas las tablas vinculadas a la misma caché.
• Lo mismo ocurre cuando se agrupan los campos de una tabla: la agrupación se hace en todas las tablas. Por ejemplo, si agrupas las fechas de un campo en meses, este cambio se reflejará en todas las tablas.
• Un campo calculado creado para una tabla, se mostrará en todas las que compartan caché.

Si estas desventajas son importantes para ti, puedes forzar a Excel a que cree una caché para cada tabla, aunque utilices la misma fuente de datos.

Separar cachés dinámicas de varias tablas que utilizan la misma fuente de datos

Si quieres usar varias tablas vinculadas al mismo origen de datos de forma totalmente independiente, te propongo dos métodos para separar las cachés dinámicas:

Usar diferentes nombres de tabla

1. Haz clic en cualquier parte de la fuente de datos y haz clic en la ficha Insertar > Tabla (también puedes utilizar la combinación de teclas Ctrl + T).

2. En el cuadro de diálogo Insertar tabla, haz clic en Aceptar. Esto creará una tabla llamada Tabla1.

3. A continuación, crea la tabla dinámica de la forma habitual: Teniendo seleccionada cualquier celda de la tabla que acabas de crear, haz clic en la ficha Insertar > Tabla dinámica. Se mostrará el cuadro Crear tabla dinámica. Fíjate en que el nombre del origen de datos se llama Tabla1. Presiona Aceptar para crear la tabla dinámica.

4. Una vez creado el esqueleto de la tabla dinámica, haz clic en la Tabla1 (la fuente de datos) y conviértela en rango (usando la ficha contextual Diseño de tabla). Acepta el mensaje de advertencia para convertirlo en rango.

5. Por último repite los pasos anteriores (del 1 al 3). Cuando vayas a crear la tabla a partir del rango, asegúrate de que le das un nombre diferente (por ejemplo Tabla2).

Aunque las tablas Tabla1 y Tabla2 se refieren a la misma fuente de datos, este método te asegura que se generarán dos cachés dinámicas independientes para cada tabla.

Usar el asistente de tablas dinámicas (obsoleto)

Otro método parra crear cachés dinámicas independientes es utilizar el antiguo cuadro de diálogo Asistente para tablas y gráficos dinámicos. Actualmente no se puede acceder a él desde la cinta de opciones, así que tienes que usar el atajo de teclado Alt + T + B (si tu configuración regional es la anglosajona el atajo es Alt + D + P).

Sigue los pasos del asistente. Al seleccionar los datos de origen y presionar Siguiente, aparecerá un cuadro de advertencia que básicamente nos pregunta si quieres crear una caché dinámica compartida o individual.

Para crear cachés independientes, presiona en No.

En el último paso, el asistente te preguntará dónde quieres ubicar la nueva tabla. Selecciona la opción que más te interese:

Conocer cuántas cachés dinámicas tiene un libro de Excel

Quizá quieras conocer el número de cachés dinámicas que tiene un libro para optimizar el funcionamiento y el tamaño del archivo.
No existe una forma visual de hacerlo. No hay un comando en la cinta de opciones para ello. Debes recurrir entonces a VBA (pero no te asustes, no es complicado).

Abre el editor de VBA presionando Alt + F11 o desde la cinta de opciones, en la ficha Programador (Desarrollador) > Visual Basic.
En el editor, haz clic en el menú Ver y selecciona la opción Ventana Inmediato (o presiona Ctrl + G).

En la ventana Inmediato introduce la siguiente instrucción y presiona Intro:

? ActiveWorkbook.PivotCaches.Count

Aparecerá el número de cachés que contiene el libro.

Mejorar el rendimiento con tablas dinámicas

Ahora que ya conoces cómo funcionan las cachés dinámicas, puedes optimizar el funcionamiento de las tablas dinámicas sin problema. Estos son mis dos trucos:

Eliminar la fuente de datos

Puedes utilizar únicamente la caché dinámica eliminando la fuente original de los datos. Excel seguirá trabajando con los datos guardados en la caché dinámica. Al eliminar la fuente de datos, el tamaño del archivo se reducirá.

En caso de que más tarde desees recuperar los datos originales, simplemente haz clic en la intersección de los totales generales de filas y columnas para crear la hoja de detalle con todos los datos utilizados en la tabla dinámica.

Eliminar la caché dinámica al salir del libro

No es necesario que Excel guarde dos veces la misma información (aunque por motivos de seguridad, sí es recomendable). Si estás seguro de que la fuente de datos siempre va a estar disponible, puedes hacer que Excel elimine la caché dinámica al cerrar el libro. El resultado será un archivo con menos peso.

Para que Excel no guarde los datos en la caché dinámica:

1. Haz clic en la ficha Análisis de tabla dinámica y a continuación selecciona Tabla dinámica y Opciones.

2. En el cuadro de diálogo Opciones de tabla dinámica, haz clic en la pestaña Datos.
3. Desmarca la opción Guardar datos de origen con el archivo.
4. Importante: Marca la opción Actualizar al abrir el archivo. En caso de no hacerlo no podrás utilizar las funcionalidades de las tablas dinámicas.

Una vez hecho esto, Excel vaciará la caché dinámica al salir del libro. Cuando vuelvas a abrirlo, se creará una nueva copia de la caché dinámica.
Aunque esto reduce significativamente el tamaño del archivo (depende también del número de registros), puede tardar algo más de tiempo en abrirse, ya que debe generar de nuevo la caché dinámica.

Compartir caché dinámica para un mejor rendimiento.

Si por accidente (o no) te encuentras en la situación de tener cachés duplicadas (del mismo origen de datos), puedes eliminar el duplicado y compartir la caché con las demás tablas.

La forma más rápida de hacer esto es eliminar una de las tablas y luego copiarla de la otra.
Para eliminar una de las tablas, selecciónala y haz clic en la ficha contextual Análisis de tabla dinámica > Acciones > Borrar > Borrar todo.

A continuación selecciona completamente la tabla dinámica que no has borrado y copiala en otra ubicación. Procura que sea en otra hoja para no tener problemas de superposición en caso de modificar la disposición de los campos.

Resumen

¿Conocías la existencia de la caché dinámica y su utilidad a la hora de trabajar con tablas dinámicas? En este artículo hemos visto qué es y cómo funciona. Además, has aprendido algunos trucos para acelerar tus libros y evitar tamaños de archivos innecesarios.

Si deseas más información, puedes echar un vistazo a la siguiente página de Microsoft (sección Usar otra tabla dinámica como un origen de datos).

¿Te ha gustado? Si es así, no te quedes con el conocimiento sólo para ti. Compártelo en tus redes sociales para que otras personas también puedan aprender y hacer que sus tablas dinámicas funcionen de lujo.