Bedingte formatierung zellwert gleich einer matrix

Zum Inhalt springen

Sie können sich in Excel automatisch die Zellen eines Bereichs visuell hervorheben lassen, die einen Fehlerwert (#DIV/0!, #NAME?, #NV, etc.) enthalten.

Abbildung: Tabelle mit Fehlermeldungen

Wenn Sie alle Zellen, welche einen Fehler enthalten zum Beispiel durch einen gelben Zellhintergrund kennzeichnen möchten, dann können Sie wie folgt vorgehen.

So geht´s:

Markieren Sie den entsprechenden Bereich, in dem Sie die fehlerhaften Zellen durch einen gelben Zellhintergrund hervorheben wollen (A1:D7).
Aktivieren Sie im Menüband die Befehlsfolge Registerkarte Start > Befehlsgruppe Formatvorlagen > Befehl Bedingte Formatierung.

Abbildung: Bedingte Formatierung im Menüband

Wählen Sie nun aus der sich öffnenden Befehlsliste den Eintrag Neue Regel aus.

Abbildung: Neue Regel auswählen

Wählen Sie im Dialogfeld Neue Formatierungsregel den Regeltyp „Nur Zellen formatieren, die enthalten“ aus.
Als nächstes wählen Sie bitte im Listenfeld „Nur Zellen formatieren mit:“ den Listeneintrag Fehler aus.

Abbildung: Bedingungen für die Formatierung festlegen

Nun müssen Sie das Format (gelber Zellhintergrund) definieren, das auf die Zellen angewendet werden soll, die einen Fehlerwert als Inhalt haben. Klicken Sie hierzu auf die Schaltfläche Formatieren.
Es öffnet sich das Dialogfeld Zellen formatieren. Aktivieren Sie hier nun die Registerkarte Ausfüllen und wählen dort die Farbe Gelb aus.
Schließen Sie nun die offenen Dialogfelder, indem Sie auf OK klicken.

Abbildung: Die fehlerhaften Zellen wurden durch die bedingte Formatierung gelb eingefärbt

Weiterführende Links:

• Weitere Beiträge zur Bedingten Formatierung
• Weitere Beiträge zu Fehlerwerten

Kennen Sie weiterführende Links zu diesem Thema? Schreiben Sie doch Ihre Linkempfehlung als Kommentar unten zu dieser Seite. Ich freue mich auf Ihr Feedback.

Beitrags-Navigation

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 Zum Themenanfang

Erstmalig auftretende Werte werden farblich hinterlegt

Das Format aus B2 kann nach unten kopiert werden:

A B C 1       2   5   3   10   4   5   5   25   6   5   7   8   8   5   9   8   10   7   11       Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B2 1. / Formel ist =ZÄHLENWENN($B$2:$B2;$B2)=1 Abc

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:

A B C D E F G 1               2   Aufenthalt von Aufenthalt bis Zimmer Name Doppelbelegung   3   15.06.2005 14.08.2005 23 Fr.Maurer Doppelbelegung!   4   13.12.2005 15.12.2005 27 Hr.Sohn     5   01.09.2005 16.09.2005 21 Fr.Winter     6   03.08.2005 23.08.2005 18 Hr.Mohn     7   01.08.2005 14.08.2005 23 Fr. Neuss Doppelbelegung!   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 Zelle Nr.: / Bedingung Format B3 1. / Formel ist =SUMMENPRODUKT(($B$3:$B$7<=$C3)*($C$3:$C$7>=$B3)*($D$3:$D$7=$D3))>1 Abc C3 1. / Formel ist =SUMMENPRODUKT(($B$3:$B$7<=$C3)*($C$3:$C$7>=$B3)*($D$3:$D$7=$D3))>1 Abc

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:

A B C D E 1           2     Spalte C ist hier benutzerdefiniert formatiert - Format TTT - in der Formel der bedingten Formatierung wird über WOCHENTAG die Kennzahl ermittelt Hilfsspalte zur Demonstration der WOCHENTAG-Funktion; (der Parameter 2 führt dazu, dass Montag die Kennzahl 1 erhält)   3   07.11.2005 Mo 1   4   08.11.2005 Di 2   5   09.11.2005 Mi 3   6   10.11.2005 Do 4   7   11.11.2005 Fr 5   8   12.11.2005 Sa 6   9   13.11.2005 So 7   10   14.11.2005 Mo 1   11           Formeln der Tabelle C3 : =B3 D3 : =WOCHENTAG(B3;2) Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B3 1. / Formel ist =WOCHENTAG($B3;2)=7 Abc B3 2. / Formel ist =WOCHENTAG($B3;2)=6 Abc C3 1. / Formel ist =WOCHENTAG($B3;2)=7 Abc C3 2. / Formel ist =WOCHENTAG($B3;2)=6 Abc

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

A B C D 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.2005 Mo   4   08.11.2005 Di   5   09.11.2005 Mi   6   10.11.2005 Do   7   11.11.2005 Fr   8   12.11.2005 Sa   9   13.11.2005 So   10   14.11.2005 Mo   11         Formeln der Tabelle C3 : =B3 Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B3 1. / Formel ist =UND(WOCHENTAG($B3;2)>1;WOCHENTAG($B3;2)<6) Abc C3 1. / 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:

A B C D E 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 Zelle Nr.: / Bedingung Format B3 1. / Formel ist =AUFRUNDEN(MONAT(B3)/3;0)=2 Abc B3 2. / Formel ist =AUFRUNDEN(MONAT(B3)/3;0)=3 Abc B3 3. / Formel ist =AUFRUNDEN(MONAT(B3)/3;0)=4 Abc

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:

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

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:

A B C D 1         2   von bis   3   15.06.2005 14.08.2005   4   13.12.2005 15.12.2005   5   01.09.2005 16.09.2005   6   14.08.2005 23.08.2005   7   01.08.2005 14.08.2005   8         Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B3 1. / Formel ist =SUMMENPRODUKT(($B$3:$B$7<=$C3)*($C$3:$C$7>=$B3))>1 Abc C3 1. / Formel ist =SUMMENPRODUKT(($B$3:$B$7<=$C3)*($C$3:$C$7>=$B3))>1 Abc

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:

DatumUhrzeit   A B C D E F G 1               2       3   Anfangsdatum 13.08.2005   Anfangszeit 12:00:00   4   Enddatum 15.09.2005   Endzeit 15:30:00   5               6   Dynamisch durch Zellbezug Fix, da Daten in der Formel als Zahl   Dynamisch durch Zellbezug     7   31.08.2005 31.08.2005   15:15:00     8   13.12.2005 13.12.2005   11:59:00     9   01.09.2005 01.09.2005   09:15:00     10   14.09.2005 14.09.2005   13:26:00     11   01.08.2005 01.08.2005   23:18:00     12   15.12.2005 15.12.2005   07:58:00     13   16.09.2005 16.09.2005   14:37:00     14   23.08.2005 23.08.2005   17:36:00     15               Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B7 1. / Formel ist =UND(B7>=$C$3;B7<=$C$4) Abc C7 1. / Formel ist =UND(C7>=38577;C7<=38610) Abc E7 1. / 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:

	A	B	C	D
1
2		Datum	Projekt-Nr.
3		25.10.2005	1
4		27.10.2005	2
5		30.10.2005	9
6		30.10.2005	6
7		01.11.2005	6
8		04.11.2005	5
9		04.11.2005	1
10		06.11.2005	7
11		06.11.2005	4
12

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format B3 1. / Formel ist =REST(SUMMENPRODUKT(N($B$2:$B2<>$B$3:$B3));2) Abc C3 1. / 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:

Formel ist�UND-BeispieleOder-BeispieleWerte in Spalte C entsprechend B4 und B5?Werte in Spalte F entsprechend E4 oder E5?Daten in Spalte C zwischen B8 und B9?Daten in Spalte F gleich E8 oder E9? Formel ist�Und und ODER in einer Formel verbindenWerte in Spalte C >=B14 UND <=B15 ODER = 21Daten in Spalte F zwischen E8 UND E9 ODER = 24.12.2005Zellwert ist�UND-BeispielOder-BeispielWerte in Spalte B zwischen 14 und 20?Zellwerte erlauben kein ODER, dann muss das in eine Formel gepackt werden.1521   A B C D E F G 1               2     3       4       5   7 7   21 6   6   7 15   12 21   7       8   15.08.2005 08.09.2005   06.06.2005 08.09.2005   9   20.10.2005 06.06.2005   20.10.2005 06.06.2005   10     11     12     13       14   15 21   08.06.2005 08.09.2005   15   19 36   20.10.2005 06.06.2005   16     18     24.12.2005   17               18     19       20       21       22       23               Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format C5 1. / Formel ist =UND(C5=B$5;C5=B$6) Abc F5 1. / Formel ist =ODER(F5=E$5;F5=E$6) Abc C6 1. / Formel ist =UND(C6=B$5;C6=B$6) Abc F6 1. / Formel ist =ODER(F6=E$5;F6=E$6) Abc C8 1. / Formel ist =UND(C8>=B$8;C8<=B$9) Abc F8 1. / Formel ist =ODER(F8=E$8;F8=E$9) Abc C9 1. / Formel ist =UND(C9>=B$8;C9<=B$9) Abc F9 1. / Formel ist =ODER(F9=E$8;F9=E$9) Abc C14 1. / Formel ist =ODER(UND(C14>=B$14;C14<=B$15);C14=21) Abc F14 1. / Formel ist =ODER(UND(F14>=E$14;F14<=E$15);F14=38710) Abc C15 1. / Formel ist =ODER(UND(C15>=B$14;C15<=B$15);C15=21) Abc F15 1. / Formel ist =ODER(UND(F15>=E$14;F15<=E$15);F15=38710) Abc C16 1. / Formel ist =ODER(UND(C16>=B$14;C16<=B$15);C16=21) Abc F16 1. / Formel ist =ODER(UND(F16>=E$14;F16<=E$15);F16=38710) Abc B21 1. / Zellwert ist zwischen 15 und 20 Abc B22 1. / Zellwert ist zwischen 15 und 20 Abc

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:

A B C D 1         2   Suchstring: Haus   3 FINDEN SUCHEN 4 Groß-Kleinschreibung wird beachtet! Groß-Kleinschreibung wird nicht beachtet! 5   Hausmaus Hausmaus   6   Hexenhaus Hexenhaus   7   Reihenhaus Reihenhaus   8   Haus Haus   9   Doppelhaus Doppelhaus   10   Taxi Taxi   11   Gartenhaus Gartenhaus   12   Hausfrau Hausfrau   13         Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B5 1. / Formel ist =FINDEN($C$2;$B5) Abc C5 1. / 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:

Groß-Kleinschreibung wird nicht beachtet! Beide Formeln liefern identische Ergebnisse!   A B C D E 1           2   Groß-Kleinschreibung wird beachtet! Deshalb hier ein anderes Ergebnis   3   Hausmaus Hausmaus Hausmaus   4   Hexenhaus Hexenhaus Hexenhaus   5   Reihenhaus Reihenhaus Reihenhaus   6   Haus Haus Haus   7   Doppelhaus Doppelhaus Doppelhaus   8   haus haus haus   9   Gartenhaus Gartenhaus Gartenhaus   10   Hausfrau Hausfrau Hausfrau   11           Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B3 1. / Formel ist =B3="Haus" Abc C3 1. / Formel ist =ZÄHLENWENN(C3;"Haus") Abc D3 1. / 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

	A	B	C	D	E	F
1
2			Groß- und Kleinschreibung wird nicht berücksichtigt Funktion: SUCHEN	Groß- und Kleinschreibung wird berücksichtigt Funktion: FINDEN
3		Suchstring	Untersuchte Zellen	Untersuchte Zellen	Bemerkung:
4		Tiger	Hunde, die bellen, beißen nicht.	Hunde, die bellen, beißen nicht.	Hunde werden markiert, weil "Hund" enthalten ist.
5		Löwen	Katzen fressen Mäuse.	Katzen fressen Mäuse.
6		Gut	Das Gut umfassst 400 ha.	Das Gut umfassst 400 ha.
7		Pferd	Es ist gut, dass die Sonne scheint	Es ist gut, dass die Sonne scheint	Hier macht sich der Unterschied zwischen SUCHEN und FINDEN bemerkbar.
8		Hund	Der Löwe gähnt.	Der Löwe gähnt.	Löwe wird nicht markiert, weil "Löwen" gesucht wurde
9

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format C4 1. / Formel ist =ANZAHL(SUCHEN($B$4:$B$8;C4)) Abc D4 1. / 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

	A	B	C	D	E	F
1
2			Groß- und Kleinschreibung wird nicht berücksichtigt Funktion: SUCHEN	Groß- und Kleinschreibung wird berücksichtigt Funktion: FINDEN
3		Suchstring	Untersuchte Zellen	Untersuchte Zellen	Bemerkung:
4		Tiger	Hunde, die bellen, beißen nicht.	Hunde, die bellen, beißen nicht.	Hunde werden markiert, weil "Hund" enthalten ist.
5		Löwen	Katzen fressen Mäuse.	Katzen fressen Mäuse.
6		Gut	Das Gut umfassst 400 ha.	Das Gut umfassst 400 ha.
7			Es ist gut, dass die Sonne scheint	Es ist gut, dass die Sonne scheint	Hier macht sich der Unterschied zwischen SUCHEN und FINDEN bemerkbar.
8		Hund	Der Löwe gähnt.	Der Löwe gähnt.	Löwe wird nicht markiert, weil "Löwen" gesucht wurde
9

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format C4 1. / Formel ist =ANZAHL(SUCHEN($B$4:$B$8;C4)/($B$4:$B$8<>"")) Abc D4 1. / 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.

Eine 7 wird an Position 5 gesucht. Kein Unterschied bei Zahlen zwischen FINDEN und SUCHEN   A B C D E 1           2   Groß-Kleinschreibung wird nicht beachtet! Groß-Kleinschreibung wird beachtet!     3   SUCHEN FINDEN     4   ein W hier ein W hier Ein "w" wird an Position 5 gesucht (kleingeschrieben in der Formel)   5   ein W hier ein W hier Ein "W" wird an Position 5 gesucht (großgeschrieben in der Formel)   6   ALG5789 ALG5789   7   ALG5589 ALG5589   8   ein " hier ein " hier Ein " wird an Position 5 gesucht. Dafür wird ZEICHEN(34) verwandt, alternativ """"   9           Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B4 1. / Formel ist =SUCHEN("w";TEIL(B4;5;1)) Abc C4 1. / Formel ist =FINDEN("w";TEIL(C4;5;1)) Abc B5 1. / Formel ist =SUCHEN("W";TEIL(B5;5;1)) Abc C5 1. / Formel ist =FINDEN("W";TEIL(C5;5;1)) Abc B6 1. / Formel ist =SUCHEN(7;TEIL(B6;5;1)) Abc C6 1. / Formel ist =FINDEN(7;TEIL(C6;5;1)) Abc B7 1. / Formel ist =SUCHEN(7;TEIL(B7;5;1)) Abc C7 1. / Formel ist =FINDEN(7;TEIL(C7;5;1)) Abc B8 1. / Formel ist =FINDEN(ZEICHEN(34);TEIL(B8;5;1)) Abc C8 1. / 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.

Zellen, die ab 3. Stelle auf 2 Stellen Länge "tt" enthalten (kleingeschrieben in der Formel)Zellen, die in den letzten 3 Zeichen 789 enthalten - kein Unterschied bei ZahlenZellen, die von der 3. bis 5. Stelle den String "hre" enhalten (kleingeschrieben in der Formel)   A B C D E 1           2   Groß-Kleinschreibung wird nicht beachtet! Groß-/Kleinschreibung wird beachtet!     3   SUCHEN IDENTISCH     4   Wetter Wetter   5   LMTTE-150 LMTTE-150   6   ALG5789 ALG5789   7   ALG5589 ALG5589   8   Lehrer Lehrer   9   FAHRER FAHRER   10           Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B4 1. / Formel ist =SUCHEN(TEIL(B4;3;2);"tt") Abc C4 1. / Formel ist =IDENTISCH(TEIL(C4;3;2);"tt") Abc B5 1. / Formel ist =SUCHEN(TEIL(B5;3;2);"tt") Abc C5 1. / Formel ist =IDENTISCH(TEIL(C5;3;2);"tt") Abc B6 1. / Formel ist =SUCHEN(RECHTS(B6;3);789) Abc C6 1. / Formel ist =IDENTISCH(RECHTS(C6;3);789) Abc B7 1. / Formel ist =SUCHEN(RECHTS(B7;3);789) Abc C7 1. / Formel ist =IDENTISCH(RECHTS(C7;3);789) Abc B8 1. / Formel ist =SUCHEN(TEIL(B8;3;3);"hre") Abc C8 1. / Formel ist =IDENTISCH(TEIL(C8;3;3);"hre") Abc B9 1. / Formel ist =SUCHEN(TEIL(B9;3;3);"hre") Abc C9 1. / 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:

A B C D E 1           2   Artikelnr.: Gesucht: an Pos.:   3   125 UFGT 458x5 789 ABRO 3   4   14 145 ABRO LOP       5   1597 G657 8 1458       6           Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B3 1. / Formel ist =INDEX(x;D$3)=C$3 Abc

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:

A B C 1       2   Artikelnr.:   3   125 UFGT 458x5 789   4   14 145 ABRO LOP   5   1597 G657 8 1458   6       Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B3 1. / 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

Zellen, in denen ein " steht, sollen bedingt formatiert werden.Zellen, in denen ein * steht, sollen bedingt formatiert werden. Das Sternchen hat hier keine Platzhalterfunktion; beachten Sie den verschiedenen Umgang mit SUCHEN und FINDEN.Zellen, in denen ein ? steht, sollen bedingt formatiert werden. Das Fragezeichen hat hier keine Platzhalterfunktion; beachten Sie den verschiedenen Umgang mit SUCHEN und FINDEN.   A B C D 1         2   Beispiele: Erklärung   3   Ein " hier   4   Kein Anführungszeichen hier   5   Ein * scheint hell.   6   Ein Stern scheint hell.   7   Ein * scheint hell.   8   Ein Stern scheint hell.   9   Ich bin ein ?   10   Ich bin ein Fragezeichen   11   Ich bin ein ?   12   Ich bin ein Fragezeichen   13         Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B3 1. / Formel ist =FINDEN("""";B3) Abc B4 1. / Formel ist =FINDEN("""";B4) Abc B5 1. / Formel ist =FINDEN("*";B5) Abc B6 1. / Formel ist =FINDEN("*";B6) Abc B7 1. / Formel ist =SUCHEN("~*";B7) Abc B8 1. / Formel ist =SUCHEN("~*";B8) Abc B9 1. / Formel ist =FINDEN("?";B9) Abc B10 1. / Formel ist =FINDEN("?";B10) Abc B11 1. / Formel ist =SUCHEN("~?";B11) Abc B12 1. / Formel ist =SUCHEN("~?";B12) Abc

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:

A B C D E F G 1               2   Firma Müller Mayer Schmitz Becker   3   06.11.2005 8 7,9 10,98 25,22   4   07.11.2005 5,5 8,4 12 48,12   5   08.11.2005 6 8,9 13,02 71,02   6   09.11.2005 6,5 9,4 14,04 93,92   7   10.11.2005 7 9,9 15,06 116,82   8   11.11.2005 7,5 10,4 16,08 139,72   9   12.11.2005 16,4 28,32 414,52 118,1   10               Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format C3 1. / Formel ist =C3=MAX($C$3:$F$9) Abc C3 2. / 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:

die 3 größten Wertedie 3 kleinsten Werte   A B C D E F G 1               2   die 3 größten und 3 kleinsten Werte   3   3 Bedingungen, verschieden Farben 1 Bedingung, gleiche Farbe 3 Bedingungen, verschieden Farben 1 Bedingung, gleiche Farbe 2 Bedingungen, pro Bedingung eine Farbe   4   578,84 578,84 578,84 578,84 578,84   5   479,71 479,71 479,71 479,71 479,71   6   120,04 120,04 120,04 120,04 120,04   7   583,91 583,91 583,91 583,91 583,91   8   193,07 193,07 193,07 193,07 193,07   9   92,56 92,56 92,56 92,56 92,56   10   646 646 646 646 646   11   348 348 348 348 348   12               Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B4 1. / Formel ist =B4=KGRÖSSTE($B$4:$B$11;1) Abc B4 2. / Formel ist =B4=KGRÖSSTE($B$4:$B$11;2) Abc B4 3. / Formel ist =B4=KGRÖSSTE($B$4:$B$11;3) Abc C4 1. / Formel ist =C4>= KGRÖSSTE($C$4:$C$11;3) Abc D4 1. / Formel ist =D4=KKLEINSTE($D$4:$D$11;1) Abc D4 2. / Formel ist =D4=KKLEINSTE($D$4:$D$11;2) Abc D4 3. / Formel ist =D4=KKLEINSTE($D$4:$D$11;3) Abc E4 1. / Formel ist =E4<= KKLEINSTE($E$4:$E$11;3) Abc F4 1. / Formel ist =F4<= KKLEINSTE($F$4:$F$11;3) Abc F4 2. / 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:

Hier in B3:F3 liegen die Zellen mit bedingter Formatierung:   A B C D E F G H I J 1                     2   Hintergrundfarbe orange Bedingung: Wert < 0 Hintergrundfarbe grün Bedingung: Wert > 10 Hintergrundfarbe blau Bedingung: Wert = 5   3   -10 15 -5 3 5 2 1 1   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 Zelle Nr.: / Bedingung Format B3 1. / Zellwert ist kleiner als 0 Abc B3 2. / Zellwert ist grösser als 10 Abc B3 3. / Zellwert ist gleich 5 Abc

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:

Eingabebereich   A B C D E F G H I 1                   2   Auswertung   3   5 1 12 10 7 -5 min. 5 rote Zellen   4   2 30 36 42 48 54     5   8 0 4 5 12 7 min. 5 rote Zellen   6   16 0 8 10 24 14     7   1 15 18 21 24 27     8   32 2 7 10 12 8     9   40 8 -24 -56 -88 -120 min. 5 rote Zellen   10   48 16 -16 -48 -80 -112     11   56 54 52 50 48 46     12   64 92 18 -56 -130 -204     13   72 130 36 -162 -308 12     14   80 3 54 -268 -486 18     15   88 206 8 -374 -664 29     16                   Formeln der Tabelle H3 : =WENN(ZÄHLENWENN(B3:G3;"<=10")>4;"min. 5 rote Zellen";"") Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B3 1. / Zellwert ist kleiner oder gleich 10 Abc

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:

Eingabebereich   A B C D E F G H I J K 1                       2     Auswertung über die bedingte Formatierung   3   1 4 3 4 5 8 18   rot = Zahl ist <= 10   4   2 30 36 42 48 54 36   grün = 5 oder mehr Zahlen <=10 in Serie   5   8 0 4 5 12 7 4       6   16 0 8 10 24 14 8       7   5 1 12 10 7 -5 12       8   32 2 7 10 12 8 7       9   40 8 -24 -56 -88 -120 50       10   48 16 -16 -48 -80 -112 -16       11   1 54 52 50 48 46 52       12   64 92 18 -56 -130 -204 18       13   72 130 36 -162 -308 12 36       14   80 3 54 -268 -486 18 54       15   88 206 8 -374 -664 29 8       16                       Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B3 1. / Formel ist =ZÄHLENWENN(B3:F3;"<=10")=5 Abc B3 2. / Zellwert ist kleiner oder gleich 10 Abc C3 1. / Formel ist =ODER(ZÄHLENWENN(C3:G3;"<=10")=5;ZÄHLENWENN(B3:F3;"<=10")=5) Abc C3 2. / Zellwert ist kleiner oder gleich 10 Abc D3 1. / Formel ist =ODER(ZÄHLENWENN($C3:$G3;"<=10")=5;ZÄHLENWENN($B3:$F3;"<=10")=5;ZÄHLENWENN($A3:$E3;"<=10")=5) Abc D3 2. / Zellwert ist kleiner oder gleich 10 Abc E3 1. / Formel ist =ODER(ZÄHLENWENN($C3:$G3;"<=10")=5;ZÄHLENWENN($B3:$F3;"<=10")=5;ZÄHLENWENN($A3:$E3;"<=10")=5) Abc E3 2. / Zellwert ist kleiner oder gleich 10 Abc F3 1. / Formel ist =ODER(ZÄHLENWENN($C3:$G3;"<=10")=5;ZÄHLENWENN($B3:$F3;"<=10")=5;ZÄHLENWENN($A3:$E3;"<=10")=5) Abc F3 2. / Zellwert ist kleiner oder gleich 10 Abc G3 1. / Formel ist =ODER(ZÄHLENWENN(D3:H3;"<=10")=5;ZÄHLENWENN(C3:G3;"<=10")=5;) Abc G3 2. / Zellwert ist kleiner oder gleich 10 Abc H3 1. / Formel ist =ZÄHLENWENN(D3:H3;"<=10")=5 Abc H3 2. / Zellwert ist kleiner oder gleich 10 Abc

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.

Gruppe hat 2 TeilnehmerGruppe hat 1 TeilnehmerGruppe hat 2 Teilnehmer   A B C D E F G H I 1                   2     1   3     2   4     3   5     Nachname Vorname Adresse PLZ       6   1 Muster Mike Querstr. 1 60322       7   3 Klein Andeas Adalbertstr. 23 60433       8   3 Akca Yasra Bettinastr. 42 60435       9   2 Müller Thorsten Eichenweg 9 60232       10   1 dela Tour Marie Schleusenweg 34 60322       11                   Formeln der Tabelle B2 : ="Gruppe hat "&ZÄHLENWENN(B$6:B$10;ZEILE()-1)&" Teilnehmer" Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B6 1. / Zellwert ist gleich 1 Abc B6 2. / Zellwert ist gleich 2 Abc B6 3. / Zellwert ist gleich 3 Abc

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.

A B C D E F G H I 1                   2   5 13 12 15 12   Farben für Werte, die   3   5 19 9 17 5   1 mal vorkommen: automatisch (keine Formel hinterlegt)   4   12 2 11 1 6   2 mal vorkommen   5   8 20 8 9 0   3 mal vorkommen   6   9 12 7 1 1   öfter als 3 mal vorkommen   7                   Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B2 1. / Formel ist =(ZÄHLENWENN($B$2:$F$6;B2)>3) Abc B2 2. / Formel ist =ZÄHLENWENN($B$2:$F$6;B2)=3 Abc B2 3. / Formel ist =ZÄHLENWENN($B$2:$F$6;B2)=2 Abc

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.

A B C D E F G H I 1                   2   5 13 12 -12 12   Farben für Werte, die   3   -121 -8 9 17 5   <-10 (automatisch (keine Formel hinterlegt)   4   -12 2 11 1 -17   >=-10 und 0   5   8 -3 8 -15 0   >=0 und <=10   6   9 12 7 -6 1   >10   7                   Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B2 1. / Zellwert ist grösser als 10 Abc B2 2. / Zellwert ist zwischen 0 und 10 Abc B2 3. / Zellwert ist zwischen 0 und -10 Abc

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:

Kriterien   A B C D E 1           2     3   > 21 21   4   <   26   5     20 20   6     21 21   7     22 22   8     23 23   9     24 24   10     25 25   11     26 26   12     27 27   13     28 28   14     29 29   15           Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format C5 1. / Zellwert ist grösser als ="$C$3" Abc D5 1. / Zellwert ist zwischen ="$D$3" und ="$D$4" Abc

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

Nach oben