In gewissen Diagrammen will man dem Leser gewisse Datenpunkte besonders Hervorheben. So kann sich dieser gleich auf das Wesentliche konzentrieren.

Wer das manuell macht, z.B. mit einem eingefügten Pfeil, muss sich immer wieder erneut damit herumgeschlagen den Bericht manuell nachzubessern (mir gehen solche Sachen dann immer vergessen!).

Damit man nicht manuell Hand anlegen muss, und trotzdem starke Aussagekräftige Diagramme erstellen kann, zeige ich im heutigen Beitrag wie Werte dynamisch in Diagrammen einblendet.

Wer sich gleich das Beispiel anschauen will kann das hier tun:

Erstellen des Basis Diagramm

Die Werte für das Diagramm sind lediglich zwei Datenreihen:

Diese erfassen wir zuerst in einem ganz normalen Diagramm wie unten abgebildet.

Wie Ihr seht habe ich anstatt nur zwei, drei Datenreihen in einem Diagramm eingefügt. Die ersten beiden Datenreihen haben die selben Werte und sind mit den Umsatzzahlen verlinkt.

Weil ich die Umsatzwerte visuell etwas hervorheben wollte, habe ich diese als hellblauen Hintergrund und gleichzeitig auch als blaue Linie dargestellt. Die Kosten sind als einfache orange Linie im Diagramm abgebildet. Mit ein wenig Formatierung habe ich jeweils die Farbe, Linie, und Beschriftung der Daten im Diagramm bearbeitet.

Bis jetzt handelt es sich noch um eine normale Tabelle wie sie Excel auch automatisch erstellen kann.

Wir wollen nun aber eine dynamische Anzeige im Diagramm haben damit wir gleich auf den ersten Blick sehen welches Quartal das profitabelste war! Als Resultat möchten wir diese Anzeige haben:

Einfügen des Delta Wertes in das Diagramm

Damit wir das Delta im Diagramm dynamisch anzeigen können müssen wir eine weitere Datenserie erfassen.

Eine senkrechte Linie im Diagramm erstellen wir am besten mit einem X-Y Diagramm. In Excel 2013 können wir mehr als nur zwei Diagrammtypen in einem einzigen Diagramm kombinieren! Ein wesentlicher Vorteil gegenüber den früheren Versionen.

Wir erstellen zuerst die Koordinaten welche wir benötigen. Diese tragen wir für den Moment noch manuell ein.

Als nächstes erfassen wir die neuen Koordinaten im Diagramm wie gewohnt über “Daten auswählen” im Diagrammtools Menü und gehen danach direkt auf Diagrammtyp ändern.

Das Diagrammtyp ändern Fenster sieht nun so aus…

Der Strich für die dynamische Anzeige ist erfasst.

Das selbe tun wir auch für den Anzeigetext welchen wir bei der Linie hinzufügen wollen.

Wir erstellen wiederum zuerst die Koordinaten, noch ohne Formeln, und fügen dann abermals eine Datenserie die wir als X-Y Diagramm formatieren ein.

Als Letztes, formatieren wir das gesamte Diagramm so wie es am Schluss aussehen soll. Weitere Anpassungen an der Formatierung des Diagramms müssen nachher keine mehr gemacht werden!

Dynamische Berechnung des Maximalen Delta Wertes

Damit das Diagramm jetzt aber dynamisch wird, müssen die Zellen welche wir für die Deltawerte ausgewählt haben (und in denen aktuell noch feste Werte stehen), auch mit Formeln hinterlegen werden welche sich anpassen können.

Unser Ziel ist es immer die Maximale Gewinnspanne anzuzeigen und diese im XY Raster wiederzugeben. Damit wir das machen können müssen wir zuerst ausfindig machen in welchem Quartal am meisten Verdient wurde.

Das machen wir am besten mit einer Matrix Formel und einer Hilfszeile!

In der Hilfszeile geben wir diese Formel als Matrix Formeln ein (und am Schluss CTRL+SHIFT+ENTER drücken).

{=N(MAX(C4:R4-C5:R5)=C4:R4-C5:R5)}

Was macht die Matrix Formel?

Kurz erklärt in Worten: Sie berechnet für alle Quartale die Differenz, prüft an welcher Stelle der Maximale Wert, liegt und markiert diese Stelle mit einem Wert von 1.

Da die Matrix Formel in diesem Fall das Herzstück ist der dynamischen Anzeige, schauen wir es uns genauer an:

Schritt 1:

Alle Delta Werte werden berechnet.

Resultat von Schritt 1:

=N(MAX({10\9\4\10\11\13\14\17\18\12\10\14\10\8\3\5})=C4:R4-C5:R5)

Schritt 2:

Der Maximale Wert wird gesucht.

Resultat von Schritt 2:

=N(18=C4:R4-C5:R5)

Schritt 3:

Damit wir wissen an welcher Stelle in der Matrix der richtige Wert steht, vergleiche ich den Maximal Wert mit der gesamten Matrix (Schritt 3.2), wobei das Resultat nun nur noch WAHR oder FALSCH sein kann. Damit ich das aber tun kann, muss ich aber erneut alle Differenzen ausrechnen (Schritt 3.1).

Resultat von Schritt 3.1:

=N(18={10\9\4\10\11\13\14\17\18\12\10\14\10\8\3\5})

Resultat von Schritt 3.2:

=N({FALSCH\FALSCH\FALSCH\FALSCH\FALSCH\FALSCH\FALSCH\FALSCH\WAHR\FALSCH\FALSCH\FALSCH\FALSCH\FALSCH\FALSCH\FALSCH})

Schritt 4:

Eigentlich könnten wir Schritt 4 überspringen und gleich so weitermachen, aber ich ziehe es vor, die WAHR / FALSCH Werte als Zahlen zu haben. Dies machen wir mit der Formel N().

Resultat von Schritt 4:

={0\0\0\0\0\0\0\0\1\0\0\0\0\0\0\0}

Alle Anderen Werte Verlinken

Die restlichen Werte anhand des lokalisierten Maximalen Wertes zu eruieren ist nun nicht mehr so schwer.

Dank dem dass VERGLEICH() die Stelle in einer Matrix zurück gibt, und nicht den Inhalt der Zelle, können wir die Formeln benutzen um  den X Punkt, also dort auf der X Achse wo wir den Strich haben möchten, ausfindig zu machen.

=VERGLEICH(1;$C$6:$R$6;0)

Mit der definierten X Achse wird es ein leichtes die Y-Koordinaten ausfindig zu machen. Hierzu benutzen wir INDEX(). Wir benutzen die Formeln mehrmals um sowohl den Strich wie auch den Text korrekt zu berechnen (und damit im Diagramm zu platzieren).

=INDEX($C$4:$R$5;1;C9)

Zuletzt können wir auch den Diagramm Titel Verlinken, damit auch die Aussage über das profitabelste Quartal im Diagramm dynamisch angepasst wird.

=”Max. DB I im “&INDEX($C$3:$R$3;C9)&” erwartet”

Fertig

So sieht das fertige Diagramm mit allen dynamischen Elementen aus:

Ich hoffe diese Einführung in dynamische Diagramme war für Euch hilfreich! Wenn es das war, dann bitte ich Euch den Beitrag mit Euren Freunden oder Arbeitskollegen zu teilen!