Con este artículo de blog espero ayudarlos a conocer un poco más sobre la herramienta de consolidar datos en Excel. Pensemos que es una herramienta que se está aplicando no sólo a Excel, sino a muchas aplicaciones de gestión administrativa.

¿Qué entendemos por consolidar datos en Excel?

Consolidar es una palabra que se utiliza en muchos ámbitos, pero, de una manera general, se refiere a resumir, integrar varios elementos en uno sólo. Ver Wikipedia.

Métodos consolidación de datos

Hay varias soluciones a la hora de resumir datos. A continuación, comentaremos los tres métodos.

1. Consolidar datos con la herramienta Consolidación

La primera es la herramienta Consolidar, que es la que comentaremos en este blog.

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

3. Consolidar datos utilizando referencias 3D en las fórmulas

Quizás es el menos recomendable, ya que hemos de tener la precaución de que las celdas que se van a sumar tienen las mismas referencias. La única ventaja es que es más rápido que los otros.

En este blog os voy a comentar la primera de ellas.

¿Cómo podemos consolidar datos en Excel?

Vamos a aplicar este término en Excel. Para ello crearemos un ejemplo práctico, para que os sea amigable el aprendizaje.

Supongamos que somos gerentes de una empresa de ventas de móviles y tenemos 100 tipos de móviles diferentes y queremos comparar nuestras ventas en las diferentes provincias de Catalunya.

A más, querré realizar un informe, fusionando los diferentes datos de las provincias y realizar un informe trimestral de la venta de cada uno de mis móviles en Catalunya.

Recopilo los datos de cada población (Barcelona, Tarragona, Lleida y Girona) en diferentes hojas de cálculo y, utilizando los métodos de consolidación de datos que Excel nos brinda, será una tarea fácil. En dos pasos tendremos las unidades vendidas de móviles a nivel de todas nuestras tiendas en Catalunya.

Ejemplo de consolidación de datos en Excel

Objetivo

El objetivo será combinar valores de diferentes rangos de datos que contienen las unidades vendidas de móviles por provincias en diferentes hojas de cálculo (Barcelona, Tarragona, Lleida y Girona).

Remarco que el orden de los encabezados de filas de las ventas de móviles, en la provincia de Tarragona no siguen el mismo orden que en las otras hojas.

En una hoja de cálculo maestra Total consolidación resumiremos la venta de móviles en Catalunya.

Consideraciones previas:

A la hora de consolidar datos debemos tener en cuenta:

• Los rangos de datos han de formar parte de una lista, es decir, deberán tener una etiqueta en la primera fila (categoría)
• Todos los rangos deben tener la misma posición
• Los rangos deben estar en diferentes hojas de cálculo
• Los rangos pueden estar en otro libro
• Las categorías (filas) no deben porque estar ordenadas.

Pasos:

1. Añadiremos una hoja de cálculo vacía y le cambiamos el nombre.

Os recuerdo la manera más fácil de cambiar el nombre en una hoja de cálculo en Excel, haciendo doble clic en la pestaña. Cuando el texto esté en video inverso le cambiamos el texto por Total Consolidación.

2. Desde la hoja de cálculo vacía que acabamos de crear -Total consolidación-, realizaremos la consolidación de los diferentes productos.

3. Clicamos en Datos – Herramientas de datos – Consolidar.

4. En el cuadro de diálogo que aparece, seleccionamos la función Suma. Lo que quiero es la venta total de móviles en Catalunya. Si quisiésemos la media de venta de móviles seleccionaríamos Promedio y así con las diferentes funciones.

Más tarde puedes practicar con cualquiera de las otras funciones. Qué producto he vendido más (Max), y así con las otras funciones

5. Ahora, deberemos ir introduciendo los rangos de datos uno a uno en el siguiente campo. Pulsamos en el cuadro Referencia, seleccionamos el primer rango y pulsamos en el botón Agregar.

6. Si el rango se encuentra en otro libro, podemos buscarlo pulsando el botón Examinar. Como ves, los rangos se van agregando a la lista de debajo, en Todas las referencias:

Importante marcar los rótulos Fila superior y Columna izquierda, para que nos reconozca los datos como tabla, siendo la fila el nombre de la categoría y la columna el nombre del campo

7. Repetiremos este paso tantas veces como rangos queramos consolidar:

8. Clicamos en el botón Aceptar y obtendremos la suma de todas las ventas de móviles de las cuatro provincias catalanas:

Siendo este el resultado:

Es interesante remarcar que nos ha sumado correctamente la venta de móviles XIAOMI Readmi 6A 16GB aunque no coincida la posición de la fila. Así, en la hoja de Enero las ventas de dicho móvil estaban en la segunda fila y en Tarragona estaban en la tercera fila. Esto es debido a que Excel consolida por categorías, independientemente de la posición que se encuentren dentro de la tabla.

9. Si quieres que los datos estén constantemente actualizados, seleccionamos la opción Crear vínculos con los datos de origen.

En nuestro caso no lo hemos activado, si modifico algún dato de la hoja de Barcelona:

No se verá reflejado en la hoja Total consolidación.

10. Si queremos que nos lo vincule, no hemos de realizar una nueva consolidación. Simplemente volvemos en la hoja consolidada, en pestaña Datos, seleccionamos Consolidar, activando el check Crear vínculos con los datos de origen.

Aparecerá el resultado actualizado, del total que tenía 348 unidades, como le he añadido 10 unidades más, me las ha reflejado en el resultado.

11. Para finalizar añadimos el encabezado en la primera fila, para mejorar la estética:

Truco:

Si nos equivocamos en alguna referencia, ya sea por equivocación, o porque no queremos que dicho rango no aparezca en la consolidación, la seleccionamos y clicamos en botón Eliminar

Resumen

Con este blog espero haberos ayudado a conocer un poco más la herramienta de consolidar datos en Excel. Pensemos que es una herramienta que se está aplicando no sólo a Excel, sino a muchas aplicaciones de gestión administrativa.

A modo de ejemplo, la nueva versión de Sage 50cloud, incluye una opción de consolidación de empresas. Siempre que tengamos creadas un mínimo de dos empresas, podremos crear una tercera empresa de consolidación, que no lleve control de stock, sino que actúe a modo de resumen, para listados. Una idea muy parecida a la herramienta consolidar de Excel. Te dejo un enlace por si te interesa conocer el programa de gestión de Sage 50cloud: https://www.sage.com/es-es/productos/sage-50cloud/

Finalmente, te animo a que practiques con las diferentes opciones de que dispone la herramienta: Suma, promedio, max, etc. Practicando es la manera en la que irás conociendo  la potencia de la consolidación de datos.

Si estás interesado en ampliar tus conocimientos sobre todo lo relacionado con la Herramienta de Consolidación de Datos Excel visita nuestro Curso de Excel