Yhteenveto
Voit etsiä maanantaina matalin vuorovesi, kun otetaan huomioon tietojoukko, jossa on useita vuorovesien nousu- ja laskuveden vuoroja. Voit käyttää taulukkoa, joka perustuu IF- ja MIN-funktioihin. Esitetyssä esimerkissä kaavan I6 kaava on:
(=MIN(IF(day=I5,IF(tide="L",pred))))
joka palauttaa datan matalin maanantain vuoroveden, -0,64
Maanantain alhaisimman vuoroveden päivämäärän saamiseksi kaavassa I7 on seuraava kaava:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Jos laskentataulukko sisältää seuraavat nimetyt alueet: päivämäärä (B5: B124), päivä (C5: C124), aika (D5: D124), pred (E5: E124), vuorovesi (F5: F124).
Molemmat ovat matriisikaavoja ja ne on syötettävä näppäimillä + + Vaihto + Enter.
Tiedot osoitteesta tidesandcurrents.noaa.gov, Santa Cruz, Kalifornia.
Selitys
Korkealla tasolla tämä esimerkki koskee vähimmäisarvon löytämistä useiden kriteerien perusteella. Tätä varten käytämme MIN-funktiota yhdessä kahden sisäkkäisen IF-funktion kanssa:
(=MIN(IF(day=I5,IF(tide="L",pred))))
Työskentelemällä sisältä ulospäin, ensimmäinen IF tarkistaa, onko päivä "Ma", I5: n arvon perusteella:
IF(day=I5 // is day "Mon"
Jos tulos on TOSI, suoritamme toisen JOS:
IF(tide="L",pred) // if tide is "L" return prediction
Toisin sanoen, jos päivä on "ma", tarkistamme, onko vuorovesi "L". Jos näin on, palautamme ennustetun vuorovesitason käyttämällä nimettyä aluetta pred .
Huomaa, että kummankaan IF: n kohdalle ei anneta arvoa, jos se on väärä. Tämä tarkoittaa, että jos jompikumpi looginen testi on EPÄTOSI, ulompi IF palauttaa EPÄTOSI. Lisätietoja sisäkkäisistä IF: stä on tässä artikkelissa.
On tärkeää ymmärtää, että tietojoukko sisältää 120 riviä, joten jokainen kaavan nimetty alue sisältää 120 arvoa. Tämä tekee tästä matriisikaavan - käsittelemme useita arvoja kerralla. Kun molemmat IF: t on arvioitu, ulompi IF palauttaa taulukon, joka sisältää 120 tällaista arvoa:
(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)
Tärkeintä tässä huomata on vain maanantaihin ja laskuveden arvot, jotka selviävät matkan sisäkkäisten IF: n kautta. Muut arvot on korvattu epätosi. Toisin sanoen käytämme kaksois-IF-rakennetta "heittämään" arvot, joista emme ole kiinnostuneita.
Yllä oleva taulukko palautetaan suoraan MIN-toimintoon. MIN-toiminto ohittaa FALSE-arvot automaattisesti ja palauttaa jäljellä olevien arvojen pienimmän arvon, -0,64.
Tämä on matriisikaava ja se on syötettävä näppäimillä + + Vaihto + Enter.
Minimi MINIFS: n kanssa
Jos sinulla on Office 365 tai Excel 2019, voit käyttää MINIFS-toimintoa saadaksesi matalin maanantain vuorovesi näin:
=MINIFS(pred,day,"Mon",tide,"L")
Tulos on sama, eikä tämä kaava vaadi ohjausta + shift + enter.
Hanki päivämäärä
Kun olet löytänyt maanantain vähimmäisveden tason, haluat epäilemättä tietää päivämäärän ja kellonajan. Tämä voidaan tehdä INDEX- ja MATCH-kaavalla. Kohdassa I7 oleva kaava on:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Työskentelemällä sisältä ulospäin, meidän on ensin löydettävä alimman maanantain vuorovesi sijainti MATCH-toiminnolla:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Tässä käydään läpi samat ehdolliset testit, joita käytimme edellä rajoittaaksemme käsittelyä vain maanantain laskuveden aikaan. Sovellamme kuitenkin vielä yhden testin rajoittaaksemme tulokset nyt I6: n vähimmäisarvoon, ja käytämme kriteereiden soveltamiseen hiukan yksinkertaisempaa boolen logiikkaan perustuvaa syntaksia. Meillä on kolme erillistä lauseketta, joista kukin testaa yhtä ehtoa:
(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
Tämä on esimerkki, joka osoittaa hienosti XLOOKUPin joustavuuden. Voimme käyttää täsmälleen samaa logiikkaa yllä olevista INDEX- ja MATCH-kaavoista yksinkertaisessa ja tyylikkäässä kaavassa.
