Das Clever-Excel-Forum.de - Treffen
... 14.-16. September 2018 im Allgäu ...

Monatsmittel nach Kalenderwochen ermitteln
#1
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.
to top
#2
Hi,
Zitat: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.
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
to top
#3
(07.05.2015, 06:50)WillWissen schrieb: Hi,

Zitat: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
to top
#4
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

ABCDEF
1MischmaschKWJahrMO der KW ist:als Monat (Datum)als Monat (Text)
22015,19192015Mo, 04.05.15MaiMai
32015,18182015Mo, 27.04.15AprilApril
42015,17172015Mo, 20.04.15AprilApril
52015,16162015Mo, 13.04.15AprilApril
62015,15152015Mo, 06.04.15AprilApril
72015,14142015Mo, 30.03.15MärzMärz
82015,13132015Mo, 23.03.15MärzMärz
92015,12122015Mo, 16.03.15MärzMärz
102015,11112015Mo, 09.03.15MärzMärz
112015,10102015Mo, 02.03.15MärzMärz
122015,0992015Mo, 23.02.15FebruarFebruar
132015,0882015Mo, 16.02.15FebruarFebruar
142015,0772015Mo, 09.02.15FebruarFebruar
152015,0662015Mo, 02.02.15FebruarFebruar
162015,0552015Mo, 26.01.15JanuarJanuar
172015,0442015Mo, 19.01.15JanuarJanuar
182015,0332015Mo, 12.01.15JanuarJanuar
192015,0222015Mo, 05.01.15JanuarJanuar
202015,0112015Mo, 29.12.14DezemberDezember
Formeln der Tabelle
ZelleFormel
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
to top
#5
Hi,

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

Tabelle1

ABCDEF
1KWHS-Monat
22015,19569,73%März65,30%
32015,18468,36%April70,65%
42015,17470,23%Mai69,73%
52015,16466,98%Juni
62015,15466,66%
72015,14481,00%
82015,13368,99%
92015,12359,50%
102015,11367,40%
Formeln der Tabelle
ZelleFormel
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
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
to top
#6
Hallo Günther!
Schöne Lösung! Thumps_up
Aber: wenn schon WENNFEHLER, warum dann nicht gleich auch MITTELWERTWENN, also ohne Matrixformel?


Tabelle3

ABCDEF
1KWHS-Monat
22015,19569,73%März65,30%
32015,18468,36%April70,65%
42015,17470,23%Mai69,73%
52015,16466,98%Juni
62015,15466,66%
72015,14481,00%
82015,13368,99%
92015,12359,50%
102015,11367,40%
Formeln der Tabelle
ZelleFormel
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
to top
#7
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

AGHIJK
1MischmaschKombiformelProzenteMonatMW
22015,19Mai69,73%Januar
32015,18April68,36%Februar30,51%
42015,17April70,23%März67,94%
52015,16April66,98%April68,06%
62015,15April66,66%Mai69,73%
72015,14März81,00%Juni
82015,13März68,99%Juli
92015,12März59,50%August
102015,11März67,40%September
112015,10März62,80%Oktober
122015,09Februar45,78%November
132015,08Februar15,24%Dezember
142015,07Februar
152015,06Februar
162015,05Januar
172015,04Januar
182015,03Januar
192015,02Januar
202015,01Dezember
Formeln der Tabelle
ZelleFormel
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
to top
#8
Hi Ralf,


Zitat: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.
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
to top
#9
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
to top
#10
Hallo zusammen,

ich würde es so machen:


Arbeitsblatt mit dem Namen 'Tabelle1'
 GHIJ
1967,3%01.03.201567,6%
21068,1%  
31165,9%  
41268,6%  
51367,9%  
61468,4%  

ZelleFormel
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
Gruß
Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste