Las tablas dinámicas son excelentes para ciertos tipos de análisis de datos. Dado que las tablas dinámicas procesan bastante los números, una de las técnicas que utiliza Excel para procesarlos más rápido es crear un «conjunto de datos intermedio»

trabajar con. Este conjunto de datos intermedio, de forma predeterminada, se almacena con la hoja de trabajo, por lo que las tablas dinámicas pueden aumentar el tamaño de sus libros de trabajo, a veces de manera espectacular.

Si su libro contiene varias tablas dinámicas, todas basadas en una única fuente de datos, Excel puede crear un conjunto de datos intermedio para cada tabla dinámica, en lugar de utilizar un conjunto de datos intermedio. Esto, por supuesto, podría aumentar el tamaño de su libro de trabajo muy rápidamente.

Puede controlar cómo Excel crea el conjunto de datos intermedio modificando las opciones que usa cuando crea la tabla dinámica.

  1. Cree su tabla dinámica como lo haría normalmente.

  2. Muestre la pestaña Opciones (Excel 2007 y Excel 2010) o la pestaña Analizar (Excel 2013) o la pestaña Analizar tabla dinámica (versiones posteriores de Excel) de la cinta. (Esta pestaña es visible solo cuando selecciona una celda en la tabla dinámica).

  3. En el extremo izquierdo de la cinta, haga clic en la herramienta Opciones en el grupo Tabla dinámica. Excel muestra el cuadro de diálogo Opciones de tabla dinámica.

  4. Asegúrese de que se muestre la pestaña Datos. (Ver figura 1)

  5. Desactive la casilla de verificación Guardar datos de origen con archivo.

  6. Elija la casilla de verificación Actualizar al abrir.

  7. Haga clic en Aceptar para cerrar el cuadro de diálogo Opciones de tabla dinámica.

No necesita seleccionar la casilla de verificación Actualizar al abrir (paso 6) si no lo desea, pero si no lo hace, deberá recordar actualizar manualmente la tabla dinámica cada vez que abra el libro.

Si ya tiene bastantes tablas dinámicas en su libro de trabajo y no desea volver a realizar el proceso de creación, puede usar una macro para recorrer las tablas dinámicas y modificar el índice de almacenamiento en caché y desactivar el guardado de la datos intermedios al disco. La siguiente macro logrará estas tareas:

Sub PTReduceSize()

Dim wks As Worksheet     Dim PT As PivotTable

For Each wks In ActiveWorkbook.Worksheets         For Each PT In wks.PivotTables             PT.RefreshTable             PT.CacheIndex = 1             PT.SaveData = False         Next     Next End Sub

Una vez que se ejecuta la macro (no tardará mucho), debe guardar su libro de trabajo usando la opción Guardar como. Esto escribirá un nuevo archivo de libro de trabajo y podrá comparar cuánto redujo este cambio el tamaño de su libro de trabajo. Sin embargo, recuerde que, dado que los datos intermedios no se guardan en el disco, la actualización de las tablas dinámicas tarda más cuando abre el libro por primera vez.

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (8669) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365. Puede encontrar una versión de este consejo para la interfaz de menú anterior de Excel aquí:

link: / excel-Reducing_File_Sizes_for_Workbooks_with_PivotTables [Reducción del tamaño de archivo para libros de trabajo con tablas dinámicas].