En esta oportunidad nos dedicaremos a consolidar datos contenidos en rangos.

Dada dos o más tablas con la misma esructura (no importa cuántas filas), podemos obtener una nueva tabla con la misma estructura pero que contenga un resumen (total, promedio, máximo, etc) de las tablas. Esto es lo que se conoce con la herramienta Consolidación de Excel.

Nota:

Como en el caso de Filtro automático y avanzado, los datos deben constituir tablas o listas que tengan una estructura de base de datos relacional; es decir, cada fila debe constituir un registro y cada columna, la descripción de dicho registro, aquí tamboién los datos deben tener dicha estructura.

De esta manera, consolidar datos significa obtener una nueva tabla resumen.

La consolidación puede realizarse sobre rangos que se encuentran:
- En la misma hoja
- En diferentes hojas
- En diferentes libros

Para consolidar rangos se debe usar la siguiente secuencia: [Ficha Datos] - [Herramientas de datos] - seleccionar el icono [Consolidar] que se encuentra indicado en la siguiente imagen .

A continuación se debe obtener la siguiente ventana de diálogo.

En esta ventana de diálogo debemos ingresar la información necesaria para la consolidación.

En [Función] debemos seleccionar el tipo de consolidación. Obtener la Suma, Promedio, Max, Min, etc.
En el cuadro de texto de [Referencia] debemos ingresar todos los rangos a ser consolidados, de uno en uno. Se ingresa el primer rango (o se selecciona de la hoja) y se hace clic en el botón [Agregar] de forma que dicho rango forme parte del cuadro combinado [Todas las referencias]. Si hay uno de más, se selecciona y se usa el botón [Eliminar].

Cuando se haya completado todos los rangos a ser consolidados, debemos seleccionar uno o más rótulos de la parte inferior de la ventana.

Si sólo se activa [Fila superior], coloca las columnas de los rangos uno al costado de los otros.
Si sólo se activa [Columna izquierda], obtiene un resumen de todas las columnas según la fucnión elegida.
Si se activa los dos rótulos, se obtendrá una sábana con etiquetas de margen superior e izquierdo.

El tercer botón [Crear vinculos con los datos de origen mostrará enlaces que se pueden visualizar.


Vamos al primer ejemplo.

Abra el archivo "Ventas dia sem.xlsx" haciendo clic aquí

Consolidar rangos de la misma hoja

Haga clic en la celda E2 de la primera hoja.
Use la secuencia: [Ficha Datos] - [Herramientas de datos] - seleccionar el icono [Consolidar]
Deje función en "Suma".
En la hoja, seleccione el rango A2:B8
Haga clic en [Agregar]
Seleccione el rango: A10:B16, clic en el botón [Agregar].
Seleccione el rango: A18:B24, clic en [Agregar]
Seleccione el rango: A26:B32, clic en [Agregar]
Primera forma de consolidar:
Active sólo la casilla [Fila superior]. Clic en {Aceptar] Segunda forma de consolidar:
Haga clic en la celda E10.
Haga clic en el icono de [Consolidar] de [Herramienta de datos].
Como pude apreciar, ya están los rangos (siempre se mantiene los ragos de la última consolidación). Desactive [Fila superior] y active solamente [Columna izquierda]. Clic en [Aceptar].

Tercera forma de consolidar:
Haga clic en la celda E19. Haga clic en el icono de [Consolidar] de [Herramienta de datos].
Seleccione las dos casillas: [Fila superior] y [Columna izquierda]. Luego clic en [Aceptar].

La imagen siguiente muestra los resultados.

De las tres formar Ud. verá la que se adecúe a sus requerimientos.

Observación:

Hemos obtenido estos resultados porque la columna de las cantidades tienen por título: Semana 1, Semana 2, Semana 3 y Semana 4.
¿Qué pasaría con estos rangos consolidados si la cabecera fuera por ejemplo "Monto", en los 4 rangos?

Para responder a esta pregunta, ingrese en las celdas B2, B10, B18 y B26, el texto "Monto".
Ahora, haga clic en K2 y vuelva a ejecutar la consolidación. Los rangos ya están definidos, active sólo la casilla de [Fila superior].
Haga clic en K10, ejecute la consolidación pero activando sólo la casilla [Columna izquierda].
Finalmente, haga clic en K18, ejecute la consolidación activando las dos casillas.
Observe el resultado.

En conclusión, dependiendo de la forma de consolidación que queremos, habrá que preparar los rangos.

Algo más, vamos ahora a la hoja "Ventas diarias 2".

En esta hoja tenemos 3 cuadros con la misma estructura pero que representan reportes de venta de las tiendas "Plaza Norte", "Lago Plaza" y "Mega Plaza".

Vamos a consolidar estos reportes, pero cambiemos algo en el procedimiento:
Usaremos nombre de rango y no el rango mismo.
Seleccione el primer cuadro, incluyendo su cabecera. Usando [Ficha Fórmula] - [Nombres definidos] - [Asignar nombre] . Digite en [Nombre]: "Reporte1".
Proceda lo mismo con los otros dos cuadros y denle por nombre: "Reporte2" y "Reporte3", respectivamente.

Ahora, haga clic en F2
Use la secuencia para consolidar.
Usted verá que en el cuadro [Todas las referencias] hay rangos que no nos interesan pues lo que vamos a usar son otros.
Para eliminarlos, haga clic en el primero y presione el botón [Eliminar].
Luego use la barra espaciadora para seleccionar el siguiente y clic en el botón [Eliminar].
Haga lo mismo con todos los rangos.

En el cuadro de texto de "Referencias" de la venana de diálogo, digite Reporte1 (ya no el rango). Haga clic en [Agregar].
Digite Reporte2 y haga clic en [Agregar]. Proceda lo mismo con el rango Reporte3.
De las dos casillas, active sólo {Fila superior].

Ahora, haga clic en F12, proceda a consolidar pero ahora active las dos casillas.

Los resultados obtenidos se muestran en la siguiente imagen.

Nota importante

Sólo ha usado la primera columna de la izquierda.
Si se deseara un consolidado por "Almacén", la primera columna(de la izquierda) debe ser "Almacén". Es decir, el rango no puede empezar en A1 sino en B1.

Lo mismo habría que decir si quisiéramos un consolidado por "Tienda". En este caso, el rango deberá empezar en C1.

Consolidar hojas

Vamos a consolidar los datos contenidos en las hojas Semana1, Semana2, Semana3 y Semana4.
El resultado de la consolidación, lo dejaremos en la hoja "TotMensualxdia".

Empezamos dando nombre a los rangos de datos de cada hoja.
Procedimiento: Usaremos otro criterio, más sencillo:
Vaya a la hoja Semana1. Seleccione el rango A2:C552 (observe que evitamos la primera fila). Haga clic en [Cuadro de nombres] casilla izquierda de la barra de fórmulas.
En ella digite "Semana1" (sin las comillas) y presione [Intro].

Vaya a la hoja Semana2, seleccione el rango de datos (desde la fila 2). Haga clic en [Cuadro de nombres], digite en ella "Semana2" (sin las comillas). Presione [Intro].

Haga lo mismo con las hojas Semana3 y Semana4.

Vamos a la hoja "TotMensualxdia". Haga clic en A2 ( o en cualquier otrs celda que Ud. desee).

Realice el procedimiento de consolidar. En [Referencias] digite Semana1, luego clic en [Agregar]. Haga lo mismo con Semana2, Semana3 y Semana4.
Active las casillas [Fila superior] y [Columna izquierda]. Luego presione [Intro].

La siguiente imagen muestra el resultado de la consolidación

Finalmente, realizaremos la última consolidación. En este caso usaremos el procedimiento más engorroso para consolidar.

Vaya a la hoja "TotMensualxOperador".
Haga clic en la celda B2. Aquí hemos preparado el ancho de la columna de acuerdo al ancho de la columna de los datos.

Realice la consolidación y cuando tenga la ventana de diálogo, haga clic en el cuadro de texto de [Referencias]
Haga clic en la hoja Semana1
Seleccione el rango desde B2 que es la columna de "Operador" hasta la última fila de datos (En esta hoja el rango debe ser B2:C552). Haga clic en [Agregar]
Haga clic en la hoja Semana2 y seleccione el rango desde B2 y todas las filas de datos. No se confíe en el rango que ya esá seleccionado, pues eso corresponde al la hoja anterior(la cantidad de filas difiere de una hoja a otra). Use el botón [Agreagar] .
Proceda igual con las hojas Semana3 y Semana4.
Active las casillas [Fila superior] y [Columna izquierda].
El resultado obtenidoserá similar al de la siguiente imagen.

Guarde sus resultados con el nombre "ventasdiasem-a.xlsx".
En la siguiente sedión veremos el caso de Consolidación de archivos.

Siguiente sesión