In dem Tipp erfahren Sie, wie man in Excel Tabellen sortiert, sogar automatisch während der Eingabe von Werten.
Excel ist eines der wichtigsten Programme aus dem Microsoft Office-Paket im Büroalltag. Der große Funktionsumfang bietet verschiedene Lösungsmöglichkeiten, um alltägliche Aufgaben wie das automatische Sortieren einer Excel-Tabelle zu ermöglichen. Im folgenden Tipp beschreiben wir daher drei verschiedene Möglichkeiten, eine Excel-Tabelle automatisch zu sortieren.
- Excel bietet eine praktische Sortierfunktion im Menüband.
- Eine automatische Sortierung direkt nach der Werteeingabe ist mit einigen Formeln in Excel auch ohne Makro möglich.
- Nutzer von Excel 365 haben mit der neuen Formel SORTIERENNACH am wenigsten Aufwand für eine automatische Sortierung.
Inhalt
1. Wunderwaffe Excel
Das Microsoft Office-Paket steckt voller Funktionen und Möglichkeiten. Leider wird nur ein kleiner Teil davon von den meisten Anwendern genutzt. Oft sind alternative Lösungen und Möglichkeiten gar nicht bekannt, weshalb neben den Grundfunktionen die im Laufe der Jahre hinzugekommenen Spezialfunktionen - vor allem in Excel - wenig bis gar nicht bekannt sind.
2. Die Problemstellung: Excel-Tabelle automatisch sortieren
In diesem Tipp zeigen wir Ihnen verschiedene Möglichkeiten, eine Excel-Tabelle mit verschiedenen Werten automatisch zu sortieren. Als Beispiel haben wir eine Tabelle mit verschiedenen Standorten, Umsätzen, Ausgaben und einer Gewinnermittlung. Alle Lösungswege erfordern keine Programmierkenntnis, wenngleich man mit Excel-Makros auch Tabellen einfach sortieren kann, entsprechende Grundkenntnisse rund um Makros vorausgesetzt.
Unsere Ursprungstabelle können Sie herunterladen.
Sie können unsere Beispieldatei mit den fertigen Formeln hier kostenlos herunterladen.
Die Aufgabe lautet, die Standorte nach Gewinn absteigend automatisch zu sortieren. Hierfür gibt es mehrere Lösungsmöglichkeiten.
3. Nutzung der Sortierungsfunktion von Excel
Unsere erste Lösung verwendet die Funktion „Sortieren und Filtern“, die aus dem Funktionsband bekannt ist. Klicken Sie auf die Schaltfläche „Sortieren und Filtern“ und wählen Sie „Benutzerdefinierte Sortierung“.
Sortieren und Filtern finden Sie im Menüband von Excel.
Im nächsten Fenster legen Sie unter „Sortieren nach“ die Spalte fest, nach der sortiert werden soll. In unserem Beispiel ist dies „Gewinn“. Im Feld „Reihenfolge“ wählen Sie „Nach Größe (absteigend)“, damit die Filiale mit dem höchsten Gewinn ganz oben steht.
Über die "Sortieren"-Funktion können Sie die Excel-Tabelle automatisch sortieren.
Damit ist die Sortierung abgeschlossen. Sie können noch weitere Sortierebenen hinzufügen, indem Sie auf „Ebene hinzufügen“ klicken. Dies ist für unser Beispiel interessant, wenn zwei Standorte den gleichen Gewinn haben. Als weiteres Sortierkriterium geben wir hier noch die „Ausgaben“ an und wählen hier „aufsteigend“, so dass bei gleichem Gewinn der Standort mit den geringeren Ausgaben weiter oben steht.
Weitere Sortierebenen können auch genutzt werden.
Die fertige Sortierung sieht dann wie folgt aus:
Die automatisch sortierte Excel-Tabelle.
Benutzerdefiniertes Sortieren ohne Spaltenangaben
In unserem Beispiel hat die verwendete Tabelle Spaltenüberschriften, die für die benutzerdefinierte Sortierung verwendet werden. Wenn Sie eine Tabelle ohne Spaltenüberschriften verwenden, was wir grundsätzlich nicht empfehlen, funktioniert der oben beschriebene Weg nicht. In diesem Fall müssen Sie die gesamte Tabelle markieren, die benutzerdefinierte Sortierung aufrufen und dann „Daten haben keine Überschriften“ auswählen. Als Sortierkriterium kann dann die Spalte (A, B, C...) angegeben werden.
Sortieren ohne Spaltenüberschriften ist auch möglich.
4. Automatische Sortierung während der Eingabe
Bei der unter Punkt 3 vorgestellten Methode muss bei jeder Änderung der Werte die Tabelle neu sortiert werden. Bequemer wäre ein Weg, bei dem Excel die Sortierung nach jeder Eingabe automatisch vornimmt. Auch hierfür bietet das Tabellenkalkulationsprogramm einen Weg.
Als Ausgangspunkt verwenden wir wieder unsere „Gewinntabelle“. Für die Ausgabe verwenden wir eine neue Tabelle auf einem anderen Tabellenblatt. Für die Sortierung verwenden wir die Funktion KGRÖSSTE. Diese ermittelt den größten Wert aus einer Liste und schreibt ihn in eine Tabelle. Als Gegenstück gibt es auch die Funktion KKLEINSTE, die aufsteigend sortiert.
Die Formel für den größten Wert lautet hier:
=KGRÖSSTE(Bereich;1)
In unserem Beispiel wird der größte Wert aus der Tabelle ausgegeben.
Die KGRÖSSTE-Funktion sucht den größten Wert in einer Spalte.
Werte und Sortiertabelle getrennt? Ob Sie die neue, automatisch sortierte Tabelle auf einem anderen Tabellenblatt - wie in unserem Beispiel oder im gleichen Tabellenblatt - anlegen, ist Geschmackssache. Wenn Sie auf einem Tabellenblatt bleiben, kann die Angabe "Tabelle1" in den Formeln jeweils entfallen.
Die Formel muss nun für alle weiteren Spalten der Ursprungstabelle wiederholt werden, wobei der Wert hinter dem Bereich immer um 1 erhöht werden muss.
KGRÖSSTE-Funktion für alle Gewinne.
Im nächsten Schritt werden die restlichen Spalten aus der Ursprungstabelle übernommen. Die Ermittlung ist über den Wert „Gewinn“ im Prinzip problemlos möglich.
Dazu verwenden wir die Funktionen VERGLEICH und INDEX. VERGLEICH sucht zunächst den Wert „Gewinn“ in der Ursprungstabelle, während INDEX dann die Inhalte aus der Tabelle für die automatische Sortierung verwendet. Der beliebte und bekannte SVERWEIS funktioniert hier nicht, da diese Funktion nur die Inhalte rechts vom gesuchten Wert auslesen kann, in unserer Tabelle sich die zu übernehmenden Werte aber links befinden.
Die Formel lautet daher für die erste Zeile und Spalte:
=INDEX(Tabelle1!A:A;VERGLEICH(E2;Tabelle1!E:E;0))
Die Formel nimmt über den INDEX den Wert aus der Spalte A, wenn mithilfe von VERGLEICH der Wert „Gewinn“ aus unserer neuen Tabelle in der Ursprungstabelle gefunden wird.
Mit INDEX und VERGLEICH kann man automatisch sortieren.
Für die anderen Spalten wird analog vorgegangen und in den Formeln der Spalten B, C und D nur der über INDEX auszulesende Wert geändert.
INDEX und VERGLEICH verwenden wir hier für alle Spalten.
Die neue Tabelle sortiert dann über den Gewinn alle Zeilen neu.
Unsere Lösung hat aber einen Schönheitsfehler. Es kann vorkommen, dass Gewinnwerte doppelt vorhanden sind, was in unserem Beispiel auch tatsächlich der Fall ist.
Doppelte Werte machen Probleme.
Der INDEX nimmt dann immer die erste Zeile, in welcher der gesuchte Gewinnwert ermittelt wird. Das Problem lässt sich lösen, indem man eine Hilfsspalte einbaut, welche zu dem Gewinn die Zeilennummer berücksichtigt. Die Formel dazu würde lauten:
=E2+ZEILE(B2)/10000
Mit der Funktion ZEILE erzeugt man neuen Gewinn als Hilfsspalte.
Wenn man die dynamische Tabelle nach der neuen Gewinnspalte sortiert und den tatsächlichen Gewinn als weitere Spalte mit INDEX und VERGLEICH ausliest, sieht das Ergebnis wie folgt aus:
Die Tabelle mit der Hilfspalte erleichtert die Sortierung bei doppelten Werten.
Der Gewinn wird dabei wie die übrigen Spalten aus der Grundtabelle ausgelesen.
Den echten Gewinn liest man mit der INDEX-VERGLEICH-Formel aus.
5. Die neue SORTIERENNACH-Funktion in Excel 365 macht alles einfacher
Nutzer von Excel 365 aus dem Paket Microsoft 365 (vormals Office 365) sowie Excel 2021 können sich den Aufwand der Vorgehensweise unter 4. ersparen und die neue SORTIERENNACH-Funktion nutzen, welche für ältere Excel-Versionen leider nicht zur Verfügung steht, dafür aber in dem kostenlosen Office-Paket von Microsoft als Web-App.
Die Formel SORTIERENNACH hat folgenden Syntax:
=SORTIERENNACH(Matrix;Nach_Matrix1;[Sortierreihenfolge1];[Nach_Matrix2];[Sortierreihenfolge2];...)
- Matrix: Sie geben zunächst die Matrix an, welche die Tabelle ohne die Kopfzeilen umfasst. In unserem Beispiel: A2:E29.
- Nach_Matrix1: Der zweite Parameter ist die Matrix, nach der sortiert werden soll. In unserem Beispiel sind das die Werte Gewinn in E2:E29.
- Sortierreihenfolge1: Das dritte Argument ist die Sortierreihenfolge. Während 1 aufsteigend bedeutet, verwenden wir hier -1 für eine absteigende Sortierung.
Die weiteren Parameter Nach_Matrix2, Sortierreihenfolge2 usw. sind optional und erlauben es, die Tabelle nach weiteren Ebenen zu sortieren, wie wir es unter Punkt 2.1 gemacht haben.
Ohne weitere Sortierebenen lautet die Formel für das Beispiel damit:
=SORTIERENNACH(Tabelle1!A2:E29;Tabelle1!E2:E29;-1)
Sobald Sie die Formel in eine Zelle eingeben, vervollständigt Excel automatisch alle weiteren Spalten und Zellen in der Tabelle. Ausschlaggebend ist dafür der Matrix-Bereich.
SORTIERENNACH in Excel ist eine Neuerung von Excel 365.
Die neue Funktion SORTIERENNACH in Excel erspart Ihnen damit nicht nur viel Arbeit, sondern arbeitet auch wie erhofft: Die Daten werden direkt nach der Eingabe neu sortiert.
27 Bewertungen
Ø 3,89
Loading...