Monatsmittel nach Kalenderwochen ermitteln

[NoFear070]

Hallo zusammen,

ich wende mich heute mit einem weiterem Problem an euch, in der Hoffnung das mir jemand helfen kann.
Ich habe Daten, welchen fortlaufend nach Kalenderwochen aufgelistet sind
Zum Beispiel:
                              A                      B                 C            D
7                      2015,19                                              69,73%
8                      2015,18                                              68,36 %
9                      2015,17                                              70,23 %
10                    2015,16                                              66,98 %
11                         usw.
 
In Spalte A stehen die Kalenderwochen ( für das Format kann ich nichts, ich habe die Datei so übernommen (-;  ).
In Spalte D (und folgenden) stehen Werte deren Mittelwert ich ermitteln möchte.
Ich möchte diesen Mittelwert aber monatsbezogen ermitteln. Das bedeutet, alles Werte aus  Februar  (März, April, usw. ) zu einem Mittelwert zusammenfassen. Das heißt für Spalte D -> Februar hatte  4 Kalenderwochen, somit Mittelwert aus 4 Werten.
Wie kann ich Excel „beibringen“ welche Werte er nehmen soll, also welche Wert , bzw. welche Kalenderwochen zu Februar, März, April, usw. gehören?
Kann mir da jemand weiterhelfen?
Im Voraus herzlichen Dank.

[WillWissen]

Hi,

Ich möchte diesen Mittelwert aber monatsbezogen ermitteln. Das bedeutet, alles Werte aus  Februar  (März, April, usw. ) zu einem Mittelwert zusammenfassen. Das heißt für Spalte D -> Februar hatte  4 Kalenderwochen, somit Mittelwert aus 4 Werten.

mMn kannst du nur entweder nach Monaten oder nach KW dein Mittelwert berechnen. Es ist nämlich nicht korrekt, dass Februar nur 4 KW hatte. Der 1. Feb. fiel z.B. noch in KW5, der letzte endete dann in KW9. KW5 gehört also zu zwei Monaten. Der Folgemonat verhält sich "großzügiger": Beginn in KW9, Ende in KW14, wobei hier insgesamt 3 Monate involviert sind (Beginn der KW9 im Februar, Ende der KW14 im April.

[NoFear070]

Hi,

Ich möchte diesen Mittelwert aber monatsbezogen ermitteln. Das bedeutet, alles Werte aus  Februar  (März, April, usw. ) zu einem Mittelwert zusammenfassen. Das heißt für Spalte D -> Februar hatte  4 Kalenderwochen, somit Mittelwert aus 4 Werten.

mMn kannst du nur entweder nach Monaten oder nach KW dein Mittelwert berechnen. Es ist nämlich nicht korrekt, dass Februar nur 4 KW hatte. Der 1. Feb. fiel z.B. noch in KW5, der letzte endete dann in KW9. KW5 gehört also zu zwei Monaten. Der Folgemonat verhält sich "großzügiger": Beginn in KW9, Ende in KW14, wobei hier insgesamt 3 Monate involviert sind (Beginn der KW9 im Februar, Ende der KW14 im April.

Hallo WillWissen,
du hast natürlich Recht, es gibt Monate die sich eine KW "teilen". Aber wenn ich am Jahresende den Mittelwert der Mittelwerte nehme, ist das nicht sooo schlimm.
Wobei ich deine Anmerkung nicht ganz verstehe ( "... kannst du nur entweder nach Monaten oder nach KW dein Mittelwert berechnen..."). Ich möchte ja gern den Mittelwert nach Monaten berechnen. Hab aber als Quelle nur die KW.
Oder kann ich denn alternativ in Vorfeld einfach festlegen KW14 - KW18 = April; KW19-KW22= Mai?

Grüße Frank

[RPP63]

Hallo!
Ein Ansatz wäre, zunächst den Montag der entsprechenden KW zu ermitteln und dies als Ausgangspunkt für die weitere Auswertung zu nutzen:
(Spalte E ist als MMMM formatiert)


Tabelle2

	A	B	C	D	E	F
1	Mischmasch	KW	Jahr	MO der KW ist:	als Monat (Datum)	als Monat (Text)
2	2015,19	19	2015	Mo, 04.05.15	Mai	Mai
3	2015,18	18	2015	Mo, 27.04.15	April	April
4	2015,17	17	2015	Mo, 20.04.15	April	April
5	2015,16	16	2015	Mo, 13.04.15	April	April
6	2015,15	15	2015	Mo, 06.04.15	April	April
7	2015,14	14	2015	Mo, 30.03.15	März	März
8	2015,13	13	2015	Mo, 23.03.15	März	März
9	2015,12	12	2015	Mo, 16.03.15	März	März
10	2015,11	11	2015	Mo, 09.03.15	März	März
11	2015,10	10	2015	Mo, 02.03.15	März	März
12	2015,09	9	2015	Mo, 23.02.15	Februar	Februar
13	2015,08	8	2015	Mo, 16.02.15	Februar	Februar
14	2015,07	7	2015	Mo, 09.02.15	Februar	Februar
15	2015,06	6	2015	Mo, 02.02.15	Februar	Februar
16	2015,05	5	2015	Mo, 26.01.15	Januar	Januar
17	2015,04	4	2015	Mo, 19.01.15	Januar	Januar
18	2015,03	3	2015	Mo, 12.01.15	Januar	Januar
19	2015,02	2	2015	Mo, 05.01.15	Januar	Januar
20	2015,01	1	2015	Mo, 29.12.14	Dezember	Dezember

Formeln der Tabelle

Zelle Formel B2 =REST(A2;1)*100 C2 =GANZZAHL(A2) D2 =DATUM(C2;1;7*B2-3-WOCHENTAG(DATUM(C2;;);3)) E2 =D2 F2 =TEXT(D2;"MMMM")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8



Gruß Ralf

[WillWissen]

Hi,

mit einer Hilfsspalte würde ich es so lösen:

Tabelle1

	A	B	C	D	E	F
1	KW	HS-Monat
2	2015,19	5		69,73%	März	65,30%
3	2015,18	4		68,36%	April	70,65%
4	2015,17	4		70,23%	Mai	69,73%
5	2015,16	4		66,98%	Juni
6	2015,15	4		66,66%
7	2015,14	4		81,00%
8	2015,13	3		68,99%
9	2015,12	3		59,50%
10	2015,11	3		67,40%

Formeln der Tabelle

Zelle Formel B2 =MONAT(("4.1."&LINKS(A2;4)*1)+RECHTS(A2;2)*1*7-7-REST("2.1."&LINKS(A2;4)*1;7)-1+ZEILE(A1)) F2 {=WENNFEHLER(MITTELWERT(WENN($B$2:$B$10=MONAT($E2);$D$2:$D$10;""));"")} B3 =MONAT(("4.1."&LINKS(A3;4)*1)+RECHTS(A3;2)*1*7-7-REST("2.1."&LINKS(A3;4)*1;7)-1+ZEILE(A2)) F3 {=WENNFEHLER(MITTELWERT(WENN($B$2:$B$10=MONAT($E3);$D$2:$D$10;""));"")} B4 =MONAT(("4.1."&LINKS(A4;4)*1)+RECHTS(A4;2)*1*7-7-REST("2.1."&LINKS(A4;4)*1;7)-1+ZEILE(A3)) F4 {=WENNFEHLER(MITTELWERT(WENN($B$2:$B$10=MONAT($E4);$D$2:$D$10;""));"")} B5 =MONAT(("4.1."&LINKS(A5;4)*1)+RECHTS(A5;2)*1*7-7-REST("2.1."&LINKS(A5;4)*1;7)-1+ZEILE(A4)) F5 {=WENNFEHLER(MITTELWERT(WENN($B$2:$B$10=MONAT($E5);$D$2:$D$10;""));"")} B6 =MONAT(("4.1."&LINKS(A6;4)*1)+RECHTS(A6;2)*1*7-7-REST("2.1."&LINKS(A6;4)*1;7)-1+ZEILE(A5)) B7 =MONAT(("4.1."&LINKS(A7;4)*1)+RECHTS(A7;2)*1*7-7-REST("2.1."&LINKS(A7;4)*1;7)-1+ZEILE(A6)) B8 =MONAT(("4.1."&LINKS(A8;4)*1)+RECHTS(A8;2)*1*7-7-REST("2.1."&LINKS(A8;4)*1;7)-1+ZEILE(A7)) B9 =MONAT(("4.1."&LINKS(A9;4)*1)+RECHTS(A9;2)*1*7-7-REST("2.1."&LINKS(A9;4)*1;7)-1+ZEILE(A8)) B10 =MONAT(("4.1."&LINKS(A10;4)*1)+RECHTS(A10;2)*1*7-7-REST("2.1."&LINKS(A10;4)*1;7)-1+ZEILE(A9))

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

[RPP63]

Hallo Günther!
Schöne Lösung! 
Aber: wenn schon WENNFEHLER, warum dann nicht gleich auch MITTELWERTWENN, also ohne Matrixformel?


Tabelle3

	A	B	C	D	E	F
1	KW	HS-Monat
2	2015,19	5		69,73%	März	65,30%
3	2015,18	4		68,36%	April	70,65%
4	2015,17	4		70,23%	Mai	69,73%
5	2015,16	4		66,98%	Juni
6	2015,15	4		66,66%
7	2015,14	4		81,00%
8	2015,13	3		68,99%
9	2015,12	3		59,50%
10	2015,11	3		67,40%

Formeln der Tabelle

Zelle Formel B2 =MONAT(("4.1."&LINKS(A2;4)*1)+RECHTS(A2;2)*1*7-7-REST("2.1."&LINKS(A2;4)*1;7)-1+ZEILE(A1)) F2 =WENNFEHLER(MITTELWERTWENN($B$2:$B$10;MONAT(E2);D$2:D$10);"")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gruß Ralf

[RPP63]

Ergänzend zu meinem ersten Ansatz (Monatszugehörigkeit der KW basiert auf dem Montag der jew. KW):
Dies mit nur einer Hilfsspalte, in Spalte J stehen die Monatsnamen als Text:

Tabelle2

	A	G	H	I	J	K
1	Mischmasch	Kombiformel	Prozente		Monat	MW
2	2015,19	Mai	69,73%		Januar
3	2015,18	April	68,36%		Februar	30,51%
4	2015,17	April	70,23%		März	67,94%
5	2015,16	April	66,98%		April	68,06%
6	2015,15	April	66,66%		Mai	69,73%
7	2015,14	März	81,00%		Juni
8	2015,13	März	68,99%		Juli
9	2015,12	März	59,50%		August
10	2015,11	März	67,40%		September
11	2015,10	März	62,80%		Oktober
12	2015,09	Februar	45,78%		November
13	2015,08	Februar	15,24%		Dezember
14	2015,07	Februar
15	2015,06	Februar
16	2015,05	Januar
17	2015,04	Januar
18	2015,03	Januar
19	2015,02	Januar
20	2015,01	Dezember

Formeln der Tabelle

Zelle Formel G2 =TEXT(DATUM(GANZZAHL(A2);1;7*REST(A2;1)*100-3-WOCHENTAG(DATUM(GANZZAHL(A2);;);3));"MMMM") K2 =WENNFEHLER(MITTELWERTWENN(G$2:G$20;J2;H$2:H$20);"")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gruß Ralf

[WillWissen]

Hi Ralf,

wenn schon WENNFEHLER, warum dann nicht gleich auch MITTELWERTWENN

wo du Recht hast, hast du Recht. Ich hatte ursprünglich mit einem anderen Ansatz experimentiert und dort MITTELWERTWENN verwendet. Der Ansatz war falsch - dementsprechend muss ich wohl bei meiner Lösung das irgendwie im Hinterstübchen gehabt und abgelehnt haben.

[NoFear070]

Hallo zusammen,
das sieht ja perfekt aus !
Ich weiß nicht, ob ich das heute oder morgen schaffe werde umzusetzen - hab ein wenig Zeitnot.
Werde euch aber auf dem Laufenden halten !!

Grüße
Frank

[BoskoBiati]

Hallo zusammen,

ich würde es so machen:

Arbeitsblatt mit dem Namen 'Tabelle1'
	G	H	I	J
1	9	67,3%	01.03.2015	67,6%
2	10	68,1%
3	11	65,9%
4	12	68,6%
5	13	67,9%
6	14	68,4%

Zelle	Formel
J1	=(SUMMEWENNS(H:H;G:G;">="&KALENDERWOCHE(7*KÜRZEN((I1+4)/7)+2;21);G:G;"<="&KALENDERWOCHE(7*KÜRZEN(MONATSENDE(I1;0)/7)))+(SVERWEIS(KALENDERWOCHE(7*KÜRZEN((I1+4)/7)+2;21)-1;G:H;2;0))/7*(7-REST(I1-2;7))+SVERWEIS(KALENDERWOCHE(7*KÜRZEN(MONATSENDE(I1;0)/7)+1;21);G:H;2;0)/7*(7-REST(I2-2;7)))/TAG(MONATSENDE(I1;0))*7

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg