Tenemos tres hojas con la misma estructura, una llamada Cádiz, otra llamada Córdoba, y, una última llamada Sevilla, donde tenemos las ventas de productos en diferentes centros comerciales, provincias, y, fechas.
Tenemos una primera hoja de resumen con el nombre de las tres provincias, y, el producto por el que queremos recuperar el total, que es el mismo para las tres provincias, siendo este, aspiradora.
La complejidad de este ejemplo es que cada provincia está en una hoja diferente.
Para este ejemplo, podemos usar la función SUMAR.SI
.
Voy a usar la función SUMAR.SI para calcular el total del producto aspiradora de la primera hoja, que es Cádiz.
=SUMAR.SI(Cadiz!E:E;Hoja4!C5;Cadiz!H:H)
Podemos ver que nos aparece el nombre de la hoja Cádiz, haciendo referencia al rango de celdas con el que trabajar.
Pero, esto no es dinámico, porque siempre miramos a la hoja de Cádiz.
Tenemos una función llamada INDIRECTO, la cual nos puede ayudar a resolver este problema.
Por ejemplo, si en una celda escribo:
=INDIRECTO("b5")
Convierte el texto en una referencia real, y, me devuelve el valor de la celda B5, que es Cádiz.
Ahora, si en F4 escribo B5, y, en la función INDIRECTO pongo:
=INDIRECTO(F4)
Me sigue devolviendo Cádiz, porque está obteniendo la dirección donde debe de ir de la celda F4.
¿Dónde tenemos que usar la función INDIRECTO?
Debemos de usarla donde hace referencia a la hoja Cádiz, pues ponemos la función INDIRECTO en la primera referencia que es Cadiz!E:E, entre comillas, y, en la segunda aparición de Cádiz.
La función INDIRECTO, convertirá en referencia real el texto entre comillas.
=SUMAR.SI(INDIRECTO("Cadiz!E:E");Hoja4!C5;INDIRECTO("Cadiz!H:H"))
Aceptamos y tenemos el mismo resultado.
Pero sigue sin ser dinámico.
Tenemos que cambiar Cádiz por el valor de la celda B5, y, no debe de ir entre comillas, por lo que tenemos que usar el operador de concatenar para unir la celda B5 con el resto de la referencia que si debe de ir entre comillas.
=SUMAR.SI(INDIRECTO(B5 & "!E:E");Hoja4!C5;INDIRECTO(B5 & "!H:H"))
Aceptamos, y, seguimos teniendo el mismo resultado.
Ahora, arrastramos.
Y tenemos el total vendida de aspiradoras para cada provincia.
También, podemos hacerlo con el condicional SI junto con la función SUMA.
Primero, voy a preguntar si en la hoja Cádiz, desde E2 hasta E100 es igual a aspiradora.
=(Cadiz!E2:E100=C5)
Obtengo una matriz desbordada con verdaderos y falsos.
Ahora, con el condicional SI voy a preguntar que si se cumple la condición me devuelva la columna de total, en caso contrario, que me devuelva un texto en blanco.
=SI((Cadiz!E2:E100=C5);Cadiz!H2:H100;"")
Obtengo una matriz desbordada con los totales que pertenecen a aspiradora.
Ahora, con la función SUMA, sumamos dichos valores.
=SUMA(SI((Cadiz!E2:E100=C5);Cadiz!H2:H100;""))
Podemos ver que tenemos el mismo total.
Pero igual que antes no es dinámico, por lo que vamos a hacer uso de la función INDIRECTO.
=SUMA(SI((INDIRECTO(B5&"!E2:E100")=C5);INDIRECTO(B5&"!H2:H100");""))
Aceptamos y tenemos el mismo resultado.
Arrastramos, y, tenemos los mismos resultados.
También lo podría hacer de la siguiente forma.
Hago una primera pregunta donde pregunto que si desde E2 a E100 de la hoja Cádiz es igual a aspiradora.
Pongo el símbolo de asterisco.
Y que me devuelva los valores de total.
Ahora uso la función SUMA, y, vemos que tenemos el mismo total.
=SUMA((Cadiz!E2:E100="Aspiradora")*(Cadiz!H2:H100))
Usamos INDIRECTO.
=SUMA((INDIRECTO(B5&"!E2:E100")=C5)*(INDIRECTO(B5&"!H2:H100")))
Arrastramos, y, seguimos teniendo los mismos totales.
Aunque también, podríamos concatenar tanto los rangos de búsquedas como las condiciones.
=SUMA((Cadiz!C2:C100&Cadiz!E2:E100=B5&C5)*(Cadiz!H2:H100))
Ahora, usamos INDIRECTO.
=SUMA((INDIRECTO(B5 & "!C2:C100")&INDIRECTO(B5&"!E2:E100")=B5&C5)*(INDIRECTO(B5&"!H2:H100")))
Arrastramos.
Ahora, tenemos el mismo modelo, pero las provincias terminan con un guion bajo, mientras que las provincias que tenemos en el modelo no tienen ese guion bajo, por lo que nos devuelve error.
Para resolver este problema vamos a usar la función SUSTITUIR, lo usamos dentro de la función INDIRECTO en la referencia B5, que es la que hace referencia a la provincia.
Tenemos que cambiar el nombre de cada provincia por el nombre de cada hoja.
=SUMAR.SI(INDIRECTO(SUSTITUIR(B5;B5;B5&"_") & "!E:E");Hoja4!C5;INDIRECTO(SUSTITUIR(B5;B5;B5&"_") & "!H:H"))
Ya lo tenemos.
コメント