Consolidar tablas en un solo archivo Excel

By | octubre 28, 2017

Es probable que en algún momento necesite juntar varias tablas en un solo archivo Excel, como parte de la recopilación de información. Esto es lo que conocemos como consolidación de datos.

Estos datos pueden ser:

  • Tablas no contiguas situadas en una misma hoja
  • Tablas situadas en diferentes hojas de un mismo libro
  • Tablas situadas en hojas de diferentes libros

si los datos están en otros libros, se considera que son datos externos. MS Excel es capaz de realizar un resumen a partir de esta información, pero la recomendación es organizar la estructura de datos de la misma manera, es decir, los mismos campos en el mismo orden.

Veremos un ejemplo, donde vamos a considerar que tenemos 3 plantas de proceso. Desde cada planta nos envían por e-mail al cierre semanal un libro Excel que contiene el detalle de los contratos de personal sustituto desde el inicio del año en curso. Nuestro Objetivo consiste en establecer, a partir de estos 3 libros, una estadística acumulada de las horas de sustitución de las 3 plantas por departamento.

Para realizar este trabajo, disponemos de 3 libros:

  • atacama.xlsx
  • pudahuel.xlsx
  • talca.xlsx

Una vez recibido desde las plantas, deberá descargar los 3 libros en Excel.

Cada libro tiene una única hoja llamada SUSTITUCION cuya estructura de datos es la siguiente:

Para facilitar el trabajo y antes de proporcionar los archivos vacíos a cada planta, se ha configurado un rango de datos dinámicos de dos columnas A y B en cada archivo. Esto es porque no sabemos leer el total de fatos contenidos en cada archivo por lo cual en cada uno de los libros, 

Si en una celda cualquiera dentro de la hoja SUSTITUCION ingresamos =ListaInterinos y presionamos tecla F9, podremos editar el contenido del rango.

El rango dinámico se ha llamado ListaInterinos, y se ha definido con la siguiente fórmula:

=DESREF(SUSTITUCION!$A$1;;;CONTARA(SUSTITUCION!$D$1:$D$500);2)

Por otra parte debemos apoyarnos con el asistente de tablas dinámicas y debe estar activo en la barra de acceso rápido, (en la esquina superior izquierda de la ventana de Excel)

  1. Activar el asistente de Tablas Dinámicas

En Excel 2010, la interfaz del software nos ofrece la posibilidad de utilizar directamente Orígenes Múltiples.  Para poder usar esta función, hay que activar el asistente.

Vamos a añadir a la barra de acceso rápido el asistente de tablas dinámicas.

  • Haga clic en el menú Archivo y en el botón Opciones
  • En la Categoría Barra de Herramientas de acceso rápido, despliegue la lista Comandos disponibles en y seleccione Comandos que no están en la cinta de opciones.
  • Elija Asistente para Tablas y Gráficos dinámicos 
  • Luego haga clic en el botón Agregar y en Aceptar

 2. Creación de la Tabla dinámica a partir de tres libros

Previa activación del asistente, debemos ahora abrir los 3 libros en Excel. Para este caso los archivos están guardados en la siguiente ruta:

C:\Documentos\Consolida tablas Excel

Una vez los tres libros abiertos en Excel vamos a la creación de un libro en blanco.

A continuación trabajaremos con el asistente de Tablas dinámicas:

  • Haga clic en la barra de herramientas de Acceso Rápido y seleccione 
  • Marque la opción Rango de consolidación múltiples y luego clic en Siguiente.

En el Paso 2, elija la creación manual de campos de página (filtros)

Marque Campos de página personalizados y haga clic en Siguiente.

A continuación debemos agregar uno a uno cada rango de origen.

  • En primer lugar haga clic en el botón Examinar, seleccione en el lugar donde se encuentran los archivos según donde lo ha guardado. Para este caso está así:

Haga clic en el primer libro ATACAMA y luego en Aceptar. Al seleccionar el libro se cargara en la línea del rango a consolidar de la siguiente manera.

Luego agregamos en esa línea el nombre del rango, se debe escribir después del signo de exclamación ListaInterinos

A continuación haga clic en el botón 

El rango se ha añadido a la lista Todos los rangos

Excel muestra la zona seleccionada rodeada de guiones parpadeantes que le permiten controlar si la zona seleccionada corresponde a la que desea.

 

El siguiente paso es agregar los otros dos rangos de los libros pudahuel.xlsx y talca.xlsx

Hacer clic de nuevo en el botón Examinar, y repita los pasos anteriores para los otros dos orígenes de datos.

Una vez agregados los datos a Todos los rangos a consolidar, vamos a añadir los campos de pagina.

Es importante identificar cada uno de estos rangos con un nombre mnemotécnico, es decir, un nombre claro y asociado, así vamos a asignar a cada rango un campo de página correcto.

Siguiente paso; Asignar un campo de página a cada rango.

  • Seleccione el primer rango , active 1 como número de campo de página a utilizar y en la zona Campo 1 asigne el nombre PlantaATACAMA a este campo.

A continuación elegimos el siguiente rango que sería el de Pudahuel.xlsx!ListaInterinos

  • Introducir el nombre PlantaPUDAHUEL en la zona Campo 1
  • Seleccione el tercer rango e introduzca el nombre PlantaTALCA en la zona Campo 1.
  • Ahora que se ha definido los 3 rangos de origen,hacer clic en el botón Siguiente.
  • Defina la ubicación de la tabla dinámica con la consolidación final de los datos. En esta sería en A3 y luego hacer clic en el botón Finalizar.

El resultado, es finalmente en una tabla dinámica con los datos de los 3 libros consolidados en un solo archivo Excel.

Es importante destacar varios puntos al cierre de este ejercicio:

En la lista de campos de la tabla dinámica resultante contiene 4 campos sin título: Fila, Columna, Valor y Página1

 

El campo Fila corresponde a la primera columna de los Orígenes de datos. Deberá asegurase, de qué coloca en la primera columna de sus futuras tablas dinámicas el dato que debe aparecer en el rótulo de Fila de la tabla dinámica.

En el campo Columna contiene las demás columnas del origen de datos. En nuestro caso, este campo se limita a la segunda columna de los datos de origen, es decir; el número de horas, ya que nuestros datos de origen se conforman sólo de dos columnas. (contenidos en el nombre de rango).

El campo Valor contiene todos los elementos del campo columna. Dado que nuestros datos están distribuidos en 2 columnas y que la columna de los montos tiene valores numéricos. Excel ha utilizado automáticamente la función Suma. Cuando crea una tabla dinámica a partir de rangos discontinuos  que incluyen un número de columnas elevado. Excel realiza una agrupación de los campos de datos en el campo Columna y utiliza la función Cuenta. Esto puede desembocar en una serie de campos inútiles que es mejor ocultar.

En los archivos de origen, entre las columnas Número de horas y semana n°, se ha insertado una columna vacía para visualizar mejor la separación entre los datos que van a intervenir en las tablas dinámicas y los datos no utilizados.

La fórmula :

=DESREF(SUSTITUCION!$A$1;;;CONTARA(SUSTITUCION!$D$1:$D$500);2)

se ha configurado para tener en cuenta solo las columnas A y B.

el campo Página1 es el único campo cuyos elementos se ha definido manualmente. Pueden establecerse un máximo de 4 campos de página y cada uno de los cuales puede moverse a una Fila o a una Columna con el fin de añadir, si es necesario, niveles de agrupamiento.

El campo Página1 permite filtrar la información de una o mas plantas.

Con el objeto de poder ver simultáneamente la información relativa a las 3 plantas, vamos a colocar el campo Página1 en etiqueta de columna.

  • En el panel Lista de campos de tabla dinámica, arrastre el campo Página1 encima del campo Columna.

La tabla se ha reorganizado y ahora muestra los datos de las 3 plantas.

Para mejorar la lectura de la tabla dinámica, modifique el título, ocultar los encabezados  de los campos y utilizar los botones  para contraer los campos y no mostrar los Subtotales por planta.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *