Angenommen, Sie haben ein riesiges Arbeitsblatt, das alle Niederschlagswerte für ein bestimmtes Gebietsschema der letzten hundert Jahre enthält. In den Zellen A2: A42733 haben Sie die Daten vom 1. Januar 1903 bis zum 31. Dezember 2019.

In den Zellen B2: B42733 haben Sie die Messungen für jedes Datum. Außerdem können einige der Messungen Null sein (wenn es an diesem Tag keinen Niederschlag gibt) oder leer sein (wenn an diesem bestimmten Tag keine Messung durchgeführt wurde). Mit all diesen Informationen möchten Sie den durchschnittlichen historischen Niederschlag für einen bestimmten Tag des Jahres berechnen.

Eine Lösung besteht in der Verwendung von Array-Formeln, wie hier beschrieben:

  1. Wählen Sie alle Zellen in Spalte A aus, die Datumsangaben enthalten, und weisen Sie diesem Bereich den Namen Datumsangaben zu.

  2. Wählen Sie alle entsprechenden Zellen in Spalte B aus. Diese enthalten die Niederschlagsdaten für die Daten in Spalte A. Weisen Sie diesem Bereich den Namen Niederschlag zu.

  3. Geben Sie in Spalte D ab Zelle D2 alle Tage des Jahres ein. Sie sollten mit D2 bis D366 enden, die mit Daten gefüllt sind.

  4. Geben Sie in Zelle E2 die folgende Array-Formel ein (beenden Sie die Formel durch Drücken von Umschalt + Strg + Eingabetaste). Das Ergebnis der Formel ist die Summe aller Zellen im Niederschlagsbereich für das in Zelle D2 angegebene Datum.

  5. Geben Sie in Zelle F2 die folgende Array-Formel ein (beenden Sie die Formel durch Drücken von Umschalt + Strg + Eingabetaste). Das Ergebnis der Formel ist die Anzahl der Zellen im Niederschlagsbereich für das Datum in Zelle D2, die einen Wert enthalten.

  6. Geben Sie in Zelle G2 die folgende reguläre Formel ein. Dies ist Ihr Durchschnitt für das Datum in Zelle D2.

  7. Wählen Sie den Bereich E2: G2 und kopieren Sie alle in Spalte D angegebenen Daten nach unten.

Dieser Ansatz funktioniert, die Berechnung dauert jedoch eine Weile. Dies liegt daran, dass Sie effektiv 822 Array-Formeln eingegeben haben, von denen jede über 42.000 Zellen überprüft. Dies ist eine Menge Arbeit, und folglich kann es so aussehen, als ob Ihre Maschine nach Abschluss von Schritt 7 „hängen geblieben“ ist. Sie hat nicht gehangen; Es dauert nur eine Weile, bis die Berechnungen abgeschlossen sind.

Um die Anzahl der durchzuführenden Berechnungen zu verringern, können Sie die obigen Schritte variieren. Befolgen Sie die Schritte 1 bis 3 wie angegeben und platzieren Sie dann die folgende Array-Formel in Zelle E2:

=AVERAGE(IF((Dates<>0)(Rainfall<>0)(MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2)),Rainfall))

Sie können dann die Formel für alle in Spalte D angegebenen Daten nach unten kopieren.

Das Ergebnis dieser Formel ist der tatsächliche durchschnittliche Niederschlag, der dem vorherigen Ansatz in Spalte G entspricht.

Sie können den Berechnungsaufwand noch weiter reduzieren, indem Sie einfach alle Tabellen entfernen, in denen die Durchschnittswerte für jeden Tag des Jahres berechnet werden. Gehen Sie mit Ihren Daten und Niederschlägen in den Spalten A und B folgendermaßen vor:

  1. Wählen Sie alle Zellen in Spalte A aus, die Datumsangaben enthalten, und weisen Sie diesem Bereich den Namen Datumsangaben zu.

  2. Wählen Sie alle entsprechenden Zellen in Spalte B aus. Diese enthalten die Niederschlagsdaten für die Daten in Spalte A. Weisen Sie diesem Bereich den Namen Niederschlag zu.

  3. Geben Sie in Zelle D2 das Datum ein, für das Sie den durchschnittlichen Niederschlag überprüfen möchten. (Das Jahr ist nicht wichtig; bei der Berechnung werden nur der Monat und der Tag verwendet.)

  4. Geben Sie die folgende Formel in Zelle E2 ein:

=AVERAGE(IF((Dates<>0)(Rainfall<>0)(MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2)),Rainfall))

Das ist es. Jetzt können Sie das Datum in Zelle D2 wie gewünscht ändern, und Zelle E2 zeigt immer den durchschnittlichen Niederschlag für dieses Datum an. Die Formel in Zelle E2 ist dieselbe wie die im letzten Ansatz verwendete Formel. Der Unterschied besteht darin, dass Sie es nicht für alle Tage eines Jahres berechnen und die Berechnung daher viel schneller erfolgt.

Ein anderer Ansatz beinhaltet die Verwendung der Filterfunktionen von Excel.

Bevor Sie sie jedoch ordnungsgemäß verwenden können, müssen Sie eine Spalte erstellen, in der nur der Monat und der Tag für jedes Datum in Ihren Daten angezeigt werden. Verwenden Sie diese Formel in Zelle C2:

=MONTH(A2) & "-" & DAY(A2)

Aktivieren Sie nun die automatische Filterung (zeigen Sie die Registerkarte Daten in der Multifunktionsleiste | Gruppe & Filter & Filter | Filter an) und wählen Sie in der Dropdown-Liste oben in der neuen Spalte das Datum aus, für das Sie einen Durchschnitt wünschen. Anschließend verwenden Sie die folgende Formel in einer beliebigen Zelle, um den durchschnittlichen Niederschlag für das ausgewählte Datum anzuzeigen:

=SUBTOTAL(1,B:B)

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

Dieser Tipp (10319) 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:

Berechnen von Durchschnittswerten nach Datum.