Office: (Office 2016) Index-Vergleich mit zwei Kriterien

Hallo zusammen!

Ich bastel seit einigen Jahren schon an einigen mehr oder weniger komplizierten Excel-Tabellen, konnte unter anderem hier immer wieder Antworten finden. Diesmal jedoch nicht, weswegen ich mich nun angemeldet habe um die Frage selbst zu stellen.
Ich hoffe hierbei, dass ich keine Lösung übersehen habe oder dergleichen, weiß aber auch nicht so wirklich wonach ich suchen sollte.

Zu meinem Problem:
Ich habe in einem Tabellenblatt in A:A Namen stehen. In Spalte B:B stehen die Positionen der Spieler. Es gibt 14 verschiedene Positionen. In Spalte H:H stehen wiederum Bewertungen der Spieler. Diese Bewertungen können von 0,00 bis 10,00 gehen.

Ich möchte nun in einem anderen Tabellenblatt zu jeder Position den am besten bewertetsten Spieler anzeigen.


In mehreren anderen Fällen arbeite ich hier mit Index(...Vergleich(...)), weswegen ich mich auch hier mit dieser Methode versucht habe. Da ich jedoch zwei Kriterien (Position und Bewertung) habe, habe ich mich an einer Matrixlösung versucht.

Momentan sieht sie folgendermaßen aus:
{=INDEX(Spieler!$A:$A;VERGLEICH("PositionX"&KGRÖSSTE(Spieler!$H:$H;1);Spieler!$B:$B&Spieler!$H:$H;0))}
bzw.
{=INDEX(Spieler!$A:$A;VERGLEICH("PositionY"&KGRÖSSTE(Spieler!$H:$H;1);Spieler!$B:$B&Spieler!$H:$H;0))}
usw.

Im Vergleich arbeite ich also mit zwei Kriterien, welche mit & verknüpft sind. Mein Problem ist nun das KGRÖSSTE. Dies gilt natürlich immer für die gesamte Spalte H:H und liefert immer die gleiche Zelle, unabhängig davon, welcher Beruf vorne gefragt ist. Die Formel funktioniert also wirklich nur bei dem Spieler (bzw. bei der Position), welcher insgesamt die beste Bewertung hat. Bei allen anderen Positionen wird ein "#NV" geliefert.

Hat ein Spieler auf der PositionY z.B. die zweitbeste Bewertung insgesamt, hilft es hier bei KGRÖSSTE k=2 zu setzen. Dies ist aber natürlich nur semi-schön, weil dieser "Rang" sich jederzeit ändern kann.


Ich hoffe ich konnte mein Problem ausführlich genug schildern und jemand hat eine Idee wie dies zu lösen ist.

Vielen Dank schonmal und einen schönen Abend noch!
Gruß
Poempel

 

Hola,

lad as doch bitte eine Beispieldatei mit händisch eingetragenem wunschergebnis hoch.

Gruß,
Steve1da

 

Hallo!

Erstmal danke für die schnelle Antwort.

Anbei dann die Beispieldatei. Hab alles ein bisschen gekürzt. In Tabelle 1 soll quasi die Top-Elf stehen. In Tabelle 2 stehen dazu die entsprechenden Daten.

Ein weiteres Problem ist mir aufgefallen. Im Beispiel im Defensiven Mittelfeld können zwei Positionen aufkommen. Hier habe ich mit "Oder(...)" gearbeitet, was aber auch einen Fehler bringt.


Ich hoffe das macht es nochmal etwas verständlicher.

Grüße
Poempel

 

Hola,

für den Torwart, als Matrixformel:

Code:

=INDEX(Tabelle2!A2:A41;VERGLEICH(MAX(WENN(Tabelle2!B2:B41="tw";Tabelle2!C2:C41));Tabelle2!C2:C41;0))

Gruß,
steve1da

 

Hallo, wenn du die unsäglichen "-" aus deiner Tabelle verbannst geht das auch mit Formel ohne CSE Abschluss...

 

Hola steve1da, wenn ein anderer Spieler zufällig (oder auch nicht zufällig) das selbe MAX hat stimmt dein Ergebnis nicht...

 

Hola,

das stimmt. Bei den Nachkommastellen hab ich das aber ausgeschlossen :)

Gruß,
steve1da

 

Nabend!

Klasse! Supervielen Dank! Im Endeffekt ja doch nicht sooo kompliziert.

Aber... MAX liefert ja nur den höchsten Wert und damit den besten Spieler jeder Position. Fürs Defensive Mittelfeld und die Innenverteidigung bräuchte ich jedoch noch den zweitbesten. Habs dann mit KGRÖSSTE wieder versucht, klappt aber nicht.

Habt ihr dafür vlt. noch eine Idee?

Exakt gleiche Werte sind tatsächlich eher unwahrscheinlich.

Vielen Dank nochmal!

Gruß,
Poempel

 

Hallo, wieso ODER("ZM";"ZDM")..? Meinst du so..? 2.bester ..: -->

PHP:

=INDEX(Tabelle2!$A$2:$A$41;AGGREGAT(14;6;ZEILE(Tabelle2!$A$2:$A$41)-1/((Tabelle2!$B$2:$B$41="ZM")+(Tabelle2!$B$2:$B$41="ZDM"))/(Tabelle2!$C$2:$C$41>0);2))

 

Hallo nochmal,

weil auf der Position die Spieler mit ZM oder mit ZDM spielen können. Ist aber kein Problem, ist ja nur meine erste Version gewesen.
Mit steve1da's Vorschlag hab ich das Problem jetzt so gelöst bekommen:

Code:

=INDEX(Tabelle2!A2:A41;VERGLEICH(MAX(WENN(Tabelle2!B2:B41="zm";Tabelle2!C2:C41);
WENN(Tabelle2!B2:B41="zdm";Tabelle2!C2:C41));Tabelle2!C2:C41;0))

Einzige was noch fehlt ist halt der zweitbeste. Danke da nochmal für deinen Vorschlag. Soviel zum Thema "nicht sooo kompliziert". Das muss ich mir jetzt erstmal genauer anschaun, AGGREGAT kenne ich noch gar nicht.

Grüße
Poempel

 

Moin!

Hab das jetzt mal mit dem Aggregat so übernommen. Verstehe nicht wirklich was es macht und es funktioniert auch nicht. Setze ich k = 1, wird mir im Defensiven Mittelfeld komischerweise der zweitbeste gezeigt. Manipuliere ich dann die Werte, sodass jemand anderes der zweitbeste ist, bleibt trotzdem der gleiche Spieler da, also der jetzige drittbeste. Setze ich k = 2, wird mir ein Spieler angezeigt bei dem ein "-" eingetragen ist.

Um es zu verstehen und selbst auf eine Lösung zu kommen... Was bewirkt alles was im Aggregat unter Array ist? Also quasi alles in Zeile(...).

Sorry, falls ich mich dämlich anstelle. Hab damit noch nie gearbeitet.

Danke nochmal im Voraus!

Grüße
Poempel

 

Hi,

hier mal eine Version, allerdings brauchst Du für jeden Spieler eine eigen Formel. Ersetze bitte ZOM durch OM, sonst klappt es nicht.

Arbeitsblatt mit dem Namen 'Tabelle1'
*	B	C	D	E	F	G	H	I	J
3	*	*	*	*	*	*	*	*	*
4	*	*	*	*	Sturm	*	*	*	*
5	*	*	*	*	Spieler 3	*	*	*	*
6	*	*	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*	*	*
9	Linkes Mittelfeld	*	*	*	Offensives Mittelfeld	*	*	*	Rechtes Mittelfeld
10	Spieler 24	*	*	*	Spieler 26	*	*	*	Spieler 32
11	*	*	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*	*	*
13	*	*	*	*	*	*	*	*	*
14	*	*	Defensives Mittelfeld	*	*	*	Defensives Mittelfeld	*	*
15	*	*	Spieler 28	*	*	*	Spieler 15	*	*
16	*	*	*	*	*	*	*	*	*
17	*	*	*	*	*	*	*	*	*
18	*	*	*	*	*	*	*	*	*
19	Linker Verteidiger	*	*	*	*	*	*	*	Rechter Verteidiger
20	Spieler 11	*	Innenverteidiger	*	*	*	Innenverteidiger	*	Spieler 29
21	*	*	Spieler 19	*	*	*	Spieler 17	*	*
22	*	*	*	*	*	*	*	*	*
23	*	*	*	*	*	*	*	*	*
24	*	*	*	*	*	*	*	*	*
25	*	*	*	*	Torwart	*	*	*	*
26	*	*	*	*	Spieler 40	*	*	*	*

Zelle	Formel
F5	=INDEX(Tabelle2!$A:$A;VERGLEICH(AGGREGAT(14;6;Tabelle2!$C$1:$C$41/(Tabelle2!$B$1:$B$41="ST")/(Tabelle2!$C$1:$C$41>0);1);Tabelle2!$C:$C;0))
B10	=INDEX(Tabelle2!$A:$A;VERGLEICH(AGGREGAT(14;6;Tabelle2!$C$1:$C$41/(Tabelle2!$B$1:$B$41="LM")/(Tabelle2!$C$1:$C$41>0);1);Tabelle2!$C:$C;0))
F10	=INDEX(Tabelle2!$A:$A;VERGLEICH(AGGREGAT(14;6;Tabelle2!$C$1:$C$41/(Tabelle2!$B$1:$B$41="OM")/(Tabelle2!$C$1:$C$41>0);1);Tabelle2!$C:$C;0))
J10	=INDEX(Tabelle2!$A:$A;VERGLEICH(AGGREGAT(14;6;Tabelle2!$C$1:$C$41/(Tabelle2!$B$1:$B$41="RM")/(Tabelle2!$C$1:$C$41>0);1);Tabelle2!$C:$C;0))
D15	=INDEX(Tabelle2!$A:$A;VERGLEICH(AGGREGAT(14;6;Tabelle2!$C$1:$C$41/(LINKS(Tabelle2!$B$1:$B$41;1)="Z")/(Tabelle2!$C$1:$C$41>0);1);Tabelle2!$C:$C;0))
H15	=INDEX(Tabelle2!$A:$A;VERGLEICH(AGGREGAT(14;6;Tabelle2!$C$1:$C$41/(LINKS(Tabelle2!$B$1:$B$41;1)="Z")/(Tabelle2!$C$1:$C$41>0);2);Tabelle2!$C:$C;0))
B20	=INDEX(Tabelle2!$A:$A;VERGLEICH(AGGREGAT(14;6;Tabelle2!$C$1:$C$41/(Tabelle2!$B$1:$B$41="LV")/(Tabelle2!$C$1:$C$41>0);1);Tabelle2!$C:$C;0))
J20	=INDEX(Tabelle2!$A:$A;VERGLEICH(AGGREGAT(14;6;Tabelle2!$C$1:$C$41/(Tabelle2!$B$1:$B$41="RV")/(Tabelle2!$C$1:$C$41>0);1);Tabelle2!$C:$C;0))
D21	=INDEX(Tabelle2!$A:$A;VERGLEICH(AGGREGAT(14;6;Tabelle2!$C$1:$C$41/(Tabelle2!$B$1:$B$41="IV")/(Tabelle2!$C$1:$C$41>0);1);Tabelle2!$C:$C;0))
H21	=INDEX(Tabelle2!$A:$A;VERGLEICH(AGGREGAT(14;6;Tabelle2!$C$1:$C$41/(Tabelle2!$B$1:$B$41="IV")/(Tabelle2!$C$1:$C$41>0);2);Tabelle2!$C:$C;0))
F26	=INDEX(Tabelle2!$A:$A;VERGLEICH(AGGREGAT(14;6;Tabelle2!$C$1:$C$41/(Tabelle2!$B$1:$B$41="TW")/(Tabelle2!$C$1:$C$41>0);1);Tabelle2!$C:$C;0))

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

 

Nabend!

Nochmal vielen Dank euch allen!

Hab noch ein bisschen an meiner Tabelle rumgebastelt, die Formeln von Edgar entsprechend angepasst und jetzt funktioniert es soweit perfekt.

Jetzt hab ich jedoch ein neues Problem... Die GES-Werte werden aus einem Haufen Daten berechnet. Das war bei der ersten Variante von steve1da kein Problem. Durch das Aggregat ist es aber nun so, dass jedes mal wenn ich in einem anderen Tabellenblatt einen Wert eingebe das Programm "laggt". Ich weiß nicht wie ich es anders nennen soll... Es ist eine relativ große Tabelle, wo jeder eingetragene Wert auf 10-15 weitere Zellen Konsequenzen hat. Trag ich nun einen Wert ein und drücke Enter, dauert es 1-5 Sekunden bis die nächste Zelle markiert ist. Das ist bei einigen Hundert Neueinträgen ziemlich nervig. Ohne die "Top-Elf" mit Aggregat passiert das nicht.
Mein PC is eigentlich für solch eine Aufgabe vollkommen überdimensioniert. Die paar Berechnungen und verknüpften Formeln sollten keine Rolle spielen.

Weiß jemand was man tun kann? Oder vlt doch eine Lösung komplett ohne Aggregat?

Danke und Gruß!
Poempel