Aufgabe: Werte, die DIN-Kalenderwochen zugeordnet sind, sollen auf ganze Kalendermonate aufgeteilt werden. Problematisch dabei ist, dass die Woche keine Teileinheit des Monats darstellt, da sie in zwei Monaten liegen kann.
A B C D E F G 1 Jahr-KW Werte Monat Aufteilwert Wochentage 2 200652 14 01.12.2006 14 7 3 200701 14 01.01.2007 38 4 200702 7 01.02.2007 28 Auf Mo-So 5 200703 7 01.03.2007 31 der Monate 6 200704 7 01.04.2007 30 wird aufgeteilt 7 200705 7 01.05.2007 31 8 200706 7 01.06.2007 30 9 200707 7 01.07.2007 31 10 200708 7 01.08.2007 31 11 200709 7 01.09.2007 30 12 200710 7 01.10.2007 31 13 200711 7 01.11.2007 30 14 200712 7 01.12.2007 31 15 200713 7 01.01.2008 6 16 200714 7 01.02.2008 0 17 200715 7 01.03.2008 0 18 200716 7 392 ... und so weiter bis ... 54 200752 7 55 200753 7 56 392
Voraussetzungen:
A2:A55 enthält die sechsstelligen (nicht zwingend fortlaufenden) KW und wird als "KW" benannt (Format 0000"-"00 für Übersichtlichkeit)
B2:B55 enthält die zugehörigen Werte und wird als "Werte" benannt
Ab D2 stehen die (fortlaufenden!) Monatsersten; dafür D3: =D2+32-TAG(D2+31) runterkopieren
In G2 (Benennung: "Wochentage") wird bestimmt, auf welche Tage beginnend mit Montag aufgeteilt werden soll. Möglich sind hier die Eingaben 1 (Mo-Mo) bis 7 (Mo-So).
Im folgenden werden Formeln benannt (mit Name Einfügen Definieren Name bezieht sich auf):
FMo =("4.1."&GANZZAHL(KW%))+REST(KW;100)*7-REST("2.1."&GANZZAHL(KW%);7)
TageLfdMonat =WENN(TAG(FMo)<8;WENN(TAG(FMo)-8+Wochentage<0;;TAG(FMo)-8+Wochentage);Wochentage)
TageVorMonat =Wochentage-TageLfdMo
AuftLfdM =TageLfdMonat/Wochentage*Werte
AuftVorM =TageLfdMonat/Wochentage*Werte
Aufteilwert =SUMMENPRODUKT((FMo>=D2)*(FMo<D3)*AuftLfdM+(FMo>=D2+32-TAG(D2+31))*(FMo<D3+32-TAG(D3+31))*AuftVorM)
(Aufteilwert muss in E2 stehend so definiert werden!)
E2: =Aufteilwert (runterkopieren)
Die Ermittlung des Kalenderwoche-Folgemontags (FMo) ist von Franz Pölt.
In Ländern mit abweichendem Wochenbeginn (USA und auch nach jüdischer Tradition: Sonntag; arabische Länder: Samstag) funktioniert diese Lösung nicht, da hier fix vom Montag nach DIN 1355 ausgegangen wird.
Anmerkungen:
Sollen bei den Monaten auch Lücken erlaubt sein, ändert sich "Aufteilwert" auf:
=SUMMENPRODUKT((FMo>=D2)*(FMo<D2+32-TAG(D2+31))*AuftLfdM+(FMo>=D2+32-TAG(D2+31))*(FMo<D2+63-TAG(D2+62))*AuftVorM)
Hintergrund: Hier bezieht sich die Formel in Zeile 2 auch nur auf selbige Zeile (abgesehen von den Arrays). Damit ist sie auch sicherer.
Das Zahlenbeispiel ist sicher schlecht, da mit jeweils dem Wert 7 pro KW und der Wahl aller Wochentage gerade die Anzahl Monatstage der Monate selbst wiedergegeben werden. In diesem Falle dient dies als Kontrolle für die Funktion der Formel.
Kurioses:
Werden einige Formeln (z.B. FMo) nicht benannt, was sonst selten einen Unterschied zur Benennung macht, funktioniert die Formel nicht.