Forecast-Diagramm mit Pfiff 6

Mit ein paar Tricks lässt sich ein schlichtes Liniendiagramm mit einer dynamischen Box aufpeppen.
 

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:

Das fertige Diagramm mit dynamischer Forecast-Box

Das fertige Diagramm mit dynamischer Forecast-Box

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).

Dropdown-Liste für Ist und Plan

Dropdown-Liste für Ist und 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:

Hilfstabelle 1

Hilfstabelle 1

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)

Formel für die Ist-Werte

Formel für die Ist-Werte

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:

Formel für die Planwerte

Formel für die Planwerte

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:

Das Liniendiagramm wird erstellt

Das Liniendiagramm wird erstellt

Und schon haben wir ein Diagramm, in welchem Ist- und Planwerte in verschiedenen Farben dargestellt werden:

Ist- und Planwerte in unterschiedlichen Farben

Ist- und Planwerte in unterschiedlichen Farben

Zurück zur Frage oben: Warum verwende ich in meiner Tabelle #NV, und nicht 0 oder „“ (leer)?
Weil in beiden Fällen im Diagramm unerwünschte Null-Linien angezeigt werden. Probiere es aus und ändere die Formeln in den Spalten E und F:

So geht's nicht: Nullwerte und leere Zellen

So geht’s nicht: Nullwerte und leere Zellen

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:

Eine Lücke im Diagramm

Eine Lücke im Diagramm

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:

Hilfstabelle 2: Der aktuelle Wert

Hilfstabelle 2: Der aktuelle Wert

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:

Neue Daten zum Diagramm hinzufügen

Neue Daten zum Diagramm hinzufügen

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:

Die Hilfstabelle 2 ist die Datenquelle

Die Hilfstabelle 2 ist die Datenquelle

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:

Der neue Punkt ist kaum zu sehen

Der neue Punkt ist kaum zu sehen

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:

Datenreihe "Aktuell" auswählen

Datenreihe „Aktuell“ auswählen

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:

Formatierung der Markierung anpassen

Formatierung der Markierung anpassen

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)

Datum für den ersten Datenpunkt

Datum für den ersten Datenpunkt

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!

Wert für den ersten Datenpunkt

Wert für den ersten Datenpunkt

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.

Datum für den zweiten Datenpunkt

Datum für den zweiten Datenpunkt

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.

Wert für den dritten Datenpunkt

Wert für den dritten Datenpunkt

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:

Die fertige Hilfstabelle 3

Die fertige Hilfstabelle 3

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:

Die letzte Datenreihe passt noch nicht

Die letzte Datenreihe passt noch nicht

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“:

Diagrammtyp der Datenreihe ändern

Diagrammtyp der Datenreihe ändern

Haken "Sekundärachse" entfernen

Haken „Sekundärachse“ entfernen

Nachdem du das Fenster mit OK geschlossen hast, sieht unser Diagramm schon fast fertig aus:

Die fast fertige Forecast-Box

Die fast fertige Forecast-Box

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:

Letzter Feinschliff

Letzter Feinschliff

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!

 

Bitte weitersagen, wenn dir der Artikel gefallen hat!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

6 Gedanken zu “Forecast-Diagramm mit Pfiff

  • Marcel

    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.

    • Martin Weiß Autor des Beitrags

      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

  • Peter L

    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.