Estamos trabajando con el modelo que suelo usar habitualmente, y, queremos realizar un filtro por cinco criterios.
Podemos usar un filtro avanzado, lo cual lo vamos a hacer, pero imaginemos que no podemos acceder a filtro avanzado, por lo que también lo haremos con fórmulas.
Queremos filtrar por las ventas del producto aspiradora, vendida en el centro comercial Nervión, en la provincia de Sevilla, entre las fechas 01/01/2018, y, 30/06/2018, además, la venta de frigorífico, en el centro comercial Metromar, en la provincia de Jaén, en las fechas 01/01/2019, y, 30/06/2019.
Podemos hacerlo usando los filtros de cada columna, ya que esta en formato de tabla, para ello, desplegamos el filtro de la columna fecha, vamos hasta filtros de fecha, y, seleccionamos personalizada.
Se abre la ventana de autofiltro personalizado, en la primera ventana de la izquierda, seleccionamos es posterior o igual a, en la ventana de la derecha, ponemos la primera fecha, en la segunda ventana de la izquierda, seleccionamos es anterior o igual a, y, en la ventana de la derecha, seleccionamos la fecha de fin.
Aceptamos, y, vemos como el modelo ha sido filtrado por dichas fechas.
Ahora, debemos de ir filtrando las siguientes columnas por provincia, centro, y, producto.
De momento, solo hemos filtrado por la primera fecha, nos quedaría filtrar por el segundo grupo de fechas, para ello, necesitamos usar el filtro avanzado, que es la diferencia entre un filtro, y, otro, para usar el filtro avanzado, debemos de tener el mismo encabezado que el del modelo de datos, y, en las siguientes líneas, poner los criterios.
Cuando usamos, como en este caso, dos veces la fecha, debemos de repetir en encabezado.
Vamos a realizar el filtro avanzado, para ello, con una celda dentro del modelo, vamos a la pestaña de datos, dentro del grupo ordenar y filtrar, hacemos clic en avanzadas.
Se abre la ventana filtro avanzado, donde vemos que en la ventana rango de la lista, aparece el modelo.
En la ventana rango de criterios, seleccionamos los criterios, incluido el encabezado.
Aceptamos.
Vemos que tenemos tres registros, que cumplen con las condiciones.
Ahora, vamos a ver cómo hacerlo con fórmulas, será un poco larga.
Tenemos que preguntar si la fecha es mayor o igual al valor de la celda G2, o, G3, es decir, vale con que se cumpla una de las condiciones, podemos usar el operador lógico O, pero no podríamos hacerlo de forma matricial, pero, si lo hacemos usando el signo (+), que es igual que el operador lógico O, no nos dará los resultados correctos por lo que vamos a abrir un paréntesis, y, ponemos la primera condición, para ello, seleccionamos la columna de fecha, y, comparamos con que sea mayor o igual al valor de G2, y, cerramos paréntesis.
=(Ventas3[Fecha]>=G2)
Ponemos el símbolo de asterisco, abrimos otro paréntesis, y, la siguiente condición, que es la columna fecha sea igual a menor al valor de la celda H2, y, cerramos paréntesis.
=(Ventas3[Fecha]>=G2)*(Ventas3[Fecha]<=H2)
provincia sea igual al valor de la celda I2, y, cerramos paréntesis.
=(Ventas3[Fecha]>=G2)*(Ventas3[Fecha]<=H2)*(Ventas3[Provincia]=I2)
Volvemos a poner el símbolo de asterisco, abrimos otro paréntesis, para la siguiente condición, que la columna centro sea igual al valor de J2, y, cerramos paréntesis.
=(Ventas3[Fecha]>=G2)*(Ventas3[Fecha]<=H2)*(Ventas3[Provincia]=I2)*(Ventas3[Centro]=J2)
Volvemos a poner el símbolo de asterisco, abrimos otro paréntesis, para la última condición, que la columna producto sea igual al valor de la celda K2, y, cerramos paréntesis.
(Ventas3[Fecha]>=G2)*(Ventas3[Fecha]<=H2)*(Ventas3[Provincia]=I2)*(Ventas3[Centro]=J2)*(Ventas3[Producto]=K2)
Obtenemos una matriz desbordada con cero donde no hay coincidencia, y, uno donde la hay.
Solo hay una coincidencia.
Ahora, con el condicional SI, voy a preguntar que si el resultado de la expresión anterior es mayor o igual a 1, que me devuelva la fila de la columna fecha, en caso contrario, que me devuelva un texto en blanco.
=SI((Ventas3[Fecha]>=G2)*(Ventas3[Fecha]<=H2)*(Ventas3[Provincia]=I2)*(Ventas3[Centro]=J2)*(Ventas3[Producto]=K2)>=1;FILA(Ventas34[Fecha]);"")
En este caso, solo devuelve una fila, pero imaginemos que devuelve mas de una fila, vemos en la matriz desbordada que tenemos un numero de fila, y, blancos, los blancos debemos de deshacernos de ellos, tenemos una función llamada K.ESIMO.MENOR, que nos va devolviendo el numero menor que le vayamos indicando, es decir, el primero, el segundo…
La pregunta es ¿Cuántos números tenemos?, para poder rescatarlos, voy a usar la función CONTAR.SI, para que me dé un recuento de las celdas que son mayores a cero.
=CONTAR.SI(G8#;">0")
Nos devuelve 1.
Pero, hemos olvidado que tenemos otra fila de criterios que aun no hemos añadido, por lo que vamos a resolver primero este problema, para ello, tenemos una función llamada APILARV (VSTACK), que nos apila varias columnas en una sola matriz desbordada, para la segunda expresión, seria la misma que la anterior, solo tenemos que cambiar el numero de fila 2 por 3, por lo que podemos copiarla.
Entonces, escribo APILARV, y, abro un paréntesis, como argumento matriz1, es la expresión anterior, como argumento matriz2, pegamos la expresión anterior, y, cambiamos el numero de fila 2 por 3.
=APILARV(SI((Ventas3[Fecha]>=G2)*(Ventas3[Fecha]<=H2)*(Ventas3[Provincia]=I2)*(Ventas3[Centro]=J2)*(Ventas3[Producto]=K2)>=1;FILA(Ventas3[Fecha]);"");SI((Ventas3[Fecha]>=G3)*(Ventas3[Fecha]<=H3)*(Ventas3[Provincia]=I3)*(Ventas3[Centro]=J3)*(Ventas3[Producto]=K3)>=1;FILA(Ventas3[Fecha]);""))
Aceptamos, y, ahora el resultado de CONTAR.SI, es de tres registros.
Ahora, es cuando debemos de ordenar los números de filas de menor a mayor, ya sabemos que tenemos tres números de filas, para ello, usamos K.ESIMO.MENOR, donde como argumento matriz es la expresión anterior.
=K.ESIMO.MENOR(G8#;
Como argumento K, debemos de indicar el numero menor que queremos, pero queremos que sea de forma matricial, ya sabemos el resultado de la función CONTAR.SI, entonces, si uso la función SECUENCIA, y, como argumento filas, uso el resultado de CONTAR.SI, me devolverá los números de filas de menor a mayor.
=K.ESIMO.MENOR(G8#;SECUENCIA(CONTAR.SI(G8#;">0")))
No lo voy a hacer todo en una sola función, porque la primera expresión es larga.
Ya tenemos la fila que debemos de rescatar, para ello, vamos a usar la función INDICE de forma matricial, donde como argumento array, son las columnas desde A hasta E.
=INDICE(A:E;K.
Como argumento numero de fila, es la función K.ESIMO.MENOR.
=INDICE(A:E;K.ESIMO.MENOR(G8#;SECUENCIA(CONTAR.SI(G8#;">0")))
Como argumento numero de columna, queremos que nos devuelva todas las columnas, para ello, usamos una constante de matriz, y, entre llaves ponemos los números desde 1 hasta 5, separado por la barra invertida.
Cerramos paréntesis y aceptamos.
=INDICE(A:E;K.ESIMO.MENOR(G8#;SECUENCIA(CONTAR.SI(G8#;">0")));{1\2\3\4\5})
Ya tenemos nuestro modelo filtrado por todas las condiciones, siendo el mismo resultado que el obtenido con el filtro avanzado.
Comentários