En muchas empresas se puede dar el caso de tener que confeccionar un informe fusionando datos que se reciben de varios centros de trabajo. Esto puede ser una tarea muy laboriosa si no conoces las herramientas para consolidar datos en Excel que te brinda el programa para hacerlo.
Te presento un caso práctico:
Una empresa fabrica más de 20 productos diferentes y los vende en cinco países.
Una vez al mes, las cinco delegaciones de los países deben enviar un informe a la central con las unidades vendidas de cada uno de los productos.
Una vez recopilados los datos, la central debe confeccionar un informe donde se consoliden las unidades vendidas por cada mes en cada uno de los países.
Hay varias soluciones diferentes de consolidar datos en Excel utilizando varias herramientas. En el artículo de hoy te mostraré tres de ellas para que puedas practicar.
Consolidar datos en Excel con la herramienta Consolidar
Comenzaré con la herramienta que está expresamente diseñada para esta tarea: Consolidar. Su objetivo es realizar cálculos sencillos con los datos que se encuentran en varias hojas diferentes.
Te presento el libro con el que trabaja la empresa. Como ves, cuenta con varias hojas, una por cada país, y una hoja de totales que, en este momento está vacía pero la he creado para alojar los datos resultantes. Las hojas de los países tienen la misma estructura.
- Activa la hoja Totales Consolidación.
- Haz clic en Datos - Herramientas de datos - Consolidar.
- En el cuadro de diálogo que aparece, selecciona la función Suma (más tarde puedes practicar con cualquiera de las otras funciones).
- Ahora, debes ir introduciendo los rangos de datos uno a uno en el siguiente campo. Pulsa en el cuadro Referencia, selecciona el primer rango y pulsa en el botón Agregar. Si el rango se encuentra en otro libro, puedes buscarlo pulsando el botón Examinar. Como ves, los rangos se van agregando a la lista de debajo.
- Utiliza as casillas Fila superior y Columna izquierda para indicarle a Excel que ahí es donde se encuentran los encabezados de los datos.
- Haz clic en Aceptar. Automáticamente se calculará la función que habías seleccionado, consolidando los datos de todos los rangos introducidos.
- Para finalizar, escribe el encabezado de la primera columna.
Si descargas el archivo de ejemplo, observarás que el tamaño de los rangos de cada país no es el mismo. En unos países no se venden algunos artículos. No debes preocuparte por esto. Excel lo reconoce y muestra siempre los datos correctos.
Consolidar datos con tabla dinámica
La forma más eficiente de consolidar datos en Excel que proceden de varias hojas es con una tabla dinámica. De esta forma sólo tendrás que actualizar la tabla cuando se modifiquen o agreguen nuevos datos:
- Pulsa las teclas Alt, T, B, de una en una y en este orden (Alt, D, P, si tu configuración regional es en inglés). Se mostrará el asistente para tablas dinámicas y gráficos dinámicos.
- Selecciona la opción Rangos de consolidación múltiples y pulsa Siguiente.
- En el paso 2a, selecciona la opción Campos de página personalizados.
- En el paso 2b debes seleccionar los datos. Selecciona cada rango y pulsa en el botón Agregar. Si el rango se encuentra en un libro diferente, pulsa el botón Examinar para buscarlo. Este es un ejemplo sencillo, por lo que no necesitarás agregar ningún campo de página. Pulsa en Siguiente.
- Para finalizar elige la ubicación de la tabla. En este caso, sitúate en la celda A1 de la hoja Totales con tabla dinámica.
Y este es el resultado. Como puedes ver, Excel agrega totales tanto en las filas como en las columnas.
Consolidar datos utilizando referencias 3D en las fórmulas
El último método que te mostraré hoy para consolidar datos de varias hojas en Excel, no lo recomiendo en absoluto. Es muy muy peligroso y solamente lo debes utilizar cuando estés totalmente seguro de que las celdas que se van a sumar tienen las mismas referencias. De lo contrario, te expones a resultados incorrectos.
El único motivo por el que te lo muestro es porque es más rápido que ninguno.
- Siguiendo el ejemplo de la misma empresa, crea una hoja y llámala Totales con referencias 3D.
- Escribe el nombre de los productos y los meses del año de la misma forma.
- Ahora, en la celda correspondiente a enero del producto 1, escribe la fórmula =SUMA(España:Argentina!B4).
Esta fórmula sumará todos los datos numéricos que se encuentren en la celda B4 desde la hoja España hasta la hoja Argentina.
Insisto, utiliza este método solo como última alternativa.
Resumen
Hoy te he presentado tres formas de consolidar datos en Excel con los que podrás hacer operaciones matemáticas con rangos que se encuentran en diferentes hojas o libros. Mi método favorito es el de las tablas dinámicas porque si quiero agregar nuevos datos a la consolidación, sólo tengo que actualizar la tabla y listo.
-
-
Título: Consolidar datos en Excel (291 clics)
Tamaño: 35 KB
Te dejo la información oficial de Microsoft acerca de este tema.
¿Conoces tú algún otro método más para consolidar datos en Excel? Me gustaría leer tu comentario.
8 comentarios en “Consolidar datos en Excel: tres métodos imprescindibles”
muchas gracias por el aporte
Pienso que el artículo quedaría excelente si se le adiciona la forma de hacerlo en Power Query.
Saludos desde Colombia.
Carlos Mario
Carlos, el artículo estaba pensado para usuarios de nivel medio y tampoco quería meterme más a fondo con ello. Te animo a que me envíes un artículo en el que indiques cómo lo harías tú con PowerQuery, y lo publicaré con mucho gusto.
Sergio. me podria explicar por que la hoja totales consolidacion arroja totales por producto diferentes a las demas. De hay en fuera me parece muy clara su explicaion y de gran utilidad. Gracias
Gracias Sergio por el aporte. Además has puesto el archivo fuente como modelo para practica…Se le agredece mucho.
Buenos días, Sergio. Muy interesante la entrada en el blog. Pero tengo una pregunta. Los datos origen, en mi caso, están estructurados en tablas automáticas, que a lo largo del año podrían variar el número de líneas, por lo que esos valores, quedarían fuera de la consolidación. Hay alguna manera de consolidar, siendo el origen tablas automáticas, para que tome todos los valores de las tablas, independientemente del número de líneas? No sé si me he explicado.
Hola, Ana!
Es muy sencillo: Convierte cada rango en una tabla y cuando tengas que hacer referencia a las celdas, escribe el nombre de la tabla.
Cuando actualices los datos, el resultado se verá reflejado en la consolidación.
Soy cubano y gusto mucho del trabajo en excel. me gusta el area de la contabilidad y asuntos financieros. todo este material gratis, pues no puedo pagarlo, ustedes saben, me es de mucha ayuda para mi conocimiento y avance con esta valiosa herramienta. agradezaco a aquellos que se esfuerzan para que otros puedan aprender. un abrazo. Les deseo exito y como decimos los cristianos Dios continúe bendiciéndoles.
Los comentarios están cerrados.