Kompl. Woche in wachsender Datenreihe für Diagramm

[grübelgrübel]

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

[WillWissen]

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.

[shift-del]

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.

[BoskoBiati]

Hallo,

als Idee:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F
1	Datum	Stunde	Verbrauch		KW	Summe
2	01.01.2015	01:00	120		1	477
3	01.01.2015	02:00	110		2	0
4	01.01.2015	03:00	122		3	0
5	01.01.2015	04:00	125		4	0

Zelle	Formel
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")

[grübelgrübel]

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.

[BoskoBiati]

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'
	A	B	C	D	E	F
1	Datum	Stunde	Verbrauch		KW
2	01.01.2015	01:00	120		2	125
3	01.01.2015	02:00	110			125
4	01.01.2015	03:00	122			116
5	01.01.2015	04:00	125			127
6	01.01.2015	05:00	127			123
7	01.01.2015	06:00	122			116
8	01.01.2015	07:00	118			130

Zelle	Formel
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

[schauan]

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

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

[BoskoBiati]

Hallo, 

Und was ist mit meinem Muster? 

[BoskoBiati]

Hallo,

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

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D	E	F
2	01.01.2015	00:00	113		4	114
3	01.01.2015	01:00	101			120
4	01.01.2015	02:00	107			109
5	01.01.2015	03:00	120			125

Zelle	Formel
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

[schauan]

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 )