Resumo
Para encontrar a maré mais baixa em uma segunda-feira, dado um conjunto de dados com muitos dias de maré alta e baixa, você pode usar uma fórmula de matriz com base nas funções IF e MIN. No exemplo mostrado, a fórmula em I6 é:
(=MIN(IF(day=I5,IF(tide="L",pred))))
que retorna a menor maré de segunda-feira nos dados, -0,64
Para recuperar a data da maré mais baixa de segunda-feira, a fórmula em I7 é:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Onde a planilha contém os seguintes intervalos nomeados: data (B5: B124), dia (C5: C124), hora (D5: D124), pred (E5: E124), maré (F5: F124).
Ambos são fórmulas de matriz e devem ser inseridos com control + shift + enter.
Dados de tidesandcurrents.noaa.gov para Santa Cruz, Califórnia.
Explicação
Em um nível superior, este exemplo é sobre como encontrar um valor mínimo com base em vários critérios. Para fazer isso, estamos usando a função MIN junto com duas funções IF aninhadas:
(=MIN(IF(day=I5,IF(tide="L",pred))))
trabalhando de dentro para fora, o primeiro IF verifica se o dia é "seg", com base no valor em I5:
IF(day=I5 // is day "Mon"
Se o resultado for TRUE, executamos outro IF:
IF(tide="L",pred) // if tide is "L" return prediction
Ou seja, se o dia for “seg”, verificamos se a maré está “L”. Nesse caso, retornamos o nível de maré previsto, usando o intervalo nomeado pred .
Observe que não fornecemos um "valor se falso" para nenhum dos IF. Isso significa que se um dos testes lógicos for FALSO, o IF externo retornará FALSO. Para obter mais informações sobre IFs aninhados, consulte este artigo.
É importante entender que o conjunto de dados inclui 120 linhas, portanto, cada um dos intervalos nomeados na fórmula contém 120 valores. Isso é o que a torna uma fórmula de matriz - estamos processando muitos valores de uma vez. Depois que os dois IFs forem avaliados, o IF externo retornará uma matriz que contém 120 valores como este:
(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)
O principal a ser observado aqui é que apenas os valores associados à segunda-feira e à maré baixa sobrevivem à viagem pelos IFs aninhados. Os outros valores foram substituídos por FALSE. Em outras palavras, estamos usando a estrutura IF dupla para "jogar fora" valores nos quais não estamos interessados.
A matriz acima é retornada diretamente para a função MIN. A função MIN ignora automaticamente os valores FALSE e retorna o valor mínimo daqueles que permanecem, -0,64.
Esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Mínimo com MINIFS
Se você tem o Office 365 ou o Excel 2019, pode usar a função MINIFS para obter a maré mais baixa de segunda-feira como esta:
=MINIFS(pred,day,"Mon",tide,"L")
O resultado é o mesmo e esta fórmula não requer control + shift + enter.
Pegue a data
Depois de encontrar o nível mínimo da maré na segunda-feira, sem dúvida você desejará saber a data e a hora. Isso pode ser feito com uma fórmula INDEX e MATCH. A fórmula em I7 é:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Trabalhando de dentro para fora, precisamos primeiro localizar a posição da maré mais baixa de segunda-feira com a função MATCH:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Aqui, executamos os mesmos testes condicionais que aplicamos acima para restringir o processamento apenas às marés baixas de segunda-feira. No entanto, aplicamos mais um teste para restringir os resultados ao valor mínimo agora em I6 e usamos uma sintaxe um pouco mais simples baseada na lógica booleana para aplicar os critérios. Temos três expressões separadas, cada uma testando uma condição:
(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
Este é um exemplo que mostra muito bem a flexibilidade do XLOOKUP. Podemos usar exatamente a mesma lógica das fórmulas INDEX e MATCH acima, em uma fórmula simples e elegante.
