Fortlaufender (?) Durchschnitt mit Wenn-Funktion und Matrixformel

[Rounder312]

Hallo Excel-Genies,
 
mein Name ist Carsten, 28 und ich steh aktuell ein wenig auf dem Schlauch. :16:  Aber ich geh von aus, dass ihr das relativ einfach lösen könnt. J
 
Bevor jemand meckert, ja, ich habe das Board nach einer Lösung durchsucht. Und ich muss auch sagen, dass ich zu jeder einzelnen Formel eine Lösung gefunden habe. Nur hilft mir das nicht weiter, da ich alles in eine Zelle packen muss. Quasi, aus verschiedenen Formeln eins machen.
 
Ich fang mal an zu erklären, versuche dabei aber, mich kurz zu halten. Möchte ja niemanden langweilen.
 
Ich bastel an einem Auswertungssheet für Fussball-Spiele. Darin aufgeführt sind diverse Statistiken (Treffen beide, Ü2,5, Anzahl Heimtore, etc). Das Ganze für 20 Ligen in vielen europäischen Ländern.
 
Nun würde ich gerne eine Vorhersage einbauen (basierend auf vorherigen Ergebnissen). Ich möchte, dass mir der Punkte-Durchschnitt der letzten fünf Spiele (gesamt, nicht heim- oder auswärts) angezeigt wird. Normalen Durchschnitt mit Wenn-Funktion mit Matrixformel habe ich rausbekommen, das ist nicht schwer. Aber wie kann ich das fortlaufend machen? Jede Woche kommen die Werte der vorangegangen Spiele dazu. Also kann es sein, dass ich 2-3x die Woche aktualisiere und die neuen Werte unten angehangen werden. Der Bezug müsste sich jedes Mal ändern.
 
Kann man sowas überhaupt mit Excel anstellen? Was für Infos fehlen in meiner Erklärung? War es überhaupt verständlich?
 
Fragen über Fragen, ich weiß. :44:
 
Aber vielleicht hab ich ja Erfolg und finde jemanden, der das auch schon mal probiert hat und eine recht simple Lösung kennt.
 
Danke euch schon mal fürs Lesen der vielen Zeilen.

Edit: Hab mal 2 Screenshots angehangen. Vielleicht macht es das irgendwie klarer.

[lupo1]

Annahme: Komplette Spieltage ab Zeile 2, mit je 9 Partien pro Tag in ordentlicher Reihenfolge notiert (Nachholspiele richtig eingeordnet)

X:X: Mittel der 5 letzten Spiele der Heimmanschaft

Gemittelte Punkte der heutigen Heimmannschaft:
X47: =SUMMENPRODUKT(
(INDEX(B:B;KÜRZEN(ZEILE(B45)/9)*9-44):INDEX(B:B;KÜRZEN(ZEILE(B45)/9)*9)=B47)
*INDEX(D:D;KÜRZEN(ZEILE(D45)/9)*9-44):INDEX(D:D;KÜRZEN(ZEILE(D45)/9)*9)+
(INDEX(C:C;KÜRZEN(ZEILE(C45)/9)*9-44):INDEX(C:C;KÜRZEN(ZEILE(C45)/9)*9)=B47)
*KÜRZEN(3-INDEX(D:D;KÜRZEN(ZEILE(D45)/9)*9-44):INDEX(D:D;KÜRZEN(ZEILE(D45)/9)*9)*1,1))/5

B:B: Heimmannschaft
C:C: Auswärtsmannschaft
D:D: Heimpunkte (Auswärtspunkte ergeben sich mit der KÜRZEN(3- -Formel dann daraus)

B47: Name der heutigen Heimmannschaft, von der aus gerechnet werden soll
C47: Name der heutigen Auswärtsmannschaft, von der aus gerechnet werden soll

Y:Y: Mittel der 5 letzten Spiele der Auswärtsmannschaft

Entsprechend gemittelte Punkte der heutigen Auswärtsmannschaft:
Y47: =SUMMENPRODUKT(
(INDEX(C:C;KÜRZEN(ZEILE(C45)/9)*9-44):INDEX(C:C;KÜRZEN(ZEILE(C45)/9)*9)=C47)
*INDEX(D:D;KÜRZEN(ZEILE(D45)/9)*9-44):INDEX(D:D;KÜRZEN(ZEILE(D45)/9)*9)+
(INDEX(B:B;KÜRZEN(ZEILE(B45)/9)*9-44):INDEX(B:B;KÜRZEN(ZEILE(B45)/9)*9)=C47)
*KÜRZEN(3-INDEX(D:D;KÜRZEN(ZEILE(D45)/9)*9-44):INDEX(D:D;KÜRZEN(ZEILE(D45)/9)*9)*1,1))/5

Anmerkung: Das Mittel der Punkte zu ermitteln ist Bullshit! Beim alten 2-Punkte-System ging es noch. Denn 2 G 1 U 2 V ergab damals 1 als Mittel. Heute: 1,4! Bei 3 G 1 U 3 V war es immer noch 1 - richtig! Bei der heutigen 3-Punkte-Regel hingegen 1,42!

[Rounder312]

Anbei noch ein Screenshot anhand von Manchester United. 

Für die rot hinterlegten Zellen hätte ich gern den Durschnitt. Gelb hinterlegt ist das Spiel, was rausfallen würde, da ich nur die letzten 5 haben möchte.  

Der Aufbau der Rohdaten ist so, wie er im Screenshot angezeigt ist.

Ich würde auch Hilfsspalten einfügen, wenn es die Sache erleichtert.

[BoskoBiati]

Hallo,

da fällt mir momentan nur das ein:

http://www.clever-excel-forum.de/thread-326.html
http://www.clever-excel-forum.de/thread-47.html

[Rounder312]

Hi BoskoBiati,

hab da mal was vorbereitet.  :21:

Ist nicht die original Datei, die wäre zu groß. Anbei ein "Abklatsch" mit den Daten (nur Premiere League England und der nächste Spieltag), die ich nutze.  

Wie gesagt, geht mir nur um den fortlaufenden Fortschritt. Alles, was damit nichts zu tun hat, habe ich weggelassen.

[lupo1]

Wir bauen uns eine Tabelle:

Code:

Sub GleitendeDurchschnitteDerPunkteDerLetzten5Spieltage()
[A1:D1] = Array("Spieltag", "Heimteam", "Auswärtsteam", "HeimPkte"): [X1:Y1] = Array("Heim-Schnitt", "Ausw-Schnitt")
[A2:A73] = "=TRUNC(ROW(R[7]C)/9)": [D2:D73] = "=ROUND(RANDBETWEEN(0,2)*1.4,)": [F2:G73] = "=RAND()"
[B2:C73] = "=CHAR(RANK(RC[4],INDEX(C6,TRUNC(ROW(R[7]C)/9)*9-7):INDEX(C7,TRUNC(ROW(R[7]C)/9)*9+1))+64)"
ActiveWorkbook.Names.Add Name:="X", RefersToR1C1:="=TRUNC(ROW(R[-2]C)/9)*9-43": [X47:Y73] = "=SUMPRODUCT(" & _
"(INDEX(C2,X):INDEX(C2,X+44)=RC[-22])*INDEX(C4,X):INDEX(C4,X+44)+" & _
"(INDEX(C3,X):INDEX(C3,X+44)=RC[-22])*TRUNC(3-INDEX(C4,X):INDEX(C4,X+44)*1.1))/5": [B2].Activate
[E:W].EntireColumn.Hidden = True: ActiveWindow.FreezePanes = True: [A1:G73] = [A1:G73].Value: [A52].Activate: End Sub

[Rounder312]

Hey Lupo,

erstmal danke für den Code. Nur habe ich das Gefühl, dass der irgendwie nicht das bewirkt, was er bewirken soll. Kann aber auch sein, dass meine Makro-Fähigkeiten sehr begrenzt sind. (Davon gehe ich eher aus)

Hab mal einen Screenshot angehangen von dem, was das Makro bei mir macht.

[lupo1]

Ich habe mein (reines Tabellenerstellungs-) Makro noch korrigiert. Es ist dabei kürzer geworden :)

Ich habe mich nicht in jeder Sicht an Deinen Vorgaben orientiert. Beispielsweise ergeben sich die Auswärtspunkte aus den Heimpunkten des Gegners. Außerdem bilde ich am sechsten Spieltag den Schnitt aus den fünfen DAVOR, nicht am fünften Tag den aus den 4 Tagen davor und dem desselben Tages. Für letzteren Fall müsstest Du die (nur einmal im Code vorkommende!) Zahl 43 in 34 ändern!

Die Spielpaarungen ergeben sich zufällig (können sich also wiederholen). Es ist aber garantiert, dass jede der 18 Mannschaften genau einmal in den 9 Paarungen eines Spieltags spielt.

[Rounder312]

Moin Lupo,

danke, dass du dich der Sache noch einmal angenommen hast. 

Anscheinend bin ich aber wirklich zu doof dafür, dass irgendwie rein zu basteln. Und ich verstehe nur Bahnhof, um ehrlich zu sein. 

Da ich das nicht kapiere und ich niemandem seine Zeit stehlen möchte, werde ich mir was anderes überlegen müssen. Dachte wirklich, es geht einfacher. 

Danke an alle, die sich damit beschäftigt haben.

Schönes Weihnachtsfest!

Carsten...

[schauan]

Hallöchen,

nur mal fürs Prinzip. Nimm mal eine "intelligente" Tabelle. Dort gibst Du die Überschriften ein und eine Mittelwertsformel über die erste zeile und die 4 Zeilen davor. Damit das geht, fängt die Tabelle in meinem Beispiel in Zeile 5 an.
Wenn Du nun in Spalte A eine neue Zahl eingibst, wandert die Formel automatisch mit und umfasst wiederum nur die letzten 5 Zeilen.