Letztes Spieldatum und -stände aus Liste finden

[truncatellina]

Liebe Forenuser,
 
für meinen Lieblingsverein führe ich regelmäßig "Statistik" und schreibe mir alle Ergebnisse (mit Datum, Gegner, Heim/Auswärts, Sieg oder Niederlage und die Tordifferenz in verschiedenen Spalten) mit -> siehe Anhang als Beispiel.
 
Da sich hier nun über die letzten Jahrzehnte viele Tausend Einträge akkumuliert haben, frage ich mich, wie ich mir aus der kompletten Liste in einer separaten Zelle den „letzten Sieg“ bzw. „die letzte Niederlage“ gegen ein bestimmtes Team als Datum ausgeben kann. Dazu müsste man wohl (i) die Liste nach dem entsprechenden Gegner durchsuchen (ii) schauen, ob es sich um Sieg oder Niederlage handelte und (iii) das letztmögliche Datum finden das (i) und (ii) vereint.
 
Zusätzlich würde mich interessieren, ob jemand eine Lösung kennt, wie man aus der gleichen Liste in einer separaten Zelle auch das Datum bzw. Ergebnis der höchsten Niederlage bzw. Sieges gegen ein bestimmtes Team ausgeben lassen kann.
 
Beachte, dass für beide Fälle diese Suche nicht einmalig erfolgen soll, sondern sich die Liste ja weiterhin permanent verlängert und die ausgegebenen Lösungen sich daher ändern / aktualisieren können muss.

Besten Dank und Grüße,
Christian

[RPP63]

Moin!
Formel aus H2 nach rechts und unten ziehen:

	A	B	C	D	E	F	G	H	I	J
1	Datum	Team	H/A	Result	Goal diff.			Win	Draw	Loose
2	01.01.2015	Devils	H	Win	5		Tigers		05.10.21	09.03.23
3	05.03.2017	Angels	H	Draw	0		Angels	10.04.24	05.03.17
4	06.08.2017	Tigers	A	Loose	-2		Devils	01.01.15	02.10.20	01.05.25
5	07.09.2018	Tigers	H	Loose	-3
6	01.01.2020	Devils	A	Loose	-6
7	02.10.2020	Devils	A	Draw	0
8	05.10.2021	Tigers	H	Draw	0
9	07.11.2022	Devils	A	Loose	-6
10	05.03.2023	Angels	H	Win	2
11	09.03.2023	Tigers	A	Loose	-8
12	10.04.2024	Angels	A	Win	2
13	01.05.2025	Devils	H	Loose	-3

Zelle	Format	Wert
H2	TT.MM.JJ;;	00:00:00

Zelle	Formel
H2	=MAXWENNS($A$2:$A$100;$D$2:$D$100;H$1;$B$2:$B$100;$G2)

Kann man übrigens auch ohne Formeln per Pivot lösen

Gruß Ralf

[HKindler]

Hi,

letzter "win" (wenn in H1 "win" steht):
=WENNFEHLER(MAX(FILTER($A$2:$A$13;($B$2:$B$13=$G2)*($D$2:$D$13=H$1)));"---")

Goal Diff highest win:
=WENNFEHLER(MAX(FILTER($E$2:$E$13;($B$2:$B$13=$G2)*($D$2:$D$13="Win")));"---")

Goal Diff highest lose:
=WENNFEHLER(MIN(FILTER($E$2:$E$13;($B$2:$B$13=$G2)*($D$2:$D$13="Loose")));"---") wobei es eigentlich "lose" heißen müsste!

Date highest win (in O2 steht die Formel von Goal Diff highest win, die -1 sorgt dafür, dass bei mehreren höchsten "win" der letzte Eintrag ausgegeben wird):
=WENNFEHLER(XVERWEIS(O2&$G2;$E$2:$E$13&$B$2:$B$13;$A$2:$A$13;;;-1);"---")

Und das Datum des letzten win/draw/lose kann man ganz easy auch mit einer Pivot ermitteln (ev. geht das auch mit diff / Datum diff)
"Team" in Zeilen, "Result" in Spalten und "Datum" in Werte. "Datum" in den Wertfeldeinstellungen auf "Max" umstellen.

[slowboarder]

Hi

ich mach dir mal einen Basis-Formel, mit der du mit wenigen Variationen die verschiedenen Ergebnisse ermitteln kannst.

PHP-Code:

=LET(
a;$A$2.:.$E$9999;
b;FILTER(a;(SPALTENWAHL(a;2)="Tigers")*(SPALTENWAHL(a;4)="Win");{"x";"x";"x";"x";"x"});
c;SORTIEREN(b;1;-1);
INDEX(c;1;1))


das Prinzip ist, dass die Liste (a) zunächst nach der Mannschaft und nach der Ergebnisart (Win, Loose, Draft) gefiltert wird (b)
dann wird die Liste sortiert, so dass der gewünschte Wert oben steht © und am Schluss gibt man das gewünschte Ergebnis aus.

die gezeigte Formel zeigt die Lösung für den letzten Sieg, dh es wird nach Win gefiltert und diese Liste nach Datum (Spalte 1) absteigend sortiert (-1), so dass der letzte Sieg in der Liste oben steht.

wenn du das Datum mit dem höchsten Sieg haben willst, musst du nach der Spalte mit dem Ergebnis (E/5) absteigend sortieren und das Datum als Ergebnis ausgeben, das wäre dann: ... c;Sortieren(b;5;-1);...
ausgeben tust du dann trotzdem das Datum: ...Index(c;1;1)...
wenn du den Wert des höchsten Sieges sehen willst, dann gibst du bei gleicher Sortierung eben den Wert aus: ...Index(c;1;5)...

für die höchste Niederlage musst du dann die Sortierrichung umdrehen: ...c;Sortieren(b;5;1)...

Gruß Daniel

[Egon12]

Hallo Daniel,

wenn O2021 beim TO stimmt dann kennt das die Funktion SPALTENWAHL() noch nicht. Das geht erst ab O2024.

Gruß Uwe

[slowboarder]

dann ersetze SPALTENWAHL durch INDEX
funktioniert, solange man nur eine Spalte benötigt, genauso
oder definiere dir die einzelnen Spalten ebenfalls als Variable.

[Egon12]

Es gibt noch das Problem mit dem .:. Da ist O2021schon recht eingeschränkt unterwegs. Was aber klappt ist dies einzeln in Variablen packen und via WAHL(SEQUENZ)) dies zusammenschrauben.

Mal ein Schnellschuss für die 1. 6 Spalten: 

Code:

=LET(
    xA;$A$2:$E$13;
    xB;WENNFEHLER(MAX(FILTER(INDEX(xA;;1);(G2=INDEX(xA;;2))*(INDEX(xA;;4)="Win")));"-");
    xC;WENNFEHLER(MAX(FILTER(INDEX(xA;;1);(G2=INDEX(xA;;2))*(INDEX(xA;;4)="Draw")));"-");
    xD;WENNFEHLER(MAX(FILTER(INDEX(xA;;1);(G2=INDEX(xA;;2))*(INDEX(xA;;4)="Loose")));"-");
    xEm; FILTER(xA; INDEX(xA;;2)=G2);
    xE;MAX(FILTER(INDEX(xEm;;1); INDEX(xEm;;5)=MAX(INDEX(xEm;;5))));
    xFm; FILTER(xA; INDEX(xA;;2)=G2);
    xF;MAX(FILTER(INDEX(xEm;;1); INDEX(xEm;;5)=MAX(INDEX(xEm;;5))));
    WAHL(SEQUENZ(1;6);xB;xC;xD;"";xE;xF)
)

Das als Matrix 2-dimensional auszugeben ist in O2021 LTSC problematisch. Deshalb mit runterziehen.

Gruß Uwe

[truncatellina]

Vielen Dank an alle, das hat wirklich sehr gut geklappt. Ich habe die Lösungen von Ralf und Helmut eingebaut und die funktionieren Einwandfrei.
 
Wenn ich hier gleich noch einen Zusatz nachschieben kann, würde mich interessieren, ob es auch möglich ist nach „Serien“ zu suchen / filtern. Also zB. #meiste Siege in Folge. Nicht alle Serien müssten dann aber in der Tabelle direkt untereinanderstehen, denn wenn ich nach #meiste Heimniederlagen in Folge gegen Team XY suche, können ja dazwischen auch viele weitere Einträge vorhanden sein. Als Ausgabe wäre die Anzahl der Ereignisse einerseits und der Beginn und das Ende der Serie als Datum andererseits wünschenswert.
 
Nochmals besten Dank!
 
PS. Das Thema Pivot ist mir neu (und interessant) würde ich aber erstmal versuchen auszuklammern (Nicht zuletzt da meine Megamatrix einige Leerzeilen hat etc).

[Egon12]

Komplette Formel in H2:

Code:

=LET(
    xA;$A$2:$E$13;
    xB;WENNFEHLER(MAX(FILTER(INDEX(xA;;1);(G2=INDEX(xA;;2))*(INDEX(xA;;4)="Win")));"-");
    xC;WENNFEHLER(MAX(FILTER(INDEX(xA;;1);(G2=INDEX(xA;;2))*(INDEX(xA;;4)="Draw")));"-");
    xD;WENNFEHLER(MAX(FILTER(INDEX(xA;;1);(G2=INDEX(xA;;2))*(INDEX(xA;;4)="Loose")));"-");
    xEm; FILTER(xA; INDEX(xA;;2)=G2);
    xE;MAX(FILTER(INDEX(xEm;;1); INDEX(xEm;;5)=MAX(INDEX(xEm;;5))));
    xFm; FILTER(xA; INDEX(xA;;2)=G2);
    xF;MAX(FILTER(INDEX(xEm;;1); INDEX(xEm;;5)=MAX(INDEX(xEm;;5))));
    xG;MAX(FILTER(INDEX(xA;;5);INDEX(xA;;2)=G2));
    xH;MIN(FILTER(INDEX(xA;;5);INDEX(xA;;2)=G2));
    WAHL(SEQUENZ(1;9);xB;xC;xD;"";xE;xF;"";xG;xH)
)

Dies sollte in O2021 funktionieren.

  Example last win.xlsx (Größe: 13,42 KB / Downloads: 6)

Gruß Uwe

[Chappy]

Eine Möglichkeit mit Power Query.