Fußball-Ligaverwaltung, 1. Bundesliga

Was macht Fußball für den Exelianer interessant? Mit dieser Frage habe ich mich beschäftigt, obwohl ich, zugegebenermaßen, nicht viel von Fußball verstehe.

Im Fußballspiel geht es darum, möglichst viele Bälle ins gegnerische Tor zu bringen. Es geht aber auch darum, unbedingt zu verhindern, dass der Gegner Tore schießt.

Wie gut das gelingt, zeigt sich für das jeweilige Spiel und insgesamt für die Saison an den Ergebnistabellen.

Und genau hier wird es für Excel interessant. Spielergebnisse sind Zahlen und damit kann Excel gut umgehen. Von Zahlen und Excel verstehe ich etwas mehr als von Fußball. Mein Plan war also, die Ergebnisse der 34 Spieltage einzeln und kumuliert in einer dynamischen Tabelle darzustellen.

Die 1. Fußball-Bundesliga hat ihren ersten Spieltag in der neuen Saison am 26.08.2016, höchste Zeit also für mich, meine Ligaverwaltung mit Excel vorzustellen.

Einleitung

Es gibt bereits sehr viele Angebote zur Ligaverwaltung, das ist unbestritten. Warum habe ich nun auch noch etwas entwickelt?

Ganz einfach, ich wollte einfach mal sehen, wie solche Modelle funktionieren. In der Regel ist es doch so, dass es keine methodischen Erläuterungen zu den Modellen gibt.

Deshalb bin ich Schritt für Schritt vorgegangen und habe nach jedem Teilergebnis gefragt, was nun geschehen müsse.

So ist ein Excel-Modell entstanden, das 37 Arbeitsblätter hat, nämlich

  • 34 Blätter für die Spieltage
  • 1 Blatt für Summen
  • 1 Blatt zur Zusammenstellung
  • 1 Blatt Abschlusstabelle

Das Modell basiert nur auf Excel-Funktionen und verzichtet vollständig auf Makros. Es kann durch jede Person mit Excel-Grundkenntnissen (Datei öffnen, Arbeitsblatt aufrufen, Eintragungen vornehmen, speichern, drucken, Datei schließen) bedient werden.

Verzichtet wurde auf komplizierte Formelkonstruktionen. Stattdessen können die Operationen von der Spieltagtabelle bis zur Abschlusstabelle anhand der Formeln nachvollzogen werden.

Im Folgenden werde ich die Tabellen im Einzelnen erläutern.

 

Arbeitsblatt Spieltag („1“ bis „34“)

Die Spielaufstellungen und zugehörige Spieltage sind für alle 34 Spieltage bereits eingetragen.  Quelle de Daten ist die Website http://www.bulibox.de [1] mit Stand 20.07.2016.

Hier als Beispiel der 1. Spieltag:

Spieltag1

Nehme Eintragungen bitte nur in den gelb markierten Zellen (D5:E13) vor.

Die Zelle F5 enthält diese Formel (mit dem Ausfüllkästchen bis F13 herunterziehen):

=WENN(D5=““;““;WENN(D5>E5;3;WENN(D5=E5;1;WENN(D5<E5;0))))

Das heißt, wenn die Anzahl der Tore in D5 größer als die Anzahl in E5 ist, gibt es 3 Punkte, bei unentschieden gibt es 1 Punkt und ist D5 kleiner als E5 gibt es 0 Punkte.

In den Zellen G5 (bis G13) habe ich diese Formel eingetragen:

=WENN(E5=““;““;WENN(E5>D5;3;WENN(E5=D5;1;WENN(E5<D5;0))))

Die Bedeutung ist gleich der aus F5.

Damit ist es in diesen Arbeitsblättern schon getan. Falls sich die Spieltage ändern sollten, kannst du sie in A5:A13 korrigieren, das hat keine Auswirkungen auf das Modell.

Rechts neben der abgebildeten Tabelle befindet sich außerhalb des Druckbereichs eine Hilfstabelle, mit der du nicht arbeiten musst. Sie fasst die erzielten und erhalten Tore je Mannschaft zusammen, errechnet die Tordifferenz, erfasst die Punkte und vermerkt Sieg – Unentschieden – Niederlage.

Spieltag2

In K5:K13 werden die Namen der Heimmannschaften übernommen, in K14:K22 die der Gastmannschaften.

L5 enthält diese Formel (runter ziehen bis L13).

=WENN(D5=““;““;D5)

L14 diese(runter ziehen bis L22):

=WENN(E5=““;““;E5)

In Spalte M sind analog diese Formeln enthalten:

=WENN(E5=““;““;E5)

und

=WENN(D5=““;““;D5)

N5:N22 bekommen durchgehend diese Formel:

=WENN(UND(L5=““;M5=““);““;L5-M5)

In Spalte O habe ich diese Formeln eingetragen:

=WENN(F5=““;““;F5)

und ab O14

=WENN(G5=““;““;G5)

Spalte P bekommt durchgehend

=WENN(UND(L5=““;M5=““);““;WENN(L5>M5;1;““))

Spalte Q diese Formel:

=WENN(UND(L5=““;M5=““);““;WENN(L5=M5;1;““))

und Spalte R diese:

=WENN(UND(L5=““;M5=““);““;WENN(L5<M5;1;““))

 

Arbeitsblatt „Summen“

Das Arbeitsblatt enthält 5 Tabellen:

  • Punkte
  • erzielte Tore
  • erhaltene Tore
  • Sieg
  • Unentschieden
  • Niederlage

Der äußere Aufbau ist identisch, hier als Beispiel ein Auszug aus der Tabelle „Punkte“:

Summen1

Je Spieltag ist eine Spalte enthalten, eine Summenspalte schließt noch an.

Die Zeilenbeschriftungen beziehen sich auf alle 18 Mannschaften. In der Tabelle „Punkte kum“ sind sie manuell eingetragen, die anderen Tabellen beziehen sich auf B4:B21.

In Spalte C4 (Spieltag 1)habe ich folgende Formel eingetragen:

=SVERWEIS($B4;’1′!$K$5:$O$22;5;FALSCH)

In Spalte D4 wird mit dieser Formel

=SVERWEIS($B4;’2′!$K$5:$O$22;5;FALSCH)

Bezug auf das Arbeitsblatt „2“ (2. Spieltag) genommen usw.

Die Formeln werden jeweils bis zur Zeile 21 heruntergezogen.

Die Summe in AK4 wird hiermit berechnet:

=SUMMEWENN(C4:AJ4;“<>#NV“;C4:AJ4)

Analog erfolgt die Verformelung für die anderen 4 Tabellen. Zu beachten ist dabei der jeweils andere Spaltenindex in der SVERWEIS-Funktion.

 

Arbeitsblatt „Zusammenstellung“

Hier steht nun die Aufgabe an, jeder Mannschaft entsprechend ihren Spielergebnissen einen Rang zuzuordnen.

Auszugsweise sieht die Tabelle so aus:

Zusammenfassung1

Die Zellen sind wiederum verformelt und greifen auf die Tabellen im Arbeitsblatt „Summen“ zu.

Die Formeln im Einzelnen (runter ziehen bis Zeile 21):

B4:  =Summen!B4

C4:  =SUMME(INDEX(Summen!$C$4:$AJ$21;VERGLEICH($B4;

Summen!$B$4:$B$21;0);Abschlusstabelle!$E$4)

:INDEX(Summen!$C$4:$AJ$21;VERGLEICH($B4;Summen!$B$4:$B$21;0);

Abschlusstabelle!$E$5))

D4: =SUMME(INDEX(Summen!$C$25:$AJ$42;VERGLEICH($B4;

Summen!$B$25:$B$42;0);Abschlusstabelle!$E$4)

:INDEX(Summen!$C$25:$AJ$42;VERGLEICH($B4;Summen!$B$25:$B$42;0);

Abschlusstabelle!$E$5))

E4: =SUMME(INDEX(Summen!$C$46:$AJ$63;VERGLEICH($B4;

Summen!$B$46:$B$63;0);Abschlusstabelle!$E$4)

:INDEX(Summen!$C$46:$AJ$63;VERGLEICH($B4;Summen!$B$46:$B$63;0);

Abschlusstabelle!$E$5))

F4:  =D4-E4

H4: =WENN($C4=KGRÖSSTE($C$4:$C$21;1);1;WENN($C4=KGRÖSSTE($C$4:$C$21;2);2;

WENN($C4=KGRÖSSTE($C$4:$C$21;3);3;WENN($C4=KGRÖSSTE($C$4:$C$21;4);4;

WENN($C4=KGRÖSSTE($C$4:$C$21;5);5;WENN($C4=KGRÖSSTE($C$4:$C$21;6);6;

WENN($C4=KGRÖSSTE($C$4:$C$21;7);7;WENN($C4=KGRÖSSTE($C$4:$C$21;8);8;

WENN($C4=KGRÖSSTE($C$4:$C$21;9);9;WENN($C4=KGRÖSSTE($C$4:$C$21;10);10;

WENN($C4=KGRÖSSTE($C$4:$C$21;11);11;WENN($C4=KGRÖSSTE($C$4:$C$21;12);12;

WENN($C4=KGRÖSSTE($C$4:$C$21;13);13;WENN($C4=KGRÖSSTE($C$4:$C$21;14);14;

WENN($C4=KGRÖSSTE($C$4:$C$21;15);15;WENN($C4=KGRÖSSTE($C$4:$C$21;16);16;

WENN($C4=KGRÖSSTE($C$4:$C$21;17);17;

WENN($C4=KGRÖSSTE($C$4:$C$21;18);18))))))))))))))))))

I4: =WENN($F4=KGRÖSSTE($F$4:$F$21;1);1;WENN($F4=KGRÖSSTE($F$4:$F$21;2);2;

usw. bis WENN($F4=KGRÖSSTE($F$4:$F$21;18);18))))))))))))))))))

J4: =WENN($D4=KGRÖSSTE($D$4:$D$21;1);1;WENN($D4=KGRÖSSTE($D$4:$D$21;2);2;

usw. bis WENN($D4=KGRÖSSTE($D$4:$D$21;18);18))))))))))))))))))

K4: =((H4*1000)+(I4*100)+(J4*10))-ZEILE()/1000000

M4: =RANG(K4;$K$4:$K$21;1)

 

Da gib es noch ein wenig Erklärungsbedarf. Die Formeln sind z.T. sehr lang. Das ist aber dem geschuldet, dass alles nachvollziehbar sein soll.

Die Formel in C4 besteht aus 3 Teilen:

=SUMME(INDEX(Summen!$C$4:$AJ$21;VERGLEICH($B4;

Summen!$B$4:$B$21;0);Abschlusstabelle!$E$4)

:INDEX(Summen!$C$4:$AJ$21;VERGLEICH($B4;Summen!$B$4:$B$21;0);

Abschlusstabelle!$E$5))

 

  1. Teil:

INDEX(Summen!$C$4:$AJ$21;VERGLEICH($B4;Summen!$B$4:$B$21;0);

Abschlusstabelle!$E$4)

Als Argument „Matrix“ wird die Tabelle C4:AJ21 im Arbeitsblatt „Summen“ verwendet.

Das Zeilenargument wird mit Hilfe der Funktion VERGLEICH bestimmt: Bestimme die Position des Wertes aus B4, also 1. FC Köln in der Matrix B4:B21, Vergleichstyp 0 (genaue Übereinstimmung).

Das Spaltenargument füllt sich aus Zelle E4 im Arbeitsblatt „Abschlusstabelle“. Das ist der Beginn der Auswertung in der Abschlusstabelle, hier wird mit dem 1. Spieltag begonnen.

  1. Teil

Der 2. Teil (nach dem Doppelpunkt) füllt sich analog dem 1. Teil. Nur das Spaltenargument der INDEX-Funktion greift auf die „bis zum“-Angabe in E5 der Abschlusstabelle zu.

  1. Teil

Um die Teile 1 und 2 wird schließlich noch die Funktion SUMME gelegt.

Mit den Formeln in D4 und E4 verhält es sich ebenso. In F4 wird die Tordifferenz errechnet.

 

Nun geht es an die Bestimmung des Tabellensiegers. Die Regel  besagt, dass

„Die erreichten Punkte einer Spielzeit werden addiert und ergeben so für jeden Spieltag eine aktuelle Rangliste der Vereine. Bei Punktgleichheit entscheidet die bessere Tordifferenz über die Reihenfolge der Platzierung, bei gleicher Differenz die Anzahl der erzielten Tore. Sollten danach zwei Mannschaften immer noch gleichplatziert sein, entscheidet das Gesamtergebnis aus den Partien gegeneinander, wobei die auswärts erzielten Tore stärker zählen. Falls auch die erzielten Auswärtstore in allen Spielen gleich sind, wird auf neutralem Platz ein Entscheidungsspiel ausgetragen. Dies war bisher in der Bundesliga jedoch noch nie der Fall.“ [1]

In H4 soll der Sieger nach Punkten ermittelt werden. Die Formel in H4 besteht aus 18 der folgenden Abfrage, nur das jedes Mal ein anderes „k“ verwendet wird.

=WENN($C4=KGRÖSSTE($C$4:$C$21;1);1

Nun wird es ganz sicher vorkommen, dass es mehrere Sieger nach Punkten gibt, gerade nach den ersten Spieltagen. Es muss also, der Regel entsprechend, bei Punktgleichheit die bessere Tordifferenz entscheiden. Das erfolgt in Spalte I. Bei gleicher Tordifferenz entscheidet die Anzahl der erzielten Tore. Das erfolgt in Spalte J.

Die Formeln in I4 und J4 sind analog der Formel in H4 zu sehen, nur das Matrixargument in KGRÖSSTE ist jeweils unterschiedlich.

Die Formel in K4

=((H4*1000)+(I4*100)+(J4*10))-ZEILE()/1000000

verarbeitet in einem ersten Schritt die Regel zur Bestimmung des Rangersten, Rangzweiten usw. Die Platzierungen nach Punkten, nach Tordifferenzen und nach erzielten Toren werden addiert, das höchste Ergebnis müsste der Sieger sein.

Aber weit gefehlt. Die folgende Abbildung zeigt die Ergebnisse von zwei Mannschaften. Schon auf den ersten Blick entscheide ich mich für die erste Mannschaft als Sieger, denn sie hat schon eine bessere Platzierung nach Punkten und insgesamt eine kleinere Rangziffer. Damit wird ihr auch der bessere Rang zugeordnet.

Zusammenfassung2

Rechne ich mit dieser Formel

=H27*1000000+I27*1000+J27*10

ist die Entscheidung richtig, die erste Mannschaft ist eindeutig Sieger. Mit den unterschiedlichen Faktoren nehme ich gleichzeitig noch eine Gewichtung vor, Punktplatzierungen werden am höchsten bewertet.

Den Formelteil „-ZEILE()/1000000“ verwende ich, um doppelte Ränge und  Platzierungslücken gänzlich auszuschließen. Die treten insbesondere schon nach dem 1. Spieltag auf.

Mit der Formel

=RANG(K5;$K$4:$K$21;1)

in M4 schließlich bestimme ich den jeweiligen Rang der Mannschaften. Als Argument Reihenfolge bestimme ich 1, aufsteigend.

 

Arbeitsblatt „Abschlusstabelle“

Die Abschlusstabelle zeigt letztlich in der Reihenfolge der Ränge von 1 bis 18 den Status der Mannschaften nach dem letzten Spieltag.

Abschluss1

Die Abbildung zeigt die Tabelle vor dem ersten Spieltag.

Mit der Formel

=BEREICH.VERSCHIEBEN(Zusammenstellung!$B$3;

VERGLEICH(KKLEINSTE(Zusammenstellung!$M$4:$M$21;B9);

Zusammenstellung!$M$4:$M$21;0);0)

in C9 suche ich zunächst mit der Funktion VERGLEICH in der Spalte M des Blattes „Zusammenstellung“ die Position, die dem Wert in B9 in der Abschlusstabelle entspricht, also der 1.

Mit der Funktion BEREICH.VERSCHIEBEN gehe ich in der Zusammenstellung von B3 aus  und erhalte die Mannschaft.

Den Inhalt für D9 hole ich mir mit dieser Formel

=SUMME(INDEX(Summen!$C$4:$AJ$21;

VERGLEICH(Abschlusstabelle!$C9;Summen!$B$4:$B$21;0);

Abschlusstabelle!$E$4):INDEX(Summen!$C$4:$AJ$21;

VERGLEICH(Abschlusstabelle!$C9;Summen!$B$4:$B$21;0);

Abschlusstabelle!$E$5))

Wichtig dafür sind die beiden Zellen E4 und E5 in der Abschlusstabelle. Damit treffe ich die Vorauswahl für die im Blatt „Summen“ zu summierenden Zellen.

Steht in E4 die 1 und in E5 z.B. die 20, dann bilde ich die Summe über den Bereich C4:V4 (Spaltenüberschrift 1 bis 20).

Analog werden die Zellen E9:I9 bestückt, nur dass die Matrix in der INDEX-Formel jeweils eine andere ist.

Die Formeln werden zum Abschluss bis Zeile 26 herunter gezogen.

Nun berechne ich in J9 noch die Differenz aus H9 und I9 und ziehe die Formel ebenfalls bis Zeile 26 herunter.

Damit ist das Modell eingerichtet, die Saison kann beginnen.

 

Wie du mit dem Modell arbeitest!

Beschreibe in den Arbeitsblättern 1 bis 34 nur die gelben Zellen, also D5:E13 mit den Spielergebnissen. Vergiß nicht, die Datei nach Eintragungen zu speichern.

Die Blätter Summen und Zusammenstellung sind tabu, dort überschreibe bitte nichts.

Im Blatt Abschlusstabelle kannst du in E4 und E5 die Ziffern von 1 bis 34 eintragen. Dabei muss der Wert in E5 gleich oder größer als der in E4 sein.

Die Abschlusstabelle zeigt dir so die Platzierung für einen Spieltag (z.B. von 5 bis 5) oder kumuliert über die bisherigen Spieltage (z.B. von 1 bis 5).

 

Abschlussbemerkung

Das Modell wurde in der Saison 2015 / 2016 getestet und zeigte die richtigen Ergebnisse.

Für die kommende Saison habe ich es nur anhand fiktiver Ergebnisse getestet, aber keinen Vergleich hinsichtlich der Richtigkeit gehabt.

Ich werde daher die Saison mit diesem Modell verfolgen, auch wenn ich kein echter Fußballfan bin.

Hier kannst du dir das Modell herunter laden: 1. Bundesliga 2016-2017

 

Quellen:

[1]          https://de.wikipedia.org/wiki/Fußball-Bundesliga

Advertisements

Autor: Gerhard Pundt

Auf meiner Site https://clevercalcul.wordpress.com geht es um die Tabellenkalkulation mit Excel. Es wird über Funktionen, Diagramme, Basiswissen u.a.m. geschrieben.

2 Kommentare zu „Fußball-Ligaverwaltung, 1. Bundesliga“

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s