Office 2016 Median einzelner Zeitabschnitte und Wochentage über das ganze Jahr berechnen

[xirn]

Hallo zusammen,

es klingt vielleicht etwas komplizierter als es wirklich ist. Die Beispieltabelle angehängt, es geht um folgendes:

Es geht mir um 30-Minuten-Zeitabschnitte pro Wochentag. Z.B. hieße das:
Am Donnerstag, den 01.01.15 von 0 bis 0:29:59 Uhr gibt es einen Eintrag. Von 0:30 bis 0:59:59 gibt es auch einen Eintrag.
Mir geht es um den MEDIAN aller Donnerstage und aller Zeitabschnitte im ganzen Jahr.
Ich möchte also den Median wissen von der Anzahl der Einträge pro Zeitabschnitt (insgesamt 48) und Wochentag, damit ich beispielsweise weiß: Von 14 bis 14:30 sind es nach Median 2,4 Einträge oder so.

Beispiel: Es gibt im Jahr 2015 53 Donnerstage. Jeder dieser Donnerstage hat 48 Zeitabschnitte, z.B.: von 12:30 bis 12:59:59 Uhr, also immer eine Zeitspanne von einer halben Stunde. Für diese halbe Stunde gibt es am ersten Donnerstag 3 Einträge. Das ist der erste Wert, also die 3. Für den nächsten Donnerstag gibt es im selben Zeitraum zb 4 Einträge, das wäre der zweite Wert. Demnach habe ich am Ende 53 Werte für diesen Zeitabschnitt und aus diesen 53 Werten muss der Median gebildet werden.

Die Mittelwerte habe ich schon ausgerechnet, das war kein Problem. Da habe ich einfach die ZÄHLENWENN Funktion (mit Zeitabschnitt und Wochentag als Bedingung) benutzt und dann durch die Anzahl des entsprechenden Wochentags im Jahr geteilt. Beim Median komme ich allerdings nicht wirklich weiter, ohne mir manuell große Hilfstabellen zu machen.

Hat vielleicht jemand eine Idee?

[DL]

Hallo xirn

Mein Vorschlag:
Hilfsspalte "Zeitabschnitt" mit der Formel:

Code: [Auswählen]

=ABRUNDEN(D2*2*24;0)+1
Eine Pivot-Tabelle einfügen:
"Wochentag" in Filter, "Datum" in Spalten, "Zeitabschnitt" in Zeilen und "BIC-Fallnr" in den Wertebereich (Funktion auf Anzahl ändern).

Jetzt kannst du pro Zeile den Median ermitteln.

Problem: Standardmäßig zeigt Excel in Pivot-Tabellen für leere Zellen nichts an. MEDIAN() ignoriert leere Zellen. Damit wäre das Ergebnis verfälscht.
Abhilfe: In den Pivot-Optionen bei "Für leere Zellen anzeigen" eine 0 eintragen.

[xirn]

Absolut fantastisch, vielen Dank!!! Es hat funktioniert!

Da das so gut geklappt hat, habe ich eine weitere Frage, die ich ebenfalls nicht hinbekommen habe:

Es geht darum, Feiertage bei ZÄHLENWENNS Funktion automatisch zu Sonntagen zählen.
Wobei die Feiertage bei den Wochentagen, bei denen sie ursprünglich gezählt wurden, abgezogen werden müssen.
Im Endeffekt geht es darum, Durchschnittswerte pro Zeiteinheiten herauszufinden, indem Werte erst summiert (bzw durch ZÄHLENWENNS gezählt) werden und die Summen durch die Anzahl der Tage geteilt werden.

Alles recht einfach ersichtlich in der angehängten Datei.

PS: Gäbe es da auch eine Möglichkeit für die Pivot-Tabelle bzgl. des Median?

Ich wäre sehr dankbar, wenn jemand eine Idee hätte. Mir fällt da keine gescheite Lösung ein

[DL]

Meinst du es so?

Code: [Auswählen]

=ZÄHLENWENNS($C$2:$C$21620;">="&G5;$C$2:$C$21620;"<="&H5;$D$2:$D$21620;$I$4)-ZÄHLENWENN($T$23:$T$33;I$4)

[xirn]

Habe es inzwischen mit dem Summenprodukt gelöst, hier um die Feiertage zu den Sonntagen zu addieren:

Code: [Auswählen]

=SUMMENPRODUKT(($C$2:$C$21620>=$G5)*($C$2:$C$21620<=$H5)*(($D$2:$D$21620=O$4)+NICHT(ISTFEHLER(VERGLEICH($B$2:$B$21620;$S$23:$S$33;0)))>0))
und hier, um die addierten Feiertage bei den restlichen Tagen abzuziehen:

Code: [Auswählen]

=SUMMENPRODUKT(($C$2:$C$21620>=$G5)*($C$2:$C$21620<=$H5)*($D$2:$D$21620=I$4)*ISTFEHLER(VERGLEICH($B$2:$B$21620;$S$23:$S$33;0)))
Es funktioniert wunderbar.

Das Problem ist, dass ich jetzt die richtigen Durchschnittswerte habe, aber der Median aus der Pivottabelle berechnet wird und in dieser sind die Feiertage nicht den Sonntagen zugeordnet, sondern werden bei ihren ursprünglichen Wochentagen mitgezählt.
Als Lösung Fällt mir eine Hilfsspalte ein, in der ich einfach die Wochentage kopiere und die Entsprechenden Feiertags-Tage mit "Sonntag" ersetze. Sieht aber etwas grobmotirisch aus. Gibt es da eine elegantere Lösung, die evtl. mehr Sinn macht? Evtl. täusche ich mich auch mit meiner Idee?

[DL]

Als Lösung Fällt mir eine Hilfsspalte ein, in der ich einfach die Wochentage kopiere und die Entsprechenden Feiertags-Tage mit "Sonntag" ersetze

Oder in der Hilfsspalte steht nur "Wochentag" oder "Feiertag". Diese Hilfsspalte ziehst du auch in den Filter.

 

[xirn]

Perfekt, vielen Dank!