John hat ein riesiges Arbeitsblatt, das tägliche Daten über ungefähr sechzig Jahre enthält. Er möchte eine Formel entwickeln, die die Medianwerte für jeden Monat in dem von den Daten abgedeckten Zeitraum berechnet.

Bevor Sie eine Lösung vorschlagen, sollten Sie einige Annahmen treffen. Für die Zwecke dieses Tipps nehmen wir an, dass sich die täglichen Daten in den Spalten A und B befinden. In Spalte A sind die mit den Daten verknüpften Daten und in Spalte B die tatsächlichen Datenwerte für jedes dieser Daten. Ferner enthalten die Zellen A1 und B1 Überschriften für jede Spalte. Dies bedeutet, dass die tatsächlichen Daten ungefähr im Bereich von A2: B22000 liegen.

Um die Verwendung Ihrer Formeln zu vereinfachen, sollten Sie in beiden Spalten A und B Namen für die Daten definieren. Wählen Sie den Datumsbereich aus (z. B. A2: A22000) und weisen Sie ihm einen Namen wie AllDates zu. Wählen Sie den Bereich der entsprechenden Daten aus (z. B. B2: B22000) und erstellen Sie auf dieselbe Weise einen Namen wie AllData.

Sie können Array-Formeln verwenden, um die tatsächlichen Medianwerte zu berechnen. Dazu muss eine weitere Datentabelle eingerichtet werden, die die Mediane enthält. Platzieren Sie Überschriften wie „Monat“ in Zelle E1 und „Median“ in Zelle F1. Platzieren Sie in Zelle E2 den ersten Tag des ersten Monats Ihres Datensatzes, z. B. den 1.1.1940. Geben Sie in Zelle E3 ein Datum ein, das einen Monat später liegt, z. B. den 01.02.1940. Wählen Sie diese beiden Zellen aus (E2: E3) und ziehen Sie den Füllpunkt für die gewünschte Anzahl von Monaten in der Datentabelle nach unten.

Wenn Ihre Daten aus 60 Jahren keine Leerzeichen enthalten, geben Sie die folgende Formel in Zelle F2 ein:

=MEDIAN(IF(DATE(YEAR(AllDates),MONTH(AllDates),1)=E2,AllData))

Schließen Sie die Formel ab, indem Sie Strg + Umschalt + Eingabetaste drücken. Dadurch wird Excel mitgeteilt, dass es sich um eine Array-Formel handelt. Sie können dann die Formel in F2 in jede der Zellen in Spalte F kopieren, die einen entsprechenden Monat in Spalte E enthält. Die Formel analysiert die Daten in Spalte B und ob Jahr und Monat dem Datum entsprechen, das Sie in Zelle E2 eingegeben haben Dann wird der Median aus allen entsprechenden Datenpunkten berechnet.

Wenn Ihre Daten aus 60 Jahren Leerzeichen enthalten (ein Datum in Spalte A ohne entsprechenden Wert in Spalte B), behandelt die Formel das Leerzeichen so, als wäre es ein Nullwert. Wenn Sie Leerzeichen haben, kann dies zu verzerrten Medianwerten führen. Um dies zu umgehen, können Sie eine andere Array-Formel verwenden, die nach leeren Werten sucht und diese ignoriert:

=MEDIAN(IF((DATE(YEAR(AllDates),MONTH(AllDates),1)=E2)*ISNUMBER(AllData),AllData))

Es gibt eine Einschränkung bei der Verwendung von Array-Formeln auf diese Weise. Wenn Sie Daten aus 60 Jahren mit ungefähr 22.000 Einzelwerten haben, dann sind das immer noch viele Monate: ungefähr 720 davon. Das bedeutet, dass Sie 720 Array-Formeln erstellen, die jeweils 22.000 Datenwerte analysieren, um eine Antwort zu erhalten. Das ist eine Menge Rechenaufwand, sodass Sie bei jeder Neuberechnung des Arbeitsblatts eine Verlangsamung der Reaktionsfähigkeit von Excel feststellen werden.

Wenn die Trägheit zu einem Problem wird, können Sie Ihre Originaldaten so reorganisieren, dass jede Zeile des Arbeitsblatts einen einzelnen Monat darstellt. Spalte A könnte den Monat für die Zeile enthalten (01.01.1940, 01.02.1940, 01.03.1940 usw.), und die Spalten B: AF wären die Tage 1 bis 31 für jeden Monat. Die sich überschneidenden Zellen in der Tabelle könnten dann den Datenpunkt für jeden Tag im Monat enthalten, und Sie könnten die MEDIAN-Funktion in Spalte AG verwenden, um den Median für jeden Monat zu berechnen. Dies führt immer noch zu 720 Formeln, aber dies sind reguläre Formeln, die jeweils nur Daten im Wert von einem Monat verarbeiten müssen, anstatt der Array-Formeln, die für jede Datenverarbeitung 60 Jahre benötigen. Das Ergebnis sind viel schnellere Berechnungen.

Für die meisten Menschen reicht die Idee, eine so große Datenmenge neu zu organisieren, natürlich aus, um Sie nachts wach zu halten. Stattdessen können Sie die Daten ganz anders analysieren. Dieser Ansatz ist möglich, da ein Median eine sehr einfach zu berechnende statistische Funktion ist. Sie sortieren einfach Ihren Datensatz und wählen das mittlere Element aus, wenn die Anzahl der Elemente im Datensatz ungerade ist. Wenn die Anzahl der Elemente gerade ist, nehmen Sie den Durchschnitt der beiden mittleren Elemente.

Um sich auf die Analyse der Daten vorzubereiten, müssen Sie einige Dinge tun.

Erstens ist es praktisch, den Monat jedes Datenpunkts eindeutig zu identifizieren. Geben Sie in Zelle C2 die folgende Formel ein:

=100*Year(A2)+Month(A2)

Dies führt dazu, dass ein Wert wie 194001, 194002, 194003 usw. in Spalte C gespeichert wird. Dies ist der eindeutige Monatswert. Jetzt sollten Sie die Daten nach Spalte C und dann nach Spalte B sortieren. Beide Sortierungen sollten in aufsteigender Reihenfolge erfolgen, damit Ihre Daten zuerst nach Jahr / Monat und dann nach Wert innerhalb des Jahres / Monats sortiert werden.

Als nächstes müssen Sie Ihren Daten Zwischensummen hinzufügen. Wählen Sie Zwischensummen aus dem Menü Daten, das das Dialogfeld Zwischensummen anzeigt. Sie möchten bei jeder Änderung in Spalte C eine Zwischensumme hinzufügen. Die zu verwendende Funktion ist Count, und Sie möchten die Zwischensumme zu Spalte B hinzufügen. Wenn Sie auf OK klicken, erhalten Sie 720 Zwischensummen, eine für jeden Monat in den Daten Bereich, wobei jeder eine Zählung der Anzahl der Datenelemente anzeigt, die in diesem Monat vorhanden waren.

Um die Mediane zu erhalten, fügen Sie der Zelle D2 eine Formel hinzu:

IF(RIGHT(B2,5)="Count", IF(MOD(C2,2)=1, INDIRECT("B"&(ROW()-1)-C2/2+1/2), (INDIRECT("B"&(ROW()-1)-C2/2)+INDIRECT("B"&(ROW()-1)-C2/2+1))/2), "")

Die Formel untersucht, was sich in Zelle B2 befindet, und wenn sie das Wort „Anzahl“ enthält, weiß sie, dass dies eine Zwischensummenzeile ist. In diesem Fall wird geprüft, ob die Anzahl in Zelle C2 ungerade oder gerade ist. Wenn es ungerade ist, wird die INDIRECT-Funktion verwendet, um den Medianwert für den Monat zu ermitteln. Wenn es gerade ist, werden die beiden Mittelwerte für den Monat addiert und in zwei Hälften geteilt.

Das Ergebnis ist, dass Sie jetzt Medianwerte für jeden Monat in derselben Zeile wie Ihre Zwischensummen haben. Auf diese Weise können Sie die Gliederung für die Daten reduzieren (klicken Sie auf die Pluszeichen im Gliederungsbereich links neben Ihren Daten)

so dass Sie nur diese Zwischensummenzeilen anzeigen.

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

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

Ableiten monatlicher Medianwerte.