El siguiente ejemplo, consiste en que tenemos 11 hojas, que desde la hoja2 hasta la hoja11 pertenecen a provincias, la hoja 1 es donde vamos a realizar el ejercicio.
Desde la hoja2 hasta la hoja11, cada una de ellas pertenece a una provincia, siendo el modelo igual para todas las hojas.
En la hoja1, queremos realizar un primer total por provincias.
El problema que se nos presenta es que cada provincia está en una hoja.
Veamos cómo podemos resolverlo.
Tenemos una función llamada HOJAS, que nos devuelve el número total de hojas, en este caso, el único argumento que tiene es nulo, es decir, simplemente, abrimos, y, cerramos paréntesis.
Si en la hoja1, en una celda, escribo =HOJAS(), me devuelve 11, que es el número total de hojas.
La pregunta es ¿Cómo acceder a cada hoja?
Si a la función HOJAS le resto uno, accedo a la hoja10, si le resto 2, accedo a la hoja9, y, así con el resto de las hojas.
Lo primero que voy a hacer es crear un índice con la función SECUENCIA, el argumento filas, lo va a decidir la función HOJAS.
=SECUENCIA(HOJAS())
Tenemos un índice que va desde 1 hasta el 11.
Si después del signo escribo entre comillas la palabra Hoja, y, lo concateno con el resultado de secuencia, tendré el nombre de la hoja con su número, que va desde la hoja1 hasta la hoja11, pero en formato de texto, quiere decir que de momento no puedo utilizarla.
="Hoja" & SECUENCIA(HOJAS())
Pero, la hoja1 no me interesa, porque es donde vamos a realizar el ejercicio, por lo que voy a quitarla.
Para ello, a HOJAS, le voy a restar 1, esto quiere decir, que si al resultado de HOJAS que es 11, le restamos 1, nos da como resultado 10.
Y, voy a usar el argumento inicio de la función SECUENCIA para que empiece en 2, quiere decir que me va a crear un contador de 10 filas empezando desde el número 2, entonces, ira desde el numero 2 hasta el 11, que son las hojas que vamos a usar.
="Hoja" & SECUENCIA(HOJAS()-1;;2)
Como he dicho anteriormente, está en formato de texto, y, debemos de convertirlo en una referencia real para rescatar el valor.
Lo primero que vamos a rescatar es el nombre de cada provincia, que se encuentra en la celda B2 de cada hoja.
Para ello, vamos a hacer uso de la función INDIRECTO, en la celda siguiente, usamos INDIRECTO y lo concatenamos con el signo de exclamación seguido de B2.
=INDIRECTO(B4 & "!b2")
Aceptamos y arrastramos, y, ya tenemos el nombre de cada provincia.
Lo siguiente es sacar los totales para cada provincia, que se encuentra en la columna G de cada hoja.
Para ello, volvemos a usar INDIRECTO, pero esta vez lo concatenamos con la columna G, y, después del signo igual usamos la función SUMA, es decir, me va a unir el valor de la celda B4, con cada valor de la columna G.
=SUMA(INDIRECTO(B4&"!g:g"))
Aceptamos y arrastramos.
Ya tenemos los totales para cada provincia.
Ahora, vamos a volver a sacar los totales pero con la función FILTRAR.
Escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis, como argumento array usamos INDIRECTO de B4, concatenado con la columna G.
=FILTRAR(INDIRECTO(B4&"!g:g");
Como argumento include, ponemos VERDADERO, ,quiere decir que nos va a devolver todos los valores.
=FILTRAR(INDIRECTO(B4&"!g:g");VERDADERO)
Englobamos la función FILTRAR dentro de la función SUMA.
=SUMA(FILTRAR(INDIRECTO(B4&"!g:g");VERDADERO))
Aceptamos, arrastramos, y, ya tenemos los totales, que podemos ver que son los mismos que los anteriores.
También, podemos hacerlo con la función DESREF.
Para ello, escribimos el signo igual seguido de la función DESREF, y, abrimos un paréntesis.
=DESREF(
Como argumento referencia, usamos INDIRECTO junto con la columna G.
=DESREF(INDIRECTO(B4&"!g:g");
Nos movemos una fila, y, cero columnas.
=DESREF(INDIRECTO(B4&"!g:g");1;0;
Ahora, viene el argumento alto, que va a ser las filas ocupadas, por lo que vamos a usar la función CONTARA, que nos devuelve un recuento de las celdas alfanuméricas ocupadas dentro de un rango.
=DESREF(INDIRECTO(B4&"!g:g");1;0;CONTARA(INDIRECTO(B4 & "!a:a"))
Como ancho, ponemos 1, para que quede en la misma columna.
=DESREF(INDIRECTO(B4&"!g:g");1;0;CONTARA(INDIRECTO(B4 & "!a:a"));1)
Por último usamos la función suma para obtener el total.
=SUMA(DESREF(INDIRECTO(B4&"!g:g");1;0;CONTARA(INDIRECTO(B4 & "!a:a"));1))
Aceptamos.
Arrastramos y tenemos los mismos totales obtenidos anteriormente.
Lo siguiente que vamos a hacer es calcular también los totales, pero por provincia y centro comercial.
Usamos la función filtrar anterior pero le añadimos una segunda condición que es el centro comercial, estas dos condiciones ya sabemos como debemos de ponerla, cada una entre paréntesis, y, separado por el símbolo de asterisco.
=SUMA(FILTRAR(INDIRECTO(B4&"!G:G");(VERDADERO)*(INDIRECTO(B4 & "!C:C")=D17)))
Fijamos la columna de B4, para que al arrastrar hacia la derecha no se actualice, y, la fila de D17, para que al arrastrar hacia abajo, la fila no se actualice.
=SUMA(FILTRAR(INDIRECTO($B4&"!G:G");(VERDADERO)*(INDIRECTO($B4 & "!C:C")=D$17)))
Aceptamos.
Nos posicionamos en la celda donde hemos colocado la función, pulsamos CTRL más C para copiar, a continuación, seleccionamos todo el rango, y, pulsamos CTRL más V para pegar.
Ya tenemos los totales por provincia y centro comercial.
Pero nos devuelve un error en los centros donde no ha habido ventas.
Pues vamos a usar la función SI.ERROR, donde ponemos que en caso de que esta expresión, devuelva un error, coloque un texto en blanco.
=SI.ERROR(SUMA(FILTRAR(INDIRECTO($B4&"!G:G");(VERDADERO)*(INDIRECTO($B4 & "!C:C")=C$18)));””)
Volvemos a copiar y pegar.
Vamos a ver el mismo ejemplo, pero en este caso no tenemos la columna de total, por lo que debemos de multiplicar cantidad por precio para cada hoja.
Tenemos una función matricial llamada MMULT, esta función multiplica dos matrices con el mismo número de filas y de columnas.
La sintaxis de esta función es matriz1, y, matriz2.
Debemos tener en cuenta que para usar la función MMULT, una de las matrices debe de estar en horizontal, y, la otra en vertical.
Vamos a ver cómo usar esta función en un modelo de datos más corto.
Tenemos los siguientes datos.
Vamos a calcular el total.
Vamos a ver como internamente trabaja la función MMULT.
Voy a escribir la siguiente expresión la siguiente expresión en F1.
=TRANSPONER(FILA(B2:B12))
Obtengo el número de fila desde B2 a B12 en horizontal.
Uso la siguiente expresión en E2.
=FILA(B2:B12)
Obtengo el mismo numero de filas pero en vertical.
En F2 voy a preguntar si el valor de F1, donde usamos el operador de rango derramado (#), para que seleccione desde F1 hasta la ultima columna ocupada, es igual al valor de E2, donde volvemos a usar el operador de rango derramado (#), quiere decir, que va a comparar cada valor de F1 con el valor de E2, y así, con el resto de los valores.
Estas son las condiciones.
=F1#=E2#
Obtengo VERDADEROS y FALSOS, VERDADERO donde el número de fila es igual, y, FALSO donde no lo es.
Pero, con estos resultados no puedo hacer nada, pues vamos a hacer uso del doble signo negativo, que nos devuelve 1 cuando el resultado es VERDADERO, y, cero cuando es FALSO.
Para ello, después del signo igual escribo el doble signo negativo, y, encierro la expresión entre paréntesis.
=--(F1#=E2#)
Vemos los uno donde la fila coincide tanto en vertical como en horizontal, y, cero donde o hay coincidencia.
Ahora, vamos a multiplicar cantidad por precio donde el resultado sea 1.
Para ello, vamos a usar el condicional SI.
Preguntamos que si el resultado de la expresión es 1, que multiplique cantidad por precio, en caso contrario, que ponga un texto en blanco.
=SI(--(F1#=E2#)=1;B2:B12*C2:C12;””)
Volvemos ver de forma piramidal los resultados.
Usamos la función suma.
=SUMA(SI(--(F1#=E2#)=1;B2:B12*C2:C12;""))
Y ya tenemos el total.
Pues así trabaja la función MMULT.
Hagámosla en una celda.
=MMULT (TRANSPONER (B2:B12); C2:C12)
Vemos que obtenemos el mismo total.
Volvemos al ejemplo anterior, abrimos el archivo, llamado EJEMPLO2.
Esta sería la expresión para calcular el total de la primera provincia.
=MMULT(TRANSPONER(INDIRECTO(B4&"!E2:E57"));INDIRECTO(B4 & "!F2:F57"))
Si nos fijamos he puesto la fila 57 como la última, porque sé que es esa fila para la hoja2, pero no sé cuál es la última fila para las demás hojas.
Tenemos una función llamada CONTARA que nos cuenta las celdas alfanuméricas dentro de un rango.
Si uso la siguiente expresión y arrastro sabre la última fila de cada hoja.
=CONTARA(INDIRECTO(B4 & "!a:a"))
Si modifica la función y añado la función CONTARA, dentro de la función INDIRECTO, tendríamos el mismo total.
=MMULT(TRANSPONER(INDIRECTO(B4&"!E2:E" & CONTARA(INDIRECTO(B4 & "!a:a"))));INDIRECTO(B4 & "!F2:F" & CONTARA(INDIRECTO(B4 & "!a:a"))))
Pero la función se hace un poco larga, por lo que vamos a usar la función LET y almacenamos en una variable la función CONTARA.
=LET(a;CONTARA(INDIRECTO(B4&"!a:a"));MMULT(TRANSPONER(INDIRECTO(B4&"!E2:E"&a));INDIRECTO(B4&"!F2:F"&a)))
Aceptamos, y, arrastramos, y, obtenemos los mismos resultados que en el ejercicio anterior.
También, podríamos hacerlo con la función SUMAPRODUCTO, dando la matriz de cantidad y la matriz de precio.
La función SUMAPRODUCTO, debemos de especificar dos matrices, las cuales las multiplicará, y, nos dará la suma total.
=SUMAPRODUCTO(INDIRECTO(B4&"!e2:e"&CONTARA(INDIRECTO(B4&"!a:a")));INDIRECTO(B4&"!f2:f"&CONTARA(INDIRECTO(B4&"!a:a"))))
Volvemos a crear la función LET.
=LET(a;CONTARA(INDIRECTO(B4&"!a:a"));SUMAPRODUCTO(INDIRECTO(B4&"!e2:e"&a);INDIRECTO(B4&"!f2:f"&a)))
Aceptamos, arrastramos, y, tenemos los mismos totales.
Damos por finalizado este ejemplo.
Videos vistos en este ejemplo.
Archivos para descargar:
Comentários