Automatische Sortierung (Teil 2) 28

Mit ein paar Excel-Funktionen und einem kleinen Trick lassen sich auch alphanumerische Listen automatisch sortiert ausgeben.
 

In der letzten Woche habe ich dir gezeigt, wie man eine unsortierte numerische Liste automatisch sortiert an einer anderen Stelle ausgeben kann. Was ist aber, wenn die zu sortierende Spalte keine Zahlen, sondern Texte enthält? Dann funktioniert der vorgestellte Weg leider nicht.

Aber es wäre ja gelacht, wenn es für dieses Problem nicht auch eine einfache Lösung gäbe. Mit einem kleinen Trick und dem geschickten Einsatz der folgenden Excel-Funktionen können wir auch Texte automatisch sortiert ausgeben lassen:

  • ZÄHLENWENN
  • INDEX
  • VERGLEICH
  • ZEILEN

Und so geht’s:

Die heutige Beispieldatei kannst du dir bei Bedarf hier herunterladen.

Die Ausgangslage

Wir haben – wie auch schon in der letzten Woche – eine Liste mit Städten und den dazugehörigen Einwohnerzahlen:

Beispiel: Unsortierte Städteliste

Beispiel: Unsortierte Städteliste

Diesmal wollen wir die Liste aber alphabetisch nach dem Namen sortiert ausgeben, also nach einem Textfeld. Die KKLEINSTE- bzw. KGRÖSSTE-Funktion hilft uns hier zunächst nicht weiter, denn die setzt Zahlenwerte voraus.

Vergleich von Texten

Zunächst sehen wir uns eine kleine Besonderheit an. Excel lässt es nämlich zu, auch zwei Texte mit arithmetischen Operatoren, wie z.B. < (kleiner als) oder > (größer als), zu vergleichen:

Zwei Städte miteinander vergleichen

Zwei Städte miteinander vergleichen

Hier habe ich jeweils zwei Städtenamen miteinander verglichen: Ist Kabul „kleiner“ als Kairo? Und „kleiner“ bedeutet hier: weiter vorn im Alphabet. Daher ist das Ergebnis WAHR. Ist St. John’s kleiner als Malabo? Alphabetisch gesehen nein, denn S kommt im Alphabet nach M, also ist das Ergebnis FALSCH.

Diese Übung diente nur dazu, den folgenden Schritt besser zu verstehen. Wir ermitteln jetzt mit Hilfe der ZÄHLENWENN-Funktion eine Rangziffer für den Städtenamen.
=ZÄHLENWENN(Bereich; Suchkriterien)
Diese Funktion ermittelt grundsätzlich die Anzahl der nichtleeren Zellen in einem Bereich, welche den angegebenen Suchkriterien entsprechen. In den Suchkriterien dürfen natürlich auch Operatoren wie < oder > verwendet werden.

Was ein wenig überraschend ist: Als Suchkriterium kann man nicht nur einen einzelnen Wert, Text oder eine Zelle angeben, sondern auch einen ganzen Zellbereich. Und genaus das habe ich hier gemacht:

Rangfolge ermitteln mit ZÄHLENWENN

Rangfolge ermitteln mit ZÄHLENWENN

Sowohl für den Bereich als auch für das Suchkriterium gebe ich jeweils die komplette Spalte mit den Städtenamen an. Das heißt für Excel folgendes:
Zähle in der Spalte mit den Städtenamen, wieviele Einträge es gibt, die kleiner oder gleich dem Städtenamen in der aktuellen Zeile sind.

Und somit haben wir haben wir eine schöne numerische Rangliste, die wir für unsere Sortierung verwenden können.

Die Sortierung

Theoretisch könnten wir jetzt die gleiche Logik anwenden, wie in der letzten Woche beschrieben und die Rangliste mit KKLEINSTE bzw. KGRÖSSTE sortieren.

Aber ich will dich ja nicht langweilen und daher nutzen wir stattdessen einen anderen Ansatz: Ein weiteres Einsatzgebiet für unser Dream-Team aus INDEX und VERGLEICH:

 Das Dream-Team INDEX und VERGLEICH

Das Dream-Team INDEX und VERGLEICH

Zur Erklärung:
Um das Ergebnis auf einen Blick zu sehen, gebe ich die sortierte Liste mit Hilfe einer verschachtelten Funktion direkt rechts neben der unsortierten aus. Was macht jetzt diese verschachtelte Funktion? Sehen wir uns die einzelnen Bestandteile von innen nach außen an:

Die ZEILEN-Funktion sagt uns, wieviele Zeilen sich in einem angegebenen Bereich befinden. So liefert z.B. ZEILEN($2:7) den Wert 6 zurück, da dieser Bereich 6 Zeilen umfasst.

VERGLEICH sucht nach einem Wert in einem Bereich und gibt die gefundene Zeilennummer aus, in der der Wert steht. So sucht z.B. VERGLEICH(6; C2:C208) nach der Zahl 6 im Bereich C2:C208 und gibt 4 zurück, wenn die Zahl in der 4. Zeile dieses Bereichs gefunden wurde.

Mit INDEX gibt man den X.ten Wert in einem angegebenen Bereich aus. So gibt z.B. INDEX(A2:A208; 4) den Wert in der 4. Zeile im Bereich A2:A208 aus.

Bezogen auf das Beispiel im Screenshot oben heißt das also folgendes:
Die eingegebene Formel steht in der Zelle F7, daher liefert die ZEILEN-Funktion den Wert 6 zurück.

Die VERGLEICH-Funktion sucht jetzt in der Rangliste (C2:C208) nach dem Wert 6 und liefert die Zeilennummer zurück, in der sie den Wert gefunden hat. Das wäre hier die vierte Zeile (bitte nicht verwirren lassen: der Wert 6 steht zwar absolut gesehen in der 5. Zeile des Tabellenblattes, aber wir haben ja im Bereich C2:C208 gesucht, und bezogen auf diesen Bereich ist das eben die 4. Zeile).

Die INDEX-Funktion sucht jetzt im Bereich A2:A208 und gibt den 4. Wert aus, was in diesem Beispiel „Algier“ ist. Für die Einwohner (Spalte G) und den Rang (Spalte H) passen wird lediglich den Bereich in der INDEX-Funktion an, so dass statt in der Spalte A jetzt in der Spalte B bzw. C gesucht wird. Alles andere bleibt unverändert:

Bereich anpassen

Bereich anpassen

Und schon haben wir unsere Städteliste vollautomatisch aufsteigend sortiert nach dem Namen ausgegeben.

Wer jetzt das Ganze noch absteigend sortiert haben möchte, muss lediglich eine winzige Änderung beim Berechnen der Rangliste vornehmen. Anstatt <= sagen wir jetzt eben >= in der ZÄHLENWENN-Funktion und erhalten damit automatisch die absteigend sortierte Liste:

Absteigend sortiert

Absteigend sortiert

Ich gebe zu, dass man manchmal etwas um die Ecke denken muss, um zum gewünschten Ergebnis zu kommen. Aber gerade das macht den Reiz von Excel aus: Es gibt für (fast) alles eine Lösung und meistens ohne Makro- oder VBA-Programmierung. Sei einfach kreativ!

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.



Schreibe einen Kommentar zu Fabian Hoffmann Antworten abbrechen

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

28 Gedanken zu “Automatische Sortierung (Teil 2)

  • TSRalex

    Danke für die „Erleuchtungshilfen“ bei den automatischen Sortieren!

    Perfetto! 🙂

    Werde mich mal hier näher umsehen & diese Website auch mit Lesezeichen versehen….

    • Martin Weiß Autor des Beitrags

      Gern geschehen, ich hoffe es gibt noch viele Erleuchtungen für Dich hier 🙂

      Schöne Grüße,
      Martin

      • TSRalex

        Ich habe mich anscheinend zu früh gefreut, nachdem alles so fein ausgesehen hat in den Tabellenblättern.

        Nachdem ich das File geschlossen hatte und neu öffnete, sah ich, dass die Werte, die ich mit Funktion „ZÄHLENWENN“ geschaffen hatte, „zerschossen“ -> =ZÄHLENWENN($#REF!$#REF!:$#REF!$#REF!;“<="&$#REF!$#REF!:$#REF!$#REF!) <- waren (und damit alle anderen, davon abhängigen Berechnungen ebenfalls….)!???

        Habe ich etwas übersehen?

        • TSRalex

          Ergänzung:

          Mein erster Alphawert, den ich berücksichtigen will steht auf M8.

          Die Funktion, die an und für sich funktioniert bis zum Wiederöffnen der Datei lautet: -> =ZÄHLENWENN($M$8:$M$100000;“<="&$M$2:$M$100000) <-

          • TSRalex

            Sorry – zu früh kopiert – so ist es natürlich richtig:

            -> =ZÄHLENWENN($M$8:$M$100000;”<="&$M$8:$M$100000) <-

  • TSRalex

    Problem gelöst:

    Es liegt an Open-Office, das bei dieser Funktion Schwierigkeiten hat, wenn es sich um eine XLS-Datei handelt.

    Ist das Ganze im OO-Format (als ODS-File) abgespeichert passt alles! 🙂

    • Martin Weiß Autor des Beitrags

      Hallo TSRalex,

      freut mich zu hören, dass doch noch alles geklappt hat. Viele Tipps funktionieren tatsächlich auch in OpenOffice, aber offensichtlich eben nicht alle.

      Schöne Grüße,
      Martin

  • Markus

    hallo,

    hab diese sortierformel auch benutzt. allerdings habe ich keine Städte und einwohnerzahlen verwendet sondern namen. wenn man mehrere gleiche namen hat kommt immer diese Fehlermeldung #NV. ich weiß nicht wie ich das ändern kann. stehe jetzt irgendwie voll auf dem schlauch.

  • desa

    Hallo,
    vielen Dank erst einmal für den super Tipp. Hat mir schon sehr weiter geholfen.
    Jedoch habe ich noch eine Frage. In der Liste, welche ich gerne sortieren möchte, habe ich Zahlen und Wörter. Wenn ich die Zählenwenn-Formel anwende um die numerische Rangliste zu erstellen habe ich das Problem, das eine Zahl und das erste Wort beide den Ranglistenplatz 1 zugewiesen bekommen. Dadurch sehe ich in der sortierten Liste das erste Wort nicht. Gibt es dafür eine Lösung?

  • Andreas S

    Vielen Dank für diese klasse Excel Hilfe. Das funktioniert einwandfrei.
    Der einzige Punkt, den ich bei meinem Projekt (Buchliste nach Autoren – ich mag Citavi nicht ) noch gerne verbessern würde, ist die dynamische Länge der Liste. Leider bin ich kein Excelkrack und habe gar keinen Plan.
    Also wenn es oben heisst Bereich A2:A208 würde ich das Ersetzen in Bereich A2:letzte:gefüllte_Zeile. Geht sowas?
    Vielen Dank schon mal.
    Andreas

  • Peter

    Hey ich würde gerne eine Liste von Personen, inbegriffen mit Ihren Geburtsdaten nach der Monatlichen Reihenfolge ordnen. D.h. ich habe Spalte mit Name und Nachname und eins daneben Geburtsdaten bsp. 01.01.1968 oder 01.02.1967.
    Ich will nun das die Namen und Nachnamen mit Ihren Geburtsdaten automatisch nach den Monaten sortiert wird. Kannst du mir da behilflich sein.
    Grüße
    Peter

    • Martin Weiß Autor des Beitrags

      Hallo Peter,

      wenn tatsächlich nur nach Monaten sortiert werden soll, und nicht nach dem kompletten Geburtsdatum, müsstest Du eine Hilfsspalte einrichten. In der würdest Du den Monat berechnen und noch eine sehr kleine Zahl anhängen, um eine Eindeutigkeit sicherzustellen.

      Beispiel, wenn das Geburtsdatum in Zelle B2 steht:
      =MONAT(B2)+ZEILE(B2)/1000

      Die Sortierung würde dann auf Basis dieser Hilfsspalte erfolgen, wie im Teil 3 dieser Artikelserie beschrieben.

      Schöne Grüße,
      Martin

      • Lutz Raasch

        Hallo zusammen,
        ich habe nach dem Beispiel ( J98 ist ein Datum) versucht eine Eindeutigkeit herszustellen.
        Dies funktioniert auch

        =J98+ZEILE(J98)/1000

        Wenn ich dann eine Rangliste erstellen will, mit folgender Formel, dann habe ich trozdem bei 500 Zeilen eine Dopplung, obwohl ich eine eindeutige Zahl jeweils rausbekomme.

        =ZÄHLENWENN($M$14:$M$517;“<="&$M$14:$M$517)

        kann mir jemand helfen?

        Excel 2007

        • Martin Weiß Autor des Beitrags

          Hallo Lutz,

          ich habe Dein Szenario gerade nachgestellt und bin auch sehr verwundert, warum es hier Doppelungen gibt…
          Als Lösungsvorschlag hätte ich anstelle von ZÄHLENWENN die normale RANG-Funktion anzubieten. Bezogen auf Dein Beispiel:
          =RANG(M14;$M$14:$M$517;1)

          Damit sollten die Doppelungen erledigt sein.

          Schöne Grüße,
          Martin

  • Stefan

    Hallo Alexander,

    vielen Dank für die tolle Beschreibung.
    Ich wollte Straßennamen sortieren und dabei ist mir ein kleines Problem in deiner Anleitung aufgefallen. Wenn ich mit der Funktion die Rangliste ermittle bekomme ich bei gleichen Straßennamen die gleiche Zahl in der Rangliste angezeigt.
    =ZÄHLENWENN($A$2:$A$8;“<="&$A$2:$A$8)
    Beispiel: Bei den u.a. Straßennamen hätte ich die Ranglisten-Nr.: 4, 4, 4 und 1
    Straße-BBB
    Straße-BBB
    Straße-BBB
    Straße-AAA

    Das wiederum hat zur Folge, dass wenn dazwischen Rangnummern fehlen (in dem Fall die Nr.: 2 und 3) die folgende Index-Funktion nicht funktioniert: =INDEX(A$2:A$8;VERGLEICH(ZEILEN($2:2);$I$2:$I$8;0))

    Ich hoffe dass ich das einigermaßen verständlich formuliert habe.

    Schöne Grüße
    Stefan

    • Martin Weiß Autor des Beitrags

      Hallo Stefan,

      ich heiße zwar Martin, aber Alexander ist auch gut 🙂
      Das Problem lässt sich mit einem ähnlichen Trick lösen, wie in Teil 3 der Artikelserie beschrieben. Ich würde also mit einer Hilfsspalte arbeiten, bei der an die Straße noch die Zeilennummer angefügt wird (bzw. ein Bruchteil der Zeilennummer):
      =A2&ZEILE()/1000
      Dann kommt so etwas heraus wie „Straße-BBB,002“, „Straße-BBB,003“ usw.
      Wenn man dann auf diese Hilfsspalte die ZÄHLENWENN-Funktion anwendet, bekommt man wieder eindeutige und fortlaufende Rangfolgen.

      Schöne Grüße,
      Martin

  • Thomas

    Hallo,
    ich habe gerade die Tipps für meine Tabelle genutzt, ich habe jedoch das Problem das manche Namen in der Tabelle doppelt auftauchen, es somit z.b. keinen Rang 2, aber dafür den Rang 3 doppelt gibt. Bei der Ausgabe der sortierten Namen steht dann an Stelle 2 #NV…..wie könnte ich das Problem lösen?
    Weiter Frage…..zu jedem Namen gibt es ca. 50 Spalten die anschließend mit sortiert werden müssen, gibt es dafür eine vereinfachte Lösung als jede Formel einzeln eingeben zu müssen?

    vielen dank!

    • Martin Weiß Autor des Beitrags

      Hallo Thomas,

      für das Problem mit doppelten Einträgen gibt es ein paar Tipps im dritten Artikel dieser Serie: https://www.tabellenexperte.de/automatische-sortierung-teil-3/

      Was die zweite Frage angeht:
      Man sollte solche Formellösungen auch nicht überstrapazieren. Wenn die Tabelle zu groß wird, würde ich wenn möglich immer auf die manuelle Sortierung über die reguläre Sortierfunktion zurückgreifen.

      Schöne Grüße,
      Martin

  • Chris

    Hallo,
    ich hoffe ich bekomme hier eine Antwort auf meine Frage. Leider kann ich noch nicht so gut mit dem wording von Excel umgehen und bin bei den meisten Erklärungen überfordert. Zu meinem Vorhaben:
    ich habe eine Tabelle die einen Fragebogen beinhaltet. Die Antworten sind „J“ „N“ oder eben keine Antwort also eine leere Zelle. Ich möchte nun alle leeren Zellen zusammen haben also sortieren, damit ich den kompletten Datensatz entfernen kann. Also U6:U2258 soll sortiert werden. Und werden dann die Angaben von VWX usw. mitgenommen? Ich hoffe das war einigermaßen Verständlich und schon mal vielen Dank für die Antwort 😉
    Liebe Grüße,
    Chris

    • Martin Weiß Autor des Beitrags

      Hallo Chris,

      ich würde die ganzen hier gezeigten Formeln vergessen und einfach auf die normale Excel-Sortierfunktion zurückgreifen. Also ersten den ganzen Tabellenbereich mit den Daten markieren und dann über das Menü „Daten | Sortieren“ die Sortierfunktion aufrufen. Wichtig ist nur, dass Du nicht nur die Spalte U markierst, sondern auch die anderen Spalten, die zu Deiner Tabelle gehören. Denn ansonsten gibt es Datensalat 🙂

      Schöne Grüße,
      Martin

  • Fabian Hoffmann

    Hallo Martin,
    besteht auch die Möglichkeit dies auf eine Tabelle ohne eine Rangliste anzuwenden? Ich bin grade dabei eine Exceltabelle für Sammelbestellungen einzurichten und will ich innerhalb des Spaltenbereichs A2:J56 eine automatische alphabetische Sortierung nach dem Nachnamen aus Spalte B erreichen. Dabei darf auch wirklich nur der Bereich sortiert werden, weil alles, was über J hinausgeht meine Funktionen beinhaltet und damit das ganze Ergebnis verschieben würde. Dabei habe ich nur Buchstaben als Sortierwert und keine Rangliste oder andere numerische Werte, anhand derer eine Sortierung, wie oben von dir beschrieben, stattfinden könnte. Ich hoffe du kannst mir da weiterhelfen.

    LG
    Fabian

    • Martin Weiß Autor des Beitrags

      Hallo Fabian,

      du musst bei der hier beschriebenen automatischen Sortierung eine Sache bedenken:
      Man braucht immer eine zweite Tabelle dazu. Die Original-Tabelle (bei dir vermutlich A2:J56) bleibt dabei immer unverändert, sprich unsortiert. Du musst daneben eine zweite identisch aufgebaute Tabelle einrichten, die dann mit den gezeigten Formeln die Inhalte aus der ersten Tabelle sortiert. Du kannst also nicht den Bereich A:J sortieren, wenn sich das Kriterium in Spalte B, also innerhalb dieser Tabelle befindet.

      Ich empfehle ohnehin, dass man die gezeigte automatische Sortierung nicht überstrapaziert. In komplexeren Umgebungen ist eine manuell angestoßene Sortierung die bessere Variante!

      Schöne Grüße,
      Martin

  • Andreas Reimers

    Hallo Herr Weiß

    ich habe hier ein kleines Problem wo ich nicht mehr weiter komme.
    Ich habe eine Excel Datei erstellt für die Auswertung unseres Damenpokals.
    Die Rechnung und Sortierung der Ergebnis des 1. und letzen funktioniert ebenfalls.
    Jedoch habe ich eine Spalte für die Platzierung eingefügt, diese funktioniert jedoch nicht automatisch.
    Hättetn Sie einen Tipp, wie ich hier weiterkommen könnte.

    Vielen Dank

    Andreas Reimers

    Nr Verein NAME Vorname Teiler 1 Teiler 2 Teiler 3 Teiler 4 Teiler gesamt Platzierung
    1 Godshorn Temme Erika 231,6 168,1 190,6 138,1 728,4
    2 Godshorn Temme Erika 130,6 453,7 157,7 43,6 785,6
    3 Godshorn Temme Erika 188,0 208,6 298,9 227,1 922,6
    4 Godshorn Temme Erika 229,6 103,5 82,7 144,2 560,0
    5 Godshorn Temme Erika 124,4 68,7 80,1 97,8 371,0

    • Martin Weiß Autor des Beitrags

      Hallo Herr Reimers,

      wenn ich es richtig verstehe, soll auf Basis von „Teiler gesamt“ die Platzierung bestimmt werden? Dafür können Sie die Funktion RANG.GLEICH (bzw. RANG) verwenden. Die Verwendung habe ich schon mal in diesem Artikel beschrieben:
      Ranglisten in Excel erstellen

      Schöne Grüße,
      Martin