Maximum je Kalenderwochea aus Stundenwerten

Forumthread: Maximum je Kalenderwochea aus Stundenwerten

thema

Hallo liebe EXperts!
Muss aus Stundenwerten über ein Jahr
für jede Kalenderwoche das Stunden-MAX ausgeben mit Zeitstempel.
Spalte A: KW
Spalte B: Wert
Spalte C: Datum (Tag)
Spalte D: Zeitstempel
Spalte E: Ausgabe des Max
Spalte F: Ausgabe des Zeitstempels vom MAX
Vgl. Datei im Anhang (die Datenreihe ist abgeschnitten, da ansonsten die Datei zu groß geworden wäre)
https://www.herber.de/bbs/user/68023.xls
Wer kann mir eine Lösung vorschlagen, die automatisiert das Max aus den Werten für die jeweilige KW ausgibt - mit dem Zeitstempel?
Vielen Dank im voraus für eure Hinweise!
Gruss TheMa

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

Daten - Pivot-Tabelle...

15.02.2010 17:27:44

Ramses

hallo
Und dem Assistenten folgen
Gruss Rainer

AW: Daten - Pivot-Tabelle...

15.02.2010 17:36:37

thema

Hallo Rainer,
vielen Dank für den schnellen Hinweis.
Jedoch: Gibt's eine Möglichkeit - ohne Pivot-Tabelle?
DENN:
Die Daten werden aus einem anderen System übermittelt,
die automatisiert ermittelten MAX-Werten und deren Zeitstempel werden für weitere Berechnungen
im gleichen Blatt benötigt. Ein manueller Anstoß der Pivot-Tabelle möchte ich daher nach Möglichkeit vermeiden.
Geht das?
Wäre toll.
Gruss
TheMa

Dann erst recht....

15.02.2010 18:00:25

Ramses

Hallo
Wenn die Daten aus einem anderen System übermittelt werden, wird die Tabelle und damit die Formeln ja immer wieder überschrieben.
Mit einer Piovt-Tabelle hast du die gesamte Auswertung auf einer anderen Tabelle und musst "bloss" aktualisieren
Alternativ halt so
Tabelle1

	A	B	C	D	E	F	G	H
6	KW (beginnend mit Montag)	Werte	Zeitstempel		Maximum je KW	Zeitstempel		KW
7	1	36.55	Do, 01.01.09	1:00	89.87	Fr, 02.01.09	13:00	1
8	1	37.12	Do, 01.01.09	2:00	92.58	Fr, 09.01.09	12:00	2
9	1	37.11	Do, 01.01.09	3:00
10	1	34.48	Do, 01.01.09	4:00	89.86852
11	1	33.91	Do, 01.01.09	5:00
12	1	33.88	Do, 01.01.09	6:00

Formeln der Tabelle

Zelle	Formel
A7	=KALENDERWOCHE(C7;2)
C7	=$C$1+(1/1440)
D7	=ZEIT($D$1;0;0)+(1/24)
E7	=MAX(B7:B102)
H7	=KALENDERWOCHE(F7;2)
A8	=KALENDERWOCHE(C8;2)
C8	=C7+(1/24)
D8	=D7+(1/24)
E8	=MAX(B103:B270)
H8	=KALENDERWOCHE(F8;2)
A9	=KALENDERWOCHE(C9;2)
C9	=C8+(1/24)
D9	=D8+(1/24)
A10	=KALENDERWOCHE(C10;2)
C10	=C9+(1/24)
D10	=D9+(1/24)
E10	{=MAX(WENN(A:A=H7;B:B))}
A11	=KALENDERWOCHE(C11;2)
C11	=C10+(1/24)
D11	=D10+(1/24)
A12	=KALENDERWOCHE(C12;2)
C12	=C11+(1/24)
D12	=D11+(1/24)

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Bedingte Formatierungen der Tabelle

Zelle	Nr.: / Bedingung	Format
B7	1. / Zellwert ist gleich =$E$7	Abc
B8	1. / Zellwert ist gleich =$E$7	Abc
B9	1. / Zellwert ist gleich =$E$7	Abc
B10	1. / Zellwert ist gleich =$E$7	Abc
B11	1. / Zellwert ist gleich =$E$7	Abc
B12	1. / Zellwert ist gleich =$E$7	Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Die Formel steht in E10
Gruss Rainer

AW: Dann erst recht....

15.02.2010 20:04:46

thema

Hallo Rainer,
danke für die Alternativlösung.
Habe diese angewendet - und auch die Pivot-Tabelle ausprobiert.
Jedoch - was in meiner ersten Anfrage "unter gegangen" ist:
Zum Maximum je Kalenderwoch muss ich das zugehörige Datum und die zugehörige Zeit ausgeben.
(Ginge das auch mit der Pivot-Tabelle?)
Ich hab's mit Formeln INDEX/Vergleich gemacht.
Habe dabei das Problem:
Wenn ein Maximum-Wert in zwei Kalenderwochen auftritt, werden die falschen zugehörigen Zeitstempel geliefert. Meine Formeln finden nur das erste Maximum.
Wie kann man die Formeln anpassen, damit der richtige Zeitstempel geliefert wird?
Kriterium ist Max UND KW...
Zum - hoffentlich - besseren Verständnis in der Anlage nochmals das - jetzt erweiterte - Beispiel,
vgl. Tabelle "Tabelle1Auswertung"
https://www.herber.de/bbs/user/68028.xls
Vielen Dank, wenn du dich weiter in mein Problem eindenen magst und mir weiterhelfen kannst.
Grüße
TheMa

AW: Dann erst recht....

15.02.2010 21:24:32

Ramses

Hallo
Tabelle1Auswertung

	A	B	C	D	E
6	KW	Max je Woche	ZeitstempelDatum	ZeitstempelStunde	Vergleichswert zum MAX
7	1	100.50	Do, 01.01.09	01:00:00	60.78
8	2	98.96	Mo, 05.01.09	05:00:00	53.43
9	3	87.84	Mo, 12.01.09	17:00:00	16.82
10	4	111.00	Mo, 19.01.09	03:00:00	8.82
11	5	98.96	Sa, 05.03.18	06:00:00	53.43
12	6	16.17	Mo, 02.02.09	01:00:00	60.78

Formeln der Tabelle

Zelle	Formel
B7	{=MAX(WENN(Tabelle1!$A$7:$A$775=A7;Tabelle1!B$7:B$775))}
C7	=SUMMENPRODUKT((GANZZAHL(Tabelle1!$A$7:$A$775)=$A7)(Tabelle1!$B$7:$B$775=$B7)Tabelle1!$C$7:$C$775)
D7	=SUMMENPRODUKT((GANZZAHL(Tabelle1!$A$7:$A$775)=$A7)(Tabelle1!$B$7:$B$775=$B7)Tabelle1!$D$7:$D$775)
E7	=INDEX(Tabelle1!$A$7:$G$775;VERGLEICH(B7;Tabelle1!$B$7:$B$775;0); 5)
B8	{=MAX(WENN(Tabelle1!$A$7:$A$775=A8;Tabelle1!B$7:B$775))}
C8	=SUMMENPRODUKT((GANZZAHL(Tabelle1!$A$7:$A$775)=A8)(Tabelle1!$B$7:$B$775=B8)Tabelle1!$C$7:$C$775)
D8	=SUMMENPRODUKT((GANZZAHL(Tabelle1!$A$7:$A$775)=$A8)(Tabelle1!$B$7:$B$775=$B8)Tabelle1!$D$7:$D$775)
E8	=INDEX(Tabelle1!$A$7:$G$775;VERGLEICH(B8;Tabelle1!$B$7:$B$775;0); 5)
B9	{=MAX(WENN(Tabelle1!$A$7:$A$775=A9;Tabelle1!B$7:B$775))}
C9	=SUMMENPRODUKT((GANZZAHL(Tabelle1!$A$7:$A$775)=A9)(Tabelle1!$B$7:$B$775=B9)Tabelle1!$C$7:$C$775)
D9	=SUMMENPRODUKT((GANZZAHL(Tabelle1!$A$7:$A$775)=$A9)(Tabelle1!$B$7:$B$775=$B9)Tabelle1!$D$7:$D$775)
E9	=INDEX(Tabelle1!$A$7:$G$775;VERGLEICH(B9;Tabelle1!$B$7:$B$775;0); 5)
B10	{=MAX(WENN(Tabelle1!$A$7:$A$775=A10;Tabelle1!B$7:B$775))}
C10	=SUMMENPRODUKT((GANZZAHL(Tabelle1!$A$7:$A$775)=A10)(Tabelle1!$B$7:$B$775=B10)Tabelle1!$C$7:$C$775)
D10	=SUMMENPRODUKT((GANZZAHL(Tabelle1!$A$7:$A$775)=$A10)(Tabelle1!$B$7:$B$775=$B10)Tabelle1!$D$7:$D$775)
E10	=INDEX(Tabelle1!$A$7:$G$775;VERGLEICH(B10;Tabelle1!$B$7:$B$775;0); 5)
B11	{=MAX(WENN(Tabelle1!$A$7:$A$775=A11;Tabelle1!B$7:B$775))}
C11	=SUMMENPRODUKT((GANZZAHL(Tabelle1!$A$7:$A$775)=A11)(Tabelle1!$B$7:$B$775=B11)Tabelle1!$C$7:$C$775)
D11	=SUMMENPRODUKT((GANZZAHL(Tabelle1!$A$7:$A$775)=$A11)(Tabelle1!$B$7:$B$775=$B11)Tabelle1!$D$7:$D$775)
E11	=INDEX(Tabelle1!$A$7:$G$775;VERGLEICH(B11;Tabelle1!$B$7:$B$775;0); 5)
B12	{=MAX(WENN(Tabelle1!$A$7:$A$775=A12;Tabelle1!B$7:B$775))}
C12	=SUMMENPRODUKT((GANZZAHL(Tabelle1!$A$7:$A$775)=A12)(Tabelle1!$B$7:$B$775=B12)Tabelle1!$C$7:$C$775)
D12	=SUMMENPRODUKT((GANZZAHL(Tabelle1!$A$7:$A$775)=$A12)(Tabelle1!$B$7:$B$775=$B12)Tabelle1!$D$7:$D$775)
E12	=INDEX(Tabelle1!$A$7:$G$775;VERGLEICH(B12;Tabelle1!$B$7:$B$775;0); 5)

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Allerdings ist mir die Spalte E überhapt nicht klar.
Du holst in Spalte B das MAX der jeweiligen KW,... gegenüber welches MAX soll denn dann in E verglichen werden ?
Gruss Rainer

AW: Dann erst recht....

15.02.2010 23:16:17

thema

Hallo Rainer,
danke für deine Mühe.
Irgendetwas stimmt noch nicht:
Vgl. dein Ergebnis in Zeile 11: KW 5, 98,96
in deiner Tabelle kommt ein Zeitstempel von Sa, 05.03.18 06:00:00 heraus.
Richtig wäre Fr, 30.01.09 10 Uhr
Hast du eine Erklärung dafür?
Zu deiner Frage in Spalte E: Die Werte in tabelle1 werden aus einem anderen System in die Tabelle übernommen. Der Wert in Spalte E wird aus tabelle1 geholt - einfach der Wert, der in der gleichen Zeile wie das Maximum steht.
Danke, wenn du nochmals antworten kannst.
Grüße in die Nacht
TheMa

AW: Dann erst recht....

15.02.2010 23:24:39

thema

Hallo Rainer,
nochmals zu deinen Lösungen:
Der Fehler scheint etwas damit zu tun zu haben, wenn ein (Maximum-)Wert sich wiederholt - dann scheint die Rückgabe des Zeitstempels nicht mehr richtig zu funktionieren.
Hoffe, du kannst noch etwas herausfinden.
Danke ganz vielmals im voraus.
TheMa

AW: Dann erst recht....

16.02.2010 12:06:26

Ramses

Hallo
Damit werden die Werte korrekt gefunden
Tabelle1Auswertung

	A	B	C	D	E
6	KW	Max je Woche	ZeitstempelDatum	ZeitstempelStunde	Vergleichswert zum MAX
7	1	89.87	Fr, 02.01.09	13:00:00	53.63
8	2	98.96	Mo, 05.01.09	05:00:00	53.43
9	3	87.84	Mo, 12.01.09	17:00:00	16.82
10	4	97.54	Fr, 23.01.09	11:00:00	8.82
11	5	98.96	Fr, 30.01.09	10:00:00	53.43
12	6	16.17	Mo, 02.02.09	01:00:00	60.78

Formeln der Tabelle

Zelle	Formel
B7	{=MAX(WENN(Tabelle1!$A$7:$A$775=A7;Tabelle1!B$7:B$775))}
C7	{=INDEX(Tabelle1!$A$7:$D$775;VERGLEICH(A7&B7;Tabelle1!$A$7:$A$775&Tabelle1!$B$7:$B$775;0); 3)}
D7	{=INDEX(Tabelle1!$A$7:$D$775;VERGLEICH($A7&$B7;Tabelle1!$A$7:$A$775&Tabelle1!$B$7:$B$775;0); 4)}
E7	=INDEX(Tabelle1!$A$7:$G$775;VERGLEICH(B7;Tabelle1!$B$7:$B$775;0); 5)
B8	{=MAX(WENN(Tabelle1!$A$7:$A$775=A8;Tabelle1!B$7:B$775))}
C8	{=INDEX(Tabelle1!$A$7:$D$775;VERGLEICH(A8&B8;Tabelle1!$A$7:$A$775&Tabelle1!$B$7:$B$775;0); 3)}
D8	{=INDEX(Tabelle1!$A$7:$D$775;VERGLEICH($A8&$B8;Tabelle1!$A$7:$A$775&Tabelle1!$B$7:$B$775;0); 4)}
E8	=INDEX(Tabelle1!$A$7:$G$775;VERGLEICH(B8;Tabelle1!$B$7:$B$775;0); 5)
B9	{=MAX(WENN(Tabelle1!$A$7:$A$775=A9;Tabelle1!B$7:B$775))}
C9	{=INDEX(Tabelle1!$A$7:$D$775;VERGLEICH(A9&B9;Tabelle1!$A$7:$A$775&Tabelle1!$B$7:$B$775;0); 3)}
D9	{=INDEX(Tabelle1!$A$7:$D$775;VERGLEICH($A9&$B9;Tabelle1!$A$7:$A$775&Tabelle1!$B$7:$B$775;0); 4)}
E9	=INDEX(Tabelle1!$A$7:$G$775;VERGLEICH(B9;Tabelle1!$B$7:$B$775;0); 5)
B10	{=MAX(WENN(Tabelle1!$A$7:$A$775=A10;Tabelle1!B$7:B$775))}
C10	{=INDEX(Tabelle1!$A$7:$D$775;VERGLEICH(A10&B10;Tabelle1!$A$7:$A$775&Tabelle1!$B$7:$B$775;0); 3)}
D10	{=INDEX(Tabelle1!$A$7:$D$775;VERGLEICH($A10&$B10;Tabelle1!$A$7:$A$775&Tabelle1!$B$7:$B$775;0); 4)}
E10	=INDEX(Tabelle1!$A$7:$G$775;VERGLEICH(B10;Tabelle1!$B$7:$B$775;0); 5)
B11	{=MAX(WENN(Tabelle1!$A$7:$A$775=A11;Tabelle1!B$7:B$775))}
C11	{=INDEX(Tabelle1!$A$7:$D$775;VERGLEICH(A11&B11;Tabelle1!$A$7:$A$775&Tabelle1!$B$7:$B$775;0); 3)}
D11	{=INDEX(Tabelle1!$A$7:$D$775;VERGLEICH($A11&$B11;Tabelle1!$A$7:$A$775&Tabelle1!$B$7:$B$775;0); 4)}
E11	=INDEX(Tabelle1!$A$7:$G$775;VERGLEICH(B11;Tabelle1!$B$7:$B$775;0); 5)
B12	{=MAX(WENN(Tabelle1!$A$7:$A$775=A12;Tabelle1!B$7:B$775))}
C12	{=INDEX(Tabelle1!$A$7:$D$775;VERGLEICH(A12&B12;Tabelle1!$A$7:$A$775&Tabelle1!$B$7:$B$775;0); 3)}
D12	{=INDEX(Tabelle1!$A$7:$D$775;VERGLEICH($A12&$B12;Tabelle1!$A$7:$A$775&Tabelle1!$B$7:$B$775;0); 4)}
E12	=INDEX(Tabelle1!$A$7:$G$775;VERGLEICH(B12;Tabelle1!$B$7:$B$775;0); 5)

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruss Rainer

AW: Dann erst recht....

16.02.2010 12:17:44

thema

Hallo Rainer,
ja - so klappt's.
Nach einer kurzen Nacht "drüber schlafen" und ausprobieren,
habe ich nun auch die INDEX-Formel statt der Summenprodukt-Formel angewandt.
Ganz herzlichen Dank für deine Mühe und Geduld! Ohne Deine Hilfe, hätte ich's wohl nicht hin bekommen.
Fall's es noch zum "Fasching" geht: viel Spaß.
Gruss
TheMa

Beliebteste Forumthreads (12 Monate)

Maximum je Kalenderwochea aus Stundenwerten

Forumthread: Maximum je Kalenderwochea aus Stundenwerten

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Maximum je Kalenderwochea aus Stundenwerten

Forumthread: Maximum je Kalenderwochea aus Stundenwerten

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