Generische Formel
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Zusammenfassung
Um den Maximalwert an einem bestimmten Wochentag (z. B. Montag, Dienstag, Mittwoch usw.) zu ermitteln, können Sie eine einfache Array-Formel verwenden, die auf den Funktionen MAX, IF und TEXT basiert. In dem gezeigten Beispiel lautet die Formel in Zelle F5:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Wobei Datumsangaben (B5: B15) und Werte (C5: C15) als Bereiche bezeichnet werden.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabetaste eingegeben werden.
Erläuterung
Mit der TEXT-Funktion wird von innen nach außen ein Wochentagswert für jedes Datum extrahiert:
=TEXT(dates,"ddd")
Dies führt zu einem Array wie dem folgenden:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
Das wird dann mit dem Text in F4 "Mon" verglichen. Das Ergebnis ist ein weiteres Array, das nur TRUE- und FALSE-Werte enthält:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Beachten Sie, dass jeder WAHR einem Montag entspricht. Dieses Array wird innerhalb der IF-Funktion als logischer Test zurückgegeben. Es fungiert als Filter, um Werte an anderen Wochentagen herauszufiltern. Das Endergebnis von IF, das direkt an die MAX-Funktion zurückgegeben wird, sieht folgendermaßen aus:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX ignoriert automatisch FALSE-Werte und gibt den höchsten verbleibenden Wert 94 zurück.
Mit AGGREGATE
Für eine etwas geekigere Formel, die keine Steuerung + Umschalt + Eingabe erfordert, können Sie die AGGREGATE-Funktion wie folgt verwenden:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
Dies ist die Formel, die in Zelle F6 im gezeigten Beispiel verwendet wird. Hier geben wir AGGREGATE 14 für das Funktionsargument (LARGE) und 6 für das Optionsargument (Fehler ignorieren) an. Dann erstellen wir einen logischen Ausdruck mit der TEXT-Funktion, um alle Daten für Montag zu überprüfen. Das Ergebnis dieser Operation ist ein Array von TRUE / FALSE-Werten, die zum Nenner der ursprünglichen Werte werden. Bei Verwendung in einer mathematischen Operation wird FALSE als Null ausgewertet und wirft ein # DIV / 0! Error. TRUE wird als 1 ausgewertet und gibt den ursprünglichen Wert zurück. Das endgültige Array von Werten und Fehlern wirkt wie ein Filter. AGGREGATE ignoriert alle Fehler und gibt den größten (maximalen) der überlebenden Werte zurück.
MAXIFS
Die in Excel Office 365 verfügbare MAXIFS-Funktion kann einen Maximalwert nach einem oder mehreren Kriterien zurückgeben, ohne dass eine Array-Formel erforderlich ist. MAXIFS ist jedoch eine bereichsbasierte Funktion und erlaubt anderen Funktionen wie TEXT nicht, Werte in Kriterienbereichen zu verarbeiten. Sie können den Daten jedoch eine Hilfsspalte hinzufügen, Wochentagswerte mit TEXT generieren und dann MAXIFS mit der Hilfsspalte als Kriterienbereich verwenden.
