Diagramm, Werte abhängig vom Datum

Bild

Betrifft: Diagramm, Werte abhängig vom Datum
von: Martin
Geschrieben am: 11.05.2015 10:33:30

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

Bild

Betrifft: dynamisches Diagramm ...
von: der neopa C
Geschrieben am: 11.05.2015 10:44:42
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
.. , - ...


Bild

Betrifft: AW: dynamisches Diagramm ...
von: Martin
Geschrieben am: 11.05.2015 10:59:49
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

Bild

Betrifft: AW: dynamisches Diagramm ...
von: Martin
Geschrieben am: 11.05.2015 11:33:28
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

Bild

Betrifft: nachgefragt ...
von: der neopa C
Geschrieben am: 11.05.2015 11:42:28
Hallo Martin,
... warum lädst Deine Datei (zuvor auf das Wesentlich reduziert) hier hoch und erklärst daran Deine konkrete Zielstellung?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: nachgefragt ...
von: Martin
Geschrieben am: 11.05.2015 12:14:14
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

Bild

Betrifft: dyn. Diagramm mit Hilfe von INDEX() ...
von: der neopa C
Geschrieben am: 11.05.2015 18:17:56
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

 HIJKL
111.05.2015Daten_akt:#WERT!Jahr_akt:2015
2 Daten_VJ:#WERT!Jahr_VJ:2014
3 Daten_VVJ:#WERT!Jahr_VVJ:2013

Formeln der Tabelle
ZelleFormel
H1=HEUTE()
J1=Daten_akt
L1=JAHR_akt
J2=Daten_VJ
L2=Jahr_VJ
J3=Daten_VVJ
L3=Jahr_VVJ
Namen in Formeln
ZelleNameBezieht sich auf
J1Daten_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;)))
L1JAHR_akt=JAHR(Tabelle1!$H$1)
J2Daten_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;))
L2Jahr_VJ=JAHR(Tabelle1!$H$1)-1
J3Daten_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;))
L3Jahr_VVJ=JAHR(Tabelle1!$H$1)-2
Namen verstehen

Gruß Werner
.. , - ...
.

Bild

Betrifft: AW: dyn. Diagramm mit Hilfe von INDEX() ...
von: Martin
Geschrieben am: 14.05.2015 19:17:58
Vielen herzlichen Dank Werner.
Ich komme leider erst Donnerstag dazu zu testen und werde dann berichten.
Gruß,
Martin

Bild

Betrifft: dynamisches Diagramm 3 Jahre
von: Beverly
Geschrieben am: 12.05.2015 08:37:58
Hi Martin,
mit einer Hilfstabelle lässt sich das ganz problemlos lösen:

Tabelle1

 BCDE
6 201620152014
7Jänner534273700
8Februar123289800
9März123294750
10April234879405
11Mai345287650
12Juni123389450
13Juli345239230
14August456564460
15September345843230
16Oktober0734134
17November0644563
18Dezember0544297

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



Bild

Betrifft: AW: dynamisches Diagramm 3 Jahre
von: Martin
Geschrieben am: 15.05.2015 08:54:26
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

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Diagramm, Werte abhängig vom Datum"