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.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.