Resumen
Para encontrar la marea más baja en un lunes, dado un conjunto de datos con muchos días de mareas altas y bajas, puede usar una fórmula de matriz basada en las funciones IF y MIN. En el ejemplo que se muestra, la fórmula en I6 es:
(=MIN(IF(day=I5,IF(tide="L",pred))))
que devuelve la marea del lunes más baja en los datos, -0,64
Para recuperar la fecha de la marea más baja del lunes, la fórmula en I7 es:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Donde la hoja de trabajo contiene los siguientes rangos con nombre: fecha (B5: B124), día (C5: C124), hora (D5: D124), pred (E5: E124), marea (F5: F124).
Ambas son fórmulas de matriz y deben ingresarse con control + shift + enter.
Datos de tidesandcurrents.noaa.gov para Santa Cruz, California.
Explicación
En un nivel alto, este ejemplo trata de encontrar un valor mínimo basado en múltiples criterios. Para hacer eso, estamos usando la función MIN junto con dos funciones IF anidadas:
(=MIN(IF(day=I5,IF(tide="L",pred))))
trabajando de adentro hacia afuera, el primer IF comprueba si el día es "Lun", según el valor en I5:
IF(day=I5 // is day "Mon"
Si el resultado es VERDADERO, ejecutamos otro SI:
IF(tide="L",pred) // if tide is "L" return prediction
En otras palabras, si el día es "Lun", comprobamos si la marea es "L". Si es así, devolvemos el nivel de marea previsto, utilizando el rango con nombre pred .
Tenga en cuenta que no proporcionamos un "valor si es falso" para ninguno de los IF. Eso significa que si alguna de las pruebas lógicas es FALSA, el IF externo devolverá FALSE. Para obtener más información sobre los IF anidados, consulte este artículo.
Es importante comprender que el conjunto de datos incluye 120 filas, por lo que cada uno de los rangos nombrados en la fórmula contiene 120 valores. Esto es lo que hace que esta sea una fórmula de matriz: estamos procesando muchos valores a la vez. Después de que se evalúen ambos IF, el IF externo devolverá una matriz que contiene 120 valores como este:
(FALSE;FALSE;FALSE;FALSE;FALSE;3.27;FALSE;0.3;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;2.02;FALSE;0.17;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;3.04;FALSE;-0.55;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;1.96;FALSE;-0.64;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;3;FALSE;-0.02;FALSE;FALSE;FALSE;FALSE)
La clave a tener en cuenta aquí es que solo los valores asociados con el lunes y la marea baja sobreviven al viaje a través de los IF anidados. Los otros valores se han reemplazado por FALSE. En otras palabras, estamos usando la estructura de doble IF para "desechar" los valores que no nos interesan.
La matriz anterior se devuelve directamente a la función MIN. La función MIN ignora automáticamente los valores FALSE y devuelve el valor mínimo de los que quedan, -0,64.
Esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
Mínimo con MINIFS
Si tiene Office 365 o Excel 2019, puede usar la función MINIFS para obtener la marea más baja del lunes como esta:
=MINIFS(pred,day,"Mon",tide,"L")
El resultado es el mismo, y esta fórmula no requiere control + shift + enter.
Obtener la fecha
Una vez que encuentre el nivel mínimo de marea del lunes, sin duda querrá saber la fecha y la hora. Esto se puede hacer con una fórmula INDICE y COINCIDIR. La fórmula en I7 es:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Trabajando de adentro hacia afuera, primero debemos ubicar la posición de la marea más baja del lunes con la función COINCIDIR:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Aquí, realizamos las mismas pruebas condicionales que aplicamos anteriormente para restringir el procesamiento solo a las mareas bajas del lunes. Sin embargo, aplicamos una prueba más para restringir los resultados al valor mínimo ahora en I6, y usamos una sintaxis ligeramente más simple basada en lógica booleana para aplicar criterios. Tenemos tres expresiones separadas, cada una de las cuales prueba una condición:
(day=I5)* // day is "Mon" (tide="L")* // tide is "L" (pred=I6) // prediction is min value
Each of these expressions runs on 120 values and returns an array of 120 TRUE FALSE results. When these arrays are multiplied by one another, the TRUE FALSE values are coerced to 1s and 0s. The result is a single array like this:
(0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0)
Because there is only one value in the entire data set that meets all three conditions, there is only a single 1 in the array.
Now you can see why we have configured the MATCH function to look for the number 1 in exact match mode. MATCH locates the 1, and returns a position of 88 directly to the INDEX function. We can now rewrite the formula like this:
=INDEX(date,88) // returns 23-Dec-19
The INDEX function then returns the 88th value in the named range date, which is 23-Dec-19. This is the date that corresponds to the lowest Monday tide level.
This is an array formulas and must be entered with control + shift + enter.
Get the time
The formula to retrieve the time of the lowest Monday tide is almost the same as the formula to get the date. The only difference is that the named range time is provided to INDEX instead of date. The formula in I8 is:
(=INDEX(time,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
In other respects the behavior of the formula is the same, so we end up with a similar result:
=INDEX(time,88) // returns 2:44 PM
As before, INDEX returns the 88th item in the array, which is 2:44 PM.
This is an array formulas and must be entered with control + shift + enter.
Note: in the event of a tie (two Monday low tides with the same value), the INDEX and MATCH formulas above will return the first match.
Date and time with XLOOKUP
With the XLOOKUP function, you can simplify the formulas used to get the date and time associated with the lowest tide:
=XLOOKUP(1,(day=I5)*(tide="L")*(pred=I6),date) // get date =XLOOKUP(1,(day=I5)*(tide="L")*(pred=I6),time) // get time
Este es un ejemplo que muestra muy bien la flexibilidad de XLOOKUP. Podemos usar exactamente la misma lógica de las fórmulas INDICE y COINCIDIR anteriores, en una fórmula simple y elegante.
