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 Mai 2011 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)=2011)(B2:B1000)) / (SUMPRODUCT((MONTH(A2:A1000)=5)(YEAR(A2:A1000)=2011)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 Mai 2011 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)=2011),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 >4/30/11     <6/1/11

In der Kriterientabelle heißt es, dass DAVERAGE alles verwenden soll, in dem die Spalte Datum ein Datum größer als 30.04.11 und ein Datum kleiner als 01.06.11 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 aktivieren Sie den AutoFilter (wählen Sie Daten | Filter | AutoFilter). Klicken Sie oben in Spalte A auf den Filterpfeil und wählen Sie dann Benutzerdefinierten Filter aus der Dropdown-Liste. Excel zeigt das Dialogfeld Benutzerdefinierter AutoFilter an. (Siehe Abbildung 1.)

image

Abbildung 1. Das Dialogfeld Benutzerdefinierter AutoFilter.

Verwenden Sie die Steuerelemente im Dialogfeld, um anzugeben, dass Datensätze größer als 30.04.11 und kleiner als 01.06.11 sein sollen. Wenn Sie auf OK klicken, werden nur die Datensätze im Mai 2011 angezeigt, und die Zwischensummenformel zeigt den Durchschnitt dieser sichtbaren Datensätze an.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (10670) gilt für Microsoft Excel 97, 2000, 2002 und 2003.

Eine Version dieses Tippes für die Multifunktionsleistenoberfläche von Excel (Excel 2007 und höher) finden Sie hier:

Mittelwerte für einen bestimmten Monat und ein bestimmtes Jahr.