Lauftagebuch // 08.02.2008
Dynamische Diagramme bei Excel
Was alles mit der Tabellenkalkulation Excel möglich ist, wissen die meisten nicht. Alleine in der Schule fällt es mir auf, dass viele Probleme mit dem Umgang von Excel haben. Die grundlegenden Möglichkeiten findet man mit etwas Übung heraus. Doch sogar bei der Erstellung von Diagrammen bieten sich einem viele Möglichkeiten.
Das habe ich herausgefunden, als ich eine Excel-Vorlage für Jedermann erstellen wollte. Es geht um die Wettkampf-Statistiken, die ich gerne zusammengefasst und ausgewertet haben möchte. Die Veröffentlichung dieser Vorlage wird bald folgen.
Aber nun zum eigentlichen Thema. Aus einer Tabelle sollen Diagrammeerzeugt wird. Das Problem liegt nun darin, dass man aber nicht weiß, wie viele Zeilen in der Tabelle enthalten sind. Das kann man noch einigermaßen einfach herausfinden. Angenommen in der Spalte D sind unsere Datensätze und in D1 befindet sich die Überschrift.
„=ZÄHLENWENN(D2:D1000;"<>")“
Diese Formel liefert uns nun die Anzahl der nichtleeren Zeilen. Möglich ist ebenso:
„=ZÄHLENWENN(D2:D1000;"<>0")“
wenn es sich um Zahlen handelt, die nicht Null sein sollen.
Nun muss man oben im Menü auf "Einfügen" » "Name" » "Definieren". Dort kann man sozusagen eine Variable erstellen.
„Name in der Arbeitsgruppe: Anzahl
Bezieht sich auf: =Daten!$H$1“
"Daten" muss hierbei der Name der Mappe (unten zu sehen) sein und H1 das Feld, wo die obige Formel eingefügt wurde. Außerdem erstellt man eine weitere Variable:
„Name in der Arbeitsgruppe: Werte
Bezieht sich auf: =BEREICH.VERSCHIEBEN(Daten!$D$2:$D$1000;0;0;Anzahl;1)“
Die Funktion ist gedacht, um einen Bezug zu verschieben (ändert nicht die Inhalte und verschiebt sie auch nicht). Sie kann aber auch dazu genutzt werden, dass der Bezug nur in der Länge verändert wird, so wie bei uns. Daten ist wieder der Name der Mappe, D2 bis D1000 der ursprüngliche Bereich (auf die Dollarzeichen achten) und Anzahl ist die Länge. Die Nullen dazwischen sind die Verschiebung, die von uns nicht gewünscht wird, und die 1 am Ende ist die Breite des Bezuges.
Dadurch erhalten wir einen dynamischen Bezug.
Nun ist es möglich diese Daten in einem Diagrammzu verarbeiten. Auf die normale Erstellung eines Diagramms gehe ich nicht weiter ein. Nachdem wir ein Diagramm erstellt haben - vorerst mit statischem Bezug - gelangen wir mit einem Rechtsklick zur "Datenquelle". Hier können wir nun etwas Anderes als den statischen Bezug festlegen. Bei den Reihen können wir nun folgendes eingeben:
„='Wettkampf-Statistiken.xls'!Werte“
Zuerst wird der Dateiname der Excel-Datei genannt und nach dem Ausrufezeichen folgt unsere Variable, in diesem Fall "Werte". Und schon haben wir einen dynamischen Bezug.
Je nachdem, wie viele Zeilen in der Datentabelle nun gefüllt werden, ändert sich auch der Bezug für das Diagramm. Wichtig ist, dass x-Achse und Werte-Reihe eine gleiche Anzahl von Zeilen besitzen. Daher sollte man gleichermaßen auch für die Beschriftung der x-Achse vorgehen.
Mithilfe dieser Formel war es mir nun möglich eine Tabelle zu erstellen, in der beliebig viele Läufe mit ihren Statistiken eingetragen werden können. Die Diagramme verändern sich nun nach der Anzahl der eingetragenen Läufe. So könnten beispielsweise auch nur die Läufe mit mehr als 1.000 Teilnehmern in einem Diagramm dargestellt werden. Dazu müsste bei der Formel Anzahl das zweite Argument ("<>") durch ">1000" ersetzt werden.
Ich hoffe ich kann damit einigen Leuten helfen, falls sie danach suchen. Mein Problem war nämlich, dass ich mit Google relativ wenig im Internet dazu gefunden habe und es meistens nicht geklappt hat.
Über Rückmeldungen würde ich mich sehr freuen.
16 Kommentare
Lob, Kritik und Anregungen sind immer herzlich willkommen.
Zum Bloggen gehören eure Meinungen ebenso sehr wie die Artikel.
Da muß ich doch wohl ein ganz besonderer Trottel sein; bei mir funktioniert das einfach nicht. Ich erhalte stets und ständig die Fehlermeldung, dass ich einen ungültigen Bezug auf ein externes Datenblatt habe. Langsam verliere ich die Geduld.
das deutet ja darauf hin, dass einer der Bezugsnamen falsch ist. Erfolgt diese Fehlermeldung bei der Angabe der Datenquelle des Diagramms? Ist der Name der Excel-Datei korrekt und wurde der definierte Bezug (in meinem Fall Werte) vorher ohne Probleme erstellt?
vielen Dank erstmal! Ich hab auch lange bei google gesucht und es ist echt schwierig sinnvolles zu finden. Ich habe allerdings noch ein Problem... bei der Datenquelle im Diagramm stellt excel es immer automatisch auf den Bereich zurück, so dass das Diagramm nicht mehr variabel ist. Hast du vielleicht eine Idee, wie man excel dazu bringt, dass es zB. ='Wettkampf-Statistiken.xls'!Werte
dort stehen lässt?
Der Beitrag ist zwar schon ein bissel älter, aber ich würd mich über Hilfe freuen!
Lg
Caro
auf welchen Bereich wird das zurück gestellt? Und wo genau? Wenn man mit Rechtsklick auf die Datenquelle klickt, kann man ja entweder einen Datenbereich oder eine Reihe auswählen. Dort muss man die Reihen einzeln auswählen. Dort bei "Werte" habe ich es noch nie erlebt, dass Excel das ändert. Was gibst du ein, und was lässt Excel dort stehen?
ja, wenn man die Reihen einzeln rein schreibt klappt es =) Danke!
Ich hatte einen Namen "Werte" und nicht alle Reihe einzeln...
Jetzt ist aber noch was komisch... ich hab ein Säulendiagramm erstellt und ändere dann die Anzahl der Zeilen von 3 auf 2. Dann "rücken" die Säulen aber nicht zusammen, sondern es bleibt dort Platz, wo vorher die 3. Reihe war. Kann man verstehen, was ich meine? Und kann man das ändern? Ich hab "leere Zellen nicht anzeigen" schon versucht...
Beispiel: Du hast oben in der Tabelle ein Feld, wo die Anzahl drin steht, welches du als "Anzahl" definierst. Dann definierst du mit Hilfe von Bereich.Verschieben einmal die "Werte" aber auch die "Beschriftung" - und beide gibst du nun als dynamischen Bezug in die Datenquelle ein, das eine mal als Reihe, und die Beschriftung als Rubrikenachse (X).
Dann müsste das gehen!?
Falls du die Anzahl der Reihen auch noch dynamisch gestalten willst, dann weiß ich leider auch nicht weiter ...
Ich würd es am liebsten auch dynamisch hinbekommen, aber keine Ahnung ob das überhaupt geht. Bin ja gerad erst am Anfang Excel kennenzulernen =)
Vielen Dank dir!
Gruß
Allerdings musste das
„=ZÄHLENWENN(D2:D1000;"<>")“ semikolon dadrin ein Komma sein.
Vielen Dank fuer die Anleitung, man weiss echt sehr wenig ueber die eigentlichen Moeglichkeiten von Excel, wenn man sich nicht ausfuehrlich damit beschaeftigt hat.
Aber Hallo erstmal,
Ich hoffe mal trotzdem das die Seite so alt ist, das mir geholfen werden kann / wird.
Geht um folgendes.
Ich habe Spalten / Zeilen welche mit Werten belegt sind. Ich brauche unbedingt hilfe. Mit 2 Werten klappt das alles Super, wenn ich nun allerdings mit einem 3ten wert anfang knallt er mir jedesmal entweder den datenbreich raus. stellt mein Diagramm so um das ich einen Wert verliere oder (so ist mein gefühl) macht einfach nur was er will und lacht mich gefühlt aus ...
Hallo Marco,
leider kann ich deinem Problem nicht ganz folgen. Auf was beziehen sich deine drei Werte? Drei Datenreihen?
ich hatte lange nach einer guten Erklärung für dynamische Grafiken gesucht und bei Dir endlich gefunden. Danke, bei mir hat alles geklappt und verstanden habe ich es auch noch.
LG
Sebastian
ich schreie mich gerade weg, weil ich gerade auf der Arbeit sitze und ein "flexibles Excel-Diagramm" erstellen will und nicht wusste, wie ich den statischen Zellbezug der Datenquelle in einen dynamischen verändern kann...
Schnell Google befragt, und wo lande ich???
Hier bei DIR!!!
Was ein lustiger "Zufall".
Ich probiere das jetzt mal aus, ob das so klappt, es hört sich auf alle Fälle so an, als wäre deine Erklärung die Lösung für mein Problem.
Lieber Gruß,
Anke
ich habe ebenfalls ein Problem bei der Erstellung des Diagramms. Gebe ich bei den Reihen wie oben beschrieben ='Mappe1.xlsx'!Werte ein, bekomme ich die Meldung, dass sich der Bezug auf ein geöffnetes Arbeitsblatt beziehen muss. Und wenn ich es dann mit =Tabelle1!Werte versuche, bekomme ich die Meldung, dass es kein gültiger Bezug ist. Das Definieren der Namen hat vorher problemlos geklappt. Ich verstehe nicht, wo der fehler liegt. Würde mich über Hilfe freuen :)
Grüße,
Sonia