Weitere Beispiele für den Einsatz der bedingten Formatierung

Hier gelangen Sie zu den Unterthemen:

• Einsatz der bedingten Formatierung als Diagrammersatz
• Zeilenformatierung
• Zellvergleich blattübergreifend mit der Funktion INDIREKT
• Nach Art des Inhalts (Gesperrte Zellen / Formelzellen / Fehlerwerte / >255 Zeichen / Textzahlen)
• Horizontaler Autofilter

Zum Blattanfang

Einsatz der bedingten Formatierung als Diagrammersatz

Sie möchten vielleicht keine echten Diagramme in Ihre Datei einbauen, z.B. um die Datei leicht zu halten. Dann können Sie mit der bedingten Formatierung einen Diagrammersatz schaffen und haben damit den Vorteil, dass Ihr Diagramm dynamisch ist.

Sie finden nachstehend Beispiele zu:

• Säulendiagramm
• Balkendiagramm

Zum Blattanfang Zum Themenanfang

Säulendiagramm

In folgendem Beispiel richtet sich die Höhe der Säulen nach den Werten in Zeile 2.

Das Format aus B4 ist nach rechts und unten kopierbar. Wesentlich ist, dass die in der Formel genannte Zahl der untersten Zeilenzahl des Diagramms entspricht für den Fall, dass Ihr Diagramm mehr als hier im Beispiel 5 Zeilen umfassen soll. Durch sehr schmale Zeilenhöhe im Diagrammbereich ließe sich das auch für wesentlich größere Zahlenräume visualisieren:

A B C D E F G H I 1                   2   3 1 5 2 4 1 5   3                   4                   5                   6                   7                   8                   9                   Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B4 1. / Formel ist =8-ZEILE()<B$2 Abc

Zum Blattanfang Zum Themenanfang

Balkendiagramm

Hier ein Beispiel zu variierenden Arbeitszeiten. Die Formel in F4 (nicht über Mitternacht) ist nach unten kopierbar, das Format in G4 sowie die WENN-Formel, die zum Anspringen der bedingten Formatierung führt, ist nach unten und rechts kopierbar:

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE 1                                                               2   Arbeitszeit   Arbeitszeit               3   von   bis   [Std:Min] 6h 7h 8h 9h 10 h 11 h   4   07:30 - 10:00   02:30 0                                                 5   07:00 - 09:00   02:00                                                   6   06:45 - 11:15   04:30                                                   7   06:15 - 10:30   04:15                                                   8   06:30 - 10:45   04:15                                                   9   07:00 - 11:30   04:30                                                   10   08:00 - 09:15   01:15                                                   11   06:45 - 09:30   02:45                                                   12                                                               Formeln der Tabelle F4 : =D4-B4 G4 : =WENN($F4="";"";WENN(ODER(0,25+(SPALTE()-7)*1/96<$B4;$D4-1/1440<0,25+(SPALTE()-7)*1/96);0;1)) Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format G4 1. / Zellwert ist gleich 0 Abc G4 2. / Zellwert ist gleich 1 Abc

Zum Blattanfang

Zeilenformatierung

Sie finden nachstehend Beispiele zu:

• Gerade Zeilen farbig unterlegen
• Ungerade Zeilen farbig unterlegen

Zum Blattanfang Zum Themenanfang

Gerade Zeilen farbig unterlegen

Das Format aus Zeile 2 ist kopierfähig.

A B C D E 1           2           3           4           5           6           7           8           9           Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B2 1. / Formel ist =ZEILE()=GERADE(ZEILE()) Abc C2 1. / Formel ist =ZEILE()=GERADE(ZEILE()) Abc D2 1. / Formel ist =ZEILE()=GERADE(ZEILE()) Abc

Zum Blattanfang Zum Themenanfang

Ungerade Zeilen farbig unterlegen

Das Format aus Zeile 2 ist kopierfähig.

A B C D E 1           2           3           4           5           6           7           8           9           10           Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B2 1. / Formel ist =ZEILE()<>GERADE(ZEILE()) Abc C2 1. / Formel ist =ZEILE()<>GERADE(ZEILE()) Abc D2 1. / Formel ist =ZEILE()<>GERADE(ZEILE()) Abc

Zum Blattanfang

Zellvergleich blattübergreifend mit der Funktion INDIREKT

Die bedingte Formatierung hat eine Einschränkung: Bezüge auf andere Tabellen oder Arbeitsmappen dürfen nicht verwendet werden. Über die Funktion INDIREKT kann man diese Einschränkung umgehen. Will man diese bedingte Formatierung über verschiedene Arbeitsmappen einsetzen, müssen diese geöffnet sein.

Um festzustellen, ob der Inhalt einer Zelle identisch ist mit dem Zellinhalt der gleichen Zelle eines anderen Tabellenblatts kann man die Funktion INDIREKT einsetzen

Um Zellen bedingt zu formatieren, die im anderen Tabellenblatt an gleicher Position sind und inhaltlich abweichen, setzt man die INDIREKT Funktion wie folgt ein. Das Format aus B2 kann nach rechts und unten kopiert werden:

	A	B	C	D	E
1
2		0	3	5
3		9	5	4
4		7	8	14
5		9	10	4
6		14	11	7
7

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format B2 1. / Formel ist =B2<>INDIREKT("Tabelle2!Z"&ZEILE()&"S"&SPALTE();) Abc

Wenn man das <>-Zeichen durch =-Zeichen ersetzt, werden die identischen Zellen markiert.

Soll die Formel auch noch verwendbar sein, wenn sich der Tabellenblattname ändert, dann muss statt der Funktion INDIREKT die Funktion BEREICH.VERSCHIEBEN genutzt werden.

Zum Blattanfang

Nach Art des Inhalts (Gesperrte Zellen / Formelzellen / Fehlerwerte / >255 Zeichen / Textzahlen)

Sie finden nachstehend Beispiele zu:

• Gesperrte Zellen farblich unterlegen
• Zellen, die Formeln enthalten, farblich unterlegen
• Zellen, die Fehlerwerte enthalten, farblich unterlegen
• Zellen, die mehr als 255 Zeichen enthalten, farblich unterlegen
• Textzellen, die eine Zahl anzeigen, auswerten

Zum Blattanfang Zum Themenanfang

Gesperrte Zellen farblich unterlegen

Markieren Sie den Bereich, in dem Sie gesperrte Zellen sofort über die Formatierung erkennen möchten.

Die erste markierte Zelle wäre B2, dann Formel =ZELLE("Protect";B2)=1

Wählen Sie das gewünschte Format für diese Zellen aus.

Die Zellsperrung alleine sagt nichts über Blattschutz aus, nur ob die Zelle gesperrt ist. Erst in Kombination mit Blattschutz wäre sie geschützt. Dieser Schutz ist mehr ein Schutz vor Fehlbearbeitung, da er schnell auszuhebeln ist.

Zum Blattanfang Zum Themenanfang

Zellen, die Formeln enthalten, farblich unterlegen

Geben Sie dem Bereich, in dem Sie die Formelzellen bedingt formatieren möchten, einen Namen über Einfügen>Name>Namen definieren..... - z.B. Name: Test

Bezieht sich auf: =ZELLE.ZUORDNEN(48;INDIREKT("ZS";))

Markieren Sie die Zellen, die bedingt formatiert werden sollen.

Dann unter bedingter Formatierung, Formel ist: =Test

und weisen das gewünschte Format zu. Ab nun erkennen Sie auf den ersten Blick, welche Zellen eine Formel enthalten.

Zum Blattanfang Zum Themenanfang

Zellen, die Fehlerwerte enthalten, farblich unterlegen

Sie möchten Zellen formatieren, die einen Fehlerwert enthalten. Dies sind:

Fehlerwerte: #NULL! #DIV/0! #WERT! #BEZUG! #NAME? #ZAHL! #NV

Einen bestimmten Fehlerwert bzw. alle Fehlerwerte bedingt formatieren können Sie so:

A B C D 1         2   #NV gezielt einen bestimmten Fehlerwert, wie in B2 #NV bedingt formatieren   3   #DIV/0! wenn sie die Schriftfarbe entsprechend der Hintergrundfarbe einstellen bei Fehlerwert, wird der Fehler unsichtbar. In Zelle B3 steht #DIV/0! Die Formel funktioniert bei allen Fehlerwerten.   4   #WERT! Jedweden Fehlerwert bedingt formatieren   5         Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B2 1. / Formel ist =ISTNV(B2) Abc B3 1. / Formel ist =ISTFEHLER(B3) Abc B4 1. / Formel ist =ISTFEHLER(B4) Abc

Zum Blattanfang Zum Themenanfang

Zellen, die mehr als 255 Zeichen enthalten, farblich unterlegen

Gelegentlich wird eine Tabelle für bestimmte Bearbeitungen in eine neue Arbeistmappe kopiert. Dabei erhalten wir eine Fehlermeldung und zwar, dass die Tabelle nicht kopiert werden kann, weil eine Zelle mehr als 255 Zeichen enthält. Es kommt dann auch zu einem ungültigen Vorgang. Mit bedingter Formatierung kann man diese Zellen visualisieren, das Format aus B2 ist kopierbar:

A B C 1       2   1. Den Brokkoli putzen und in kleine Röschen teilen. Die Nudeln in reichlich Salzwasser al dente kochen. Etwa 2 Minuten vor Ende der Garzeit den Brokkoli dazugeben. Abgießen und gut abtropfen lassen. Inzwischen die Tomaten überbrühen, häuten, entkernen und in Würfel schneiden. Den Knoblauch schälen und fein hacken. Die Kräuter waschen, trockenschütteln und ohne Stiele hacken.   3   2. Das Olivenöl in einer Pfanne erhitzen und den Knoblauch kurz andünsten. Dann das Hackfleisch dazu geben und krümelig braten. Mit Salz und Pfeffer kräftig würzen. Die Pfanne vom Herd nehmen und die Kräuter unterrühren. Den Backofen auf 200 ° vorheizen.   4       Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B2 1. / Formel ist =LÄNGE(B2)>255 Abc

So erkennen Sie direkt die "Problemfälle" und können die Kopie im Einzelnen anpassen.

Zum Blattanfang Zum Themenanfang

Textzellen, die eine Zahl anzeigen, auswerten

Um Textzahlen mit bedingter Formatierung auszuwerten, muss man die Zellen in der Formel der bedingten Formatierung mit 1 multiplizieren. Hierdurch wird die Textzahl in eine auswertbare Zahl für die bedingte Formatierung gewandelt. Würde man den Zellinhalt direkt mit 1 multiplizieren, zerschlüge man die bedingte Formatierung. Ein Beispiel (das Format aus B3 ist kopierbar):

A B C 1       2   Textzahlen z.B. <=10:   3   5   4   7   5   12   6       Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B3 1. / Formel ist =B3*1<=10 Abc

Zum Blattanfang

Horizontaler Autofilter

Wir alle kennen den in Excel integrierten vertikalen Autofilter. Eine horizontale Filterung kann man über VBA erreichen oder über folgenden optischen Trick mit bedingter Formatierung, dazu ist eigentlich nur eine zusätzliche Spalte erforderlich (hier Spalte C).

Hier zunächst einmal die Ansicht der "ungefilterten Liste". In B3 kann man ein Gültigkeitsdropdown zur Auswahl von Art1, Art2 oder Art3 einfügen. Oder zum Filtern dies per Hand dort eingeben. Der Trick liegt darin, dass durch die bedingte Formatierung wenn B3<>"" die Zellen in Spalten D:F die Schriftfarbe gemäß Hintergrundfarbe annehmen. Ungefiltert sind die Inhalte von C2:C3 unsichtbar. Die Formel aus C6 ist nach unten kopierbar, ebenfalls das Format aus Zeile 6 in Spalten D:F. In Zeile 5 ist ja aufgrund der Hintergrundfarbe der Überschrift eine andere Schriftfarbe ausgewählt, deshalb darf die nicht runterkopiert werden:

A B C D E F G 1               2   Auswahl (D5:F5):           3               4               5   Artikel   Art1 Art2 Art3   6   Korb   41 37 99   7   Platte   80 58 73   8   Suppenteller   17 88 75   9   Suppentasse   32 14 83   10   Kanne   32 43 21   11               Formeln der Tabelle C2 : =WENN(B3="";"";"Summe:") C3 : =WENN(B3="";"";TEILERGEBNIS(9;C6:C10)) C5 : =WENN(B3="";"";B3) C6 : =WENN($B$3="";"";INDIREKT(ADRESSE(ZEILE();VERGLEICH($B$3;$D$5:$F$5;0)+3))) Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format C2 1. / Formel ist =B$3="" Abc C3 1. / Formel ist =B$3="" Abc D5 1. / Formel ist =$B$3<>"" Abc E5 1. / Formel ist =$B$3<>"" Abc F5 1. / Formel ist =$B$3<>"" Abc D6 1. / Formel ist =$B$3<>"" Abc E6 1. / Formel ist =$B$3<>"" Abc F6 1. / Formel ist =$B$3<>"" Abc

"Gefiltert" in B3 präsentiert sich die gleiche Liste dann z.B. so (die Formeln habe ich hier nicht wiederholt, die sind wie oben):

A B C D E F G 1               2   Auswahl (D5:F5): Summe:         3   Art2 240         4               5   Artikel Art2         6   Korb 37         7   Platte 58         8   Suppenteller 88         9   Suppentasse 14         10   Kanne 43         11

Durch Leeren der Zelle B3 leert sich optisch Spalte C und die Inhalte aus Spalten D:F werden wieder sichtbar.