Lernen Sie in Excel grüne Abweichungen bei positiven und rote Abweichungen bei negativen Werte zu erstellen
Wenn wir im Controlling arbeiten, oder eine Analyse erstellen, helfen uns Abweichungen zum Vorjahr oder Plan weiter.
Dabei werden die absoluten Werte, wie zum Beispiel Euro, gerne mit roter Schrift bei negativen Abweichungen erstellt und bei positiven Werten werden grüne Schrift. Umsetzen lässt sich dies mit bedingten Formatierungen.
Dies funktioniert, so lange es nicht zu viele Positionen gibt, die analysiert werden müssen, zum Beispiel Regionen wie Nord, Ost, Süd und West.
Wenn Sie nun allerdings mehrere Warengruppen oder Marken analysieren und über 20 Positionen haben, die alle eine negative Abweichungen im dreistelligen besitzen, wird es schon deutlich schwieriger, wie das folgende Beispiel zeigt:
Erkennen Sie die 3 höchsten negativen Abweichungen? - Nein?
Kein Wunder, wir haben hier auch einen kleinen Zahlendschungel.
Natürlich wäre es möglich, die höchsten Abweichungen mit automatischen Pfeilen zu markieren.
Das würde uns allerdings nur für ein paar und nicht für alle Marken weiterhelfen.
Wie werden rote Balken bei negativen und grüne Balken bei positiven Werten eingefügt?
Zunächst erstellen Sie eine ganz normale Tabelle mit den absoluten Abweichungen.
Rechts neben der Abweichungen schreiben Sie in der Überschrift POS für positive Abweichungen und NEG für negative Abweichungen.
Verwenden Sie folgende Formeln (in diesem Beispiel):
POS: =WENN(I5>0;I5;"")
NEG: =WENN(I5<0;I5;"")
Somit werden Ihnen in der Spalte POS nur die positiven und in der Spalte NEG nur die negativen Abweichungen angezeigt.
Schreiben Sie nun rechts in die Überschriften:
- Balken
- Faktor
- Format
Markieren Sie nun die erste Zelle unter der Überschrift Balken.
Gehen Sie dann auf die Register-Karte "Einfügen" und wählen Sie hier Symbole.
Stellen Sie die Schriftart auf "Arial" und suchen Sie nach dem Balken-Symbol. Sie können auch den ASCII Code 2588 verwenden.
Fügen Sie den Balken in die Zelle hinein und kopieren Sie diesen für alle anderen Zellen hinunter.
Für den Faktor schreiben Sie, in diesem Beispiel, in die Zelle Q2 die Bezeichnung "Faktor", rechts daneben, in die Zelle R2 schreiben Sie den Wert 0,015. Markieren Sie die Zelle R2 mit gelber Hintergrundfarbe, damit Sie wissen, dass es sich hier um einen Wert handelt, den Sie manuell anpassen können.
Verknüpfen Sie nun den Zelllwert aus R2 für die Zellen in der Tabelle:
Schreiben Sie nun in die Zellen unter Format:
'+#.##0;-#.##0
Damit wird bezweckt, dass positive Werte mit einem Pluszeichen und negative Werte mit einem Minus-Zeichen angezeigt werden.
Wie werden rote Balken bei negativen Werten in Excel eingefügt?
Minimieren Sie die Spalte O nun auf 25 Pixel.
Die Spalten P und Q vergrößern Sie auf je 200 Pixel Breite.
Verbinden Sie anschließen die Zellen P4 und Q4.
Schreiben Sie hier als Überschrift ∆ EUR hinein und formatieren Sie diese fett.
Fügen Sie eine Rahmenlinie unten hinzu.
In die Zelle P5 schreiben Sie nun folgende Formel:
=WENN(K5<>"";TEXT(K5;N5)&" "&WIEDERHOLEN(L5;ABS(K5)*M5);"")
Formatieren Sie die Zelle mit rote Schriftfarbe.
Sie sehen schon dass hier die Abweichung mit einem roten Balken dargestellt wird:
Wie werden positive bzw. grüne Balken in Excel eingefügt?
Für die positiven Werte bzw. grünen Balken benötigen wir in Excel allerdings eine andere Formel.
Ändern Sie zunächst den Ist-Wert, so dass Sie eine positive Abweichung haben und auch überprüfen können, ob Ihre Formel richtig ist.
Benutzen Sie folgende Formel für die grünen Balken:
=WENN(J5<>"";WIEDERHOLEN(L5;ABS(J5)*M5)&" "&TEXT(J5;N5);"")
Formatieren Sie die Zelle mit dunkelgrüner Schrift.
Jetzt werden Ihnen die positiven Werte in Excel als Balken inkl. Pluszeichen angezeigt.
Kopieren Sie die Formeln nun hinunter und verwenden Sie für die Zellen gepunktete Rahmenlinien.
Markieren Sie nun die Spalten H bis N und blenden Sie diese aus.
Je nach Abweichungswert wird Ihnen nun die richtige Größe angezeigt uns Sie erkennen wesentlich schneller, wo Sie die höchste Abweichung haben.
Über die Zelle R2 können Sie nun auch die Balken-Größe bei Bedarf individuell einstellen.
Möchten Sie gerne die dazugehörige Excel-Vorlage haben?
Melden Sie sich unten einfach für den Newsletter an und Sie erhalten noch viele weitere Tools zum Sofort-Download.
Autor
Kristoffer Ditz ist Leiter der Hanseatic Business School in Hamburg und freiberuflicher Dozent an der AMD Akademie Mode und Design / Hochschule Fresenius.
Er verfügt über mehrere Jahre Berufserfahrung u.a. bei der Puma AG, Ludwig Beck AG, Dänisches Bettenlager und der Hamburger Drogeriemarktkette Iwan Budnikowsky.
Für das Controlling Journal und Controller Magazin schreibt er regelmäßig Fachartikel zu den Themen Controlling und Microsoft Excel.
Zum Newsletter anmelden und tolle Excel Tools erhalten
Sie erhalten dann u.a. das eBook
25 Excel Formeln
zum Sofort-Download nach Anmeldung
Daneben erhalten Sie viele weitere Tools
Ihre Daten werden nicht an 3. weitergegeben - Datenschutz (Punkt 11)
Hinweis: Sie können sich jederzeit mit nur einem Mausklick wieder austragen.