Zeilenumbruch innerhalb einer Formel (Formel-Text-Kombination)

Auf Excelblog.ch wurde die Möglichkeit bereits aufgezeigt, wie Du Formeln und Text miteinander verknüpfen kannst. Beispielsweise mit der Eingabe von ="Heute ist der "&HEUTE() wirst Du den eingegebenen Text und immer das aktuelle Datum angezeigt bekommen (zwar noch als Zahl; das Datum müsste noch mit der Formel "TEXT" umformatiert werden - mehr dazu findest Du im Beitrag "Dynamische Titel (Formel "TEXT")"). Wenn diese Texte und Kombinationen jedoch länger werden, kann es durchaus sein, dass man das Gebilde an einer bestimmten Stelle auf eine neue Zeile umbrechen möchte. Die normale Zeilenumbruch-Funktion ist da ziemlich unflexibel und bringt nicht immer das gewünschte Resultat - besonders, wenn Du die Spaltenbreite aufgrund anderer Inhalte Deiner Excel-Tabelle nicht beliebig anpassen kannst. Fügst Du jedoch die Formel ZEICHEN(10) mit ein, wirst Du Deine Kombination genau an dieser Stelle auf die neue Zeile brechen. ZEICHEN(10) stellt also einen Zeilenumbruch dar. Um an das Datumsbeispiel zu Beginn dieses Beitrags anzuknüpfen, würde die Formel mit einem Zeilenumbruch demnach lauten:

="Heute ist der "&ZEICHEN(10)&HEUTE()

respektive "perfekt" dargestellt mit der TEXT-Formel, verlinkt weiter oben:

="Heute ist der "&ZEICHEN(10)&TEXT(HEUTE();"TT.MM.JJJJ")

Häufigster Wert eines Bereichs ausgeben (auch Text)

Mit den Formeln Anzahl oder Zählenwenn können bestimmte Werte gezählt werden. Doch was, wenn unzählige verschiedene Werte in einem Bereich aufgeführt sind und wir nur den häufigsten davon auslesen wollen? Nachfolgend sind unsere Daten, aus welchen wir den häufigsten Wert auslesen möchten (die Farben hatte ich vorgängig mit bedingten Formatierungen erstellt):

datenhaeufigsterwert

Nebenan möchten wir nun ermitteln, welches denn der häufigste Wert ist. Aufgrund der Farben - und da die Liste nur 11 Angaben umfasst - könnten wir auch von Auge ermitteln, dass es "Wert2" ist. Dennoch: Wir möchten dies mit einer Formel ermitteln. Diese lautet:

{=INDEX(A1:A11;VERGLEICH(MAX(ZÄHLENWENN(A1:A11;A1:A11));ZÄHLENWENN(A1:A11;A1:A11);0))}

Die geschweifte Klammer ist nicht selbst einzugeben - es handelt sich um eine Matrixformel! Schliesse die Formel mit CTRL + SHIFT + ENTER ab, um die Matrixformel zu bestätigen.

Das Resultat: Wert2 ist am häufigsten im Bereich "A1:A11". Sobald Du die Werte änderst (bspw. mehr "Wert3" im Bereich aufführst), zeigt Dir die Formel sofort den neuen Wert an.

Einen eingefärbten Kalender erstellen

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.