Rangliste, 3 Formeln in eine packen

anon85587316 · 12. November 2019 um 16:36

Guten Morgen zusammen,

ich habe in meiner Tabbi folgende Spalten:

DG für den Rang
Formel: =RANG(DH13;$DH$13:blush:DH$166)

DH für die Gesamtpunktzahl
Formel : =RSUMME(J13)

DI für den Namen des Teilnehmers
Formel : =E13

Die Rangliste ist noch unsortiert, was ich mit folgenden Formeln bewerkstelligen könnte:

=KKLEINSTE(Tabbi!$DG$13:blush:DG$166;ZEILE()-ZEILE(Tabbi!$DG$12)) (für den Rang)

{=INDEX(Tabbi!E:E;KKLEINSTE(WENN(Tabbi!$DG$13:blush:DG$166=SE!L13;ZEILE(Tabbi!$13:blush:166));ZÄHLENWENN($L$13:L13;L13)))} für den Namen

{=KGRÖSSTE(WENN(Tabbi!$E$13:blush:E$166"";(Tabbi!DH$13:smiley:H$166-ZEILE(Tabbi!$13:blush:166)/10000000));ZEILE()-12)} für die Punktzahl

Ich möchte nun,dass Rang,Name und Punkte in einer Zelle erscheinen ohne zusätzlich Hilfspalten oder ähnliches.

Wie bekomme ich quasi alle 3 Formeln in eine einzige?

Sortierfolge sollte 1,1,22,3,3 usw. sein wenn vorhanden.

Geht das überhaupt und hat jemand ne Idee

LG Frank

Thomas_Ramel · 12. November 2019 um 16:37

Grüezi Frank

Mit geringfügig anderen Formeln könntest Du das (momentan im gleichen Tabellenblatt) wie folgt angehen:

Tabellenblatt: [Mappe1]!Tabbi1
 │ DG │ DH │ DI │ DJ│ DK │ DL│
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
11 │ Rang │ Punkte │ Name │ │ │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
12 │ │ │ │ │ │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
13 │ 12 │ 4 │ Name 1 │ │ 1 / Name 7 / 20 │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
14 │ 7 │ 10 │ Name 2 │ │ 2 / Name 14 / 18 │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
15 │ 14 │ 3 │ Name 3 │ │ 3 / Name 6 / 17 │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
16 │ 5 │ 15 │ Name 4 │ │ 3 / Name 12 / 17 │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
17 │ 10 │ 9 │ Name 5 │ │ 5 / Name 4 / 15 │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
18 │ 3 │ 17 │ Name 6 │ │ 5 / Name 11 / 15 │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
19 │ 1 │ 20 │ Name 7 │ │ 7 / Name 2 / 10 │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
20 │ 11 │ 5 │ Name 8 │ │ 7 / Name 9 / 10 │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
21 │ 7 │ 10 │ Name 9 │ │ 7 / Name 10 / 10 │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
22 │ 7 │ 10 │ Name 10 │ │ 10 / Name 5 / 9 │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
23 │ 5 │ 15 │ Name 11 │ │ 11 / Name 8 / 5 │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
24 │ 3 │ 17 │ Name 12 │ │ 12 / Name 1 / 4 │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
25 │ 12 │ 4 │ Name 13 │ │ 12 / Name 13 / 4 │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
26 │ 2 │ 18 │ Name 14 │ │ 14 / Name 3 / 3 │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
27 │ 15 │ 1 │ Name 15 │ │ 15 / Name 15 / 1 │ │
───┼──────┼────────┼─────────┼────┼──────────────────┼────┤
28 │ │ │ │ │ │ │
───┴──────┴────────┴─────────┴────┴──────────────────┴────┘
Benutzte Formeln:
DG13: =RANG(DH13;$DH$13:blush:DH$166)+ZEILE()%
DK13: =KÜRZEN(KKLEINSTE($DG$13:blush:DG$27;ZEILEN($1:1)))&" / "&INDEX($DI:blush:DI;VERGLEICH(KKLEINSTE($DG$13:blush:DG$27;ZEILEN($1:1));$DG:blush:DG;0))&" / "&INDEX(DH:smiley:H;VERGLEICH(KKLEINSTE($DG$13:blush:DG$27;ZEILEN($1:1));$DG:blush:DG;0))

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

anon85587316 · 12. November 2019 um 16:37

Guten Morgen Thomas,

soweit funktioniert die Formel ja schon.Dafür erstmal besten Dank. Da ich zwischenzeitlich aber gelesen habe,dass in der RANG-Funktion doppelte Plätze nicht berücksichtigt werden,d.h die Rangliste nicht fortlaufend durch numeriert wird,bin ich auf einen alten Beitrag von mir gestossen und verstehe nun,was Reinhard damals meinte.Wieder was gelernt .

Habe nun folgende Funktion eingefügt:

Function XRang(rng As Range, dValue As Double) As Integer

Dim iCounter As Integer, iCell As Integer, iLarge As Integer
For iCounter = 1 To rng.Rows.Count
 If iLarge Application.Large(rng, iCounter) Then
 iLarge = Application.Large(rng, iCounter)
 iCell = iCell + 1
 If Application.Large(rng, iCounter) = dValue Then
 XRang = iCell
 Exit Function
 End If
 End If
Next iCounter
End Function

Dementsprechend habe ich natürlich die Formel in Zelle DG13 ff. abgeändert.
=XRANG($DH$13:blush:DH$166;DH13)

Auch soweit noch alles okay. Jetzt habe ich deine Formel mal irgendwo reinkopiert und so weit nach unten gezogen,wie ich sie brauch und in Spalte DG einfach +ZEILE()% hinter meine XRANG-Formal dran gehangen.Mal abgesehen davon,dass hinter jede Platzierung noch die Zeilennummer gesetzt wird habe ich keinen Unterschied gemerkt. Bis Zeile 99 klappt alles prima. Ab Zeile 100 kommt nun das Problem.Excel verschiebt alle Plätz um 1 nach oben.

Wie muss die Formel nun abgeändert werden,da meine Idee Spalte DG einfach auf 3 Nachkommastellen zu formatieren auch nichts gebracht hat.

LG Frank

Thomas_Ramel · 12. November 2019 um 16:37

Grüezi Frank

Du mischst mal wieder bunt die Themen und Beiträge ducheinander, stösst dabei auf Probleme und möchtest diese dann gelöst haben - seufz!!

soweit funktioniert die Formel ja schon.Dafür erstmal besten
Dank. Da ich zwischenzeitlich aber gelesen habe,dass in der
RANG-Funktion doppelte Plätze nicht berücksichtigt werden,d.h
die Rangliste nicht fortlaufend durch numeriert wird

Nunja, was soll die RANG()-Funktion denn auch anderes tun, wenn zwei Ergebnisse identisch sind. Oder wer will festlegen, welcher der beiden Teilnehmer mit gleich vielen Punkten denn nun vor dem anderen liegt?

Meine Formeln berücksichtigen dies alles und arbeiten daher auch korrekt ohne dass Du daran herumdokterst.

Habe nun folgende Funktion eingefügt:

Lass das mal einfach bleiben und folge 1:1 den Schritten die ich oben gezeigt habe.
Die Formeln waren alle auf Zeile 13 abgestimmt, die Du auch in deinem Beispiel verwendet hast - kopiere sie daher auch in Zeile 13, damit alles passt.

Was ich vergass zu erwähnen ist, das Du die Zellen in Spalte DG ohne Nachkommastellen formatieren solltest.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Thomas_Ramel · 12. November 2019 um 16:37

Grüezi Frank

Noch ein Nachtrag - ich hatte nicht im Sinn über wie viele Zeilen deine Berechnungen gehen.

Passe daher das Anhängsel an die RANG()-Formel wie folgt an:

+ZEILE()%% (also 2x % - wenn Du mehr als 10’000 Zeilen hast, dann halt noch ein drittes mal).

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

anon85587316 · 12. November 2019 um 16:38

Huhu Thomas,

nanana Herr Chefarzt,nicht gleich schimpfen

Mal Spaß beiseite,herumgedoktert hab ich schon,bevor ich deine Lösung bekam. Ich bin nämlich auf folgenden Beitrag gestoßen :

http://www.herber.de/mailing/021898h.htm

Nun habe ich alles genauso gemacht,wie du geschrieben hast und dann passiert genau das,was ich vermeiden wollte. Excel sortiert 1,1,3,4,5,5,7 usw.

Deswegen hatte ich schon vorher die XRANG-Funktionen genommen und das +ZEILE()%% mal zu Testzwecken hinten dran gehangen.Im Moment klappt das.Kann ich das denn so lassen?

LG Frank

Thomas_Ramel · 12. November 2019 um 16:38

Grüezi Frank

Nun habe ich alles genauso gemacht,wie du geschrieben hast und
dann passiert genau das,was ich vermeiden wollte. Excel
sortiert 1,1,3,4,5,5,7 usw.

…hmmm, aber was soll denn bei gleichen Punktezahlen mit den Personen geschehen?
Nach welchen Kriterien reihst Du den einen vor dem anderen ein?

…ich würde mich beschweren, wenn ich dieselbe PunkteZahl wie mein Mitspieler habe, aber einen Rang hinter ihm eingestuft werde…

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

anon85587316 · 12. November 2019 um 16:38

Hallo Thomas,

Nun habe ich alles genauso gemacht,wie du geschrieben hast und
dann passiert genau das,was ich vermeiden wollte. Excel
sortiert 1,1,3,4,5,5,7 usw.

…hmmm, aber was soll denn bei gleichen Punktezahlen mit
den Personen geschehen?
Nach welchen Kriterien reihst Du den einen vor dem anderen
ein?

irgendwie versteh ich grad nicht was du meinst. Mit deiner RANG-Funktion bekommen Spieler mit gleicher Punktzahl auch den gleichen Rang. Soweit ist es ja okay.Nur die RANG Funktion vergibt die Ränge ja nicht fortlaufend,was ich aber gerne möchte und momentan mit der XRANG Funktion von Reinhard auch hinbekomme

RANG = 1,1,3,3,5,5 XRANG = 1,1,2,2,3,3 usw.
Leider kann ich keine Tabelle darstellen,das das Makro nicht läuft bei mir.

Hoffe du verstehst jetzt was ich meine und deshalb meine Frage ob ich das +ZEILE()%% einfach an die XRANG Formel dran hängen kann?Bisher habe ich noch keinen Fehler bemerkt,was aber nicht heissen soll,dass da eventuell einer drin steckt.

LG Frank

Thomas_Ramel · 12. November 2019 um 16:38

Grüezi Frank

Nur die RANG Funktion
vergibt die Ränge ja nicht fortlaufend,was ich aber gerne
möchte und momentan mit der XRANG Funktion von Reinhard auch
hinbekomme

RANG = 1,1,3,3,5,5 XRANG = 1,1,2,2,3,3 usw.

Ich verstehe nicht so ganz den Sinn dieser Art der Zählung, habe aber in der Tat nicht erfasst gehabt, dass deine Funktion ebenfalls zwei gleiche Ränge vergibt bei gleichen Punktzahlen.

Aber das kannst Du handhaben wie Du möchtest oder es in deinem/eurem Spiel üblich ist.

Hoffe du verstehst jetzt was ich meine und deshalb meine Frage
ob ich das +ZEILE()%% einfach an die XRANG Formel dran hängen
kann?Bisher habe ich noch keinen Fehler bemerkt,was aber nicht
heissen soll,dass da eventuell einer drin steckt.

Ja, das kannst Du - der Zusatz bewirkt, dass die Zeilennummer als Nachkommastelle an den Rang gehängt wird und es so wieder ein klares Unterscheidungsmerkmal gibt und alle Einzelnen in der gewünschten Reihenfolge ermittelt und aufgelistet werden können.

Sorry für mein Missverständnis - mit etwas mehr umfassender Information wäre es vielleicht nicht dazu gekommen.
Du darfst nicht vergessen, dass für uns jeder einzelne Punkt neu ist, Du dich aber schon länger bis lange damit befasst hast.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

anon85587316 · 12. November 2019 um 16:38

Hallo Thomas,

RANG = 1,1,3,3,5,5 XRANG = 1,1,2,2,3,3 usw.

Normal ist es so das es einen 1.,2.,3.Platz gibt.Mit der RANG Formel würde es aber bei 2 ersten Plätzen keinen 2. Platz geben sondern direkt den 3.Platz. Bei uns weren die Plätze fortlaufend vergeben,also ohne Lücken.Das war eigentlich schon alles.

Ja, das kannst Du - der Zusatz bewirkt, dass die Zeilennummer
als Nachkommastelle an den Rang gehängt wird und es so wieder
ein klares Unterscheidungsmerkmal gibt und alle Einzelnen in
der gewünschten Reihenfolge ermittelt und aufgelistet werden
können.

Supi

Sorry für mein Missverständnis - mit etwas mehr umfassender
Information wäre es vielleicht nicht dazu gekommen.
Du darfst nicht vergessen, dass für uns jeder einzelne Punkt
neu ist, Du dich aber schon länger bis lange damit befasst
hast.

Thomas,ich denke mal,dass es eher an meinem mangelnden Talent lag es vernünftig zu formulieren.Ich muss da sorry sagen.

Warte lieber mal meine nächste Frage ab.Ich weiss schon gar nicht wie ich die formulieren soll um die gewünschten Hinweise auf Beispiele im Netz zu bekommen.

LG Frank