Generische Formel
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Zusammenfassung
Um den Durchschnitt pro Monat zu ermitteln, können Sie mithilfe der Funktion EOMONTH eine Formel verwenden, die auf der Funktion AVERAGEIFS basiert. In dem gezeigten Beispiel lautet die Formel in F4:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Diese Formel verwendet die genannten Bereiche "Beträge" (D5: D104) und "Daten" (C5: C104).
Erläuterung
Die AVERAGEIFS-Funktion kann Bereiche basierend auf mehreren Kriterien mitteln. In diesem Fall konfigurieren wir AVERAGEIFS so, dass die monatlichen Beträge nach zwei Kriterien gemittelt werden: (1) Übereinstimmungsdaten größer oder gleich dem ersten Tag des Monats, (2) Übereinstimmungsdaten kleiner oder gleich dem letzten Tag des Monats. Wenn wir die Daten für Januar 2016 mithilfe der DATE-Funktion fest in die Formel codieren würden, würde dies so aussehen.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Wir möchten jedoch keine Daten fest codieren, sondern möchten, dass Excel diese Daten für uns generiert. Normalerweise ist dies ein Problem, denn wenn Sie in Spalte F Monatsnamen als Text hinzufügen (z. B. "Januar", "Februar", "März" usw.), müssen Sie zusätzliche Anstrengungen unternehmen, um Daten zu erstellen, die Sie für Kriterien verwenden können .
In diesem Fall verwenden wir jedoch einen einfachen Trick, um die Sache zu vereinfachen: In Spalte F fügen wir anstelle der Eingabe von Monatsnamen tatsächliche Daten für den ersten eines jeden Monats hinzu (1.1.2016, 01.02.2016, 3) / 1/2016 usw.) und verwenden Sie ein benutzerdefiniertes Datumsformat ("mmm"), um die Monatsnamen anzuzeigen.
Dies macht es einfach, die Kriterien zu erstellen, die wir für AVERAGEIFS benötigen. Um Daten zu finden, die größer oder gleich dem ersten des Monats sind, verwenden wir:
">="&E4
Und um Daten abzugleichen, die kleiner oder gleich dem letzten Tag des Monats sind, verwenden wir:
"<="&EOMONTH(E4,0)
EOMONTH gibt automatisch den letzten Tag desselben Monats zurück, da wir für das Argument month null angeben.
Hinweis: Die Verkettung mit einem kaufmännischen Und (&) ist erforderlich, wenn Kriterien basierend auf einer Zellreferenz erstellt werden.
Pivot Table-Lösung
Eine Pivot-Tabelle ist eine hervorragende Lösung, wenn Sie Daten nach Jahr, Monat, Quartal usw. zusammenfassen müssen, da Pivot-Tabellen Steuerelemente für die automatische Gruppierung nach Datum bieten. In diesem Video finden Sie einen direkten Vergleich von Formeln und Pivot-Tabellen: Warum Pivot-Tabellen.
