Povzetek
Če želite najti najnižjo plimo v ponedeljek, glede na nabor podatkov z veliko dnevi plime in oseke, lahko uporabite matrično formulo, ki temelji na funkcijah IF in MIN. V prikazanem primeru je formula v I6:
(=MIN(IF(day=I5,IF(tide="L",pred))))
ki vrne najnižjo ponedeljkovo plimo v podatkih, -0,64
Če želite pridobiti datum najnižje ponedeljkove plime, je formula v I7:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Kjer delovni list vsebuje naslednje imenovane obsege: datum (B5: B124), dan (C5: C124), čas (D5: D124), pred (E5: E124), plima (F5: F124)
Obe sta formuli matrike in ju je treba vnesti s tipko control + shift + enter.
Podatki tidesandcurrents.noaa.gov za Santa Cruz v Kaliforniji.
Pojasnilo
Na visoki ravni gre v tem primeru za iskanje najnižje vrednosti na podlagi več meril. Za to uporabljamo funkcijo MIN skupaj z dvema ugnezdenima funkcijama IF:
(=MIN(IF(day=I5,IF(tide="L",pred))))
pri delu od znotraj navzven prvi IF preveri, ali je dan "pon", glede na vrednost v I5:
IF(day=I5 // is day "Mon"
Če je rezultat TRUE, zaženemo še IF:
IF(tide="L",pred) // if tide is "L" return prediction
Z drugimi besedami, če je dan "pon", preverimo, ali je plima "L". Če je tako, vrnemo napovedano raven plime z uporabo imenovanega obsega pred .
Upoštevajte, da za noben IF ne podajamo "vrednosti, če je napačno". To pomeni, da če je kateri koli logični test FALSE, bo zunanji IF vrnil FALSE. Za več informacij o ugnezdenih IF-jih glejte ta članek.
Pomembno je razumeti, da nabor podatkov vključuje 120 vrstic, zato vsak od imenovanih obsegov v formuli vsebuje 120 vrednosti. To je tisto, zaradi česar je ta matrična formula - obdelujemo veliko vrednosti hkrati. Po oceni obeh IF bo zunanji IF vrnil matriko, ki vsebuje 120 vrednosti, kot je ta:
(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)
Ključna stvar, ki jo je treba tukaj opaziti, so le vrednosti, povezane s ponedeljkom in oseko, ki preživijo potovanje skozi ugnezdene IF. Ostale vrednosti so bile nadomeščene z FALSE. Z drugimi besedami, z dvojno IF strukturo "vržemo" vrednosti, ki nas ne zanimajo.
Zgornje polje se vrne neposredno v funkcijo MIN. Funkcija MIN samodejno prezre vrednosti FALSE in vrne najmanjšo vrednost tistih, ki ostanejo, -0,64.
To je matrična formula, ki jo je treba vnesti s tipko control + shift + enter.
Najmanj z MINIFI
Če imate Office 365 ali Excel 2019, lahko s funkcijo MINIFS dosežete najnižjo ponedeljkovo plimo, kot je ta:
=MINIFS(pred,day,"Mon",tide,"L")
Rezultat je enak in ta formula ne zahteva nadzora + shift + enter.
Dobite datum
Ko najdete najnižjo raven ponedeljkove plime, boste nedvomno želeli vedeti datum in uro. To lahko storite s formulo INDEX in MATCH. Formula v I7 je:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Če delamo od znotraj navzven, moramo najprej najti položaj najnižje ponedeljkove plime s funkcijo MATCH:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Tukaj izvajamo iste pogojne teste, ki smo jih uporabili zgoraj, da omejimo obdelavo le na ponedeljkovo oseko. Vendar uporabimo še en test za omejitev rezultatov na najnižjo vrednost, ki je zdaj v I6, in za uporabo meril uporabljamo nekoliko preprostejšo sintakso, ki temelji na logični logiki. Imamo tri ločene izraze, pri čemer vsak preizkuša en pogoj:
(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
To je primer, ki lepo kaže prilagodljivost XLOOKUP-a. Popolnoma enako logiko iz zgornjih formul INDEX in MATCH lahko uporabimo v preprosti in elegantni formuli.
