Sammanfattning
För att hitta lägsta tidvatten på måndag, med en uppsättning data med många dagar med höga och låga tidvatten, kan du använda en matrisformel baserad på funktionerna IF och MIN. I exemplet som visas är formeln i I6:
(=MIN(IF(day=I5,IF(tide="L",pred))))
som returnerar det lägsta måndagsvattnet i data, -0,64
För att hämta datumet för det lägsta måndagvattnet är formeln i I7:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Där kalkylbladet innehåller följande namngivna intervall: datum (B5: B124), dag (C5: C124), tid (D5: D124), pred (E5: E124), tidvatten (F5: F124).
Båda är matrisformler och måste anges med kontroll + skift + enter.
Data från tidesandcurrents.noaa.gov för Santa Cruz, Kalifornien.
Förklaring
På hög nivå handlar detta exempel om att hitta ett minimivärde baserat på flera kriterier. För att göra det använder vi MIN-funktionen tillsammans med två kapslade IF-funktioner:
(=MIN(IF(day=I5,IF(tide="L",pred))))
arbetar inifrån och ut, den första IF kontrollerar om dagen är "mån", baserat på värdet i I5:
IF(day=I5 // is day "Mon"
Om resultatet är SANT kör vi en annan IF:
IF(tide="L",pred) // if tide is "L" return prediction
Med andra ord, om dagen är "må" kontrollerar vi om tidvattnet är "L". Om så är fallet returnerar vi den förutspådda tidvattennivån med hjälp av det angivna intervallet pred .
Observera att vi inte ger ett "värde om det är falskt" för varken IF. Det betyder att om något logiskt test är FALSE kommer det yttre IF att returnera FALSE. Mer information om kapslade IF: er finns i den här artikeln.
Det är viktigt att förstå att datamängden innehåller 120 rader, så att vart och ett av de angivna intervallen i formeln innehåller 120 värden. Det är detta som gör detta till en matrisformel - vi bearbetar många värden samtidigt. När båda IF: erna har utvärderats kommer den yttre IF att returnera en matris som innehåller 120 värden så här:
(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)
Det viktigaste att märka här är bara värden associerade med måndag och lågvatten överlever resan genom de kapslade IF: erna. De andra värdena har ersatts med FALSE. Med andra ord använder vi den dubbla IF-strukturen för att "kasta bort" värden som vi inte är intresserade av.
Arrayen ovan returneras direkt till MIN-funktionen. MIN-funktionen ignorerar automatiskt FALSE-värdena och returnerar minimivärdet för de kvarvarande, -0,64.
Detta är en matrisformel och måste anges med kontroll + skift + enter.
Minsta med MINIFS
Om du har Office 365 eller Excel 2019 kan du använda MINIFS-funktionen för att få det lägsta måndagsvattnet så här:
=MINIFS(pred,day,"Mon",tide,"L")
Resultatet är detsamma, och den här formeln kräver inte kontroll + shift + enter.
Få datumet
När du väl har hittat lägsta tidvattennivå på måndag vill du utan tvekan veta datum och tid. Detta kan göras med en INDEX- och MATCH-formel. Formeln i I7 är:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
När vi arbetar inifrån och ut måste vi först hitta positionen för det lägsta måndagvattnet med MATCH-funktionen:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Här går vi igenom samma villkorliga tester som vi använde ovan för att begränsa behandlingen till måndagens lågvatten. Vi tillämpar dock ytterligare ett test för att begränsa resultaten till minimivärdet nu i I6, och vi använder en något enklare syntax baserad på boolesk logik för att tillämpa kriterier. Vi har tre separata uttryck, var och en testar ett villkor:
(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
Detta är ett exempel som snyggt visar XLOOKUPs flexibilitet. Vi kan använda exakt samma logik från INDEX- och MATCH-formlerna ovan, i en enkel och elegant formel.
