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
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
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
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
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
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 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
Und wenn man jetzt noch alle Funktionen zusammenfasst, kommt man zur folgenden finalen 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
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
Wie man in der unsortierten Liste sieht, sollte stattdessen meine „Fantasia-City“ aufgeführt werden:
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.
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.
Pingback: Automatische Sortierung (Teil 2) | Der Tabellen-Experte
Pingback: Automatische Sortierung (Teil 3) | Der Tabellen-Experte
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
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
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
Hallo Tobias,
ja, das ist in Teil 3 der Artikelserie beschrieben.
Schöne Grüße,
Martin
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
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
Hallo Martin,
ich habe eine Frage: die automatische Sortierung funktioniert super aber kann ich mein Arbeitsblatt auch so formatieren, dass er mir automatisch immer die Zeile mit dem höchsten Wert nach oben schiebt?
Bsp.: wir nehmen deine Tabelle mit den Einwohnerzahlen und ändern die Einwohnerzahl in der Arbeitsmappe „unsortiert“ von Istanbul auf 15.000.000. Jetzt möchte ich eigentlich, dass excel mir die Zeile mit Istanbul in der gleichen Arbeitsmappe (also in „unsortiert“) an die entsprechende Stelle schiebt?!
Hallo Adrian,
eine solche Automatik ist mit „normalen“ Mitteln nicht möglich. Dazu ist ein wenig VBA-Programmierung erforderlich.
Schöne Grüße,
Martin
Hallo Martin,
hab mich über Makros belesen und es damit gemacht. Eine Tastenkombination und schon ist es sortiert 🙂
Danke trotzdem…
Bis dahin…
Hallo Martin,
Hallo an alle anderen Statistik-Fans
danke für die schlüssigen Erläuterungen – wirklich eine große Hilfe!
Gibt es auch die Möglichkeit weitere Kategorien in der Sortierung zu berücksichtigen. Um das obere Beispiel fortzuführen: Wenn zwei Positionen (Istanbul und Fantasia-City) in der ersten Prüfpriorität (Einwohner) den identischen Wert besitzen, dass ein zweites Prüfkriterium (in zweiter Priorität) das Sortieren bestimmt (z.B. Anzahl der Biergärten :D). In meinem Fall habe ich ca. 20 Positionen, welche nach 5 bis 10 priorisierten Kategorien sortiert werden sollen. Wobei die einzelnen Kategorien zwischen 1 bis 10 bewertet werden – somit gibt es einige identische Einträge und nur die weiteren Kategorisierungen lassen eine korrekte Sortierung zu.
Danke im Voraus für allfällige Tipps.
Gruß
Jürgen
Hallo Jürgen,
ich kann Dir hier zwar keine fertige Lösung bieten, aber Du müsstest wahrscheinlich in zwei Stufen sortieren: In der zweiten Stufe wird die bereits vorsortierte Liste nach dem zweiten Kriterium sortiert. Vielleicht kommst Du damit ja einen Schritt weiter.
Schöne Grüße,
Martin
Hallo Martin,
Danke für die rasche Antwort.
Das war auch mein erster Gedanke – jedoch funktioniert dies in meinem Fall nicht, da die Reihenfolge der ersten Priorität nicht mehr berücksichtigt wird. Bezogen auf das Beispiel: Bei zweiter Sortierung nach Anzahl Biergärten steht (wahrscheinlich) München an erster Stelle.
Leider konnte ich in anderen Quellen auch nichts Brauchbares für eine dynamische Sortierung nach mehreren priorisierten Kriterien finden. Somit muss ich dies weiterhin manuell über die Sortierfunktion ausführen.
Gruß
Jürgen
Hallo Martin,
Hast Du auch eine Loesung fuer alphanumerische Daten (z.B. Kfz-Kennzeichen)?
Hallo Marc,
ja, in Teil 2 dieser Artikelserie wird die Sortierung von alphanumerischen Listen behandelt:
https://www.tabellenexperte.de/automatische-sortierung-teil-2/
Schöne Gürße,
Martin
für einhöner nicht verständlich! Total scheiße 🙁
Hallo,
ich fand diesen Beitrag sehr gut. Wenn ich aber eine Tabelle habe:
Nitrit 0,022 mg/L
Ammonium 0,679 mg/L
Nitrat 8,41 mg/L
Phosphat 0,433 mg/L
(das sind werte die jeden tag gewonnen werden also 365 x 4 Werte plus großes Labor einmal im Monat
Wie kann ich nach den einzelnen Werten eine Monatsauswertung machen??
Für einen Tipp bin ich sehr dankbar
lg
mfg
Martin Miosga
Hallo Martin,
das klingt für mich eher nach einer Pivot-Tabelle. Hast Du es damit schon mal versucht? Wenn Du bisher noch keine Erfahrung damit hast, empfehle ich Dir diese beiden Einsteiger-Artikel:
https://www.tabellenexperte.de/pivot-tabellen/
https://www.tabellenexperte.de/pivot-tabellen-teil-2/
Schöne Grüße,
Martin
Hallo, ich habe eine Kontaktliste in excel angelegt. Dort sind Firmen angelegt, die in unregelmässigen Abständen anrufen. Sobald ich eine Firma gesprochen habe, schreibe ich einen Text in eine Zelle (extra Spalte), die alle möglichen Texte und Zeichen enthält. Wie kann ich meine neuesten Einträge immer von oben nach unten (neuesten Einträge on top) sortieren? Erkennt excel das aktuelle Eingabedatum ohne dass ich ein Datum in der Zelle eingebe?
Vielen Dank!
Hallo Elke,
Excel erkennt das Eingabedatum nicht automatisch, das muss wirklich manuell gesetzt werden. Aber wenn ich es richtig verstanden habe, schreibst Du alle Texte zu einer Firma immer in die gleiche Zelle, versehen mit dem aktuellen Datum. Richtig? Dann wird es ohnehin schwierig bis unmöglich. Für eine vernünftige Sortierung müsste jedes Gespräch in eine eigene Zelle geschrieben werden und das Datum dazu in eine weitere separate Zelle daneben. Nur dann wird eine Sortierung überhaupt möglich.
Schöne Grüße,
Martin
Möchte man den „Stolperstein“ umgehen so muss man den werten einfach +0,0000000000000001, +0,0000000000000002 usw. addieren.
Wo genau wird es denn eingefügt?
Idee zum Einbezug mehrerer gleicher Einträge: Hilfsspalte, die den Wert mit z.B. 100 multipliziert und die Anzahl der gleichen Einträge addiert. kann dann später wieder zurückgerechnet werden.
Hallo Martin, diese ist der beste Blog den ich bisher zu Tabellenkalkulationen gefunden habe.Gratulation. Durch die hier angebotene Lösung „Sortier-Automatik Teil 1 bis Teil 3“ habe ich das Problem gelöst. Was mir jetzt noch fehlt ist bei dem hier angegebenen Beispiel zu bleiben, eine Lösung zur Formatierung. Wenn die Einwohnerspalte mit der Bedingten Formatierung über die „Drei-Farben Skala“ formatiert sind, wie bekomme ich diese „Drei-Farben Skala“ Formate auf die Städte übertragen?
Mit freundlichen Grüßen
Michael Schröter
Hallo Michael,
danke für das Lob, das freut mich sehr. Leider wird das mit der Drei-Farben-Skala nicht klappen. Diese Art von Regeln kann nur direkt auf die jeweilige Zelle angewendet werden, nicht jedoch mit Bezug auf eine andere Zelle.
Schöne Grüße,
Martin
Moin,
Ich habe die automatische Sortierung intensiv verfolgt und kann sie gut anwenden. Allerdings ergibt sich das Problem für mich, dass die Sortierung nicht mehr funktioniert, wenn ich nicht in Zeile 1 anfange..
Was mache ich falsch bzw. was muss ich ändern?
Hallo isegrimm47,
eigentlich spielt es in meinen Formeln keine Rolle, wo die Tabelle beginnt. Es sei denn, oberhalb der unsortierten Tabelle befinden sich noch andere Daten, die nicht berücksichtigt werden dürfen. In diesem Fall muss in den Formeln der tatsächliche relevante Datenbereich angegeben werden. Also statt A:A bzw. B:B beispielsweise A10:A100 bzw. B10:B100
Schöne Grüße,
Martin
Hallo Martin, die automatische Sortierung einer Spalte klappt. Aber der Inhalt der anderen Spalte wird in der „alten“ Reihenfolge übernommen und NICHT entsprechend der Sortierung zugewiesen.
Formel Spalte 1: =KGRÖSSTE(I:I;ZEILEN($I$10:I10))
Formel Spalte2: =INDEX(H:H;VERGLEICH(I10;I:I;0))
Erläuterung: Spalte I (bei dir die Einwohner) ist Kriterium zur Sortierung (Ausgabe in gleichem Blatt), Spalte H (wäre die Stadt) wird NICHT zugewiesen. Was ist falsch?
Und: gibt es eine Lösung, mehrere Spalten in einer zu sortieren? Also z. B. deine Städte (und Einwohner) nicht in einer (A+B), sondern in mehreren Spalten (C+D, E+F, …) mit dem selben Ergebnis in EINER Spalte (bzw. zwei – G+H).
LG Uwe
Hallo Uwe,
du musst in der Formel in der zweiten Spalte im ersten Teil der VERGLEICH-Funktion Bezug auf die bereits sortierte Spalte nehmen. So wie es für mich aussieht, verwendest du dort die Spalte aus der noch unsortierten Tabelle. Hast du dir die Beispieldatei heruntergeladen? Dort wird das vielleicht deutlicher.
Was deine zweite Frage angeht: Hier bin ich leider völlig verwirrt. Mehrere Spalten in einer Spalte sortieren? Ich stehe auf dem Schlauch…
Schöne Grüße,
Martin
Hallo Martin, danke für den Hinweis mit dem Verweis – klappt jetzt.
Zum „Schlauch“: ich habe nicht 200 Daten in einem Spaltenpaar stehen, sondern nur 40 (Spalte A+B), daneben wieder 40 (Spalte C+D) usw. Bei mir geht es um eine Auszählung nach D’Hondt. aus 5 Listen, welche in einem Spaltenpaar aufgelistet werden müssen. Jetzt muss ich diese aber ebenso wie beschrieben sortieren, aber eben nicht in 5, sondern diese ALLE zusammen gefasst in EINEM Spaltenpaar (z. B. Spalte K+L, Reihung nach D’Hondt). Derzeitiger Lösungsgedanke: ich müsste mit Verweis mit =A1, =B1 usw. dann =C1, =D1… usw. auf diese Listen verweisen und diese Verweise würden dann untereinander stehen, also hier dann 200 und diese sortiere ich dann wie von dir beschrieben. Diesen Umstand wollte ich vermeiden. Ich hoffe, meine Erklärung konnte ich gut zum Ausdruck bringen. Danke schon mal.
LG Uwe
Hallo Uwe,
ich würde in diesem Fall nicht mehr auf Formeln zurückgreifen, sondern alles über Power Query abwickeln. Dort lassen sich die 5 Spaltenpaare wunderbar zu einer einzigen Tabelle zusammenfassen und mit diese Tabelle kannst Du dann ebenfalls in Power Query sortieren oder sonstwie bereinigen. Das ist deutlich unkomplizierter und weniger fehleranfällig als eine Formellösung.
Schöne Grüße,
Martin
Hallo Martin,
danke für deine Info. ich habe kurz mal versucht, mit Power Query etwas anzufangen und bin kläglich gescheitert. Wohl darum, weil ich noch nie damit zu tun hatte. Für mich ein Buch mit sieben Siegeln. ich bleibe dann doch bei meinem umständlichen Zusammenfügen der Spalten in einer und bei den Formeln.
Besten Dank nochmals für deine Hilfe.
LG Uwe
Hallo Uwe,
ja, aller Anfang ist schwer. Aber es lohnt sich wirklich, sich mit Power Query zu beschäftigen. Wenn du erst einmal den Einstieg gefunden hast, wirst du es dir nie wieder ohne vorstellen können 🙂
Es gibt hier auch ein paar Blogartikel dazu:
https://www.tabellenexperte.de/stichwort/power-query/
Schöne Grüße,
Martin
Sehr hllfreich. wirklich. Gut erklärt, gut hergeleitet. danke!
Gern geschehen 🙂
Da bei der Verwendung von Zufallszahlen die Reihenfolge von (u.U.) mehreren gleichen Werte zufällig ist und bei der Verwendung von Zeile oder Rang.Gleich die Reihenfolge sogar invers ist, kann man den Wert um den Kehrwert der Zeilennummer ergänzen.
Damit bleibt bei gleichen Werten die ursprüngöiche Reihenfolge erhalten.
Hilfsspalte D:
=C2+1/ZEILE(C2)/1000000
Hallo Jürgen,
vielen Dank für diese Ergänzung!
Schöne Grüße,
Martin