Du kannst Elo-Zahlen relativ leicht mit Hilfe eines Tabellenkalkulationsprogramms wie Libre Calc oder Excel1 selber berechnen. Um die folgenden Arbeitsschritte zu verstehen, solltest du die beiden Vorgängerartikel gelesen haben: Elo-Zahlen berechnen (1) und den Blogpost mit den Beispielrechnungen (2).
Nehmen wir als einfaches Beispiel die Gruppenphase der Gruppe B in der Champions League 2015/16. Das Grundprinzip lässt sich auf jede Liga mit beliebig mehr Teams übertragen.
1. Die Grundstruktur
| 2016-04-16-elo-libre-calc-01.ods |
In der Grundstruktur findest du den Spielplan, die Spielergebnisse, und die nötigen Spalten für die verschiedenen Variablen der Elo-Formeln:
Als Ausgangswert für die Elo-Zahlen aller Teams habe ich 1500 gewählt, als K-Wert (in Spalte L) 30.
Als Ausgangswert für die Elo-Zahlen aller Teams habe ich 1500 gewählt, als K-Wert (in Spalte L) 30.
2. Das Rating „abholen“
| 2016-04-16-elo-libre-calc-02-das-rating-abholen.ods |
Im ersten Schritt willst du jetzt erreichen, dass in Spalten F und G das passende Rating für die Heim- und für die Gastmannschaft abgeholt wird. Dazu verwendest du folgende Formeln:
F3: =WENN(B3=O$1,O2,WENN(B3=P$1,P2,WENN(B3=Q$1,Q2,WENN(B3=R$1,R2,"Fehler"))))
G3: =WENN(B3=O$1,O2,WENN(B3=P$1,P2,WENN(B3=Q$1,Q2,WENN(B3=R$1,R2,"Fehler"))))
F3: =WENN(B3=O$1,O2,WENN(B3=P$1,P2,WENN(B3=Q$1,Q2,WENN(B3=R$1,R2,"Fehler"))))
G3: =WENN(B3=O$1,O2,WENN(B3=P$1,P2,WENN(B3=Q$1,Q2,WENN(B3=R$1,R2,"Fehler"))))
Für F3 sieht das im Screenshot dann folgendermaßen aus:
Die Dollarzeichen halten den Wert direkt dahinter konstant, wenn du nachher die Formeln für die kompletten Spalten automatisch auffüllst. Die beiden Formeln überprüfen, ob der Wert aus der jeweiligen Zelle in Spalte B und C mit den Zellen O1 bis R1 übereinstimmt. Falls ja, übernimmt die Formel das Elo-Rating des Teams vor dem Spiel – also die Werte aus der Zeile über dem Spiel.
Falls die Teamnamen in den Spalten B und C nicht übereinstimmen, bekommst du eine Fehlermeldung – um das zu testen, musst du nur den Clubnamen in B3 oder C3 entsprechend ändern, in „Dortmund“ beispielsweise.
3. Den E-Wert berechnen
| 2016-04-16-elo-libre-calc-03-e-wert-berechnen.ods |
Nun berechnest du den E-Wert mit folgenden Formeln für die Zellen J3 und I3:
H3: =1/(1+10^((G3-F3)/400))
I3: =1-H3
Screenshot:
H3: =1/(1+10^((G3-F3)/400))
I3: =1-H3
Screenshot:
Du könntest für I3 natürlich auch die korrekte Formel verwenden [=1/(1+10^((F3-G3)/400)], ich möchte dir aber davon abraten, weil das zu leichten Rundungsfehlern führt, was auf Dauer die Ratings ein wenig verzerren kann. Kein Drama, aber du erhältst mathematisch sauberere Ergebnisse, wenn du wie hier im Beispiel geschehen die direkte Gegenwahrscheinlichkeit zur H-Spalte berechnest.
4. Den S-Wert berechnen
| 2016-04-16-elo-libre-calc-04-s-wert-berechnen.ods |
Der S-Wert ergibt sich direkt aus dem Spielergebnis, das du in den Spalten D und E findest. Das führt zu folgenden Formeln:
J3: =WENN(D3>E3,1,WENN(D3=E3,0.5,WENN(D3<E3,0,"Fehler")))
K3: =WENN(D3>E3,0,WENN(D3=E3,0.5,WENN(D3<E3,1,"Fehler")))
Screenshot:
J3: =WENN(D3>E3,1,WENN(D3=E3,0.5,WENN(D3<E3,0,"Fehler")))
K3: =WENN(D3>E3,0,WENN(D3=E3,0.5,WENN(D3<E3,1,"Fehler")))
Screenshot:
Die Formel überprüft zunächst, ob eine Mannschaft mehr, gleich viel, oder weniger Tore als die andere erzielt hat, und gibt dem Team entsprechend einen S-Wert von 1 (Sieg), 0.5 (Unentschieden) oder 0 (Niederlage). Ist keine Bedingung erfüllt (etwa weil du statt bei den Heimtoren aus Versehen keine Zahl, sondern einen Buchstaben eingegeben haben solltest), bekommst du eine Fehlermeldung.
5. Das neue Rating berechnen
| 2016-04-16-elo-libre-calc-05-das-neue-rating-berechnen.ods |
Jetzt berechnest du das Rating, dass beide Teams nach dem Spiel erhalten. Lege dazu zunächst den K-Wert in Zelle L3 fest, und verwende dann folgende Formeln für die Ratings:
L3: =30
M3: =F3+L3*(J3-H3)
N3: =G3+L3*(K3-I3)
Screenshot:
L3: =30
M3: =F3+L3*(J3-H3)
N3: =G3+L3*(K3-I3)
Screenshot:
Den K-Wert in Zelle L3 kannst du auch im Nachhinein noch beliebig verändern, und so beobachten, wie ein veränderter K-Wert die Elo-Zahlen beeinflusst. In den Zellen M3 und N3 findest du im Prinzip die klassische Elo-Formel – nur dass jetzt alle Werte in den entsprechenden Zellen abgelegt sind.
6. Das neue Rating in die passende Teamspalte übertragen
| 2016-04-16-elo-libre-calc-06-das-neue-rating-in-die-passende-teamspalte-uebertragen.ods |
Nun möchtest du, dass das aktualisierte Rating in der passenden Teamspalte (Spalten O bis R) übernommen wird. Dazu verwendest du folgende Formeln:
O3: =WENN($B3=O$1,$M3,WENN($C3=O$1,$N3,O2))
P3: =WENN($B3=P$1,$M3,WENN($C3=P$1,$N3,P2))
Q3: =WENN($B3=Q$1,$M3,WENN($C3=Q$1,$N3,Q2))
R3: =WENN($B3=R$1,$M3,WENN($C3=R$1,$N3,R2))
Screenshot:
O3: =WENN($B3=O$1,$M3,WENN($C3=O$1,$N3,O2))
P3: =WENN($B3=P$1,$M3,WENN($C3=P$1,$N3,P2))
Q3: =WENN($B3=Q$1,$M3,WENN($C3=Q$1,$N3,Q2))
R3: =WENN($B3=R$1,$M3,WENN($C3=R$1,$N3,R2))
Screenshot:
Die Formeln überprüfen, ob Heim- oder Auswärtsmannschaft mit dem Teamnamen im Spaltenkopf übereinstimmen. Falls ja, übernimmt die Formel das passende aktualisierte Rating aus Spalte M (falls es sich um die Heimmannschaft handelt) oder N (für das Auswärtsteam). Falls nein, wird einfach das Rating aus der Zeile darüber weitergeführt.
7. Die verbleibenden leeren Zeilen auffüllen
| 2016-04-16-elo-libre-calc-07-fertiger-spreadsheet.ods |
Der Grundstein ist gelegt, jetzt musst du nur noch die verbleibenden leeren Zeilen auffüllen. Das erreichst du mit folgenden Arbeitsschritten:
a) Klicke Zelle F3 an
b) Halte die Umschalttaste (Shift) gedrückt und klicke Zelle R3 an
c) Ziehe das kleine schwarze Quadrat rechts unten bis R14 hinunter
Du erreichst das selbe Ergebnis, wenn du doppelt auf das kleine schwarze Quadrat klickst.
Damit verfügst du nun über eine Grundarchitektur, auf der du mit mehr Teams und mehr Spielen beliebig aufbauen kannst, um so deine Elo-Ratings weitgehend automatisiert zu berechnen.
Fußnoten:
1 Selber bevorzuge ich Libre Calc, das auch den Vorteil hat, kostenlos zu sein. Die selben Formeln funktionieren aber auch in Excel und ähnlichen Tabellenkalkulationsprogrammen.
RSS-Feed