Datos de origen de tabla dinámica
Cómo localizar y cambiar los datos de origen de una tabla dinámica de Excel. Para los datos de origen que faltan, pasos para intentar volver a crearlos. Cambie las opciones para guardar los datos de origen con un archivo de tabla dinámica.
NOTA: Para macros que ayuden a administrar los datos de origen, vaya a la página de macros de datos de origen de la tabla dinámica. Para obtener una macro para desvincular los datos de origen, vaya a la página Desvivar macro de datos de Excel
Localizar y corregir datos de origen
En este video, verá cómo ubicar la tabla dinámica fuente de datos, luego verifique la fuente de datos para asegurarse de que incluye todas las filas y columnas que necesita. Si es necesario, ajuste la fuente de datos para incluir nuevas filas o columnas.
NOTA: Para un largo solución de término, use una tabla de Excel con nombre, como fuente de datos. Se ajustará automáticamente, si se agregan nuevas filas.
Las instrucciones escritas están debajo del video, y puede descargar el archivo de muestra para seguir con el video.
Localice los datos de origen
Después de crear una tabla dinámica, es posible que desee agregar nuevos registros en los datos de origen o cambiar los registros existentes. libro de trabajo grande, puede ser difícil ubicar la fuente exacta de la tabla dinámica, si hay varias tablas o listas.
NOTA: Si ha comprado mi complemento Pivot Power Premium, haga clic en Tabla dinámica Información, luego haga clic en Vaya a Datos de origen.
Siga estos pasos para encontrar los datos de origen de una tabla dinámica:
- Seleccione cualquier celda de la tabla dinámica.
- En la cinta, en la pestaña Herramientas de tabla dinámica, haga clic en la pestaña Analizar (en Excel 2010, haga clic en la pestaña Opciones).
- En el grupo Datos, haga clic en la sección superior del comando Cambiar fuente de datos.
Se abre el cuadro de diálogo Cambiar fuente de datos de tabla dinámica y puede ver la tabla o rango fuente en el cuadro Tabla / Rango. Esto puede ser una referencia a una hoja y rango de celdas, como
- Pedidos! $ A $ 1: $ H $ 9
o el nombre de una tabla, como
- Sales_East
Detrás del cuadro de diálogo, puede ver el rango de origen en la hoja de trabajo, rodeado por un borde en movimiento.
En la siguiente captura de pantalla, el rango de datos de origen termina en la fila 9 y se ha agregado un nuevo registro en la fila 10. Ese registro no aparecerá en la tabla dinámica, a menos que se ajuste la Fuente de datos.
Ajustar el rango de datos de origen
Si los datos de origen de una tabla dinámica son una referencia estática a una hoja y un rango específicos, no se ajusta automáticamente cuando se agregan nuevos datos. En la captura de pantalla anterior, el rango de la fuente de datos se refiere a Pedidos! $ A $ 1: $ H $ 9, y se puede ajustar manualmente para incluir la fila 10.
Para ajustar manualmente el rango de la fuente estática:
Puede ajustar el rango de la fuente cuando sea necesario, para que incluya cualquier dato nuevo, pero una mejor solución es crear una fuente dinámica y basar la tabla dinámica en eso. Las instrucciones están a continuación.
Cambiar los datos de origen
Ocasionalmente, es posible que necesite cambiar una tabla dinámica, por lo que utiliza una fuente de datos diferente. En este ejemplo, se ha creado una nueva tabla, con solo datos para el Este r egion. La tabla dinámica se cambiará para usar esa fuente, y luego se puede enviar a un administrador en la región Este, sin revelar los datos de la región Oeste.
NOTA: Después de cambiar a una tabla fuente diferente , es posible que deba cambiar el nombre de algunos campos o agregar campos diferentes a la tabla dinámica.
Para cambiar los datos de origen de una tabla dinámica de Excel, siga estos pasos:
- Seleccione cualquier celda de la tabla dinámica.
- En la cinta de opciones, en la pestaña Herramientas de tabla dinámica, haga clic en la pestaña Analizar (en Excel 2010, haga clic en la pestaña Opciones).
- En Datos grupo, haga clic en la sección superior del comando Cambiar fuente de datos.
- Se abre el cuadro de diálogo Cambiar fuente de datos de tabla dinámica y puede ver la tabla o rango de origen en el cuadro Tabla / Rango. En esta captura de pantalla a continuación, la fuente de datos actual es la tabla Sales_Data, que contiene datos de ambas regiones ..
- Elimine la fuente de datos existente. y escriba un nuevo nombre de tabla, o seleccione una nueva fuente de datos e rango. La Tabla / Rango se cambia a Sales_East, donde solo se enumeran los datos de East.
- Haga clic en Aceptar.
Fuente dinámica para tabla dinámica
En lugar de usar un rango estático como fuente de datos de la tabla dinámica, una mejor solución es crear un rango dinámico, que se ajustará en tamaño automáticamente.
Crear fuente dinámica – Tabla de Excel
Fuente dinámica: rango con nombre
Fuente dinámica: matriz dinámica
Utilice una fuente dinámica
Fuente dinámica: tabla de Excel
En lugar de utilizar un rango estático como fuente de datos de la tabla dinámica, una mejor solución es crear un rango dinámico, basado en una tabla de Excel con nombre. Hay instrucciones escritas aquí, o mira el video a continuación.
Tome nota del nombre de la tabla, para que pueda usarlo como fuente de la tabla dinámica
Fuente dinámica – Rango con nombre
Si no puede usar una tabla de Excel como datos de origen, usted crea un rango con nombre dinámico, basado en una fórmula INDICE o OFFSET. Aquí hay instrucciones escritas, o vea el video a continuación.
El video muestra cómo crear un rango dinámico con la función OFFSET, en Excel 2007, y los pasos son similares en versiones posteriores.
NOTA: Recuerde el nombre del rango, para que pueda usarlo como fuente de la tabla dinámica
Dynamic Source – Dynamic Array
Si tiene una versión de Excel que admita las nuevas funciones, como SORT o UNIQUE, puede usar esas funciones para crear arreglos dinámicos. Esta técnica solo usa fórmulas, no macros .
En la sección Descargar, obtenga el archivo de muestra de datos de origen filtrados. Muestra cómo configurar un rango con nombre con solo las filas visibles de una tabla de Excel con nombre.
Aquí está el filtro d, en una hoja diferente, con solo las 2 repeticiones y 3 categorías de las filas visibles.
Luego, puede crear una tabla dinámica basada solo en esos datos filtrados.
Usar fuente dinámica para tabla dinámica
Después de crear una fuente dinámica que contiene la tabla dinámica datos, siga estos pasos para usar los datos de origen:
- Seleccione cualquier celda de la tabla dinámica.
- En la cinta, en la pestaña Herramientas de tabla dinámica, haga clic en Analizar pestaña (en Excel 2010, haga clic en la pestaña Opciones).
- En el grupo Datos, haga clic en la sección superior del comando Cambiar fuente de datos.
- En el cuadro de diálogo Cambiar fuente de datos de tabla dinámica, desactive el cuadro Tabla / Rango.
- En el cuadro Tabla / Rango, escriba el nombre de la tabla o rango con nombre que creó. En este ejemplo, la fuente dinámica es una tabla con nombre: tblOrders.
- Haga clic en Aceptar
Guardar datos de origen con una tabla dinámica
Cuando crea una tabla dinámica en su libro de trabajo, los registros de datos de origen se guardan en un área de memoria especial: una caché dinámica. Luego, cuando cierra el archivo, Excel puede guardar los datos de origen en esta caché dinámica o borrar esa memoria.
Hay ventajas y desventajas en ambas opciones:
Si guarda los datos de origen:
- El archivo será más grande
- El archivo puede abrirse más rápidamente
Si no guarda los datos de origen:
- El relleno será más pequeño
- El archivo puede abrirse lentamente mientras se reconstruye la caché dinámica
- La tabla dinámica debe actualizarse después de abrir el archivo
Cómo cambiar la configuración
Para activar Guardar la configuración de Datos de origen está activada o desactivada:
- Haga clic con el botón derecho en una celda de la tabla dinámica y seleccione Opciones de tabla dinámica.
- En la pestaña Datos, en la sección Datos de tabla dinámica , agregue o elimine la marca de verificación de Guardar datos de origen con archivo
- Haga clic en Aceptar.
Actualizar datos al abrir
Si elige activar Si desactiva la opción Guardar datos de origen con archivo, debe activar la opción Actualizar datos al abrir el archivo. Esa configuración está justo debajo de la casilla de verificación Guardar datos de origen.
De lo contrario, verá un mensaje cuando intente filtrar los datos o realizar cualquier otro cambio de diseño.
- «El informe de tabla dinámica se guardó sin los datos subyacentes. Utilice el comando Refresh Data para actualizar el informe. «
Si ve ese mensaje, haga clic en Aceptar, luego actualice manualmente la tabla dinámica.
Y para evitar el molesto mensaje, active la opción Actualizar datos al abrir el archivo.
Volver a crear datos de origen de la tabla dinámica
Si elimina accidentalmente la hoja de trabajo que tiene los datos de origen para su tabla dinámica, o si recibió un archivo sin los datos de la tabla dinámica, es posible que pueda usar la función Mostrar detalles de la tabla dinámica para recrearlo.
NOTA: Esto no funcionará para todas las tablas dinámicas, pero es vale la pena intentarlo para recuperar los datos de origen.
Para intentar volver a crear los datos de origen para una tabla dinámica, siga estos pasos para utilizar la función Mostrar detalles:
- Asegúrese de que ninguno de los elementos de los campos de la tabla dinámica esté oculto; borre todos los filtros y segmentaciones que se han aplicado.
- No es necesario que la tabla dinámica contenga todos los campos; solo asegúrese de que no haya hay al menos un campo en el Val área de ues.
- Muestre los totales generales de filas y columnas. Si los totales no están visibles, seleccione una celda en la tabla dinámica y, en la cinta de opciones, en Herramientas de tabla dinámica, haga clic en la pestaña Analizar. En el grupo Diseño, haga clic en Totales generales y luego en Activado para Filas y columnas.
- Haga doble clic en la celda de total general en la parte inferior derecha de la tabla dinámica. Esto debería crear una nueva hoja con los registros relacionados de los datos de origen originales.
Corrija los datos extraídos Datos
Si recupera los datos de origen, es posible que deba realizar algunos cambios adicionales para restaurarlos a su estado original.
- Cambie el nombre de la tabla, utilizando el nombre de la fuente de datos original, si se conoce.
- Si la fuente de datos original contenía fórmulas, tendrá que volver a crearlas, porque la función Mostrar detalles exporta solo los datos.
- Las columnas en los datos extraídos estarán en el mismo orden en que estaban en los datos de origen originales.
- Los datos extraídos serán formateado con el formato de tabla predeterminado del libro. Puede aplicar un formato de tabla diferente o aplicar su propio formato.
- Nota: Si realizó cambios en los datos de origen y no actualizó la tabla dinámica, esos cambios no estarán en los datos extraídos.
Conéctese a los datos extraídos
Si cambia el nombre de la tabla que se creó durante el proceso Mostrar detalles y usa el mismo nombre que la tabla que originalmente contenía la fuente datos, la tabla dinámica podría conectarse automáticamente a los nuevos datos de origen.
De lo contrario, puede conectarse a los datos de origen recreados; siga los pasos de la sección anterior: Use Dynamic Source For Pivot Table – use el nombre de la tabla que le dio a la nueva tabla.
Descargue el archivo de muestra
Ejemplo de fuente dinámica: para seguir los tutoriales, puede descargar el archivo DateAmt.zip. El archivo comprimido está en formato xlsx y no contiene macros.
Ejemplo de datos de origen filtrados: este ejemplo es para versiones de Excel que tienen las nuevas funciones, como SORT y UNIQUE. Dow ncargue este archivo de muestra para ver que se crea una tabla dinámica a partir de las filas visibles únicamente, en una tabla de Excel filtrada. El archivo comprimido está en formato xlsx y no contiene macros.
Herramientas de tabla dinámica
Para ahorrar tiempo al crear, formatear y modificar sus tablas dinámicas, utilice las herramientas de mi tabla dinámica Complemento Power Premium. Con solo unos pocos clics, puede:
- copiar el formato de una tabla dinámica y aplicarlo a otra tabla dinámica.
- cambie todos los valores de Count a Sum
- elimine la «Sum of» de todos los encabezados
¡y mucho más!