Ich habe eine Excel-Tabelle mit zwei Arbeitsblättern. Das erste enthält nur eine Kopfzeile und eine einzelne Spalte mit Artikelnamen. Das zweite ist eine Liste von Artikelgruppen mit einer Kopfzeile und einem Titel in der Spalte ganz links, wobei jede nachfolgende Zeile ein Artikel aus dem anderen Blatt ist:
Sheet1: Sheet2:
+-------+--+--+ +-------+-------+-------+-------+-------+
| Item | | | | Group | Item1 | Item2 | Item3 | ...
+-------+--+--+ +-------+-------+-------+-------+-------+
| Shirt | | | | A | Shirt | Hat | Tie |
+-------+--+--+ +-------+-------+-------+-------+-------+
| Hat | | | | B | Socks | Shirt | SHOES |
+-------+--+--+ +-------+-------+-------+-------+-------+
| Socks | | | | C | Hat | Socks | |
+-------+--+--+ +-------+-------+-------+-------+-------+
| Tie | | | | D | Tie | Tie | Socks |
+-------+--+--+ +-------+-------+-------+-------+-------+
| ... | | |
+-------+--+--+
Ich möchte alle Zellen in „Tabelle2“ bedingt formatieren, sodass alle Werte, die nicht mit einem Wert in der ersten Spalte von „Tabelle1“ übereinstimmen, mit einem roten Hintergrund markiert werden; alle Werte, die mit einem Wert in der ersten Spalte von „Tabelle1“ übereinstimmen, werden mit einem grünen Hintergrund markiert. In diesem Beispiel wären also alle Zellen ab B2 grün, außer dem Wert „SCHUHE“. Der Wert darunter ist nicht eingetragen und würde daher überhaupt nicht formatiert.
Die Formatierungsregel für Grün, die ich ausprobiert habe, ist:
=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)>0)
Für Rot gilt ungefähr das Gleiche:
=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)<1)
Beide Regeln werden auf einen relativ willkürlichen Bereich „angewendet“ (ich möchte, dass sie auf das ganze Blatt angewendet werden, abzüglich der obersten und ganz linken Zeile/Spalte):
=$C$3:$E$10,$C$36:$Q$50,$E$11,$C$11,$C$2,$E$2:$Q$2,$C$12:$E$35,$F$3:$Q$35
Das funktioniert halbwegs, aber die Ergebnisse sind unvorhersehbar. Einige Werte werden wie erwartet hervorgehoben, aber nur für einige Zeilen, und andere nicht. Wahrscheinlich sind meine Bereiche irgendwie durcheinander, aber ich verwende Excel nicht mehr so oft wie früher. Kann mir jemand helfen?
Danke!
Antwort1
Wie Doktoro Reichard sagt, möchten SieBedingte Formatierungum dies zu tun. In diesem speziellen Fall möchten Sie drei Regeln haben:
- Wenn die Zelle leer ist, ändern Sie den Hintergrund nicht
- Wenn die Zelle eine Übereinstimmung aufweist, machen Sie den Hintergrund grün
- Wenn die Zelle keine Entsprechung hat, machen Sie den Hintergrund rot
Entschuldigung, mein Excel ist Japanisch. Es ist der Tag der Mehrsprachigkeit.
Dazu benötigen wir drei Formeln, die für jede dieser Bedingungen TRUEoder zurückgeben. Ich gehe davon aus, dass Ihre Daten wie folgt aussehen:FALSE
Blatt1
Tabelle 2
Regel 1
Die folgende Formel gibt an, ob die Zelle leer ist oder nicht. Ich habe ausgewählt
=ISBLANK(B2)
Beachten Sie, dass ich Zellen mit relativen Referenzen ausgewählt habe B2:D5. Dadurch wird dieselbe Formel angewendet, die die Zellreferenz für jede Zelle im ausgewählten Bereich ändert. Stellen Sie die Hintergrundfarbe auf Weiß (oder was auch immer Sie bevorzugen) ein, wenn diese Bedingung erfüllt ist.
Regel Nr. 2
Die folgende Formel gibt zurück, ob es in der Liste auf Blatt 1 eine perfekte Übereinstimmung gibt oder nicht:
=NOT(ISERROR(MATCH(B2,Sheet1!$A:$A,0)))
Regel Nr. 3
Die folgende Formel gibt zurück, ob in der Liste auf Blatt 1 keine perfekte Übereinstimmung vorhanden ist oder nicht:
=ISERROR(MATCH(B2,Sheet1!$A:$A,0))
Befehl
Die oberste Regel wird zuerst ausgeführt. Da alle leeren Zellen keine Übereinstimmungen sind, müssen Sie die Regel für leere Zellen zuerst setzen. Die Reihenfolge von Nr. 2 und Nr. 3 spielt keine Rolle (sie überschneiden sich nie).
Antwort2
Anscheinend benötigen Sie eine bedingte Zellenformatierung.
Hier ist einVerknüpfungbeschreiben, was das ist.
Meine Bilder stammen aus einer portugiesischen Version von Excel 2003, aber die Funktionalität sollte auch in Office 2010 verfügbar sein. Tatsächlich ermöglicht Office 2010 eine bedingte Formatierung zwischen Blättern, was in 2003 nicht möglich ist, und deshalb mache ich alles auf einem Blatt.
Zunächst die Tabelle:
Sie möchten ein Element aus der zweiten Tabelle mit allen Elementen aus der ersten vergleichen. Sie müssen also eine Funktion wie diese schreiben:
=OR(EXACT($B$6;E3);EXACT($B$5;E3);EXACT($B$4;E3);EXACT($B$3;E3))
Dabei EXACT()werden zwei Textzeichenfolgen verglichen. Dies OR()geschieht True, wenn eine beliebige logische Bedingung darin erfüllt ist True.
Nachdem Sie dies gesagt haben, markieren Sie alle Zellen der 2. Tabelle und drücken dann, wie gezeigt, auf „Bedingte Formatierung“.
Wenn Sie Office 2010 und die neue Ribbon-Oberfläche kennen, sollten Sie im Formatbereich danach suchen. Soweit ich mich erinnere, ist es dort als Symbol zu sehen.
Wenn Sie auf dieses Symbol klicken, wird ein Fenster ähnlich dem folgenden geöffnet:
Dort müssen Sie zunächst auswählen, dass Sie eine Formel möchten, und dann die Formel einfügen, die ich zuvor erwähnt habe. Um alle Zellen, die die Bedingung erfüllen, grün zu machen, ändern Sie einfach das Format. Um die rote Formatierung zu verwenden, verwenden Sie einfach NOT(OR(...)); dies gibt das Gegenteil der von Ihnen festgelegten Bedingung zurück.
Um sicherzustellen, dass keine Zellen formatiert werden, die nichts enthalten, legen Sie eine dritte Bedingung fest, bei der die Formel ISBLANK(E3) lautet (wobei E3 die obere linke Ecke ist).
Etwas, das ich nach dem Schreiben des ersten Entwurfs überprüft habe, ist die Vorrangstellung. Zumindest in meiner Version wird Bedingung 1 vor Bedingung 2 überprüft und so weiter. Sie sollten die Bedingungen also so anordnen, dass sie sich nicht gegenseitig behindern. Also:
1st condition - =ISBLANK(E3)
2nd condition - =OR(...)
3rd condition - =NOT(OR(...))
Daher sollte Ihr Fenster wie das folgende aussehen:
Versuchen Sie, dies an Ihre Situation anzupassen. Wenn ich mich recht erinnere, unterscheidet es sich nicht so sehr von dem, was ich zeige. Das Ergebnis sollte ungefähr so aussehen:
