VBA dynamischer Datenbereich Diagramm

Bild

Betrifft: VBA dynamischer Datenbereich Diagramm
von: Robert
Geschrieben am: 02.11.2015 13:29:12

Hallo Community,
ich möchte für eine Tabelle mit den Dateninhalten von A2 bis E"dynamisch" ein Linien Diagramm erstellen.
In Spalte A Steht jeweils ab A2 das Datum. Von B1:E1 die Bezeichnung für die Datenreihe. Nachfolgend dann ab der 2. Zeile die Werte für das Diagramm. Auf der x-achse soll das Datum eingetragen werden. und auf der Y Achse die Werte für die 4 Datenreihen/Linien.
Wie kann man das per VBA lösen, dass die Daten dynamisch abgegriffen werden? Muss das Diagramm bereits erstellt werden um dann nachfolgend nur noch auf das Diagramm zuzugreifen und den Datenbereich zu erweitern, oder kann man das Diagramm erstmalig erstellen und dann tag für tag den datenbereich erweitern? Kann man das auch noch so erweitern, dass man vom letzten Eintrag in der Tabelle an,nur die letzten 90 Tage in das Diagramm einträgt, damit das Diagramm in der x-Achse nicht zu breit wird?
Danke schon mal für eure Hilfe.

Bild

Betrifft: AW: VBA dynamischer Datenbereich Diagramm
von: Rudi Maintaire
Geschrieben am: 02.11.2015 13:44:45
Hallo,
das würde ich ohne VBA machen.
Statt dessen mit dynamischen Namensdefinitionen.
Gruß
Rudi

Bild

Betrifft: AW: VBA dynamischer Datenbereich Diagramm
von: Robert
Geschrieben am: 02.11.2015 14:12:11
Hallo Rudi,
Wie würde man das denn dann machen? Muss man das Diagramm schon erstellt haben und dann für den flexiblen Bereich deine Idee umsetzen? Die Tabelle erhält nämlich täglich Daten aus einer anderen Datei und das Diagramm soll dann die täglichen Daten miteinbeziehen.

Bild

Betrifft: AW: VBA dynamischer Datenbereich Diagramm
von: Robert
Geschrieben am: 02.11.2015 16:46:59
Hallo Rudi,
Wie würde man das denn dann machen? Muss man das Diagramm schon erstellt haben und dann für den flexiblen Bereich deine Idee umsetzen? Die Tabelle erhält nämlich täglich Daten aus einer anderen Datei und das Diagramm soll dann die täglichen Daten miteinbeziehen.

Bild

Betrifft: AW: VBA dynamischer Datenbereich Diagramm
von: Rudi Maintaire
Geschrieben am: 02.11.2015 16:55:38
Hallo,
wühl dich mal bei Beverly durch
http://excel-inn.de/diagramm.htm
Gruß
Rudi

Bild

Betrifft: AW: dynamischer Diagramm mit INDEX() ...
von: ... neopa C
Geschrieben am: 02.11.2015 17:00:39
Hallo Robert,
... angenommen Deine Datei hast Du DIGR.xlsx benannt und darin die Tabelle: Daten mit den Datenwerten in Spalte A:B davon die Datumswerte beginnend in A3 sowie die dazugehörigen Werte in B3.
Definiere zwei Bereichsnamen. Nennen wir sie Daten und Werte.
Daten:

=INDEX($A:$A;MAX(3;VERWEIS(9^9;$A$1:$A$999;ZEILE($A$1:$A$999))-9)):INDEX($A:$A;MAX(4;VERWEIS(9^9; $B$1:$B$999;ZEILE($A$1:$A$999))))

Werte:

=INDEX($B:$B;MAX(3;VERWEIS(9^9;$A$1:$A$999;ZEILE($A$1:$A$999))-9)):INDEX($B:$B;MAX(4;VERWEIS(9^9; $B$1:$B$999;ZEILE($A$1:$A$999))))

Erstelle Dein Liniendiagramm aus den ersten paar Datenwerten. Anschließend ändere die dortigen Bereichsdefinitionen wie folgt ab:

Reihenwerte für Werte
zu: =DIAGR.xlsx!Werte und
Achsbeschriftung zu =DIAGR.xlsx!Daten
und ferdsch ...
Gruß Werner
.. , - ...

Bild

Betrifft: warum erzählst du mir das?
von: Rudi Maintaire
Geschrieben am: 03.11.2015 09:05:30
Hallo Werner,
das weiß ich doch alles. ;-)
Aber wenn wir schon mal dabei sind:
Da ich faul bin, nenne ich das Blatt mit den Diagrammdaten erst mal immer x.
Dann muss ich die Diagrammreihen nur nach dem Schema
=datenreihe(x!$A$1;x!Daten;x!Werte;1)
ändern.
Excel macht dann selbst aus x! den Dateinamen. (z.b. DieDateimitLangemNamen.xlsx!Werte)
Gruß
Rudi

Bild

Betrifft: AW: warum erzählst du mir das?
von: Daniel
Geschrieben am: 03.11.2015 11:11:12
weil du Robert heißt, Rudi ;-)
Gruß Daniel

Bild

Betrifft: AW: warum erzählst du mir das?
von: Rudi Maintaire
Geschrieben am: 03.11.2015 13:16:14
ja, ich habe viele Namen.
Als gespaltene Persönlichkeit brauche ich die, um meine Ichs auseinander halten und mich mit ihnen unterhalten zu können.
Gruß
Rudi

Bild

Betrifft: AW: das war für Robert gedacht aber ...
von: ... neopa C
Geschrieben am: 03.11.2015 14:04:20
Hallo Rudi,
... ich hab meinen Beitrag vielleicht in weiser Voraussicht ;-) falsch im thread zugeordnet, damit Du meinen schwerwiegenden Schreibfehler in "...angenommen Deine Datei hast Du DIGR.xlsx benannt..." bemerkst. Sollte natürlich dort wirklich DIAGR.xlsx lauten.
Danke für den Hinweis, vielleicht sollte ich ich mich doch künftig nur auf x beschränken.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: dynamischer Diagramm mit INDEX() ...
von: Robert
Geschrieben am: 04.11.2015 14:07:51
Hallo Werner,
ich versuche gerade dein Beispiel umzusetzen. Hierfür habe ich allerdings noch ein paar Verständnisfragen.
Also in meiner Datei.xlsx habe ich in Spalte A ab A2 das Datum stehen. Um die Bereichsnamen zu definieren, muss ich dann in A2 reinklicken, auf Formeln gehen und dann Namen definieren!?
Dort gebe ich dann den Namen ein und dann unter: Bezeiht sich auf: gebe ich das mit dem Index ein.
Für mich ist nicht ganz klar welcher Teil was macht, wie müsste ich das ändern wenn die Daten ab A2 beginnen? Muss ich aus der 3 eine 2 machen und aus der 4 eine 3? und dies dann eben für die werte anpassen?
und dann eben noch die Frage: welcher Part was macht? nimmt der da nur die letzten 90 Tage?
Tut mir leid für die vielen Fragen, ich bin wohl nah am DAU ;)

Bild

Betrifft: AW: VBA dynamischer Datenbereich Diagramm
von: matthias
Geschrieben am: 02.11.2015 16:59:07
Hallo Robert,
dieses Thema ist so alt wie Excel selber. Schau deshalb bitte hier, schöner können wir das auch nicht beschreiben:
http://www.it-service-ruhr.de/tipp/excel-dynamische-diagramme
lg Matthias

Bild

Betrifft: AW: schöner nicht aber effektiver mit INDEX() owT
von: ... neopa C
Geschrieben am: 02.11.2015 17:04:33
Gruß Werner
.. , - ...

Bild

Betrifft: dynamisches Diagramm fixer Anzeigebereich
von: Beverly
Geschrieben am: 02.11.2015 21:01:47
Hi Robert,
wenn der Anzeigebereich für das Diagramm ein fixer Wert ist (90 Tage), dann ist der einfachste Weg, eine Hilfstabelle zu verwenden, in der du alle relevanten Werte per Formel übernimmst. Im Anhang ein Beispiel für 1 Datenreihe - musst du einfach nur Spalten mit den Formeln für deine anderen Datenreihen hinzufügen.
https://www.herber.de/bbs/user/101194.xlsx




Bild

Betrifft: AW: so ist es aber nur teilweise dynamisch ...
von: ... neopa C
Geschrieben am: 03.11.2015 14:08:59
Hallo Karin,
... und zwar nur bzgl. des Datenende.
Mein Vorschlag berücksichtigt auch ein variablen Datenanfang. Es werden da immer nur die letzten 90 (oder anfangs weniger, wenn noch nicht soviel Daten erfasst sind) Daten ausgewertet.
Gruß Werner
.. , - ...

Bild

Betrifft: ich habe auch nichts Gegenteiliges...
von: Beverly
Geschrieben am: 03.11.2015 15:00:52
Hi Werner,
...behauptet - mir ging es vor allem darum zu zeigen, dass man auch ohne den Aufwand auskommt, der mit den Namensdefinitionen verbunden ist, wenn eine definierte Anzahl an (letzten) Datenpunkten angezeigt werden soll. In meinem Beispiel ist es im Falle von weniger vorhanden Datenpunkten eben so, dass im Diagramm aufgezeigt wird, dass da mal mehr dazukommen, bis eben die gewünschte Spanne erreicht ist. Es sollte letztendlich auch dem Fragesteller überlassen bleiben, welche Variante für ihn die günstigere ist.
Und dass ich mich mit dynamischen Diagrammen ein klein wenig auskenne, solltest du eigentlich wissen ;-). Auf meiner HP in der Beispielmappe Dynamisches Diagramm1 - auf die Rudi bereits verwiesen hat - gibt es übrigens auch ein Beispiel nach einem ähnlichen Prinzip wie deins (ganz konkret im Tabellenblatt "letzte_10").




Bild

Betrifft: AW: ist schon klar, doch ...
von: ... neopa C
Geschrieben am: 03.11.2015 17:09:02
Hallo Karin,
... ich hatte gestern in etwa 10 -15 Minuten eine eigene Lösung erarbeitet. Als ich die einstellen wollen, sah ich, dass Rudi (nach längerer Zeit) doch noch einen Beitrag eingestellt hat. Nun wollte ich mein "selbst gestricktes" trotzdem auch noch einstellen. Dabei hatte ich vergessen, dass ich mittlerweile in Rudis "Ast" hing und den somit falsch zugeordnet.
Mein Lösungsvorschlag ist direkt auf die Fragestellung von Robert zugeschnitten, d.h. der variable Start ist direkt abhängig vom variablen Ende und ist immer max 90 Datensätze breit.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: ist schon klar, doch ...
von: Beverly
Geschrieben am: 03.11.2015 21:59:10
Hi Werner,
Mein Lösungsvorschlag ist direkt auf die Fragestellung von Robert zugeschnitten, d.h. der variable Start ist direkt abhängig vom variablen Ende und ist immer max 90 Datensätze breit.
... das ist mein Vorschlag ohne Namensdefinition auch.




Bild

Betrifft: AW: dem ist nur teilweise so ...
von: ... neopa C
Geschrieben am: 04.11.2015 09:26:27
Hallo Karin,
... in Deiner hier im thread hoch geladen Datei, hast Du kein völlig dynamisches Diagramm realisiert. Du ermöglichst eine Darstellung des Diagramms dynamisch bzgl. Dateneingaben, aber eben nur starr bis max 90 Datensätze.
Meine Lösung ermöglicht es mehr als 90 Datensätze zu erfassen und trotzdem nur 90 dynamisch auszuwerten. Es werden hier immer nur die letzten 90 Datensätze im Diagramm abgebildet, wie es Robert in seiner Aufgabenstellung geschrieben hatte.
Gruß Werner
.. , - ...

Bild

Betrifft: du hast...
von: Beverly
Geschrieben am: 04.11.2015 10:03:15
Hi Werner,
...Recht, aber nur insofern, dass meine Formel in E1 nicht korrekt ist. Mit der folgenden jedoch wird die Horizontalachse immer genau auf die letzten 90 Tage "skaliert":

=WENN(ANZAHL(A:A)>=E2;INDEX(A:A;ANZAHL(A:A)-E2+2);MIN(A:A))



Bild

Betrifft: AW: dem ist nur teilweise so ...
von: Robert
Geschrieben am: 04.11.2015 10:03:26
Hallo Karin und Werner,
hier ist ja eine richtige Diskussion entfacht. Ich bedanke mich für eure Hilfe, muss das ganze allerdings erstmal noch für mein Dokument umsetzen.
Ich muss Werner Recht geben. Meine Tabelle kann mit der Zeit Daten für über 150 Tage zB sammeln, soll dann allerdings das Diagramm nur für die letzten 90 Tage anzeigen, um das Diagramm nicht zu sehr zu füllen.
Eine Frage hätte ich vielleicht noch: In Spalte A habe ich das Datum stehen und in Spalte B der dazugehörige Wochentag. Kann ich im Diagramm auf der X-Achse zeitgleich das Datum und den zugehörigen Wochentag darstellen lassen, mit der Tatsache, dass Datum und Wochentag in 2 verschiedenne Spalten stehen?

Bild

Betrifft: wenn du...
von: Beverly
Geschrieben am: 04.11.2015 10:11:30
Hi Robert,
...meinen letzten Betrag gelesen hättest, würdest du sehen, dass auch meine Variante korrekt funktioniert.
Um beide Daten anzuzeigen musst du für die Achsenbeschriftung beide Spalten auswählen.




Bild

Betrifft: AW: wenn du...
von: Robert
Geschrieben am: 04.11.2015 10:46:42
die 11 sekunden, die du vor mir deinen Beitrag abgesendet hattest, war ich mit tippen beschäftigt für meinen Beitrag.
Ich hoffe du nimmst mir das nicht übel. :)

Bild

Betrifft: weshalb sollte...
von: Beverly
Geschrieben am: 04.11.2015 11:55:15
...ich das übel nehmen? Ich schaue doch auch nicht auf die Uhrzeit, wann welcher Beitrag abgeschickt wurde. Ich wollte nur einfach darauf hinweisen, falls der Beitrag "untergegangen" wäre - das kann schon mal passieren, wenn mehrere Teil-Threads nebeneinander herlaufen.




Bild

Betrifft: AW: nun doch günstiger mit Datei ...
von: ... neopa C
Geschrieben am: 04.11.2015 15:21:23
Hallo Robert,
... Deine Fragen hier: https://www.herber.de/forum/messages/1456302.html sind eindeutiger zu beantworten, wenn ich Dir hier die Datei https://www.herber.de/bbs/user/101248.xlsx beifüge.
Beachte dass Du für Dein Beispiel in den Formeln nur die 12 durch die 90 ersetzen musst.
Die Dynamik erkennst Du nun einfach, wenn Du in Zeile 20 neue Werte erfasst oder Du die Daten in A17:A20 löschst.
Die Auswertung ist auch so flexibel, dass Du Zeilen vor A3:B3 einfügen kannst oder Spalten vor Spalte A.
Zu der Definition der Bereichsnamen siehe: http://www.online-excel.de/excel/singsel.php?f=60.
Bei weiteren evtl. Fragen muss ich Dich etwas vertrösten, da ich erst einmal offline gehe.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: nun doch günstiger mit Datei ...
von: Robert
Geschrieben am: 05.11.2015 09:21:16
Hallo Werner,
vielen Dank für deine Hilfe, du hast mir sehr geholfen. Eine letzte Frage hätte ich dann noch:
Für die x-Achse würde ich gerne 2 Spalten als Beschriftung nehmen. Spalte A mit dem Datum und Spalte B mit dazugehörigem Wochentag.
Beide Bereiche habe ich dynamisch nach deinem Prinzip erstellt.
Stellt sich für mich nur noch die Frage wie der Achsenbeschriftungsbereich benannt werden muss, wenn der Bereichsname Datum udn Tag ist?
=Tabellenblattname!Datum:Tag
oder ähnliches hat leider nicht geklappt

Bild

Betrifft: AW: warum nicht mit nur einer Achsbeschriftung?...
von: ... neopa C
Geschrieben am: 05.11.2015 10:07:11
Hallo Robert,
... Du kannst das Zahlenformat der x-Achsbeschriftung benutzerdefiniert formatieren.
Zu z.B.: TTT, TT.MM.JJ
Aus meiner Sicht ist das etwas einfacher und schneller zu bewerkstelligen, als eine zweite Achsbeschriftungszeile einzurichten.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: nun doch günstiger mit Datei ...
von: Beverly
Geschrieben am: 05.11.2015 15:09:52
Hi Robert,
sicher geht das zu machen - siehe angehängtes Beispiel, aber du musst das Diagramm sehr breit ziehen, damit die Achsenbeschriftung auch lesbar ist.
https://www.herber.de/bbs/user/101272.xlsx




 Bild

Beiträge aus den Excel-Beispielen zum Thema "VBA dynamischer Datenbereich Diagramm"