Explicación
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 SI y MIN. En el ejemplo que se muestra, la fórmula en I6 es:
que devuelve la marea más baja del lunes en los datos, -0.64
Para recuperar la fecha de la marea más baja del lunes, la fórmula en I7 es:
Donde la hoja de trabajo contiene los siguientes rangos nombrados: 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.
Cómo funciona esta fórmula
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 SI anidadas:
trabajando desde adentro hacia afuera, el primer SI verifica si el día es "Mon", basado en el valor en I5:
SI(day=I5 // is day "Mon"
Si el resultado es VERDADERO, ejecutamos otro SI:
SI(tide="L",pred) // if tide is "L" return prediction
En otras palabras, si el día es "lunes", verificamos si la marea es "L". Si es así, devolvemos el nivel de marea predicho, usando el rango pred pred.
Tenga en cuenta que no proporcionamos un "valor si es falso" para ninguno de los SI. Eso significa que si cualquiera de las pruebas lógicas es FALSO, el SI externo devolverá FALSO. Para obtener más información sobre los SI anidados, consulte este artículo.
Es importante comprender que el conjunto de datos incluye 120 filas, por lo que cada uno de los rangos con nombre 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 evaluar ambos SI, el SI externo devolverá una matriz que contiene 120 valores como este:
{FALSO;FALSO;FALSO;FALSO;FALSO;3.27;FALSO;0.3;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;2.02;FALSO;0.17;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;3.04;FALSO;-0.55;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;1.96;FALSO;-0.64;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;3;FALSO;-0.02;FALSO;FALSO;FALSO;FALSO}
Lo 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 SI anidados. Los otros valores han sido reemplazados por FALSO. En otras palabras, estamos utilizando la estructura de doble SI para "tirar" 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 FALSO y devuelve el valor mínimo de los que quedan, -0.64.
Esta es una matriz de fórmulas 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 de los lunes de esta manera:
=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:
Trabajando de adentro hacia afuera, primero debemos ubicar la posición de la marea más baja del lunes con la función COINCIDIR:
COINCIDIR(1,(day=I5)*(tide="L")*(pred=I6),0))
Aquí, ejecutamos las mismas pruebas condicionales que aplicamos anteriormente para restringir el procesamiento a las mareas bajas de los lunes solamente. 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 la lógica booleana para aplicar criterios. Tenemos tres expresiones separadas, cada una probando una condición:
(day=I5)* // day is "Mon" (tide="L")* // tide is "L" (pred=I6) // prediction is min value
Cada una de estas expresiones se ejecuta en 120 valores y devuelve una matriz de 120 resultados VERDADERO FALSO. Cuando estas matrices se multiplican entre sí, los valores VERDADERO FALSO se convierten en 1s y 0s. El resultado es una matriz única como esta:
{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}
Debido a que solo hay un valor en todo el conjunto de datos que cumple con las tres condiciones, solo hay un 1 en la matriz.
Ahora puede ver por qué hemos configurado la función COINCIDIR para buscar el número 1 en el modo de coincidencia exacta. COINCIDIR localiza el 1 y devuelve una posición de 88 directamente a la función INDICE. Ahora podemos reescribir la fórmula de esta manera:
=INDICE(date,88) // returns 23-Dec-19
La función INDICE devuelve el valor 88 en la fecha de rango con nombre, que es el 23 de diciembre de 19. Esta es la fecha que corresponde al nivel de marea más bajo del lunes.
Esta es una matriz de fórmulas y debe ingresarse con control + shift + enter.
Obtener el tiempo
La fórmula para recuperar la hora de la marea más baja del lunes es casi la misma que la fórmula para obtener la fecha. La única diferencia es que el intervalo de tiempo especificado se proporciona a INDICE en lugar de la fecha. La fórmula en I8 es:
En otros aspectos, el comportamiento de la fórmula es el mismo, por lo que terminamos con un resultado similar:
=INDICE(time,88) // returns 2:44 PM
Como antes, INDICE devuelve el elemento 88 en la matriz, que es 2:44 PM.
Esta es una matriz de fórmulas y debe ingresarse con control + shift + enter.
Nota: en caso de empate (dos mareas bajas los lunes con el mismo valor), las fórmulas INDICE y COINCIDIR anteriores devolverán el primer partido.
Fecha y hora con XLOOKUP
Con la función XLOOKUP, puede simplificar las fórmulas utilizadas para obtener la fecha y la hora asociadas con la marea más baja:
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.
0 comentarios:
Publicar un comentario