Kokkuvõte
Esmaspäeva madalaima mõõna leidmiseks, arvestades andmekogumit, kus on mitu päeva mõõna ja mõõna, võite kasutada massiivi valemit, mis põhineb funktsioonidel IF ja MIN. Näidatud näites on valem punktis I6:
(=MIN(IF(day=I5,IF(tide="L",pred))))
mis tagastab andmete madalaima esmaspäeva mõõna, -0,64
Madalaima esmaspäeva mõõna kuupäeva saamiseks on valemis I7 toodud valem:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Kui tööleht sisaldab järgmisi nimetatud vahemikke: kuupäev (B5: B124), päev (C5: C124), kellaaeg (D5: D124), pred (E5: E124), tõusulaine (F5: F124).
Mõlemad on massiivi valemid ja need tuleb sisestada juhtklahviga + shift + enter.
Andmed saidil tidesandcurrents.noaa.gov Californias Santa Cruzi kohta.
Selgitus
Kõrgel tasemel on see näide mitme kriteeriumi alusel miinimumväärtuse leidmise kohta. Selleks kasutame funktsiooni MIN koos kahe sisestatud IF-funktsiooniga:
(=MIN(IF(day=I5,IF(tide="L",pred))))
töötades seestpoolt, kontrollib esimene IF, kui päev on "E", I5 väärtuse põhjal:
IF(day=I5 // is day "Mon"
Kui tulemus on tõene, käivitame teise IF:
IF(tide="L",pred) // if tide is "L" return prediction
Teisisõnu, kui päev on "E", kontrollime, kas tõusulaine on "L". Sel juhul tagastame prognoositud loodetase, kasutades nimega vahemikku pred .
Pange tähele, et me ei anna kummagi IF-i puhul väärtust "vale". See tähendab, et kui kumbki loogiline test on FALSE, tagastab välimine IF vale. Lisateavet pesastatud IF-de kohta leiate sellest artiklist.
Oluline on mõista, et andmekomplekt sisaldab 120 rida, seega sisaldab iga valemis nimetatud vahemik 120 väärtust. See teebki sellest massiivvalemi - töötleme korraga palju väärtusi. Pärast mõlema IF hindamist tagastab välimine IF massiivi, mis sisaldab 120 sellist väärtust:
(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)
Peamine asi, mida siin tähele panna, on ainult esmaspäeva ja mõõna seotud väärtused, mis elavad reisi läbi pesastatud IF-de kaudu. Ülejäänud väärtused on asendatud väärtusega FALSE. Teisisõnu, me kasutame topelt IF-struktuuri, et "visata ära" väärtused, mis meid ei huvita.
Ülaltoodud massiiv tagastatakse otse funktsioonile MIN. Funktsioon MIN ignoreerib FALSE väärtusi automaatselt ja tagastab allesjäänud väärtuste minimaalse väärtuse -0,64.
See on massiivi valem ja see tuleb sisestada juhtklahviga + tõstuklahv + sisestusklahv.
Miinimum MINIFS-iga
Kui teil on Office 365 või Excel 2019, saate funktsiooni MINIFS abil saada madalaimat esmaspäeva mõõna:
=MINIFS(pred,day,"Mon",tide,"L")
Tulemus on sama ja see valem ei vaja juhtimist + tõstuklahvi + sisestusklahvi.
Hankige kuupäev
Kui leiate minimaalse esmaspäeva mõõna taseme, soovite kahtlemata teada kuupäeva ja kellaaega. Seda saab teha valemiga INDEX ja MATCH. I7 valem on:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Seestpoolt töötades peame esmalt leidma madalaima esmaspäeva tõusulaine positsiooni funktsiooniga MATCH:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Siin läbime samad tingimuslikud testid, mida rakendasime eespool, et piirata töötlemist ainult esmaspäeva mõõna ajal. Kuid me rakendame veel ühe testi, et piirata tulemusi nüüd I6-s toodud miinimumväärtusega, ja kriteeriumide rakendamiseks kasutame natuke lihtsamat süntaksit, mis põhineb boolean loogikal. Meil on kolm eraldi avaldist, millest igaüks testib ühte tingimust:
(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
See on näide, mis näitab kenasti XLOOKUPi paindlikkust. Saame kasutada täpselt sama loogikat ülaltoodud valemitest INDEX ja MATCH lihtsas ja elegantses valemis.
