Excel-Datenanalyse - Bedingte Formatierung

In Microsoft Excel können Sie verwenden Conditional Formattingzur Datenvisualisierung. Sie müssen die Formatierung für einen Zellbereich basierend auf dem Inhalt des Zellbereichs festlegen. Die Zellen, die die angegebenen Bedingungen erfüllen, werden wie von Ihnen definiert formatiert.

Beispiel

In einem Bereich, der die Verkaufszahlen des letzten Quartals für eine Reihe von Verkäufern enthält, können Sie diejenigen Zellen hervorheben, die darstellen, wer das definierte Ziel erreicht hat, z. B. 2500 US-Dollar.

Sie können die Bedingung als Gesamtumsatz der Person> = festlegen $2500und geben Sie einen Farbcode grün an. Excel überprüft jede Zelle im Bereich, um festzustellen, ob die von Ihnen angegebene Bedingung, dh der Gesamtumsatz der Person> = 2500 USD, erfüllt ist.

Excel wendet das von Ihnen gewählte Format, dh die grüne Farbe, auf alle Zellen an, die die Bedingung erfüllen. Wenn der Inhalt einer Zelle die Bedingung nicht erfüllt, bleibt die Formatierung der Zelle unverändert. Das Ergebnis ist wie erwartet, nur für die Verkäufer, die das Ziel erreicht haben, werden die Zellen grün hervorgehoben - eine schnelle Visualisierung der Analyseergebnisse.

Sie können eine beliebige Anzahl von Bedingungen für die Formatierung angeben, indem Sie angeben Rules. Sie können die Regeln abrufen, die Ihren Bedingungen entsprechen

• Markieren Sie die Zellenregeln
• Top / Bottom Regeln

Sie können auch Ihre eigenen Regeln definieren. Sie können -

• Fügen Sie eine Regel hinzu
• Löschen Sie eine vorhandene Regel
• Verwalten Sie die definierten Regeln

Darüber hinaus haben Sie in Excel mehrere Formatierungsoptionen, um diejenigen auszuwählen, die für Ihre Datenvisualisierung geeignet sind.

• Datenleisten
• Farbskalen
• Symbolsätze

Die bedingte Formatierung wurde gegenüber den Versionen Excel 2007, Excel 2010 und Excel 2013 heraufgestuft. Die Beispiele in diesem Kapitel stammen aus Excel 2013.

In den folgenden Abschnitten lernen Sie die Regeln für die bedingte Formatierung, die Formatierungsoptionen und die Arbeit mit Regeln kennen.

Markieren Sie die Zellenregeln

Sie können verwenden Highlight Cells Regel zum Zuweisen eines Formats zu Zellen, deren Inhalt eines der folgenden Kriterien erfüllt:

• Zahlen innerhalb eines bestimmten Zahlenbereichs -
  • Größer als
  • Weniger als
  • Between
  • Gleicht
• Text, der eine bestimmte Textzeichenfolge enthält.
• Datum, das innerhalb eines bestimmten Datumsbereichs relativ zum aktuellen Datum auftritt -
  • Yesterday
  • Today
  • Tomorrow
  • In den letzten 7 Tagen
  • Letzte Woche
  • Diese Woche
  • Nächste Woche
  • Im vergangenen Monat
  • Diesen Monat
  • Nächsten Monat
• Doppelte oder eindeutige Werte.

Befolgen Sie die Schritte zum bedingten Formatieren von Zellen -

• Wählen Sie den Bereich aus, der bedingt formatiert werden soll.

• Klicken Conditional Formatting in dem Styles Gruppe unter Home Tab.

• Klicken Highlight Cells Rules aus dem Dropdown-Menü.

• Klicken Greater Thanund geben Sie> 750 an. Wähle grüne Farbe.

• Klicken Less Than und geben Sie <500 an. Wählen Sie die rote Farbe.

• Klicken Between und geben Sie 500 und 750 an. Wählen Sie die gelbe Farbe.

Die Daten werden basierend auf den angegebenen Bedingungen und der entsprechenden Formatierung hervorgehoben.

Top / Bottom Regeln

Sie können verwenden Top / Bottom Rules um Zellen ein Format zuzuweisen, deren Inhalt eines der folgenden Kriterien erfüllt:

• Top 10 items - Zellen, die im oberen N rangieren, wobei 1 <= N <= 1000.

• Top 10% - Zellen, die in den oberen n% rangieren, wobei 1 <= n <= 100 ist.

• Bottom 10 items - Zellen, die im unteren N rangieren, wobei 1 <= N <= 1000.

• Bottom 10% - Zellen, die im unteren n% rangieren, wobei 1 <= n <= 100.

• Above average - Zellen, die für den ausgewählten Bereich überdurchschnittlich sind.

• Below average - Zellen, die für den ausgewählten Bereich unterdurchschnittlich sind.

Befolgen Sie die unten angegebenen Schritte, um die Top / Bottom-Regeln zuzuweisen.

• Wählen Sie den Bereich aus, der bedingt formatiert werden soll.

• Klicken Conditional Formatting in dem Styles Gruppe unter Home Tab.

• Klicken Top/Bottom Rulesaus dem Dropdown-Menü. Die Optionen für die oberen / unteren Regeln werden angezeigt.

• Klicken Top Ten Items und spezifizieren 5. Wählen Sie grüne Farbe.

• Klicken Bottom Ten Items und spezifizieren 5. Wählen Sie rote Farbe.

Die Daten werden basierend auf den angegebenen Bedingungen und der entsprechenden Formatierung hervorgehoben.

• Wiederholen Sie die ersten drei oben angegebenen Schritte.

• Klicken Top Ten% und spezifizieren 5. Wählen Sie grüne Farbe.

• Klicken Bottom Ten% und spezifizieren 5. Wählen Sie rote Farbe.

Die Daten werden basierend auf den angegebenen Bedingungen und der entsprechenden Formatierung hervorgehoben.

• Wiederholen Sie die ersten drei oben angegebenen Schritte.

• Klicken Above Average. Wähle grüne Farbe.

• Klicken Below Average. Wähle rote Farbe.

Die Daten werden basierend auf den angegebenen Bedingungen und der entsprechenden Formatierung hervorgehoben.

Datenleisten

Sie können farbig verwenden Data Barsum den Wert in einer Zelle relativ zu den Werten in den anderen Zellen zu sehen. Die Länge der Datenleiste repräsentiert den Wert in der Zelle. Ein längerer Balken repräsentiert einen höheren Wert und ein kürzerer Balken repräsentiert einen niedrigeren Wert. Für die Datenleisten stehen sechs Volltonfarben zur Auswahl: Blau, Grün, Rot, Gelb, Hellblau und Lila.

Datenbalken sind hilfreich bei der Visualisierung der höheren, niedrigeren und mittleren Werte, wenn Sie über große Datenmengen verfügen. Beispiel - Tagestemperaturen über Regionen in einem bestimmten Monat. Sie können Farbbalken mit Verlaufsfüllung verwenden, um den Wert in einer Zelle relativ zu den Werten in anderen Zellen anzuzeigen. Du hast sechsGradient Colors zur Auswahl für die Datenleisten - Blau, Grün, Rot, Gelb, Hellblau und Lila.

• Wählen Sie den Bereich aus, der bedingt formatiert werden soll.

• Klicken Conditional Formatting in dem Styles Gruppe unter Home Tab.

• Klicken Data Barsaus dem Dropdown-Menü. DasGradient Fill Optionen und Fill Optionen werden angezeigt.

Klicken Sie auf die blaue Datenleiste in der Gradient Fill Optionen.

• Wiederholen Sie die ersten drei Schritte.

• Klicken Sie auf die blaue Datenleiste in der Solid Fill Optionen.

Sie können Datenleisten auch so formatieren, dass die Datenleiste in der Mitte der Zelle beginnt und sich bei negativen Werten nach links und bei positiven Werten nach rechts erstreckt.

Farbskalen

Sie können verwenden Color Scalesum den Wert in einer Zelle relativ zu den Werten in den anderen Zellen in einem bestimmten Bereich zu sehen. Wie im Fall vonHighlight Cells Rules, ein Color ScaleVerwendet die Zellschattierung, um die Unterschiede in den Zellenwerten anzuzeigen. Ein Farbverlauf wird auf eine Reihe von Zellen angewendet. Die Farbe gibt an, wo jeder Zellenwert in diesen Bereich fällt.

Sie können wählen aus -

• Drei - Farbskala -
  • Grün - Gelb - Rot Farbskala
  • Rot - Gelb - Grün Farbskala
  • Grün - Weiß - Rot Farbskala
  • Rot - Weiß - Grün Farbskala
  • Blau - Weiß - Rot Farbskala
  • Rot - Weiß - Blau Farbskala
• Zweifarbige Skala -
  • Weiß - Rot Farbskala
  • Rot - Weiß Farbskala
  • Grün - Weiß Farbskala
  • Weiß - Grüne Farbskala
  • Grün - Gelbe Farbskala
  • Gelb - Grüne Farbskala

Befolgen Sie die unten angegebenen Schritte -

• Wählen Sie den Bereich aus, der bedingt formatiert werden soll.

• Klicken Conditional Formatting in dem Styles Gruppe unter Home Tab.

• Klicken Color Scalesaus dem Dropdown-Menü. DasColor Scale Optionen werden angezeigt.

• Klicken Sie auf die Farbskala Grün - Gelb - Rot.

Die Daten werden anhand der Farbskala Grün - Gelb - Rot im ausgewählten Bereich hervorgehoben.

• Wiederholen Sie die ersten drei Schritte.
• Klicken Sie auf die Farbskala Grün - Weiß.

Die Daten werden anhand der grün-weißen Farbskala im ausgewählten Bereich hervorgehoben.

Symbolsätze

Mit den Symbolsätzen können Sie numerische Unterschiede visualisieren. Die folgenden Symbolsätze sind verfügbar -

Wie Sie sehen, besteht ein Symbolsatz aus drei bis fünf Symbolen. Sie können Kriterien definieren, um jedem Wert in einem Zellbereich ein Symbol zuzuordnen. Zum Beispiel ein roter Abwärtspfeil für kleine Zahlen, ein grüner Aufwärtspfeil für große Zahlen und ein gelber horizontaler Pfeil für Zwischenwerte.

• Wählen Sie den Bereich aus, der bedingt formatiert werden soll.

• Klicken Conditional Formatting in dem Styles Gruppe unter Home Tab.

• Klicken Icon Setsaus dem Dropdown-Menü. DasIcon Sets Optionen werden angezeigt.

• Klicken Sie auf die drei farbigen Pfeile.

Farbige Pfeile werden neben den Daten angezeigt, basierend auf den Werten im ausgewählten Bereich.

• Wiederholen Sie die ersten drei Schritte. DasIcon Sets Optionen werden angezeigt.

• Wählen Sie 5 Bewertungen. Die Bewertungssymbole werden neben den Daten basierend auf den Werten im ausgewählten Bereich angezeigt.

Neue Regel

Sie können verwenden New Rule Erstellen einer eigenen Formel als Bedingung für das Formatieren einer Zelle, wie Sie sie definieren.

Es gibt zwei Möglichkeiten, die neue Regel zu verwenden:

• Mit New Rule Option aus dem Dropdown-Menü

• Mit New Rule Taste ein Manage Rules Dialogbox

Mit der Option Neue Regel aus dem Dropdown-Menü

• Wählen Sie den Bereich aus, der bedingt formatiert werden soll.

• Klicken Conditional Formatting in dem Styles Gruppe unter Home Tab.

• Klicken New Rule aus dem Dropdown-Menü.

Das New Formatting Rule Das Dialogfeld wird angezeigt.

• Wählen Sie im Feld Regeltyp auswählen die Option Formel verwenden aus, um zu bestimmen, welche Zellen formatiert werden sollen. Edit the Rule Description Feld erscheint.

• Geben Sie in den Formatwerten, in denen diese Formel wahr ist, die Formel ein.

• Klicken Sie auf die Schaltfläche Formatieren und dann auf OK.

Zellen, die Werte mit der Formel TRUE enthalten, werden wie definiert formatiert.

Mit der Schaltfläche "Neue Regel" im Dialogfeld "Regeln verwalten"

• Wählen Sie den Bereich aus, der bedingt formatiert werden soll.

• Klicken Conditional Formatting in dem Styles Gruppe unter Home Tab.

• Klicken Manage Rules aus dem Dropdown-Menü.

Das Conditional Formatting Rules Manager Das Dialogfeld wird angezeigt.

Drücke den New Rule Taste.

Das New Formatting Rule Das Dialogfeld wird angezeigt.

Wiederholen Sie die oben angegebenen Schritte, um Ihre Formel und Ihr Format zu definieren.

Das Conditional Formatting Rules Manager Das Dialogfeld wird mit definiert angezeigt New Rulehervorgehoben. Drücke denApply Taste.

Zellen, die Werte mit der Formel TRUE enthalten, werden wie definiert formatiert.

Klare Regeln

Sie können Regeln löschen, um alle bedingten Formate zu löschen, für die Sie erstellt haben

• Ausgewählte Zellen
• Aktuelles Arbeitsblatt
• Ausgewählte Tabelle
• Ausgewählte PivotTable

Befolgen Sie die angegebenen Schritte -

• Wählen Sie den Bereich aus / Klicken Sie auf ein Arbeitsblatt / Klicken Sie auf die Tabelle> PivotTable, in der bedingte Formatierungsregeln entfernt werden müssen.

• Klicken Conditional Formatting in dem Styles Gruppe unter Home Tab.

• Klicken Clear Rulesaus dem Dropdown-Menü. Die Optionen Regeln löschen werden angezeigt.

Wählen Sie die entsprechende Option. Die bedingte Formatierung wird aus dem Bereich / Arbeitsblatt / Tabelle / PivotTable gelöscht.

Regeln verwalten

Sie können Manage Rulesvon dem Conditional Formatting Rules ManagerFenster. Sie können Formatierungsregeln für die aktuelle Auswahl, für das gesamte aktuelle Arbeitsblatt, für die anderen Arbeitsblätter in der Arbeitsmappe oder für die Tabellen oder PivotTables in der Arbeitsmappe anzeigen.

• Klicken Conditional Formatting in dem Styles Gruppe unter Home Tab.

• Klicken Manage Rules aus dem Dropdown-Menü.

Das Conditional Formatting Rules Manager Das Dialogfeld wird angezeigt.

Klicken Sie auf den Pfeil im Listenfeld neben Show formatting rules for Aktuelle Auswahl, Dieses Arbeitsblatt und andere Blätter, Tabellen, PivotTable, falls mit Regeln für bedingte Formatierung vorhanden, werden angezeigt.

Wählen This Worksheetaus der Dropdown-Liste. Formatierungsregeln im aktuellen Arbeitsblatt werden in der Reihenfolge angezeigt, in der sie angewendet werden. Sie können diese Reihenfolge mit den Aufwärts- und Abwärtspfeilen ändern.

Sie können eine neue Regel hinzufügen, eine Regel bearbeiten und eine Regel löschen.

• Du hast es schon gesehen New Ruleim vorherigen Abschnitt. Sie können eine Regel löschen, indem Sie die Regel auswählen und auf klickenDelete Rule. Die hervorgehobene Regel wird gelöscht.

• Um eine Regel zu bearbeiten, wählen Sie die REGEL aus und klicken Sie auf Edit Rule. Edit Formatting Rule Das Dialogfeld wird angezeigt.

• Sie können

  • Wählen Sie einen Regeltyp

  • Bearbeiten Sie die Regelbeschreibung

  • Formatierung bearbeiten

• Wenn Sie mit den Änderungen fertig sind, klicken Sie auf OK.

• Die Änderungen für die Regel werden in der Conditional Formatting Rules ManagerDialogbox. KlickenApply.

• Die Daten werden basierend auf den geänderten hervorgehoben Conditional Formatting Rules.