In diesem Excel Tipp erfahren Sie, wie Sie die Abweichung in absolut und Prozent mit Vorzeichen darstellen können
Wenn wir im Controlling bzw. mit Excel arbeiten und nun eine Analyse durchführen, kommen wir zwangsläufig nicht an den Abweichungen vorbei, mit anderen Worten, die sogenannte Soll- Ist-Analyse.
Eine absolute Abweichung ist hier noch am einfachsten.
Dazu nehmen wir einfach die Zellen der Ist- und Vergleichswerte (Vorjahr, Plan, Forecast, etc) und ziehen diese mit Minus ab.
Beispiel: =B4-C4
Für die prozentualen Abweichungen ist dies auch relativ einfach.
Hierzu benutzen wir einfach den Backslash "/" und subtrahieren mit minus 1.
Beispiel: =B4/C4-1
Natürlich ist es auch möglich mit mal 100 zu arbeiten, aber das ist Geschmackssache.
In dem oben genannten Beispiel sehen wir in den Zellen D5 und E5 eine positive Abweichung.
Wenn kein Vorzeichen dabei ist, kennen wir aus der Mathematik, dass es sich um einen positiven Wert handelt.
Doch was ist, wenn wir nun bei den positiven Abweichungen auch das Plus "+" als Vorzeichen haben möchten?
Excel Lösung: Abweichungen absolut mit Vorzeichen
Hierfür verwenden wir einfach eine WENN-Formel und zwar:
absolut
=WENN(B5-C5>=0;"+"&RUNDEN(B5-C5;0);B5-C5)
Hier sehen wir, dass wenn die Abweichungen größer gleich 0 ist ein Plus-Zeichen hinzugefügt wird. Sollte der Wert kleiner sein, muss nicht extra ein Minus erstellt werden.
Die Funktion RUNDEN sorgt dafür, dass wenn Sie "krumme" Werte haben, nur die absoluten Werte sichtbar sind, sonst haben Sie zum Beispiel +20,987689
Hinweis: Bei einer Abweichung von 0 wird auch ein Pluszeichen dabei sein. Eine schwarze null, die aus betriebswirtschaftlicher Sicht positiv ist.
Excel-Lösung: Abweichungen in Prozent mit Vorzeichen
Bei den prozentualen Abweichungen können wir die Formel nicht verwenden, weil diese hier als Text umgewandelt wird, wie in dem folgenden Bild zu sehen ist.
Hierfür müssen wir die Formel etwas erweitern und die Formel mit 100 multiplizieren. Im Anschluss fügen wir das Prozent-Zeichen als Text hinzu.
Die Formel lautet nun:
=WENN(B5/C5-1>=0;"+"&RUNDEN((B5/C5-1)*100;0)&"%";B5/C5-1)
Wichtig dabei ist, dass die Abweichung B5/C5-1 in Klammern gesetzt und erst dann mit 100 multipliziert wird und auch hier wie bei der absoluten Abweichung die Funktion RUNDEN mit eingebaut wird. Zusätzlich wird das Prozentzeichen hinzugefügt.
Ich wünsche Ihnen viel Spaß beim Ausprobieren!
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.