Excel 2016: Management-Reports

Sparklines mit benannter Formel erstellen

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Eine platzsparende Sparkline bildet den Produktverkauf dynamisch ab. Eine benannte Formel mit relativem Zellbezug bildet die Grundlage, um die produktzugehörige Datenreihe ziehen zu können.

Transkript

In diesem Video zeige ich Ihnen, wie Sie den Platzverlauf eines jeden Produktes anhand einer Sparkline anzeigen. Die Sparkline ist im Grunde ein Minidiagramm, das in einer Zelle, z.B. in der Zelle "E6" sich befindet. Damit ich nicht 18 verschiedene Sparklines erstellen muss, benutze ich einen Trick. Ich benutze eine benannte Formel. Diese habe ich bereits erstellt und zeige Ihnen, wie sie arbeitet. Über die Registerkarte "Formeln", "Namensmanager", "Daten", wird mir diese Formel angezeigt. Diese Formel benutzt die Funktion "BEREICH.VERSCHIEBEN". Aus Sicht der aktiven Zelle wird diese Formel mir angezeigt. Entscheidend ist, dass die Adresse "D6" relativ ist. Wenn ich jetzt in diese Formel hineinklicke, wird mir genau der Bereich gezeigt, der in dem Bereich zum "Produkt 08" gehört. Wie ist diese Formel zu lesen? Die Funktion "BEREICH.VERSCHIEBEN" erwartet zunächst einen Startpunkt. Das ist diese Zelle, die hat den Namen "Startpunkt". Als nächstes wird in der Funktion "VERGLEICH" ermittelt, wo denn der Name "Produkt08" steht. Das wäre diese Zelle. Das wird in diesem Teil gemacht. Dann bin ich an dieser Stelle. Die Funktion "VERGLEICH" ermittelt mir also den Zeilenversatz vom "Startpunkt". Von dort aus muss ich um eine Spalte weiter nach rechts gehen. Das wäre die erste "1". Ab hier beschreibe ich mit den nächsten beiden Argumenten in der Funktion "BEREICH.VERSCHIEBEN", wie der neue Bereich aufgebaut sein soll. Und zwar in seiner Höhe und in seiner Breite. Na ja, die Höhe ist wiederum "1", eine einzige Zeile. Das ist die zweite "1". Und wie breit ist der Bereich? 34 Wochen. Insgesamt wird mir dann zum "Produkt 08" dieser Datenbereich zurückgegeben. Ich schließe mal den Namensmanager und gehe jetzt mal in die Zelle "E14". Wenn ich jetzt über den "Namensmanager" "Formeln" "Namensmanager" diese benannte Formel auswähle, steht dort jetzt "D14", und klicke hinein, dann wird mir ein anderer Bereich eben zu dem gerade ausgewählten Produkt, "Produkt 13" der Bereich ermittelt. Diese Formel hat den Namen "Daten". Und über den Namen "Daten" kann die Sparkline erstellen. Und zwar wie folgt. Ich markiere zunächst die Zelle "E6", klicke auf die Registerkarte "Einfügen", "Linie" in der Gruppe "Sparklines". Es wird mir zwar hier der Datenbereich "6" angezeigt, aber dort gebe ich an: "Daten". Positionsbereich der Sparkline, ja das ist genau die Zelle "E6" und klicke auf "OK". Da ist meine Sparkline. Das kann ich jetzt nach unten kopieren. Für jedes Produkt wird die entsprechende Sparkline aus dem Datenbereich gezogen. Für die Zelle "J6" mache ich es entsprechend. Registerkarte "Einfügen" "Sparklinegruppe" "Linie", wiederum "Datenbereich" ist "Daten", "Positionsbereich", da klicke ich einfach auf die Zelle "J6" und auf die Schaltfläche "OK". Doppelklick auf den Anfasser und es wird nach untenhin ausgefüllt. Jetzt habe ich alle Sparklines. Die müssen jetzt noch formatiert werden. Damit das für alle Sparklines gleich gilt, markiere ich mal die erste Gruppe, drücke die Steuertaste und markiere die zweite Gruppe. Und dieses Sparklines kopiere ich über die Registerkarte "Sparklinetools" "Entwurf", "Gruppieren". Ich klicke mal nur eine Zelle an, dann ist die durch die blauen Rahmen zu sehen. Beide Gruppen sind jetzt kopiert und werden in gleicher Weise formatiert. Registerkarte "Sparklinetools" "Entwurf", "Sparklinefarbe", "schwarz". "Datenpunktfarbe" für den Höchstpunkt: "grün". "Datenpunktfarbe" für den Tiefpunkt: "rot". Alle Sparklines bekommen jetzt noch die gleiche Achse. Und zwar Optionen für den Minimalwert der vertikalen Achse, "Identisch für alle Sparklines". Und noch einmal "Achse", "Optionen für den Höchstwert der vertikalen Achse", "Identisch für alle Sparklines". Jetzt ist deutlicher zu sehen, wo die Sparkline entlangführt. Sie ist also mehr im oberen Bereich und hier geht die Sparkline mehr im unteren Bereich. Wähle ich eine andere Woche aus, acht, kommen neue Datenpunkte hinzu und die Sparkline wird Stück für Stück länger, aber auch, und das muss beachtet werden, immer genau zu dem jeweiligen Produkt angezeigt. Ich nehme nochmal weitere Veränderungen vor und so ist zu sehen, wie sich der Platzverlauf zu jedem Produkt ergibt. In diesem Video habe ich Ihnen gezeigt, wie Sie Sparklines vom Typ "Linie" erstellen. Damit ich nicht 18 verschiedene Sparklines erstellen musste, habe ich einen Trick über eine benannte Formel benutzt. Diese benannte Formel benutzt eine relative Adresse und hat den Namen "Daten". Aufgrund der relativen Adressierung, lässt sich eben dieser Name für die unterschiedlichen Sparklines benutzen. Die Sparklines wurden anschließend gruppiert und formatiert. Der Höchstpunkt - "grün", Tiefstpunkt - "rot". Sparklinelinie - "schwarz". Damit alle Sparklines die selbe vertikale Achse, die nicht zu sehen ist, benutzen, wurde über die Registerkarte "Sparklinetools" "Entwurf" die Achse für alle Sparklines identisch eingerichtet.

Excel 2016: Management-Reports

Präsentieren Sie Ihre Zahlen verständlich und überzeugend mit den Werkzeugen und Arbeitstechniken aus diesem Video-Training.

4 Std. 7 min (38 Videos)
Derzeit sind keine Feedbacks vorhanden...

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!