Sommario
Per trovare la marea più bassa di lunedì, dato un insieme di dati con molti giorni di alta e bassa marea, è possibile utilizzare una formula di matrice basata sulle funzioni SE e MIN. Nell'esempio mostrato, la formula in I6 è:
(=MIN(IF(day=I5,IF(tide="L",pred))))
che restituisce la marea di lunedì più bassa nei dati, -0,64
Per recuperare la data della marea del lunedì più bassa, la formula in I7 è:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Dove il foglio di lavoro contiene i seguenti intervalli denominati: data (B5: B124), giorno (C5: C124), ora (G5: D124), pred (E5: E124), marea (F5: F124).
Entrambe sono formule di matrice e devono essere inserite con CTRL + MAIUSC + INVIO.
Dati da tidesandcurrents.noaa.gov per Santa Cruz, California.
Spiegazione
Ad un livello elevato, questo esempio riguarda la ricerca di un valore minimo basato su più criteri. Per fare ciò, stiamo usando la funzione MIN insieme a due funzioni IF annidate:
(=MIN(IF(day=I5,IF(tide="L",pred))))
lavorando dall'interno verso l'esterno, il primo IF controlla se il giorno è "Mon", in base al valore in I5:
IF(day=I5 // is day "Mon"
Se il risultato è VERO, eseguiamo un altro SE:
IF(tide="L",pred) // if tide is "L" return prediction
In altre parole, se il giorno è "Lun", controlliamo se la marea è "L". In tal caso, restituiamo il livello di marea previsto, utilizzando l'intervallo denominato pred .
Si noti che non forniamo un "valore se falso" per IF. Ciò significa che se uno dei test logici è FALSE, l'IF esterno restituirà FALSE. Per ulteriori informazioni sugli IF annidati, vedere questo articolo.
È importante capire che il set di dati include 120 righe, quindi ciascuno degli intervalli denominati nella formula contiene 120 valori. Questo è ciò che rende questa formula di matrice: stiamo elaborando molti valori contemporaneamente. Dopo che entrambi gli IF sono stati valutati, l'IF esterno restituirà un array che contiene 120 valori come questo:
(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)
La cosa fondamentale da notare qui è che solo i valori associati al lunedì e alla bassa marea sopravvivono al viaggio attraverso gli IF annidati. Gli altri valori sono stati sostituiti con FALSE. In altre parole, stiamo usando la struttura a doppio IF per "buttare via" i valori che non ci interessano.
L'array sopra viene restituito direttamente alla funzione MIN. La funzione MIN ignora automaticamente i valori FALSE e restituisce il valore minimo di quelli che rimangono, -0,64.
Questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO.
Minimo con MINIFS
Se hai Office 365 o Excel 2019, puoi utilizzare la funzione MINIFS per ottenere la marea del lunedì più bassa in questo modo:
=MINIFS(pred,day,"Mon",tide,"L")
Il risultato è lo stesso e questa formula non richiede CTRL + MAIUSC + INVIO.
Ottieni la data
Una volta trovato il livello minimo di marea del lunedì, vorrai senza dubbio conoscere la data e l'ora. Questo può essere fatto con una formula INDICE e CONFRONTA. La formula in I7 è:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Lavorando dall'interno verso l'esterno, dobbiamo prima individuare la posizione della marea del lunedì più bassa con la funzione MATCH:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Qui, eseguiamo gli stessi test condizionali che abbiamo applicato sopra per limitare l'elaborazione solo alle basse maree del lunedì. Tuttavia, applichiamo un altro test per limitare i risultati al valore minimo ora in I6 e utilizziamo una sintassi leggermente più semplice basata sulla logica booleana per applicare i criteri. Abbiamo tre espressioni separate, ciascuna delle quali verifica una condizione:
(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
Questo è un esempio che mostra bene la flessibilità di XLOOKUP. Possiamo usare esattamente la stessa logica delle formule INDICE e CONFRONTA sopra, in una formula semplice ed elegante.
