Para el siguiente ejemplo, tenemos una serie de provincias, unos centros asignados a cada provincia, y, en horizontal tenemos años, y, debajo las ventas para cada año, provincia, y, centro.
En la celda K1, tenemos las siguientes provincias.
Asturias,Cadiz,Sevilla,Huelva
En la celda K2, el siguiente centro comercial.
C.C. Nervión
Y, en la celda K3, los siguientes años.
2018,2020
Queremos calcular el total para dichas provincias, centro, y, años.
Si en una celda uso la función HALLAR, que no distingue entre mayúsculas, y, minúsculas, donde como argumento texto buscado, selecciono las provincias, y, como argumento dentro del texto, selecciono la celda K1.
Obtengo una matriz desbordada con error donde no se encuentra ninguna de las provincias, y, la posición de la primera letra donde se encuentra la provincia.
Ahora, después del signo igual, uso la función ESNUMERO, que devolverá VERDADERO si es número, y, FALSO si no lo es.
Pero, debemos de trabajar con ceros, y, unos, ya que VERDADERO, y, FALSO nos daría error, para ello, antes de la función, ponemos el doble signo negativo.
=--ESNUMERO(HALLAR(B2:B21;K1))
Obtenemos una matriz desbordada con 1 donde era VERDADERO, y, FALSO donde era 0.
Lo siguiente es usar la función FILTRAR, donde como argumento array, es todo el modelo, sin encabezados, y, como argumento Include es la expresión anterior, me va a devolver todas las filas que sean igual a 1 (VERDADERO).
=FILTRAR(B2:H21;--ESNUMERO(HALLAR(B2:B21;K1)))
Ahora, vamos por el centro, debemos de añadir una nueva condición a la función FILTRAR, por lo que la condición anterior, la voy a poner entre paréntesis, ponemos el símbolo de asterisco, abrimos otro paréntesis, seleccionamos la columna de centro, y, la igualamos al valor de K2, cerramos paréntesis, y, aceptamos.
=FILTRAR(Ventas;(ESNUMERO(HALLAR(Ventas[Provincias];K1)))*(Ventas[Centro]=K2))
Vemos que se ha reducido la lista a dos registros.
Vamos por los años, vamos a hacer lo mismo que hemos hecho anteriormente, usamos la función ESNUMERO, abrimos un paréntesis, ponemos la función HALLAR, abrimos otro paréntesis, como argumento texto buscado, seleccionamos los años, como argumento dentro del texto, seleccionamos la celda con los años, cerramos paréntesis, y, aceptamos.
=ESNUMERO(HALLAR(Ventas[[#Encabezados];[2017]:[2021]];K3))
Obtenemos una matriz desbordada con VERDADERO donde hay coincidencia, y, FALSO donde no la hay.
Pero, la función FILTRAR, no puede trabajar con VERDADEROS, y, FALSOS, debemos de convertirlos en número, para ello, antes de la función ESNUMERO, ponemos el doble signo negativo.
=--ESNUMERO(HALLAR(Ventas[[#Encabezados];[2017]:[2021]];K3))
Ahora, tenemos el valor 1 donde había VERDADERO, y, el valor 0, donde había FALSO.
Si la siguiente expresión:
FILTRAR(Ventas;(ESNUMERO(HALLAR(Ventas[Provincias];K1)))*(Ventas[Centro]=K2))
La filtro, y, como argumento Include, ponemos la función ESNUMERO junto con el doble signo negativo, nos va a devolver los encabezados de los años cuyo valor sea igual a 1.
=FILTRAR(FILTRAR(B2:H21;(--ESNUMERO(HALLAR(B2:B21;K1))=1)*(C2:C21=K2));--ESNUMERO(HALLAR(B1:H1;K3)))
En la celda K7, uso la función DIVIDIRTEXTO, donde como argumento texto, seleccionamos la celda con los años.
=DIVIDIRTEXTO(K3
Punto y coma, y, como argumento delimitador de columna, entre comillas dobles, ponemos la coma, cerramos paréntesis y aceptamos.
=DIVIDIRTEXTO(K3;",")
Ya tenemos el encabezado de los años.
Ahora, en la celda J8, usamos la función FILTRAR, como lo hemos hecho antes, para filtrar las provincias.
=FILTRAR(B2:B21;(ESNUMERO(HALLAR(B2:B21;K1)))*(C2:C21=K2))
Ya tenemos los encabezados de provincias.
Pues, ya tenemos nuestro ejercicio terminado.
留言