Office: Monatsdurchschnitte für Zeitreihen bilden

Hallo liebe Leute,

ich habe ein Problem bei der Bildung von Monatsdurschnitten.
Meine Tabelle sieht so aus: Spalte 1: "date" in form von TT.MM.JJJJ, wird aber al TT-MM-JJJJ angezeigt. Dabei sind die Anzahl der Beobachtungen pro Monat unterschiedlich, meistens habe ich Beobachtungen im Abstand von etwa drei Tagen. Das Datum beginnt mit dem 03-01-2007 und endet am 31-12-2014 (1212 Datumsangaben).
In den Spalten 2 bis 53 befinden sich Städte für die zum jeweiligen Datum ein Preis erhoben wurde.

Ich möchte aus den vielen Beobachtung pro Monat für jede der 53 Städte jeweils einen Monatsdurchschnitt bekommen. Also für alle 53 Städte jeweils den Durchschnitt für Januar 07, Februar 07, ... , Dezember 14.

Ich verwende die Englische Office Version.
Ich nehme an ich muss mit dem averageif-Befehl arbeiten!? Leider habe ich den noch nicht recht verstanden.
Wie erkläre ich Excel, dass es die Monatsdurchscnitte für jeden Monat und jede Stadt bilden soll, wobei die Anzahl der Beobachtungen pro Monat und Stadt unterschiedlich ist?

Ich habe mal einen Auszug der Tabelle mit drei Städten angehängt.
Für Tipps und Hilfe bin ich sehr dankbar!!

 

Hola,

dazu musst du die Daten in F2 und folgende in richtige Daten umwandeln.

In F2: 01.01.2007, in F3: 01.02.2007 usw. Formatieren kannst du diese Daten dann benutzerdefiniert als: MM-JJJJ.

Dann müssen die Namen der Städte auch so in G1, H1, I1.....stehen - ohne "monthly average....".

Erst jetzt kannst du sinnvoll vergleichen - allerdings nicht mit "averageif", da hier nur eine Bedingung abgefragt wird.

Summenprodukt ist im Englischen sumproduct.

Gruß,
steve1da

 

Hi Just11111,

mein Ansatz ist gleich dem von Steve1da. Ich habe nur mit Hilfsspalten gearbeitet.

Gruß
podie

 

Wow, ich bin echt beeindruckt! Da wäre ich nie drauf gekommen! Das ist echt ein super Befehl, vielen Dank steve1da!

Allerdings gibt es noch ein Problem das bei dem Befehl noch nicht gelöst ist.
Teilweise fehlen für einige Tage einzelne Beobachtungen. Das verfälscht natürlich den Durchschnitt. Das kann man gut in der Beispieltabelle sehen:
Für Januar 2007 ergibt dein Befehl für die 2. Stadt (Mexicali) einen Durchschnitt von 4.23, wobei sechs Beobachtungen jeweils mit dem Wert 12 gemacht wurden. Das Ergebnis sollte daher 12 und nicht 4.23 sein.

Wie kann man also den Befehl ergänzen, damit Excel für die Berechnung des Durchschnitts nur durch die Anzahl von Beobachtungen teilt, für die auch wirklich eine Beobachtung vorliegt?

Vielen Dank schon mal für deine Mühen!
Gruß,
Just

 

Hola,

ich hatte meine Formel zwischenzeitlich noch leicht abgeändert.

Gruß,
steve1da

 

Hola,

@podie: deine Lösung erfordert aber viel Handarbeit - außerdem wird der Ort nicht berücksichtigt.

Gruß,
steve1da

 

Hallo,

@steve1da

...und ich dachte ich hätte den großen Wurf gelandet, dann aber beim nächsten Mal. :-)

Schöne Grüße
podie

 

super, jetzt passt alles!
Vielen Dank!!
Beste Grüße,
Just

 

Hallo steve1da,

ich brauch noch einmal deine Hilfe. Ich möchte deinen Code für die monatlichen Durchschnitte in einer weiteren Tabelle anwende, die jedoch nur eine Zeitreihe und nicht mehrere enthält, jeweils wieder mit mehreren Beobachtungen pro Monat.
Ich denke die Tablle habe ich richtig formatiert. Im Original ist das Datum mit TT.MM.JJJJ angegeben, in der Tabelle die dann die Monatsdurchschnitte enthalten soll mit MM.JJJJ
Leider funktioniert dein Befehl nicht mehr. Ich hab ihn in folgendes Format geändert:

SUMPRODUCT((TEXT($A$4:$A$2890;"MM.JJJJ")=TEXT($D4;"MM.JJJJ"))*($B$3=E$3)*($B$4:$B$2890))/SUMPRODUCT(($B$4:$B$2890>0)*($B$3=E$3)*(TEXT($A$4:$A$2890;"MM.JJJJ")=TEXT($D4;"MM.JJJJ"))*1)

Anmerkung: Originaltabelle: Zellen A3: "Datum" (täglich), B3 "Kurs", Zellen B4:B2890 enthalten die entsprechenden Daten und A4:A2890 die entsprechenden Datumsangaben.
Zieltabelle: D3: "Datum" (monatlich)", E3 "Kurs"
Die Bezeichnungen von "Kurs" in den Zellen B3 und E3 sind identisch.

Ich bin dir sehr dankbar wenn du mir sagen kannst, warum der Befehl nicht funktioniert.

Gruß,
Just

 

Hola,

eine aktuelle Beispieltabelle würde mir ungemein helfen ;)

Gruß,
steve1da

 

Kein Problem:
Hier ein Ausschnitt der Tabelle (1600 statt fast 3000 Datumsangaben)
Besten Dank,
Just

 

Hola,

die Formel arbeitet doch super - die Eintragung in Spalte A beginnen erst ab Juni 2002. Von daher gibt es für 01.2002-05.2002 keine Ergebnisse, also teilt er durch 0 und das erzeugt den Fehler.

Gruß,
steve1da

 

Oh mann, da hätte ich echt mal selber drauf kommen können...
Vielen Dank für die schnelle Antwort und die Fehlerbehebung! :)
Gruß,
Just