Tenemos dos hojas con dos provincias, la estructura es la misma, pero la columna total, no se encuentra en la misma posición en las dos hojas.
En la primera hoja, que es Albacete, la columna total es la quinta columna.
En la segunda hoja, que es Cádiz, la columna total es la séptima columna.
Queremos calcular el total de ambas provincias.
Voy a usar la versión de Excel en inglés, para poder usar la función VSTACK.
Voy a ir haciéndolo paso a paso, aunque después, deberá de quedar una sola función.
Lo primero va a ser, traernos el nombre de cada provincia, en ambas hojas, la provincia se encuentra en la columna B, a partir, de la celda B2.
Con la función VSTACK, voy a unir desde B2 hasta B100, porque en ninguna de las hojas, se llega a la fila 100, de ambas hojas.
Escribo el signo igual, seguido del nombre de la función, y, abro un paréntesis, como argumento matriz1, voy a la hoja de Albacete, y, selecciono desde B2 a B100, y, como argumento matriz2, selecciono desde B2 a B100, pero de la hoja Cádiz.
Cierro paréntesis, y, acepto.
=VSTACK(Albacete!B2:B60;Cadiz!B2:B60)
Obtengo una matriz desbordada de una sola columna con las provincias, pero como hemos puesto hasta la fila 60, y, en ninguno de los casos, llega a la fila 60, pues esas filas de mas me la devuelve como cero.
Ahora, debemos de quedarnos con los valores únicos, por lo que voy a usar la UNICOS (UNIQUE), antes de cada provincia, como sigue:
=VSTACK(UNIQUE(Albacete!B2:B60);UNIQUE(Cadiz!B2:B60))
Obtenemos una matriz desbordada, pero con ceros, donde no había valores.
Pero, debemos de quitar esos ceros, por lo que voy a usar la función INDICE (INDEX), donde como argumento matriz, va a ser la función VSTACK, como argumento numero de fila, voy a usar una constante de matriz, para indicar que me devuelva solo las filas 1, y, 3, y, voy a omitir el argumento numero de columna, por lo que tomara el valor 1 por defecto.
=INDEX(VSTACK(UNIQUE(Albacete!B2:B60);UNIQUE(Cadiz!B2:B60));{1;3})
Ya tenemos, solo, las provincias.
Lo siguiente es calcular el total para ambas provincias, como la columna de total, cambia de posición en cada hoja, debo de ser la posición que ocupa en cada hoja, por lo que voy a usar de nuevo VSTACK, pero para unir los encabezados de ambas hojas, como sigue:
=VSTACK(Albacete!A1:G1;Cadiz!A1:G1)
Obtengo una matriz desbordada, de dos filas, con todos los encabezados.
Ahora, debo de hallar la posición del encabezado Total, en ambas filas, para ello, voy a usar la función COINCIDIR (MATCH), antes del nombre de la provincia, dentro de la función VSTACK, para que me encuentre la palabra Total, con una coincidencia exacta.
=VSTACK(MATCH("Total";Albacete!A1:G1;0);MATCH("Total";Cadiz!A1:G1;0))
Tenemos una matriz desbordada de dos filas con la posición de la columna Total, en ambas filas.
Con la función DIRECCION (ADDRESS), podemos, dando el numero de fila, y, de columna convertirlo en referencia, pero nos lo devuelve en formato de texto, el argumento número de fila, será siempre la fila 2, y, el argumento numero de columna es el resultado obtenido anteriormente, pero, debemos de concatenar de nuevo con la función DIRECCION, para indicar donde tiene que acabar, como sigue:
=ADDRESS(2;C3) & ":" & ADDRESS(60;C3)
Obtenemos un rango donde empieza, y, donde acaba, pero como digo, en formato de texto.
Con la función INDIRECTO (INDIRECT), vamos a rescatar ese rango de la hoja Albacete, para ello, escribimos el signo igual, seguido de la función INDIRECTO (INDIRECT), abrimos un paréntesis, como primer argumento, referencia, señalamos la celda B3, que pone Albacete.
=INDIRECT(B3
Concatenamos con el signo de admiración.
=INDIRECT(B3 & "!"
Y, concatenamos con la función DIRECCION anterior.
=INDIRECT(B3 & "!" & ADDRESS(2;C3)&":"&ADDRESS(60;C3))
Vemos que tenemos la columna total de la hoja Albacete, ahora, después del signo igual, usamos la función SUMA (SUM).
Arrastramos, y, tenemos el total para cada provincia.
Comments