Office-Blog

Beim manuellen Sortieren wird der ursprünglichen Datenbestand geändert. Wer das verhindern will, erledigt das mit Formeln. Excel bietet dazu die Funktionen SORTIEREN und SORTIERENNACH. Sie ermöglichen es, die Daten in einem separaten Bereich in beliebigen Abfolgen anzuordnen.

Die per Formel umsortierten Daten bleiben dynamisch mit den Originaldaten verknüpft. Ändern sich die Originaldaten, wird die umsortierte Ansicht automatisch aktualisiert. Wer solche flexiblen Ansichten seiner Originaldaten braucht, erfährt hier, WIE es geht.

Mit diesen drei Formelvarianten werden die Originaldaten umsortiert

Die Ausgangslage: Eine nach Bundesländern sortierte Tabelle

Die Originaltabelle mit Angaben zu Windenergieanlagen (WEA) ist alphabetisch nach Bundesländern sortiert. Diese Sortierung ist jedoch nicht optimal, wenn die Daten beispielsweise nach der Leistung oder nach der Anzahl der Standorte oder der WEA verglichen werden sollen.

Die Lösung: Die Daten getrennt von der Originaltabelle in einem separaten Bereich so umsortieren, dass Vergleiche leichter fallen.

Die alphabetisch sortierte Originaltabelle mit Angaben zu Windenergieanlagen

1) Die Daten umsortieren nach Kategorie mit der Funktion SORTIEREN

• Die Originaltabelle hat den Namen Daten.
• In Spalte 3 dieser Tabelle sind die Kategorien erfasst.
• Die Daten sollen in einem separaten Bereich nach Kategorie aufsteigend sortiert angezeigt werden.

Das gelingt mit der folgenden einfachen Formel: =SORTIEREN(Daten; 3; 1)

Die Syntax der Funktion ist wie folgt aufgebaut:
SORTIEREN(Matrix; [Sortierindex]; [Sortierreihenfolge]; [nach_Spalte]).

• Matrix ist der Bereich mit den Originaldaten, die umsortiert werden sollen.
• Sortierindex ist die Nummer der Spalte oder Zeile, nach der sortiert werden soll.
• Sortierreihenfolge – hier bedeutet 1 aufsteigend und -1 absteigend sortieren.
• nach_Spalte ist optional und standardmäßig auf FALSCH gesetzt
  FALSCH bedeutet Sortierung nach Zeilen (vertikal) und
  WAHR bewirkt eine Sortierung nach Spalten (horizontal).

Die Daten sind jetzt nach der Spalte Kategorie aufsteigend sortiert

2) Innerhalb jeder Kategorie die Daten nach Leistung absteigend sortieren mit SORTIERENNACH

Durch das Neuanordnen der Daten nach Kategorie ist gut zu sehen, welche Bundesländer bei der Windenergieerzeugung an der Spitze, im Mittelfeld oder am Ende stehen.

Um das Vergleichen der Daten noch leichter zu machen, sollen innerhalb jeder Kategorie die Bundesländer mit der höchsten Leistung ganz oben stehen.

Dazu bedarf es eines zweiten Sortierkriteriums, das die Daten zusätzlich nach Leistung sortiert – und zwar absteigend. Das erledigt die Funktion SORTIERENNACH. Sie ermöglicht eine Sortierung von nach mehreren Kriterien, wobei die Sortierreihenfolge und die Kriterien separat angegeben werden.

Die Formel für das Beispiel hier lautet:
=SORTIERENNACH(Daten; Daten[Kategorie]; 1; Daten[Leistung (MW)]; -1)

Damit werden die Daten – wie in der folgenden Abbildung gezeigt – sortiert nach Kategorie (aufsteigend) und Leistung (absteigend) aufgelistet.

Ergebnis nach der Sortierung mit zwei Kriterien: Bundesländer mit der höchsten Leistung werden in jeder der drei Kategorien zuerst aufgelistet

Die Syntax der Funktion ist wie folgt aufgebaut:
SORTIERENNACH(Matrix; Sortiermatrix1; [Sortierreihenfolge1]; [Sortiermatrix2]; [Sortierreihenfolge2]; …)

• Matrix ist der Bereich mit den Originaldaten, die umsortiert werden sollen.
• Sortiermatrix erfasst die Bereiche, nach denen sortiert werden soll.
• Sortierreihenfolge – 1 bedeutet auch hier aufsteigend und -1 absteigend sortieren.

In der folgenden Abbildung lässt sich der Aufbau der hier verwendeten Formel im Detail noch besser nachvollziehen:

Die Argumente der SORTIERENNACH-Formel zum besseren Verständnis zeilenweise angeordnet

3) Profi-Technik: So beherrscht SORTIEREN auch mehr als nur ein Kriterium – die Magie von {}

Was kaum bekannt ist: Die Funktion SORTIEREN meistert auch mehrere Sortieranweisungen. Dazu müssen die Angaben zu den beiden Argumenten Sortierindex und Sortierreihenfolge in geschweifte Klammern geschrieben werden.

Was heißt das konkret im hier vorgestellten Beispiel?

• Damit zuerst nach der dritten, dann nach der zweiten Spalte sortiert wird, gebe ich für das Argument Sortierindex ein {3 . 2}.
• Um die Spalte Kategorie aufsteigend, die Spalte Leistung hingegen absteigend zu sortieren, trage ich für das Argument Sortierreihenfolge ein {1 . -1}.

Hier die vollständige Formel, mit der auch die Funktion SORTIEREN mehrere Kriterien und Bereiche meistern kann.

Mehrere Angaben zu Spaltenindex und Sortierreihenfolge mit Hilfe geschweifter Klammern in die Funktion SORTIEREN einbauen

4) Optik-Tipp: Mehr Übersicht in der umsortierten Liste durch automatische Trennlinien

Um die Vergleichbarkeit noch weiter zu verbessern, folgt zum Schluss optischer Feinschliff. Und zwar sollen die einzelnen Kategorien optisch voneinander getrennt werden. Das erledigen automatische Trennlinien am Ende jeder Kategorie mittels Bedingter Formatierung.

• Dazu markiere ich den Bereich mit den umsortierten Daten – hier die Zellen H4 bis L19.
• Auf der Registerkarte Start wähle ich Bedingte Formatierung > Neue Regel.

Die umsortierten Daten markieren und die Bedingte Formatierung aufrufen

• Unter Regeltyp wähle ich den letzten Eintrag: Formel zur Ermittlung der zu formatierenden Zellen verwenden [1].
• Die erforderliche Formel lautet: =$J4<>$J5 [2].
• Dann klicke ich rechts unten auf Formatieren [3] und wechsle im folgenden Dialog zum Register Rahmen.
• Nun lege ich die Farbe der Linie [4] fest und dass sie am unteren Rand (also nach dem letzten Eintrag einer Kategorie) angezeigt werden soll [5].

Den letzten Regeltyp wählen und die erforderliche Formel eintragen

Die Rahmenformatierung einstellen

Die fertige Liste mit Trennlinien sieht dann wie folgt aus:

Die zweifach umsortierte Liste mit automatischen Trennlinien zwischen den Kategorien