Die Idee zum heutigen Artikel habe ich mir bei meinem geschätzten Excel-Kollegen Andreas Thehos „ausgeliehen“. Der hat auf den Excellent Days 2018 ein Umsatzdiagramm gezeigt, das er mit ein paar Tricks um einen dynamischen Kasten erweitert hat. Durch diesen coolen Effekt lassen sich die Istwerte von den Planwerten sehr eindrucksvoll abgrenzen.
Wie das aussieht und wie man so ein Diagramm erstellt, zeigt der folgende Beitrag.
Und so geht’s:
Das Ende kommt am Anfang
Damit du eine Vorstellung davon bekommst, was wir hier überhaupt machen, zeige ich dir hier schon einmal das fertige Endergebnis:
Und hier kannst du dir die Beispieldatei herunterladen.
Die Ausgangslage
Es geht um eine einfache Tabelle, in der die monatlichen Umsätze eingetragen werden. In einer separaten Spalte wird über ein Dropdown-Feld ausgewählt, ob es sich bei dem angegebenen Wert um tatsächliche Umsätze handelt (= Ist) oder ob es noch ein Forecast ist (= Plan).
Um daraus ein Diagramm erstellen zu können, in dem die Ist- und die Planzahlen unterschiedlich dargestellt werden, muss die Umsatzspalte auf zwei Spalten aufgeteilt werden.
Schritt 1
Wir legen also rechts neben der vorhandenen Tabelle eine weitere Tabelle mit zwei Spalten an:
Je nachdem, ob hinter dem Umsatz in Spalte C ein „Ist“ oder ein „Plan“ steht, soll der Umsatz in einer der beiden Spalten erscheinen. Für die Ist-Werte habe ich dafür folgende einfache WENN-Abfrage verwendet:
=WENN(C3="Ist";B3;#NV)
Wie man im Bild sieht, wird jetzt für alle Planzahlen stattdessen ein #NV angezeigt. Aber warum gebe ich stattdessen nicht eine Null aus? Oder ein Leerzeichen? Das erkläre ich gleich, wenn wir das Diagramm erstellen.
Für die Plan-Werte ist eine leicht erweiterte Formel notwendig:
=WENN(ODER(C3="Plan";C4="Plan");B3;#NV)
Hier wird geprüft, ob in der aktuellen Zeile oder in der nächsten Zeile in Spalte C ein „Plan“ steht. Das führt dazu, dass der letzte Ist-Wert aus der Ursprungstabelle sowohl in der Ist- als auch in der Plan-Spalte angezeigt wird:
Auch dazu werde ich gleich noch eine Erklärung liefern. Zuerst wollen wir aber mal unser Diagramm erstellen.
Schritt 2: Das Liniendiagramm
Hierfür markiere ich die Bereiche mit dem Datum, der Ist- und der Plan-Spalte. Also die Bereiche A2:A14 sowie E2:F14. Dann wähle ich aus dem Menü „Einfügen“ das Liniendiagramm mit Datenpunkten aus:
Und schon haben wir ein Diagramm, in welchem Ist- und Planwerte in verschiedenen Farben dargestellt werden:
Zurück zur Frage oben: Warum verwende ich in meiner Tabelle #NV, und nicht 0 oder „“ (leer)?
Weil in den beiden letztgenannte Fällen im Diagramm unerwünschte Null-Linien angezeigt werden. Probiere es aus und ändere die Formeln in den Spalten E und F:
Fehlerwerte wie #NV hingegen ignoriert Excel einfach. Und das ist genau, was wir hier wollen.
Und nun noch zur Frage, warum in der letzten Ist-Zeile der Umsatz sowohl in der Ist- als auch in der Plan-Spalte dargestellt werden soll? Weil ansonsten eine Lücke im Diagramm entstehen würde. Gib dazu einfach vorübergehend in Zelle F10 in der Plan-Spalte ein #NV ein:
Der letzte Datenpunkt in der Ist-Spalte wäre in meinem Beispiel der 1. August und danach ist Ende. Erst am 1. September geht es wieder weiter mit den Planwerten.
Unsere erweiterte WENN-Formel hingegen tut so, als ob die Planung auch schon ab dem 1. August beginnen würde und damit haben wir eine durchgängige Linie.
Schritt 3: Der aktuelle Wert
Um den Übergang von Ist zu Plan noch deutlicher zu zeigen, wollen wir für den letzten Istwert einen dicken, roten Punkt ins Diagramm zeichnen. Dafür brauchen wir eine weitere Tabelle. Dort prüfe ich mit einer WENN-Formel, ob Ist- und Planwert identisch sind. Und wie wir jetzt wissen, ist nur für den jeweils letzten Ist-Wert der Fall:
Um diesen Wert im Diagramm aufzunehmen, markierst du das Diagramm durch einen einfachen Klick. Jetzt kannst du in den Diagrammtools in der Registerkarte „Entwurf“ über die Schaltfläche „Daten auswählen“ das entsprechende Dialogfenster aufrufen:
Hier klickst du auf die Schaltfläche „Hinzufügen“ und gibst im nächsten Fenster die Bereiche für den Reihennamen und die Reihenwerte an, indem du die jeweiligen Zellen einfach mit der Maus markierst:
Im Diagramm wirst du vermutlich zunächst noch wenig davon sehen. Standardmäßig wird einfach ein einzelner grauer Punkt gesetzt. Den gilt es jetzt etwas anzupassen:
Da ein einzelner Punkt immer etwas schwer zu treffen ist, gehst du am besten so vor:
In den Diagrammtools klickst du im Register „Format“ auf die Schaltfläche „Auswahl formatieren“. Es spielt keine Rolle, welches Diagrammelement markiert ist. Über diese Schaltfläche wird nämlich der Arbeitsbereich für die Formatierungen eingeblendet. Dort kannst du dann über das Auswahlfeld in den Datenreihenoptionen die Reihe „Aktuell“ auswählen. Und damit wird genau der eine Datenpunkt markiert:
Hier lässt sich dann für die Markierung die Größe und die Füllen nach den eigenen Vorstellungen anpassen, so dass der Datenpunkt gut sichtbar wird:
Schritt 4: Die dynamische Forecast-Box
Kommen wir jetzt langsam zum Finale. Es fehlt nur noch der Kasten, welcher den Forcastbereich umschließt. Das Ganze soll ja dynamisch sein und sich automatisch anpassen, wenn aus Planwerten Istwerte werden. Dazu ist eine letzte Hilfstabelle erforderlich, welche die für unsere Forecast-Box benötigten Datenpunkte enthält.
Technisch gesprochen handelt es sich dabei um ein Punktediagramm mit geraden Linien. Wir brauchen insgesamt 5 Datenpunkte, damit am Ende ein geschlossener Kasten herauskommt: Einen für jede Ecke und einen zusätzlichen, um zum Ausgangspunkt zurückzukommen.
Der erste Datenpunkt liegt auf dem Datum des letzten Istwertes. Dieses Datum berechnen wir mit einer Kombination aus INDEX und VERGLEICH:
=INDEX($A$3:$A$14;VERGLEICH("Plan";$C$3:$C$14;0)-1)
Die VERGLEICH-Funktion sucht in Spalte C nach dem ersten Planwert gesucht und von dieser Position der Wert 1 abgezogen, um auf den letzten Istwert zu kommen. Mit dem Ergebnis holt sich die INDEX-Funktion das passende Datum.
Etwas vertrackter ist die Bestimmung des Wertes. Man könnte jetzt dazu verleitet sein, einfach den zum Datum passenden Wert zu übernehmen. Das wäre jedoch etwas zu kurz gedacht, denn die linke untere Ecke des Kastens soll ja immer unterhalb des niedrigsten Planwertes liegen, damit der Kasten hinterher die komplette Planlinie umschließt. Ich habe daher folgende Matrix-Formel gewählt:
{=MIN(WENN(ISTZAHL(F3:F14);F3:F14))-5}
Beim Stichwort „Matrix-Formel“ sollte es bei dir klingeln: Diese Formel muss zwingend mit Strg+Umschalt+Eingabe abgeschlossen werden!
Diese Formel prüft, in welchen Zellen in der Spalte F Zahlen enthalten sind und bestimmt daraus das Minimum. Da die ISTZAHL-Funktion normalerweise nur eine einzelne Zelle verarbeiten kann, ist hier eine Matrix-Formel notwendig. Vom Ergebnis ziehe ich noch den Wert 5 ab, damit die Linie ein wenig unterhalb des niedrigsten Wertes beginnt. Aber das ist Geschmacksache.
Der zweite Datenpunkt muss auf dem letzten Datum liegen, daher wird hier einfach die MAX-Funktion verwendet, um das größte Datum zu bestimmen. Da vom Startpunkt eine gerade Linie gezogen werden soll, muss der Wert identisch mit dem Wert des Startpunktes sein (im Beispiel also 75 €). Hier verweise ich einfach auf die Zelle K3.
Der dritte Datenpunkt liegt auf der gleichen Linie wie der zweite, nur muss als Wert der Maximalwert aller Planzahlen bestimmt werden. Das geht analog wie beim ersten Datenpunkt, nur eben mit der MAX-Funktion:
{=MAX(WENN(ISTZAHL(F3:F14);F3:F14))+5}
Der Wert 5 wird addiert, damit der Punkt wieder etwas oberhalb des Maximalwertes liegt.
Der vierte Datenpunkt ist einfach: Das Datum entspricht dem des ersten Datenpunkts, der Wert dem des 3 Datenpunkts. Daher verweise ich in Zelle J6 auf Zelle J3 und in Zelle K6 auf K5.
Damit unsere Box am Ende eine geschlossen ist, wird ein letzter Datenpunkt benötigt. Und der ist identisch mit dem ersten. Die fertige Hilfstabelle sieht also so aus:
Jetzt müssen diese Werte nur noch im Diagramm aufgenommen werden. Dazu zeige ich dir einen kleinen Trick:
Markiere die eben erstellte Tabelle einschließlich der Überschriften (also den Bereich J2:K7) und kopiere das Ganze mit Strg+C in die Zwischenablage.
Dann klickst du einmal das vorhandene Diagramm an, so dass es markiert ist und fügst mit Strg+V die Daten aus der Zwischenablage ein – ja, auch so kann man einem Diagramm neue Daten hinzufügen!
Das Ergebnis sieht allerdings noch nicht ganz so aus, wie du es vielleicht erwartet hast:
Keine Sorge, mit ein paar kleinen Schritten haben wir das gleich. Markiere im Diagramm die gerade hinzugefügte Datenreihe, indem du sie einfach einmal anklickst. Öffne dann in den Diagrammtools im Register „Entwurf“ das Menü „Diagrammtyp ändern“. Ändere nun für die Datenreihe „Planungshorizont“ den Diagrammtyp auf „Punkt mit geraden Linien“ und entferne danach noch den Haken in der Spalte „Sekundärachse“:
Nachdem du das Fenster mit OK geschlossen hast, sieht unser Diagramm schon fast fertig aus:
Klicke die Datenreihe im Diagramm nochmal an und ändere dann in den Formatierungen die Linieneinstellungen auf „Einfarbige Linie“, Farbe Rot (oder was immer dir gefällt) und als Strichtyp eine gestrichelte Linie. Und damit hast du es endlich geschafft:
Wenn du nun in der Ursprungstabelle in Spalte C aus dem ersten „Plan“ ein „Ist“ machst oder aus dem letzten „Ist“ wieder ein „Plan“, wird der Kasten entsprechend mitwandern. Und genauso natürlich, wenn sich die Umsatzzahlen in Spalte B ändern. Ziemlich cool, oder?
Wie eingangs erwähnt, stammt die Idee dazu von Andreas Thehos, Ruhm und Ehre gebühren also ihm!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
sehr sehr cool. Gefällt mir 🙂
Hallo Annette,
dankeschön, freut mich!
Schöne Grüße,
Martin
Was soll ich großartig zum Beitrag schreiben?
Eine tolle Idee, ein toller Beitrag
Danke an Andreas und danke an Martin.
Ich werde das Thema ganz sicher auf meinem Laptop nachstellen.
Hallo Gerhard,
vielen Dank für das Feedback und viel Spaß beim Nachbauen.
Schöne Grüße,
Martin
Bei der Suche nach Min/Max hätte ich statt der Matrix-Formel die Aggregat-Funktion verwendet (z.b. =AGGREGAT(5;6;$F$3:$F$14)). Es ist für viele verständlicher und bei größeren Tabellen/Dateien kann es sich positiv auf die Performance auswirken.
Hallo Marcel,
ja, die Aggregat-Funktion ist eine gute Variante und Andreas Theos hat sie in seiner Lösung tatsächlich auch verwendet. Und der Hinweis auf die Performance ist berechtigt und sollte immer im Hinterkopf behalten werden.
Ich wollte mit der MIN/MAX-Funktion nur eine andere Möglichkeit aufzeigen, die auch für Anwender von Excel 2007 funktioniert, denn die AGGREGAT-Funktion wurde erst mit Excel 2010 eingeführt.
Wie immer: Viele Wege führen auch in Excel nach Rom 🙂
Schöne Grüße,
Martin
Hallo Zusammen,
ich finde Ihre Art der Darstellung und des Ablaufs prima. Mir hat es sehr gut gefallen u. ich werde Euch weiter empfehlen. Dies ist ein hervorragendes Beispiel für professionelles Arbeiten – es macht richtig Freunde.Also herzlichen Dank an Alle Mitwirkende.
Hallo Peter,
vielen Dank für das tolle Feedback!
Schöne Grüße,
Martin