Diagramm, Werte abhängig vom Datum

Forumthread: Diagramm, Werte abhängig vom Datum

11.05.2015 10:33:30

Martin

Hallo,
ich suche (wieder) eine Lösung für ein aktuelles Problem einer Diagrammdarstellung.
Das File sollte zum Schluss so ausschauen wie das File im Anhang.
Userbild

Wenn jetzt zum Beispiel die ersten Daten von 2016 eingegeben werden dann sollen automatisch die Daten in der Vergangenheit einen 12-Monats-Sprung machen...
Die Daten werden in einem separaten Arbeitsblatt laufend ergänzt.
Es sollte vermieden werden, dass jedes Jahr die Bezugsdaten händisch angepasst werden.
Habt ihr da eine Idee für mich?
Danke,
Martin

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

dynamisches Diagramm ...

11.05.2015 10:44:42

der

Hallo Martin,
.. dazu sieh mal hier: http://www.excelformeln.de/tips.html?welcher=9.
Es geht zwar auch anders, aber so ist es für mich momentan am schnellsten erklärt.
Gruß Werner
.. , - ...

AW: dynamisches Diagramm ...

11.05.2015 10:59:49

Martin

Hallo Werner,
danke dir - ich arbeite in diesem größeren File auch schon mit unzähligen Namen.
Das kann ich gerne jetzt auch wieder so machen.
Nur stellt sich mir die Frage:
Ich benötige:
Name1 (Daten vor 3 Jahren Jän-Dez)
Name2 (Daten vor 2 Jahren Jän-Dez)
Name3 (Daten vor 1 Jahren Jän-Dez)
Name4 (Daten aktuelles Jahr fortlaufend)
Beim Jahressprung muss dann der Bezug "ein Jahr" nach unten rutschen.
Welche Formel definiere ich also bei Name1, Name2, etc.?!
Ich bin da ziemlich ahnungslos... sorry - danke!
Daten von Bezugsquelle wieder im Anhang.
Userbild

AW: dynamisches Diagramm ...

11.05.2015 11:33:28

Martin

Hallo nochmal,
ich habe ja ein aktuelles Monat/Jahr Feld:
Zb. März 2015
Die Formel müsste irgendwie heißen:
Wenn März 2015 ausgewählt nimm bei Name1 gesamte Werte von Jänner bis Dezember 2012 (12 Zellen)
Wenn März 2015 ausgewählt nimm bei Name2 gesamte Werte von Jänner bis Dezember 2013 (12 Zellen)
etc.
Wisst ihr was ich meine?
Danke - ciao,
Martin

nachgefragt ...

11.05.2015 11:42:28

der

Hallo Martin,
... warum lädst Deine Datei (zuvor auf das Wesentlich reduziert) hier hoch und erklärst daran Deine konkrete Zielstellung?
Gruß Werner
.. , - ...

AW: nachgefragt ...

11.05.2015 12:14:14

Martin

Hallo nochmal, danke Werner.
Ich weiß, ich hab's mal schnell nachgebaut... ;)
https://www.herber.de/bbs/user/97575.xlsx
Hier sollte nun das ab Eingabe von 2017er-Werten das 2014er Jahr rausfallen und nur mehr 2015 und 2016 als Historie und 2017 (wachsend) angezeigt werden.
Ich hoffe ich konnte mich verständlich ausdrücken.
Im Original-File habe ich dann ein "Aktuelles Monat" Feld - die Formel im Name1 sollte dann eventuell irgendwie so aussehen:
=INDEX(MasterDaten!$E$2:$E$731;VERGLEICH(Monatsdefinition!$D$4;MasterDaten!$A:$A);1) :INDEX(MasterDaten!$E$2:$E$858;VERGLEICH(Monatsdefinition!$D$4;MasterDaten!$A:$A) -Monatsdefinition!$D$13+1;1)
Die Formel kommte jetzt von ganz was anderem - doch ich denke es sollte in die Richtung gehen.
Nochmal in Prosa:
Wenn Aktuelles Monat ist 2015 dann nimm die Werte von 2013 (12 Monate), 2014 (12 Monate) und 2015 (soviel als da ist)
Wenn Aktuelles Monat ist 2016 dann nimm die Werte von 2014 (12 Monate) etc.
Ich muss hier etwa 20 Werte so in Diagrammen darstellen und möchte die Arbeit vom Zellbezug nachjustieren eben vermeiden.
Danke! ciao
Martin

dyn. Diagramm mit Hilfe von INDEX() ...

11.05.2015 18:17:56

INDEX()

Hallo Martin,
... nachfolgend zeige ich Dir für Dein Datenbeispiel auf, wie Du die jeweiligen Datenwerte des aktuellen Jahres sowie der vorangegangenen zwei Jahre dynamisch mit Hilfe von INDEX() angezeigt bekommst. Dazu definiere die 6 nachfolgenden Bereichsnamen wie von mir in J1:J3 sowie L1:L3 aufgezeigt (die Benennung ist natürlich Dir überlassen) und weise die Bereichsnamen den Digrammdatenreihen zu.
Ich habe =HEUTE() in H1 eingeschrieben, auf das die Formeln zugreifen. Du kannst HEUTE() natürlich auch in die Formeln einarbeiten, es ist allerdings mit der Hilfszelle zweckmäßiger.
Natürlich werden in Deiner Datei die Ausgabe der hier dargestellten Zellen I:L3 nicht notwendig.
Tabelle1

	H	I	J	K	L
1	11.05.2015	Daten_akt:	#WERT!	Jahr_akt:	2015
2		Daten_VJ:	#WERT!	Jahr_VJ:	2014
3		Daten_VVJ:	#WERT!	Jahr_VVJ:	2013

Formeln der Tabelle

Zelle	Formel
H1	=HEUTE()
J1	=Daten_akt
L1	=JAHR_akt
J2	=Daten_VJ
L2	=Jahr_VJ
J3	=Daten_VVJ
L3	=Jahr_VVJ

Namen in Formeln

Zelle	Name	Bezieht sich auf
J1	Daten_akt	=INDEX(Tabelle1!$H:$H;VERGLEICH(DATUM(JAHR(Tabelle1!$H$1);1;1);Tabelle1!$G:$G;)):INDEX(Tabelle1!$H:$H;MIN(VERWEIS(9^9;Tabelle1!$H:$H;ZEILE(Tabelle1!$H:$H));VERGLEICH(DATUM(JAHR(Tabelle1!$H$1);12;1);Tabelle1!$G:$G;)))
L1	JAHR_akt	=JAHR(Tabelle1!$H$1)
J2	Daten_VJ	=INDEX(Tabelle1!$H:$H;VERGLEICH(DATUM(JAHR(Tabelle1!$H$1)-1;1;1);Tabelle1!$G:$G;)):INDEX(Tabelle1!$H:$H;VERGLEICH(DATUM(JAHR(Tabelle1!$H$1)-1;12;1);Tabelle1!$G:$G;))
L2	Jahr_VJ	=JAHR(Tabelle1!$H$1)-1
J3	Daten_VVJ	=INDEX(Tabelle1!$H:$H;VERGLEICH(DATUM(JAHR(Tabelle1!$H$1)-2;1;1);Tabelle1!$G:$G;)):INDEX(Tabelle1!$H:$H;VERGLEICH(DATUM(JAHR(Tabelle1!$H$1)-2;12;1);Tabelle1!$G:$G;))
L3	Jahr_VVJ	=JAHR(Tabelle1!$H$1)-2

Namen verstehen

Gruß Werner
.. , - ...
.

AW: dyn. Diagramm mit Hilfe von INDEX() ...

14.05.2015 19:17:58

INDEX()

Vielen herzlichen Dank Werner.
Ich komme leider erst Donnerstag dazu zu testen und werde dann berichten.
Gruß,
Martin

dynamisches Diagramm 3 Jahre

12.05.2015 08:37:58

Beverly

Hi Martin,
mit einer Hilfstabelle lässt sich das ganz problemlos lösen:

Tabelle1

	B	C	D	E
6		2016	2015	2014
7	Jänner	534	273	700
8	Februar	123	289	800
9	März	123	294	750
10	April	234	879	405
11	Mai	345	287	650
12	Juni	123	389	450
13	Juli	345	239	230
14	August	456	564	460
15	September	345	843	230
16	Oktober	0	734	134
17	November	0	644	563
18	Dezember	0	544	297

verwendete Formeln
Zelle	Formel	Bereich	N/A
C6	{=JAHR(INDEX($G:$G;MAX(ISTZAHL(H$1:H$999)*ZEILE($1:$999))))}	$C$6
D6	{=JAHR(INDEX($G:$G;MAX(ISTZAHL(H$1:H$999)*ZEILE($1:$999))))-1}	$D$6
E6	{=JAHR(INDEX($G:$G;MAX(ISTZAHL(H$1:H$999)*ZEILE($1:$999))))-2}	$E$6
C7:E18	=INDEX($H:$H;VERGLEICH(DATUM(C$6;"01";"01");$G:$G;0)+ZEILE(A1)-1)

{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.02 einschl. 64 Bit

Außerdem definierst du einen Namen für das aktuelle Jahr und verwendest dazu diese Formel:


=Tabelle1!$C$7:INDEX(Tabelle1!$C$7:$C$18;SUMMENPRODUKT((Tabelle1!$C$7:$C$18>0)*1))

Dein Diagramm basiert dann für das aktuelle Jahr auf diesem Namen und für die Vorjahre entsprechend jeweils fix auf D7:D18 bzw. E7:E18.
Die Formel in C6:E6 ist diese: http://www.excelformeln.de/formeln.html?welcher=50
https://www.herber.de/bbs/user/97589.xlsx

AW: dynamisches Diagramm 3 Jahre

15.05.2015 08:54:26

Martin

Vielen herzlichen Dank an euch beide, ich bin bei Werner's Lösung für die Daten_akt immer auf einen Fehler gelaufen, bei welchem mir Excel bei der Berechnung (9^9?) abschmierte... Karin, deine Lösung wird aktuell implementiert und verwendet!
Nochmal vielen vielen Dank an euch, ihr seid eine sehr große Hilfe!
Ciao & Gruß aus Österreich,
Martin

Beliebteste Forumthreads (12 Monate)

Diagramm, Werte abhängig vom Datum

Forumthread: Diagramm, Werte abhängig vom Datum

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Diagramm, Werte abhängig vom Datum

Forumthread: Diagramm, Werte abhängig vom Datum

9 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread