Kopsavilkums
Lai atrastu zemāko plūdmaiņu pirmdienā, ņemot vērā datu kopumu ar daudzu dienu plūdmaiņu un bēgumu, varat izmantot masīva formulu, pamatojoties uz IF un MIN funkcijām. Parādītajā piemērā formula I6 ir:
(=MIN(IF(day=I5,IF(tide="L",pred))))
kas atgriež zemāko pirmdienas plūdmaiņu datos, -0,64
Lai izgūtu zemākās pirmdienas plūdmaiņas datumu, I7. Formulā ir:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Ja darblapā ir šādi nosauktie diapazoni: datums (B5: B124), diena (C5: C124), laiks (D5: D124), pred (E5: E124), plūdmaiņa (F5: F124).
Abas ir masīvu formulas, un tās jāievada ar vadības taustiņu + Shift + Enter.
Dati no tidesandcurrents.noaa.gov par Santakrūzu, Kalifornijā.
Paskaidrojums
Augstā līmenī šis piemērs ir par minimālās vērtības atrašanu, pamatojoties uz vairākiem kritērijiem. Lai to izdarītu, mēs izmantojam MIN funkciju kopā ar divām ligzdotām IF funkcijām:
(=MIN(IF(day=I5,IF(tide="L",pred))))
strādājot no iekšpuses uz āru, pirmais IF pārbauda, vai diena ir "Mon", pamatojoties uz vērtību I5:
IF(day=I5 // is day "Mon"
Ja rezultāts ir PATIESA, mēs izpildām vēl vienu IF:
IF(tide="L",pred) // if tide is "L" return prediction
Citiem vārdiem sakot, ja diena ir "pirmdiena", mēs pārbaudām, vai plūdmaiņa ir "L". Ja tā, mēs atgriežam paredzamo plūdmaiņu līmeni, izmantojot nosaukto diapazona pred .
Ievērojiet, ka mēs nenorādām “vērtību, ja nepatiesa” ne vienam, ne otram IF. Tas nozīmē, ka, ja kāds no loģiskajiem testiem ir FALSE, ārējais IF atgriezīs FALSE. Lai iegūtu papildinformāciju par ligzdotajiem IF, skatiet šo rakstu.
Ir svarīgi saprast, ka datu kopā ir 120 rindas, tāpēc katrā nosauktajā diapazonā formulā ir 120 vērtības. Tas padara šo masīva formulu - mēs vienlaikus apstrādājam daudzas vērtības. Pēc abu IF novērtēšanas ārējais IF atgriezīs masīvu, kas satur 120 šādas vērtības:
(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)
Galvenais, ko šeit pamanīt, ir tikai tās vērtības, kas saistītas ar pirmdienu, un bēgums pārdzīvo ceļojumu caur ligzdotajiem IF. Pārējās vērtības ir aizstātas ar FALSE. Citiem vārdiem sakot, mēs izmantojam dubulto IF struktūru, lai "izmestu" vērtības, kas mūs neinteresē.
Iepriekš minētais masīvs tiek tieši atgriezts MIN funkcijai. MIN funkcija automātiski ignorē FALSE vērtības un atgriež minimālo vērtību, kas paliek, -0.64.
Šī ir masīva formula, un tā jāievada ar vadības taustiņu + Shift + Enter.
Minimums ar MINIFS
Ja jums ir Office 365 vai Excel 2019, varat izmantot funkciju MINIFS, lai iegūtu zemāko pirmdienas plūdmaiņu šādi:
=MINIFS(pred,day,"Mon",tide,"L")
Rezultāts ir tāds pats, un šai formulai nav nepieciešama vadība + Shift + Enter.
Iegūstiet datumu
Kad atradīsit minimālo pirmdienas plūdmaiņas līmeni, jūs neapšaubāmi vēlaties uzzināt datumu un laiku. To var izdarīt ar INDEX un MATCH formulu. Formula I7 ir:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Strādājot no iekšpuses uz āru, mums vispirms jāatrod zemākās pirmdienas plūdmaiņas pozīcija ar funkciju MATCH:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Šeit mēs veicam tos pašus nosacītos testus, kurus izmantojām iepriekš, lai ierobežotu apstrādi tikai pirmdienas bēguma laikā. Tomēr mēs piemērojam vēl vienu testu, lai ierobežotu rezultātus līdz minimālajai vērtībai tagad I6, un kritēriju piemērošanai mēs izmantojam nedaudz vienkāršāku sintaksi, kas balstīta uz Būla loģiku. Mums ir trīs atsevišķas izteiksmes, no kurām katra pārbauda vienu nosacījumu:
(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
Šis ir piemērs, kas lieliski parāda XLOOKUP elastību. Mēs varam izmantot tieši to pašu loģiku no iepriekš minētajām INDEX un MATCH formulām vienkāršā un elegantā formulā.
