¿cómo crear una tabla dinámica con varias hojas?
Una tabla dinámica es una de las mejores formas de resumir datos.
Puede crear un informe resumido en segundos a partir de miles de filas de datos. Pero cuando trabajas con datos en el mundo real, no todo es perfecto.
Y hoy quiero compartir con ustedes un problema común al que todos nos enfrentamos.
Antes de comenzar, me gustaría agradecer a Raman Girdhar por su pregunta. Me sigue desde hace bastante tiempo.
A veces recibimos o capturamos nuestros datos en diferentes libros y, en este caso, crear una tabla dinámica requiere un esfuerzo adicional para combinar estos múltiples libros en uno.
Sólo después de eso podremos crear una tabla dinámica.
Entonces, en este artículo, me gustaría mostrarle un proceso de tres pasos para crear una tabla dinámica utilizando datos de varios libros.
Pasos para crear una tabla dinámica utilizando datos de varios libros
Importante: Para esto necesitamos usar Power Query, así que asegúrese de tener Power Query en su versión de Excel. Para Excel 2016, se encuentra en la pestaña Datos y para otras versiones (2010 y 2013), debe instalar el complemento.
Aquí tengo cuatro libros diferentes con datos de ventas para diferentes áreas. Puede descargar estos archivos desde aquí para seguirlos .
Y asegúrese de tener todos estos archivos en una carpeta.
Paso 1: combinar archivos mediante Power Query
Primero, necesitamos combinar todos los archivos en uno solo con PowerQuery.
- Vaya a la pestaña Datos -> Obtener transformación -> Nueva consulta -> Desde archivo -> Desde carpeta.
- Ahora, desde la ventana de selección de carpeta, haz clic en “Examinar” y selecciona la carpeta donde tienes todos los archivos.
- Haga clic en Aceptar.
- Desde allí aparecerá una ventana «Combinar archivos».
- Y desde esta ventana, seleccione la hoja de trabajo con sus datos de todos los libros. (Consejo: asegúrese de tener el mismo nombre de hoja de trabajo en todos los libros).
- Una vez que haga clic en Aceptar, todos los datos de los libros se editarán en el Editor de Power Query.
Paso 2: preparar los datos para la tabla dinámica
Ahora necesitamos realizar pequeños cambios en nuestros datos para prepararlos para una tabla dinámica.
- Si observa los datos, aquí tenemos una columna adicional con el nombre del archivo fuente.
- Haga clic derecho en esta columna y seleccione Dividir columna -> Por delimitador.
- En la ventana del delimitador, seleccione personalizado, agregue “–” como delimitador y seleccione “Delimitador más a la izquierda”.
- Haga clic en Aceptar.
- Después de eso, elimine la segunda columna.
- Ahora cambie el nombre de la primera columna.
- Una cosa más, haga clic derecho en la primera columna nuevamente y vaya a Transformar -> Poner en mayúscula cada palabra.
- Y ahora tus datos están listos. Entonces, haga clic en cerrar y cargar.
Paso 3: inserte la tabla dinámica
En este punto, tenemos una nueva hoja de trabajo en nuestro libro de trabajo con los datos combinados de los cuatro archivos.
Ahora es el momento de crear una tabla dinámica a partir de ella.
- Seleccione la tabla, vaya a Insertar pestaña y haga clic en el botón Tabla dinámica.
- Aquí obtendrá una ventana de inserción de tabla dinámica.
- Haga clic en Aceptar en la ventana Insertar tabla dinámica y obtendrá una nueva tabla dinámica en su libro.
¡Felicidades! Ha creado con éxito una nueva tabla dinámica a partir de diferentes archivos.
Actualizar tabla dinámica
Estoy seguro de que está pensando en cómo actualizar su tabla dinámica después de actualizar los archivos fuente.
Es sencillo. Para actualizar su tabla dinámica, debe actualizar la consulta para que los datos del archivo de origen se puedan actualizar en la tabla de origen que creó con PowerQuery. Y después de eso puedes actualizar tu tabla dinámica.
Pero la mejor y más fácil forma es utilizar el botón de actualización en la pestaña de datos. Se referirá a ambos (tabla dinámica + consulta).
Puntos importantes
Aquí he enumerado algunos puntos que debe recordar al utilizar Power Query para una tabla dinámica.
- Los archivos deben estar en una sola carpeta.
- Los datos deben estar en el mismo formato en todos los archivos. De lo contrario, deberás hacerlo utilizable después de combinarlo.
- Los nombres de las hojas de trabajo deben ser los mismos en todos los archivos.
- Asegúrese de que no haya errores en un archivo. O puede utilizar la opción «Ignorar archivos de error».
- No cambie el nombre de ningún libro de trabajo.