Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1500to1504
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Rangfolge sortieren nach zweitem Kriterium

Rangfolge sortieren nach zweitem Kriterium
23.06.2016 00:02:49
Andreas
Hallo,
ich habe eine Frage zur Rangsortierung per Formel (mit der Funktion RANG komme ich nicht weiter).
Ich möchte die Rangfolge ermitteln und zwar folgendermaßen:
In einer Spalte (C) stehen die Tippspielpunkte von Personen deren Namen in Spalte A stehen (in den Zeilen 3 bis 99). In Spalte D steht für jede Person noch ein Wert (eine Zahl).
Ich möchte nun wie folgt den Rang ermitteln: Die Person mit der höchsten Punktzahl in Spalte C soll Rang 1 haben (Ausgabe: "1."), die Person mit der niedrigsten Punktzahl soll Rang 96. haben. Wenn zwei Personen dieselbe Punktzahl haben, soll die Person den besseren (höheren) Rang haben, deren Zahl in Spalte D niedriger ist. Nur wenn bei Punktgleichheit auch die Zahlen in Spalte D identisch sind, soll der gleiche Rang ausgewiesen werden.
Gibt es eine Formel, mit der ich meine Vorstellung umsetzen kann?
Schon einmal Danke für jede Hilfe.
Andreas Landgraf

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Das kann man wie folgt machen, ...
23.06.2016 02:54:31
Luc:-?
…Andreas:
 ABCDEF
3alpha 89922
4beta 791044
5gamma 99611
6delta 34101313
7epsilon 831818
8vau 4131212
9zeta 18101515
10eta 791919
11theta 57599
12iota 7101920
13kappa 4711111
14lambda 74666
15my 1741616
16ny 1741616
17xi 76755
18omikron 87333
19pi 60288
20qoppa 61377
21rho 5591010
22sigma 2531414
23E3[:E22]:=RANG(C3;C$3:C$22)
24F3[:F22]: {=VERGLEICH(C3*100+10-D3;KGRÖSSTE(C$3:C$22*100+10-D$3:D$22;ZEILE(A$1:A$20));0)}
Hierbei muss der jeweilige D-Wert vom Maximum der D-Werte abgezogen wdn. Hier ist es 10. Man kann das aber auch in der MatrixFml berücksichtigen, die dann so aussähe:
F3[:F99]: {=VERGLEICH(C3*10^LÄNGE(MAX(D$3:D$99))+MAX(D$3:D$99)-D3;
KGRÖSSTE(C$3:C$99*10^LÄNGE(MAX(D$3:D$99))+MAX(D$3:D$99)-D$3:D$99;ZEILE(A$1:A$97));0)}
Natürlich ist der letzte Rang = der letzten PositionsNr und somit 97, falls nicht der vorherige Rang =96 (Position in C und D gleich) ist → nur dann kann sie ≤96 sein! D.h. natürlich auch, dass die gleichen Rängen nachfolgenden Ränge entsprd höhere Nrn haben, also Rangstufen fehlen (wie bei RANG und KGRÖSSTE!).
Den Pkt hinter den Rang (hier nicht dargestellt) setzt du am besten per benutzerdefiniertem Format → 0. reicht dafür aus.
Feedback nicht unerwünscht! Morrn, Luc :-?
Besser informiert mit …

Anzeige
AW: Das kann man wie folgt machen, ...
23.06.2016 09:29:55
Landgraf
Hallo Luc,
herzlichen Dank für Deine schnelle Antwort.
Ich habe beides probiert, die Formel in Spalte F in Deinem Bildbeispiel und die Matrixformel, beide Male war das Ergebnis "#Wert"
Meine Formel sieht so aus:
{=VERGLEICH(D2*10^LÄNGE(MAX(AB$2:AB$97))+MAX(AB$2:AB$97)-AB2; KGRÖSSTE(C$2:D$97*10^LÄNGE(MAX(AB$2:AB$97))+MAX(AB$2:AB$97)-AB$2:AB$97;ZEILE(A$2:A$97));0) }
weil in meiner Tabelle tatsächlich
- die Punkte für die Erstrangverteilung in Spalte D stehen, und
- und die Werte für die Hilfsrangverteilung in Spalte AB stehen.
Kannst Du sehen, wo mein Fehler liegt? Kannst Du mir auch kurz erklären, wofür das "^"-Zeichen steht? Und warum man den Befehl ZEILE mit der Spalte A kombiniert (Spalte A stehen bei mir die Namen der Teilnehmer, deren Rang ich ermitteln möchte).
Schönen Tag,
Andreas

Anzeige
AW: Das kann man wie folgt machen, ...
23.06.2016 09:34:19
Landgraf
Habe einen Fehler gefunden in meiner Formel, anstelle von C2 hinter KGRÖSSTE muss es natürlich D2 heißen, aber nun ist das Ergebnis "#ZAHL".

^ heißt hoch, also Potenzierung, und ...
23.06.2016 12:34:12
Luc:-?
…ich hatte ZEILE(A$1:A$97) geschrieben, Andreas,
weil das der RangZähler für von dir angegebene 97 Positionen, ZEILEN(D3:D99)=97, ist. Wenn du den, ohne das Prinzip verstanden zu haben, so änderst, erhältst du die Ränge 2 bis 97!
Also entweder sollten die von dir gemachten Angaben mit der Realität übereinstimmen oder du solltest eine Fml sinngemäß anpassen können…
Luc :-?

AW: ^ heißt hoch, also Potenzierung, und ...
23.06.2016 13:54:06
Landgraf
Hallo Luc,
Danke für die Erklärung, soweit verstanden.
Ich dachte, ich hätte die Formel sinngemäß angepasst.
Zum Gegentest habe ich nun Dein Beispiel mal in Excel kopiert und Deine Formel in F3:
=VERGLEICH(C3*100+10-D3;KGRÖSSTE(C$3:C$22*100+10-D$3:D$22;ZEILE(A$1:A$20));0)
bringt bei mir auch nur das Ergebnis "#NV". Dasselbe bei Nutzung der Matrixformel.
Kannst Du bitte nochmal schauen, ob Deine Beispielformel in Deinem Beispiel funktioniert?
Danke,
Andreas
PS: habe eine Excel-Datei mit Deinem Beispiel und meinen Versuchen beigefügt.
https://www.herber.de/bbs/user/106460.xlsx

Anzeige
Wie gibt man eine MatrixFml ein, ...
23.06.2016 14:48:07
Luc:-?
…Andreas?
Fml in die Zelle schreiben und mit [Strg][Umsch][Enter] abschließen*! Hier handelt es sich um singulare (1zellige) MatrixFmln → also die 1.Fml eintragen und dann einfach nach unten kopieren! Prompt fktioniert es auch (mit beiden Fmln → es handelt sich ja auch nicht um dein, sondern mein Bsp)!
* Das ist etwas, was man beherrschen muss, will man in Xl wirklich gut sein!
Luc :-?

AW: Wie gibt man eine MatrixFml ein, ...
23.06.2016 15:25:40
Landgraf
Hallo Luc,
wieder was gelernt. Funktioniert in Deinem Beispiel bestens.
Wenn ich mich nicht mehr melde, habe ich es auch mit den logischen Änderungen in meiner Tabelle hinbekommen.
Vielen Dank für Deine Geduld.
Ich werde zukünftig "Basiskenntnisse in Excel" angeben, wenn ich mal wieder eine Frage habe.
Eine schöne Restwoche,
Andreas

Anzeige
Na, denn iss ja jut! ;-) owT
23.06.2016 23:45:00
Luc:-?
:-?

vielleicht viel einfacher mit Hilfsspalte
23.06.2016 14:10:29
IngoG
Hallo Zusammen,
wenn man eine Hilfsspalte mit der Formel =B3+1/C3 zB in zelle X3 einbaut und nach unten kopiert,
sollte wieder eine einfache Rang-Formel mit =rang(X3,$X$3:$X$100) funktionieren...
Voraussetzung:c3:c100 grösser 0 bei evtl c3=0 einfach X3 =B3+1/(1+C3)
Vielleicht hilft dir das weiter
Gruß Ingo

AW: vielleicht viel einfacher mit Hilfsspalte
23.06.2016 14:20:48
Landgraf
Hallo Ingo,
wenn Du B schreibst, meinst Du dann D? Die Spalte B ist in dem Beispiel doch leer.
Die Idee ist, eine Ranggleichheit zu vermeiden, indem man ausschließlich bei Punktgleichheit einen weiteren Wert betrachtet.
Im richtigen Tippspiel habe ich alle Teilnehmer (96) schätzen lassen, wieviele Tore bei der EM insgesamt fallen werden (der Fall, dass zwei oder mehrere Spieler punktgleich sind und auch noch denselben Wert geschätzt haben, ist eher gering - aber auch schon vorgekommen). Dann errechne ich die Abweichung des geschätzten Werten mit den tatsächlich erzielten Toren, und dieser errechnete Wert wird dann für die Platzierung bei Punktgleichheit herangezogen, d.h. bei punktgleichen Spielern soll derjenige am höchsten plaziert werden, dessen Abweichungswert am geringsten ist.
Bisher habe ich Tabellen mit Makros sortiert, aber nun möchte ich eine Spalte haben, die ohne Makrosortierung den Rang korrekt ausrechnet.
Gruß,
Andreas

Anzeige
AW: vielleicht viel einfacher mit Hilfsspalte
23.06.2016 14:39:48
IngoG
Hallo Andreas,
also wenn Deine Daten folgendermaßen aussehen:
D3:D97 enthält Punkte der Mitspieler
E3:E97 die Abweichung der Torschätzung, also Wert grösser oder gleich 0
dann schreibst Du in das Hilfsfeld X3 =D3+1/(1+E3) und in das Feld Y3 =Rang(X3;$X$3:$X$97)
die beiden Formeln kopierst Du dann nach unten bis in die Zeile X97:Y97
Fertig
Gruß Ingo

AW: vielleicht viel einfacher mit Hilfsspalte
23.06.2016 15:09:08
Landgraf
verstanden, Danke Ingo,
Gruß,
Andreas

AW: Rangfolge sortieren nach zweitem Kriterium
23.06.2016 14:11:32
Daniel
Hi
hier mal eine Lösung mit einfachen Formeln und Hilfsspalten
a) für den Fall, dass es sich bei beiden Werten um positive Ganzzahlen handelt:
füge in Spalte E noch eine Hilfsspalte ein, ein welcher du zum Wert in C noch den Kehrwert von D hinzuaddierst, also
=C3+1/D3
bilde dann den Rang in dieser Hilfsspalte.
b) wenn Kommazahlen im Spiel sind dann so:
1. Hilfsspalte: Rang mit den Werten aus Spalte C (höchster Wert = 1)
2. Hilfsspalte: Rang mit den Werten aus Spalte D (kleinster Wert = 1)
3. Hilfsspalte: Teile den Wert der zweiten Hilfsspalte durch 100 und addiere den Wert der ersten Hilfsspalte hinzu.
4. Ergebnisspalte: Ermittle den Rang für die 3. Hilfsspalte (kleinster Wert = 1)
Gruß Daniel

Anzeige
AW: Rangfolge sortieren nach zweitem Kriterium
23.06.2016 14:32:52
Landgraf
Hallo Daniel,
sieht aus, als wenn dies gut funktioniert.
Danke.
Andreas

301 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige