En el siguiente ejemplo, tenemos el campeón de liga de la liga española desde el año 1979 hasta el año 2022.
Tenemos la temporada, el equipo que gano la liga, la jornada, el nombre del equipo, y, los puntos.
Queremos crear una validación de datos, para poder elegir una temporada, crear otra validación para elegir otra temporada, y, que nos devuelva un informe entre la primera temporada seleccionada, y, la segunda temporada seleccionada.
Empecemos creando la primera validación, lo haremos en la celda H4, para ello, vamos a la pestaña datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Desplegamos permitir, y, seleccionamos lista, y, en la ventana origen, seleccionamos las temporadas.
Aceptamos.
Seleccionamos una temporada.
Ahora, vamos a por la segunda validación, pero, no podemos elegir una temporada anterior, por lo que en la celda Q3, voy a usar la función FILTRAR, donde como argumento array, seleccionamos las temporadas.
=FILTRAR(B4:B46;
Como argumento include, volvemos a seleccionar las temporadas, ponemos el símbolo de comparación mayor, y, seleccionamos la celda H4.
Cerramos paréntesis, y, aceptamos.
=FILTRAR(B4:B46;B4:B46>H4;"")
Obtenemos una matriz desbordada con las temporadas a partir de la siguiente que hemos seleccionado.
Lo vamos a hacer de diferentes formas, la primera de ellas va a ser con la función FILTRAR, donde como argumento array, seleccionamos las temporadas (B4:B46).
=FILTRAR(B4:F46;
Como argumento include, vamos a poner dos condiciones, por lo que abrimos un paréntesis, seleccionamos las temporadas, ponemos el operador de comparación mayor o igual, y, señalamos la celda H4.
Cerramos paréntesis.
=FILTRAR(B4:F46;(B4:B46>=H4)
Ponemos el símbolo de asterisco, que es igual que el operador lógico Y, quiere decir, que devuelve VERDADERO si todas las condiciones se cumplen, abrimos otro paréntesis, y, ponemos la siguiente condición, donde volvemos a seleccionar las temporadas, ponemos el operador de comparación menor o igual, y, seleccionamos la celda la celda H7.
Cerramos paréntesis de la condición, y, de FILTRAR.
Aceptamos.
=FILTRAR(B4:F46;(B4:B46>=H4)*(B4:B46<=H7))
Obtenemos una matriz desbordada con el informe de las temporadas entre la primera y segunda selección.
Veamos otra forma de hacerlo.
En una celda, voy a usar la función COINCIDIR, para encontrar la primera temporada seleccionada en el rango de temporadas, para ello, escribo el signo igual, seguido de la función, y, abro un paréntesis.
Como argumento valor buscado, seleccionaos la primera temporada seleccionada.
=COINCIDIR(H4;
Como argumento matriz buscada, seleccionamos las temporadas.
=COINCIDIR(H4;B4:B46
Ponemos una coincidencia exacta, cerramos paréntesis, y, aceptamos.
=COINCIDIR(H4;B4:B46;0)
Nos devuelve la posición 3.
Voy a volver a usar la función COINCIDR para encontrar la posición de la temporada seleccionada en la segunda validación de datos.
=COINCIDIR(H7;B4:B46;0)
Nos devuelve la posición 12.
Si restamos ambas cantidades, nos devuelve 9, pero si contamos nueve a partir de la primera selección, vemos que faltaría 1.
Por lo que, a la resta, debemos de sumar 1, la función quedaría:
=(COINCIDIR(H7;B4:B46;0)-COINCIDIR(H4;B4:B46;0))+1
Nos devuelve 10.
Ahora, con la función INDICE, vamos a rescatar dichas filas, para ello, ponemos la función, abrimos un paréntesis, y, como argumento matriz, seleccionamos las temporadas.
=INDICE(B4:F46
Como argumento numero de fila, sería el resultado de COINCIDIR, pero si ponemos el resultado que es 10, nos devolverá la fila 10, por lo que vamos a usar la función SECUENCIA, para que nos cree una matriz desbordada, donde como argumento filas, es la resta.
=INDICE(B4:F46;SECUENCIA((COINCIDIR(H7;B4:B46;0)-COINCIDIR(H4;B4:B46;0))+1
Omitimos el argumento columnas, como argumento inicio, es el resultado del primer COINCIDIR, y, cerramos paréntesis.
=INDICE(B4:F46;SECUENCIA((COINCIDIR(H7;B4:B46;0)-COINCIDIR(H4;B4:B46;0))+1;;COINCIDIR(H4;B4:B46;0))
Con esto, obtenemos una matriz desbordada de 10 filas, empezando desde el numero 3, pues, este es el argumento numero de fila de la función INDICE.
Punto y coma, ahora viene el argumento número de columna, donde vamos a usar una constante de matriz, para que nos devuelva las cinco columnas.
La separación entra cada numero es la barra invertida, porque los valores a rescatar son columnas.
Cerramos paréntesis, y, aceptamos.
=INDICE(B4:F46;SECUENCIA((COINCIDIR(H7;B4:B46;0)-COINCIDIR(H4;B4:B46;0))+1;;COINCIDIR(H4;B4:B46;0));{1\2\3\4\5})
Obtenemos los mismos resultados que anteriormente.
Ahora, lo vamos a hacer con la función DESREF.
El primer argumento es referencia, es decir, la celda donde vamos a partir, que es la celda B3, los encabezados.
=DESREF(B3
El siguiente argumento es filas, pues es el primer COINCIDIR que hemos usado para obtener la posición de la temporada seleccionada en la primera validación.
=DESREF(B3;COINCIDIR(H4;B4:B46;0)
El siguiente argumento es columnas, no nos vamos a mover ninguna columna, por lo que lo omitimos, el siguiente argumento es alto, que es la resta de los COINCIDIR más uno.
=DESREF(B3;COINCIDIR(H4;B4:B46;0);;(COINCIDIR(H7;B4:B46;0)-COINCIDIR(H4;B4:B46;0))+1
Y, como argumento ancho, ponemos 5, que son las cinco columnas que necesitamos, cerramos paréntesis, y, aceptamos.
=DESREF(B3;COINCIDIR(H4;B4:B46;0);;(COINCIDIR(H7;B4:B46;0)-COINCIDIR(H4;B4:B46;0))+1;5)
Obtenemos los mismos resultados.
Ahora, lo vamos a hacer con la función BUSCARX.
Si en una celda, pongo la función BUSCARX, donde como primer argumento, que es valor buscado, ponemos la primera validación.
=BUSCARX(H4
Como argumento matriz buscada, seleccionamos las temporadas, y, como argumento matriz devuelta, ponemos de nueva, todas las temporadas, cerramos paréntesis, y, aceptamos.
=BUSCARX(H4;B4:B46;B4:B46)
Obtenemos la temporada seleccionada en la primera validación, bien, ahora ponemos dos puntos (:), y, ponemos otro BUSCARX, como argumento valor buscado, ponemos la celda de la segunda validación.
=BUSCARX(H4;B4:B46;B4:B46):BUSCARX(H7
Como argumento matriz buscada, seleccionamos las temporadas.
=BUSCARX(H4;B4:B46;B4:B46):BUSCARX(H7;B4:B46
Como argumento matriz devuelta, ponemos desde B4 a F6, para que nos devuelva las columnas que necesitamos.
Cerramos paréntesis, y, aceptamos.
=BUSCARX(H4;B4:B46;B4:B46):BUSCARX(H7;B4:B46;B4:F46)
Y, obtenemos los mismos resultados.
Por último, vamos a crear una regla de formato condicional, para que en el modelo queden resaltas las filas que cumplan las condiciones, para ello, seleccionamos el modelo, excepto el encabezado, vamos a la pestaña de inicio, desplegamos formato condicional, y, seleccionamos nueva regla.
Se abre la ventana nueva regla de formato, seleccionamos la ultima opción, que es la que nos permite introducir una formula, o, función.
Vamos a usar el operador lógico Y, donde preguntamos si el valor de B4 es mayor o igual al valor de la celda H4, y, además, es menor o igual al valor de la celda H7, ponemos dólar delante de B4, para que se resalte la fila completa, le damos un formato, y, aceptamos.
Vemos como se han resaltado las filas que cumplen las condiciones.
Commentaires