Generische Formel
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Zusammenfassung
Um den Maximalwert in einem bestimmten Monat zu ermitteln, können Sie die MAXIFS-Funktion oder eine der folgenden Alternativen verwenden. Im gezeigten Beispiel lautet die Formel in G5:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Wo Verkäufe (D5: D15), Daten (B5: B15) und Werte (C5: C15) als Bereiche bezeichnet werden.
Erläuterung
Die MAXIFS-Funktion kann den Maximalwert in einem Bereich anhand eines oder mehrerer Kriterien ermitteln. In dem gezeigten Beispiel verwenden wir MAXIFS, um den maximalen Verkaufswert basierend auf einem bestimmten Monat zu ermitteln, indem wir die Daten zwischen dem ersten Tag des Monats und dem letzten Tag des Monats in Klammern setzen. Das erste Kriterium prüft, ob die Daten größer oder gleich dem ersten des Monats sind:
dates,">="&G4,dates // first of month
Hinweis: Wir gehen davon aus, dass das Datum in G4 ein Datum für den ersten Monat ist.
Das zweite Kriterium prüft, ob die Daten kleiner oder gleich dem letzten des Monats sind, berechnet mit der Funktion EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Wenn beide Kriterien TRUE zurückgeben, liegt das Datum im angegebenen Monat, und MAXIFS gibt den Maximalwert der Daten zurück, die die Kriterien erfüllen.
Alternative Formeln
Wenn Ihre Excel-Version die MAXIFS-Funktion nicht enthält, gibt es andere Optionen. Eine Option ist eine einfache Array-Formel, die auf den Funktionen MAX und IF basiert:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
Eine andere Möglichkeit besteht darin, die AGGREGATE-Funktion wie folgt zu verwenden:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Beide oben genannten Optionen verwenden eine einfachere Methode zum Testen von Daten basierend auf der TEXT-Funktion, die ein Zahlenformat verwendet, um den Monat und das Jahr aus einem Datum zu extrahieren. Die TEXT-Funktion kann in der MAXIFS-Funktion nicht so verwendet werden, da für MAXIFS ein Bereich erforderlich ist. Diese alternativen Ansätze werden hier näher erläutert.
Der maximale Client
Um den Client abzurufen, der dem Maximalwert in einem bestimmten Monat zugeordnet ist, lautet die Formel in G6:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
Die Idee hier ist, dass wir bereits den Maximalwert in einem Monat kennen (G5) und diesen Wert als "Schlüssel" verwenden können, um den Client zu suchen. Beachten Sie, dass wir die Logik zum Isolieren von Werten im angegebenen Monat wiederholen müssen, um eine falsche Übereinstimmung in einem anderen Monat zu vermeiden.
Wie die oben genannten Alternativen verwendet auch diese Formel einen vereinfachten Test für Daten, die auf der TEXT-Funktion basieren. Lesen Sie hier mehr über INDEX und MATCH mit mehreren Kriterien.
Ersetzen Sie für eine All-in-One-Formel G5 durch eine der oben erläuterten Formeloptionen, um den Maximalwert in einem bestimmten Monat zu bestimmen.
