✅ Caché dinámico en Excel: qué es y cómo usarlo mejor

Tabla de contenido

¿Qué es la caché dinámica?
Efectos secundarios de la caché dinámica
Compartiendo la caché dinámica
Creación de caché dinámica duplicada (con la misma fuente de datos)
Cuente el número de cachés dinámicos
Mejora del rendimiento al trabajar con tablas dinámicas

Si trabaja con tablas dinámicas de Excel, la caché dinámica es algo que definitivamente debe conocer.

¿Qué es la caché dinámica?

La caché dinámica es algo que se genera automáticamente cuando crea una tabla dinámica.

Es un objeto que contiene una réplica de la fuente de datos. Si bien no puede verlo, es parte del libro de trabajo y está conectado a la tabla dinámica. Cuando realiza cambios en la tabla dinámica, no utiliza la fuente de datos, sino que utiliza la caché dinámica.

La razón por la que se genera una caché dinámica es para optimizar el funcionamiento de la tabla dinámica. Incluso cuando tiene miles de filas de datos, una tabla dinámica es muy rápida para resumirlos. Puede arrastrar y soltar elementos en los cuadros de filas / columnas / valores / filtros y actualizará instantáneamente los resultados.

Pivot Cache permite este rápido funcionamiento de una tabla dinámica.

Si bien cree que está directamente vinculado a los datos de origen, en realidad, accede a la caché dinámica (y no a los datos de origen) cuando realiza cambios en la tabla dinámica.

Esta es también la razón por la que necesita actualizar la tabla dinámica para reflejar cualquier cambio realizado en el conjunto de datos.

Efectos secundarios de la caché dinámica

Una desventaja de la caché dinámica es que aumenta el tamaño de su libro de trabajo.

Dado que es una réplica de los datos de origen, cuando crea una tabla dinámica, se almacena una copia de esos datos en la caché dinámica.

Cuando usa grandes conjuntos de datos para crear una tabla dinámica, el tamaño del archivo del libro aumenta significativamente.

Compartiendo la caché dinámica

Desde Excel 2007 en adelante, si ya tiene una tabla dinámica y crea una tabla dinámica adicional utilizando los mismos datos de origen, Excel comparte automáticamente la caché dinámica (lo que significa que ambas tablas dinámicas usan la misma caché dinámica). Esto es útil ya que evita la duplicación de la caché dinámica y, a su vez, da como resultado un menor uso de memoria y un tamaño de archivo reducido.

Limitaciones de la caché dinámica compartida

Si bien una caché dinámica compartida mejora el funcionamiento de la tabla dinámica y el uso de la memoria, adolece de las siguientes limitaciones:

Cuando actualiza una tabla dinámica, todas las tablas dinámicas vinculadas a la misma caché se actualizan.
Cuando agrupa campos en una de las tablas dinámicas, se aplica a todas las tablas dinámicas utilizando la misma caché dinámica. Por ejemplo, si agrupa las fechas por meses, este cambio se reflejará en todas las tablas dinámicas.
Cuando inserta un campo / elemento calculado en una de las tablas dinámicas, aparece en todas las tablas dinámicas que comparten la caché dinámica.

La forma de evitar estas limitaciones es obligar a Excel a crear una caché dinámica separada para diferentes tablas dinámicas (mientras se usa la misma fuente de datos).

Nota: Si está utilizando diferentes fuentes de datos para diferentes tablas dinámicas, Excel generará automáticamente cachés dinámicos separados para él.

Creación de caché dinámica duplicada (con la misma fuente de datos)

Aquí hay 3 formas de crear una caché dinámica duplicada mientras se crean tablas dinámicas a partir de la misma fuente de datos:

# 1 Usando diferentes nombres de tablas

Haga clic en cualquier lugar de la fuente de datos y vaya a Insertar -> Tabla (o puede usar el atajo de teclado - Control + T).
En el cuadro de diálogo Crear tabla, haga clic en Aceptar. Creará una tabla con el nombre Table1.
Con cualquier celda seleccionada en la tabla, vaya a Insertar -> Tabla dinámica.
En el cuadro de diálogo Crear tabla dinámica, observará que en el campo Tabla / Rango tiene el nombre de la tabla. Haga clic en Aceptar.
- Esto creará la primera tabla dinámica.
Vaya a la fuente de datos (tabla), seleccione cualquier celda y vaya a Diseño de herramientas de tabla -> Herramientas -> Convertir a rango. Aparecerá un mensaje que le preguntará si desea convertir la tabla a rango normal. Haga clic en Sí. Esto convertirá la tabla en datos tabulares regulares.

Ahora repita los pasos anteriores y simplemente cambie el Nombre de la tabla (de Table1 a Table2 o lo que desee). Puede cambiarlo ingresando el nombre en el campo debajo de Nombre de tabla en la pestaña Diseño de herramientas de tabla.

Aunque ambas tablas (Tabla1 y Tabla2) hacen referencia a la misma fuente de datos, este método garantiza que se generen dos cachés dinámicos independientes para cada tabla.

# 2 usando el antiguo asistente de tabla dinámica

Siga estos pasos cuando desee crear una tabla dinámica adicional con una caché dinámica separada mientras usa la misma fuente de datos.

Seleccione cualquier celda de los datos y presione ALT + D + P.
- Esto abrirá el Asistente para tablas dinámicas y gráficos dinámicos.
En el paso 1 de 3, haga clic en Siguiente.
En el paso 2 de 3, asegúrese de que el rango de datos sea correcto y haga clic en Siguiente.
Excel muestra un mensaje que básicamente dice que haga clic en Sí para crear una caché dinámica compartida y No para crear una caché dinámica separada.
Haga clic en No.
En el Paso 3 del Asistente, seleccione si desea la tabla dinámica en una nueva hoja de trabajo o en la misma hoja de trabajo y luego haga clic en Finalizar.

Nota: asegúrese de que los datos no sean una tabla de Excel.

Cuente el número de cachés dinámicos

Es posible que desee contar el número de cachés dinámicos solo para evitar múltiples cachés dinámicos de la misma fuente de datos.

Aquí hay una forma rápida de contarlo:

Presione ALT + F11 para abrir VB Editor (o vaya a la pestaña Desarrollador -> Visual Basic).
En el menú del editor de Visual Basic, haga clic en Ver y seleccione Ventana Inmediato (o presione Control + G). Esto hará que la Ventana Inmediata sea visible.
En la ventana Inmediato, pegue el siguiente código y presione Entrar:
```
? ActiveWorkbook.PivotCaches.Count
```

Instantáneamente mostrará la cantidad de cachés dinámicos en el libro de trabajo.

Mejora del rendimiento al trabajar con tablas dinámicas

Hay un par de cosas que puede hacer para mejorar el rendimiento de los libros de trabajo (tamaño de archivo y uso de memoria) mientras trabaja con tablas dinámicas:

# 1 Eliminar los datos de origen

Puede eliminar los datos de origen y usar solo la caché dinámica. Aún podrá hacer todo con el caché dinámico, ya que contiene una instantánea de los datos originales. Pero dado que eliminó los datos de origen, el tamaño del archivo de su libro de trabajo se reduciría.

En caso de que desee recuperar los datos de origen, simplemente haga doble clic en la intersección de los totales generales para esa tabla dinámica. Creará una nueva hoja de trabajo y mostrará todos los datos utilizados para crear esa tabla dinámica.

# 2 No guarde los datos en la caché dinámica

Cuando guarda un archivo con una tabla dinámica y datos de origen, también guarda la caché dinámica que tiene una copia de los datos de origen. Esto significa que está guardando los datos de origen en dos lugares: en la hoja de trabajo que tiene los datos y en la caché dinámica.

Existe una opción para no guardar los datos en la caché y cerrarla. Esto conducirá a un tamaño de archivo más bajo.

Para hacer esto:

Seleccione cualquier celda de la tabla dinámica.
Vaya a Analizar -> Tabla dinámica -> Opciones.
En el cuadro de diálogo Opciones de tabla dinámica, vaya a la pestaña Datos.
Desmarque la opción - Guardar datos de origen con archivo.
Marque la opción - Actualizar datos al abrir el archivo.
- Si no marca esta opción, cuando abra el Libro de Excel, no actualizará los datos y no podrá utilizar las funcionalidades de la tabla dinámica. Para que funcione, deberá actualizar manualmente la tabla dinámica.

Cuando haga esto, Excel no guardará los datos en la caché dinámica, pero los actualizará cuando abra el libro de Excel la próxima vez. Sus datos pueden estar en el mismo libro de trabajo, en otro libro de trabajo o en una base de datos externa. Cuando abre el archivo, actualiza los datos y se vuelve a crear la caché dinámica.

Si bien esto puede llevar a un tamaño de archivo más bajo, puede llevar un poco más de tiempo abrir el archivo (ya que Excel recrea la caché).

Ver también: Guardar datos de origen con tabla dinámica.

Nota: Si usa esta opción, asegúrese de tener la fuente de datos intacta. Si elimina los datos de origen (del libro de trabajo o de cualquier origen de datos externo), no podrá volver a crear la caché dinámica.

# 3 Compartiendo la caché dinámica para un mejor rendimiento

Si por accidente (o intencionalmente) termina en una situación en la que tiene un caché dinámico duplicado y desea eliminar el duplicado y compartir el caché dinámico, estos son los pasos para hacerlo:

Elimine una de las tablas dinámicas para las que desea eliminar la caché. Para hacer esto, seleccione la tabla dinámica y vaya a Inicio -> Borrar -> Borrar todo.
Ahora simplemente copie la tabla dinámica que desea duplicar y péguela (ya sea en la misma hoja de trabajo o en una hoja de trabajo separada).
- Se recomienda pegarlo en hojas de trabajo separadas para que no se superponga con la otra tabla dinámica cuando la expanda. Aunque, a veces lo copio uno al lado del otro para comparar diferentes puntos de vista. Esta copia de pegar la tabla dinámica asegura que la caché dinámica sea compartida.