Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1464to1468
1464to1468
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Rang ohne doppelte Werte (bei mehreren Spalten!)

Forumthread: Rang ohne doppelte Werte (bei mehreren Spalten!)

Rang ohne doppelte Werte (bei mehreren Spalten!)
18.12.2015 15:20:46
Joerschi
Hallo liebes Forum,
leider komme ich nicht weiter (oder übersehe das Offensichtliche...).
Ich möchte eine Rangfolge ohne doppelte Werte herbeiführen, wobei sich die
Ausgangswerte über mehrere Spalten erstrecken.
Skizze + Musterdatei:
Userbild
https://www.herber.de/bbs/user/102351.xlsx
Die Musterlösung für ein Ranking ist in den gelben Feldern eingezeichnet
(höchste Zahl = Rang Nr. 1, zweithöchste Zahl = Rang 2 usw.). Die gelben Felder sollen per Formel ausgerechnet werden.
Problem im Beispiel ist die Zahl 7, welche aufeinanderfolgende Rangwerte bekommen soll (wobei es mir für die Lösung egal ist, welche "7" den Rang 3 bis 5 erhält).
Als Basis dafür wollte ich eine adaptierte Formel für die Zelle C1 von hier verwenden (http://www.excelformeln.de/formeln.html?welcher=307), aber leider bekomme ich es nicht hin. :-(
Hätte jemand eine Idee, wie das umzusetzen wäre?
Viele Grüße
Joerschi

Anzeige

22
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: z.B. mit SUMMENPRODUKT() ....
18.12.2015 15:40:26
der
Hallo Joerschi,
... Formel nach rechts und unten ziehend kopieren:
 ABCDEFG
1Zahlenwerte      
21174 148
37411 592
4274 12610
5683 7311
6       

Formeln der Tabelle
ZelleFormel
E2=WENN((ZEILE(A1)>ANZAHL(A:A))+(SPALTE(A1)>ANZAHL($A2:$D2)); "";(SUMMENPRODUKT((($A$2:$D$9-ZEILE($A$2:$D$9)%%-SPALTE($A$2:$D$9)%%%%)>A2-ZEILE(A2)%%-SPALTE(A2)%%%)*1)))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
zu kompliziert: da doch wohl lieber 2 Formeln
18.12.2015 16:25:54
WF
Hi,
in D2:
=RANG(A2;$A$2:$B$5)+ZÄHLENWENN(A$2:A2;A2)-1
bis D5 kopieren
in E2:
=RANG(B2;$A$2:$B$5)+ZÄHLENWENN(B$2:B2;B2)-1+ZÄHLENWENN(A$2:A$5;B2)
bis E5 kopieren
WF

AW: was ist daran zu kompliziert? owT
18.12.2015 16:32:42
der
Gruß Werner
.. , - ...

frag nicht mich sondern Joerschi
18.12.2015 16:38:15
WF
.

Anzeige
AW: er hat schon komplzierte Formeln erhalten ...
18.12.2015 16:42:11
der
Hallo WF,
... und auch verstanden. Aber nun hat Joerschi ja hier die Wahl.
Gruß Werner
.. , - ...

Danke an neopa & WF
18.12.2015 17:19:18
Joerschi
Hallo Werner & WF,
vielen vielen Dank für Eure Lösungen.
Es ist in der Tat so: Werners Lösung ist auf den ersten Blick sehr anspruchsvoll (und wenn ich mir´s "leicht" machen will, kopiere ich sie einfach und passe lediglich die Zellen an), WF´s Formel eher was für´s Auge und zum nachhaltigen Verstehen (dafür aber eine zweiformelige Lösung).
So richtig entschieden habe ich mich aber noch nicht :-)
Nochmals Dank und vorab einen schönen 4. Advent!
Joerschi
PS: Während des Schreibens habe ich grad ein bisschen probiert und werde wohl vorerst WF´s Formel verwenden.
Hauptgrund ist, dass man auch in den Ausgangsdaten Leerzellen (statt Zahlenwerte) haben kann und das Ergebnisranking wird davon nicht beeinträchtigt (bzw. zeigt #NV, was via WENNFEHLER aber leicht zu beheben ist).
Bei der großen Formel werden im Ergebnis zusätzliche Leerzellen bei den Rankings produziert.
Allerdings muss ich betonen, dass das mein Fehler ist, da neopa´s Formel sonst sehr gut funktioniert und nix dafür kann, weil ich diese Möglichkeit (Leerzelle in Ausgangsdaten) in der Aufgabenstellung schlicht nicht erwähnt hatte.
PPS: Doch noch einen Bug in Deiner Berechnung entdeckt @ Werner:
Wenn man die Ausgangsdaten z.B. weiter mit dem Wert 7 auffüllt, wird der Rang 4 und 6 doppelt ausgegeben
Userbild

Anzeige
AW: Danke an neopa & WF
18.12.2015 18:33:08
Josef
Hallo
Ich denke nachfolgende Formel behebt diese genannten Fehler.
Gruss Sepp
Tabelle1

 ABCDEFG
1Zahlenwerte      
211114 128
37411 593
4274 11610
568  74 
6       

Formeln der Tabelle
ZelleFormel
E2=WENN(A2=""; "";SUMMENPRODUKT(--($A$2:$C$6-ZEILE($A$2:$C$6)%%-SPALTE($A$2:$C$6)%%%%>A2-ZEILE(A2)%%-SPALTE(A2)%%%%))+1)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Anzeige
Dankeschön - das tut sie :-)
18.12.2015 20:48:30
Joerschi
Hallo Sepp,
vielen Dank.
Die Neufassung klappt bestens!
Viele Grüße
Joerschi

Betreff
18.12.2015 20:53:43
WF
Du reparierst die Formel von neopa und Deine Überschrift lautet:
"AW: Danke an neopa & WF"
Diesen Beitrag öffnet jeder !
WF

AW: da hatte ich wohl ein % "verschluckt" ;-) ...
19.12.2015 07:54:14
der
Hallo Joerschi,
... aber an die Möglichkeit leerer Datenzellen nicht gedacht, weil Du sonst derartige Ausnahmen immer mit angegeben hast. Aber Sepp hat ja dankenswerterweise repariert.
Gruß Werner
.. , - ...

Anzeige
AW: da hatte ich wohl ein % "verschluckt" ;-) ...
19.12.2015 12:05:52
Joerschi
Hi Werner,
ja, da hatte ich mich auch ein wenig geärgert.
Aber oft ist es so: Man sucht eine Lösung, bekommt sie und adaptiert sie erfolgreich und plötzlich ergeben sich aus der Lösung (weils nun endlich richtig rechnet) wieder neue Probleme, die man noch nicht vorhergesehen hatte.
Schlimmer Excel-Kreislauf :-)
Viele Grüße
Joerschi

Anzeige
Nachzügler
20.12.2015 21:28:43
Besserwisser
Hallo,
hier noch ein Nachzügler:
E2: =WENNFEHLER(RANG.GLEICH(A2;$A$2:$C$5)+ZÄHLENWENN($A$1:$C1;A2)+ZÄHLENWENN($A2:A2;A2)-1;"")
Viele Grüße
Christian N.

prima, das funktioniert
21.12.2015 06:38:04
WF
Hi,
... natürlich nur, wenn Zeile 1 nicht zum Auswertungsbereich gehört - was hier aber ja der Fall ist.
Und RANG (solo) reicht - was RANG.GLEICH bewirken soll, hab ich noch nicht geschnallt.
WF

Anzeige
AW: Deine Annahme ist nicht notwendig ...
21.12.2015 09:36:12
...
Hallo WF,
... soweit ich das jetzt auf die Schnelle erfasst habe kannst man ja dann auch (wenn in Zeile 1 nicht auszuwertende Zahlen stehen) schreiben:
=WENNFEHLER(RANG.GLEICH(A2;$A$2:$C$5)+ZÄHLENWENN($A$2:$C2;A2)-1+ZÄHLENWENN($A2:A2;A2)-1;"")
Gruß Werner
.. , - ...

Anzeige
AW: Funktion RANG.GLEICH() ...
21.12.2015 09:32:37
...
Hallo Christian,
... diese Funktion war für mich bisher "unbekannt". Durch Deinen Beitrag bin ich jetzt auf diese aufmerksam geworden und sie ist durchaus merkenswert.
Danke für Deinen Beitrag.
Gruß Werner
.. , - ...

wozu RANG.GLEICH - RANG langt
21.12.2015 12:02:56
WF
Hi,
die Formel, die Du oben gebracht hast, funktioniert genauso mit RANG:
=WENNFEHLER(RANG(A2;$A$2:$C$5)+ZÄHLENWENN($A$2:$C2;A2)-1+ZÄHLENWENN($A2:A2;A2)-1;"")
WF

Anzeige
AW: ja, RANG() ist ausreichend ...
21.12.2015 14:47:30
...
Hallo WF,
... ich hatte heute Morgen auf die Schnelle (wie ich es auch in meinem Beitrag von 9:36 an Dich zum Ausdruck brachte) die Online-Hilfe nicht genau genug gelesen und deshalb mehr in diese hinein interpretiert, als wie vorhanden.
Offensichtlich scheint es wirklich nur eine neuer Name für die gleiche Funktionalität zu sein. Ich kann jetzt jedenfalls auch keinen Unterschied zwischen den beiden Funktionen erkennen.
Gruß Werner
.. , - ...

Anzeige
AW: Funktion RANG.GLEICH() ...
21.12.2015 12:11:19
Besserwisser
Hallo Zusammen,
natürlich funktioniert die Formel auch mit Rang. Ich hab mir nur angewöhnt, immer die aktuellen Funktionen aus Excel zu verwenden (Rang steht aus Kompatibilitätsgründen in Excel weiterhin zur Verfügung).
Gruß
Christian N.

nix Kompatibilitätsgrund
21.12.2015 14:37:54
WF
Hi,
in der online-Hilfe gibt es sowohl RANG als auch RANG.GLEICH.
Da steht nicht "... nur aus Gründen der Kompatibilität ..."
Zudem sind beide Beschreibungen unterschiedlich.
Ich hab bisher aber keinen Unterschied feststellen können?
WF

Anzeige
Doch: Kompatibilitätsgrund
21.12.2015 20:35:56
Besserwisser
Hallo Zusammen,
RANG (Funktion)
In diesem Artikel werden die Formelsyntax und die Verwendung der Funktion RANG in Microsoft Excel beschrieben.
Beschreibung
Gibt den Rang zurück, den eine Zahl innerhalb einer Liste von Zahlen einnimmt. Als Rang einer Zahl wird deren Größe, bezogen auf die anderen Werte der jeweiligen Liste, bezeichnet. (Wenn Sie die Liste sortieren würden, würde die Rangzahl der Zahl deren Position angeben.)
WICHTIG Diese Funktion wurde durch eine oder mehrere neue Funktionen ersetzt, die ggf. eine höhere Genauigkeit bieten und deren Namen die Verwendung besser wiedergeben. Obwohl diese Funktion aus Gründen der Abwärtskompatibilität weiterhin verfügbar ist, sollten Sie von nun an die neuen Funktionen verwenden, da die alte Funktion in zukünftigen Versionen von Excel möglicherweise nicht mehr verfügbar sein wird.
Weitere Informationen zu den neuen Funktionen finden Sie unter RANG.MITTELW (Funktion) und RANG.GLEICH (Funktion).
Gruß
Christian N.

Anzeige
STIMMT - mea maxima culpa
21.12.2015 20:55:38
WF
Hi,
ich hatte diesbetr. nur die Hinweise gelesen und nicht die Beschreibung.
WF

AW: Nachzügler
21.12.2015 09:35:25
Joerschi
Hi Christian,
auch von mir ein herzliches Danke.
Kurz und knackig die Formel. Gefällt :-)
Liebe Grüße
joerschi

Forumthreads zu verwandten Themen

Variable Gültigkeitsliste ohne doppelte Werte
Susi

Hallo liebe Excel-Freunde,
ich möchte gerne aus einer Datentabelle den Inhalt einer Spalte für eine Auswertung über eine Variable Gültigkeitsliste ziehen.
Zur Verdeutlichung hier ein einfaches Spiel-Beispiel:
Spalte A Spalte B
Äpfel 10
Äpfel 20
Birnen...
28.11.2008 10:08:27
Thread-ID: 1028603
5 Beiträge
kkleinste ohne doppelte werte
gisela

Hallo zusammen,
nach einigen Stunden Recherche benötige ich noch mal Eure Hilfe.
Verschiedene Datumsangaben (siehe Datei) sollen aufsteigend ohne doppelte Werte angezeigt werden.
Meine Formel funktioniert nur bedingt. Irgendetwas stimmt nicht.
Kann mir bitte jemand helfen?
h...
31.01.2009 23:47:11
Thread-ID: 1045792
2 Beiträge
Anzeige
Range aus mehreren Zellbereichen als Variabeln
Randolf

Hallo,
ich möchte folgenden Zeile mit Variabeln darstellen:
Range("AM65:AN65,BB65:BC65").Select folgenden Ansatz habe ich derzeit, aber mir mach der Doppelpunkt zu schaffen:
Range(Cells(i, 39), Cells(i, 40), Cells(i, 54), Cells(i, 55)).Select
versuche wie Range(Cells(i, 3...
08.05.2009 11:06:07
Thread-ID: 1073827
13 Beiträge
Gültigkeite - Liste ohne doppelte Werte
susi

Hallo,
ich habe eine kleines, eher optisch unschönes Problem. In einer Tabelle wird von den Nutzern auf dem ersten TB eine Liste mit Werte al la ABC1000; ABC1010 ... eingetragen. Nun können sich aber diese Werte auch wiederholen. Auf einem zweiten TB habe ich eine Spalte die sich über DATEN...
13.08.2009 07:57:25
Thread-ID: 1095462
2 Beiträge
Anzeige
VBA Hilfe- max. Wert bei gleichen Vergleichswerten
Peter

Hallo,
den auch mehrfach vorkommenden Werten in Spalte C sind in Spalte K
Datumswerte zugeordnet.
Bei mehrfach vorkommenden Werten soll nur der Maximalwert in Spalte A
Kopiert werden.
Falls die Erklärung verwirrend ist siehe Anlage. Die gelb hinterlegten
Zellen sind das...
05.09.2009 02:46:12
Thread-ID: 1100739
2 Beiträge
Zählen von Zellen ohne doppelte / mehrfache Werte | Herbers Excel-Forum
Julia
Hey Leute,
ich habe eine vorgefertigte Excel-Datei bekommen und muss in einem neuen Tabellenblatt die Anzahl der Daten zählen.
Die Daten befinden sich auf dem Tabellenblatt "VZ-KVs" im Bereich von "Z10S2:Z1009S2" (ist in einer Spalte).
Bei den Daten handelt es sich um Text mit Zahlen u...
08.02.2010 20:29:07
Thread-ID: 1136852
6 Beiträge
Liste ohne doppelte Werte
ing.grohn

Hallo Forum,
mit Daten Gültigkeit kann ich verhindern, daß doppelte Eintragungen vorkommen:
Einstellungen Benutzerdefiniert und dann z.B =UND(ISTZAHL(H1);ZÄHLENWENN($H$1:$H$30;H1)
ich habe hierzu ein Makro aufgezeichnet: With Selection.Validation .Delete .Add Type:=xlValidateCustom...
18.10.2010 19:34:20
Thread-ID: 1182959
4 Beiträge
CMB ohne doppelte Werte
Heike

Hallo liebes Forum,
wie kann ich 2 CMB ohne doppelte Werte anzeigen lassen? Mit einer CMB klappt es!
Also, ich habe in meiner Userform 2 Frames erstellt mit je einer ComboBox.
In Frame1 wird die CMB1 mit folgendem Code ohne doppelte Einträge angezeigt:
Dim lIndxA As Long...
10.05.2013 23:10:44
Thread-ID: 1312526
8 Beiträge
Bei gleichen Werten nur den Letzten behalten
godLiTE

Hi,
ich habe eine Liste in der durch ein Export zu einem Artikel mehrere Zeilen entstanden sind(sehr oft mehr als 2).
Ich benötige aber immer nur die letzt Zeile.
Ich habe es mit der bedingten Formatierung versucht um mir mal einen Überblick zu schaffen - es betrifft mehr als 1000 Z...
09.08.2013 12:57:19
Thread-ID: 1326304
3 Beiträge
Rang ohne doppelte werte
Walter
Hallo Excelfreunde
Ich habe eine Tabelle (Torschützen), in Spalt "f" Anzahl der Tore
In Spalte "B" steht der Rang. 1,2,2,2,3,3,3
Es sollte so aussehen 1,2, , , 3 , , , ,4 , , ,
Gibt es hier eine Lösung?
Danke für die Hilfe
GrußWalter
30.06.2014 17:19:51
Thread-ID: 1368257
2 Beiträge
Inhalt einer Spalte zählen ohne doppelte werte
Andre

Hallo Liebe Forum Mitglieder!,
irgendwie stehe ich auf dem Schlauch. Ich habe eine Tabelle
Spalte 1 Zeigt mir die Tage an wann was gearbeitet wurde
Spalte 2 Zeigt mir eine Leistungsposition an
Spalte 3 Zeigt mir die Straße an
nun möchte ich das in Spalte 1 die Tage zusammeng...
10.04.2015 07:10:17
Thread-ID: 1420074
14 Beiträge
Sverweis bei gleichen bzw. größeren Werten
Rick

Hallo,
ich habe bisher alles selber oder mit Hilfe dieses Forums geschafft. Diesmal weiß ich allerdings nicht weiter und konnte auch keinen passenden Beitrag finden.
Gesucht ist die optimale Verpackung für stangenförmige Produkte. Zur Auswahl stehen Standardbehälter mit verschiedenen Du...
26.06.2015 16:57:43
Thread-ID: 1433387
8 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Rangfolge ohne doppelte Werte in Excel erstellen

Schritt-für-Schritt-Anleitung

Um in Excel eine Rangfolge ohne doppelte Werte zu erstellen, kannst Du die Funktion RANG.GLEICH oder RANG verwenden. Hier ist eine Schritt-für-Schritt-Anleitung:

  1. Daten eingeben: Lege Deine Daten in mehreren Spalten an. Zum Beispiel in den Zellen A1 bis C5.
  2. Formel in die Zielzelle einfügen: Wähle die Zelle aus, in der Du den Rang anzeigen möchtest, z.B. D2.
  3. Formel eingeben: Gib die folgende Formel ein: 
    =WENNFEHLER(RANG.GLEICH(A2; $A$2:$C$5) + ZÄHLENWENN($A$2:A2; A2) - 1; "")
  4. Formel kopieren: Ziehe die Formel nach unten, um den Rang für alle Werte zu berechnen.

Diese Methode erzeugt den Rang für die Werte in Spalte A und berücksichtigt dabei die Werte in mehreren Bereichen.

Häufige Fehler und Lösungen

  • Fehler: Rangwerte erscheinen doppelt.

    • Lösung: Stelle sicher, dass Du die Zählung der doppelten Werte korrekt in Deiner Formel berücksichtigst, wie in der Schritt-für-Schritt-Anleitung beschrieben.

  • Fehler: Die Formel zeigt #NV für leere Zellen an.

    • Lösung: Verwende die Funktion WENNFEHLER, um Fehler zu ignorieren.

Alternative Methoden

Es gibt verschiedene Methoden, um den Rang in Excel ohne doppelte Werte zu berechnen:

  1. SUMMENPRODUKT:

    =SUMMENPRODUKT(($A$2:$C$5 > A2) * 1)

    Diese Methode zählt die Werte, die größer sind als der aktuelle Wert.

  2. Zwei Formeln: Du kannst auch zwei separate Formeln verwenden, wie in den Beiträgen von WF beschrieben:

    • Für die erste Spalte: 
      =RANG(A2; $A$2:$B$5) + ZÄHLENWENN(A$2:A2; A2) - 1
    • Für die zweite Spalte: 
      =RANG(B2; $A$2:$B$5) + ZÄHLENWENN(B$2:B2; B2) - 1 + ZÄHLENWENN(A$2:A$5; B2)

Praktische Beispiele

Hier ist ein einfaches Beispiel zur Anwendung der Formeln:

A B C Rang
11 7 4 1
7 4 11 2
2 7 4 3
6 8 3 4

Die Rangwerte werden in einer zusätzlichen Spalte berechnet, um die Informationen klar darzustellen.

Tipps für Profis

  • Um die Funktion RANG.GLEICH sinnvoll zu nutzen, solltest Du die verschiedenen Bereiche genau definieren.
  • Berücksichtige, dass die Funktion RANG vor allem für einfache Ranglisten verwendet wird, während RANG.GLEICH für komplexere Berechnungen von Vorteil ist.
  • Achte darauf, die Formeln stets auf die aktuellen Datenbereiche zu beziehen, um die Genauigkeit zu gewährleisten.

FAQ: Häufige Fragen

1. Wie funktioniert die Funktion RANG.GLEICH?
RANG.GLEICH gibt den Rang eines Wertes in Bezug auf eine Liste zurück und berücksichtigt dabei gleiche Werte.

2. Kann ich auch leere Zellen in meinen Daten haben?
Ja, mit der Verwendung von WENNFEHLER kannst Du sicherstellen, dass leere Zellen korrekt behandelt werden.

3. Wie gehe ich mit gleichen Werten um?
Wenn Du gleiche Werte hast, kannst Du die Zählung in Deine Rangformel integrieren, um doppelten Rängen vorzubeugen.

4. Ist es möglich, RANG ohne doppelten Werte zu verwenden?
Ja, die Verwendung von RANG zusammen mit ZÄHLENWENN ermöglicht es Dir, eine Rangliste ohne doppelte Werte zu erstellen.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige