Das grosse Archiv mit 640
VBA-freien Formellösungen und Tips
 
   
 
 ÜBER UNS   NEWS   FORMELN   TIPPS   KONTAKT   FORUM   LINKS 
   
Übersicht   Zauberbuch   Rätselbuch   Sparbuch   Bücher-Downloads / Errata  
 
Statistik (6) ->

Ermittlung des Ranges ohne Platzierungslücken - Variante * (230)
 
 
Aufgabe
Wenn bei der Ermittlung der Ränge einer Zahlungsreihe gleiche Werte vorkommen, ergeben sich mit der Funktion RANG() bei den nachfolgenden Platzierungen Lücken.

Beispiel: die Zahlen 10,10,9 haben den Rang 1,1,3. Das gewünschte Ergebnis ist aber 1,1,2. Wie kann dies erreicht werden ?

       A               B               C       
1   Rang  Ohne  
2 Werte  normal  Lücken  
3 1  
4 2  
5 1  
6 3  
7 3  
8 4  
9

Lösung
Die unsortierten Werte (auch negativ und null - Leerzellen und Texte werden ignoriert) stehen in A3:A9

in C3 steht
{=ISTZAHL(A3)*SUMME(WENN(WENN(ISTZAHL(A$3:A$99)*(ZÄHLENWENN(INDIREKT("A1:A"&ZEILE($3:$99));A$3:A$99)=1);A$3:A$99;MIN(A$3:A$99)-1)>A3;1);1)}
bis C9 runterkopieren

Stehen die Werte in einer Zeile (A1:H1), lautet die Formel in A2:
{=ISTZAHL(A1)*SUMME(WENN(WENN(ISTZAHL($A1:$H1)*(ZÄHLENWENN(INDIREKT("A1:"&ADRESSE(1;SPALTE($A:$H)));$A1:$H1)=1);$A1:$H1;MIN($A1:$H1)-1)>A1;1);1)}
bis H2 kopieren

Variante (kurz und tricky!) von Josef Burch:
{=ANZAHL((A3&"")/HÄUFIGKEIT(WENN(A$3:A$99>=A3;A$3:A$99);A:A))}
Soll die Rangfolge umgekehrt werden (der geringste Wert erhält Rang 1) in der Formel lediglich >= in <= ändern.

Erläuterung
Wie ergibt sich z.B. die 4 in C8 (betrifft die lange Formel)?

Mit ISTZAHL()* am Anfang werden alle eventuellen Texte eliminiert.
Es werden alle Zellen gezählt die größer sind als die 2 in A8, aber nur, wenn sie zum ersten mal vorkommen. die 5 in A5 und die 3 in A7 werden also ignoriert. So bleiben 3 Zellen, die addiert werden. Nun wird noch +1 addiert und das ergibt 4.

=ZÄHLENWENN(INDIREKT("A1:A"&ZEILE($3:$9));A$3:A$9)=1
ist die Bedingung, die prüft, ob die Zahl zum ersten mal vorkommt.

Ist dies nicht der Fall, wird das Duplikat durch
MIN($A$3:$A$9)-1
ersetzt, damit sie auf Alle Fälle kleiner ist, als die zu prüfende Zahl (in dem Fall 2 in A6)

damit ergibt sich
=SUMME(WENN({5;4;0;3;0;2;1}>A8;1))+1
dann
=SUMME({1;1;FALSCH;1;FALSCH;FALSCH;FALSCH})+1
und das ergibt 4
 
    zurück


 
powered by telltarget