fbpx
NOTICIAS CIPSA INFORMÁTICA, MARKETING DIGITAL, PROGRAMACIÓN, VIDEOJUEGOS, OFFICE, DISEÑO, 3D, ETC
24
marzo
2020

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

consolidar datos tabla moviles barcelona
Unidades vendidas de móviles en Barcelona

 

consolidar datos tabla moviles tarragona
Unidades vendidas de móviles en Tarragona

 

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.

 

consolidacion de datos Unidades vendidas de móviles en Lleida
Unidades vendidas de móviles en Lleida

 

consolidar datos
Unidades vendidas de móviles en Girona

 

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.

consolidar datos totales

 

3. Clicamos en DatosHerramientas de datosConsolidar.

consolidar datos herramientas

 

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

consolidar datos total suma

 

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:

consolidar datos funcion suma

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:

consolidar datos excel provincias

 

Siendo este el resultado:

Enero Febrero Marzo
Samsung Galaxy A6 32GB 348 uni. 276 uni. 675 uni.
XIAOMI Redmi 6A 16GB 560 uni. 654 uni. 538 uni.
APPLE iPhone 6 16GB 878 uni. 728 uni. 878 uni.
HUAWEI P20 Lite 1.139 uni. 854 uni. 658 uni.
APPLE iPhone XR 64GB 337 uni. 298 uni. 517 uni.
SAMSUNG Galaxy S8 559 uni. 706 uni. 462 uni.
HUAWEI Mate 20 Lite 920 uni. 1.098 uni. 1.014 uni.

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.

consolidar datos excel unidades moviles vendidas

 

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

consolidar datos excel vinculos

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

consolidar datos excel hoja barcelona

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

consolidar datos excel consolidacion

 

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.

consolidar datos excel crear vinculos

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:

consolidar datos excel estetica


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

consolidar datos trucos


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

Author

Jordi Llort

Profesor de Máster en Programación .NET . Máster de Gestión digital de Empresas, Máster de Videojuegos y SAP. Formador en CIPSA, con más de 30 años de experiencia.

Deja una respuesta

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

Recibe de primero nuestras ofertas de empleo y noticias