Резиме
Да бисте пронашли најнижу плиму у понедељак, с обзиром на скуп података са вишедневном осеком и осеком, можете користити формулу низа засновану на функцијама ИФ и МИН. У приказаном примеру, формула у И6 је:
(=MIN(IF(day=I5,IF(tide="L",pred))))
што враћа најнижу плима у понедељак у подацима, -0,64
Да бисте пронашли датум најниже плиме у понедељак, формула у И7 је:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Тамо где радни лист садржи следеће именоване опсеге: датум (Б5: Б124), дан (Ц5: Ц124), време (Д5: Д124), пред (Е5: Е124), плима и осека (Ф5: Ф124).
Обе су формуле низа и морају се унети са цонтрол + схифт + ентер.
Подаци са тидесандцуррентс.ноаа.гов за Санта Цруз, Калифорнија.
Објашњење
На високом нивоу, овај пример говори о проналажењу минималне вредности на основу више критеријума. Да бисмо то урадили, користимо функцију МИН заједно са две угнежђене функције ИФ:
(=MIN(IF(day=I5,IF(tide="L",pred))))
радећи изнутра према ван, први ИФ проверава да ли је дан „пон“, на основу вредности у И5:
IF(day=I5 // is day "Mon"
Ако је резултат ИСТИНА, покрећемо још један ИФ:
IF(tide="L",pred) // if tide is "L" return prediction
Другим речима, ако је дан „понедељак“, проверавамо да ли је плима „Л“. Ако је тако, враћамо предвиђени ниво плиме и осеке, користећи именовани предмер опсега .
Приметите да не пружамо „вредност ако је нетачно“ ни за један ИФ. То значи да ако је било који логички тест ФАЛСЕ, спољни ИФ ће вратити ФАЛСЕ. За више информација о угнежђеним ИФ-овима погледајте овај чланак.
Важно је схватити да скуп података садржи 120 редова, па сваки од именованих опсега у формули садржи 120 вредности. То је оно што ову формулу низа чини - обрађујемо много вредности одједном. Након процене оба ИФ, спољни ИФ ће вратити низ који садржи 120 вредности попут ове:
(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)
Кључна ствар коју овде треба приметити су само вредности повезане са понедељком и осеком, које преживе путовање кроз угнежђене ИФ. Остале вредности су замењене ФАЛСЕ. Другим речима, користимо двоструку структуру ИФ да бисмо „бацили“ вредности које нас не занимају.
Горњи низ се враћа директно у функцију МИН. МИН функција аутоматски занемарује ФАЛСЕ вредности и враћа минималну вредност оних које су остале, -0,64.
Ово је формула низа и мора се унети са цонтрол + схифт + ентер.
Минимално са МИНИФС
Ако имате Оффице 365 или Екцел 2019, можете да користите функцију МИНИФС да бисте добили најнижу плима у понедељак попут ове:
=MINIFS(pred,day,"Mon",tide,"L")
Резултат је исти, а ова формула не захтева контролу + схифт + ентер.
Нађи датум
Једном када пронађете минимални ниво плима у понедељак, несумњиво ћете желети да знате датум и време. То се може урадити помоћу ИНДЕКС и МАТЦХ формуле. Формула у И7 је:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Радећи изнутра ка споља, прво морамо да лоцирамо положај најниже осеке у понедељак помоћу функције МАТЦХ:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Овде пролазимо кроз исте условне тестове које смо претходно применили да бисмо ограничили обраду само на осеке у понедељак. Међутим, примењујемо још један тест за ограничавање резултата на минималну вредност сада у И6, а за примену критеријума користимо мало једноставнију синтаксу засновану на логичкој логици. Имамо три одвојена израза, од којих сваки тестира један услов:
(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
Ово је пример који лепо показује флексибилност КСЛООКУП-а. Можемо користити потпуно исту логику из горњих формула ИНДЕКС и МАТЦХ, у једноставној и елегантној формули.
