Sommaire
Pour trouver la marée la plus basse un lundi, étant donné un ensemble de données avec plusieurs jours de marées hautes et basses, vous pouvez utiliser une formule matricielle basée sur les fonctions IF et MIN. Dans l'exemple illustré, la formule en I6 est:
(=MIN(IF(day=I5,IF(tide="L",pred))))
qui renvoie la plus basse marée du lundi dans les données, -0,64
Pour récupérer la date de la plus basse marée du lundi, la formule en I7 est:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Où la feuille de calcul contient les plages nommées suivantes: date (B5: B124), jour (C5: C124), heure (D5: D124), pred (E5: E124), marée (F5: F124).
Les deux sont des formules matricielles et doivent être saisies avec Ctrl + Maj + Entrée.
Données tirées de tidesandcurrents.noaa.gov pour Santa Cruz, Californie.
Explication
À un niveau élevé, cet exemple consiste à trouver une valeur minimale basée sur plusieurs critères. Pour ce faire, nous utilisons la fonction MIN avec deux fonctions IF imbriquées:
(=MIN(IF(day=I5,IF(tide="L",pred))))
en travaillant de l’intérieur vers l’extérieur, le premier IF vérifie si le jour est «Mon», en se basant sur la valeur en I5:
IF(day=I5 // is day "Mon"
Si le résultat est TRUE, nous exécutons un autre IF:
IF(tide="L",pred) // if tide is "L" return prediction
En d'autres termes, si le jour est "Lun", nous vérifions si la marée est "L". Si tel est le cas, nous renvoyons le niveau de marée prévu, en utilisant la plage nommée pred .
Notez que nous ne fournissons pas de «valeur si faux» pour l'un ou l'autre IF. Cela signifie que si l'un des tests logiques est FALSE, le IF externe retournera FALSE. Pour plus d'informations sur les IF imbriquées, consultez cet article.
Il est important de comprendre que l'ensemble de données comprend 120 lignes, de sorte que chacune des plages nommées dans la formule contient 120 valeurs. C'est ce qui en fait une formule matricielle - nous traitons plusieurs valeurs à la fois. Une fois que les deux IF sont évalués, le IF externe renvoie un tableau contenant 120 valeurs comme ceci:
(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 chose clé à noter ici est que seules les valeurs associées au lundi et à la marée basse survivent au voyage à travers les FI imbriquées. Les autres valeurs ont été remplacées par FALSE. En d'autres termes, nous utilisons la structure double IF pour «rejeter» les valeurs qui ne nous intéressent pas.
Le tableau ci-dessus est renvoyé directement à la fonction MIN. La fonction MIN ignore automatiquement les valeurs FALSE et renvoie la valeur minimale de celles qui restent, -0,64.
Il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
Minimum avec MINIFS
Si vous avez Office 365 ou Excel 2019, vous pouvez utiliser la fonction MINIFS pour obtenir la marée la plus basse du lundi comme ceci:
=MINIFS(pred,day,"Mon",tide,"L")
Le résultat est le même et cette formule ne nécessite pas de contrôle + Maj + Entrée.
Obtenez la date
Une fois que vous avez trouvé le niveau minimum de la marée du lundi, vous voudrez sans aucun doute connaître la date et l'heure. Cela peut être fait avec une formule INDEX et MATCH. La formule en I7 est:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
En travaillant de l'intérieur vers l'extérieur, nous devons d'abord localiser la position de la marée la plus basse du lundi avec la fonction MATCH:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Ici, nous effectuons les mêmes tests conditionnels que nous avons appliqués ci-dessus pour limiter le traitement aux marées basses du lundi uniquement. Cependant, nous appliquons un test supplémentaire pour limiter les résultats à la valeur minimale maintenant dans I6, et nous utilisons une syntaxe légèrement plus simple basée sur la logique booléenne pour appliquer les critères. Nous avons trois expressions distinctes, chacune testant une condition:
(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
C'est un exemple qui montre bien la flexibilité de XLOOKUP. Nous pouvons utiliser exactement la même logique des formules INDEX et MATCH ci-dessus, dans une formule simple et élégante.
