Clever-Excel-Forum

Normale Version: Kompl. Woche in wachsender Datenreihe für Diagramm
Du siehst gerade eine vereinfachte Darstellung unserer Inhalte. Normale Ansicht mit richtiger Formatierung.
Seiten: 1 2
Hallo Zusammen,

ich hoffe Ihr könnt mir helfen. Ich habe folgendes Problem bei einer wachsenden Datengrundlage für ein Diagramm.

           Sp. A     | Sp. B   | Sp. C
Zeile  | Datum   | Stunde | Verbrauch
2       | 01.01.15| 01:00  |  120
3       | 01.01.15| 02:00  |  110
4       | 01.01.15| 03:00  |  122
5       | 01.01.15| 04:00  |  125
                 °
                 °
                 °
        | 23.06.15| 21:00  |  150
        | 23.06.15| 22:00  |  119
        | 23.06.15| 23:00  |  127
        | 24.06.15| 00:00  |  123

Die Daten in den Spalten A,B und C werden je nach Tag der Auswertung im Jahr bzw. des manuellen Einfügens über die Zwischenablage zunehmende Zeilen haben. Die Daten fangen immer am 01.01. an. Können aber unterwöchig aufhören. In den Spalten F,G und H gibt es einen identischen Bereich welcher aber nur die Daten für eine komplette Woche (Montag 01 Uhr bis Folgemontag 00 Uhr) enthält, welche die Grundlage eines Diagrammes sind.

Wie bekomme ich nun folgendes verformelt? In dem Bereich F,G und H sollen automatisch die Daten der letzten komplett!!! vorhandenen Woche gezogen werden.

Danke schon mal!!!!!!
Hi,

ich fürchte, mit Formeln wird dein Vorhaben nicht gelöst werden können. Eventuell mit einem Makro - aber das müssen dir die VBA-Spezialisten sagen.
Hallo

@Günter
Da würde ich noch nicht die Formel-Flinte ins Korn werfen.
Für eine komplette Woche müssen 7x24 Werte vorliegen von denen der erste Montag 01 Uhr und der letzte Montag 00 Uhr ist. Das läßt sich in einer Hilfsspalte abbilden - irgendwie. Die Auswertung kann dann mit einem Pivot-Chart erfolgen.

@grübelgrübel
Ein Beispielmappe wäre hilfreich.
Hallo,

als Idee:



Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEF
1DatumStundeVerbrauch KWSumme
201.01.201501:00120 1477
301.01.201502:00110 20
401.01.201503:00122 30
501.01.201504:00125 40

ZelleFormel
F2=SUMMEWENNS(C:C;A:A;">="&("4.1."&JAHR($A$2))+E2*7-7-REST("2.1."&JAHR(A2);7);A:A;"<="&("4.1."&JAHR($A$2))+E2*7-7-REST("2.1."&JAHR(A2);7)+6)
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

unter der Vorgabe, dass die Woche komplett sein muß (144Werte):


Code:
=WENN(ZÄHLENWENNS(A:A;">="&("4.1."&JAHR($A$2))+E2*7-7-REST("2.1."&JAHR(A2);7);A:A;"<="&("4.1."&JAHR($A$2))+E2*7-7-REST("2.1."&JAHR(A2);7)+6)=144;SUMMEWENNS(C:C;A:A;">="&("4.1."&JAHR($A$2))+E2*7-7-REST("2.1."&JAHR(A2);7);A:A;"<="&("4.1."&JAHR($A$2))+E2*7-7-REST("2.1."&JAHR(A2);7)+6);"#NV")
Hallo Zusammen,

@ BoskoBiati - Nein Summen sollen nicht berechnet werden
@ WillWissen - Am liebsten sind mir halt noch die Formeln
@ shift-del - Momentan suche ich mir halt in den Spalten A bis C die letze komplette Woche von Mo 01 Uhr-Folge Mo 00 Uhr markiere die Daten in den 3 Spalten für eine diese Woche und erstelle daraus ein Liniendiagramm. Ich möchte jetzt die Suche beseitigen und über Hilfspalten in einem festen Bereich die Daten über Formeln automatisch aus den Spalten A - C suchen lassen die dann in einem Diagramm angzeigt (aktualisiert) werden.
Hallo,

dann hättest Du trotzdem die Formel nehmen können und was draus basteln. Ich verstehe immer noch nicht, was Du wirklich willst, nur eine Auflistung der Werte einer kompletten Woche untereinander?
Dafür würde m.E. der Spezialfilter reichen.

Arbeitsblatt mit dem Namen 'Tabelle2'
 ABCDEF
1DatumStundeVerbrauch KW 
201.01.201501:00120 2125
301.01.201502:00110  125
401.01.201503:00122  116
501.01.201504:00125  127
601.01.201505:00127  123
701.01.201506:00122  116
801.01.201507:00118  130

ZelleFormel
F2=WENN(ZÄHLENWENNS(A:A;">="&("4.1."&JAHR($A$2))+$E$2*7-7-REST("2.1."&JAHR(A2);7);A:A;"<"&("4.1."&JAHR($A$2))+$E$2*7-7-REST("2.1."&JAHR(A2);7)+7)=168;WENN(ZEILE(A1)<169;INDEX(C:C;VERGLEICH(("4.1."&JAHR($A$2))+$E$2*7-7-REST("2.1."&JAHR(A2);7);A:A;0)+ZEILE(A1)-1);"");"")
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Hallo Edgar,

es sollen die Daten der letzten vollen Woche aufgelistet werden. Heute z.B. wären das noch die Daten der vorigen Woche - die bereits vorhandenen Daten dieser Woche wäre erst ab kommenden Montag dran.

Vom Ansatz her könnte ich mir vorstellen, dass in einer Hilfsspalte die KW eingetragen wird. Die Formellösung müsste dann die größte KW suchen, die 168x vorhanden ist, und die Daten daraus rüberziehen. Ausnahme wären die beiden Zeitumstellungen, da hier die 168 nicht stimmt. Man müsste also auch noch per Formel die Sommerzeitumstelllung berücksichtigen. Wenn die Auswertung über den Jahreswechsel geht, müsste auch noch das Jahr in die Hilfsspalte und nicht nur die KW.
Eventuell geht das zu vereinfachen, wenn man mit einer entsprechenden Bedingung die KW oder noch einfacher nur ein x nur in der letzten vollen Woche ausgibt und in allen anderen nicht. Als Bedingung bräuchte man nur zählen, ob das aktuelle Datum +1 168x da ist - wie gesagt, unter Beachtung der beiden Ausnahmen. Wenn nicht, kommt das x hin, wenn doch, ist man im falschen Film - nein Datum Wink

Man könnte natürlich auch ein Diagramm mit dem gesamten Bereich füttern und dann die gewünschte Woche filtern ...
Hallo, 

Und was ist mit meinem Muster? 
Hallo,

meine Formel passt schon, nur muß in einer Hilfsspalte die Woche ermittelt werden:

Arbeitsblatt mit dem Namen 'Tabelle2'
 ABCDEF
201.01.201500:00113 4114
301.01.201501:00101  120
401.01.201502:00107  109
501.01.201503:00120  125

ZelleFormel
D2=WENN((REST(A2;7)=2)*(B2=0)*(INDEX(A:A;ZEILE()+167)=A2+6)*(INDEX(B:B;ZEILE()+167)=23/24);KALENDERWOCHE(A2;21);"")
E2=MAX(D:D)
F2=WENN(ZÄHLENWENNS(A:A;">="&("4.1."&JAHR($A$2))+$E$2*7-7-REST("2.1."&JAHR(A2);7);A:A;"<"&("4.1."&JAHR($A$2))+$E$2*7-7-REST("2.1."&JAHR(A2);7)+7)=168;WENN(ZEILE(A1)<169;INDEX(C:C;VERGLEICH(("4.1."&JAHR($A$2))+$E$2*7-7-REST("2.1."&JAHR(A2);7);A:A;0)+ZEILE(A1)-1);"");"")
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Hallo Edgar,

mit dem kurzen Ausschnitt muss ich erst mal passen. Ich hoffe mal, dass bei kompletter Darstellung die 168 Werte vorhanden sind.

Meine Hinweise mit der Sommerzeit kann man übrigens auch ignorieren, die 168 sind bei anderer Betrachtung übrigens auch irrelevant. Man bräuchte ja nur zu schauen, ob beim letzten Eintrag der letzte Tag der Woche und die letzte Uhrzeit des Tages steht. Wenn nicht, muss man den vorletzten Tag nehmen. Ob die Formel dadurch einfacher wird, wer weiß ... Ich hole jetzt erst mal Brötchen, sonst gibt's Ärger Smile)
Seiten: 1 2