Das Auge isst bekanntermaßen mit. Diese Erkenntnis sollte man sich besonders bei der Aufbereitung von endlosen Zahlenfriedhöfen in Excel zu Herzen nehmen. Denn auch hier gilt:
Ein Bild (sprich: Diagramm) sagt mehr als 1000 Worte.
Heute zeige ich dir, wie du bei der nächsten Präsentation vor deinem Chef mächtig Eindruck schinden kannst. Mit ein paar kleinen Tricks wird aus einem langweiligen Kreisdiagramm ein optisch ansprechender Tachometer.
Und so geht’s:
Die Ausgangslage
Ich habe eine kleine Beispieltabelle erstellt, die die Umsätze nach verschiedenen Verkaufsregionen zeigt. Gleichzeitig sieht man die Planwerte und den Grad der Zielerreichung. Als Vorbereitung für die folgenden Arbeiten habe ich über der Tabelle noch ein Dropdown-Feld erstellt, über das ich später die gewünschte Region auswählen kann:
Diese sehr nüchterne Darstellung wollen wir nun in einem Diagramm ein wenig „aufhübschen“.
Kreisdiagramm 1
Unser fertiger Tacho besteht im Wesentlichen aus zwei Kreisdiagrammen. Das erste davon soll zehn sichtbare Segmente haben, die alle gleich groß sind. Dazu benötigen wir eine kleine Hilfstabelle:
Jeder der 10 Bereiche wird später 10% auf unserem Tacho darstellen. Zusätzlich benötigen wir einen elften Bereich, den ich hier „Leer“ genannt habe. Wozu das gut ist, sehen wir gleich.
Nun erstellen wir das erste Kreisdiagramm auf Basis dieser Hilfstabelle:
Als Variante wähle ich den Ring („Donut“). Damit haben wir das erste Rohdiagramm, aus dem wir den Diagrammtitel und die Legende entfernen, so dass lediglich noch der Donut übrig bleibt:
Wie im Bild gezeigt, müssen wir das Diagramm ein wenig anpassen: Dazu machen wir einen Rechtsklick direkt in das Diagramm und wählen die Option „Datenreihen formatieren“. Nun passen wir in den Datenreihenoptionen den Winkel des ersten Segments auf 270 Grad und die Innenringgröße auf 40% an.
Nun kommt der erste Trick: Für das große Segment, welches in unserer Hilfstabelle dem Element „Leer“ entspricht, entfernen wir die Füllfarbe und die Linienfarbe. Dann klicken wir reihum die einzelnen verbleibenden Segmente an und färben sie wie gewünscht ein: Von Dunkelrot (ganz links) bis Dunkelgrün (ganz rechts). Deiner Fantasie und Kreativität sind hier keine Grenzen gesetzt:
Kreisdiagramm 2
Jeder Tacho braucht eine Tacho-Nadel. Dabei handelt es sich, wie wir gleich sehen werden, ebenfalls um ein Kreisdiagramm. Auch hierfür nutzen wir eine kleine Hilfstabelle:
Zur Erläuterung:
Die Hilfstabelle besteht aus drei Elementen: Das erste Element („Wert“) holt sich per SVERWEIS auf Basis der in Zelle C3 ausgewählten Region den dazugehörigen Zielerreichungsgrad. Diesen Prozentsatz teilen wir noch durch zwei. Warum? Da unser Tacho kein ganzer Kreis sein soll, sondern nur ein halber, müssen wir die darzustellenden Werte auch halbieren. Dadurch werden alle Werte im oberen, sichtbaren Teil unseres Kreisdiagramms dargestellt.
Über das zweite Element werden wir die Breite unserer Tacho-Nadel einstellen. Und das dritte Element errechnet sich automatisch aus der Differenz von 2 (= 200%) und den ersten beiden Elementen. Zur Erinnerung: Im ersten Kreisdiagramm ergeben die 10 + 1 Bereiche auch insgesamt 200%.
Dann entfernen wir vom Diagrammbereich (also vom Diagrammhintergrund) noch die Füllung und schieben unser neues Kreisdiagramm über das erste Diagramm und verringern die Größe so, dass das untere Diagramm dahinter ein wenig zu sehen ist. Anschließend ändern wir auch hier wieder den Winkel für das erste Segment auf 270 Grad:
(Hinweis: Damit man die Nadel am Anfang besser erkennt, habe ich in der Tabelle den Wert für die Breite auf 10% erhöht.)
Jetzt setzen wir den bereits oben verwendeten Trick mit der Farbe ein und entfernen die Füllung und die Linienfarben für das erste und das dritte Segment. Für das zweite Segment, welches unsere Tacho-Nadel darstellen soll, entfernen wir die Füllung, färben aber die Rahmenlinien schwarz und erhöhen die Linienstärke auf 3 oder 4 Punkt:
Der Tacho nimmt langsam Gestalt an!
Feintuning
Damit die Tacho-Nadel auch mehr wie eine Nadel aussieht, verringern wir in unserer Hilfstabelle den Wert für die Breite von 10% auf 0,5%. Damit bleiben praktisch nur noch die Rahmenlinien sichtbar.
Jetzt fügen wir über die Registerkarte „Einfügen – Formen“ noch ein kleines Kreissegment ein, welches wir scharz färben und sozusagen als Nabe über die Tacho-Nadel legen. Eine ebenfalls manuell eingefügte schwarze dicke Linie verbessert die Optik noch ein wenig:
Aber was wäre ein Tacho ohne Kilometeranzeige! In unserem Fall wollen wir den Umsatz der ausgewählten Region als Wert und natürlich den Namen der Region anzeigen lassen. Dazu habe ich unterhalb unserer Hilfstabelle in Zelle L8 über einen SVERWEIS den Umsatz ermittelt. Über entsprechende Schriftarten, -größen und Füllfarben sieht es ein wenig aus, wie eine richtige Kilometeranzeige.
In Zelle L10 habe ich lediglich einen Verweis auf die ausgewählte Region gesetzt und diese Zelle so formatiert, wie ich sie hinterher gerne in meinem Tacho sehen möchte.
Dann kopiere ich nacheinander diese beiden Hilfszellen, füge sie jeweils als verknüpftes Bild ein und ziehe sie mit der Maus an die gewünschte Stelle in meinem Tacho:
(zum Thema „Verknüpfte Grafiken“ hatte ich hier einen Artikel geschrieben).
Und somit haben wir unser fertiges Tacho-Diagramm, welches sich dynamisch ändert, sobald man in Zelle C3 eine andere Region auswählt:
Und wer sich die ganze Arbeit sparen möchte, kann sich hier die fertige Beispieldatei herunterladen.
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.
Habs sofort nachgebastelt!
Hallo,
Ich möchte gerne die Beispieldatei downloaden (Tacho Dia). Vielen Dank für’s zuschicken.
Mfg
Susanne Schütte
Hallo Susanne,
einfach oben ganz am Ende des Artikel auf den Link klicken.
Grüße,
Martin
Super Darstellung, vielen Dank dafür!
Vielleicht noch ein Tip für all diejenigen, die mit dem Dropdown Hilfe brauchen: http://www.traens.com/tipps/microsoft/excel-dropdown.html
Hallo!
Super Anleitung! Aber wie kann ich einstellen, dass die Tachonadel bei Übererfüllung (also z. B. 105%) nicht über den Rad springt, sondern bei 100% stoppt?
Viele Grüße!
Hallo TZ,
du kannst über eine MIN-Funktion den Wert für die Nadel auf 100% beschränken. Also in etwa so:
=MIN(Berechneter Wert;1)
Wenn der eigentlich berechnete Wert > 100% (also größer als 1) ist, wird 1 genommen.
Schöne Grüße,
Martin
Ich würde gerne im Nachhinein die Farben eines (z.B. 3stufigen) Tachos ändern, also z.B. von Rot-Gelb-Grün, auf Blau-Weiß-Orange.
Wie geht das? Geht das überhaupt?
Hallo,
selbstverständlich geht das. Da der Tacho aus zwei übereinanderliegenden Diagrammen besteht, musst Du erst das obere (mit der Nadel) ein wenig zur Seite schieben. Danach kannst Du wie oben beschrieben im Kreisdiagramm jedes einzelne Segment anklicken und dann über das Menü „Diagrammtools | Format | Fülleffekt“ die gewünschte Farbe zuweisen.
Schöne Grüße,
Martin
Hallo und vielen Dank für die tolle Vorlage. Ich habe ein Problem mit der Grafik unter dem Tacho. Wenn ich in der Tabelle die Region anpasse und z. B. Erreichbarkeit statt Nord eingebe wird mir in der Grafik nur rreichbarkei angezeigt. Wie kann ich die Grafik so anpassen, dass auch längere Worte angezeigt werden? Vielen Dank für eine Rückmeldung
Hallo Alexander,
du musst nur auf das Wort unter der Grafik klicken, damit wird das Element markiert und du kannst es dann über die Markierungspunkte auf die gewünschte Größe ziehen.
Schöne Grüße,
Martin
Hallo Martin,
herzlichen Dank für die tolle Beispieldatei. Eine große Hilfe, die mir viel Zeit spart!
Viele Grüße
Kathrin
Hallo Kathrin,
gern geschehen!
Schön Grüße,
Martin
Starkes Fundstück und super erklärt – vielen Dank.
Ich persönlich ziehe es vor, einfach 2 gleich große Bereiche zu machen, einen via leere Füllung auszublenden (wie beschrieben) und einen mit einem Farbverlauf von rot nach grün (oder anderweitig/auch komplexer) zu formatieren. Dann kann man sich die Abstufungen sparen und es sieht vielleicht auch hübscher aus – ist ja bekanntlich Geschmackssache 😉
Hallo Chris,
ja, die Idee mit dem Farbverlauf ist auch eine gute Variante – und sicherlich schneller in der Umsetzung.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für deine Anleitung. Ich habe nur noch eine Frage: in meiner Datei möchte ich die Hilfstabellen zwar und die Tachoanzeige auf dem selben Tabellenblatt wie den Tacho integrieren, aber zur besseren Übersichtlichkeit die Spalten ausblenden. Mein Problem ist nur, dass dann die verknüpften Grafiken ebenfalls verschwinden. Bei den Diagrammen kann ich ja einstellen, dass ausgeblendete Werte angezeigt werden sollen, aber wie geht das bei den verknüpften Grafiken? Hast du hier einen Tipp für mich?
Herzliche grüße
Susanne
Hallo Susanne,
mit verknüpften Grafiken funktioniert das meines Wissens nicht, die entsprechenden Zellen müssen immer eingeblendet sein
Schöne Grüße,
Martin
Hallo Martin,
erst mal ein großes Lob. Das Diagramm ist klasse.
Habe es mit ein paar Anpassungen versucht nachzubauen, was nicht so ganz klappt.
Zuerst habe ich 13 Bereiche genommen (für 130%), da ich auch Werte über 100% darstellen möchte.
Damit der Tacho nicht nur halbrund ist, habe ich die Bereiche auf 16(%) gestellt.
Das „Leer“-Feld hat dafür einen Wert von 152 (360-13×16).
Wenn der darzustellende Wert jetzt 100(%) ist, legt die Nadel eine Punktlandung hin. Bei allen anderen Werten leider nicht mehr.
Was muss ich hier anpassen, damit die Werte aus der Verkaufsregion wieder richtig im Diagramm dargestellt werden?
Ich habe schon viel versucht, bekomme es aber leider nicht hin.
Schade, dass man hier keinen Screenshot einfügen kann.
Hallo Peer,
da dieses Diagramm wirklich sehr speziell ist, kann ich hier leider keinen Tipp geben.
Schöne Grüße,
Martin
Eine super Sache. Wenn ich nun aber das Tacho als ganzes verschieben möchte, geht es nicht. Ich kann nur einzelne Elemente verschieben. Gibt es dafür auch einen Lösung?
Hallo gime,
ja, du musst dazu die ganzen Grafikelemente gruppieren. Am einfachsten geht das über das Menü „Start | Suchen und Auswählen | Inhalte auswählen | Objekte“. Damit werden alle Grafikobjekte im Arbeitsblatt markiert. Jetzt wählst du das Menü „Formformatierung | Gruppieren | Gruppieren“ bzw. in den Zeichentools das Menü „Format | Gruppieren | Gruppieren“. Danach kannst du das Diagramm komplett mit der Maus verschieben.
Schöne Grüße,
Martin
Lieber Martin,
vielen Dank für Deine Erklärungen! Es ist perfekt! Habe sofort nachgebastelt!
Nur in der zweiten Runde bin ich gescheitert, da meine Datentabelle auch negative Zahlen enthält. Diese werden natürlich im Kreisdiagram als „positiv“ gelesen und somit ist die Darstellung irreführend.
Muss schauen, wie ich das Thema lösen kann:)
Liebe Grüße, Kseniya
Hallo Kseniya,
freut mich, danke für dein Feedback. Da mein Tacho ja nur einen Halbkreis umfasst, könntest du über eine WENN-Funktion den Wert für die Nadel auf 0% setzen, sobald die Zielerreichung negativ ist.
Bezogen auf meine Beispieldatei würde dann in Zelle L3 stehen:
=WENN(SVERWEIS(C3;$B$7:$E$10;4;FALSCH)/2<0;0;SVERWEIS(C3;$B$7:$E$10;4;FALSCH)/2)
Damit bleibt die Nadel zumindest auf dem Nullwert liegen.
Vielleicht hilft das ja weiter.
Liebe Grüße,
Martin
Hallo,
besten Dank erstmals für die tolle Erklärung!
Ich habe nur ein Problem, sobald ich den Wert in L8 (Nord) verändere und eine andere Region auswähle zersägt es mir meine Tacho…
Wissen Sie woran das liegen könnte?
Vielen Dank im Voraus und beste Grüße,
Julian
Hallo Julian,
du beziehst dich sicherlich auf meine Beispieldatei. In Spalte L stehen nur Formeln, dort solltest du nichts verändern. Die Region sollte ausschließlich über das Dropdown-Feld in C3 geändert werden.
Schöne Grüße,
Martin
Hallo Martin,
kann man das auch mit einem Pivot-Chart realisieren?
VG
Andi
Hallo Andreas,
nein, mit Pivot-Charts wird das vermutlich nichts. Das Problem ist, dass die zugrunde liegenden beiden Hilfstabellen für die Diagramme größtenteils nur statische Werte enthalten, mit Ausnahme des einen Wertes für die Tacho-Nadel. Denn man greift für die Diagramme ja nicht direkt auf die Datentabelle mit den Umsätzen zu. Und damit sehe ich eigentlich keine Anwendung für Pivot-Diagramme.
Schöne Grüße,
Martin