Kennst du eigentlich „Sparklines“?
Sparklines sind kleine Grafiken, mit deren Hilfe Zahlenmaterial auf kleinstem Raum grafisch dargestellt werden kann. Man sieht sie oftmals im Zusammenhang mit Börsenkursen, wo z.B. die Entwicklung von einzelnen Aktienkursen im Tagesverlauf gezeigt wird.
In Excel gibt es seit der Version 2010 eine entsprechende Funktion, um Sparklines innerhalb einer Excel-Zelle darzustellen. Wer also z.B. mit Management-Dashboards arbeitet oder einfach seinen Zahlenfriedhöfen etwas optische Würze verpassen möchte, sollte sich die Sparklines unbedingt ansehen. Es ist wirklich einfach!
Und so geht’s:
Sparklines erstellen
Ich habe eine kleine Tabelle vorbereitet, in der ich die monatlichen Deckungsbeiträge für eine Reihe von Produkten eingetragen habe (wer will, kann sich die Datei hier herunterladen).
Für jedes dieser Produkte möchte ich am rechten Rand eine sogenannte Sparkline erstellen und stelle dazu den Cursor neben meine Tabelle in die leere Zelle N4.
Die Ausgangstabelle
Nun öffne ich im Menüband das Register „Einfügen“. Dort befindet sich die Gruppe „Sparklines“, aus der ich den gewünschten Diagrammtyp auswählen kann:
Einfügen von Sparklines
Ich habe mich für die Säulen entschieden. Im folgenden Fenster gebe ich als Datenbereich alle Wertezellen für das Produkt 1 an, also den Bereich B4:M4
Im Feld Positionsbereich steht bereits die Adresse $N$4. Das ist die Stelle, an der meine Sparkline gleich erscheinen wird:
Datenbereich festlegen
Mit einem Klick auf „OK“ erhalte ich auch schon das Diagramm für mein erstes Produkt:
Sparklinetools: Das Entwurfs-Menü
Solange ich meine aktive Zelle auf der Sparkline stehen habe, gibt es im Menüband auch ein neues Register „Sparklinetools – Entwurf“, in dem ich verschiedene Einstellungen für meine Sparkline vornehmen kann. Das sehen wir gleich noch näher an.
Zunächst kopieren wir aber die vorhandene Sparkline in die Zellen für die anderen Produkte. Da sich die Sparkline ja innerhalb einer Zelle befindet, kann ich diese Zelle einfach wie gewohnt mit der Maus nach unten ziehen:
Kopieren der vorhandenen Sparkline
Da ich meine Tabelle auch als Tabelle formatiert habe, setze ich nur noch eine Überschrift über die Sparklines, und schon fügen sie sich nahtlos in das ganze Bild ein:
Eine Sparkline für jede Zeile
Finetuning
Alle einzelnen Sparkline-Zellen werden automatisch zu einer Gruppe zusammengefasst. Das merkt man, sobald man den Cursor auf eine der Zellen stellt und sofort auch alle anderen markiert sind, was die Arbeit nochmal vereinfacht.
Im Entwurfs-Menü der Sparklinetools finden sich ein paar Checkboxen, mit deren Hilfe man seine kleinen Diagramme noch anschaulicher gestalten kann. So lassen sich z.B. verschiedene Datenpunkt farblich hervorheben:
Anzeige anpassen
Welche Farbe dabei für die einzelnen Datenpunkte zum Einsatz kommt, kannst du selbst festlegen:
Datenpunktfarbe anpassen
Über die Schaltfläche „Achsen“ kannst du bestimmen, ob eine horizontale Achse angezeigt werden soll und ob z.B. die Minimal- und Höchstwerte für alle Sparklines identisch sein sollen:
Achsen anpassen
Damit werden die einzelnen Diagramme noch besser miteinander vergleichbar und man vermeidet irreführende Skalierungseffekte:
Die angepassten Sparklines
Gruppierung aufheben
Manchmal ist die automatische Gruppierung unerwünscht und man möchte vielleicht für bestimmte (oder alle) Zeilen eine unterschiedliche Darstellung wählen. Kein Problem! Einfach die Zelle mit der Sparkline markieren, die aus der Gruppierung herausgelöst werden soll. Wenn die Gruppierung komplett aufgehoben werden soll, dann markiere einfach alle Sparklines und klicke die Schaltfläche „Gruppierung aufheben“:
Gruppierung aufheben
Und schon lassen sich die Einstellungen für jede Sparkline individuell anpassen, wie man hier sehen kann (auch wenn dieser Wildwuchs zugegebenermaßen nicht zwingend zur Übersichtlichkeit beitragen muss):
Individuelle Sparklines
Wie du siehst, lassen sich mit Hilfe von Sparklines ohne großen Aufwand Trends und Zeitreihen anschaulich präsentieren. Immer dann, wenn es um einen schnellen Überblick geht, die gesamte Funktionalität von „richtigen“ Diagrammen in Excel aber nicht benötigt wird, können Sparklines ihre Stärken ausspielen.
Noch ein wichtiger Hinweis zum Schluß:
Sparklines funktionieren nur in Excel 2010 und neuer und auch nur dann, wenn die Datei im XLSX-Format gespeichert wird. Liegt die Datei im alten XLS-Format vor, sind die Schaltflächen zum Einfügen von Sparklines ausgegraut und stehen damit nicht zur Verfügung.
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.
Hallo Martin,
erstmal – deine Beiträge sind super, ich habe schon mehrere Lösungen für verzwickte Probleme bei dir gefunden.
Zu den Sparklines – ich habe einen Geschäftsbericht (5 Updates im Jahr) mit Kostenpositionen deren Trend ich mit einer Sparkline in jeder Position darstelle.
In einem Markt haben sich nun Volumen reduziert – die Einzelpreise sind aber gleich geblieben. Rechnerisch ergibt sich damit eine Abweichung <0,01 €. Die Sparkline zeigt hier bei 2 (augenscheinlich) identischen Werten einen klaren Abwärtstrend an. Wenn im 3. Monat nun wieder eine minimale Abweichung dazu kommt habe ich einen Zick-zack Verlauf obwohl die Werte augenscheinlich gleich sind.
Das provoziert bei meinen Chefs leider nachfragen und der Verweis auf Nachkommastellen ist m.E. unglücklich.
Fällt dir eine Lösung dazu ein?
Hallo Frank,
auf die Sparklines selbst hat man kaum Einfluss, dort gibt es keine Einstellungen, mit denen so etwas geregelt werden kann. Du kannst nur dafür sorgen, dass die zugrunde liegende Datentabelle dahingehend angeglichen werden, dass solche unerwünschten Effekte nicht auftreten. Beispielsweise durch eine Rundung auf ganze Zahlen. Dazu kannst du ja auch eine Hilfstabelle verwenden und die Originalwerte bleiben unverändert. Und die Sparklines basieren dann auf der Hilfstabelle.
Schöne Grüße,
Martin
Hallo Martin,
ich finde es super, dass Sie versuchen, Excel so zu vermitteln, dass man auf einfachem Wege verständliche Lösungen für seine Herausforderungen finden kann. Schon öfter bin ich auf Ihrer Seite fündig geworden. Mit meiner jetzigen Aufgabe habe ich zwar Lösungsansätze gefunden, aber in der Umsetzung hapert es noch. Ich hoffe, Sie können mir helfen.
Ich möchte Sparklines einsetzen, um den Verlauf von Börsenkursen anzuzeigen. In Spalte A steht das Datum, in Spalte B befindet sich der Börsenwert. Die neuen Werte werden zuoberst eingefügt, damit man immer die aktuellen Werte vor Augen hat (Die Liste umfasst schon Daten mehrerer Jahre.). Die Sparkline soll nur die Daten der letzten 10 Einträge darstellen, also z. B. die Daten B2:B12. Dieser Bereich soll immer gleich bleiben, auch wenn ich eine weitere Zeile einfüge, aus B2 dann also B3 wird. Leider bekomme ich das nicht hin, der Datenbereich verschiebt sich immer von B2:B12 dann zu B3:B13. Dabei spielt es auch keine Rolle, ob ich einen relativen oder absoluten Zellbezug verwende. Was muss ich Excel sagen, damit es mich versteht? Für Ihre Hilfe bin ich sehr dankbar.
Freundliche Grüße
Silke
Hallo Silke,
erst einmal vielen Dank für das schöne Feedback!
Leider fürchte ich, dass es für das beschriebene Problem keine Lösung gibt. Sobald neue Zeilen eingefügt werden, helfen auch absolute Bezüge nicht mehr weiter.
Schöne Grüße,
Martin
Hallo Martn & Silke,
das geht über die Funktion „indirekt“: Wenn die zu zeigenden Werte in Spalte B ab Zeile 1 stehen, dann irgendwo folgende Formeln eintragen:
=INDIREKT(„$B$2“)
=INDIREKT(„$B$3“)
….
=INDIREKT(„$B$12“)
Diese enthalten immer die Werte aus Zelle B2…Zelle B12
Idealerweise nicht neben den Werten in Spalte C, damit man sie nicht auseinanderzieht, wenn ganze Zeilen eingefügt werden.
Diese Formel lässt sich nicht über den Bereich ziehen, dann würde immer dieselbe Zelle herangezogen. Wenn man mehr Formeln braucht, als von Hand sinnvoll eingebbar sind, kann man die Zeile noch über =indirekt(„$B$“&Zeile(Zelle) + Offset) errechnen, wobei „Zelle“ für die Adresse steht, die diese Formel enthält und Offset den Zeilenabstand zwischen der Quell-Zelle und der Formel-Zelle
Der Post ist zwar schon 2 Jahre her, aber vielleicht das Problem noch immer da …
Liebe Grüße,
Hallo Gerhard,
das nenne ich mal eine interessante Einsatzmöglichkeit für INDIREKT. Auf diese Variante wäre ich tatsächlich nie gekommen.
Vielen Dank dafür!
Martin
Interessant wäre doch eine dynamische Sparkline, welche städig neu dazugekomme Wert aus Zeilen oder Spalten mit aufnehmen. Hier findet man überhaupt keine Information. Es wird wohl die Index Funktion dafür benötigt. Nur komme ich damit nicht weiter. Entweder kommen Werte, Bezug, oder sonstige Fehlermeldungen. Wäre Ihnen dankbar hierfür die einzelne nachvollziehbare Schritte aufzeigen könnten. Danke.
Hallo Herr Hoepfner,
grundsätzlich würde man für dynamische Bereiche einen Namen definieren, der auf entsprechenden Formeln basiert (z.B. BEREICH.VERSCHIEBEN) und diesen Namen dann bei der Angabe des Datenbereichs verwenden. Ich habe jedoch festgestellt, dass der Name nach einmaliger Verwendung in den Sparklines wieder automatisch durch die Zellenbezüge ersetzt wird, ist dieses Verfahren leider nicht zielführend (zumindest nicht in meiner M365-Version). Vielleicht greife ich das Thema zu einem späteren Zeitpunkt nochmal mit einem eigenen Beitrag auf, im Moment kann ich Ihnen jedoch keine einfache Lösung bieten.
Schöne Grüße,
Martin
Hallo Martin,
Immer wieder klasse, deine Beiträge zu lesen.
Ich möchte Sparklines für eine Mischungskalkulation verschiedener Materialien benutzen. Also Material A soll idealerweise zu 30% ins Produkt gehen, Material B ideal zu 20% usw. Die tatsächlichen Werte variieren natürlich von Charge zu Charge (Charge 1 A 33% B 19%, Charge 2 A 31% B 21%, usw). Die Chargennummern stehen in A3:A10, die Materialnamen in B1:F1, die Ideal-Anteile in B2:F2 und die IST-Anteile in B3:F10. In B11:F11 soll nun für jedes Material eine Sparkline erstellt werden (funktioniert auch). Aber als Achswert soll hierbei der Idealwert aus Zeile 2 herangezogen werden, um so auf einen Blick Abweichungen vom Idealwert nach oben und unten sehen zu können.
Langer Text, kurze Frage: weißt du wie man den Achswert dynamisch auf eine Zelle beziehen kann?
EDIT: Hab mir grad eine Krücke gebaut. Habe für jedes Material noch Hilfsspalten eingefügt in H3:L10 und berechne hier jeweils IST-Anteil – IDEAL-Anteil. Damit ergeben sich dann Prozentsätze um den Nullpunkt und diese nutze ich als Datenbasis für die Sparkline. Funktioniert aber braucht halt Hilfsspalten. Vielleicht fällt dir oder der Community ja noch eine elegantere Lösung ein.
Liebe Grüße,
Kevin
Hallo Kevin,
vielen Dank für dein Feedback!
Leider habe ich auch keinen Tipp, wie du das Problem ohne Hilfszellen eleganter lösen könntest.
Schöne Grüße,
Martin