Automatische Sortierung (Teil 3) 25

Wie man Wertelisten automatisch sortieren kann - auch bei identischen Werten
 

Vor geraumer Zeit habe ich hier gezeigt, wie man eine Werteliste mit Hilfe der Funktionen KGRÖSSTE (bzw. KKLEINSTE) automatisch sortieren kann.

Die vorgestellte Lösung hatte allerdings einen Schönheitsfehler: Sobald die Liste mehrere gleich große Werte enthält, klappt die Sortierung nicht mehr zuverlässig.

Wie man trotzdem die automatische Sortierung hinbekommt, erkläre ich im heutigen Artikel. Die verwendeten Funktionen:

  • KGRÖSSTE
  • ZUFALLSZAHL
  • ZEILE
  • INDEX
  • VERGLEICH
  • ZEILEN

Und so geht’s:

Die Ausgangslage

Ich habe eine unsortierte Liste mit Städten und deren Einwohnerzahlen, die ich an anderer Stelle automatisch nach Größe sortiert ausgeben möchte (die Beispielliste kannst du hier herunterladen):

Die Ausgangslage

Die Ausgangslage

Links befindet sich die unsortierte Liste, rechts davon die mit Hilfe von KGRÖSSTE sortierte Version. Zur Erinnerung: Die KGRÖSSTE-Funktion gibt den k-größten Wert einer Liste aus:
=KGRÖSSTE(Liste; k)

Wer es etwas genauer wissen möchte, kann hier das Beispiel nochmal nachlesen.

Bei näherer Betrachtung sieht man, dass es in meiner unsortierten Liste zwei Städte mit exakt gleicher Einwohnerzahl gibt: Istanbul und Fantasia-City. Und das führt in der rechten sortierten Liste zu dem unschönen Ergebnis, dass Istanbul doppelt angezeigt wird:

Fehlerhaftes Ergebnis

Fehlerhaftes Ergebnis

Wodurch kommt das?

Wie man in der Bearbeitungszeile sieht, wird die Stadt mit Hilfe einer Kombination aus INDEX und VERGLEICH ermittelt. Vereinfacht gesagt sucht diese Formel nach der Einwohnerzahl und zeigt dann die erste gefundene Stadt mit genau dieser Einwohnerzahl an. Und das ist natürlich Istanbul.

Wie löst man dieses Problem?

Variante 1: Der Zufall hilft

Ein Leser hatte mir diese Lösung geschickt (Danke an Herrn Hoffmann an dieser Stelle). Der Gedankengang ist sehr einfach: Man muss nur dafür sorgen, dass es keine identischen Werte gibt. Dies lässt sich über eine Hilfsspalte realisieren, in der zur Einwohnerzahl eine Zufallszahl addiert wird:

Der Zufall hilft

Der Zufall hilft

Die Funktion ZUFALLSZAHL liefert einen mehr oder weniger beliebigen Wert zwischen Null und Eins. Da uns bereits ein kleiner Unterschied genügt, dividieren wir die Zufallszahl noch durch 1000. Dies wäre bei Einwohnerzahlen zwar nicht nötig, aber wenn es sich bei der Werteliste z.B. um Beträge mit Nachkommastellen handelt, macht es Sinn. Schließlich wollen wir die Beträge an sich ja nicht verändern.

Damit haben wir eine funktionierende Lösung:

Lösung 1: Hilfsspalte mit Zufallszahl

Lösung 1: Hilfsspalte mit Zufallszahl

Die tatsächliche Einwohnerzahl holen wir uns über die INDEX-/VERGLEICH-Kombination.

Variante 2: Die Zeilennummer

Die Variante 1 funktioniert grundsätzlich sehr gut, hat lediglich einen kleinen Nachteil: Die verwendete Funktion ZUFALLSZAHL berechnet permanent neue Werte, sobald man irgendwo in der Tabelle einen Wert ändert (<Klugscheißmodus an> Es handelt sich um eine sogenannte volatile Funktion – dazu werde ich nächste Woche etwas mehr erzählen <Klugscheißmodus aus>)

Was dazu führt, dass die Hilfsspalte ständig “in Bewegung” ist und die Tabelle damit etwas unruhig wirkt. Daher habe ich mir eine leicht abgewandelte Version überlegt.

Ebenfalls in einer Hilfsspalte setzte ich anstelle der ZUFALLSZAHL-Funktion die ZEILE-Funktion ein. Diese ermittelt lediglich die Zeilennummer der jeweils aktuellen Zelle. Damit sich das Ganze im hinteren Nachkommabereich abspielt, habe ich die Zeilennummer dann wieder durch eine große Zahl dividiert:

Hilfsspalte mit der ZEILE-Funktion

Hilfsspalte mit der ZEILE-Funktion

Da in jeder Zeile nur eine Einwohnerzahl steht, bekomme ich wieder eindeutige Werte (ok, in extrem unwahrscheinlichen Ausnahmefällen kann es auch hier zu identischen Werte kommen). Und damit ergibt sich folgendes Lösungsbild:

Lösungsvariante 2

Lösungsvariante 2

Wie man sieht, führen in Excel fast immer verschiedene Wege zur Lösung. Wenn dir noch eine weitere einfällt, lass es uns doch unten in den Kommentaren wissen.

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

25 Gedanken zu “Automatische Sortierung (Teil 3)

  • Gerhard Pundt

    Hallo Martin,

    wieder ein interessanter Beitrag. Den Weg mit der Zeilennummer finde ich schon sehr gut. Da kann es wohl gar nicht zu Dopplungen kommen.

    Zur Ergänzung noch eine Idee von mir:
    =B3+(LÄNGE(A3)/10000)
    Die Wahrscheinlichkeit, dass Einwohnerzahl und Wortlänge identisch sind, ist, glaube ich, gering.

    Viele Grüße
    Gerhard

    • Martin Weiß Beitragsautor

      Hallo Gerhard,

      die Ergänzung von Dir mit der Wortlänge ist eine sehr gute Idee! Und wenn man das noch mit der Zeilennummer kombiniert, ist die Wahrscheinlichkeit für identische Werte so groß, wie bei einem Lotto-Sechser vom Blitz erschlagen zu werden 🙂

      Schöne Grüße,
      Martin

  • Patrick

    Hallo Martin,

    die Formel KKLEINSTE hilft mir schon sehr weiter! Ich stehe allerdings vor dem Problem, dass ich verschiedene Zeilen nach Datum ordnen muss. Hier funktioniert leider weder Zufallszahl noch Zeile.

    Hast Du vielleicht noch einen anderen Tipp auf Lager, wie ich verschiedene Einträge automatisch ohne Makro nach Datum ordnen kann?

    Danke und viele Grüße
    Patrick

    • Martin Weiß Beitragsautor

      Hallo Patrick,

      mit dem Datum sollte es eigentlich genauso funktionieren, da Excel-intern ein Datum auch nur eine fortlaufende Zahl ist.

      Schöne Grüße,
      Martin

  • Marco

    Hallo Martin,

    also ich nutze für automatische Sortierung immer die formel RANG.GLEICH (bei früheren Excel Versionen nur RANG).

    =RANG.GLEICH(Zahl;Bezug;Reihenfolge), wobei ich über den Parameter der Reihenfolge mit der gleichen Formel vorgeben kann, ob ich auf- oder absteigend sortieren möchte.

    Mein Vorgehen:
    Vor die unsortierte Tabelle füge ich eine Hilfsspalte ein, in die die RANG-Formel geschrieben wird.
    Die erste Spalte der sortierten Tabelle ist wieder eine Hilfsspalte in der nur die Zahlen 1,2,3,4… etc. stehen.
    Anschließend sortiere ich die Elemente der unsortierten Tabelle über Sverweis.
    Die Hilfsspalten werden abschließend noch ausgeblendet.

    Ich hoffe, dass ich meine Vorgehensweise einigermaßen verständlich beschrieben habe.

    Viele Grüße Marco

    p.s. auch bei dieser Formel hat man das oben beschriebene Problem mit zwei gleichen Werten, welches ich auch über die 2. Variante mit der Zeilennummer löse.

    • Martin Weiß Beitragsautor

      Hallo Marco,

      vielen Dank für den Hinweis auf die Funktion RANG.GLEICH, das ist ein guter Tipp!

      Schöne Grüße,
      Martin

  • Maik

    Hallo Martin,

    dort, wo die Sortierfunktion von Excel versagt, kommt man mit Formeln tatsächlich ein wenig weiter – und dazu ist jetzt sogar alles dynamisch 🙂
    Viele Dank für deine Anleitung! Du ahnst gar nicht, wie sehr du mir geholfen hast!

    Beste Grüße
    Maik

  • Alex

    Hallo Martin,

    ein sehr interessantes Thema welches hier behandelt wird!

    Ich bin aber auf der Suche nach einer Lösung mit unterschiedlichen Datumsangaben. Erstes Kriterium ist das Datum der Einstellung, sollte das identisch sein, soll der ältere Kollege genannt werden. Ich zerbreche mir seit 2 Tagen darüber den Kopf ob und ja wenn wie soetwas umsetzbar ist.

    Vlt. hast du ja eine Lösung.

    Viele Grüße
    Alex

  • Philipp Ackermann

    Danke für die tolle Anleitung. Ich habe mir hiermit gerade einen kleinen Passwortgenerator erstellt, wobei ich frei wählen kann zwischen der Anzahl der Zahlen, Sonderzeichen und Zeichen, der Länge des Passwortes und auch der erlaubten Sonderzeichen im Selbigen.

  • Benny

    Vielen Dank. Das hat mir als Ansatz schonmal sehr geholfen. Wie kann ich allerdings negative Zahlen aus der Liste herausnehmen? Wie können Daten ab einer bestimmten Anzahl (z.B. alle Städte mit mehr als 500.000 Einwohner) ausgenommen werden?

    • Martin Weiß Beitragsautor

      Hallo Benny,

      zumindest ich habe keinen Vorschlag, und offensichtlich von den anderen Lesern auch niemand. Wobei ich tatsächlich zugeben muss, dass mir nicht ganz klar ist, was genau bei der von Dir gewünschten Sortierung herauskommen soll, wenn bestimmte Werte ausgeschlossen werden sollen.

      Grüße,
      Martin

      • Benny

        Bei mir wird nicht per Einwohnerzahl sortiert sondern nach Tagen. Meine aktuelle sortierte Liste sind derzeit folgendermaßen aus: -4 -3 -2 -1 0 1 2 3 4 …… Nun soll mir die sortierte Liste allerdings nur positive Werte anzeigen. Also 1 2 3 4 …….. Auch soll ein Höchstwert angegeben werden. Sprich, es sollen nur Werte bis 3 angezeigt werden, so dass meine sortierte Beispielsliste nicht -4 -3 -2 -1 0 1 2 3 4 sondern 1 2 3 ausgibt.

        • Marcus

          HI Benni,
          ich hoffe das hilft dir jetzt auch noch.

          Also in Spalte A stehen deine Werte {-4,-3,…,3,4}.
          In Spalte B errechnest du den Rang ab deinem Minimalwert (in diesem Bsp. 0)
          und lässt bei überschreiten des Maximalwertes (in diesem Bsp. 3) direkt eine “0” ausgeben.

          Zelle B12 habe ich in Min umbenannt (Wert=0)
          Zelle B13 habe ich in Max umbenannt (Wert=3)

          Sp A Spalte B [Sp B]
          -4 =WENN(A2>Max;0;ZÄHLENWENNS(A$2:A$10;”=”&Min)) 0
          -3 =WENN(A3>Max;0;ZÄHLENWENNS(A$2:A$10;”=”&Min)) 4
          -2 =WENN(A4>Max;0;ZÄHLENWENNS(A$2:A$10;”=”&Min)) 0
          -1 =WENN(A5>Max;0;ZÄHLENWENNS(A$2:A$10;”=”&Min)) 2
          0 =WENN(A6>Max;0;ZÄHLENWENNS(A$2:A$10;”=”&Min)) 1
          1 =WENN(A7>Max;0;ZÄHLENWENNS(A$2:A$10;”=”&Min)) 0
          2 =WENN(A8>Max;0;ZÄHLENWENNS(A$2:A$10;”=”&Min)) 3
          3 =WENN(A9>Max;0;ZÄHLENWENNS(A$2:A$10;”=”&Min)) 0
          4 =WENN(A10>Max;0;ZÄHLENWENNS(A$2:A$10;”=”&Min)) 0

          Spalte C [Sp C]
          =WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B2);B$2:B$10;0));””) 0
          =WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B3);B$2:B$10;0));””) 1
          =WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B4);B$2:B$10;0));””) 2
          =WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B5);B$2:B$10;0));””) 3
          =WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B6);B$2:B$10;0));””)
          =WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B7);B$2:B$10;0));””)
          =WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B8);B$2:B$10;0));””)
          =WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B9);B$2:B$10;0));””)
          =WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B10);B$2:B$10;0));””)

          Dann viel Spaß beim ausprobieren!
          Viele Grüße!

      • Stefan

        Hallo Benny.
        Hallo Martin.

        Das dürfte sich nur über einen Zwischenschritt (zusätzliche Spalte) lösen lassen. Erst die Liste auf die gewünschten Einträge beschränken
        B1=WENN(ODER(A1< =0;A1>3);””;A1)
        B2=WENN(ODER(A2< =0;A2>3);””;A2)

        und dann sortieren und die Fehler abfangen
        C1=WENN(ISTFEHLER(KKLEINSTE(B$1:B$100;1));””;KKLEINSTE(B$1:B$100;1))
        C2=WENN(ISTFEHLER(KKLEINSTE(B$1:B$100;2));””;KKLEINSTE(B$1:B$100;2))

        Richtig “sauber” lässt sich das nur über eine selbstdefinierte Funktion (VBA) lösen.

        Gruß
        Stefan

        PS: Nette informative Seite. 🙂

        • Stefan

          Hallo Martin.

          Kann man in den Antworten keine Größer-, Kleiner-, Keinergleich-Zeichen etc. verwenden? Meine Formeln werden immer falsch wiedergegeben.
          In den Klammern zum Befehl ODER müsste eigentlich
          (A1 kleinergleich 0;A1 größer 3) bzw. (A2 kleinergleich 0;A2 größer 3)
          stehen.

          Gruß
          Stefan

          • Martin Weiß Beitragsautor

            Hallo Stefan,

            ich habe Deinen ersten Kommentar jetzt entsprechend angepasst. Bei mir hat es mit den Größer-/Kleiner-Zeichen funktioniert, aber vielleicht auch nur, weil ich Admin bin. Das liegt vermutlich daran, dass diese beiden Zeichen zum Umschließen von HTML-Tags verwendet werden.

            Danke auf jeden Fall für Deinen Tipp!

            Schöne Grüße,
            Martin

  • Michael

    Hallo Martin,

    ein weiterer Weg wäre, die Orte gleich mit zu sortieren.

    Dazu bietet sich die naive Version an, hier z.B. für die Zeile 7…
    =B7+(TEXT(CODE(LINKS(A7;1));”000″)&TEXT(CODE(TEIL(A7;2;1));”000″)&TEXT(ZEILE();”0000″))/10^10
    … die aber nicht sauber funktioniert, wenn die Einwohner in “Einern” statt in 1000ern angegeben werden, weil offensichtlich die Anzahl von Excels signifikanten Stellen überschritten wird.
    Für München mit 1400 (in 1000) Einwohnern spuckt die Formel aus: 1400,0772520007

    Eine Verbesserung wäre der Soundex-Algorithmus (z.B. hier zu finden: http://rosettacode.org/wiki/Soundex#VBScript), aber das geht halt kaum ohne eine UDF: der Vorteil liegt auf der Hand: man muß nur das 1. Zeichen in CODE umwandeln, dann folgen immer exakt 3 Ziffern.

    Übrigens ist Index/Vergleich für die EW-Spalte verschenkte Rechenleistung: =Ganzzahl(hilfsspalte) tut’s auch.

    Ansonsten vielen Dank für die Anregungen!

    Schöne Grüße,

    Michael

  • Stephan Wölbert

    Hallo Martin,
    erst einmal vielen Dank für die tollen Anleitungen, die ich ausgesprochen hilfreich finde. Ein Problem/Frage habe ich alerdings noch: Ich möchte die beschriebene Sortierfunktion auf Namen/Text anwenden. Auch hier kann es zu Doppellungen kommen, alo ein Name zweimal auftauchen. In der Ausgabeliste soll er dann aber nur einmal auftauchen. Wie kann man das Problem lösen?
    Danke für eine Antwort.
    Viele Grüße
    Stephan Wölbert

  • Daniel Schlatter

    Guten Tag
    Als Erstes: herzlichen Dank für die Anleitungen zur autom. Sortierung Teil 1 bis 3. Selbst für einen blutigen Laien wie mich ist alles verständlich, und es funktioniert! Das will etwas heissen……

    Ein Problem habe ich allerdings noch. Ich versuche zu erklären, worum es bei mir geht.

    Es geht um Sport und Ranglisten. Durchgeführt werden 4 Rennserien ( Welt-Cup, Intercontinantal-Cup, Europa-Cup, Nord-Amerika-Cup), aus denen eine Weltrangliste erstellt wird über jeweils 8 Rennen maximal, egal, egal in welcher Rennserie diese absolviert wind. Am besten ist diese Weltrangliste mit der Tennis-Weltrangliste der ATP od. WTA vergleichbar. Jede Klassierung ergibt Punkte, wobei die nach Rennserien unterschiedlich sind.

    Für die Erstellung der Weltrangliste (über alle 4 Serien) habe ich je eine Excel-Tabelle “Männer” und “Frauen”, in die ich via Ergebnis-Tabellen die Resultate eingeben kann. Die Tabelle dient mir als Quelle.

    Was ich jetzt auf der Basis dieser Quelle erstellen will ist eine Rangliste pro Rennserie. Und zwar so, dass ich diese nicht von Hand sortieren muss. Wie gesagt, gelingt mir das nach Anwendung von Teil 1 bis 3.

    Ich habe folgendes gemacht:

    Pro Rennserie existiert eine Tabelle “Ranking Männer” und Ranking “Frauen”

    Im Teil “unsortiert” hole ich aus der Tabelle “Männer”
    -alle lizenzierten Männer, egal, in welcher Serie sie mehrheitlich starten (sie können nämlich auch in mehreren Serien an den Start gehen)
    -in einer weiteren Kolonne hole ich mit SVERWEIS aus der Quelle die Punkte für die entsprechende Rennserie
    -in einer weiteren Kolonneermittle ich mit MAX auf der jeweiligen Zeile des Sportlers in der Quelle den SCHLECHTESTEN Rang, wobei ich den durch 100 dividiere
    -um die berühmten Doppler zu umgehen, ermittle ich in einer weiteren Kolonne mit ZEILE() die zu ermittelnde Zeilen.Nr. und dividiere diese durch 10000
    -jetzt addiere ich Punkte, schlechtesten Rang und Zeilennummer zu einem Wert in einer Kolonne, die ich nach KGRÖSSTE sortiere.

    Wunderbar, meine Rangliste steht. Aber:

    In den Regeln des Verbandes steht, dass bei Punktgleichheit das bessere Einzelergebnis entscheidet. Weil ich aber die SCHLECHTESTE Platzierung mit MAX ermittle und nach KGRÖSSTE sortiere geschieht genau das Gegenteil: der mit dem schlechteren Einzelergebnis steht bei Punktgleichheit vor dem mit dem besseren!

    Klar, also das beste Ergebnis mit MIN ermitteln und mit KKLEINSTE sortieren! Einfach, oder ? Schön wär’s! Weil es eben solche gibt, die keine Rennen gefahren sind oder eines in der Serie ausgelassen haben, ist die Beste Platzierung NULL. Und dann funktioniert nichts mehr….

    Ich suche jetzt verzweifelt nach einer Möglichkeit, diese Nuller auszuschliessen, fine sie aber nicht. Irgendwie etwas mit der WENN-Funktion? Aber wie müsste dann die Syntax heissen?

    Ist (hoffentlich) jemand aus dem von mir Geschriebenen schlau geworden und hat darüberhinaus sogar noch eine Idee? Das wäre bärig!

    Mit freundlichen Grüssen

    Daniel

    PS: der entsprechende Sportverband hat natürlich diese Ranglisten nach Serien auf seiner Web-Site. Aber verdammt, es reizt mich einfach, das selbst hinzukriegen!

  • Dominik

    Hallo,

    super Beitrag.
    Ich persönlich brauche diese Funktionskombi für ein Glossar, das ich auf einer Webseite erstellen möchte. Dazu habe ich aber eine Frage, die auch genau das Thema der identischen Einträge tangiert.

    Grundsätzlich: Über eine Maske (Google-Formular) werden Daten gesammelt und Zeile für Zeil in eine Excel (Google-Tabellenblatt) übertragen. Soweit so gut. Bei einem Glossar geht es natürlich 1) in erster Linie um Stichworte und 2) in meinem Fall um Autoren in zweiter Linie. D. h. ich habe z. B. das Stichwort ‘Hammer’ und trage darunter ein, was Herr Heidegger oder/und Herr Chomsky dazu geschrieben haben.

    Die Excel-Tabelle hat also min. 3 Spalten: Stichwort, Autor, Beitrag.

    Sortiert soll nun werden in einem weiteren Tabelleblatt oder -dokument zunächst nach Stichwort, dann nach Autor, sodass aus
    A2 : Hammer B2 : Heidegger C2 : Funktion
    A3 : Zange B3 : Wittgenstein C3 : Wille
    A4 : Hammer B4 : Chomsky C4: Struktur

    das Folgende rauskommt:
    A2 : Hammer B2 : Chomsky C2 : Struktur
    A3 : Hammer B3 : Heidegger C3 : Funktion
    A4 : Zange B4 : Wittgenstein C4: Wille

    Im Prinzip wäre hier die Formel sozusagen nicht über die Zufallsfunktion o. ä. zu berichtigen, sondern über den alphabetischen Rang des Werts rechts davon. Es wäre sozusagen wie das Sortieren auf zwei Ebenen.

    Hast Du eine Idee?

    Grüße
    Dominik

    • Dominik

      Lösung gefunden:
      Ich habe einfach die jeweilige Zelle in Spalte A mit der in Spalte B verkettet (=A2&B2) und meine Formel darauf bezogen.