Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1424to1428
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Diagramm, Werte abhängig vom Datum

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

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

Betreff
Datum
Anwender
Anzeige
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

Anzeige
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

Anzeige
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

 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
.. , - ...
.

Anzeige
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
 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


Anzeige
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

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige