Santrauka
Norėdami rasti mažiausią potvynį pirmadienį, atsižvelgiant į duomenų rinkinį, kuriame yra daugybė potvynių ir atoslūgių, galite naudoti masyvo formulę, pagrįstą IF ir MIN funkcijomis. Parodytame pavyzdyje formulė I6 yra:
(=MIN(IF(day=I5,IF(tide="L",pred))))
kuris pateikia žemiausią pirmadienio potvynio duomenis, -0,64
Norėdami gauti žemiausio pirmadienio potvynio datą, I7 formulė yra tokia:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Kai darbalapyje yra šie įvardyti intervalai: data (B5: B124), diena (C5: C124), laikas (D5: D124), pred (E5: E124), potvynis (F5: F124).
Abi yra masyvo formulės ir jas reikia įvesti su „Control“ + „Shift“ + „Enter“.
Santa Cruz, Kalifornija, duomenys iš tidesandcurrents.noaa.gov.
Paaiškinimas
Aukštu lygiu šis pavyzdys yra apie minimalios vertės nustatymą pagal kelis kriterijus. Norėdami tai padaryti, mes naudojame MIN funkciją kartu su dviem įdėtomis IF funkcijomis:
(=MIN(IF(day=I5,IF(tide="L",pred))))
dirbant iš vidaus, pirmasis IF patikrina, ar diena yra „pirmadienis“, remiantis I5 verte:
IF(day=I5 // is day "Mon"
Jei rezultatas TIKRA, vykdome kitą IF:
IF(tide="L",pred) // if tide is "L" return prediction
Kitaip tariant, jei diena yra „pirmadienis“, patikriname, ar potvynis yra „L“. Jei taip, mes grąžinsime numatomą potvynio lygį naudodami įvardytą diapazono pred .
Atkreipkite dėmesį, kad nenurodome „reikšmės, jei klaidinga“ nei vienam iš IF. Tai reiškia, kad jei kuris nors loginis testas yra NETIESA, išorinis IF grąžins NETIESA. Daugiau informacijos apie įterptąsias IF rasite šiame straipsnyje.
Svarbu suprasti, kad duomenų rinkinyje yra 120 eilučių, todėl kiekviename iš pavadintų formulės diapazonų yra 120 reikšmių. Tai daro tai masyvo formule - mes vienu metu apdorojame daug reikšmių. Įvertinus abi IF, išorinis IF grąžins masyvą, kuriame yra 120 tokių reikšmių:
(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)
Svarbiausias dalykas, kurį reikia pastebėti, yra tik su pirmadieniu ir atoslūgiu susijusios vertės, išgyvenančios kelionę per įdėtus IF. Kitos vertės buvo pakeistos į FALSE. Kitaip tariant, mes naudojame dvigubą IF struktūrą „išmesti“ vertybes, kurios mums neįdomios.
Aukščiau pateiktas masyvas grąžinamas tiesiai į MIN funkciją. MIN funkcija automatiškai ignoruoja FALSE reikšmes ir grąžina mažiausią likusių reikšmę -0,64.
Tai yra masyvo formulės, kurias reikia įvesti su „Control“ + „Shift“ + „Enter“.
Minimalus su MINIFS
Jei turite „Office 365“ arba „Excel 2019“, galite naudoti funkciją MINIFS, kad gautumėte žemiausią pirmadienio potvynį:
=MINIFS(pred,day,"Mon",tide,"L")
Rezultatas yra tas pats, o šiai formulei nereikia valdyti + shift + enter.
Gaukite datą
Suradę minimalų pirmadienio potvynio lygį, neabejotinai norėsite sužinoti datą ir laiką. Tai galima padaryti pagal INDEX ir MATCH formulę. I7 formulė yra:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Dirbdami iš vidaus, pirmiausia turime nustatyti žemiausio pirmadienio potvynio padėtį naudodami funkciją MATCH:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Čia mes atliekame tuos pačius sąlyginius testus, kuriuos taikėme aukščiau, kad apdorojimas būtų apribotas tik pirmadienio atoslūgiais. Tačiau mes taikome dar vieną testą, kad apribotume rezultatus iki minimalios vertės dabar I6, ir taikome šiek tiek paprastesnę sintaksę, pagrįstą logine logika, kad taikytume kriterijus. Mes turime tris atskiras posakius, kiekvienas išbando vieną sąlygą:
(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
Tai pavyzdys, puikiai parodantis XLOOKUP lankstumą. Mes galime naudoti tą pačią logiką iš aukščiau pateiktų INDEX ir MATCH formulių paprastoje ir elegantiškoje formulėje.
