Összegzés
Ha meg szeretné találni a legalacsonyabb dagályt egy hétfőn, adatsor alapján sok napos dagály és apály, használhatja az IF és a MIN függvényeken alapuló tömbképletet. A bemutatott példában az I6 képlete a következő:
(=MIN(IF(day=I5,IF(tide="L",pred))))
amely az adatok legalacsonyabb hétfői árapályát adja, -0,64
A legalacsonyabb hétfői árapály dátumának lekéréséhez az I7 képlet a következő:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Ahol a munkalap a következő megnevezett tartományokat tartalmazza: dátum (B5: B124), nap (C5: C124), idő (D5: D124), pred (E5: E124), dagály (F5: F124).
Mindkettő tömbképlet, és a vezérlő + shift + Enter billentyűkkel kell megadni.
Adatok a tidesandcurrents.noaa.gov webhelyről a kaliforniai Santa Cruz számára.
Magyarázat
Magas szinten ez a példa egy minimális érték megtalálásáról szól több kritérium alapján. Ehhez a MIN függvényt két beágyazott IF függvénnyel együtt használjuk:
(=MIN(IF(day=I5,IF(tide="L",pred))))
belülről kifelé haladva az első IF ellenőrzi, hogy a nap "H"-e, az I5-ben szereplő érték alapján:
IF(day=I5 // is day "Mon"
Ha az eredmény IGAZ, akkor futtatunk egy másik IF-t:
IF(tide="L",pred) // if tide is "L" return prediction
Más szóval, ha a nap "H", akkor ellenőrizzük, hogy az árapály "L"-e. Ha igen, akkor megadjuk az előre jelzett árapályszintet, a megnevezett pred tartomány használatával .
Vegye figyelembe, hogy egyik IF esetén sem adunk meg "értéket, ha hamis". Ez azt jelenti, hogy ha bármelyik logikai teszt FALSE, akkor a külső IF FALSE-t ad vissza. A beágyazott IF-kről további információt ebben a cikkben talál.
Fontos megérteni, hogy az adatkészlet 120 sort tartalmaz, így a képlet minden megnevezett tartománya 120 értéket tartalmaz. Ez teszi ezt tömbképletté - sok értéket dolgozunk fel egyszerre. Miután mindkét IF értékelése megtörtént, a külső IF egy olyan tömböt ad vissza, amely 120 ilyen értéket tartalmaz:
(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)
A legfontosabb dolog, amit itt észre kell venni, csak a hétfőhöz és az apályhoz kapcsolódó értékek élik túl az utat a beágyazott IF-ken keresztül. A többi érték helyébe FALSE lépett. Más szavakkal, a kettős IF struktúrát használjuk az általunk nem érdekelt értékek "eldobására".
A fenti tömb közvetlenül a MIN funkcióba kerül. A MIN funkció automatikusan figyelmen kívül hagyja a FALSE értékeket, és a fennmaradó értékek minimális értékét adja vissza, -0,64.
Ez egy tömbképlet, amelyet a control + shift + enter billentyűkombinációval kell megadni.
Minimum MINIFS-szel
Ha rendelkezik Office 365 vagy Excel 2019 programmal, akkor a MINIFS funkcióval megkaphatja a legalacsonyabb hétfői árapályt:
=MINIFS(pred,day,"Mon",tide,"L")
Az eredmény ugyanaz, és ez a képlet nem igényel vezérlést + shift + enter.
Szerezd meg a dátumot
Miután megtalálta a minimális hétfői árapályszintet, kétségtelenül tudni szeretné a dátumot és az időt. Ezt megtehetjük INDEX és MATCH képlettel. Az I7 képlete a következő:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Belülről kifelé haladva először meg kell találnunk a legalacsonyabb hétfői árapály helyzetét a MATCH funkcióval:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Itt ugyanazokat a feltételes teszteket hajtjuk végre, amelyeket a fentiekben alkalmaztunk, hogy a feldolgozást csak a hétfői apályokra korlátozzuk. Azonban még egy tesztet alkalmazunk az eredmények minimális értékre történő korlátozására az I6-ban, és a kritériumok alkalmazásához egy kissé egyszerűbb logikai logikán alapuló szintaxist használunk. Három külön kifejezésünk van, mindegyik egy feltételt tesztel:
(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
Ez egy példa, amely szépen megmutatja az XLOOKUP rugalmasságát. Pontosan ugyanazt a logikát használhatjuk a fenti INDEX és MATCH képletekből, egyszerű és elegáns képletben.
