Automatische Sortierung (Teil 1) 8

Das automatische Sortieren von Excel-Listen ist mit den Funktionen KKLEINSTE und KGRÖSSTE gar nicht so schwer
 

Ein Leser hatte kürzlich wegen einer automatischen Sortierung einer Tabelle angefragt: Datensätze werden (unsortiert) in einem Arbeitsblatt eingegeben und sollen in einem anderen Arbeitsblatt automatisch sortiert angezeigt werden.

Da dies auch für andere Excel-Anwender interessant sein könnte, nehme ich diese Inspiration (danke, Daniel!) zum Anlass für einen entsprechenden Blogartikel.

Je nach Art und Inhalt der zu sortierenden Spalte können unterschiedliche Methoden zum Einsatz kommen.

Heute sehen wir uns die automatische Sortierung von numerischen Spalten an und nutzen dabei vor allem die folgenden Funktionen:

  • KKLEINSTE
  • KGRÖSSTE

Außerdem zeige ich dir, welche Stolpersteine es dabei zu beachten gilt.

Und so geht’s:

Im Artikel “5 nützliche Excel-Funktionen… für Zahlenjongleure” hatte ich die beiden Funktionen KKLEINSTE und KGRÖSSTE schon einmal vorgestellt. Zur Erinnerung: Man ermittelt damit den x-kleinsten oder x-größten Wert einer Liste (also z.B. den zweitkleinsten, viertgrößten etc.)

Diese Erkenntnis lässt sich in Kombination mit etwas Hirnschmalz in eine brauchbare Lösung für unser Sortierproblem nutzen.

Die unsortierte Ausgangsliste

Als Beispiel habe ich eine unsortierte Liste mit Städtenamen und den dazugehörigen Einwohnerzahlen. Die Datei kannst du dir bei Bedarf hier herunterladen.

Die unsortierte Ausgangsliste

Die unsortierte Ausgangsliste

Diese Liste soll nun auf einem zweiten Arbeitsblatt nach der Einwohnerzahl sortiert ausgegeben werden. Und zwar automatisch / dynamisch: Wenn ich an meine Ausgangstabelle unten weitere Datensätze anfüge, sollen sie sofort in meiner sortierten Tabelle an der richtigen Stelle auftauchen.

Beginnen wir also am Anfang…

Aufsteigend sortieren

An erster Stelle meiner sortierten Liste soll die Stadt mit der kleinsten Einwohnerzahl kommen. An zweiter Stelle die mit der zweitkleinsten usw.
Für diesen Zwecke hilft uns hier die KKLEINSTE-Funktion:
=KKLEINSTE(Bereich; k)

Der Bereich ist die Spalte B mit den Einwohnerzahlen in unserer unsortierten Liste. Damit würden die ersten drei Einträge in der sortierten Liste so aussehen:

Schritt 1: Einsatz von KKLEINSTE

Schritt 1: Einsatz von KKLEINSTE

Es ändert sich also immer nur der zweite Parameter, mit dem wir angeben, den wievielten kleinsten Wert wir anzeigen wollen (1, 2, 3…). Dieser Wert lässt sich über die ZEILEN-Funktion auch ganz leicht dynamisch ermitteln:

=ZEILEN(Bezug)

Diese Funktion gibt die Anzahl der Zeilen im angegebenen Bezug aus. Das heißt =ZEILEN(B2:B4) gibt den Wert “3” aus, da dieser Bereich drei Zeilen umfasst.

Schritt 2: Einsatz von ZEILEN

Schritt 2: Einsatz von ZEILEN

Da der Bereichsanfang in unserer Formel immer B2 ist, machen wir daraus einen absoluten Bezug, so dass beim Kopieren der Formel dieser Anfangspunkt unverändert bleibt.

Kombiniert mit unserer KKLEINSTE-Funktion sieht die verbesserte Version dann so aus:

Schritt 4: Die kombinierte Formel

Schritt 4: Die kombinierte Formel

Nun brauchen wir nur noch die dazugehörenden Städtenamen in Spalte A.

Da sich der Name der Stadt links von der Einwohnerspalte befindet, können wir die allseits beliebte SVERWEIS-Funktion leider nicht anwenden. Das ist aber kein Problem: Stattdessen kommt unser Dream-Team INDEX und VERGLEICH zum Einsatz.

=VERGLEICH(Suchkriterium; Bereich; Vergleichstyp)

Die VERGLEICH-Funktion sucht also in einem Bereich nach einem bestimmten Kriterium und gibt dann die Position an. Das heißt, die Zeile, in der sich mein gesuchter Wert befindet (der Vergleichstyp “0” besagt, dass wir nach einer exakten Übereinstimmung suchen). In unserem Beispiel sieht das so aus:

Schritt 5: Den Städtenamen finden

Schritt 5: Den Städtenamen finden

Wir suchen also nach dem Wert “1001985” (bzw. 1001998 bzw. 1012010) in der unsortierten Liste. Und Excel findet diesen Wert in Zeile 66 (bzw. 217 bzw. 96):

Die richtige Zeile in der unsortierten Liste

Die richtige Zeile in der unsortierten Liste

Die INDEX-Funktion nimmt dann diese Zeilennummer und gibt den Inhalt der angegebenen Spalte aus:

=INDEX(Bereich; Zeile; [Spalte])

Das Schöne dabei ist: Ich kann jede beliebige Spaltennummer ausgeben lassen und bin nicht – wie beim SVERWEIS – auf eine Spalte rechts von meiner ursprünglichen Suchspalte festgelegt.

Schritt 7: Die INDEX-Funktion

Schritt 7: Die INDEX-Funktion

Und wenn man jetzt noch alle Funktionen zusammenfasst, kommt man zur folgenden finalen Formel:

Letzer Schritt: Die finale Formel

Letzer Schritt: Die finale Formel

Absteigend sortieren

Die einzige Änderung, die notwendig ist, um die Tabelle stattdessen absteigend zu sortieren: Anstelle der KKLEINSTE-Funktion nutzen wir die KGRÖSSTE-Funktion zur Berechnung der Einwohnerzahl:

Absteigend sortieren mit KGRÖSSTE

Absteigend sortieren mit KGRÖSSTE

Die Formel, die die dazu passende Stadt liefert, bleibt unverändert.

Stolperstein

Auch wenn die vorgestellte Lösung in sehr vielen Fällen ausreichend ist, möchte ich dir einen wichtigen Schwachpunkt nicht vorenthalten:

Sobald in der Suchspalte der exakt gleiche Eintrag mehrmals in der Liste vorkommt, funktioniert die vorgestellte Lösung nicht mehr:

Das falsche Ergebnis

Das falsche Ergebnis

Wie man in der unsortierten Liste sieht, sollte stattdessen meine “Fantasia-City” aufgeführt werden:

Identische Werte sind kritisch

Identische Werte sind kritisch

Ich werde zu einem späteren Zeitpunkt darauf eingehen, wie sich auch dieser Fall lösen lässt. Wer jetzt schon einen guten Ansatz weiß, darf ihn gern in den Kommentaren teilen.

Ausblick:
Im heutigen Artikel haben wir am Beispiel der Einwohnerzahl gesehen, wie man numerische Werte automatisch sortiert bekommt. Doch auch für alphanumerische Werte gibt es eine einfache Lösung, die ich dir nächste Woche vorstellen werde.

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.

8 Gedanken zu “Automatische Sortierung (Teil 1)

  • Maximilian

    Hallo Martin,
    ich habe deine Methode angewendet und sie funktioniert super, allerdings habe ich ein Problem.
    Meine Rohdaten Liste wird ständig erweitert da es sich um eine Inventarliste mit Auftragsnummern handelt. Gibt es eine Möglichkeit, dass sich die sortierte Tabelle entsprechend von alleine verlängert? Bis jetzt muss ich immer manuell erweitern durch ziehen.
    Grüße,
    Maxi

    • Martin Weiß Beitragsautor

      Hallo Maxi,

      eine automatische Verlängerung der sortierten Tabelle ist leider nicht möglich (zumindest nicht ohne VBA). Was mir spontan einfällt: Du könntest die sortierte Tabelle schon auf einen größeren Bereich erweitern und die nicht benötigten Elemente über eine bedingte Formatierung ausblenden (d.h. alle Zeilen, in denen ein Fehlerwert angezeigt wird).

      Schöne Grüße,
      Martin

  • Tobias Dressel

    Hallo Martin,

    hast Du schon eine Lösung zu dem oben genannten Stolperstein, wenn zwei identische Werte vorhanden sind?

    Habe nämlich das Problem bei einer Auswertung und komme nicht weiter… 🙁

    Viele Grüße,
    Tobias

  • Karl

    Hallo Martin,

    danke für das aufschlussreiche Tutorial. Gibt es eine Möglichkeit die Formatierung der Ausgangszelle in der neuen Zelle der sortierten Spalte zu übernehmen? Da ich verschiedene Datenpakete, deren Werte Weibullartig verteilt sind, kurz gegeneinander abschätzen will wäre das sehr hilfreich:-)

    VG aus Aachen

    • Martin Weiß Beitragsautor

      Hallo Karl,

      da sehe ich eigentlich nur den Weg über eine bedingte Formatierung, sofern das im konkreten Anwendungsfall möglich ist. Wenn sich also Regeln bestimmen lassen, nach denen eine Formatierung stattfinden soll (z.B. aufgrund eines bestimmten Zellwertes), dann wäre das eine Möglichkeit.

      Schöne Grüße,
      Martin