Daremos una segunda vuelta de tuerca a un tema recurrente que ya hemos visto en ocasiones anteriores en el blog (ver uno y dos).
Trataremos la forma de conseguir sumar de manera condicionada sobre todas las hojas del libro deseadas, siempre que existe una distribución por columnas homogénea en todas las hojas.
El desarrollo que veremos nace por la necesidad de una alumna en uno de los cursos que impartí recientemente, donde a partir de ficheros con un número alto de hojas de trabajo (más de cien en algún caso), debía obtener un consolidado de acuerdo a uno elemento o condición... y lo expuesto en esto otro post) quedaba un poco inoperativo.
Supongamos un fichero con tres hojas (en realidad podrían ser 300...): ene, feb y mar; donde tenemos una serie de códigos repetidos en las columnas A de cada hoja, igualmente cantidades a consolidar 'unidades' e 'importe' en las columnas B y C.
Es fundamental que se de esta circunstancia: Datos dispuestos en mismas columnas!!!
Nuestro objetivo acumular en una primera hoja 'resumen' los valores de unidades e importes que aparezcan en el resto de hojas...
La diferencia respecto al método empleado en el post anterior comentado será el número de hojas involucradas, que podría ser desconocido.
La clave por tanto es identificar y listar los nombres de todas las hojas de libro empleando la función de Excel 4.0: GET.WORKBOOK o en español INDICAR.LIBRO.
OJO!!, esto requerirá que nuestro libro de trabajo se grabe como libro de Excel habilitado para macros!!.
Por otra parte para poder hacer uso de este tipo de funciones en nuestras hojas, deberemos emplearlo dentro de los nombres definidos!!.
El primer paso es generar un nombre definido que comprenderá todas las hojas.
Nuestro nombre definido se llamará 'NombreHojas' y deberemos incluir la siguiente fórmula:
=REEMPLAZAR(INDICAR.LIBRO(1)&T(AHORA());1;ENCONTRAR("]";INDICAR.LIBRO(1)&T(AHORA()));"")
INDICAR.LIBRO devuelve el nombre completo de nuestro libro incluyendo la hoja...
Con la función REEMPLAZAR eliminamos el nombre del libro y nos quedamos únicamente con lo que nos interesa, que es el nombre de la hoja.
Para conseguir el listado de nuestras hojas, aplicaremos la función INDICE sobre el conjunto de nombres que retornará INDICAR.LIBRO.
Para ello crearemos una tabla donde insertaremos un orden desde 2 hasta un número alto, que represente la posición de todas las hojas de nuestro libro (esto es, 2,3,4,5, ..., 300).
Comenzamos en 2 por que la hoja 1 es la de 'resumen', hoja sobre la que no deseamos operar.
En la tabla añadimos la fórmula:
=SI.ERROR(INDICE(NombreHojas;[@orden]);"")
Ya tenemos el listado de hojas del libro que necesitábamos.
Este paso se puede obviar y escribir manualmente si el listado fuera personalizado.
En el siguiente paso generaremos un nuevo nombre definido con fórmula para trabajar sobre las hojas existentes.
El nombre lo llamaré 'MisHojas' y tendrá la fórmula:
=DESREF(TblHojas[[#Encabezados];[hojas]];1;0;CONTARA(TblHojas[hojas])-CONTAR.BLANCO(TblHojas[hojas]))
que devolverá las hojas existentes, evitando las vacías que provocarían fallos en los pasos siguientes.
Y finalmente compondremos nuestra fórmula matricial definitiva para consolidar los datos de las diferentes hojas según el código buscado.
En D4 insertamos y ejecutamos matricialmente (presionando Ctrl+mayusc+Enter):
=SUMA(SUMAR.SI(INDIRECTO(MisHojas & "!A:A");$C4;INDIRECTO(MisHojas & "!B:B")))
y en E2 insertamos y ejecutamos matricialmente (presionando Ctrl+mayusc+Enter):
=SUMA(SUMAR.SI(INDIRECTO(MisHojas & "!A:A");$C4;INDIRECTO(MisHojas & "!C:C")))
arrastrando después tanto como necesitemos...
Consiguiendo nuestra meta...
Trataremos la forma de conseguir sumar de manera condicionada sobre todas las hojas del libro deseadas, siempre que existe una distribución por columnas homogénea en todas las hojas.
El desarrollo que veremos nace por la necesidad de una alumna en uno de los cursos que impartí recientemente, donde a partir de ficheros con un número alto de hojas de trabajo (más de cien en algún caso), debía obtener un consolidado de acuerdo a uno elemento o condición... y lo expuesto en esto otro post) quedaba un poco inoperativo.
Supongamos un fichero con tres hojas (en realidad podrían ser 300...): ene, feb y mar; donde tenemos una serie de códigos repetidos en las columnas A de cada hoja, igualmente cantidades a consolidar 'unidades' e 'importe' en las columnas B y C.
Es fundamental que se de esta circunstancia: Datos dispuestos en mismas columnas!!!
Nuestro objetivo acumular en una primera hoja 'resumen' los valores de unidades e importes que aparezcan en el resto de hojas...
La diferencia respecto al método empleado en el post anterior comentado será el número de hojas involucradas, que podría ser desconocido.
La clave por tanto es identificar y listar los nombres de todas las hojas de libro empleando la función de Excel 4.0: GET.WORKBOOK o en español INDICAR.LIBRO.
OJO!!, esto requerirá que nuestro libro de trabajo se grabe como libro de Excel habilitado para macros!!.
Por otra parte para poder hacer uso de este tipo de funciones en nuestras hojas, deberemos emplearlo dentro de los nombres definidos!!.
El primer paso es generar un nombre definido que comprenderá todas las hojas.
Nuestro nombre definido se llamará 'NombreHojas' y deberemos incluir la siguiente fórmula:
=REEMPLAZAR(INDICAR.LIBRO(1)&T(AHORA());1;ENCONTRAR("]";INDICAR.LIBRO(1)&T(AHORA()));"")
INDICAR.LIBRO devuelve el nombre completo de nuestro libro incluyendo la hoja...
Con la función REEMPLAZAR eliminamos el nombre del libro y nos quedamos únicamente con lo que nos interesa, que es el nombre de la hoja.
Para conseguir el listado de nuestras hojas, aplicaremos la función INDICE sobre el conjunto de nombres que retornará INDICAR.LIBRO.
Para ello crearemos una tabla donde insertaremos un orden desde 2 hasta un número alto, que represente la posición de todas las hojas de nuestro libro (esto es, 2,3,4,5, ..., 300).
Comenzamos en 2 por que la hoja 1 es la de 'resumen', hoja sobre la que no deseamos operar.
En la tabla añadimos la fórmula:
=SI.ERROR(INDICE(NombreHojas;[@orden]);"")
Ya tenemos el listado de hojas del libro que necesitábamos.
Este paso se puede obviar y escribir manualmente si el listado fuera personalizado.
En el siguiente paso generaremos un nuevo nombre definido con fórmula para trabajar sobre las hojas existentes.
El nombre lo llamaré 'MisHojas' y tendrá la fórmula:
=DESREF(TblHojas[[#Encabezados];[hojas]];1;0;CONTARA(TblHojas[hojas])-CONTAR.BLANCO(TblHojas[hojas]))
que devolverá las hojas existentes, evitando las vacías que provocarían fallos en los pasos siguientes.
Y finalmente compondremos nuestra fórmula matricial definitiva para consolidar los datos de las diferentes hojas según el código buscado.
En D4 insertamos y ejecutamos matricialmente (presionando Ctrl+mayusc+Enter):
=SUMA(SUMAR.SI(INDIRECTO(MisHojas & "!A:A");$C4;INDIRECTO(MisHojas & "!B:B")))
y en E2 insertamos y ejecutamos matricialmente (presionando Ctrl+mayusc+Enter):
=SUMA(SUMAR.SI(INDIRECTO(MisHojas & "!A:A");$C4;INDIRECTO(MisHojas & "!C:C")))
arrastrando después tanto como necesitemos...
Consiguiendo nuestra meta...
No hay comentarios:
Publicar un comentario
Nota: solo los miembros de este blog pueden publicar comentarios.