Wie kann ich bei einem Fußballspielplan nur die Heim- bzw. Auswärtspunkte heraussuchen?
Guten Tag, ich suche nun schon lange nach eine geeignete Funktion in Excel für das herausfiltern von Heim/Auswärtspunkten der jeweiligen Mannschaft.
Um mein Ziel etwas besser beschreiben zu können, habe ich einen Screenshot bearbeitet um es zu visualisieren.
In Tabelle 4 in den Spalten "H";"J";"K";L";usw. sollen die Punkte je Spieltag rein (nur Heimspiele). Also muss in "H" eine "3" rein, da Manchester City am 1. Spieltag Heim spielt und gewonnen hat. In der Spalte "J" darf nichts rein, da Manchester City am 2. Spieltag ein Auswärtsspiel hatte. In der Spalte "K" muss dann wieder eine Zahl rein (also erhaltene Punkte), da Manchester City wieder ein Heimspiel hat. (auf dem Screenshot nicht mehr zu erkennen)
Also muss die Funktion in Tabelle 3 Spalte "B" (Mannschaften die Heimspiel haben) nach einem bestimmten Team suchen (in diesem Beispiel Manchester City), sollte das Team an diesem Spieltag in der Spalte "B" sein, so sollen die Punkte (Spalte "G") übernommen werden in Spalte "H" Tabelle 4 (am ersten Spieltag). Sollte das Team an diesem Spieltag nicht in Spalte "B" zu finden sein, so soll kein Eintrag in Tabelle 4 z.B. Spalte "H" stattfinden.
Das Ziel ist eigentlich leicht zu verstehen, ich möchte anhand dieser Punkte erkennen, wie gut die jeweilige Mannschaft gerade drauf ist bei Heim/Auswärtsspielen also wie viele Punkte sie z.B. in den letzten 3 - 4 Spielen bekommen hat.
Wichtig dabei ist, dass jeder Spieltag ein separate Spalte hat, also Spieltag 1: Tabelle 4 Spalte "H" Spieltag 2: Tabelle 4 Spalte "J" usw...
Aber wenn die Mannschaft z.B. am 2. Spieltag ein Auswärtsspiel hat, darf in Spalte "J" nicht "0" stehen, denn die "0" bedeutet ja gleichzeitig dass die Mannschaft ihr Heimspiel verloren hat, obwohl sie, in dem Falle, gar kein Heimspiel hatte.
Ich hoffe, das ist mit Excel umsetzbar und dass ihr mir helfen könnt!
Solltet ihr meine Frage/Ziel nicht verstehen, fragt gerne nach! Ich antworte schnell!
Mit freundlichen Grüßen, Justel
1 Antwort
Ich kann nicht ganz verstehen, warum die Spieltage in eigene Spalten übernommen werden sollen. Für die anderen Angaben wie Tore musst Du doch über eine Formel auch direkt auf die Spielergebnisse zugreifen und erzeugst keine Zwischenablage.
Über Summewenn kannst Du in Tabelle 4 die Angaben für die Heimspiele ermitteln,
in C29
=SUMMEWENN(Tabelle3!B:B;B29;Tabelle3!G:G) z.B. für ManU
D29 entsprechen
=SUMMEWENN(Tabelle3!B:B;B29;Tabelle3!D:D)
Je Spalte die Ergebnisse übernehmen ginge mit dem normalen Sverweis. Kompliziert ist nur, dass jeweils pro Spieltag der zu durchsuchende Bereich geändert werden muss. Ich nehme an, dass willst Du beim Kopieren der Formel in die Spalten je Spieltag möglichst automatisch haben. Eine Lösung dafür kann ich nicht aus dem Ärmel schütteln, daher die Frage, ob es wirklich benötigt wird, bevor ich mir einen abbreche.
Grundsätzlich einfacher wäre diese Geschichte, wenn der Spieltag in einer eigenen Spalte stehen würde.
Unter der Annahme, dass der Spieltag in Tabelle 3 in Spalte A steht (also die anderen Spalten um eins rutschen)
=INDEX(Tabelle3!$A:$G;VERGLEICH(SPALTE(H29)-7&$B29;Tabelle3!$A:$A&Tabelle3!$B:$B;0);7)
mit STRG+SHIFT+Enter abschließen, damit sich die Matrixklammern {} bilden
näheres ist hier erläutert:
Das Vertrauen ehrt mich - was Du vorhast ist aber nicht trivial, das kann ich auch nicht einfach runtertippen und weiß nicht, ob ich für so ein Projekt einen Batzen Zeit investieren kann und will.
Die Problemstellung mit den letzten drei Heim oder Auswärtsspielen fängt ja schon damit an, dass man erstmal rausbekommen muss, ab dem wievielten Spieltag die Betrachtung erfolgen muss. Unter der Annahme, dass in Tabelle3 der Spieltag in Spalte A steht (also am ersten Spieltag eine 1 in jeder Zeile mit einer Begegnung), könnte man das so machen:
=SUMMENPRODUKT((Tabelle3!C5:C500="ManU")*(Tabelle3!A5:A500>=KGRÖSSTE(WENN(Tabelle3!C5:C500="ManU";Tabelle3!A5:A500);3)-0)*(Tabelle3!G5:G500))
auch Matrixformel, d.h. Strg+Shift+Enter zum Abschließen.
Den Bereich jeweils Zeile 5 - 500 habe ich einfach angenommen, ich weiß nicht, wie weit Deine Daten tatsächlich gehen. Wichtig ist, dass in A nur Zahlen stehen, sonst gibt es eine Fehlermeldung.
Die fettgedruckte 3 ist die Anzahl der Spiele, die rückwirkend berücksichtigt werden. Die 0 ist die Differenz zwischen dem maximalen Spieltag (in der BuLi 34.) Spieltag und dem Spieltag, der für die Auswertung relevant ist. Mir ist nichts eleganteres eingefallen, wie man sonst die noch nicht stattgefundenen Spieltage abgrenzen kann.
Grob erklärt: Über das kgrösste (+dieser Korrekturfaktor, der im Beispiel auf 0 steht) wird ermittelt, ab dem wievielten Spieltag die Betrachtung erfolgt. Es ist quasi der drittgrößte Zahl (Spieltag), bei der die Mannschaft bei "Heim" geführt wird.
Das Summenprodukt liefert die Punkte, die in den drei Heimspielen erzielt wurden.
Guten Tag, danke für deine ausführliche Antwort!
Ich möchte dir gerne mein endgültiges Ziel aufzeigen, damit du weißt, warum ich diese Funktion benötige. Eventuell hast du dann sogar eine bessere Idee diese umzusetzen. Ich bin dir sehr dankbar, dass du dir die Zeit nimmst!
Also, ich bin gerade dabei einen Wert zu entwickeln, der sich aus mehreren Faktoren zusammensetzt wie z. B. geschossene Tore, kassierte Tore, Punkteausbeute der letzten 5 Spiele, aber auch Punkteausbeute der letzten 3 Heimspiele bzw. Auswärtsspiele (je nachdem ob die Mannschaft das nächste Spiel Zuhause oder Auswärts bestreitet). Dieser Wert soll dann logischer Weise ein Prognose aufzeigen, welche Mannschaft favorisiert ist.
Doch ich habe Probleme genau beim automatischen errechnen der Punkteausbeute der letzten drei Heim/Auswärtsspiele. Ich möchte die Punkte automatisch angezeigt bekommen, wenn ich einfach die Ergebnisse der Spieltage eintrage.
Also wenn zum Beispiel der 20. Spieltag ist und ManU ein Auswärtsspiel hat, soll mir die Funktion die erhaltenen Punkte aus den letzten drei Auswärtsspielen "herausgeben".
Nur wie ist das am besten umsetzbar?
Ich denke, sobald dieses "Tool" fertig geschrieben ist, kann man aus vielen verschiedenen Faktoren (auch Marktwert der Mannschaften) die favorisierten Teams schnell herausfinden, ohne großartig sich mit der Liga zu beschäftigen. (Natürlich muss man beachten ob die Mannschaft ein Pokalspiel zuvor hatte und somit eventuell geschwächt aufläuft)
Daher möchte ich die Idee unbedingt umsetzen, doch leider fehlt mir da etwas das "Know - How" in Excel und in der Programmierung.
Mit besten Grüßen,
Justel