Sammendrag
For å finne lavvannet på en mandag, gitt et sett med data med mange dager med høy og lavvann, kan du bruke en matriseformel basert på IF- og MIN-funksjonene. I eksemplet vist er formelen i I6:
(=MIN(IF(day=I5,IF(tide="L",pred))))
som gir den laveste mandag tidevannet i dataene, -0,64
For å hente datoen for laveste tidevann, er formelen i I7:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Der regnearket inneholder følgende navngitte områder: dato (B5: B124), dag (C5: C124), tid (D5: D124), pred (E5: E124), tidevann (F5: F124).
Begge er matriseformler og må legges inn med kontroll + skift + enter.
Data fra tidesandcurrents.noaa.gov for Santa Cruz, California.
Forklaring
På et høyt nivå handler dette eksemplet om å finne en minimumsverdi basert på flere kriterier. For å gjøre det bruker vi MIN-funksjonen sammen med to nestede IF-funksjoner:
(=MIN(IF(day=I5,IF(tide="L",pred))))
arbeider fra innsiden og ut, den første IF sjekker om dagen er "man", basert på verdien i I5:
IF(day=I5 // is day "Mon"
Hvis resultatet er SANT, kjører vi en annen IF:
IF(tide="L",pred) // if tide is "L" return prediction
Med andre ord, hvis dagen er "man", sjekker vi om tidevannet er "L". I så fall returnerer vi det forutsagte tidevannsnivået ved hjelp av det nevnte området pred .
Legg merke til at vi ikke gir en "verdi hvis falsk" for verken IF. Det betyr at hvis en eller annen logisk test er FALSE, vil den ytre IF returnere FALSE. For mer informasjon om nestede IF-er, se denne artikkelen.
Det er viktig å forstå at datasettet inneholder 120 rader, så hvert av de nevnte områdene i formelen inneholder 120 verdier. Dette er det som gjør dette til en matriseformel - vi behandler mange verdier samtidig. Etter at begge IF-ene er evaluert, vil den ytre IF returnere en matrise som inneholder 120 verdier som dette:
(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)
Det viktigste å merke seg her er bare verdier knyttet til mandag og tidevann overlever turen gjennom de nestede IF-ene. De andre verdiene er erstattet med FALSE. Vi bruker med andre ord den doble IF-strukturen for å "kaste bort" verdier vi ikke er interessert i.
Matrisen ovenfor returneres direkte til MIN-funksjonen. MIN-funksjonen ignorerer automatisk FALSE-verdiene, og returnerer minimumverdien for de som er igjen, -0,64.
Dette er en matriseformel og må legges inn med kontroll + skift + enter.
Minimum med MINIFS
Hvis du har Office 365 eller Excel 2019, kan du bruke MINIFS-funksjonen for å få den laveste mandag tidevannet slik:
=MINIFS(pred,day,"Mon",tide,"L")
Resultatet er det samme, og denne formelen krever ikke kontroll + skift + enter.
Få datoen
Når du har funnet minimumsvannnivået på mandag, vil du utvilsomt vite dato og klokkeslett. Dette kan gjøres med en INDEX- og MATCH-formel. Formelen i I7 er:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Når vi arbeider fra innsiden og ut, må vi først finne posisjonen til det laveste mandagvannet med MATCH-funksjonen:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Her går vi gjennom de samme betingede testene vi brukte ovenfor for å begrense behandlingen til mandagens lavvann. Imidlertid bruker vi en test til for å begrense resultatene til minimumsverdien nå i I6, og vi bruker en litt enklere syntaks basert på boolsk logikk for å anvende kriterier. Vi har tre separate uttrykk, hver som tester en tilstand:
(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 som pent viser XLOOKUPs fleksibilitet. Vi kan bruke nøyaktig samme logikk fra INDEX- og MATCH-formlene ovenfor, i en enkel og elegant formel.
