rezumat
Pentru a găsi cea mai joasă maree într-o zi de luni, având în vedere un set de date cu multe zile de maree joasă și mare, puteți utiliza o formulă matrice bazată pe funcțiile IF și MIN. În exemplul prezentat, formula din I6 este:
(=MIN(IF(day=I5,IF(tide="L",pred))))
care returnează cea mai mică maree de luni din date, -0,64
Pentru a recupera data celei mai mici valuri de luni, formula din I7 este:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
În cazul în care foaia de lucru conține următoarele intervale denumite: data (B5: B124), ziua (C5: C124), ora (D5: D124), pred (E5: E124), maree (F5: F124).
Ambele sunt formule matrice și trebuie introduse cu control + shift + enter.
Date de la tidesandcurrents.noaa.gov pentru Santa Cruz, California.
Explicaţie
La un nivel ridicat, acest exemplu este despre găsirea unei valori minime pe baza mai multor criterii. Pentru a face acest lucru, folosim funcția MIN împreună cu două funcții IF imbricate:
(=MIN(IF(day=I5,IF(tide="L",pred))))
lucrând din interior spre exterior, primul IF verifică dacă ziua este „luni”, pe baza valorii din I5:
IF(day=I5 // is day "Mon"
Dacă rezultatul este ADEVĂRAT, rulăm un alt IF:
IF(tide="L",pred) // if tide is "L" return prediction
Cu alte cuvinte, dacă ziua este „luni”, verificăm dacă valul este „L”. Dacă da, returnăm nivelul de maree prezis, folosind intervalul pred .
Observați că nu furnizăm o „valoare dacă este falsă” pentru niciun IF. Asta înseamnă că dacă oricare test logic este FALS, IF-ul exterior va returna FALSE. Pentru mai multe informații despre IF-uri imbricate, consultați acest articol.
Este important să înțelegeți că setul de date include 120 de rânduri, deci fiecare dintre intervalele denumite din formulă conține 120 de valori. Acesta este ceea ce face ca aceasta să fie o formulă matrice - procesăm multe valori simultan. După evaluarea ambelor IF-uri, IF-ul exterior va returna o matrice care conține 120 de valori ca aceasta:
(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)
Principalul lucru pe care trebuie să-l observăm aici este doar valorile asociate cu luni și valul scăzut supraviețuiește călătoriei prin FI-uri imbricate. Celelalte valori au fost înlocuite cu FALS. Cu alte cuvinte, folosim structura dublă IF pentru a „arunca” valorile care nu ne interesează.
Matricea de mai sus este returnată direct funcției MIN. Funcția MIN ignoră automat valorile FALSE și returnează valoarea minimă a celor care rămân, -0,64.
Aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.
Minim cu MINIFS
Dacă aveți Office 365 sau Excel 2019, puteți utiliza funcția MINIFS pentru a obține cea mai mică marea de luni, astfel:
=MINIFS(pred,day,"Mon",tide,"L")
Rezultatul este același și această formulă nu necesită control + shift + enter.
Obțineți data
Odată ce ați găsit nivelul minim de maree de luni, veți dori fără îndoială să știți data și ora. Acest lucru se poate face cu o formulă INDEX și MATCH. Formula din I7 este:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Lucrând din interior spre exterior, trebuie mai întâi să localizăm poziția celei mai mici valuri de luni cu funcția MATCH:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Aici, parcurgem aceleași teste condiționale pe care le-am aplicat mai sus, pentru a restricționa procesarea numai la valurile mici de luni. Cu toate acestea, aplicăm încă un test pentru a restrânge rezultatele la valoarea minimă acum în I6 și folosim o sintaxă ceva mai simplă bazată pe logica booleană pentru a aplica criteriile. Avem trei expresii separate, fiecare testând o condiție:
(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
Acesta este un exemplu care arată frumos flexibilitatea XLOOKUP. Putem folosi exact aceeași logică din formulele INDEX și MATCH de mai sus, într-o formulă simplă și elegantă.
