Bedingte Formatierung & Co – 3 Top Excel-Techniken im Controlling

Bild: Lord of the Dice von CGP Grey. Lizenz: CC BY 2.0

Vorbereitung und freier Download der Excel-Vorlage

Als Beispiel werden in einem Produktionsbetrieb 7 verschiedene Felgen in mehreren Stufen bearbeitet, bevor sie in den Vertrieb gelangen. Die Prozessschritte lauten CNC, Lackieren, Härten und Messen. Jedem Prozessschritt sind maximal erlaubte Arbeitsschritte zugeordnet, wie diese Tabelle zeigt:

Weil die Felgentypen eine unterschiedliche Anzahl an Arbeitsschritten in den einzelnen Stufen jeweils benötigen, ist es sinnvoll die Maschinen in der Bearbeitung der Felgentypen zu optimieren. Die erlaubten Arbeitsschritte, wie sie in der Tabelle angegeben sind, sind daher variabel und können geändert werden.

Damit Sie die Techniken der bedingten Formatierung, S-Verweis und dynamische Grafiken direkt ausprobieren können, laden Sie diese in Excel 2007 erstellt Vorlage herunter. Die Datei enthält zwei kleine Tabellen: Eine Primärtabelle mit den vorgegebenen Arbeitsschritten, die zur Bearbeitung der 7 Felgentypen benötigt werden, und eine Sekundärtabelle mit den variabel einstellbaren Arbeitsschritten der Maschinen.

Einteilung Primär- und Sekundär-Tabelle. Für die Zellen F2 – F8 soll eine Funktion über das Bearbeitungsfeld eingetragen werden, die automatisch auf die markierten Zellen kopiert und angepasst wird.

Dlie bedingte Formatierung

Mit den Befehlen dieses Menüs können Sie den Zellen eines Tabellenblattes Regeln zuweisen, mit denen die Formatierung der Zellen gesteuert wird. Das Erstellen, Bearbeiten, Löschen und Anzeigen aller Regeln zur bedingten Formatierung erfolgt in der Arbeitsmappe über die Registerkarte Start/Bedingte Formatierung/Regeln. Wenn zwei oder mehr Regeln zur bedingten Formatierung für einen Bereich gelten, werden die Regeln gemäß ihrem Rang nacheinander ausgewertet.

Über die ersten fünf Befehle können Sie vorgefertigte Regeln aufrufen, die nur noch minimal angepasst werden müssen. Für die Lösung der vorgestellten Aufgabe müssen jedoch die Formatierungsregeln manuell vergeben werden.

Übung 1: Hervorheben von Zellen mit Text in der Spalte „Freigabe“ und der dazugehörigen Zellen in Spalte „Felgentyp“

Im ersten Beispiel sollen in der Primär-Tabelle alle Zellen in der Spalte „Freigabe“ und „Felgentyp“ rot hinterlegt werden, wenn das Ergebnis einer WENN-Prüfung ergibt, dass einer der Werte in der zugehörigen Zeile unterhalb der vorgeschriebenen Anzahl an Arbeitsschritten liegt. Die Anzahl der Arbeitsschritte soll dabei nicht in der Regel fixiert sein, sondern variabel aus der Sekundär-Tabelle ausgelesen werden können. Auf diese Weise lässt sich die Anzahl der geforderten Arbeitsschritte bei einer Anpassung in der Sekundär-Tabelle variabel überprüfen und automatisch in der Primär-Tabelle formatiert darstellen.

• Markieren Sie die Zellen F2 – F8 in der soeben heruntergeladenen Excel-Vorlage und klicken Sie anschließend in die Bearbeitungsleiste.
• Geben Sie nun folgende Funktion ein und bestätigen Sie mit Enter. =WENN((B2>=$I$2)*(C2>=$I$3)*(D2>=$I$4)*(E2>=$I$5)=1;”JA”;”NEIN”)
  [pw-clippy caption=“Copy“]=WENN((B2>=$I$2)*(C2>=$I$3)*(D2>=$I$4)*(E2>=$I$5)=1;“JA“;“NEIN“)[/pw-clippy]

Da die Funktion in eine formatierte Tabelle eingefügt wird, wird die Formel in jeder Zeile einmal berechnet ausgegeben.

Ergebnis nach Einfügen der Funktion in die Tabelle. Für AGZ 1 und GRM ZRB sollte nun unter Freigabe „JA“ erscheinen, sonst „NEIN“.

Erläuterung der Funktion: Die Funktion WENN gibt einen bestimmten Wert zurück, wenn eine angegebene Bedingung als WAHR bewertet wird, und einen anderen Wert, wenn die Bedingung als FALSCH bewertet wird.

WENN(Prüfung;Dann_Wert;Sonst_Wert)

In diesem Beispiel soll in der Spalte „Freigabe“ der Text JA oder NEIN ausgegeben werden. JA soll ausgegeben werden, wenn die Werte aller Zellen (CNC, Lackieren, Härten, Messen) der Zeile den vorgegebenen Arbeitsschritten aus der Sekundär-Tabelle entsprechen. NEIN soll ausgegeben werden, wenn mindestens ein Wert der Zellen diese unterschreitet.

Die WENN-Funktion enthält zuerst die Bedingung (Prüfung), dass der jeweilige Wert der Zellen B2, C2, D2 und E2 größer oder gleich dem dazugehörigen Wert der Zellen I2, I3, I4 und I5 sein soll. Mit dem Vorzeichen “$” (Dollar) wird beim Übernehmen der Formel in die Zellen unterhalb von F2 sichergestellt, dass der Bezug zum jeweils vorgegebenen Wert der Spalte „Arbeitsschritte“ fixiert bleibt. Die zu prüfenden Zellen sollen sich verändern, denn beim Kopieren der Formel in die darunter liegende Zellen entsprechen die zu prüfenden Werte denen der Zellen B3, C3, D3 und E3, bzw. B4, C4, D4 und E4, usw.

Excel gibt Prüfungsergebnisse mit dem Ergebnis WAHR als Zahl 1 und FALSCH als Zahl 0 aus. Durch die Multiplikation und das Prüfkriterium =1 hinter der Rechnung, erlaubt die Prüfung aller Teilergebnisse die Ausgabe des Wertes =1 nur, wenn tatsächlich alle Zellen der Zeile WAHR (1) entsprechen.

In diesem Fall soll Excel den Text JA als Dann_Wert ausgeben, den Text NEIN als Sonst_Wert. Hierzu muss der auszugebende Text in „“ gesetzt werden, um als solcher von der Funktion erkannt werden. Sollte nach dem Kopieren der Formel ein Fehler auftreten, probieren Sie zunächst die Anführungszeichen manuell in Excel einzugeben.

Jetzt möchten wir mit Hilfe der bedingten Formatierung die Zellen farblich hervorheben. Freigegebene Felgentypen sollen auf grünem Hintergrund, nicht freigegebene Felgentypen sollen in roter Schrift auf rötlichem Hintergrund dargestellt werden.

• Markieren Sie die Zellen A2 – A8 und F2 – F8 (Strg-Taste beim Markieren des zweiten Bereichs gedrückt halten).
• Zeigen Sie die Registerkarte “Start” an und klicken Sie auf die Schaltfläche Bedingte Formatierung.
• Klicken Sie im Menü der Schaltfläche auf den Befehl Neue Regel.
• Wählen Sie den Regeltyp Formel zur Ermittlung der zu formatierenden Zellen verwenden aus.
• Geben Sie in das Eingabefeld folgende Formel ein:
  =$F2=“nein“
  [pw-clippy caption=“Copy“]=$F2=“nein“[/pw-clippy]

Erläuterung: Im diesem Beispiel sollen die Zellen in den markierten Spalten Felgentyp (A2 – A8) und Freigabe (F2 – F8) formatiert werden, wenn das Ergebnis der WENN-Prüfung NEIN ergibt.

Die eingegebene Formel gibt Excel die Bedingung für die Formatierung der markierten Zellen, und zwar wenn der Wert der Zelle in Spalte F (= $F2) NEIN (= “nein“) ist. Der Prüfwert für die Formatierung (JA oder NEIN) liegt in den Zellen der Spalte F, weswegen die Spaltenangabe mit $ fixiert werden muss.

• Klicken Sie auf die Schaltfläche Formatieren… und es öffnet sich das Dialogfeld Zellen Formatieren.
• Wählen Sie im Register Ausfüllen eine Hintergrundfarbe und im Register Schrift eine Schriftfarbe.

Abbildung: Formatierungsoption in Excel

• Bestätigen Sie die Formatierung und erstellen Sie die neue Regel durch Klicken auf OK im Dialogfeld Neue Formatierungsregel.

So sieht das Ergebnis bei Nutzung der bedingten Formatierung aus.

Geben Sie eine andere Anzahl an Arbeitsschritten in der Sekundär-Tabelle oder an durchgeführten Arbeitsschritten in der Primär-Tabelle ein und prüfen Sie, ob die Formatierung entsprechend angepasst wird.

Übung 2: Hervorheben von Zellen durch SVerweis

Im nächsten Beispiel sollen in der Primär-Tabelle die Zellen der Spalten „CNC“, „Lackieren“, „Härten“ und „Messen“ rot hinterlegt werden, wenn diese die Vorgaben aus der Sekundär-Tabelle unterschreiten. Mit dieser Formatierung lassen sich die Zellen anzeigen, welche die Freigabe der Felge verhindern.

• Markieren Sie die Zellen B2 – E8.
• Rufen Sie nun den Befehl Bedingte Formatierung/Neue Regel auf und wählen Sie den Regeltyp Formel zur Ermittlung der zu formatierenden Zellen verwenden aus.
• Geben Sie in das Eingabefeld folgende Formel ein:
  =B2<SVERWEIS(B$1;$H$2:$I$5;2;0)
  [pw-clippy caption=“Copy“]=B2<SVERWEIS(B$1;$H$2:$I$5;2;0)[/pw-clippy]

Erläuterung: Das S in SVERWEIS steht für senkrecht. Er sucht in der ersten Spalte einer Tabellenmatrix nach einem Wert und gibt in der gleichen Zeile einen Wert aus einer anderen Spalte in der Tabellenmatrix zurück.

SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)

Die Formatierung in diesem Beispiel soll erfolgen, wenn der Wert der Zelle (=B2, hier stellvertretend für alle anderen markierten Zellen) kleiner (<) ist als der vorgegebene Wert der Zelle in der Sekundär-Tabelle.

Mit SVERWEIS wird zunächst das Suchkriterium definiert, also der Wert, der in der ersten Spalte der Matrix (markierter Bereich) gefunden werden soll. Das Suchkriterium entspricht der Spaltenüberschrift „CNC“ (B$1). Die Spalte ist durch die Markierung B bis E flexibel, die Zeile 1 muss jedoch mit $ fixiert werden.

Die Matrix enthält den zu durchsuchenden Bereich ($H$2:$I$5). Die Werte in der ersten Spalte der Matrix sind die Werte, die vom Suchkriterium durchsucht werden („CNC“,“Lackieren“, usw.).

Der Spaltenindex bezeichnet die Spaltennummer der Matrix, aus welcher der entsprechende Wert zurückgegeben werden soll. Der Spaltenindex von 2 in der Formel gibt somit den Wert der zweiten Spalte der Matrix zurück (Arbeitsschritte für CNC = 3).

Mit Bereich_Verweis wird vorgegeben, ob mit FALSCH (0) eine genaue Übereinstimmung, oder mit WAHR (1) eine ungefähre Übereinstimmung gesucht werden soll. Wenn dieser Parameter WAHR ist oder weggelassen wird und keine genaue Entsprechung gefunden wird, wird der nächstgrößere Wert zurückgegeben, der kleiner als das Suchkriterium ist. Ist der Parameter FALSCH und es wird keine genaue Übereinstimmung gefunden, wird der Fehlerwert #NV zurückgegeben.

• Wählen Sie durch die Schaltfläche Formatieren… die gewünschte Hervorhebung und klicken Sie OK.

• Erstellen sie die neue Regel durch Klicken auf OK im Dialogfeld Neue Formatierungsregel.
• Ändern Sie einige Werte der Tabellen und prüfen Sie, ob die Darstellung der Zellen entsprechend wechselt.

Im Gegensatz zu früheren Versionen ist es ab Excel 2007 auch möglich, mehrere Regeln für eine Zelle zu definieren. Eine Regel, die sich an höherer Stelle in der Liste befindet, besitzt Vorrang vor einer Regel weiter unten in der Liste. Standardmäßig werden neue Regeln stets an den Anfang der Liste hinzugefügt und besitzen daher Vorrang. Sie können jedoch die Rangfolge mithilfe der Pfeile Nach Oben und Nach unten im Dialogfeld ändern.

• Öffnen Sie den Regelmanager: Bedingte Formatierung/Regeln verwalten.
• Wechseln Sie die Anzeige der Formatierungsregeln von Aktuelle Auswahl auf Dieses Arbeitsblatt.
• Gelten mehrere Regeln zur bedingten Formatierung für den eigestellten Bereich, kann mithilfe der Pfeiltasten im Dialogfeld die Reihenfolge und somit auch die Rangfolge der Regeln geändert werden. Wenn Sie möchten, so können Sie hier die Rangfolge der bedingten Formatierungsregeln ändern. Für dieses Beispiel hat eine Rangänderung zwar keine Auswirkung, dennoch wollten wir Ihnen der Vollständigkeit halber diese Einstellung zeigen, da es in Ihren zukünftigen Projekten wichtig sein könnte.

Mit dem Regelmanager können Sie die Rangfolge mehrerer bedingter Formatierungsregeln ändern und sich schnell einen Überblick über alle Regeln im Geltungsbereich verschaffen.

Übung 3: Erstellen eines dynamischen Diagramms durch Bezug zu einem Feld mit Datenüberprüfung

Im dritten Beispiel soll aus den Daten der Primär-Tabelle ein Fortschritts-Diagramm für den jeweiligen Felgentyp mit Bezug auf die bereits durchgeführten Arbeitsschritte je Prozess erstellt werden. Das Diagramm soll dabei nur durch die Auswahl des Felgentyps in einem formatierten Feld mit Datenüberprüfung automatisch angepasst werden.

• Klicken Sie in das Feld B10.
• Wählen Sie die Registerkarte Daten und klicken Sie auf die Schaltfläche Datenüberprüfung.

Mit der Datenüberprüfung können Sie die Datentypen oder Werte steuern, die von Benutzern in eine Zelle eingegeben werden. Sie können beispielsweise die Auswahl durch eine Liste eingrenzen oder sicherstellen, dass Zahlen nur bis zu einem Maximalwert eingegeben werden.

• Wählen Sie den Befehl Datenüberprüfung zum Öffnen des Dialogfensters.

• Öffnen Sie im Register Einstellung das Dropdown-Menü im Eingabefenster Zulassen und wählen Sie Liste (siehe oben).
• Im Eingabefeld Quelle können Sie jetzt eine Liste, bzw. den Bereich festlegen. Die Eingabe kann manuell, oder über Markieren des Bereichs mit der Maus erfolgen.

Hinweis: Stellen Sie sicher, dass die Option Zellendropdown aktiviert ist.

• In diesem Beispiel soll eine Auswahlliste der verschiedenen Felgentypen erstellt werden. Markieren Sie daher als Quelle die Zellen A2 – A8 oder schreiben Sie ins Feld Quelle =$A$2:$A$8
  [pw-clippy caption=“Copy“]=$A$2:$A$8[/pw-clippy]
• Beenden Sie die Eingabe mit OK.
• Geben Sie in die Zelle C12 folgende Formel ein:
  =SVERWEIS($B$10;$A$2:$F$8;2)/$I2
  [pw-clippy caption=“Copy“]=SVERWEIS($B$10;$A$2:$F$8;2)/$I2[/pw-clippy]

Erläuterung: Mit dem SVERWEIS wird festgelegt, dass das Suchkriterium in Zelle B10 mit der ersten Spalte der Matrix A2:F8 abgeglichen werden soll. Da sich sowohl die Zelle des Suchkriteriums, wie auch die Matrix nicht verändern sollen, werden die Daten mit $ fixiert.

Entspricht der Wert in der ersten Spalte der Matrix dem Suchkriterium, soll der Wert der zweiten Spalte ausgegeben werden. Für die Zellen C13 – C16 muss der jeweilige Spaltenindex manuell an die jeweilige Spalte angepasst werden (Lackieren ist Spalte 3, usw.).

Da der Fortschritt in Prozent ausgegeben werden soll, wird der Wert der Zelle mit dem Vorgabewert in der Zelle I2 aus der Sekundär-Tabelle dividiert. In diesem Fall wird lediglich die Spalte fixiert, da sich die Bezugswerte in der Sekundär-Tabelle beim kopieren der Formel in Tabelle nach unten, ebenfalls nach unten verschieben.

• Kopieren Sie die Formel aus Zelle C12 in die Zellen C13 – C16.
• Passen Sie den Spaltenindex in den Zellen C13 – C16 wie beschrieben an (Lackieren = 3, Härten = 4, Messen = 5, Freigabe = 6).

• Markieren Sie die Zellen C12 – C15.
• Wählen Sie die Registerkarte Start und klicken Sie auf die Schaltfläche Prozentformat.

• Wählen Sie über das erzeugte Auswahlmenü in Zelle B10 einige Werte und prüfen Sie, ob die Werte der Zellen C12 – C16 entsprechend wechseln und korrekt angezeigt werden. Im Bild sehen Sie das Ergebnis für den Felgentyp AGZ 1C.

• Markieren Sie die Zellen B12 – C15.
• Fügen Sie über die Registerkarte Einfügen und die Schaltfläche Diagramm ein gruppiertes Säulendiagramm ein.

• Ändern Sie im Auswahlmenü den Felgentyp und prüfen Sie, ob sich das Diagramm verändert und die Werte der Tabelle korrekt übernommen werden.

• Markieren Sie das Diagramm und ändern Sie das Layout des Diagramms über die Registerkarte „Entwurf“. Wählen Sie unter Diagrammlayouts den Typ Layout 10.

Abbildung: Im Register Entwurf lässt sich die Formatierung des markierten Diagramms mit Vorlagen anpassen.

• Öffnen Sie durch Rechtsklick auf das Diagramm das Kontextmenü.

Abbildung: Das Diagramm enthält jetzt einen Diagrammtitel und die Prozentwerte der Tabelle als Beschriftung der Balken.

• Öffnen Sie das Dialogfeld zur Bearbeitung des Diagramms mit Daten auswählen…

Abbildung: Mit einem Rechtsklick auf das Diagramm wird das Kontextmenü mit weiteren Formatierungsmöglichkeiten aufgerufen.

• Wählen Sie die Schalfläche Bearbeiten unter Legendeneinträge (Reihen).

Abbildung: Im Dialogfeld Datenquelle auswählen lassen sich die Werte zur Beschriftung des Diagramms ändern.

• Klicken Sie in das Eingabefeld Reihenname.

Abbildung: Im Dialogfeld Datenreihe bearbeiten, kann der Bereich der Reihenwerte sowie der Reihennamen festgelegt werden.

• Klicken Sie auf die Schaltfläche Bereich auswählen und markieren Sie die Zelle B10 mit dem Auswahlmenü für die Felgentypen.
• Wenn die Daten der Zelle B10 in das Eingabefeld übernommen wurden, bestätigen Sie mit OK.

Abbildung: Reihenname und Diagrammtitel entsprechen nun dem Wert der Zelle B10. Ändert sich dieser, ändert sich neben den Daten in der Tabelle und den Diagrammwerten (Balken) auch der Diagrammtitel und die Legendenbeschriftung.

• Bestätigen Sie die Änderung der Datenquelle mit OK, um das Dialogfenster zu schließen.
• Markieren Sie die Legende rechts neben den Diagrammbalken mit der Maus und entfernen Sie diese.
• Ändern Sie im Auswahlmenü den Felgentyp und prüfen Sie, ob sich neben dem Diagramm nun auch der Diagrammtitel verändert.

Abbildung: Das fertige Ergebnis ist eine Tabelle die übersichtlich darstellt, welche Prozesse noch nicht die vorgegebenen Arbeitsschritte durchlaufen haben. Eine Fortschrittsanzeige nach Felgentyp kann sowohl in einer Tabelle, wie auch in einem Diagramm angezeigt und in einem Report verwendet werden.

Nun können Sie an dem Beispiel die Zahlen der Sekundärtabelle nach Belieben ändern und beobachten, wie sich Grafik und Tabelle dynamisch anpassen.

Auf Lecturio können Controller übrigens noch viel mehr praktische Tipps aus über 219 Videokursen direkt in die tägliche Büroarbeit einbringen. Ein Blick lohnt sich allemal!

Oder wollen Sie Ihre Excel Kenntnisse weiter vertiefen? In unserer Excel-Reihe lernen Sie, wie spielend einfach es ist, einen Kalender in Excel zu erstellen, wie Sie Makros unter dem Aspekt der Sicherheit ihres Betriebssystems aktivieren sollten und erfahren alles über die Berechnung der Standardabweichung (eine wichtige statistische Kennzahl zur Bewertung von Durchschnittswerten) mit Hilfe von Excel.