Profi-Tipp: Berücksichtigen Sie sich ändernde Bedingungen in der bedingten Formatfunktion von Excel

Die bedingte Formatierungsfunktion von Excel ermöglicht die sofortige Formatierung. Beispielsweise können Sie Datensätze markieren, wenn ein Fälligkeitsdatum das aktuelle Datum ist, oder Sie können Datensätze für einen bestimmten Projektleiter markieren. Wenn sich Ihre Regel jedoch ändert, müssen Sie die Regel aktualisieren. Wenn dies häufig vorkommt, benötigen Sie möglicherweise eine flexiblere Lösung.

Um diesem Artikel zu folgen, können Sie mit jedem einfachen Datensatz arbeiten oder die Beispielarbeitsmappe .xls oder .xlsx herunterladen.

Das Problem

Normalerweise werden bedingte Formate angepasst, wenn Sie Werte eingeben und aktualisieren. Es ist einfach, erwartete Änderungen zu berücksichtigen - genau das macht die Funktion! Aber was passiert, wenn sich der Zustand und nicht die Daten ändern? Betrachten wir einen einfachen Datensatz, der Datensätze für den Projektleiter hervorhebt. Wenn sich der Anführer ändert, müssen Sie die bedingten Regeln aktualisieren. Eine Regel einmal zu aktualisieren ist keine große Sache. Es ist jedoch mühsam, diese Regeln häufig oder mehrere Regeln gleichzeitig zu aktualisieren!

Der einfache Datensatz in Abbildung A ist eine vereinfachte Version dessen, was Ihnen möglicherweise begegnet. Eine einfache bedingte Regel, = $ A2 = "Bill", aktualisiert Datensätze für den Projektleiter, Bill. Wenn Sie den Namen ändern, entfernt die Regel die Markierung. Hier wird es etwas knifflig. Wenn Sie den Datensatz neu zuweisen, soll die Hervorhebung verschwinden (siehe Abbildung A) . Der Datensatz gehört nicht mehr Bill, dem Projektleiter. So funktioniert die bedingte Formatierung. Wenn John der neue Projektleiter ist, müssen Sie die zugrunde liegende Regel aktualisieren, da das Highlight erhalten bleiben soll. Das Highlight gehört dem Projektleiter, nicht ausschließlich Bill oder John.

Abbildung A.

Eine bedingte Regel hebt Datensätze für Bill hervor. Wenn Sie den Namen ändern, verschwindet die Markierung.

In einer perfekten Welt würden Sie einfach die Regel = $ A2 = "John" aktualisieren und fortfahren. Wenn Änderungen häufig auftreten, sollten Sie das Setup neu bewerten. Die Position des Projektleiters ist die Bedingung, nicht der Name der Person, die zu einem bestimmten Zeitpunkt der Projektleiter ist. Die Anpassung dieser sich ändernden Bedingung in Ihrem Datensatz und anschließend in Ihrer bedingten Regel ist die beste Lösung. Sie können dies tun, indem Sie eine Spalte für die Position jeder Person im Team hinzufügen und diese Spalte für diese bestimmte Regel verwenden. In der realen Welt ist dies jedoch nicht immer eine Option.

Eine Referenzierungslösung

Versuchen Sie, sich nicht zu sehr auf das einfache und (sehr) erfundene Beispiel einzulassen. Der Beispieldatensatz ist nicht wichtig - die Option, die ich anbiete, ist. Wenn eine Änderung des Datensatzes (um einen stabileren Zustand einzuschließen) nicht möglich ist, können Sie auf den Änderungszustand im Blatt verweisen, wie in Abbildung B dargestellt .

Abbildung B.

Verweis auf den sich ändernden Zustand im Blatt.

Wie Sie sehen können, habe ich den Namen des Projektleiters in Zelle A2 eingegeben. Geben Sie nun eine Regel ein, die mit den Daten in der Zelle übereinstimmt:

  1. Wählen Sie den Datensatz aus, den Sie formatieren möchten - A6: B9.
  2. Klicken Sie auf der Registerkarte Start in der Gruppe Stile auf Bedingte Formatierung. Wählen Sie in Excel 2003 im Menü Format die Option Bedingte Formatierung und fahren Sie mit Schritt 5 fort.
  3. Wählen Sie Neue Regel aus der Dropdown-Liste.
  4. Wählen Sie im oberen Bereich die Option Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen.
  5. Geben Sie die Formel ein = $ A6 = $ A $ 2 In Excel 2003 müssen Sie Formel Is aus der Dropdown-Liste auswählen, bevor Sie die Formel eingeben. Passen Sie die absolute und relative Referenzierung genau an. Sie möchten, dass Excel alle Zeilen im ausgewählten Datensatz berücksichtigt, die Regel sollte jedoch immer auf den Text verweisen, mit dem Sie in Zelle A2 übereinstimmen.
  6. Klicken Sie auf Format. Wählen Sie auf der Registerkarte Füllen (Muster in früheren Versionen) eine Farbe aus und klicken Sie auf OK. An dieser Stelle können Sie eine Vorschau Ihres Formats anzeigen ( Abbildung C ).
  7. Klicken Sie auf OK, um zum Blatt zurückzukehren.

Abbildung C.

An dieser Stelle können Sie eine Vorschau Ihres Formats anzeigen.

Höchstwahrscheinlich möchten Sie die ursprüngliche Regel löschen, indem Sie in der Option Bedingte Formatierung die Option Regeln verwalten auswählen. Wählen Sie dann dieses Arbeitsblatt aus der Dropdown-Liste Formatierungsregeln anzeigen für. Wählen Sie die ursprüngliche Regel aus und klicken Sie dann auf Regel löschen. Wenn die Regel andere Bedingungen enthält, müssen Sie sie ändern, anstatt sie zu löschen.

Jetzt können Sie den Datensatz neu zuweisen und den Projektleiter ändern, ohne die zugrunde liegende Regel anzupassen. Wenn Sie beispielsweise John in Zelle A6 eingeben, verschwindet die Markierung. Bill ist immer noch der Projektleiter und es gibt keine Aufzeichnungen für ihn im Datensatz. Wenn Sie John in A2 eingeben, wird die Hervorhebung entsprechend angepasst (siehe Abbildung D) . Johns Datensatz (anstelle von Bills) wird jetzt hervorgehoben.

Abbildung D.


Bedingte Änderungen zulassen.

Die Bedingung komplizieren

Eine kompliziertere Bedingung ändert nichts an dieser Lösung. Suchen Sie einfach die Komponente, die sich ändert, geben Sie den sich ändernden Wert in das Blatt ein und verweisen Sie auf diese Komponente in der Formel der bedingten Regel. Angenommen, Sie möchten Datensätze hervorheben, bei denen das Datum in Spalte B relativ zum aktuellen Datum ist, sich jedoch der relative Faktor ändert. Manchmal möchten Sie Daten hervorheben, die mit dem aktuellen Datum übereinstimmen. Möglicherweise möchten Sie auch Daten hervorheben, die eine Woche in der Zukunft (oder in der Vergangenheit) liegen. Sie können mehrere bedingte Markierungen zuweisen oder auf den sich ändernden Wert verweisen. Abbildung E zeigt die erforderliche Anpassung des Blattes - der referenzierenden Tageskomponente in Zelle B2.

Abbildung E.

Fügen Sie eine Zelle für die sich ändernde Tageskomponente hinzu.

Fügen wir nun die neue Regel hinzu:

  1. Wählen Sie den Datensatz aus, den Sie formatieren möchten - A6: B9.
  2. Klicken Sie auf der Registerkarte Start in der Gruppe Stile auf Bedingte Formatierung. Wählen Sie in Excel 2003 im Menü Format die Option Bedingte Formatierung und fahren Sie mit Schritt 5 fort.
  3. Wählen Sie Neue Regel aus der Dropdown-Liste.
  4. Wählen Sie im oberen Bereich die Option Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen.
  5. Geben Sie die Formel ein = $ B6 = TODAY () + $ B $ 2 Passen Sie die absolute und relative Referenzierung genau an. Wenn Sie Excel 2003 verwenden, vergessen Sie nicht, Formel Is aus der Dropdown-Liste auszuwählen, damit Sie die Formel eingeben können.
  6. Klicken Sie auf Format. Wählen Sie auf der Registerkarte Füllen (oder in früheren Versionen die Registerkarte Muster) eine Farbe aus und klicken Sie auf OK. An dieser Stelle können Sie eine Vorschau Ihres Formats anzeigen ( Abbildung F ).
  7. Klicken Sie auf OK, um zum Blatt zurückzukehren.

Abbildung F.

Erstellen Sie eine neue Formatierungsregel.

Abbildung G zeigt das resultierende bedingte Format. Wie Sie sehen können, hängt die Hervorhebung vom Wert in Zelle B2 ab. Fünf Tage ab dem 10. August ist der 15. August. Ebenso ist der Tag vor dem 10. August der 9. August, und das bedingte Format wird entsprechend aktualisiert. (Wenn Sie mit der herunterladbaren Beispieldatei arbeiten, beachten Sie, dass das aktuelle Datum nicht mit dem Tag übereinstimmt, an dem ich diese Zahlen aufgenommen habe, sodass das Format entsprechend angepasst wird.)

Abbildung G.


Ein Highlight basierend auf einer sich ändernden Anzahl von Tagen.

Ich habe die Funktion TODAY () zu B4 hinzugefügt, damit Sie das tatsächliche Datum sehen können. Die Technik erfordert diese Funktion nicht. Es ist eine visuelle Bequemlichkeit.

Anpassung an Veränderungen und zusätzliche Flexibilität

Wenn es sich bei Ihrer bedingten Aktualisierung um eine einmalige Änderung handelt, müssen Sie Ihr Blatt nicht anpassen. Aktualisieren Sie einfach die Regel. Wenn sich die Bedingung häufig ändert, arbeiten Sie diese Aktualisierungen in Ihr Blatt ein, um ein wenig Flexibilität hinzuzufügen. Das Ändern von Bedingungen kann auf diese Weise leicht berücksichtigt werden, wenn die bedingte Formatierungsfunktion von Excel verwendet wird.

Senden Sie mir Ihre Frage zu Office

Ich beantworte die Fragen der Leser, wenn ich kann, aber es gibt keine Garantie. Seien Sie so genau wie möglich, wenn Sie mich kontaktieren. Beispiel: "Bitte beheben Sie Fehler in meiner Arbeitsmappe und beheben Sie, was falsch ist" wird wahrscheinlich keine Antwort erhalten, aber "Können Sie mir sagen, warum diese Formel nicht die erwarteten Ergebnisse zurückgibt?" könnte. Ich werde weder von TechRepublic für meine Zeit oder mein Fachwissen erstattet, noch verlange ich von den Lesern eine Gebühr. Sie können mich unter kontaktieren.

© Copyright 2020 | mobilegn.com