Crea una tabla dinámica a partir de diferentes hojas

Con una tabla dinámica, resumes tus datos en segundos. Puede crear informes, analizar datos y compartirlos con otros fácilmente.

Esa es la belleza de las tablas dinámicas.

Pero aquí hay un giro:

Normalmente, cuando crea una tabla dinámica, puede seleccionar datos de origen de una sola tabla en una hoja de trabajo, porque Excel no le permite hacer referencia a diferentes hojas de trabajo.

Pero a veces sucede que necesitamos utilizar datos de origen de varias hojas de trabajo para crear una tabla dinámica.

Por eso, hoy me gustaría compartir con ustedes pasos sencillos para utilizar varias hojas de trabajo en una tabla dinámica.

¡El problema!

Digamos que quieres analizar los datos de ventas de tu empresa y extraes datos anuales de los últimos 4 años.

Así es como se obtiene el volcado de datos en Excel.

Tenga en cuenta que los datos de las 4 hojas son diferentes, pero la estructura es exactamente la misma, es decir, el mismo número de columnas y encabezados.

Ahora, para analizar estos datos, necesita crear un informe de tabla dinámica única a partir de estas múltiples hojas.

Algunos de los métodos comunes son:

1. Copie y pegue manualmente los datos de cada hoja y cree un único conjunto de datos en una hoja nueva.
2. Utilice código VBA para consolidar automáticamente datos de varias hojas.
3. O puede consolidar varias hojas de cálculo en una sola utilizando la opción de consolidación de Excel.

Pero el caso es que estos métodos requieren codificar, copiar y pegar o son repetitivos.

La solución

Aquí discutiré un nuevo método que utiliza la consulta de Microsoft que es dinámico, sólido y simple.

Créeme, ¡te encantará!

Con Microsoft Query, puede crear una tabla dinámica a partir de varias hojas de trabajo.

Descargue este archivo de datos para seguirlo.

Pasos para crear una tabla dinámica a partir de varias hojas de trabajo

Aquí tenemos pasos simples que puede seguir y antes de eso, descargue este archivo desde aquí para seguirlo.

• Primero, seleccione todos los datos de cada hoja y asígnele un nombre.
  • Datos de 2005 denominados como – Año 2005
  • Datos de 2006 nombrados – Año 2006
  • Datos de 2007 nombrados – Año 2007
  • Datos de 2007 nombrados – Año 2007

• Después de eso, aplique tablas de datos a todos los datos en cuatro hojas de trabajo.
  • Seleccione cualquier celda en el rango de datos.
  • Utilice Ctrl + T para convertir los datos de cada hoja en una tabla.
  • Asegúrese de que «Mi tabla tiene encabezados» esté marcado cada vez.
  • Repetir esta operación durante los 4 años (hojas).

• Comencemos por consolidar estos datos en una nueva hoja (atajo para agregar una nueva hoja: Shift + F11).
• En la pestaña Datos, haga clic en «De otras fuentes» -> Elija «De Microsoft Query».

• En el cuadro Elegir fuente de datos:
  • Haga clic en Archivos de Excel y luego presione Aceptar.
  • Seleccione la ruta de su archivo de Excel y luego seleccione su archivo y haga clic en Aceptar.
  • Los rangos nombrados aparecerán en el cuadro ‘Asistente de consultas – Elegir columnas’.

• En el asistente de consultas:
  • Arrastre cada rango con nombre (usando el botón de flecha) a las «Columnas en su cuadro de consulta».
  • Haga clic en Siguiente: olvide el error.
  • Y haga clic en Aceptar

• Ahora tenemos todos los datos en nuestro editor de consultas, todo lo que queda es combinar los datos de todos los rangos nombrados. Para hacer esto, haga clic en el pequeño botón SQL.

• En el cuadro SQL, elimine todo el texto, escriba una nueva consulta y haga clic en Aceptar.

Seleccione * del año 2005Unión todosSeleccione * del año 2006Unión todosSeleccione * del año 2007Unión todosSeleccione * del año 2008

• Ahora la tabla que aparece en la pantalla contiene los datos de las 4 hojas.
• Lo único que tenemos que hacer es ir a la pestaña Archivo e importar esta tabla a Excel.
• Al final, importe los datos nuevamente a Excel como una tabla dinámica.
  • En el menú Archivo -> haga clic en Devolver datos a Microsoft Excel.
  • Puedes ver que en total (en las 4 hojas) tenemos 592 registros.
  • Además, si agrega más datos a cualquiera de las 4 hojas, la tabla dinámica se actualizará tan pronto como la actualice.