So ändern Sie ein Excel-bedingtes Format im laufenden Betrieb

Bild: Mariakray, iStock / Getty Images

Es ist einfach, einen Datensatz in Microsoft Excel mithilfe der bedingten Formatierung hervorzuheben. In der Regel wird die Bedingung mit einem vorhandenen Wert im Datensatz verglichen. Ist dieser Wert größer, kleiner oder gleich "dies" oder "das"? Sie können den Wert, einen Teil des Datensatzes oder den gesamten Datensatz markieren. Sie können sogar einen anderen Wert basierend auf einem anderen hervorheben - beide im Datensatz. Was Sie nicht einfach tun können, ist den Zustand zu ändern. Ich zeige Ihnen, wie Sie eine Eingabezelle in Excel hinzufügen, auf die in der bedingten Formatierungsregel verwiesen wird, mit der Sie eine Bedingung im laufenden Betrieb ändern können, ohne die eigentliche Regel selbst zu ändern.

Um diese Technik zu implementieren, benötigen Sie Daten, eine eindeutige Liste von Filterwerten, eine Datenüberprüfungssteuerung und eine bedingte Regel. Es mag komplex klingen, ist es aber nicht. Ich gehe davon aus, dass Sie grundlegende Funktionen kennen, z. B. das Einfügen von Zeilen, das Erstellen eines Tabellenobjekts, das Sortieren von Daten usw.

Offenlegung: TechRepublic kann eine Provision für einige der auf dieser Seite vorgestellten Produkte verdienen. TechRepublic und der Autor wurden für diese unabhängige Überprüfung nicht entschädigt.

Ich verwende Microsoft Excel in Office 365 auf einem Windows 10 64-Bit-System, aber Sie können frühere Versionen verwenden. Sie können mit Ihren eigenen Daten arbeiten oder die Demonstrationsdatei .xlsx und .xls herunterladen. Diese Technik ist für die Browser-Edition nicht geeignet.

MEHR ERFAHREN: Preise und Funktionen für Office 365 Consumer

So richten Sie es ein

Der in Abbildung A gezeigte einfache Datensatz ist als Tabellenobjekt formatiert und speichert 45 Zeilen mit Produktinformationen (die ich aus der Access Northwind-Datenbank kopiert habe). Ich habe ein Tabellenobjekt verwendet, weil ich möchte, dass die gesamte Technik so dynamisch wie möglich ist, aber Sie müssen kein Tabellenobjekt verwenden. (Um eine Tabelle zu erstellen, klicken Sie auf eine beliebige Stelle im Datensatz, klicken Sie auf die Registerkarte Einfügen, klicken Sie in der Gruppe Tabellen auf Tabelle und klicken Sie auf OK.)

Abbildung A.

We'll create a dynamic conditional format expression to highlight specific records in this data set.

" data-credit="" rel="noopener noreferrer nofollow">

Wir erstellen einen dynamischen Ausdruck für das bedingte Format, um bestimmte Datensätze in diesem Datensatz hervorzuheben.

Angenommen, Sie möchten Produkte mit einem Lagerbestand hervorheben, der kleiner oder gleich dem Nachbestellwert des Produkts ist. Sie möchten jedoch nicht alle anzeigen - Sie möchten die Produkte, die diese Bedingung erfüllen, nach Kategorien anzeigen. Mit anderen Worten, Sie möchten alle Getränke oder Gewürze sehen, die bestellt werden müssen.

Eingebaute Filter können nicht helfen, und ein bedingtes Format allein kann es auch nicht. Sie können jedoch ein Listensteuerelement mit einem bedingten Format kombinieren, um ein bedingtes Format zu erstellen, das bedingter ist, als es sonst sein könnte.

So organisieren Sie die Liste

Das erste, was wir brauchen, ist ein Steuerelement, das die Kategorien in einer Dropdown-Liste anzeigt. Der ausgewählte Wert wird in der zugrunde liegenden Zelle gespeichert, und die bedingte Regel bezieht sich auf diese Zelle. Magie (fast)!
Die Liste sollte eine Reihe eindeutiger Werte enthalten. Sie können die Kategorieliste manuell eingeben (es ist einfach genug, alle aus unserem einfachen Datensatz zu entnehmen), oder Sie können Excel dies für Sie tun lassen, um sicherzustellen, dass Sie keine verpasst haben. Dazu verwenden Sie die erweiterte Filterfunktion. Zu diesem Zeitpunkt können Sie einen erweiterten gefilterten Satz nicht auf ein anderes Blatt kopieren. Die Funktion wird nur auf das aktive Blatt (Art) kopiert. Wenn Sie mit dem Zielblatt anstelle des Quellblatts beginnen, funktioniert dies einwandfrei. Das wird in einer Minute Sinn machen.
Sie können die Liste beliebig kopieren, aber einen abgelegenen Ort auswählen. Ich habe ein Blatt ausgewählt, das speziell Listen dieser Art gewidmet ist, und es wurde entsprechend benannt: Listen. Wir kopieren einen eindeutigen Satz von Kategorien aus dem Datensatz wie folgt in das Listenblatt.

  1. Beachten Sie den Kategoriebereich - Sie werden ihn in Kürze benötigen. Für unsere Demonstrationsdaten sind es $ G $ 2: $ G $ 46.

  2. Wählen Sie einen abgelegenen Ort für die Liste. Wählen Sie B2 auf dem Blatt mit dem Namen Listen.

  3. Klicken Sie auf die Registerkarte Daten und dann in der Gruppe Sortieren und Filtern auf Erweitert, um das Dialogfeld Erweiterter Filter anzuzeigen.

  4. Klicken Sie unter den Aktionseinstellungen auf An einen anderen Speicherort kopieren.

  5. Geben Sie für den Listenbereich $ G $ 2: $ G $ 46 oder Tabelle 1 Kategorie ein, wenn Sie ein Tabellenobjekt verwenden. Oder klicken Sie auf die Registerkarte Blatt und markieren Sie die Werte manuell.

  6. Geben Sie im Bereich Kopieren nach die Ankerzelle für die eindeutige Liste ein. In diesem Fall sind es Listen! $ B $ 2.

  7. Klicken Sie auf die Option Nur eindeutige Datensätze ( Abbildung B ) und dann auf OK.

  8. Sortieren Sie die resultierende Liste, wenn in der Dropdown-Liste eine alphabetische Liste angezeigt werden soll. Geben Sie der Liste eine Kopfzelle und formatieren Sie sie als Tabellenobjekt ( Abbildung C ).

Abbildung B.

Identify the original list and the unique list location.

" data-credit="" rel="noopener noreferrer nofollow">

Identifizieren Sie die ursprüngliche Liste und den eindeutigen Listenort.

Abbildung C.

Excel's advanced filter feature creates a unique list of category values.

" data-credit="" rel="noopener noreferrer nofollow">

Die erweiterte Filterfunktion von Excel erstellt eine eindeutige Liste von Kategoriewerten.

Der nächste Schritt besteht darin, ein Listensteuerelement einzubetten, das die gerade erstellten eindeutigen Kategorien anzeigt.

So steuern Sie die Liste

Wir benötigen ein Listensteuerelement, mit dem Benutzer eine bestimmte Kategorie auswählen können, und der logischste Punkt befindet sich über dem Kategorie-Header. Fügen Sie zu diesem Zweck einige Zeilen über dem Datensatz ein. (Wählen Sie eine Zeile aus, klicken Sie mit der rechten Maustaste auf die Auswahl und wählen Sie Einfügen. Ich habe drei Zeilen ausgewählt.) Mit leeren Zeilen über dem Datensatz können Sie das Listensteuerelement wie folgt hinzufügen.

  1. Wählen Sie G1. Klicken Sie dann auf die Registerkarte Daten und wählen Sie Datenvalidierung aus der Dropdown-Liste Datenvalidierung in der Gruppe Datentools.

  2. Wählen Sie in der Dropdown-Liste Zulassen im daraufhin angezeigten Dialogfeld die Option Liste.

  3. Identifizieren Sie im Quelldialog die Kategorieliste ( Abbildung D ) und klicken Sie auf OK. Die resultierende Steuerung ist in Abbildung E dargestellt .

Abbildung D.

Create a data validation control populated with the category values.

" data-credit="" rel="noopener noreferrer nofollow">

Erstellen Sie ein Datenüberprüfungssteuerelement, das mit den Kategoriewerten gefüllt ist.

Abbildung E.

This list control displays a unique list of categories.

" data-credit="" rel="noopener noreferrer nofollow">

Dieses Listensteuerelement zeigt eine eindeutige Liste von Kategorien an.

Wenn Sie eine Kategorie aus der Liste Datenüberprüfung auswählen, wird dieser Wert in Zelle G1 gespeichert. Sie müssen diese Zelle in der bedingten Formatregel referenzieren, die als Nächstes angezeigt wird.

Sie erinnern sich vielleicht, dass ich erwähnt habe, dass der Datensatz und die Liste beide Tabellenobjekte waren - hier ist der Grund: Wenn Sie die Liste der eindeutigen Kategorien aktualisieren, wird die Datenüberprüfungsliste automatisch aktualisiert. Es ist nicht zwingend erforderlich, dass die Technik funktioniert, aber es ist sicherlich ein schöner Bonus. Nun zur bedingten Formatregel.

Verwendung des bedingten Formats

Die Grundvoraussetzung ist einfach: Wir möchten Datensätze hervorheben, wenn sie bestellt werden müssen. Wir können das als einfachen Ausdruck schreiben:

Einheiten auf Lager <= Bestellstand

Wenn der Lagerbestand kleiner oder gleich dem Schwellenwert für die Nachbestellung ist, ist der Ausdruck wahr. Andernfalls ist der Ausdruck falsch. Aber warte - es gibt noch mehr! Wir müssen auch die Kategorie berücksichtigen. Wir möchten nur die Datensätze hervorheben, bei denen dieser Ausdruck wahr ist und der Kategoriewert des Datensatzes mit dem in G1 übereinstimmt. Wir können dies als einen anderen wahren oder falschen Ausdruck schreiben:

Kategorie = ausgewählter Wert in der Datenvalidierungssteuerung

Mit dem AND-Operator können wir die beiden Bedingungen kombinieren, um einen Ausdruck zu erstellen, der nur dann true zurückgibt, wenn beide Bedingungen true sind:

= AND ($ D5 <= $ F5, $ G5 = $ G $ 1)

Der letzte Schritt besteht darin, diesen bedingten Formatausdruck wie folgt hinzuzufügen.

  1. Wählen Sie den Datensatz aus: B5: G49. Wenn Sie nicht die gesamte Zeile hervorheben möchten, markieren Sie die Spalte (n), die Sie hervorheben möchten.

  2. Klicken Sie auf der Registerkarte Start in der Gruppe Stile auf Bedingte Formatierung und wählen Sie in der Dropdown-Liste die Option Neue Regel aus.

  3. Wählen Sie im oberen Bereich die Option Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen.

  4. Geben Sie im unteren Bereich die folgende Formel ein: AND ($ D5 <= $ F5, $ G5 = $ G $ 1) . (Der Punkt ist grammatikalisch und nicht Teil der Formel.)

  5. Klicken Sie auf Format. Klicken Sie im nächsten Bereich auf die Registerkarte Füllen, wählen Sie eine Farbe aus und klicken Sie auf OK. Abbildung F zeigt die Formel und das Format. Klicken Sie auf OK, um zum Arbeitsblatt zurückzukehren.

Abbildung F.

Set the conditional format to apply when the expression returns true.

" data-credit="" rel="noopener noreferrer nofollow">

Set the conditional format to apply when the expression returns true.

" data-credit="" rel="noopener noreferrer nofollow">Legen Sie das bedingte Format fest, das angewendet werden soll, wenn der Ausdruck true zurückgibt.

Set the conditional format to apply when the expression returns true.

" data-credit="" rel="noopener noreferrer nofollow">Wie Sie in Abbildung G sehen können, lautet die Auswahl in der Datenvalidierungssteuerung Gewürze, und die bedingte Regel hebt zwei Datensätze hervor: Anissirup und Chang müssen neu angeordnet werden. Set the conditional format to apply when the expression returns true.

" data-credit="" rel="noopener noreferrer nofollow">Verwenden Sie das Datenüberprüfungssteuerelement, um den Kategoriewert zu ändern und zu beobachten, wie die Regel verschiedene (oder möglicherweise keine) Datensätze hervorhebt.

Set the conditional format to apply when the expression returns true.

" data-credit="" rel="noopener noreferrer nofollow">Abbildung G.

The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">

The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">Das bedingte Format hebt Produkte nach Kategorien hervor, die neu angeordnet werden müssen.

The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">Im laufenden Betrieb

The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">Die Möglichkeit, einen Wert zu ändern, der durch eine bedingte Formatierungsregel ausgewertet wird, bietet Ihnen viel Flexibilität. The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">Wenn Sie diese Technik implementieren, teilen Sie Ihre Erfahrungen bitte im Kommentarbereich mit.

The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">Senden Sie mir Ihre Frage zu Microsoft Office

The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">Ich beantworte die Fragen der Leser, wenn ich kann, aber es gibt keine Garantie. The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">Senden Sie keine Dateien, es sei denn, Sie werden dazu aufgefordert. The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">Erste Hilfeanfragen, die mit angehängten Dateien eingehen, werden ungelesen gelöscht. The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">Sie können Screenshots Ihrer Daten senden, um Ihre Frage zu klären. The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">Seien Sie so genau wie möglich, wenn Sie mich kontaktieren. The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">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?" The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">könnte. The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">Bitte erwähnen Sie die App und Version, die Sie verwenden. The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">TechRepublic erstattet mir weder meine Zeit noch mein Fachwissen, wenn ich Lesern helfe, noch verlange ich von den Lesern, denen ich helfe, eine Gebühr. The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">Sie können mich unter email protected kontaktieren.

The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">

The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">Microsoft Weekly Newsletter

The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">Seien Sie der Microsoft-Insider Ihres Unternehmens mit Hilfe dieser Windows- und Office-Tutorials und der Analysen unserer Experten zu Microsoft-Unternehmensprodukten. The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">Wird montags und mittwochs geliefert

The conditional format highlights products by category that need to be reordered.

" data-credit="" rel="noopener noreferrer nofollow">Heute anmelden

© Copyright 2021 | mobilegn.com