Autor: Beate Schmitz  --- Aus Excel Standard - Gruppe: Spezielle Menübefehle

Bedingte Formatierung (3) - Weitere Anwendungsbeispiele

Autor: Beate Schmitz - Erstellt: 2005-11      - Letzte Revision: ?Gruppenthema: 3 Folgen 1 2 3 Sie sind in Folge:3
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:

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:

 
 ABCDEFGHI
1         
2 3152415 
3         
4         
5         
6         
7         
8         
9         
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B41. / Formel ist =8-ZEILE()<B$2Abc
 


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:
 
 ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1                               
2 Arbeitszeit Arbeitszeit       
3 von bis [Std:Min]6h7h8h9h10 h11 h 
4 07:30-10:00 02:300                        
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
ZelleNr.: / BedingungFormat
G41. / Zellwert ist gleich 0Abc
G42. / Zellwert ist gleich 1Abc
 


Zum Blattanfang

Zeilenformatierung

Sie finden nachstehend Beispiele zu:

Zum Blattanfang Zum Themenanfang

Gerade Zeilen farbig unterlegen

Das Format aus Zeile 2 ist kopierfähig.

 
 ABCDE
1     
2     
3     
4     
5     
6     
7     
8     
9     
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B21. / Formel ist =ZEILE()=GERADE(ZEILE())Abc
C21. / Formel ist =ZEILE()=GERADE(ZEILE())Abc
D21. / Formel ist =ZEILE()=GERADE(ZEILE())Abc
 


Zum Blattanfang Zum Themenanfang

Ungerade Zeilen farbig unterlegen

Das Format aus Zeile 2 ist kopierfähig.

 
 ABCDE
1     
2     
3     
4     
5     
6     
7     
8     
9     
10     
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B21. / Formel ist =ZEILE()<>GERADE(ZEILE())Abc
C21. / Formel ist =ZEILE()<>GERADE(ZEILE())Abc
D21. / 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:

 ABCDE
1     
2 035 
3 954 
4 7814 
5 9104 
6 14117 
7     

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B21. / 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 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:
 
 ABCD
1    
2 #NVgezielt 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
ZelleNr.: / BedingungFormat
B21. / Formel ist =ISTNV(B2)Abc
B31. / Formel ist =ISTFEHLER(B3)Abc
B41. / 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:
 
 ABC
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
ZelleNr.: / BedingungFormat
B21. / Formel ist =LÄNGE(B2)>255Abc
 

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):

 
 ABC
1   
2 Textzahlen z.B. <=10: 
3 5 
4 7 
5 12 
6   
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B31. / Formel ist =B3*1<=10Abc
 


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:
 
 ABCDEFG
1       
2 Auswahl (D5:F5):     
3       
4       
5 Artikel Art1Art2Art3 
6 Korb 413799 
7 Platte 805873 
8 Suppenteller 178875 
9 Suppentasse 321483 
10 Kanne 324321 
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
ZelleNr.: / BedingungFormat
C21. / Formel ist =B$3=""Abc
C31. / Formel ist =B$3=""Abc
D51. / Formel ist =$B$3<>""Abc
E51. / Formel ist =$B$3<>""Abc
F51. / Formel ist =$B$3<>""Abc
D61. / Formel ist =$B$3<>""Abc
E61. / Formel ist =$B$3<>""Abc
F61. / 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):
 
 ABCDEFG
1       
2 Auswahl (D5:F5):Summe:    
3 Art2240    
4       
5 ArtikelArt2    
6 Korb37    
7 Platte58    
8 Suppenteller88    
9 Suppentasse14    
10 Kanne43    
11       
 

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

Fragen stellen im Online-Excel Forum

Neu in Online-Excel: Online-Excel Newsletter

Dieses Thema hat weitere Beiträge

Bedingte Formatierung (1) - Grundlagen
Bedingte Formatierung (2) - Beispiele allgemein
Bedingte Formatierung (3) - Weitere Anwendungsbeispiele

Weitere Artikel der Gruppe: Spezielle Menübefehle Aus Excel Standard
Nach oben