Automatische Sortierung (Teil 2) 11

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.



Kommentar erstellen

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

11 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….

      • 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ß Beitragsautor

      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?