En el siguiente ejemplo, tenemos tres hojas, que corresponden a una provincia, donde tenemos las ventas de productos, en diferentes centros comerciales, y, fechas, una para la provincia de Sevilla, otra para la provincia de Huelva, y, otra para la provincia de Cádiz.
Los tres modelos están en formato de tabla.
En una hoja nueva, queremos seleccionar una provincia, un centro comercial, y, queremos ver el total.
Además, vamos a construir un panel, donde depende del total, le asignaremos una letra.
Pues, empecemos.
Vamos a crear una primera validación para seleccionar una provincia, para ello, vamos a la pestaña de datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Se abre la ventana de validación de datos, desplegamos la ventana de permitir, y, seleccionamos lista.
En la ventana origen, vamos a escribir las provincias, separadas por punto y coma.
Aceptamos.
Vamos a crear una segunda validación, para los productos, pero en este, caso, debemos de tener productos únicos, en las tres hojas, los productos son los mismos, para ello, por ejemplo, en la hoja de Sevilla, en una celda, usamos la función UNICOS en la columna de productos.
=UNICOS(Sevilla[Producto])
Seleccionamos los productos, vamos al cuadro de nombres, y, le damos un nombre, por ejemplo, productos.
Seleccionamos la celda, donde vamos a poner la validación, seguimos los pasos anteriores para crear la primera validación, y, en la ventana origen, ponemos el nombre de rango que acabamos de crear.
Aceptamos.
Seleccionamos una provincia, y, un producto.
Ahora, vamos a calcular el total para la provincia, y, producto seleccionado, para ello, ponemos el signo igual, seguido de la función FILTRAR, y, abrimos un paréntesis.
=FILTRAR(
Como argumento array, seleccionamos la columna total de la hoja Sevilla.
=FILTRAR(Sevilla[Total];
Como argumento include, abrimos un paréntesis para poner la primera condición, la cual debe de ser que la columna provincia de la hoja Sevilla, debe de ser igual a la provincia seleccionada, y, cerramos paréntesis.
=FILTRAR(Sevilla[Total];(Sevilla[Provincia]=Hoja1!B3)
Ponemos el símbolo de asterisco, para poner la siguiente condición.
Abrimos paréntesis, y, la columna de producto de la hoja de Sevilla debe de ser igual al producto seleccionado, cerramos paréntesis de la condición, y, paréntesis de la función.
=FILTRAR(Sevilla[Total];(Sevilla[Provincia]=Hoja1!B3)*(Sevilla[Producto]=Hoja1!C3))
Nos devuelve un error de cálculo, que es correcto.
Porque hemos seleccionado la provincia de Huelva, y, estamos buscando en la hoja de Sevilla.
Esto lo vamos a arreglar con la función INDIRECTO, que convierte el valor de una celda en una referencia real, entonces, voy a sustituir la provincia de Sevilla por INDIRECTO de la celda B3, concatenado entre comillas dobles, con el nombre de la columna.
=FILTRAR(INDIRECTO(B3&"[Total]");(INDIRECTO(B3&"[Provincia]")=Hoja1!B3)*(INDIRECTO(B3&"[Producto]")=Hoja1!C3))
Tenemos una matriz desbordada con los totales para la provincia y el producto seleccionado.
Después del signo igual, ponemos la función SUMA, para obtener el total, y, lo ponemos en formato de moneda, sin decimales.
=SUMA(FILTRAR(INDIRECTO(B3&"[Total]");(INDIRECTO(B3&"[Provincia]")=Hoja1!B3)*(INDIRECTO(B3&"[Producto]")=Hoja1!C3)))
Si cambiamos de provincia, y, producto veremos el total.
Ahora, quiero el total de la provincia seleccionada, para ello, voy a usar la función SUMA, pero de nuevo, con la función INDIRECTO.
La sintaxis quedaría:
=SUMA(INDIRECTO(B3&"[Total]"))
Tenemos el total de todos los productos vendidos en la provincia seleccionada.
En la celda siguiente al total general, voy a dividir la cantidad entre 2.
=B7/2
Arrastro dos veces.
Vamos a crear el cuadro dinámico, para establecer la letra que corresponde.
En la celda contigua ponemos el 100%.
Volvemos a dividir este porcentaje entre 2, y, arrastramos.
Ya tenemos el porcentaje que corresponde con cada cantidad, y, en la celda contigua, ponemos las siguientes letras.
Lo siguiente es que aparezca el estado, lo primero que quiero conocer es la posición del total dentro del cuadro de cantidades, para ello, voy a usar la función COINCIDIRX, donde el valor buscado en el total, lo debe de buscar dentro de las cantidades, con una coincidencia exacta, y, si no lo hay, que me devuelva el siguiente elemento menor.
=COINCIDIRX(D3;B7:B10;-1)
Nos devuelve la posición 3, que es el siguiente elemento menor.
Ahora, con la función INDICE, voy a traerme el estado, donde la función COINCIDIRX, es el argumento numero de fila.
=INDICE(D7:D10;COINCIDIRX(D3;B7:B10;-1))
Nos devuelve el estado C.
Si elijo TV 32 pulgadas, me devuelve en estado D.
Lo siguiente es saber en que centros comerciales, se vendieron dicho producto, lo primero es extraer los centros únicos de la provincia seleccionada, por lo que voy a usar la función UNICOS, junto con INDIRECTO, para extraer los centros únicos de la provincia seleccionada.
=UNICOS(INDIRECTO(B3&"[centro]"))
Tenemos una matriz desbordada con los centros únicos.
Para el total, voy a usar la función SUMAR.SI, pero igual que antes, debo de usar INDIRECTO.
=SUMAR.SI(INDIRECTO(B3 & "[centro]");H3#;INDIRECTO(B3 & "[Total]"))
Ya tenemos el total para centro del producto seleccionado, que, si sumamos las cantidades, vemos que es igual al total obtenido.
Lo siguiente es ir a la pestaña insertar, desde iconos, dentro del grupo ilustraciones, voy a insertar cinco personas, con posiciones distintas, y, las voy a recortar, voy a colocar cada persona dentro de una celda.
Selecciono una persona, y, pulso CTRL más C, para copiar.
Cada foto tiene un nombre.
Voy a seleccionar tanto los nombres, como las fotos, y, voy a crear un nombre de rango para cada imagen, para ello, voy a la pestaña de fórmulas, dentro del grupo nombres definidos, clic en crear desde la selección.
En la ventana que se abre, seleccionamos fila superior, y, aceptamos.
Ahora, copio una imagen, pero he de seleccionar la celda, no la imagen, voy a la hoja de resultados, y, pego con imagen vinculada, para ello, hacemos clic con botón alternativo de ratón, desplegamos pegado especial, y, seleccionamos imagen vinculada.
Para que esta imagen, se actualice según el estado, vamos a crear un nombre de rango, para ello, vamos a la pestaña de fórmulas, dentro del grupo nombres definidos, hacemos clic en asignar nombre.
Le damos un nombre, y, en la ventana se refiere a, usamos la función INDIRECTO, haciendo referencia a la celda F3, y lo concatenamos con el guion bajo.
Es decir, va a buscar el valor de la celda F3, y, buscara si hay un nombre de rango llamado igual, si lo hay lo cargara.
Vamos a la hoja de resultados, seleccionamos la imagen, y, en la barra de fórmulas, borramos lo que hay, y, ponemos el nombre de rango, que acabamos de crear.
Vemos como ahora la imagen cambia según el estado.
Comments