Samenvatting
Om het laagste tij op een maandag te vinden, kunt u, uitgaande van een set gegevens met vele dagen met hoog- en laagwater, een matrixformule gebruiken op basis van de ALS- en MIN-functies. In het getoonde voorbeeld is de formule in I6:
(=MIN(IF(day=I5,IF(tide="L",pred))))
die het laagste getij van maandag in de gegevens retourneert, -0,64
Om de datum van het laagste getij op maandag te achterhalen, is de formule in I7:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Waar het werkblad de volgende benoemde bereiken bevat: datum (B5: B124), dag (C5: C124), tijd (D5: D124), pred (E5: E124), tij (F5: F124).
Beide zijn matrixformules en moeten worden ingevoerd met control + shift + enter.
Gegevens van tidesandcurrents.noaa.gov voor Santa Cruz, Californië.
Uitleg
Op een hoog niveau gaat dit voorbeeld over het vinden van een minimumwaarde op basis van meerdere criteria. Om dat te doen, gebruiken we de MIN-functie samen met twee geneste IF-functies:
(=MIN(IF(day=I5,IF(tide="L",pred))))
werkend van binnen naar buiten, controleert de eerste IF of de dag "Mon" is, gebaseerd op de waarde in I5:
IF(day=I5 // is day "Mon"
Als het resultaat WAAR is, voeren we nog een IF uit:
IF(tide="L",pred) // if tide is "L" return prediction
Met andere woorden, als de dag "ma" is, controleren we of het tij "L" is. Als dit het geval is, retourneren we het voorspelde getijdenpeil met behulp van het genoemde bereik pred .
Merk op dat we voor geen van beide IF een "waarde indien onwaar" opgeven. Dat betekent dat als een van de logische tests FALSE is, de buitenste IF FALSE zal retourneren. Zie dit artikel voor meer informatie over geneste IF's.
Het is belangrijk om te begrijpen dat de gegevensset 120 rijen bevat, dus elk van de benoemde bereiken in de formule bevat 120 waarden. Dit is wat dit een matrixformule maakt - we verwerken veel waarden tegelijk. Nadat beide IF's zijn geëvalueerd, retourneert de buitenste IF een array met 120 waarden zoals deze:
(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)
Het belangrijkste dat hier moet worden opgemerkt, is dat alleen waarden die zijn gekoppeld aan maandag en eb de reis door de geneste IF's overleven. De andere waarden zijn vervangen door FALSE. Met andere woorden, we gebruiken de dubbele IF-structuur om waarden weg te gooien waarin we niet geïnteresseerd zijn.
De bovenstaande array wordt rechtstreeks teruggestuurd naar de MIN-functie. De MIN-functie negeert automatisch de FALSE-waarden en retourneert de minimumwaarde van de resterende waarden, -0,64.
Dit zijn matrixformules en moeten worden ingevoerd met control + shift + enter.
Minimum met MINIFS
Als u Office 365 of Excel 2019 heeft, kunt u de MINIFS-functie gebruiken om het laagste getij op maandag als volgt te krijgen:
=MINIFS(pred,day,"Mon",tide,"L")
Het resultaat is hetzelfde en voor deze formule is geen controle + shift + enter vereist.
Haal de datum
Als je eenmaal het minimale getijdenpeil van maandag hebt gevonden, wil je ongetwijfeld de datum en tijd weten. Dit kan worden gedaan met een INDEX- en MATCH-formule. De formule in I7 is:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Als we van binnen naar buiten werken, moeten we eerst de positie van het laagste getij op maandag lokaliseren met de MATCH-functie:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Hier doorlopen we dezelfde voorwaardelijke tests die we hierboven hebben toegepast om de verwerking te beperken tot alleen maandag eb. We passen echter nog een test toe om de resultaten nu in I6 tot de minimumwaarde te beperken, en we gebruiken een iets eenvoudigere syntaxis op basis van booleaanse logica om criteria toe te passen. We hebben drie afzonderlijke uitdrukkingen, die elk één voorwaarde testen:
(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
Dit is een voorbeeld dat de flexibiliteit van XLOOKUP mooi laat zien. We kunnen exact dezelfde logica van de INDEX- en MATCH-formules hierboven gebruiken, in een eenvoudige en elegante formule.
