souhrn
Chcete-li najít nejnižší příliv v pondělí, vzhledem k množině dat s mnoha dny přílivu a odlivu, můžete použít maticový vzorec založený na funkcích IF a MIN. V zobrazeném příkladu je vzorec v I6:
(=MIN(IF(day=I5,IF(tide="L",pred))))
který vrací nejnižší pondělní příliv v datech, -0,64
Chcete-li načíst datum nejnižšího pondělního přílivu, je vzorec v I7:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Pokud list obsahuje následující pojmenované rozsahy: datum (B5: B124), den (C5: C124), čas (D5: D124), před (E5: E124), příliv (F5: F124).
Oba jsou maticové vzorce a musí být zadány pomocí kláves Control + Shift + Enter.
Data z tidesandcurrents.noaa.gov pro Santa Cruz v Kalifornii.
Vysvětlení
Na vysoké úrovni je v tomto příkladu hledání minimální hodnoty na základě více kritérií. K tomu používáme funkci MIN spolu se dvěma vnořenými funkcemi IF:
(=MIN(IF(day=I5,IF(tide="L",pred))))
při práci zevnitř ven první IF zkontroluje, zda je den „Po“, na základě hodnoty v I5:
IF(day=I5 // is day "Mon"
Pokud je výsledek TRUE, spustíme další IF:
IF(tide="L",pred) // if tide is "L" return prediction
Jinými slovy, pokud je den „Po“, zkontrolujeme, zda je příliv „L“. Pokud ano, vrátíme předpokládanou úroveň přílivu a odběru pomocí pojmenovaného rozsahu pred .
Všimněte si, že pro žádný IF neposkytujeme „hodnotu, pokud je nepravdivá“. To znamená, že pokud je jeden logický test FALSE, vnější IF vrátí FALSE. Další informace o vnořených investičních fondech najdete v tomto článku.
Je důležité si uvědomit, že datová sada obsahuje 120 řádků, takže každý z pojmenovaných rozsahů ve vzorci obsahuje 120 hodnot. Díky tomu je tento vzorec pole - zpracováváme mnoho hodnot najednou. Po vyhodnocení obou IF vrátí vnější IF pole, které obsahuje 120 hodnot, jako je tato:
(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)
Klíčovou věcí, kterou si zde všimnete, jsou pouze hodnoty spojené s pondělkem a odlivem, které přežijí cestu vnořenými IF. Ostatní hodnoty byly nahrazeny FALSE. Jinými slovy, používáme strukturu dvojitého IF k „vyhodení“ hodnot, které nás nezajímají.
Pole výše se vrací přímo do funkce MIN. Funkce MIN automaticky ignoruje FALSE hodnoty a vrací minimální hodnotu těch, které zůstanou, -0,64.
Toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Minimum s MINIFY
Pokud máte Office 365 nebo Excel 2019, můžete pomocí funkce MINIFS získat nejnižší pondělní příliv takto:
=MINIFS(pred,day,"Mon",tide,"L")
Výsledek je stejný a tento vzorec nevyžaduje control + shift + enter.
Získejte datum
Jakmile najdete minimální úroveň odlivu v pondělí, nepochybně budete chtít znát datum a čas. To lze provést pomocí vzorce INDEX a MATCH. Vzorec v I7 je:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Při práci zevnitř ven musíme nejprve najít polohu nejnižšího pondělního přílivu pomocí funkce MATCH:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Zde projdeme stejnými podmíněnými testy, které jsme použili výše, abychom omezili zpracování pouze na pondělní odlivy. Aplikujeme však ještě jeden test, abychom omezili výsledky na minimální hodnotu nyní v I6, a k použití kritérií použijeme o něco jednodušší syntaxi založenou na logické logice. Máme tři samostatné výrazy, každý testuje jednu podmínku:
(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 příklad, který pěkně ukazuje flexibilitu XLOOKUPu. Můžeme použít přesně stejnou logiku z výše uvedených vzorců INDEX a MATCH v jednoduchém a elegantním vzorci.
