Consolidar datos en Excel: tres métodos imprescindibles

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.

  1. Activa la hoja Totales Consolidación.

consolidar excel total

  1. Haz clic en Datos – Herramientas de datos – Consolidar.

consolidar datos en excel

  1. En el cuadro de diálogo que aparece, selecciona la función Suma (más tarde puedes practicar con cualquiera de las otras funciones).

consolidar datos en excel función suma

  1. 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.

seleccionar rangos consolidar datos en excel

  1. Utiliza as casillas Fila superior y Columna izquierda para indicarle a Excel que ahí es donde se encuentran los encabezados de los datos.

consolidar datos en excel encabezados columna

  1. Haz clic en Aceptar. Automáticamente se calculará la función que habías seleccionado, consolidando los datos de todos los rangos introducidos.

consolidar datos en excel totales

  1. Para finalizar, escribe el encabezado de la primera columna.

consolidar datos en excel encabezado tabla

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:

  1. 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.

consolidar datos en excel tabla dinámica

  1. Selecciona la opción Rangos de consolidación múltiples y pulsa Siguiente.

rangos de consolidación multiples consolidar datos en excel

  1. En el paso 2a, selecciona la opción Campos de página personalizados.
  2. 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.

asistente tablas dinámicas consolidar datos en excel

  1. 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.

totales tabla consolidar datos en excel

Y este es el resultado. Como puedes ver, Excel agrega totales tanto en las filas como en las columnas.

tabla dinámica excel consolidar datos en

 

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.

  1. Siguiendo el ejemplo de la misma empresa, crea una hoja y llámala Totales con referencias 3D.
  2. Escribe el nombre de los productos y los meses del año de la misma forma.
  3. 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.

Consolidar datos en Excel
Título: Consolidar datos en Excel (32 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.

 

¡Compartir es vivir!
Sergio
 

La destreza y el perfeccionismo quizá sean las dos virtudes que me permiten ayudar a mis clientes a facilitar las tareas administrativas de sus negocios.

Haz clic aquí para dejar un comentario 0 comentarios

Escribe una respuesta: