Excel Formel Fussball Tabelle?
Hallo zusammen, ich habe einen Fussballturnierplan. Hier möchte ich das die Gruppen berechnet werden ( Punkte, Tore, Differenz) und dann nach punkten automatisch sortiert. ebenfalls soll sich dann errechnen wer in das Viertelfinale kommt ( Hier der erste und zweite jeder Gruppe, zusätzlich die beiden besten Gruppen dritten aus allen 3 Gruppen) Hat jemand solche formeln für mich? danke für tips
123
2 Antworten
Lang ist es her, seitdem ich diese Frage beantwortet habe:
Aber hier mal mein strukturelles Denken:
um keine falschen Daten zu aggregieren. Verwende ich in der Differnzspalte "I" eine gesonderte Formel.
=WENN(UND(ISTLEER(HSTAPELN(F2;H2)));"";F2-H2)
Hier stelle ich einfach sicher, dass wenn beide Felder leer sind "" ausgegeben wird und nicht 0, denn 0 hat mit einigen Aggregatsfunktionen Probleme z.B. Min, daher vermeide ich dies im vorhinein.
Ergebnisse:
In der Zelle L3 kommt folgende Formel zum Einsatz:
=LET(Ergebnis;GRUPPIERENNACH(VSTAPELN($D$2:$D$46;$E$2:$E$46);HSTAPELN(ERSTERWERT(VORZEICHEN(VSTAPELN($I$2:$I$46;-(I2:I46)));-1;0;0;1;1;3);VSTAPELN($F$2:$F$46;$H$2:$H$46);VSTAPELN($H$2:$H$46;F2:F46));LAMBDA(x;SUMME(x));;0;;VSTAPELN($C$2:$C$46;$C$2:$C$46)=RECHTS(L2;1)); WEGLASSEN(SORTIERENNACH(Ergebnis;INDEX(Ergebnis;;2);-1;INDEX(Ergebnis;;3);-1;INDEX(Ergebnis;;4);1);;-1) )
Diese Formel ist etwas komplizierter, in aller Kürze mal erklärt.
ich sortiere die Gesamte Tabelle nach Teamnamen, die dazugehörigen Werte sind 1. Punkte 2. Tore 3. Gegentore
Da die Teamnamen in 2 verschiedenen Spalten vorkommen, stapele ich beide Spalten übereinander. Deckungsgleich dazu natürlich auch die Tore.
Etwas besonderer wird es bei den Punkten, denn hier muss ich Auswerten ob gewonnen/verloren oder unentschieden gespielt wurde.
Ich bin kein Fußball Experte aber ich glaube Sieg gibt 3 Punkte Unentschieden 1 Punkt, Niederlage 0, sollte das bei Eurer Tabelle anders sein, müsstest du dies anpassen.
Die Auswertung mach ich hier in Ersterwert(Vorzeichen(...)
Mit Vorzeichen erhalte ich je nach dem ob ein Wert Positiv/negativ oder exakt 0 ist die Ergebnisse 1/-1/0, dies mache ich mir in ERSTERWERT zu nutze und weise dem 1 -> 3 zu, dem Wert 0 -> 1 zu und dem Wert -1 -> 0 zu.
Damit sichergestellt wird, das nur alle Ergebnisse des Gruppe A ausgewertet werden, kommt der folgende Teil zum Einsatz, dies ist prinzipiell ein Filter:
VSTAPELN($C$2:$C$46;$C$2:$C$46)=RECHTS(L2;1)
Damit ziehe ich mir aus Überschrift "Gruppe A" in L2 nur das A heraus und schaue in welchen Teamspielen dies ebenfalls vorkommt.
Zum Ende hin wird nur noch zuerst sortiert nach der Spalte 2, also Punkte, sind diese gleich, werden die Tore ausgewertet, sind diese auch gleich, werden die erhaltenen Gegentore ausgewertet, sollte hier eine andere Auswertung erfolgen sollen, müsstest du das anmerken, gerne kann ich dir auch helfen.
Jetzt wird nur noch die Spalte 4 (Die Gegentore ausgeblendet, da dieser erst nach dem ":" kommen. Alternativ könnte man auch eine Spalte mit ":" einfügen. Dann würde eine Formel reichen.
In P3 steht exakt nochmal die gleiche Formel mit dem einzigen Unterschied, das hier im letzten Schritt alle Spalten außer die 4 ausgeblendet werden.
Die Formel ist eigentlich copy&Paste fähig
Das war der komplizierteste Teil der Tabelle der Rest ist eigentlich relativ einfach.
Gruppen 3.:
=LET(Ergebnis;VSTAPELN(ZEILENWAHL(L3:P8;3);ZEILENWAHL(L11:P16;3);ZEILENWAHL(L19:P24;3));
SORTIERENNACH(Ergebnis;INDEX(Ergebnis;;2);-1;INDEX(Ergebnis;;3);-1;INDEX(Ergebnis;;5);1) )
Hier Greife ich auf alle oberen Ergebnistabellen zurück und ziehe mir aus jeder die 3 Zeile heraus. und sortiere auch diese wieder nach der gleichen Logik.
Hier nochmal als Detail Screenshot:
Viertelfinale:
In S3 kommt die folgende Formel zum Einsatz:
=BEREICH.VERSCHIEBEN(XVERWEIS(TEXTNACH(S4;" ");$L:$L;$L:$L);LINKS(S4;1);0)
hier passiert gar nicht viel, du suchst mittels Xverweis nach der passenden Gruppe, dies ist in S4 definiert, damit das "1. " aus 1. Gruppe A abgeschnitten wird, verwende ich die Funktion Textnach, alternativ ginge TEIL(S4;4;100) oder auch eine Kombination aus Suchen und Teil. Je nach dem was die am besten erscheint.
Als Rückgabe von XVERWEIS erhältst du die Position in der Gruppe X (A-C) steht.
mit Links mache ich jetzt das umgedrehte, ich hole mir nur das 1. Zeichen, also die "1" und dies nutze ich um von dem Gruppe A Feld mit Bereich.verschieben 1 Feld herunter zu gehen, was in dem Fall TeamA 2 ist.
Auch diese Formel ist vollkommen Copy&Paste fähig. In jede der 8 Felder.
In Y3 wird über die Formel:
=WENNS(V3="";"";V3>X3;S3;1;U3)
der Sieger ermittelt
Detailbild des Viertelfinales:
Halbfinale:
In AB3 kommt nun folgende Formel zum Einsatz:
=WENN(ISTLEER(Y3);"";Y3)
Natürlich kannst du dir wieder eine ähnliche Logik wie oben aufbauen, ich habe hier auf eine vereinfachte Logik zurückgegriffen und die Ergebnisse direkt adressiert.
Wenn deine Tabelle Skalierbar sein sollte, also für unterschiedlich große Starterfelder taugen soll, dann ist dies Variante vermutlich nicht so geeignet.
Hier wäre z.B. auch Denkbar die Spielnummer abzufragen oder die VF Tabllen um die Zeile des Viertelfinalspiels zu ergänzen. Dann könnte man hier wieder mit Bereich verschieben arbeiten.
Ich denke du verstehst recht gut selbst, was die Formel tut, ist kein Siegerteam bekannt soll auch nichts in der Halbfinal Tabelle stehen, ohne das ISTLEER würden hier sonst 0 Werte drin stehen.
Detailbild Halbfinale:
In der aktuellen Form sind die Felder nicht copy&Paste fähig, sondern jedes Ergebnisfeld wird einzeln angesprochen.
Ein Beispiel für eine automatisierte Logik wäre z.B. die Folgende:
Formel:
=XVERWEIS(TEXTVOR(AB4;" ")*1;$R:$R;$Y:$Y)
Platz 1 und 2 wird wieder mit
=WENNS(AE3="";"";AE3>AG3;AB3;1;AD3)
=WENNS(AE3="";"";AE3<AG3;AB3;1;AD3)
ausgewertet.
Finale:
Hier wird exakt die gleicher Herangehensweise wie beim Halbfinale genutzt, daher werde ich das jetzt nicht näher erläutern.
Formel für die jeweiligen Sieger: (adaptiert auf die vorherige Logik)
=XVERWEIS(TEXTVOR(AK4;" ")*1;$AA:$AA;$AH:$AH)
Formel für die jeweiligen 2 (Spiel um Platz 3):
=BEREICH.VERSCHIEBEN(XVERWEIS(TEXTVOR(AK9;" ")*1;$AA:$AA;$AH:$AH);1;0)
Detailbild des Finales:
Würde mich über Rückmeldung freuen!
- - - alte Antwort
kannst du vielleicht mal ein Screenshot teilen, wie du dir das ungefähr vorstellst?
Als Strukturellen Aufbau, damit man darauf ansetzen kann?
Wieviele Teilnehmer Pro Gruppe, wie viele Teilnehmer insgesamt, Exakt die gleiche Logik wie im echten Fußball beim weiter kommen oder eigens kreierte, es wäre ja auch denkbar, dass z.B. immer die Gruppen 1 im Viertelfinale gegeneinander Spielen.
Ein paar kleine Infos mehr bräuchten wir da schon vor dir.
Mal für den Anfang. Vielleicht kannst Du darauf aufbauen und weitermachen, wobei ich bei gleichem Torverhältnis (K8-K13) eine weitere Ranggewichtung der Tordifferenz (*0,01) hinzugefügt habe.
Das mit der Feinkorrektur um 0,1 bzw. 0,01 ist auch eine gute Idee, schöne Variante, bei dir kann man echt immer gucken und sieht fast immer eine Variante, die ich selber nicht auf dem Schirm hatte.