Bundesliga-Ergebnistippspiel

Vorwort
Bei dieser Excel-Tabelle handelt es sich um eine Auswertung für ein Fußball-Bundesliga-Tippspiel. Bis zu 20 Mitspieler können jede Saison um den Sieg kämpfen, die Anzahl der maximal zu wertenden Spieltage kann festgelegt werden und es kann entweder im Ergebnis-Tipp-Modus oder im Tendenz-Tipp-Modus gespielt werden. Die Funktion «maximal zu wertende Spiele» ermöglicht es, z.B. nur 20 Spieltage werten zu lassen. Es werden dann die 20 besten Ergebnisse pro Spieler unter «Max.-Punkte» aufgeführt. 

Im Ergebnis-Tipp-Modus wird automatisch die aktuelle Bundesligatabelle errechnet. Außerdem können der Tippeinsatz (in Euro) und die prozentuale Gewinnausschüttung pro Platzierung eingegeben werden. Die Spielpaarungen werden anhand des Rasters auf dem Tabellenblatt «Spielplan» errechnet.

In Version 2.x sind einige Neuerungen hinzugekommen. Nachfolgend die Versionshistorie:

Neuerungen in Version 2.0

• Import-Funktion für den Import von Tipps aus anderen Tabellenblättern
• Neues Tabellenblatt «Einstellungen»: Punkte und maximale Spieltage werden der Übersichtlichkeit halber jetzt hier verwaltet
• Vergabe von unterschiedlichen Punkten für Heimsieg, Remis und Heimniederlage bei Unterscheidung zwischen korrektem Ergebnis, Differenz und Tendenz
• Tipp Torabweichung: Zusätzlich oder ausschließlich kann die Abweichung der erzielten Tore miteinfließen
• Ergebnis-Formatierung: Bei der Eingabe von Ergebnissen, die keinen zweistelligen Torwert haben, genügt es nun, das Ergebnis ohne jedes Trennzeichen zu schreiben, was eine erhebliche Zeitersparnis bringen dürfte. Die Ergebnisse werden nachformatiert.
• Kontrollmechanismen für die Erfassung des Spielpaarungs-Rasters für neue Spielzeiten.

Neuerungen in Version 2.01

• Beim Tendenz-Tipp (1/0/2) kann jetzt das Ergebnis in Toren eingetragen werden (z.B. 2:1), während der Tipp 1/0/2 erfasst wird. Diese Neuerung ermöglicht die Berechnung der aktuellen Bundesligatabelle auch beim Tendenz-Tipp. In der Funktion TIPPCOUNT (Zähler der Anzahl der korrekten Tipps pro Blatt) wurde diese Änderung ebenfalls berücksichtigt.

Neuerungen in Version 2.02

• In der Überschrift "Spielpaarungen, x. Spieltag" befindet sich nun ein Link zur Webseite des Kicker-Sportmagazins. Der Link verweist auf die Ergebnis-Seite des jeweiligen Spieltages. Um den Link nutzen zu können, muss Ihre Firewall den Zugriff von Excel auf das Internet zulassen.

Neuerungen in Version 2.03

• Neue Funktion «Meistertipp» und Tipp der drei Absteiger im Tabellenblatt «Spieler» (Zellen AX3 bis BA22). Die Punkte fließen in die Gesamtwertung und in die Max.-Punkte ein. Optional können die Punkte bereits während der Saison anteilsmäßig in die Gesamtpunkte/Max.-Punkte eingerechnet werden. 

Neuerungen in Version 2.04

• Neue Funktion «Sonderpunkte». Auf dem Tabellenblatt «Spieler» können in den Zellen BD3 bis BD22 alle Punkte manuell eingetragen werden, die nicht über die vorhandenen Funktionen des Tippspiels ermittelt werden können. So können Tippgemeinschaften auch eigene Tipps wie Herbstmeister, Torschützenkönig oder andere Tipps einfließen lassen. In den Zellen BL3 bis BP22 können die dazugehörenden Informationen textlich (ohne Funktion) erfasst werden.

Neuerungen in Version 2.05

• Neues Tabellenblatt «Statistik» mit den Gesamtpunkten, aufgelistet je Spieltag und den Gesamtplatzierungen, ebenfalls aufgelistet je Spieltag. 

Neuerungen in Version 2.06

• Unter «Statistik» sind noch weitere Auswertungen hinzu gekommen: Top/Flop-Platzierungen, Top/Flop-Punkte, Durchschnittspunkte (auch pro maximal zu wertende Spieltage), Median, Streuung von Punkten

Neuerungen in Version 2.07-2.09

• Im Sheet «Spielplan» können nun auch Punktabzüge erfasst werden. Diese fließen in die Tabelle ein
• Ebenfalls im Sheet «Spielplan» wurden weitere Kontrollmechanismen zur Spielplanerfassung eingebaut.

Berechnungen

Maximal zu wertende Spieltage
Die Tabelle berechnet den Tabellenstand der Mitspieler anhand der maximal zu wertenden Spieltage. Ist diese Zahl zum Beispiel mit 17 (Hälfte aller Spieltage) festgelegt und hat ein Spieler bereits 18 Spieltage getippt, so kommen nur die 17 besten Ergebnisse in seine Wertung. Auf der einen Seite hat man einen Anreiz häufiger zu tippen, weil mit jedem weiteren Tipp ein schlechter Spieltag ersetzt wird. Auf der anderen Seite haben Mitspieler, die sehr gut tippen, aber am Saisonende vielleicht nur 20 Spieltage getippt haben, ebenfalls eine Siegchance. Voreingestellt sind die maximalen Spieltage mit dem Wert 20. Dieser kann aber geändert werden (min. 5, nach oben zwar keine Grenze, aber es werden sowieso nur 34 Spieltage gewertet).

Die Punkteverteilung
Für jedes Spiel erhält jeder Mitspieler folgende Punktzahlen:
Beim Tendenz-Tipp-Spiel (es wird nur Heimsieg=1, Unentschieden=0 oder Auswärtssieg=2 getippt) gibt es für jede richtige Tendenz 1 Punkt (bzw. so viele Punkte wie auf Tabellenblatt «Spieler» in Zelle C6 eingetragen sind, ein anderer Wert als 1 macht aber wenig Sinn), sonst 0 Punkte. Auch unter Ergebnis muss 1, 0 oder 2 eingegeben werden.
Beim Ergebnis-Tipp-Spiel werden echte Ergebnisse getippt (und es wird automatisch die aktuelle Bundesligatabelle berechnet). Für ein Ergebnis, das genau richtig getippt wurde (Tipp 2:1, Ergebnis 2:1), erhält man so viele Punkte wie auf Tabellenblatt «Einstellungen» in Zelle C4 bis E4 eingetragen sind. Für die richtige Differenz (Tipp 2:1, Ergebnis 1:0 oder 3:2 usw.) erhält man Punkte gemäß den Zellen C5 bis E5. Für die richtige Tendenz, aber falsches Ergebnis und auch falsche Differenz (Tipp 2:1, Ergebnis 2:0 oder 4:1 usw.) erhält man Punkte gemäß den Zellen C6 und E6 (Unentschieden D5). Bei Unentschieden werden falsche Ergebnisse (Tipp 1:1, Ergebnis 0:0) konsequenterweise mit den Punkten für die richtige Differenz bewertet.

Beschreibung der einzelnen Tabellenblätter

Tabellenblatt «Versand»
Dieses Tabellenblatt entfällt ab Version 2.0, da die Ergebnisse nun aus anderen Sheets importiert werden können.

Tabellenblatt «Spielplan»
In der Tabelle «Spielplan» kann auch für künftige Saisons ein Spielplan erfasst werden. Entscheidend ist dabei nur die obere Tabelle. Der zweite Teil der oberen Tabelle wird automatisch berechnet, da es sich um die Rückspiele handelt. Erfasst werden müssen die Vereine in Spalte B sowie das Raster in C2 bis S19 (Hinrunde). Die Zahlen unterhalb des Rasters und die Kontrollbox in B27 bis F46 ist gedacht, um Fehler bei der Erfassung aufzudecken. In der Kontrollbox müssen am Ende der Vorrunde alle Vereine 17 Spiele haben, sonst ist irgendwo ein Fehler passiert. 

Pro Spieltag müssen 9 Begegnungen erfasst werden. Es werden immer nur die Spiele der HEIMMANNSCHAFTEN erfasst. Dazu werden in der Spalte des jeweiligen Spieltags die Zahlen erfasst, die bei dem Gegner unter der Spalte «Nr» zu finden sind. Anhand des Beispiels aus der jeweiligen Vorsaison, lässt sich das Prinzip schnell erkennen.

Siehe auch Abschnitt «Zusatzinfos». 

Tabellenblatt «Einstellungen»
Hier trägst du die Punktzahlen, die es für richtige Ergebnisse, richtige Differenz oder Tendenz gibt, ein (C4 bis E6). Die Punktzahlen können je nach Heimsieg, Remis und Auswärtssieg (Heimniederlage) unterschieden werden. 

Ein Sonderfall ist der Wert «Basis-Punkte Torabweichung» (C7): Dieser Wert wird auf die Punkte addiert, wenn sowohl zu Hause als auch auswärts geschossene Tore in Ergebnis und Tipp übereinstimmen. Für jedes Tor Abweichung (egal ob bei den Heim- oder bei den Auswärtstoren) wird wieder ein Punkt abgezogen. Es können maximal so viele Punkte abgezogen werden, wie in diesem Feld eingetragen werden. Dieser Wert kann also die Endpunkte nur positiv beeinflussen, nicht negativ. Willst du die Torabweichung NICHT benutzen, so setze diesen Wert auf 0. Willst du NUR die Torabweichung benutzen, so setze diesen Wert auf eine gewünschte Zahl und alle anderen Werte auf 0.

In Spalte B23 trägst du die maximal zu wertenden Spieltage ein.

In den Spalten C28 und C29 folgen die Punkte für den Meistertipp und den Absteigertipp. Mit den dahinter liegenden Kontrollkästchen kannst du die eingetragenen Punkte anteilsmäßig bereits während der Saison mit einrechnen lassen. Anteilsmäßig bedeutet, dass die Anzahl der Punkte durch 34 gerechnet und mit der Spieltagsnummer multipliziert wird. Bei einem Meistertipp mit 20 Punkten ergibt sich am 5. Spieltag folgende Berechnung wenn die entsprechende Mannschaft auf Platz 1 steht: 20 x 5 : 34 = 2,94 Punkte. Hiervon wird nur die Vorkommazahl genommen. Also ergeben sich 2 Punkte.

Tabellenblatt «Spieler»
Hier trägst du die Spielerkurznamen (Zellen A3 bis A22) und die ausgeschriebenen Spielernamen (AS3 bis AS22) ein. Außerdem trägst du den Wetteinsatz (AT39) und die prozentualen Geldbeträge (AU26 bis AU35) ein, die die Spieler auf Platz 1 bis x nach der Saison erhalten. Die Geldbeträge errechnen sich automatisch. Der Gesamtbetrag wird errechnet aus der Spalte «bezahlt» (AT3 bis AT22), wenn der Wert «ja» ist. Die eingegebenen Spielernamen, die momentan in der Tabelle stehen sind frei erfunden. Jede Übereinstimmung oder Ähnlichkeit mit lebenden Personen ist rein zufällig und nicht beabsichtigt ...

Außerdem bietet dieses Tabellenblatt einige Auswertungen. Neben der Punktübersicht pro Spieler und Spieltag gibt es z.B. die Auswertung wie oft jeder Spieler 1 Punkt, 2 Punkte oder 3 Punkte für einen Tipp erhalten hat. Letztere Info ist seit der Umstellung auf variable Punktvergabe allerdings nur noch interessant, wenn wie in der Ursprungsversion 1, 2 und 3 Punkte für die verschiedenen korrekten Tipps vergeben werden.

In den Spalten AX3 bis AX22 kann der Meister getippt werden. In den Spalten AY3 bis BA22 können die Absteiger getippt werden, wobei die Reihenfolge der Absteiger keine Rolle spielt. Es wird nur gewertet, ob die drei getippten Mannschaften sich unter den Absteigern befinden. Der im Tabellenblatt «Einstellungen» eingetragene Punktwert gilt pro Absteiger. Maximal ist also der dreifache Wert aus Zelle C29 erreichbar.

In den Zellen BD3 bis BD22 hast du letztlich die Möglichkeit, Punkte aus anderen Tipps manuell einfließen zu lassen. Diese Tipps kannst du in den Zellen BL3 bis BP22 textlich erfassen. Die Eingaben und Berechnungen laufen hier jedoch manuell, außer dass die manuell erfasste Spalte BD automatisch in das Gesamtergebnis einfließt.

Tabellenblatt «Statistik»
Übersicht über die Gesamtplatzierungen sowie der Gesamtpunkte für jeden Spieltag der gesamten Saison. Dazu Top/Flop-Platzierungen, Top/Flop-Punkte, Durchschnittspunkte (auch pro maximal zu wertende Spieltage), Median, Streuung von Punkten.

Tabellenblatt «Rangliste»
Enthält die Rangliste der Tippspieler. Die Rangliste ist abhängig von den erzielten Punkten und der Anzahl der maximal zu wertenden Spieltage (erste Punktespalte). Die untere Rangliste ist eine Nur-Text-Version. Diese kann leicht in E-Mails mit Nur-Text-Format kopiert werden.

Tabellenblatt «Tabelle»
Errechnet die aktuelle Bundesligatabelle anhand der eingegebenen Ergebnisse. Beim Tendenz-Tipp (1, 0, 2) kann die Tabelle logischerweise nicht berechnet werden.

Tabellenblatt «Import»
Auf diesem Tabellenblatt findest du die neue Import-Funktion. Beim Import von externen Daten wird davon ausgegangen, dass die Quelle genauso aussieht, wie die Datei, in die importiert werden soll. Steht also der Spieler «Uwe» in der Datei des Spielleiters in Spalte K, so muss er auch in der Quelle in Spalte K eingetragen sein. In der Regel erreicht man dies, indem man die Spielleiter-Datei einfach kopiert, umbenennt (in den Kurznamen des Empfängers mit nachfolgendem «.xls») und versendet. Von dieser Zeit an braucht der Empfänger seine Tipps nur noch in seiner Datei zu pflegen und diese von Spieltag zu Spieltag (oder nach abgesprochenen Terminen) an den Spielleiter senden, der wiederum einzelne, mehrere oder alle Spieltage importieren kann. Theoretisch kann jeder Spieler die Daten aller Mitspieler importieren. Der Spieler «Uwe» kann auch nur seine eigenen Ergebnisse ändern, oder besser gesagt: Aus der Datei «Uwe.xls» wird auch nur der Spieler «Uwe» berücksichtigt, egal ob Uwe andere Tipps seiner Mitspieler abgeändert hat. Wenn alle Spieler ihre Tipps an alle Mitspieler verschicken würden, könnte jeder Spieler alle anderen Tipps importieren (das Importprogramm berücksichtigt die Auslassung der eigenen Datei). Ob das Sinn macht, oder ob nicht der Spielleiter nach dem Spieltag die komplette Datei versendet, ist eine andere Frage. Doch egal, wie sich die Tipprunde organisiert - alle Varianten sollten möglich sein.

Der Spielleiter (bzw. derjenige, der die anderen Tipps importiert) kann pro Spieler entscheiden, welche Spieltage (von-bis) importiert werden sollen. Es können ein oder mehrere Spieltage gleichzeitig importiert werden. Außerdem kann pro Spieler (also jeder Spieler einzeln) oder es können alle Spieler importiert werden. Die Quell-Dateien müssen immer so heißen wie der Kurzname des jeweiligen Spielers (eingetragen im Sheet «Spieler» in den Zellen A3 bis A22) gefolgt von «.xls». 

Es ist der Überschreibemodus eingestellt, d.h. es wird nicht geprüft, ob sich in der Spielleiter-Datei in den einzutragenden Zellen bereits Daten befinden. Bereits bestehende Daten werden also überschrieben, wenn man sie nochmals importiert. Empfehlung: Normalerweise sollten NIE ALLE, sondern nur zukünftige Spieltage importiert werden.

Bitte folgende Mindestanforderungen beachten:

• Die Datei, die dem Tipper zugeschickt wird bzw. die der Tipper zurückschickt, muss genauso heißen wie sein Kurzname gefolgt von «.xls»
• Diese Datei muss genauso aufgebaut sein, wie die Hauptdatei, d.h. es dürfen keine Spalten oder Zeilen eliminiert werden.
• Es muss sich mindestens das Tabellenblatt des jeweils zu importierenden/aktuellen Spieltags in der Datei des Tippers befinden. Man kann dazu die gesamte Datei verschicken, oder wer das nicht will kann auch einzelne Tabellenblätter herauskopieren: Am besten eine neue Arbeitsmappe öffnen, in der Hauptdatei das gesamte Tabellenblatt des aktuellen Spieltags markieren, kopieren und in der neuen Arbeitsmappe einfügen, anschließend in der neuen Arbeitsmappe gleich noch «Inhalte einfügen/Werte» ausführen, da sonst die Formeln ins leere laufen. Diese fertige Datei dann entweder x mal kopieren oder einfach einmal verschicken und die Tipper müssen dann die Datei umbenennen.
  ACHTUNG: Wenn nicht die gesamte Datei versendet wird, muss dafür Sorge getragen werden, dass das Tabellenblatt den korrekten Namen hat. Es muss so heißen wie das entsprechende Blatt in der Ursprungsdatei, z.B. «1-2»oder «1516»...
•  Es müssen sich zu allen Spieltagen, die importiert werden sollen, Tabellenblätter in der Quelldatei befinden, ansonsten wird mit Fehlermeldung abgebrochen.

Tabellenblätter «1-2» bis «3334»
Hier sind die Tipps und die Ergebnisse einzugeben und es werden die Punkte pro Spieltag errechnet.

Historie...

Zusatzinfos (Achtung: gilt nur für Saisons bis 2005/06)

Berechnung der Spieltage - Die Matrix, Spieltag 16 und andere Hintergründe
Gilt nicht für Saisons ab 2006/07...
Die Berechnung der Spielpaarungen erfolgte lange Zeit auf der Grundlage einer Formel. Irgendwann fiel mir mal auf, dass fast alle Vereine in der gleichen Reihenfolge gegen die anderen Teams spielen. Wenn also heute Bochum gegen Dortmund spielt und dann gegen Cottbus und Stuttgart, dann spielt ein anderes Team, das am Spieltag x gegen Dortmund spielt, anschließend auch gegen Cottbus und Stuttgart. Dies gilt für alle Teams bis auf eines - ich nenne dieses Team den Springer. Dieser spielt immer gegen diejenige Mannschaft, die an diesem Spieltag (nach obiger Regel) gegen sich selbst spielen würde. Im Abschnitt «Der Springer» (unten) wird dies noch näher erläutert. Jedenfalls ergibt sich daraus die Matrix auf dem Tabellenblatt «SPIELPLAN». Es werden dabei auch Heim- und Auswärtsspiele berücksichtigt. Heimrecht hat ein Team, wenn in seiner Zeile im jeweiligen Spieltag die Nummer des Gegners eingetragen ist. 
Für Spieltag 16 ergibt sich dort theoretisch ein Fehler beim Team Nr. 9. Hier müsste eigentlich eine 18 stehen, weil das Team 9 in der Hinrunde gemäß den übrigen Gesetzmäßigkeiten der Matrix Heimrecht gegen das Team 18 haben müsste. Vermutlich wollte der DFB hiermit verhindern, dass Team 9 zwischen Spieltag 16 und 18 dreimal hintereinander Heimrecht hat und Team 18 dreimal auswärts antreten muss. Jedenfalls hatte in den vergangenen Jahren am 16. Spieltag immer Team 18 Heimrecht.
Anmerkung: Es kann sein, dass die DFL manche Spiele «dreht». Das bedeutet, dass das Heimrecht bei einzelnen Spielen wechseln kann. Der Grund kann sein, dass in betroffenen Stadien andere Veranstaltungen stattfinden, oder ähnliches. Sollte ein solcher Fall auftreten, so genügt es, nur die Hinrundenspiele manuell zu drehen. Dabei unbedingt auf die richtige Schreibweise der Teams achten! Es sollte immer mit einer Sicherheitskopie der Excel-Datei gearbeitet werden, damit diese im nächsten Jahr wieder verwendet werden kann.

Der Springer
Eines vorweg: Die DFL hat das Spieltagsraster ab Saison 2006/07 komplett umgestellt. Ob dies die nächsten Jahre so bleibt, wird die Zukunft zeigen. Nachfolgend die Ermittlung für die vergangenen Jahre:
Um den Springer zu ermitteln, muss man - wie oben bereits angeschnitten - herausfinden, wann ein beliebiges Team normalerweise gegen sich selbst spielen würde. Als Hintergrund ist noch zu erwähnen, dass 17 der 18 Teams alle Gegner in der gleichen Reihenfolge durchlaufen - bis auf den Springer. Theoretisch kann man als Ansatz also versuchen, eben dieses Team herauszufinden, indem man schaut, welches Team die Gegner in einer abweichenden Reihenfolge spielt. Dies hat sich jedoch nicht als sehr ergiebig erwiesen, da dieses Vorgehen zum einen sehr aufwändig wäre, zum anderen aber auch sehr unübersichtlich. Letztlich habe ich folgende sehr einfache Strategie entwickelt:

Kommende Spielzeiten
Auch dieser Abschnitt muss durch die Umstellung ab der Saison 2006/07 eingeschränkt werden. Möglicherweise muss das Raster nun jedes Jahr geändert werden.
Diese Excel-Tabelle kann im Prinzip jede zukünftige Bundesligasaison abbilden, solange die gleichen Spieltagsraster verwendet werden. Alles was du dazu tun musst, ist die Originaldatei mit den Formeln zu sichern und jedes Jahr zu kopieren. Dann müssen die Vereine eingetragen werden, die in der aktuellen Saison in der Bundesliga spielen. Dazu gehst du wie folgt vor:
Finde heraus, welche Mannschaft der Springer (siehe oben unter «Berechnung der Spieltage») ist. Der Springer ist deshalb so wichtig, weil man ihn in einem fertigen Spielplan als einziges Team 100%ig identifizieren kann. Mit seiner Hilfe bekommt man das Team heraus, das an Nr. 1 steht.

• Der Springer wird in der Tabelle NEU als Team Nr. 18 notiert.
• Anschließend setzt man die Mannschaft, die am 17. Spieltag gegen den Springer spielt, in der Tabelle NEU auf Nr. 1 (weil diese Mannschaft erst am 17. Spieltag gegen sich selbst spielen würde).
• Das Team, das am ersten Spieltag gegen das Team auf Nr. 1 spielt, wird auf Nr. 2 gesetzt.
• Das Team, das am zweiten Spieltag gegen das Team auf Nr. 1 spielt, wird auf Nr. 3 gesetzt.
• Es folgen nacheinander alle anderen Teams in der Reihenfolge, in der sie gegen Team Nr. 1 spielen.
• Im obigen Beispiel (s. «Der Springer») würde der HSV auf 18 gesetzt, Bremen als Gegner des HSV vom 17. Spieltag auf 1, Arminia Bielefeld als Gegner der BREMER vom ersten Spieltag auf 2, Mainz als Gegner der BREMER vom 2. Spieltag auf 3 usw.

Dieses System funktioniert. Ich nutze diese Tabelle selbst seit 4 Spielzeiten. 2006/07 wurde auf ein anderes System umgestellt.
Und jetzt viel Spaß mit dem Tippspiel.

Armin Höpfl