Zusammenfassung
Um die niedrigste Flut an einem Montag zu finden, können Sie bei einem Datensatz mit vielen Tagen Hoch- und Ebbe eine Array-Formel verwenden, die auf den IF- und MIN-Funktionen basiert. In dem gezeigten Beispiel lautet die Formel in I6:
(=MIN(IF(day=I5,IF(tide="L",pred))))
Dies gibt die niedrigste Montagflut in den Daten zurück, -0,64
Um das Datum der niedrigsten Montagflut abzurufen, lautet die Formel in I7:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Wo das Arbeitsblatt die folgenden benannten Bereiche enthält: Datum (B5: B124), Tag (C5: C124), Uhrzeit (D5: D124), Pred (E5: E124), Gezeiten (F5: F124).
Beide sind Array-Formeln und müssen mit Strg + Umschalt + Eingabe eingegeben werden.
Daten von tidesandcurrents.noaa.gov für Santa Cruz, Kalifornien.
Erläuterung
In diesem Beispiel geht es auf hoher Ebene darum, einen Mindestwert anhand mehrerer Kriterien zu finden. Dazu verwenden wir die MIN-Funktion zusammen mit zwei verschachtelten IF-Funktionen:
(=MIN(IF(day=I5,IF(tide="L",pred))))
Von innen nach außen prüft die erste IF, ob der Tag "Mo" ist, basierend auf dem Wert in I5:
IF(day=I5 // is day "Mon"
Wenn das Ergebnis TRUE ist, führen wir eine weitere IF aus:
IF(tide="L",pred) // if tide is "L" return prediction
Mit anderen Worten, wenn der Tag "Mo" ist, prüfen wir, ob die Flut "L" ist. In diesem Fall geben wir den vorhergesagten Gezeitenpegel unter Verwendung des benannten Bereichs pred zurück .
Beachten Sie, dass wir für keine IF einen "Wert, wenn falsch" angeben. Das heißt, wenn einer der logischen Tests FALSE ist, gibt die äußere IF FALSE zurück. Weitere Informationen zu verschachtelten IFs finden Sie in diesem Artikel.
Es ist wichtig zu verstehen, dass der Datensatz 120 Zeilen enthält, sodass jeder der genannten Bereiche in der Formel 120 Werte enthält. Dies macht dies zu einer Array-Formel - wir verarbeiten viele Werte gleichzeitig. Nachdem beide IFs ausgewertet wurden, gibt die äußere IF ein Array zurück, das 120 Werte wie folgt enthält:
(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)
Das Wichtigste dabei ist, dass nur Werte für Montag und Ebbe die Reise durch die verschachtelten IFs überleben. Die anderen Werte wurden durch FALSE ersetzt. Mit anderen Worten, wir verwenden die doppelte IF-Struktur, um Werte wegzuwerfen, an denen wir nicht interessiert sind.
Das obige Array wird direkt an die MIN-Funktion zurückgegeben. Die MIN-Funktion ignoriert automatisch die FALSE-Werte und gibt den Mindestwert der verbleibenden Werte zurück, -0,64.
Dies ist eine Array-Formel, die mit Strg + Umschalt + Eingabe eingegeben werden muss.
Minimum mit MINIFS
Wenn Sie über Office 365 oder Excel 2019 verfügen, können Sie die MINIFS-Funktion verwenden, um die niedrigste Montagflut wie folgt zu erhalten:
=MINIFS(pred,day,"Mon",tide,"L")
Das Ergebnis ist das gleiche und diese Formel erfordert keine Steuerung + Umschalt + Eingabe.
Holen Sie sich das Datum
Sobald Sie das Mindestniveau für die Gezeiten am Montag gefunden haben, möchten Sie zweifellos Datum und Uhrzeit wissen. Dies kann mit einer INDEX- und MATCH-Formel erfolgen. Die Formel in I7 lautet:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Von innen nach außen müssen wir zuerst die Position der niedrigsten Montagflut mit der MATCH-Funktion ermitteln:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Hier durchlaufen wir dieselben bedingten Tests, die wir oben angewendet haben, um die Verarbeitung nur auf Ebbe am Montag zu beschränken. Wir wenden jedoch einen weiteren Test an, um die Ergebnisse auf den Mindestwert in I6 zu beschränken, und verwenden eine etwas einfachere Syntax basierend auf der Booleschen Logik, um Kriterien anzuwenden. Wir haben drei separate Ausdrücke, die jeweils eine Bedingung testen:
(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
Dies ist ein Beispiel, das die Flexibilität von XLOOKUP deutlich macht. Wir können genau dieselbe Logik aus den obigen INDEX- und MATCH-Formeln in einer einfachen und eleganten Formel verwenden.
