Resumé
For at finde laveste tidevand på en mandag, givet et sæt data med mange dage med høj og lav tidevand, kan du bruge en matrixformel baseret på IF- og MIN-funktionerne. I det viste eksempel er formlen i I6:
(=MIN(IF(day=I5,IF(tide="L",pred))))
der returnerer den laveste mandag tidevand i dataene, -0,64
For at hente datoen for den laveste mandag tidevand er formlen i I7:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Hvor regnearket indeholder følgende navngivne områder: dato (B5: B124), dag (C5: C124), tid (D5: D124), pred (E5: E124), tidevand (F5: F124).
Begge er matrixformler og skal indtastes med kontrol + skift + enter.
Data fra tidesandcurrents.noaa.gov for Santa Cruz, Californien.
Forklaring
På et højt niveau handler dette eksempel om at finde en minimumsværdi baseret på flere kriterier. For at gøre det bruger vi MIN-funktionen sammen med to indlejrede IF-funktioner:
(=MIN(IF(day=I5,IF(tide="L",pred))))
arbejder indefra og ud, kontrollerer den første IF, om dagen er "man", baseret på værdien i I5:
IF(day=I5 // is day "Mon"
Hvis resultatet er SAND, kører vi en anden IF:
IF(tide="L",pred) // if tide is "L" return prediction
Med andre ord, hvis dagen er "man", kontrollerer vi, om tidevandet er "L". I så fald returnerer vi det forudsagte tidevandsniveau ved hjælp af det navngivne interval pred .
Bemærk, at vi ikke angiver en "værdi, hvis falsk" for hverken IF. Det betyder, at hvis en eller anden logisk test er FALSE, returnerer den ydre IF FALSE. For mere information om indlejrede IF'er, se denne artikel.
Det er vigtigt at forstå, at datasættet indeholder 120 rækker, så hvert af de navngivne områder i formlen indeholder 120 værdier. Dette er, hvad der gør dette til en matrixformel - vi behandler mange værdier på én gang. Når begge IF'er er evalueret, returnerer den ydre IF en matrix, der indeholder 120 værdier som denne:
(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)
Den vigtigste ting at bemærke her er kun værdier forbundet med mandag, og lavvande overlever turen gennem de indlejrede IF'er. De andre værdier er erstattet med FALSE. Med andre ord bruger vi den dobbelte IF-struktur til at "smide" værdier, som vi ikke er interesseret i.
Arrayet ovenfor returneres direkte til MIN-funktionen. MIN-funktionen ignorerer automatisk FALSE-værdierne og returnerer minimumværdien af de resterende, -0,64.
Dette er en matrixformel og skal indtastes med kontrol + skift + enter.
Minimum med MINIFS
Hvis du har Office 365 eller Excel 2019, kan du bruge MINIFS-funktionen til at få den laveste mandag tidevand som denne:
=MINIFS(pred,day,"Mon",tide,"L")
Resultatet er det samme, og denne formel kræver ikke kontrol + skift + enter.
Få datoen
Når du har fundet det mindste tidevandsniveau på mandag, vil du utvivlsomt gerne vide dato og klokkeslæt. Dette kan gøres med en INDEX- og MATCH-formel. Formlen i I7 er:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Når vi arbejder indefra og ud, skal vi først finde positionen for den laveste mandag tidevand med MATCH-funktionen:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Her gennemgår vi de samme betingede tests, som vi anvendte ovenfor, for kun at begrænse behandlingen til mandags lavvande. Vi anvender dog endnu en test for at begrænse resultaterne til minimumsværdien nu i I6, og vi bruger en lidt enklere syntaks baseret på boolsk logik til at anvende kriterier. Vi har tre separate udtryk, der hver tester en betingelse:
(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
Dette er et eksempel, der pænt viser XLOOKUPs fleksibilitet. Vi kan bruge nøjagtig den samme logik fra INDEX- og MATCH-formlerne ovenfor i en enkel og elegant formel.
