Tabla dinámica de varias hojas | ¿Cómo crear una tabla dinámica?

Tabla dinámica de Excel de varias hojas

Las tablas dinámicas de varias hojas son un concepto en el que deben agregarse dos o más tablas a una tabla y los campos se pueden seleccionar de acuerdo con el requisito desde un solo lugar.

En tablas diferentes de una palabra, se pueden agregar datos de diferentes hojas utilizando algunas técnicas y atajos.

¿Cómo crear una tabla dinámica a partir de varias hojas?

La implementación de tablas dinámicas desde varias hojas de trabajo tiene algunos procesos adicionales. Este proceso también se puede realizar de diferentes formas. Los siguientes son los diferentes métodos para crear una tabla dinámica a partir de varias hojas en Excel.

Puede descargar esta tabla dinámica de la plantilla de Excel de varias hojas aquí - Tabla dinámica de la plantilla de Excel de varias hojas

Proceso # 1

Para este proceso, necesitamos datos en varias hojas de trabajo. Por ejemplo: Hoja1, hoja2, hoja3, etc.

El no. de hojas debe ser dos o más de 2.

Paso 1: Haga clic en Alt + D, luego haga clic en P. Aparecerá el siguiente cuadro de diálogo.

Paso 2: En ese cuadro de diálogo, seleccione Varios rangos de consolidación y haga clic en SIGUIENTE.

Paso 3: En este paso, después de hacer clic en SIGUIENTE, irá al paso 2, y en ese, seleccione "Crearé los campos de la página" como en el cuadro de diálogo a continuación.

Paso 4: Después de seleccionar las opciones anteriores, haga clic en SIGUIENTE nuevamente. La ventana también se moverá al tercer paso. En este paso, se abrirá otro paso después de hacer clic en el botón SIGUIENTE como se muestra a continuación,

Paso 5: En eso, seleccione el rango de la tabla 1 y luego haga clic en el botón AGREGAR y luego seleccione el rango de otra tabla en otra hoja y luego haga clic en AGREGAR. Ahora las dos tablas se agregarán a una sola tabla.
Paso 6: Ahora seleccione el campo Página como 1 debajo de la opción Rangos. Luego, asigne los nombres a las dos hojas según la mejor comprensión, de modo que si hacemos clic en la tabla 1, el nombre de la tabla se mostrará en la tabla dinámica para una mejor comprensión.

Paso 7: Ahora hay una opción para seleccionar si la tabla dinámica se insertará en una nueva hoja de trabajo o en una hoja existente. De forma predeterminada, se seleccionará una nueva hoja de trabajo como se muestra en la figura.

Paso 8: Finalmente, haga clic en Finalizar. Ahora la tabla dinámica se creará en una nueva hoja de trabajo.

Paso 9: De forma predeterminada, se mostrará el valor de Count. Debemos cambiar el valor en los títulos de la suma. Para eso, vaya a cualquier celda que contenga un valor y haga clic derecho y luego seleccione la opción "Resumir valores por" y en
Paso 10: Después de esto, podemos eliminar las columnas que realmente necesitamos y no las necesitamos. Esto se puede hacer seleccionando las "Etiquetas de columna". Podemos comprobar las etiquetas de las columnas según sea necesario.
Paso 11: Ahora está la otra columna para Grandes Totales. En general, no necesitamos los totales generales de columnas. Por lo tanto, se puede eliminar haciendo "clic derecho en el valor y seleccione" Opciones de tabla dinámica "y aparecerá un cuadro de diálogo como se muestra a continuación.

Paso 12: Vaya a la pestaña "Totales y filtros" y desmarque la opción "Mostrar totales generales para filas". Si es necesario, puede conservarlo o desmarcar la opción correspondiente. Ahora haga clic en Aceptar.

Paso 13: Ahora los totales generales de las columnas solo estarán presentes para las columnas.

Paso 14: El paso final sería cambiar el nombre de la tabla dinámica. Puede ser definido por el usuario o relacionado con los datos de la tabla dinámica.

Este es el primer proceso para crear una tabla dinámica a partir de varias hojas en Excel. En esta tabla dinámica como en la tabla dinámica normal, solo los campos del lado derecho se pueden arrastrar y soltar según el requisito.

Proceso # 2

En este método, debería haber una fila común en ambas tablas. Esto actuará como clave principal para la primera tabla y clave externa para la segunda tabla.

Aquí usaremos la hoja 4, hoja 5 para crear una tabla dinámica a partir de varias hojas en Excel.

Analizaremos el segundo método con la ayuda del siguiente ejemplo.

Paso 1: En la hoja 3 y 4 seleccione la tabla, haga clic en CTRL + T, para seleccionar los datos completos y dibujar una tabla para los datos completos. Ahora se insertará una tabla en los datos. El nombre se mostrará para la tabla en la esquina izquierda. Este proceso se puede realizar para todas las tablas que contienen los datos.

Los filtros se agregarán por defecto, si no los necesitamos podemos desactivarlos haciendo clic en CTRL + MAYÚS + L acceso directo desde un teclado o simplemente ir a la pestaña "Datos" y hacer clic en la opción Filtros. Entonces los filtros se desactivarán.

Ahora para crear una tabla dinámica para los siguientes datos, estos son los pasos a seguir.

Paso 1: Haga clic en la pestaña Insertar y haga clic en Tablas dinámicas. Aparecerá un cuadro de diálogo y en él se le preguntará si la tabla dinámica debe crearse en una hoja nueva o en la misma hoja.

Es bueno usar una nueva opción de hoja en Excel.

Paso 2: Por último, marque la casilla "Agregar esta tabla al modelo de datos". Este es un paso importante en este proceso de creación de tabla dinámica.

Paso 3: La tabla dinámica se creará ahora en la nueva hoja de trabajo y en ese lado derecho tenemos todos los campos relacionados con la tabla dinámica.

Paso 4: Vaya a la pestaña "Analizar " -> Relaciones -> Nuevo.

Paso 5: La tabla es la tabla actual. La tabla relacionada es la tabla que se agregará a la tabla dinámica. La columna relacionada es la columna que es la misma en ambas tablas, esto es de la primera tabla, también se llama clave primaria. Una columna es la misma columna en la segunda columna que se llama clave externa.

Paso 6: Ahora haz clic en Aceptar.

Paso 7: Ahora podemos seleccionar los campos obligatorios según el requisito. Los campos se pueden seleccionar de todas las tablas en las tablas dinámicas. Para eso, primero le pedirá que cree una nueva tabla dinámica.

Paso 8: Haga clic en Sí y podrá seleccionar campos de todas las tablas para crear una tabla dinámica.

Proceso # 3

Lo primero y más importante de este método es asignar un nombre a todos los datos. Ilustraremos esto con un ejemplo. A continuación se muestra el ejemplo de este proceso.

Paso 1: cree una tabla para todos los datos con CTRL + T y elimine los filtros. Ahora vaya a la pestaña "Datos" -> haga clic en "De otras fuentes" -> De Microsoft Query -> Archivos de Excel.

Paso 2: Luego seleccione la hoja de trabajo en la que ha creado los datos.

Paso 3: En este paso, se mostrará otra ventana para seleccionar las tablas de todas las hojas del libro. Las tablas se pueden seleccionar según el requisito que se muestra a continuación. Hemos seleccionado las Fichas No 5 y 6.

Paso 4: Después de seleccionar las tablas de las hojas, haga clic en Siguiente, obtendrá este cuadro de diálogo y haga clic en Aceptar.

Este cuadro de diálogo dice que sin unirse a las tablas no se puede ejecutar la consulta y ahora unirse a las tablas.

Paso 5: Luego aparecerá una ventana y en ella estarán presentes todos los datos pertenecientes a todas las tablas. En esa ventana habrá dos partes, una es "Rango de nombres" y la otra es "Área de datos".

Paso 6: Ahora vaya al menú Archivo, seleccione "Devolver datos a Microsoft Excel"

Paso 7: Ahora obtendrá una ventana de "Importar datos".

Paso 8: En esta ventana, como queremos crear una tabla dinámica, seleccione la opción "Informe de tabla dinámica" y haga clic en Aceptar.

Paso 9: Ahora la tabla dinámica está lista y podemos crearla según el requisito.

Nota: La razón para insertar una tabla en los datos es que, si en el futuro se agregan datos en el último, la tabla dinámica se puede actualizar con nuevos datos automáticamente con solo actualizar la página.

Cosas para recordar

Debe haber dos o más tablas en un libro de trabajo para crear una tabla dinámica.
Si se agrega algún dato nuevo en cualquiera de las tablas después de crear una tabla dinámica, para reflejar los cambios, necesitamos actualizar la tabla dinámica manualmente.
Cada vez que creamos una tabla dinámica, debe crearse en una nueva hoja de trabajo para reflejar los cambios correctamente.
Cuando arrastramos y soltamos en Excel los atributos de cualquiera de las tablas en cualquiera de los 4 campos, el resumen del informe se generará en solo unos segundos.
Lo principal al crear una tabla dinámica es que ninguna celda, columna o fila debe dejarse en blanco si no hay ningún valor, o debe ser "0" o cualquier otro valor. De lo contrario, la tabla dinámica no se mostrará correctamente.
Sugerencia rápida sobre herramientas: La referencia rápida de la tabla dinámica se puede agregar a la barra de herramientas rápida mediante el siguiente proceso.

Haga clic en Personalizar barra de herramientas de acceso rápido, vaya a Más comandos -> Comandos populares -> Seleccionar tablas dinámicas -> haga clic en AÑADIR.

Ahora la opción de tabla dinámica se agregará a la barra de herramientas de acceso rápido para facilitar el acceso.