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

Bedingte Formatierung (2) - Beispiele allgemein

Autor: Beate Schmitz - Erstellt: 2005-11      - Letzte Revision: ?Gruppenthema: 3 Folgen 1 2 3 Sie sind in Folge:2
Zum Blattanfang

Kennzeichnung von Dubletten bzw. Mehrfachvorkommenden

Man kann die Eingabe von Dubletten vermeiden, wenn man dies über Menü>Daten>Gültigkeit beschränkt. Manchmal kann es aber gewollt sein, Dubletten zuzulassen - aber sie sollen zumindest auffallen. Dafür markiert man den Bereich, der auf Dubletten verglichen werden soll und bedient sich der Möglichkeit, die Bedingung per Formel in der bedingten Formatierung zu definieren.

Sie finden nachstehend Beispiele zu: Zum Blattanfang Zum Themenanfang

Dubletten bzw. Mehrfachvorkommen farbig markieren

Das Format in B2 ist kopierbar, Sie müssen nur den Bereich entsprechend anpassen:

 
 ABCDE
1     
2 JohnJoanGeorge 
3 MikeG`HazelLisa 
4 AaronEstherBob 
5 DamonLoryJonnie 
6 BetsyWalterMilli 
7 JaneCarolJoe 
8 JoanJamesMarion 
9 BillTimBob 
10 BobJackLester 
11     
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B21. / Formel ist =ZÄHLENWENN($B$2:$D$10;B2)>1Abc
 

 
Formelbeispiele:
(Hier wäre die erste Zelle immer B2)
Vergleich mit dem gesamten Tabellenblatt=ZÄHLENWENN($1:$65536;B2)>1
Vergleich mit einem Bereich=ZÄHLENWENN($B$2:$D$10;B2)>1
Vergleich innerhalb einer Spalte=ZÄHLENWENN($B:$B;B2)>1
Vergleich innerhalb einer Zeile=ZÄHLENWENN($2:$2;B2)>1
 


Zum Blattanfang Zum Themenanfang

Erstmalig auftretende Werte werden farblich hinterlegt

Das Format aus B2 kann nach unten kopiert werden:

 
 ABC
1   
2 5 
3 10 
4 5 
5 25 
6 5 
7 8 
8 5 
9 8 
10 7 
11   
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B21. / Formel ist =ZÄHLENWENN($B$2:$B2;$B2)=1Abc
 


Zum Blattanfang Zum Themenanfang

Dubletten über 2 Dateien farblich hinterlegen

Grundvoraussetzung für die Anzeige ist, dass beide Dateien geöffnet sind.

Aber zunächst müssen Sie die bedingte Formatierung einbauen. Dafür definieren Sie Namen, in Datei1.xls z.B. den Namen Dat1 für den Bereich und in Datei2.xls z.B. den Namen Dat2 für den zu vergleichenden Bereich.

Dann markieren Sie den Bereich und geben die Formel in der bedingten Formatierung ein, dabei wäre E1 die erste Zelle im Bereich - und suchen im Anschluss das gewünschte Format aus:
=ZÄHLENWENN(Dat1;E1)+ZÄHLENWENN(Dat2;E1)>1


Zum Blattanfang Zum Themenanfang

Dubletten über viele Blätter kenntlich machen

Für alle Tabellen werden zunächst Namen mit den gleichen Bereichen definiert (z.B. Tab1, Tab2, Tab3).

Die bedingte Formatierung kann man vereinfachen, indem man die Mehrfachmarkierung verwendet. Das geht so: Klicken Sie auf den ersten Tabellenreiter, drücken die Umschalt-Taste und klicken gleichzeitig auf den letzten Tabellenreiter, nun sind alle Tabellenreiter markiert. Jetzt markieren Sie einfach den Tabellenbereich z.B. D11:O105 und tragen in der bedingten Formatierung unter "Formel ist" folgende Formel ein:

=ZÄHLENWENN(Tab1;D11)+ZÄHLENWENN(Tab2;D11)+ZÄHLENWENN(Tab3;D11)+ZÄHLENWENN(Tab4;D11)>1

Das Format wählen und mit OK bestätigen.

Zum Blattanfang Zum Themenanfang

Doppelbelegung bei zeitlicher Überschneidung - Raumplanung

Überschneidungen der einzelnen Aufenthaltszeiten bei gleichem Zimmer werden hier mit bedingter Formatierung eingefärbt. Das Format aus Zeile 3 kann nach unten kopiert werden:

 
 ABCDEFG
1       
2 Aufenthalt vonAufenthalt bisZimmerNameDoppelbelegung 
3 15.06.200514.08.200523Fr.MaurerDoppelbelegung! 
4 13.12.200515.12.200527Hr.Sohn  
5 01.09.200516.09.200521Fr.Winter  
6 03.08.200523.08.200518Hr.Mohn  
7 01.08.200514.08.200523Fr. NeussDoppelbelegung! 
8       
Formeln der Tabelle
F3 : =WENN(SUMMENPRODUKT(($B$3:$B$7<=C3)*($C$3:$C$7>=B3)*($D$3:$D$7=D3))>1;"Doppelbelegung!";"")
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B31. / Formel ist =SUMMENPRODUKT(($B$3:$B$7<=$C3)*($C$3:$C$7>=$B3)*($D$3:$D$7=$D3))>1Abc
C31. / Formel ist =SUMMENPRODUKT(($B$3:$B$7<=$C3)*($C$3:$C$7>=$B3)*($D$3:$D$7=$D3))>1Abc
 



Zum Blattanfang Zum Themenanfang

Wochentage bedingt formatieren

Jeder Wochentag hat in Excel eine Kennzahl, die wir mit der Funktion WOCHENTAG ermitteln können; dies nutzen wir für die bedingte Formatierung.

Die Formeln in Zeile 3 sowie für die Formate aus B3:C3 können nach unten kopiert werden:
 
 ABCDE
1     
2  Spalte C ist hier benutzerdefiniert formatiert - Format TTT - in der Formel der bedingten Formatierung wird über WOCHENTAG die Kennzahl ermitteltHilfsspalte zur Demonstration der WOCHENTAG-Funktion;
(der Parameter 2 führt dazu, dass Montag die Kennzahl 1 erhält)
 
3 07.11.2005Mo1 
4 08.11.2005Di2 
5 09.11.2005Mi3 
6 10.11.2005Do4 
7 11.11.2005Fr5 
8 12.11.2005Sa6 
9 13.11.2005So7 
10 14.11.2005Mo1 
11     
Formeln der Tabelle
C3 : =B3
D3 : =WOCHENTAG(B3;2)
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B31. / Formel ist =WOCHENTAG($B3;2)=7Abc
B32. / Formel ist =WOCHENTAG($B3;2)=6Abc
C31. / Formel ist =WOCHENTAG($B3;2)=7Abc
C32. / Formel ist =WOCHENTAG($B3;2)=6Abc
 

Ebenso können Sie aber auch festlegen, dass z.B. Zellen von Dienstag bis Freitag bedingt formatiert werden:

 
 ABCD
1    
2  Spalte C ist hier benutzerdefiniert formatiert - Format TTT - in der Formel der bedingten Formatierung wird über WOCHENTAG die Kennzahl ermittelt 
3 07.11.2005Mo 
4 08.11.2005Di 
5 09.11.2005Mi 
6 10.11.2005Do 
7 11.11.2005Fr 
8 12.11.2005Sa 
9 13.11.2005So 
10 14.11.2005Mo 
11    
Formeln der Tabelle
C3 : =B3
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B31. / Formel ist =UND(WOCHENTAG($B3;2)>1;WOCHENTAG($B3;2)<6)Abc
C31. / Formel ist =UND(WOCHENTAG($B3;2)>1;WOCHENTAG($B3;2)<6)Abc
 

Dies als Beispiel, wie man Spannen über mehrere Tage über eine Formel bedingt formatieren kann. Spalte C wird dafür nicht benötigt, es reicht das Datum. Interessant ist sowas z.B. bei Schichtdienstkalendern.

Zum Blattanfang Zum Themenanfang

Quartale bedingt formatieren

Eine Datenspalte soll bedingt formatiert werden in Abhängigkeit, in welches Quartal das Datum fällt:

Das Format aus B3 kann nach unten kopiert werden:
 
 ABCDE
1     
2 Datum Farben für Quartale: 
3 09.10.2005 1. Quartal automatisch (keine Formel hinterlegt) 
4 21.08.2005 2. Quartal 
5 10.01.2005 3. Quartal 
6 24.12.2005 4. Quartal 
7 06.06.2005   
8 08.09.2005   
9 03.03.2005   
10 15.05.2005   
11 11.10.2005   
12     
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B31. / Formel ist =AUFRUNDEN(MONAT(B3)/3;0)=2Abc
B32. / Formel ist =AUFRUNDEN(MONAT(B3)/3;0)=3Abc
B33. / Formel ist =AUFRUNDEN(MONAT(B3)/3;0)=4Abc
 


Zum Blattanfang Zum Themenanfang

Terminverfolgung

Sie möchten eine farbliche Unterlegung, wenn ein Termin eintritt, sich nähert oder überschritten ist?

Das Format in B3 ist kopierbar:
 
 ABCD
1    
2 Das Beispiel wurde am 15.11.2005 erstellt,
somit betrachten Sie dieses Datum als HEUTE()
 
3 15.11.2005Termin exakt erreicht ist 
4 02.12.2005Termin tritt in weniger als 30 Tagen ein 
5 01.11.2005Termin überschritten 
6 31.12.2005Termin liegt außerhalb der Bedingungen 
7    
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B31. / Formel ist =B3-HEUTE()<0Abc
B32. / Formel ist =B3=HEUTE()Abc
B33. / Formel ist =B3-HEUTE()<30Abc
 


Zum Blattanfang Zum Themenanfang

Terminüberschneidungen

Überschneidungen bei Von/Bis-Zeiträumen werden hier mit bedingter Formatierung eingefärbt.
Die Formate aus Zeile 3 können nach unten kopiert werden:

 
 ABCD
1    
2 vonbis 
3 15.06.200514.08.2005 
4 13.12.200515.12.2005 
5 01.09.200516.09.2005 
6 14.08.200523.08.2005 
7 01.08.200514.08.2005 
8    
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B31. / Formel ist =SUMMENPRODUKT(($B$3:$B$7<=$C3)*($C$3:$C$7>=$B3))>1Abc
C31. / Formel ist =SUMMENPRODUKT(($B$3:$B$7<=$C3)*($C$3:$C$7>=$B3))>1Abc
 


Zum Blattanfang Zum Themenanfang

Markierung, wenn Termin (Datum oder Uhrzeit) in bestimmtem Zeitraum liegt

Sie möchten Daten bedingt formatieren, die in einen bestimmten Zeitraum fallen:
  • Beispiel Spalte B + E: Um diese schnell ändern zu können, schreiben Sie die Von/Bis-Termine in Zellen, auf die sich in der bedingten Formatierung bezogen wird. Dies ist dynamischer, als die bedingte Formatierung nachzubearbeiten!
  • Beispiel Spalte C: Wenn Sie ein Datum in eine Formel direkt integrieren möchten, bedenken Sie, dass Sie dort nicht das Datum selbst sondern den entsprechenden Wert hineinschreiben!
Das Format aus Zeile 7 kann nach unten kopiert werden:

 
 ABCDEFG
1       
2 Datum Uhrzeit 
3 Anfangsdatum13.08.2005 Anfangszeit12:00:00 
4 Enddatum15.09.2005 Endzeit15:30:00 
5       
6 Dynamisch durch ZellbezugFix, da Daten in der Formel als Zahl Dynamisch durch Zellbezug  
7 31.08.200531.08.2005 15:15:00  
8 13.12.200513.12.2005 11:59:00  
9 01.09.200501.09.2005 09:15:00  
10 14.09.200514.09.2005 13:26:00  
11 01.08.200501.08.2005 23:18:00  
12 15.12.200515.12.2005 07:58:00  
13 16.09.200516.09.2005 14:37:00  
14 23.08.200523.08.2005 17:36:00  
15       
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B71. / Formel ist =UND(B7>=$C$3;B7<=$C$4)Abc
C71. / Formel ist =UND(C7>=38577;C7<=38610)Abc
E71. / Formel ist =UND(E7>=$C$2;E7<=$C$3)Abc
 


Zum Blattanfang Zum Themenanfang

Sortierte Daten abwechselnd farblich bedingt formatieren

Sie möchten Daten und eventuell alle belegten Spalten der entsprechenden Zeile nach Datensortierung farblich bedingt formatieren, damit man den Datenwechsel visualisiert? Das Format aus Zeile 3 kann nach unten kopiert werden. So geht es:

 ABCD
1    
2 DatumProjekt-Nr. 
3 25.10.20051 
4 27.10.20052 
5 30.10.20059 
6 30.10.20056 
7 01.11.20056 
8 04.11.20055 
9 04.11.20051 
10 06.11.20057 
11 06.11.20054 
12    

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B31. / Formel ist =REST(SUMMENPRODUKT(N($B$2:$B2<>$B$3:$B3));2)Abc
C31. / Formel ist =REST(SUMMENPRODUKT(N($B$2:$B2<>$B$3:$B3));2)Abc

Excel behandelt Daten als Zahlen, von daher können Sie diese Formel auch einsetzen, um sortierte Werte bedingt abwechselnd (z. B. bei Änderung einer Artikelnummer) zu formatieren.

Zum Blattanfang

Mehrere Kriterien in einer Bedingung (UND / ODER / UND-ODER)

Wie wir es aus aus WENN-Funktionen kennen, können wir auch bei der bedingten Formatierung mehrere Kriterien in einer Formel kombinieren. Das Wort WENN entfällt jedoch, da die bedingte Formatierung ja grundsätzlich von WENN ausgeht. Es können mehrere UND- oder ODER-Bedingungen aneinandergereiht werden oder sogar beide Funktionen miteinander verknüpft werden. Hier einige Beispiele:
 
 ABCDEFG
1       
2 Formel ist… 
3 UND-Beispiele Oder-Beispiele 
4 Werte in Spalte C entsprechend B4 und B5? Werte in Spalte F entsprechend E4 oder E5? 
5 77 216 
6 715 1221 
7 Daten in Spalte C zwischen B8 und B9? Daten in Spalte F gleich E8 oder E9? 
8 15.08.200508.09.2005 06.06.200508.09.2005 
9 20.10.200506.06.2005 20.10.200506.06.2005 
10   
11 Formel ist… 
12 Und und ODER in einer Formel verbinden 
13 Werte in Spalte C >=B14 UND <=B15 ODER = 21 Daten in Spalte F zwischen E8 UND E9 ODER = 24.12.2005 
14 1521 08.06.200508.09.2005 
15 1936 20.10.200506.06.2005 
16  18  24.12.2005 
17       
18 Zellwert ist… 
19 UND-Beispiel Oder-Beispiel 
20 Werte in Spalte B zwischen 14 und 20? Zellwerte erlauben kein ODER, dann muss das in eine Formel gepackt werden. 
21 15  
22 21  
23       
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
C51. / Formel ist =UND(C5=B$5;C5=B$6)Abc
F51. / Formel ist =ODER(F5=E$5;F5=E$6)Abc
C61. / Formel ist =UND(C6=B$5;C6=B$6)Abc
F61. / Formel ist =ODER(F6=E$5;F6=E$6)Abc
C81. / Formel ist =UND(C8>=B$8;C8<=B$9)Abc
F81. / Formel ist =ODER(F8=E$8;F8=E$9)Abc
C91. / Formel ist =UND(C9>=B$8;C9<=B$9)Abc
F91. / Formel ist =ODER(F9=E$8;F9=E$9)Abc
C141. / Formel ist =ODER(UND(C14>=B$14;C14<=B$15);C14=21)Abc
F141. / Formel ist =ODER(UND(F14>=E$14;F14<=E$15);F14=38710)Abc
C151. / Formel ist =ODER(UND(C15>=B$14;C15<=B$15);C15=21)Abc
F151. / Formel ist =ODER(UND(F15>=E$14;F15<=E$15);F15=38710)Abc
C161. / Formel ist =ODER(UND(C16>=B$14;C16<=B$15);C16=21)Abc
F161. / Formel ist =ODER(UND(F16>=E$14;F16<=E$15);F16=38710)Abc
B211. / Zellwert ist zwischen 15 und 20Abc
B221. / Zellwert ist zwischen 15 und 20Abc
 



Zum Blattanfang Zum Themenanfang

Teilstring enthalten?

Wenn Sie Zellen bedingt formatieren möchten, die einen bestimmten String enthalten, bieten sich die Funktionen SUCHEN und FINDEN an. Wie Sie im Beitrag Groß - Klein in Funktionen nachlesen können, muss unterschieden werden, ob Groß- und Kleinschreibung beachtet werden soll oder nicht.

FINDEN arbeitet also im Gegensatz zu SUCHEN CaseSensitiv, d.h. Groß- und Kleinschreibung werden berücksichtigt.

Als Information am Rande:
Excel verfügt nur über vier Funktionen, die CaseSensitiv sind, nämlich:
IDENTISCH - FINDEN - CODE - WECHSELN

Das Format aus Zeile 5 kann nach unten kopiert werden. Wie verschieden sich die beiden Funktionen auswirken, sehen Sie an diesem Beispiel:
 
 ABCD
1    
2 Suchstring:Haus 
3 FINDENSUCHEN 
4Groß-Kleinschreibung wird beachtet!Groß-Kleinschreibung wird nicht beachtet!
5 HausmausHausmaus 
6 HexenhausHexenhaus 
7 ReihenhausReihenhaus 
8 HausHaus 
9 DoppelhausDoppelhaus 
10 TaxiTaxi 
11 GartenhausGartenhaus 
12 HausfrauHausfrau 
13    
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B51. / Formel ist =FINDEN($C$2;$B5)Abc
C51. / Formel ist =SUCHEN($C$2;$C5)Abc
 


Zum Blattanfang Zum Themenanfang

Übereinstimmung mit Suchstring

Wenn Sie nur Zellen bedingt formatieren möchten, die genau dem Suchstring entsprechen, müssen Sie ebenso wie in obigem Beispiel beachten, ob Sie nach Groß- und Kleinschreibung unterscheiden möchten.

Das Formate aus Zeile 3 kann nach unten kopiert werden. Die verschiedenen Auswirkungen erkennen Sie an folgendem Beispiel:
 
 ABCDE
1     
2 Groß-Kleinschreibung wird nicht beachtet! Beide Formeln liefern identische Ergebnisse!Groß-Kleinschreibung wird beachtet!
Deshalb hier ein anderes Ergebnis
 
3 HausmausHausmausHausmaus 
4 HexenhausHexenhausHexenhaus 
5 ReihenhausReihenhausReihenhaus 
6 HausHausHaus 
7 DoppelhausDoppelhausDoppelhaus 
8 haushaushaus 
9 GartenhausGartenhausGartenhaus 
10 HausfrauHausfrauHausfrau 
11     
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B31. / Formel ist =B3="Haus"Abc
C31. / Formel ist =ZÄHLENWENN(C3;"Haus")Abc
D31. / Formel ist =IDENTISCH(D3;"Haus")Abc
 


Zum Blattanfang Zum Themenanfang

Enthält eine Zelle einen Suchstring, der in einem bestimmten Bereich vorkommt?

Eine Zelle soll bedingt formatiert werden, wenn sie einen String enthält, der in einem Bereich vorkommt.

Hier wollen wir untersuchen, ob der Suchstring aus dem Bereich B4:B8 in Spalte C (hier wird nicht zwischen Groß- und Kleinschreibung unterschieden) bzw. Spalte D (hier wird zwischen Groß- und Kleinschreibung unterschieden) enthalten ist.

Normalerweise müßte der Bereich B4:B8 als Matrix (mit geschweifter Klammer) eingegeben werden - doch bei der bedingten Formatierung ist dies überflüssig. Die Bereichsangabe erspart uns umfangreiche ODER-Konstrukte in der Formel, der Bereich kann beliebig groß sein.

Der "Suchstring" kann Text oder Werte enthalten. Aber Vorsicht mit Werten: Die Suche nach "15" würde sowohl 15 als auch alle Werte, in denen die Zahlenfolge 15 enthalten ist, bedingt formatieren.

Wichtig ist jedoch, ob im Bereich Leerzellen vorkommen, daher für beide Varianten Beispiele:

Beispiel 1: Im Bereich B4:B8 können keine Leerzellen vorkommen

 ABCDEF
1      
2  Groß- und Kleinschreibung wird nicht berücksichtigt
Funktion: SUCHEN
Groß- und Kleinschreibung wird berücksichtigt
Funktion: FINDEN
  
3 SuchstringUntersuchte ZellenUntersuchte ZellenBemerkung: 
4 TigerHunde, die bellen, beißen nicht.Hunde, die bellen, beißen nicht.Hunde werden markiert, weil "Hund" enthalten ist. 
5 LöwenKatzen fressen Mäuse.Katzen fressen Mäuse.  
6 GutDas Gut umfassst 400 ha.Das Gut umfassst 400 ha.  
7 PferdEs ist gut, dass die Sonne scheintEs ist gut, dass die Sonne scheintHier macht sich der Unterschied zwischen SUCHEN und FINDEN bemerkbar. 
8 HundDer Löwe gähnt.Der Löwe gähnt.Löwe wird nicht markiert, weil "Löwen" gesucht wurde 
9      

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
C41. / Formel ist =ANZAHL(SUCHEN($B$4:$B$8;C4))Abc
D41. / Formel ist =ANZAHL(FINDEN($B$4:$B$8;D4))Abc

Die Formeln aus Zeile 4 können nach unten kopiert werden.


Beispiel 2: Im Bereich B4:B8 können Leerzellen vorkommen

 ABCDEF
1      
2  Groß- und Kleinschreibung wird nicht berücksichtigt
Funktion: SUCHEN
Groß- und Kleinschreibung wird berücksichtigt
Funktion: FINDEN
  
3 SuchstringUntersuchte ZellenUntersuchte ZellenBemerkung: 
4 TigerHunde, die bellen, beißen nicht.Hunde, die bellen, beißen nicht.Hunde werden markiert, weil "Hund" enthalten ist. 
5 LöwenKatzen fressen Mäuse.Katzen fressen Mäuse.  
6 GutDas Gut umfassst 400 ha.Das Gut umfassst 400 ha.  
7  Es ist gut, dass die Sonne scheintEs ist gut, dass die Sonne scheintHier macht sich der Unterschied zwischen SUCHEN und FINDEN bemerkbar. 
8 HundDer Löwe gähnt.Der Löwe gähnt.Löwe wird nicht markiert, weil "Löwen" gesucht wurde 
9      

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
C41. / Formel ist =ANZAHL(SUCHEN($B$4:$B$8;C4)/($B$4:$B$8<>""))Abc
D41. / Formel ist =ANZAHL(FINDEN($B$4:$B$8;D4)/($B$4:$B$8<>""))Abc

Die Formeln aus Zeile 4 können nach unten kopiert werden.


Zum Blattanfang Zum Themenanfang

Enthält ein String an bestimmter Stelle ein bestimmtes Zeichen?

Sie möchten Zellen bedingt formatieren, die ein bestimmtes Zeichen an einer bestimmten Stringposition aufweisen. Wieder haben Sie die Möglichkeit zu wählen, ob Klein- und Großschreibung beachtet werden soll oder nicht.

 
 ABCDE
1     
2 Groß-Kleinschreibung wird nicht beachtet!Groß-Kleinschreibung wird beachtet!  
3 SUCHENFINDEN  
4 ein W hierein W hierEin "w" wird an Position 5 gesucht (kleingeschrieben in der Formel) 
5 ein W hierein W hierEin "W" wird an Position 5 gesucht (großgeschrieben in der Formel) 
6 ALG5789ALG5789Eine 7 wird an Position 5 gesucht.
Kein Unterschied bei Zahlen zwischen FINDEN und SUCHEN
 
7 ALG5589ALG5589 
8 ein " hierein " hierEin " wird an Position 5 gesucht. Dafür wird ZEICHEN(34) verwandt, alternativ """" 
9     
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B41. / Formel ist =SUCHEN("w";TEIL(B4;5;1))Abc
C41. / Formel ist =FINDEN("w";TEIL(C4;5;1))Abc
B51. / Formel ist =SUCHEN("W";TEIL(B5;5;1))Abc
C51. / Formel ist =FINDEN("W";TEIL(C5;5;1))Abc
B61. / Formel ist =SUCHEN(7;TEIL(B6;5;1))Abc
C61. / Formel ist =FINDEN(7;TEIL(C6;5;1))Abc
B71. / Formel ist =SUCHEN(7;TEIL(B7;5;1))Abc
C71. / Formel ist =FINDEN(7;TEIL(C7;5;1))Abc
B81. / Formel ist =FINDEN(ZEICHEN(34);TEIL(B8;5;1))Abc
C81. / Formel ist =SUCHEN(ZEICHEN(34);TEIL(C8;5;1))Abc
 


Zum Blattanfang Zum Themenanfang

Enthält ein String in einem bestimmten Bereich bestimmte Zeichen?

Sie möchten Zellen bedingt formatieren, die bestimmte Zeichen in einem bestimmten Bereich aufweisen. Wieder haben Sie die Möglichkeit zu wählen, ob Klein- und Großschreibung beachtet werden soll oder nicht.

 
 ABCDE
1     
2 Groß-Kleinschreibung wird nicht beachtet!Groß-/Kleinschreibung wird beachtet!  
3 SUCHENIDENTISCH  
4 WetterWetterZellen, die ab 3. Stelle auf 2 Stellen Länge "tt" enthalten (kleingeschrieben in der Formel) 
5 LMTTE-150LMTTE-150 
6 ALG5789ALG5789Zellen, die in den letzten 3 Zeichen 789 enthalten - kein Unterschied bei Zahlen 
7 ALG5589ALG5589 
8 LehrerLehrerZellen, die von der 3. bis 5. Stelle den String "hre" enhalten (kleingeschrieben in der Formel) 
9 FAHRERFAHRER 
10     
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B41. / Formel ist =SUCHEN(TEIL(B4;3;2);"tt")Abc
C41. / Formel ist =IDENTISCH(TEIL(C4;3;2);"tt")Abc
B51. / Formel ist =SUCHEN(TEIL(B5;3;2);"tt")Abc
C51. / Formel ist =IDENTISCH(TEIL(C5;3;2);"tt")Abc
B61. / Formel ist =SUCHEN(RECHTS(B6;3);789)Abc
C61. / Formel ist =IDENTISCH(RECHTS(C6;3);789)Abc
B71. / Formel ist =SUCHEN(RECHTS(B7;3);789)Abc
C71. / Formel ist =IDENTISCH(RECHTS(C7;3);789)Abc
B81. / Formel ist =SUCHEN(TEIL(B8;3;3);"hre")Abc
C81. / Formel ist =IDENTISCH(TEIL(C8;3;3);"hre")Abc
B91. / Formel ist =SUCHEN(TEIL(B9;3;3);"hre")Abc
C91. / Formel ist =IDENTISCH(TEIL(C9;3;3);"hre")Abc
 


Zum Blattanfang Zum Themenanfang

Enthält ein String mit Leerzeichen in einem bestimmten Abschnitt bestimmte Zeichen?

Sie möchten herausfinden, ob ein regelmäßiger oder unregelmäßiger String (Zahlen und Text können gemischt sein), der durch Leerzeichen in 4 Abschnitte unterteilt ist, an einer der 4 Positionen eine bestimmte Zeichenfolge hat und die Zelle dann einfärben. Die einzelnen Abschnitte können auch verschieden lang sein. Erst die Beispieltabelle, danach der Weg - das Format aus Zelle B3 ist nach unten kopierbar:
 
 ABCDE
1     
2 Artikelnr.:Gesucht:an Pos.: 
3 125 UFGT 458x5 789ABRO3 
4 14 145 ABRO LOP   
5 1597 G657 8 1458   
6     
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B31. / Formel ist =INDEX(x;D$3)=C$3Abc
 

Am Anfang steht eine Namensdefinition:
  • Zunächst markieren Sie die erste Zelle des Bereichs, hier B3
  • Dann gehen Sie über Menü > Einfügen > Namen definieren.... und vergeben den Namen x in der Arbeitsmappe
  • Unter bezieht sich auf: folgende Formel eingeben, die Sie natürlich Ihrem Tabellennamen und aktiver Zelle anpassen müssen: =AUSWERTEN("{"&""""&WECHSELN(Tabelle1!$B3;" ";""""&"."&"""")&""""&"}")
  • Den Hinzufügen-Button anklicken und das Dialogfenstr mit OK verlassen
In Zelle C3 ist der Suchstring genannt, der gemäß Zelle D3 an 3. Position gefunden werden soll. Sie sind also durch die Zelleingabe dynamisch betreffend Suchstring und Position (im Beispiel bis 4 möglich).

Wollen Sie immer einen bestimmten String an einer bestimmten Position auswerten, können Sie das in die Formel der bedingten Formatierung integrieren, dann sind die beiden Zellen überflüssig. Beachten Sie, auch wenn Sie nach Zahlen suchen, müssen diese in Anführungszeichen stehen, da für Excel das Ergebnis der Namensauswertung Text ist:

 
 ABC
1   
2 Artikelnr.: 
3 125 UFGT 458x5 789 
4 14 145 ABRO LOP 
5 1597 G657 8 1458 
6   
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B31. / Formel ist =INDEX(x;2)="G657"Abc
 


Zum Blattanfang Zum Themenanfang

Der Umgang mit Sonderzeichen

Sollen Zellen bedingt formatiert werden, die Sonderzeichen enthalten, müssen besondere Regeln beachtet werden. Bei der Funktion FINDEN darf der Suchtext keine Platzhalter enthalten. Für die Funktion SUCHEN gilt:
  • Wird nach einem Anführungszeichen gesucht, so müssen vier Anführungszeichen in die Formel eingetragen werden. Oder man nutzt die Funktion ZEICHEN(34)
  • Eine als Suchtext angegebene Zeichenfolge darf die Platzhalterzeichen Fragezeichen (?) und Sternchen (*) enthalten. Ein Fragezeichen ersetzt ein Zeichen; ein Sternchen ersetzt eine beliebige Zeichenfolge
  • Suchen Sie nach einem Fragezeichen oder Sternchen, müssen Sie eine Tilde (~) vor das zu suchende Zeichen setzen
 
 ABCD
1    
2 Beispiele:Erklärung 
3 Ein " hierZellen, in denen ein " steht, sollen bedingt formatiert werden. 
4 Kein Anführungszeichen hier 
5 Ein * scheint hell.Zellen, in denen ein * steht, sollen bedingt formatiert werden. Das Sternchen hat hier keine Platzhalterfunktion; beachten Sie den verschiedenen Umgang mit SUCHEN und FINDEN. 
6 Ein Stern scheint hell. 
7 Ein * scheint hell. 
8 Ein Stern scheint hell. 
9 Ich bin ein ?Zellen, in denen ein ? steht, sollen bedingt formatiert werden. Das Fragezeichen hat hier keine Platzhalterfunktion; beachten Sie den verschiedenen Umgang mit SUCHEN und FINDEN. 
10 Ich bin ein Fragezeichen 
11 Ich bin ein ? 
12 Ich bin ein Fragezeichen 
13    
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B31. / Formel ist =FINDEN("""";B3)Abc
B41. / Formel ist =FINDEN("""";B4)Abc
B51. / Formel ist =FINDEN("*";B5)Abc
B61. / Formel ist =FINDEN("*";B6)Abc
B71. / Formel ist =SUCHEN("~*";B7)Abc
B81. / Formel ist =SUCHEN("~*";B8)Abc
B91. / Formel ist =FINDEN("?";B9)Abc
B101. / Formel ist =FINDEN("?";B10)Abc
B111. / Formel ist =SUCHEN("~?";B11)Abc
B121. / Formel ist =SUCHEN("~?";B12)Abc
 


Zum Blattanfang

Kennzeichnung von Min/Max-Werten - der x-größten und -kleinsten Werte

Sie finden nachstehend Beispiele zu:

Zum Blattanfang Zum Themenanfang

Größten und/oder kleinsten Wert in einem Bereich farblich unterlegen

In folgendem Beispiel wird der kleinste und der größte Wert eines Bereichs farblich unterlegt. Sie können das natürlich auch nur mit einer Bedingung umsetzen, also nur den größten oder kleinsten Wert bedingt formatieren. Das Format aus C3 ist kopierbar:
 
 ABCDEFG
1       
2 FirmaMüllerMayerSchmitzBecker 
3 06.11.200587,910,9825,22 
4 07.11.20055,58,41248,12 
5 08.11.200568,913,0271,02 
6 09.11.20056,59,414,0493,92 
7 10.11.200579,915,06116,82 
8 11.11.20057,510,416,08139,72 
9 12.11.200516,428,32414,52118,1 
10       
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
C31. / Formel ist =C3=MAX($C$3:$F$9)Abc
C32. / Formel ist =C3=MIN($C$3:$F$9)Abc
 


Zum Blattanfang Zum Themenanfang

Kennzeichnung von Min/Max-Werten - der x-größten und x-kleinsten Werte

In folgendem Beispiel wird der x-kleinsten und/oder die x-größten Wert eines Bereichs farblich unterlegt. Das Format aus Zeile 4 ist kopierbar:
 
 ABCDEFG
1       
2 die 3 größten Wertedie 3 kleinsten Wertedie 3 größten und 3 kleinsten Werte 
3 3 Bedingungen, verschieden Farben1 Bedingung, gleiche Farbe3 Bedingungen, verschieden Farben1 Bedingung, gleiche Farbe2 Bedingungen, pro Bedingung eine Farbe 
4 578,84578,84578,84578,84578,84 
5 479,71479,71479,71479,71479,71 
6 120,04120,04120,04120,04120,04 
7 583,91583,91583,91583,91583,91 
8 193,07193,07193,07193,07193,07 
9 92,5692,5692,5692,5692,56 
10 646646646646646 
11 348348348348348 
12       
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B41. / Formel ist =B4=KGRÖSSTE($B$4:$B$11;1)Abc
B42. / Formel ist =B4=KGRÖSSTE($B$4:$B$11;2)Abc
B43. / Formel ist =B4=KGRÖSSTE($B$4:$B$11;3)Abc
C41. / Formel ist =C4>= KGRÖSSTE($C$4:$C$11;3)Abc
D41. / Formel ist =D4=KKLEINSTE($D$4:$D$11;1)Abc
D42. / Formel ist =D4=KKLEINSTE($D$4:$D$11;2)Abc
D43. / Formel ist =D4=KKLEINSTE($D$4:$D$11;3)Abc
E41. / Formel ist =E4<= KKLEINSTE($E$4:$E$11;3)Abc
F41. / Formel ist =F4<= KKLEINSTE($F$4:$F$11;3)Abc
F42. / Formel ist =F4>= KGRÖSSTE($F$4:$F$11;3)Abc
 



Zum Blattanfang Zum Themenanfang

Farbe auslesen bei bedingter Formatierung

Sie möchten bei bedingter Formatierung die Farbe auslesen, egal, ob Hintergrundfarbe oder Schriftfarbe? Dies ist nicht so einfach. Aber es gibt einen leichten Ausweg:

Lesen Sie nicht die Farbe aus, sondern stellen Sie in einer Formel die Bedingung nach, die zur Farbgebung führt! Denn schließlich erfolgt die bedingte Formatierung ja, wie der Ausdruck sagt, unter einer Bedingung.

Das Format in B3 kann nach rechts kopiert werden:

Ein kleines Beispiel:
 
 ABCDEFGHIJ
1          
2 Hier in B3:F3 liegen die Zellen mit bedingter Formatierung:Hintergrundfarbe orange
Bedingung: Wert < 0
Hintergrundfarbe grün
Bedingung: Wert > 10
Hintergrundfarbe blau
Bedingung: Wert = 5
 
3 -1015-535211 
4          
Formeln der Tabelle
G3 : =ZÄHLENWENN(B3:F3;"<0")
H3 : =ZÄHLENWENN(B3:F3;">10")
I3 : =ZÄHLENWENN(B3:F3;"=5")
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B31. / Zellwert ist kleiner als 0Abc
B32. / Zellwert ist grösser als 10Abc
B33. / Zellwert ist gleich 5Abc
 

Sie müssen einfach nur die Formel Ihren Bedingungen anpassen!


Zum Blattanfang Zum Themenanfang

Auslesen der bedingten Formatierung
mind. 5 Zellen in einer Zeile treffen die Bedingung


Als Bedingung für rote Formatierung gilt <=10. In Spalte H wird ausgewertet, indem nicht wirklich die Farbe gezählt wird, sondern die Bedingung in Kombination mit ZÄHLENWENN nachgestellt wird.

Um die Übersicht zu wahren, zeige ich Ihnen nur die Formeln für die bedingte Formatierung und deren Auswertung in Spalte H von Zeile 3, diese sind nach unten kopierbar:

 
 ABCDEFGHI
1         
2 EingabebereichAuswertung 
3 5112107-5min. 5 rote Zellen 
4 23036424854  
5 8045127min. 5 rote Zellen 
6 1608102414  
7 11518212427  
8 322710128  
9 408-24-56-88-120min. 5 rote Zellen 
10 4816-16-48-80-112  
11 565452504846  
12 649218-56-130-204  
13 7213036-162-30812  
14 80354-268-48618  
15 882068-374-66429  
16         
Formeln der Tabelle
H3 : =WENN(ZÄHLENWENN(B3:G3;"<=10")>4;"min. 5 rote Zellen";"")
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B31. / Zellwert ist kleiner oder gleich 10Abc
 


Zum Blattanfang Zum Themenanfang

Auswertung über die bedingte Formatierung
mind. 5 Zellen mit gleicher Bedingung am Stück


Nachstehend möchte ich Ihnen eine weitere interessante Lösung zur bedingten Formatierung vorstellen.

Zellen mit Wert <=10 rot sollen werden. Wenn aber mind. 5 Zellen <=10 am Stück nebeneinder liegen, dann sollen diese grün werden.

Beachten Sie bitte:
  • Die Spalten B, C, G, H, haben verschiedene Formeln für die bedingte Formatierung
  • Die Spalten D, E, F haben alle die selbe Formel für die bedingte Formatierung
Die Formate in Zeile 3 sind nach unten kopierbar:

 
 ABCDEFGHIJK
1           
2 Eingabebereich Auswertung über die bedingte Formatierung 
3 14345818 rot = Zahl ist <= 10 
4 2303642485436 grün = 5 oder mehr Zahlen <=10 in Serie 
5 80451274   
6 16081024148   
7 5112107-512   
8 3227101287   
9 408-24-56-88-12050   
10 4816-16-48-80-112-16   
11 1545250484652   
12 649218-56-130-20418   
13 7213036-162-3081236   
14 80354-268-4861854   
15 882068-374-664298   
16           
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B31. / Formel ist =ZÄHLENWENN(B3:F3;"<=10")=5Abc
B32. / Zellwert ist kleiner oder gleich 10Abc
C31. / Formel ist =ODER(ZÄHLENWENN(C3:G3;"<=10")=5;ZÄHLENWENN(B3:F3;"<=10")=5)Abc
C32. / Zellwert ist kleiner oder gleich 10Abc
D31. / Formel ist =ODER(ZÄHLENWENN($C3:$G3;"<=10")=5;ZÄHLENWENN($B3:$F3;"<=10")=5;ZÄHLENWENN($A3:$E3;"<=10")=5)Abc
D32. / Zellwert ist kleiner oder gleich 10Abc
E31. / Formel ist =ODER(ZÄHLENWENN($C3:$G3;"<=10")=5;ZÄHLENWENN($B3:$F3;"<=10")=5;ZÄHLENWENN($A3:$E3;"<=10")=5)Abc
E32. / Zellwert ist kleiner oder gleich 10Abc
F31. / Formel ist =ODER(ZÄHLENWENN($C3:$G3;"<=10")=5;ZÄHLENWENN($B3:$F3;"<=10")=5;ZÄHLENWENN($A3:$E3;"<=10")=5)Abc
F32. / Zellwert ist kleiner oder gleich 10Abc
G31. / Formel ist =ODER(ZÄHLENWENN(D3:H3;"<=10")=5;ZÄHLENWENN(C3:G3;"<=10")=5;)Abc
G32. / Zellwert ist kleiner oder gleich 10Abc
H31. / Formel ist =ZÄHLENWENN(D3:H3;"<=10")=5Abc
H32. / Zellwert ist kleiner oder gleich 10Abc
 

Bei einer abweichenden Spaltenzahl müssen Sie natürlich auch die Formeln ändern, deshalb soll dies hier nur als Anregung dienen.

Zum Blattanfang Zum Themenanfang

Visualisierung von Gruppen mit Auswertungsmöglichkeit

Personen sollen in drei Gruppen eingeteilt werden, die man auf Anhieb farblich erkennt. Gleichzeitig will man auswerten, wieviele Teilnehmer in jeder Gruppe sind.

Die Lösung: In den Zellen B6:B10 liegt die bedingte Formatierung mit jeweils drei Bedingungen, dabei ist diese so angepasst, dass die Schriftfarbe der Hintergrundfarbe entspricht. Durch Eingabe von Zahlen zwischen 1 und 3 wird die Gruppe festgelegt und ist leicht veränderbar durch Zahleingabe. Vorteil hier zusätzlich: Die Spalte wäre filterbar trotz unsichtbarem Inhalt. Die sichtbare Auswertung erfolgt aber in den Zellen B2:B4 - um auch bei großen Gruppen die Teilnehmerzahl immer präsent zu haben. Wieder wird über ZÄHLENWENN nicht die Farbe, sondern die Bedingung ausgewertet.

Das Format in B6 kann nach unten kopiert werden. Ebenso die Formel in B2.

 
 ABCDEFGHI
1         
2 Gruppe hat 2 Teilnehmer 1 
3 Gruppe hat 1 Teilnehmer 2 
4 Gruppe hat 2 Teilnehmer 3 
5  NachnameVornameAdressePLZ   
6 1MusterMikeQuerstr. 160322   
7 3KleinAndeasAdalbertstr. 2360433   
8 3AkcaYasraBettinastr. 4260435   
9 2MüllerThorstenEichenweg 960232   
10 1dela TourMarieSchleusenweg 3460322   
11         
Formeln der Tabelle
B2 : ="Gruppe hat "&ZÄHLENWENN(B$6:B$10;ZEILE()-1)&" Teilnehmer"
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B61. / Zellwert ist gleich 1Abc
B62. / Zellwert ist gleich 2Abc
B63. / Zellwert ist gleich 3Abc
 


Zum Blattanfang Zum Themenanfang

Format zeigt an, wie oft ein Eintrag vorkommt

Sie möchten erreichen, dass die Zellen bedingt formatiert werden in Abhängigkeit davon, wie viele identische Zellen es im Bereich gibt. Dies erreichen Sie mit diesen Formeln, das Format aus Zelle B2 ist kopierbar nach rechts und unten, den Bereich in der Formel müssen Sie natürlich ihren Bedingungen anpassen.
 
 ABCDEFGHI
1         
2 513121512 Farben für Werte, die 
3 5199175 1 mal vorkommen: automatisch (keine Formel hinterlegt) 
4 1221116 2 mal vorkommen 
5 820890 3 mal vorkommen 
6 912711 öfter als 3 mal vorkommen 
7         
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B21. / Formel ist =(ZÄHLENWENN($B$2:$F$6;B2)>3)Abc
B22. / Formel ist =ZÄHLENWENN($B$2:$F$6;B2)=3Abc
B23. / Formel ist =ZÄHLENWENN($B$2:$F$6;B2)=2Abc
 


Zum Blattanfang Zum Themenanfang

Format zeigt an, in welchen Zahlenbereich eine Zahl fällt

Sie möchten erreichen, dass die Zellen bedingt formatiert werden in Abhängigkeit davon, in welchen Wertebereich sie fallen. Dies können sie über den Zellwert festlegen. Das Format aus Zelle B2 ist kopierbar nach rechts und unten.

 
 ABCDEFGHI
1         
2 51312-1212 Farben für Werte, die 
3 -121-89175 <-10 (automatisch (keine Formel hinterlegt) 
4 -122111-17 >=-10 und 0 
5 8-38-150 >=0 und <=10 
6 9127-61 >10 
7         
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B21. / Zellwert ist grösser als 10Abc
B22. / Zellwert ist zwischen 0 und 10Abc
B23. / Zellwert ist zwischen 0 und -10Abc
 


Zum Blattanfang Zum Themenanfang

Wertebereich dynamisieren

Sie können Wertebereiche auch dynamisch halten, indem Sie diese in Zellen schreiben und in der Formel auf diese Zellen Bezug nehmen, das Format aus Zeile 5 kann nach unten kopiert werden:

 
 ABCDE
1     
2 Kriterien 
3 >2121 
4 < 26 
5  2020 
6  2121 
7  2222 
8  2323 
9  2424 
10  2525 
11  2626 
12  2727 
13  2828 
14  2929 
15     
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
C51. / Zellwert ist grösser als ="$C$3"Abc
D51. / Zellwert ist zwischen ="$D$3" und ="$D$4"Abc
 



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
ToDo
Google Werbung