Benutzerdefiniertes Sortieren einer Tabelle

Eine Liste lässt sich mit wenigen Klicks sortieren, alphabetisch von A-Z (aufsteigend) oder Z-A (absteigend). Allerdings reicht dies unter Umständen nicht aus, wenn Du eine individuelle (benutzerdefinierte) Sortierung nach Kategorien oder ähnlich anstrebst. Vielleicht hast Du Glück und die Kategorien sind zufälligerweise in einer Reihenfolge, die sich sinngemäss auf- oder absteigend sortieren lässt. Meist ist dies jedoch nicht der Fall (z.B. auch bei Monaten oder Wochentagen). In diesem Beitrag zeige ich Dir aber eine Variante, wie Du eine eigene Sortierung vorgeben kannst ohne Hilfsspalten zu verwenden.

Angenommen Du hast eine Liste mit Freizeitaktivitäten:

sortList_initial

Wenn Du diese Liste nach ihrer Priorität (Spalte B) sortierst, wirst Du entweder die Einträge mit "Ignorieren" oder "Prio. 2" zu oberst darstellen. Gerne hätten wir jedoch die Reihenfolge: Prio. 1; Prio. 2; Ignorieren.

Um dies zu erlangen, wenden wir die Sortierung nach einer benutzerdefinierten Liste an. Markiere zunächst Deine gesamte Tabelle mit allen Aktivitäten & deren Prioritäten (oder klicke auf z.B. eine Überschrift "Freizeitaktivität" - denn der mögliche Tabellenbereich wird automatisch erkannt und vorgeschlagen). Gehe anschliessen im Menüband "Daten" auf "Sortieren":

Im angezeigten Fenster zur Sortierung kannst Du dann wählen: Sortieren nach "Priorität" und als Reihenfolge wählst Du "Benutzerdefinierte Liste...":

sortList_customList

Dies öffnet Dir umgehend ein weiteres Fenster. Generiere dort eine neue Liste, indem Du einfach Listeneinträge nach ihrer Reihenfolge eingibst. Jedes Kriterium schreibst Du auf eine eigene Zeile und bestätigst jeweils mit "Enter", als Zeilenumbruch. Erst wenn Du alle Einträge in ihrer Reihenfolge eingetragen hast, gehst Du rechts auf "Hinzufügen". Nun hast Du deine eigene Liste definiert, nach der Du sortieren kannst.

sortList_customListValues

Auf der linken Seite findest Du auch weitere benutzerdefinierte Listen. Da sind weitere Anwendungsfälle ersichtlich, die Du früher allenfalls nur mit Hilfsspalten gelöst hast: Die Sortierung nach Wochentagen (Montag bis Sonntag) oder nach Monaten (Januar bis Dezember). Diese lassen sich ja auch nicht alphabetisch sinnvoll sortieren.

Sobald Du die Liste ausgewählt hast, greift diese Reihenfolge. Du kannst in den Sortierungseinstellungen weitere Ebenen einfügen (z.B. nach der Priorität dann die Freizeitaktivitäten alphabetisch sortieren). Füge dazu weitere Ebenen nach Deinen Bedürfnissen ein. So erhalten wir eine benutzerdefinierte, sortiere Tabelle:

Dynamische Titel (Formel "TEXT")

Im kürzlich veröffentlichten Beitrag Dynamische Ergebnisse (Formel "Teilergebnis") wurde gezeigt, wie auf angewandte Filter reagiert werden kann, sodass stets das korrekte Ergebnis geliefert wird. Die herkömmliche SUMME-Formel berücksichtigt den aktiven Filter nicht, wohingegen sich TEILERGEBNIS nur auf die noch angezeigten Daten bezieht.

Wir verwenden hier wiederum die Verkäufe, wie in nachfolgendem Bild ersichtlich:

Teilergebnis1

Ziel ist es nun, oberhalb der Überschrift (Datum; Kundennummer; Umsatz; Absatz) einen Titel einzufügen. Dieser soll folgendermassen lauten: "Verkäufe von 10.01.2014 bis 11.02.2014". Desweiteren soll der Titel dynamisch sein, damit direkt auf den aktiven Filter reagiert und stets der korrekte Zeitraum angegeben wird.

Text und Formel verketten

In diesem Abschnitt beziehe ich mich auf bereits im Blog Inhalte aus Zellen miteinander verketten gezeigte Inhalte, wo wir sowohl Text und Formeln/Bezüge miteinander verknüpft haben. Nach dem Einfügen einer neuen Zeile geben wir vorerst in Zelle A1 folgendes ein:

="Verkäufe von "

Um nun das tiefste Datum zu ermitteln, verwenden wir die Formel "TEILERGEBNIS", mit der Funktion "MIN" (Minimum). In der Zelle A1 haben wir demzufolge:

="Verkäufe von "&TEILERGEBNIS(5;A3:A9)

Wichtig ist hier das Kaufmännische-Und-Zeichen (&), welches uns ermöglicht, sowohl Text ("Verkäufe von ") und dann eben auch Bezüge oder Formeln (eingeläutet mit dem &-Zeichen) miteinander zu kombinieren.

Nach dieser Eingabe siehst Du, dass der Titel noch nicht vollständig ist, wir erhalten als Resultat "Verkäufe von 41649". Du bist jedoch auf dem richtigen Weg - der 10. Januar 2014 ist einfach noch nicht korrekt formatiert worden! Es ist hier zu erwähnen, dass jedes Datum als Zahl im System hinterlegt ist, beginnend beim 1. Januar 1900 - probier es doch mal aus: Gib in einer beliebigen Zelle "01.01.1900" ein und formatiere diesen Wert in eine Zahl um - Du erhälst "1". Dies erlaubt es uns, mit Daten zu rechnen (beispielsweise Rechnungsdatum +30 = Fälligkeitsdatum). Aber zurück zum Thema: Wie kannst Du nun das Datum umformatieren? Hier kommt die Formel "TEXT" zum Zuge.

Die Formel "TEXT"

Mittels der Formel "TEXT" kann das Format eines Wertes beliebig angepasst werden. In unserem Falle für ein Datum bedeutet dies: Das Datum 10.01.2014 (oder die Zahl 41649) kann beliebig umformatiert werden. Die Formelsyntax von "TEXT" verlangt nach einem Wert [Bezug (Datum oder Zahl)] und nach dem gewünschten Format. Der Bezug ist in unserem Beispiel das Datum 10.01.2014. Als Textformat kannst Du nun z.B. “T. MMMM JJJ“ eingeben. Das Resultat: "10. Januar 2014". Auch die erfassten Punkte und Leerschläge wurden akzeptiert und berücksichtigt. Doch was bedeuten diese "T, M & J" im Detail?

T steht für Tag, M für Monat und J für Jahr. Ich liste hier auf, welche Formate möglich sind:

T = Tag ohne vorhergehende Null (1., 2., 10., 20. Januar)

TT = Tag mit vorhergehender Null (01., 02., 10., 20. Januar)

TTT = Tag ausgeschrieben, abgekürzt (Mo, Di, Mi, Do, Fr, Sa, So)

TTTT = Tag ausgeschrieben (Montag, Dienstag, Mittwoch, …)

 

M = Monat ohne vorhergehende Null (1., 2., 10., 12. 2014)

MM = Monat mit vorhergehender Null (01., 02., 10., 12. 2014)

MMM = Monat ausgeschrieben, abgekürzt (Jan, Feb, Mrz, …)

MMMM = Monat ausgeschrieben (Januar, Februar, März, …)

 

J oder JJ = Jahr, abgekürzt (14, 15, 16)

JJJ oder JJJJ = Jahr, nicht abgekürzt (2014, 2015, 2016)

Bezogen auf unseren dynamischen Titel ist nun also die Formel "TEILERGEBNIS(...)" mit obiger Formel zu kombinieren. Das Teilergebnis entspricht dem Wert der TEXT-Formel, für welches das Format ("TT.MM.JJJ") bestimmt wird. In Zelle A1 steht demnach:

="Verkäufe von "&TEXT(TEILERGEBNIS(5;A3:A9);"TT.MM.JJJ")

Nun haben wir - auf den Filter reagierend - stets den tiefsten Wert/das tiefste Datum ermittelt. Um "Verkäufe von ... bis ..." zu erlangen, wäre die Formel analog oben zu erweitern. Wir gehen wiederum Schritt für Schritt vor und erweitern den Titel erstmal um den Textteil " bis ". Zu beachten ist, dass die Formel mit einem &-Zeichen und der Text mit Gänsefüsschen abzugrenzen ist. In Zelle A1 steht danach:

="Verkäufe von "&TEXT(TEILERGEBNIS(5;A3:A9);"T.MM.JJJ")&" bis "

Der letzte Schritt ist nun, noch das Maximum mittels Teilergebnis zu ermitteln. Am einfachsten kopierst Du den Teil "&TEXT(TEILERGEBNIS [...] "T.MM.JJJ")" und setzt diesen ans Ende der Formel in der Zelle A1. Die Funktion für MAX in der Formel TEILERGEBNIS ist die Nummer 4, für MIN ist es die Nummer 5. Dies ist noch anzupassen, danach hast Du einen dynamischen Titel gesetzt, der auf den Filter reagiert und Dir stets den korrekten Zeitraum angibt. Die fertigte Formel in A1 lautet:

="Verkäufe von "&TEXT(TEILERGEBNIS(5;A3:A9);"TT.MM.JJJ")&" bis "&TEXT(TEILERGEBNIS(4;A3:A9);"TT.MM.JJJ")

Du könntest nun den Datumstyp beliebig anpassen und beispielsweise den Wochentag integrieren, indem das Format in "TTTT, TT. MMMM JJJ" geändert wird. Das Resultat: Freitag, 10. Januar 2014. Daraus wird ersichtlich: Du kannst mit der Formel "TEXT" jederzeit - und schnell - den Wochentag ermitteln, indem Du als Format einfach "TTTT" wählst.

Grundsätzlich siehst Du, habe ich die Formel Schritt für Schritt aufgebaut - es wird nur unnötig kompliziert, wenn man mehrere Angaben miteinander als Formel anzugeben versucht. Ich werde in einem späteren Blog zeigen, wie ich beim Erstellen von ganzen Berichten jeweils vorgehe. Nun wünsche ich Dir jedoch erstmal viel Erfolg beim Formatieren von Daten und dem Erstellen von dynamischen Titeln und Berichten. Hinterlasse mir doch bei Fragen und Anregungen ungeniert einen Kommentar.