Mittelung der Werte für einen bestimmten Monat und ein bestimmtes Jahr (Microsoft Excel)
George hat ein Arbeitsblatt, das Datumsangaben (in Spalte A) und mit diesen Daten verknüpfte Werte (in Spalte B) enthält. Das Arbeitsblatt enthält Werte für die letzten Jahre. Er möchte den Durchschnitt aller Werte für einen bestimmten Monat in einem bestimmten Jahr berechnen. Zum Beispiel möchte George den Durchschnitt aller Werte für Februar 2020 berechnen.
Es gibt verschiedene Möglichkeiten, um dieses Problem anzugehen. Eine Möglichkeit besteht darin, eine PivotTable basierend auf Ihren Daten zu erstellen. (PivotTables eignen sich hervorragend zum Aggregieren und Analysieren großer Datenmengen.) Sie können das Wertefeld einfach auf Durchschnitt (anstelle der Standardsumme) setzen und die Spalte Datumsangaben nach Ihren Wünschen gruppieren.
Wenn Sie lieber keine PivotTable verwenden möchten, können Sie Ihrem Arbeitsblatt eine beliebige Anzahl von Formeln hinzufügen. In der folgenden Formel wird beispielsweise die Funktion SUMPRODUCT verwendet, um den Durchschnitt zu berechnen:
=SUMPRODUCT((MONTH(A2:A1000)=5)(YEAR(A2:A1000)=2020)(B2:B1000)) / (SUMPRODUCT((MONTH(A2:A1000)=5)(YEAR(A2:A1000)=2020)1))
Die Formel geht davon aus, dass Ihre Daten und Werte in Zeile 2 beginnen (um Überschriften zu berücksichtigen) und nicht über Zeile 1000 hinausgehen. Wenn die Daten keine Daten enthalten, die im Monat Februar 2020 liegen, gibt die Formel ein #DIV zurück / 0! Error.
Ein anderer Ansatz besteht darin, eine Array-Formel wie die folgende zu verwenden:
=AVERAGE(IF((MONTH(A2:A1000)=5)*(YEAR(A2:A1000)=2020),B2:B1000))
Dieser Ansatz ist kürzer als die SUMPRODUCT-Formel, aber Sie müssen daran denken, bei der Eingabe der Formel Strg + Umschalt + Eingabetaste gedrückt zu halten.
Sie erhalten auch die Division durch den Fehler Null, wenn für den gewünschten Monat und das gewünschte Jahr keine Daten vorhanden sind.
Ein weiterer Ansatz ist die Verwendung einer der Datenbankfunktionen von Excel, DAVERAGE. Sie müssen lediglich eine Kriterientabelle einrichten, die definiert, wonach Sie suchen. Angenommen, die Überschriften in den Spalten sind originell, z. B. Datum (Zelle A1) und Wert (Zelle B1).
Sie können eine Kriterientabelle an einer anderen Stelle einrichten, z. B. D1: E2. Die Tabelle könnte folgendermaßen aussehen:
Date Date >1/31/20 <3/1/20
In der Kriterientabelle heißt es, dass DAVERAGE alles verwenden soll, in dem die Spalte Datum ein Datum größer als 1/31/20 und ein Datum kleiner als 3/1/20 enthält. Hier ist die Formel:
=DAVERAGE(A1:B1000,"Value",D1:E2)
Der erste Parameter definiert Ihre Datenbank, der zweite Parameter gibt an, dass Sie die Informationen in der Spalte Wert (Spalte B) mitteln möchten, und der dritte Parameter teilt DAVERAGE mit, wo sich Ihre Kriterientabelle befindet.
Eine recht einfache Möglichkeit besteht darin, Datumsfilterungen anzuwenden und die Funktion SUBTOTAL zu verwenden. Geben Sie die folgende Formel in eine Zelle ein:
=SUBTOTAL(101,B2:B1000)
Wählen Sie eine Zelle in Ihrem Datenbereich aus und filtern Sie Ihre Daten (zeigen Sie die Registerkarte Daten des Menübands an und klicken Sie auf das Filterwerkzeug). Klicken Sie oben in Spalte A auf den Filterpfeil und wählen Sie dann Datumsfilter | Benutzerdefinierter Filter aus der Dropdown-Liste. Excel zeigt das Dialogfeld Benutzerdefinierter AutoFilter an.
(Siehe Abbildung 1.)
Abbildung 1. Das Dialogfeld Benutzerdefinierter AutoFilter.
Verwenden Sie die Steuerelemente im Dialogfeld, um anzugeben, dass Datensätze größer als 31.01.20 und kleiner als 01.03.20 sein sollen. Wenn Sie auf OK klicken, werden nur die Datensätze im Februar 2020 angezeigt, und die Zwischensummenformel zeigt den Durchschnitt dieser sichtbaren Datensätze an.
Der vielleicht einfachste Ansatz ist jedoch die Verwendung der AVERAGEIFS-Funktion. Sie können einen Durchschnitt einiger Werte berechnen, je nachdem, ob ein zugeordneter Wert (in diesem Fall das Datum) die von Ihnen angegebenen Kriterien erfüllt. So sieht die Formel aus:
=AVERAGEIFS(B2:B1000,A2:A1000,">31 Jan 2020",A2:A1000,"<01 Mar 2020")
Beachten Sie, dass dies keine Array-Formel ist und Sie keine Kriterientabelle für Ihre Kriterien definieren müssen – sie sind direkt in die Formel integriert.
ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.
Dieser Tipp (10671) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier: