TEXT

Einen eingefärbten Kalender erstellen

Roman Allgemein, Formeln , , , , , , , , , , , , , ,

Wer kennt das nicht: Es beginnt ein neues Kalenderjahr und sämtliche Dateien wie Personaleinsatzplanung, Ferienpläne, Projektübersichten, Terminlisten, etc. müssen für das neue Jahr aufgesetzt werden. In diesem Blog zeige ich Dir, wie Du dies mithilfe von vereinzelten Formeln sowie bedingten Formatierungen ganz schnell hinbringst!

Du kannst Dir dafür gerne direkt diese Datei (Farbiger_Kalender.xlsx) herunterladen. Alternativ kannst Du nachfolgendes Bild selbst nachkonstruieren (Beschreibungen dazu folgen unmittelbar):

In der Zelle "B1" gibst Du das gewünschte Jahr ein, in "B2" den entsprechenden Monat. Unterhalb wird Dir mittels der Formel "DATUM" die Kombination aus Tag (fortlaufende Nummer im Bereich  "D3:D33") sowie dem Monat und dem Jahr in "B2" respektive "B1" erstellt. Daraus können wir in der Spalte "E" mittels der Formel "TEXT" den jeweiligen Wochentag ermitteln (im Beitrag Dynamische Titel (Formel "TEXT") haben wir bereits angeschaut, wie man den Wochentag von einem beliebigen Datum auslesen kann).

Hier zusammengefasst noch einmal die bereits erwähnten Formeln:

[In der Zelle "B3" und dann entsprechend angewendet auf "B3:B33"]
=DATUM($B$1;C3;D3)
[In der Zelle "C3" und dann entsprechend angewendet auf "C3:C33"]
=MONAT(1&$B$2)
[In der Zelle "E3" und dann entsprechend angewendet auf "E3:E33"]
=TEXT(B3;"TTTT")

Die bedingte Formatierung zur farblichen Hervorhebung von Samstagen und Sonntagen

Das einzige, was jetzt noch fehlt, ist die Markierung von den Wochenenden. Hierzu verwenden wir bedingte Formatierungen. Falls Du die oben bereitgestellte Datei verwendest, klicke beispielsweise die Zelle "E3" an und gehe dann via "Start" in der Menüleiste auf "Bedingte Formatierung" und dort auf "Regeln verwalten...". Auf den Bereich "$D$3:$K$33" wenden wir zwei Formeln an. Falls der jeweilige Wert in der Spalte "E" ein Samstag respektive Sonntag ist, werden die Zellen von "D" bis "K" grau eingefärbt. Weitere Informationen findest Du, wenn Du auf "Regel bearbeiten" klickst.

Wie erstellt man man so eine Formel selbst? Falls Du das gerne nachkonstruieren möchtest, gehst Du wie folgt vor: Selektiere zum Beispiel die Zelle "E3" und gehe dann via "Start" → "Bedingte Formatierung" auf "Neue Regel...". Nimm dann die Einstellungen in der Grafik unten vor. Diese sind: Wähle einerseits "Formel zur Ermittlung der zu formatierenden Zellen verwenden" als Regeltyp aus und gebe dann folgende Formel ein:

=$E3="Samstag"

Danach kannst Du via dem Button "Formatieren..." dein gewünschtes Format auswählen.

Formatierungsregel

Wichtig zu erwähnen ist an dieser Stelle, dass der Bezug unbedingt "$E3" sein muss. Wir setzen dadurch die Spalte "E" absolut. Dies bedeutet, dass die Regel in beispielsweise der Zelle "K3" ebenfalls überprüft, ob eben in dieser Zelle "E3" "Samstag" steht. Die Zeilennummer ist nicht fixiert, was bedeutet, dass in der Zelle "K6" zu "E6" referenziert wird. Nach dem Erstellen dieser Regel können wir via "Start" → "Bedingte Formatierung" → "Regeln verwalten..." bei "Wird angewendet auf" den Bereich =$D$3:$K$33 eintragen. Diese Schritte wiederholst Du dann für Sonntag, indem Du eine weitere Regel anlegst.

Falls Du weniger oder mehr Spalten eingefärbt haben möchtest (da Du beispielsweise mehrere Mitarbeitende oder Projekte erfassen möchtest), kannst Du einfach Deine Regeln verwalten und den Bereich von =$D$3:$K$33 auf =$X$3:$X$33 erweitern!

... und nun kannst Du in den Zellen "B1" und "B2" andere Monate und Jahre eingeben und hast direkt die Wochenende eingefärbt!

Wie kann ich das nun verwenden?

Diese Frage ist natürlich abhängig davon, was Du genau für eine Anwendung dafür hast. Falls Du nicht alle Informationen von diesem Tabellenblatt beibehalten möchtest, kannst Du entweder vereinzelte Zellen/Spalten ausblenden oder wie folgt vorgehen: Der einfachste Weg, damit Du diese Datei verwenden kannst, ist, den gewünschten Monat auszuwählen und dann mit einem Rechtsklick auf das Tabellenblatt via "Verschieben oder kopieren..." eine Kopie zu erstellen. Wenn Du nun den Bereich "B3:E33" auswählst und kopierst und im Anschluss darüber nur "Werte" einfügst, bleiben die Formatierungen erhalten! Du kannst die übrigen Spalten "A" bis "C" sowie die Zeilen eins und zwei löschen. Danach wiederholst Du den Vorgang für weitere Monate/Jahre. Zum Beispiel für den Monat Februar wirst Du zusätzliche vorige Zeilen unterhalb haben, diese kannst Du natürlich ebenfalls entfernen.

P.S.: Falls Du eine englische Version von Excel verwendest, müsstest Du selbstverständlich die Monate in "B2" auf Englisch eingeben, die Formel "TEXT" leicht anpassen ("TTTT" wird zu "DDDD") und die bedingte Formatierung von "Samstag" auf "Saturday" und von "Sonntag" auf "Sunday" ändern.

Dynamische Titel (Formel "TEXT")

Roman Formeln , , , ,

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.