Zhrnutie
Ak chcete nájsť najnižší príliv v pondelok, vzhľadom na množinu údajov s prílivom a odlivom za mnoho dní, môžete použiť maticový vzorec založený na funkciách IF a MIN. V zobrazenom príklade je vzorec v I6:
(=MIN(IF(day=I5,IF(tide="L",pred))))
ktorý vráti najnižší pondelkový príliv v dátach, -0,64
Na získanie dátumu najnižšieho pondelkového prílivu je vzorec v I7:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Ak pracovný hárok obsahuje nasledujúce pomenované rozsahy: dátum (B5: B124), deň (C5: C124), čas (D5: D124), pred (E5: E124), príliv a odliv (F5: F124).
Oba sú maticové vzorce a musia byť zadané pomocou klávesov Control + Shift + Enter.
Údaje z tidesandcurrents.noaa.gov pre Santa Cruz v Kalifornii.
Vysvetlenie
Na vysokej úrovni je tento príklad o hľadaní minimálnej hodnoty na základe viacerých kritérií. Aby sme to dosiahli, používame funkciu MIN spolu s dvoma vnorenými funkciami IF:
(=MIN(IF(day=I5,IF(tide="L",pred))))
pri práci zvnútra von prvý IF skontroluje, či je deň „pondelok“, na základe hodnoty v I5:
IF(day=I5 // is day "Mon"
Ak je výsledok TRUE, spustíme ďalší IF:
IF(tide="L",pred) // if tide is "L" return prediction
Inými slovami, ak je deň „Po“, skontrolujeme, či je príliv a odliv „L“. Ak je to tak, vrátime predpovedanú hladinu prílivu a odlivu pomocou pomenovaného rozsahu pred .
Upozorňujeme, že pre žiadny IF neposkytujeme hodnotu „if if false“. To znamená, že ak je ktorýkoľvek logický test FALSE, vonkajší IF vráti FALSE. Ďalšie informácie o vnorených IF nájdete v tomto článku.
Je dôležité pochopiť, že množina údajov obsahuje 120 riadkov, takže každý z pomenovaných rozsahov vo vzorci obsahuje 120 hodnôt. Vďaka tomu je tento vzorec poľa - spracovávame veľa hodnôt naraz. Po vyhodnotení obidvoch IF vráti vonkajší IF pole, ktoré obsahuje 120 hodnôt, ako je táto:
(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)
Kľúčovou vecou, ktorú si tu treba všimnúť, sú iba hodnoty spojené s pondelkom a odlivom, ktoré prežijú cestu vnorenými IF. Ostatné hodnoty boli nahradené hodnotou FALSE. Inými slovami, na „vyhodenie“ hodnôt, ktoré nás nezaujímajú, používame štruktúru dvojitého IF.
Vyššie uvedené pole sa vráti priamo do funkcie MIN. Funkcia MIN automaticky ignoruje FALSE hodnoty a vráti minimálnu hodnotu tých zostávajúcich, -0,64.
Toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter.
Minimálne s MINIFS
Ak máte Office 365 alebo Excel 2019, môžete pomocou funkcie MINIFS získať najnižší pondelkový príliv takto:
=MINIFS(pred,day,"Mon",tide,"L")
Výsledok je rovnaký a tento vzorec nevyžaduje ovládanie + shift + enter.
Získajte dátum
Akonáhle nájdete minimálnu úroveň prílivu v pondelok, nepochybne budete chcieť poznať dátum a čas. To je možné pomocou vzorca INDEX a MATCH. Vzorec v I7 je:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Pri práci zvnútra smerom von musíme najskôr nájsť pozíciu najnižšieho pondelkového prílivu pomocou funkcie MATCH:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Tu prechádzame rovnakými podmienenými testami, aké sme použili vyššie, aby sme obmedzili spracovanie iba na pondelkové prílivy. Aplikujeme však ešte jeden test na obmedzenie výsledkov na minimálnu hodnotu teraz v I6 a na použitie kritérií použijeme o niečo jednoduchšiu syntax založenú na logickej logike. Máme tri samostatné výrazy, z ktorých každý testuje jednu podmienku:
(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
Toto je príklad, ktorý pekne ukazuje flexibilitu produktu XLOOKUP. Môžeme použiť úplne rovnakú logiku z vyššie uvedených vzorcov INDEX a MATCH v jednoduchom a elegantnom vzorci.
