Zellenfarbe basierend auf einem Wert in einem anderen Blatt und einer anderen Zelle formatieren

Question

Ich habe eine Arbeitsmappe mit zwei Blättern. Ich möchte die Hintergrundfarbe der Zellen in der ersten Spalte von Blatt 1 auf der Grundlage der Werte in der zweiten Spalte von Blatt 2 formatieren.

Wenn z. B. der Wert von Blatt 2, Zeile 6, Spalte 2 gleich 4 ist, dann möchte ich, dass die Hintergrundfarbe von Blatt 1, Zeile 4, Spalte 1 grün ist. Wenn keiner der Werte in Blatt 2, Spalte 2 auf eine bestimmte Zeile in Blatt 1 verweist, möchte ich keine Farbe einstellen. Es gibt kein Verbot, dass derselbe Wert mehrmals in der zweiten Spalte von Blatt 2 erscheint. Einen Bonus gibt es, wenn Sie mir sagen können, wie ich die Farbe zurücksetzen kann, wenn der letzte Wert in Blatt 2, der auf eine Zeile in Blatt 1 verweist, entfernt wird.

Ich bin mir sicher, dass dies für die Excel-Experten da draußen trivial sein könnte, aber ich habe nur selten Gelegenheit, Excel zu benutzen, und sicherlich nicht die Zeit, mich darin zu schulen. Kann mir jemand Ratschläge, Hinweise oder eine schnelle Formel für diese Aufgabe geben? Wenn ich dafür einen komplexen VB-Code benötige, lohnt es sich nicht.

Ich danke Ihnen!

Answer 1

Sie können dies auch tun mit benannte Bereiche damit Sie die Zellen nicht von Blatt1 nach Blatt2 kopieren müssen:

1. Definieren Sie einen benannten Bereich, zum Beispiel Sheet1Vals für die Spalte, die die Werte enthält, auf die Sie Ihre Bedingung stützen wollen. Sie können einen neuen benannten Bereich definieren, indem Sie die Insert\Name\Define... Menüpunkt. Geben Sie Ihren Namen ein und verwenden Sie dann den Zellenbrowser im Fenster Refers to um die Zellen auszuwählen, die Sie in den Bereich aufnehmen möchten. Wenn sich der Bereich im Laufe der Zeit ändert (Zeilen hinzufügen oder entfernen), können Sie diese Formel verwenden, anstatt die Zellen explizit auszuwählen:

   =OFFSET('SheetName'!$COL$ROW,0,0,COUNTA('SheetName'!$COL:$COL)) .

   Hinzufügen einer -1 vor dem letzten ) wenn die Spalte eine Kopfzeile hat.

2. Definieren Sie einen benannten Bereich, zum Beispiel Sheet2Vals für die Spalte, die die Werte enthält, die Sie bedingt formatieren wollen.

3. Verwenden Sie das Dialogfeld Bedingte Formatierung, um Ihre Bedingungen zu erstellen. Angeben Formula Is in der Dropdown-Liste, dann geben Sie dies für die Formel ein:

   =INDEX(Sheet1Vals, MATCH([FirstCellInRange],Sheet2Vals))=[Condition]

   wobei [FirstCellInRange] ist die Adresse der Zelle, die Sie formatieren wollen, und [Condition] ist der Wert, den Sie überprüfen.

Wenn meine Bedingungen in Blatt1 zum Beispiel die folgenden Werte haben 1 , 2 y 3 und die Spalte, die ich formatiere, ist die Spalte B in Blatt2, dann würden meine bedingten Formate etwa so aussehen:

=INDEX(Sheet1Vals, MATCH(B1,Sheet2Vals))=1
=INDEX(Sheet1Vals, MATCH(B1,Sheet2Vals))=2
=INDEX(Sheet1Vals, MATCH(B1,Sheet2Vals))=3

Mit dem Formatpainter können Sie diese Formate dann auf die übrigen Zellen übertragen.

Answer 2

So habe ich es in Excel 2003 mit bedingter Formatierung gemacht.

So wenden Sie bedingte Formatierung an Sheet1 mit Werten aus Sheet2 müssen Sie die Werte spiegeln in Sheet1 .

Erstellen einer Spiegelung von Blatt 2, Spalte B in Blatt 1

1. Ir a Sheet1 .

2. Fügen Sie eine neue Spalte ein, indem Sie mit der rechten Maustaste auf die Überschrift von Spalte A klicken und "Einfügen" wählen.

3. Geben Sie die folgende Formel in A1 ein:

   =IF(ISBLANK(Sheet2!B1),"",Sheet2!B1)

4. Kopieren A1 indem Sie mit der rechten Maustaste darauf klicken und die Option " Copy ".

5. Fügen Sie die Formel in die Spalte A indem Sie mit der rechten Maustaste auf seine Kopfzeile klicken und " Paste ".

Sheet1 Spalte A sollte nun genau die Werte in Sheet2 Spalte B .

(Hinweis: Wenn es Ihnen nicht gefällt, in der Spalte A funktioniert es genauso gut, wenn es in der Spalte Z oder irgendwo anders).

Anwenden der bedingten Formatierung

1. Bleiben Sie dran Sheet1 .

2. Spalte auswählen B indem Sie mit der linken Maustaste auf seine Überschrift klicken.

3. Wählen Sie den Menüpunkt Format > Conditional Formatting...

4. Ändern Sie Condition 1 zu " Formula is " und geben Sie diese Formel ein:

   =MATCH(B1,$A:$A,0)

5. Klicken Sie auf die Format... Taste und wählen Sie einen grünen Hintergrund.

Sie sollten nun sehen, dass der grüne Hintergrund auf die entsprechenden Zellen in Sheet1 .

Verstecken der Spiegelsäule

1. Bleiben Sie dran Sheet1 .
2. Klicken Sie mit der rechten Maustaste auf die Überschrift der Spalte A und wählen Sie " Hide ".

Dies sollte automatisch die Sheet1 wann immer etwas in Sheet2 geändert wird.

Answer 3

Ich verwende Excel 2003.

Das Problem bei der Verwendung der bedingten Formatierung ist, dass Sie in Ihren Bedingungen nicht auf ein anderes Arbeitsblatt oder eine andere Arbeitsmappe verweisen können. Was Sie jedoch tun können, ist, eine Spalte auf Blatt 1 gleich der entsprechenden Spalte auf Blatt 2 zu setzen (in Ihrem Beispiel =Blatt2!B6). Ich habe in meinem Beispiel unten Spalte F verwendet. Dann können Sie die bedingte Formatierung verwenden. Markieren Sie die Zelle auf Blatt 1, Zeile, Spalte 1, und gehen Sie dann in das Menü für bedingte Formatierung. Wählen Sie "Formel ist" aus der Dropdown-Liste und setzen Sie die Bedingung auf "=$F$6=4". Klicken Sie auf die Schaltfläche "Format" und wählen Sie dann die Registerkarte "Muster". Wählen Sie die gewünschte Farbe, und schon sind Sie fertig.

Sie können das Formatierungswerkzeug verwenden, um bedingte Formatierungen auf andere Zellen anzuwenden, aber beachten Sie, dass Excel standardmäßig absolute Referenzen in den Bedingungen verwendet. Wenn Sie möchten, dass sie relativ sind, müssen Sie die Dollarzeichen aus der Bedingung entfernen.

Sie können bis zu 3 Bedingungen auf eine Zelle anwenden (verwenden Sie die Schaltfläche "Hinzufügen >>" am unteren Rand des Dialogfelds "Bedingte Formatierung"). Wenn also die letzte Zeile feststeht (z. B. wenn Sie wissen, dass es immer Zeile 10 sein wird), können Sie sie als Bedingung verwenden, um die Hintergrundfarbe auf "keine" zu setzen. Angenommen, der letzte Wert, der Sie interessiert, steht in Zeile 10 (und Sie haben die Spalte F auf Blatt 1 auf die entsprechenden Zellen auf Blatt 2 gesetzt), dann setzen Sie die erste Bedingung auf Formel ist =$F$10="" und das Muster auf Keine. Machen Sie dies zur ersten Bedingung, und sie wird alle folgenden widersprüchlichen Anweisungen außer Kraft setzen.

Answer 4

Hier ist meine eigene Lösung für die Wiederherstellung der ursprünglichen Farben beim Kopieren bestimmter stark formatierter Blätter oder Vorlagen in ein neues Arbeitsblatt. Es kopiert alle Daten direkt, so dass es nur funktioniert, wenn Sie ein Blatt kopieren müssen, nicht nur Farben auf ein anderes Blatt mit anderen Daten anwenden:

Kopieren Sie die Arbeitsmappe im Originalformat mit der Tastenkombination Strg + g und wählen Sie den entsprechenden Bereich aus.

fügen Sie es in das neue Arbeitsblatt ein, die Farben werden alle geändert

Klicken Sie mit der rechten Maustaste auf das markierte Ziel, wählen Sie "Einfügen Spezial" und wählen Sie "Alle unter Verwendung des Ausgangsthemas", dann wiederholen Sie die Funktion "Einfügen Spezial", diesmal nur mit "Werte", und es sollte mit dem kopierten Originalblatt identisch sein.

Answer 5

Ich habe das schon einmal gemacht mit bedingte Formatierung . Es ist eine großartige Möglichkeit, die Zellen in einer Arbeitsmappe visuell zu untersuchen und Ausreißer in Ihren Daten zu erkennen.