Sažetak
Da biste pronašli najnižu plimu i oseku u ponedjeljak, s obzirom na skup podataka s višednevnom osekom i osekom, možete koristiti formulu niza koja se temelji na funkcijama IF i MIN. U prikazanom primjeru, formula u I6 je:
(=MIN(IF(day=I5,IF(tide="L",pred))))
što vraća najnižu plima u ponedjeljak u podacima, -0,64
Da biste pronašli datum najniže plime u ponedjeljak, formula u I7 je:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Gdje radni list sadrži sljedeće imenovane raspone: datum (B5: B124), dan (C5: C124), vrijeme (D5: D124), pred (E5: E124), plima i oseka (F5: F124).
Obje su formule polja i moraju se unijeti s control + shift + enter.
Podaci tidesandcurrents.noaa.gov za Santa Cruz, Kalifornija.
Obrazloženje
Na visokoj razini, ovaj primjer govori o pronalaženju minimalne vrijednosti na temelju više kriterija. Da bismo to učinili, koristimo funkciju MIN zajedno s dvije ugniježđene funkcije IF:
(=MIN(IF(day=I5,IF(tide="L",pred))))
radeći iznutra prema van, prvi IF provjerava je li dan "ponedjeljak", na temelju vrijednosti u I5:
IF(day=I5 // is day "Mon"
Ako je rezultat ISTINA, pokrećemo još jedan IF:
IF(tide="L",pred) // if tide is "L" return prediction
Drugim riječima, ako je dan "ponedjeljak", provjeravamo je li plima "L". Ako je tako, vraćamo predviđenu razinu plime i oseke, koristeći imenovani raspon pred .
Primijetite da ne pružamo "vrijednost ako je netočno" ni za jedan od IF-a. To znači da ako je bilo koji logički test FALSE, vanjski IF vratit će FALSE. Za više informacija o ugniježđenim IF-ovima pogledajte ovaj članak.
Važno je razumjeti da skup podataka uključuje 120 redaka, pa svaki od imenovanih raspona u formuli sadrži 120 vrijednosti. To je ono što ovu formulu niza čini - obrađujemo mnoge vrijednosti odjednom. Nakon što se procijene oba IF, vanjski IF vratit će niz koji sadrži 120 vrijednosti poput ove:
(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 koju ovdje treba primijetiti su samo vrijednosti povezane s ponedjeljkom i osekom, koje preživljavaju putovanje kroz ugniježđene IF-ove. Ostale su vrijednosti zamijenjene FALSE. Drugim riječima, koristimo dvostruku strukturu IF da bismo "odbacili" vrijednosti koje nas ne zanimaju.
Gornji niz vraća se izravno u funkciju MIN. MIN funkcija automatski zanemaruje FALSE vrijednosti i vraća minimalnu vrijednost onih koje ostaju, -0,64.
Ovo je formula za niz i mora se unijeti sa control + shift + enter.
Minimalno s MINIFS
Ako imate Office 365 ili Excel 2019, možete koristiti funkciju MINIFS da biste dobili najnižu plima u ponedjeljak poput ove:
=MINIFS(pred,day,"Mon",tide,"L")
Rezultat je isti, a za ovu formulu nije potrebna kontrola + shift + enter.
Doznaj datum
Jednom kad pronađete minimalnu razinu plime i oseke, nesumnjivo ćete htjeti znati datum i vrijeme. To se može učiniti pomoću INDEX i MATCH formule. Formula u I7 je:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Radeći iznutra prema van, prvo moramo locirati položaj najniže plime u ponedjeljak s funkcijom MATCH:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Ovdje prolazimo kroz iste uvjetne testove koje smo primijenili gore kako bismo ograničili obradu samo na ponedjeljak oseku. Međutim, primjenjujemo još jedan test za ograničavanje rezultata na minimalnu vrijednost sada u I6, a za primjenu kriterija koristimo malo jednostavniju sintaksu koja se temelji na logičkoj logici. Imamo tri odvojena izraza, od kojih svaki testira jedan uvjet:
(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
Ovo je primjer koji lijepo pokazuje fleksibilnost XLOOKUP-a. Možemo koristiti potpuno istu logiku iz gornjih formula INDEX i MATCH, u jednostavnoj i elegantnoj formuli.
