Office: Zahl von einem vorgegebenen Zeitraum in Kalenderwochen aufteilen

Ahh Mc Santa,

jetzt versteh ich. Anteilig der Tage in den Kalenderwochen. Das geht natürlich auch, da ist die Berechnung nur auf die Tage in der KW zu verteilen... Das geht natürlich auch! *biggrin.gif*

 

Hi McSanta,

genau so sollte es aussehen *Smilie Sorry ich habe mich von Anfang an falsch ausgedrückt: Die Menge soll genau so wie du es berechnet hast, aufgeteilt werden:

Beispiel: Menge 15.000 von 31.07. - 08.08.
KW31: 4 Tage
KW32: 5 Tage

KW31: 15.000/9*4=6666,7
KW32: 15.000/9*5=8333,3

Wäre genial, wenn du für das eine Lösung finden könntest - ich stoße hier leider ziemlich an meine Grenzen *wink.gif*

 

Mit Arbeitstagen oder vollen Wochentagen (sprich 7)?

 

Hi, mit vollen Wochentagen (7) wäre es völlig ausreichend

 

Hallo,

also in der Datei können jetzt jahresübergreifend Zeiträume angegeben werden. Die Berechnung erfolgt dann nur bis zum Ende des Jahres (31.12.) bzw. ab dem Beginn des Jahres (01.01.)! Basis 7-Tage-Woche!!

Für die Auflösung der übergreifenden Zeiträume muss der Satz einfach noch einmal für das 2te Jahr eingefügt werden (d.h. gleicher Datensatz nur das Jahr entsprechend anpassen)...

Hoffe das hilft soweit...

 

Hi BCoder

Wow perfekt vielen Dank!!!! *Smilie Eine kleine Bitte noch: Könntest du noch die ausgegebenen Zahlen ohne Dezimalstellen runden?
Kannst du mir auch erklären aus wwas sich die Formel auseinander setzt, dass ich so halbwegs nachvollziehen kann was hier geschieht?

Danke!

 

Guten Morgen,

klar kann ich machen *Smilie...

Also die Formel besteht aus 3 Teilen:

=$C2/($F2-$E2+1)*(WENN(JAHR(DATUM($D2;1;7*WERT(TEIL(G$1;3;2))-3-WOCHENTAG("2/"&$D2;3))+6)>$D2;DATUM($D2;12;31);WENN(KALENDERWOCHE($F2)=WERT(TEIL(G$1;3;2));$F2;DATUM($D2;1 ;7*WERT(TEIL(G$1;3;2))-3-WOCHENTAG("2/"&$D2;3))+6))-WENN(JAHR(DATUM($D2;1;7*WERT(TEIL(G$1;3;2))-3-WOCHENTAG("2/"&$D2;3)))=KALENDERWOCHE(WENN(JAHR($E2)$D2;DATUM($D2;12;31);WENN(KALENDERWOCHE($F2)=WERT(TEIL(G$1;3;2));$F2;DATUM($D2;1 ;7*WERT(TEIL(G$1;3;2))-3-WOCHENTAG("2/"&$D2;3))+6))-WENN(JAHR(DATUM($D2;1;7*WERT(TEIL(G$1;3;2))-3-WOCHENTAG("2/"&$D2;3)))=KALENDERWOCHE(WENN(JAHR($E2) Alle Zellen auswählen, die Du formatieren möchtest
-> Rechtskclick -> Zellen formatieren
-> im Reiter Zahlen -> auf Zahl -> Dezimalstellen einstellen und evtl Tausendertrennzeichen -> OK

 

Vielen herzlichen Dank *Smilie

 

Hallo BCoder,

ich bräuchte wieder deine Hilfe, denn ich müsste nun die Menge nun doch in einer eigenen Tabelle auf die einzelnen Tage runterbrechen, denn ich muss es nun dann zusätzlich auch pro Monat auswerten.

Hast du da einen Lösungsansatz bzw. kannst du mir bitte helfen?

Danke im Voraus

 

Hi m8dungel und BCoder,
( habt ihr vielleicht auch richtige Namen, mit denen man euch besser ansprechen könnte? :-) )

zunächst zu der Lösung für die KWs. Da kommt in der Formel einige Male die Excelfkt. KALENDERWOCHE() vor,
mal mit der 2 als 2. Parameter, mal ohne 2. Parameter. So rechnet diese Fkt. oft falsch.
Erst ab XL2010 gibt es den 2. Parameter 21 - und nur damit rechnet KALENDERWOCHE() die nach DIN/EN korrekte KW.
Dazu gibt es viele Seiten im WWW. Hier nur mal eine davon:
http://www.excelformeln.de/formeln.html?welcher=7

In der Tabelle taucht in 2014 eine KW 53 auf. Im Jahr 2014 gibt es aber keine KW 53.

m8dungel, hattest du den Wunsch, dass die Ergebnisse auf Ganzzahlen gerundet weden?
Dann reicht das Umformatieren nicht aus, denn dabei bleiben die Werte mit x Nachkommastellen in den Zellen stehen,
sie werden nur nicht angezeigt.
Mit RUNDEN() werden die Werte geändert.

Die Formel in O5 ist auch etwas kürzer als die in O3.

	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1				Wo-Beg	13.01.14	20.01.14	27.01.14	03.02.14	10.02.14	17.02.14	24.02.14		15.12.14	22.12.14	29.12.14	05.01.15	12.01.15
2	Menge gebucht	Jahr	Laufzeit Start	Laufzeit Ende	2014-03	2014-04	2014-05	2014-06	2014-07	2014-08	2014-09		2014-51	2014-52	2015-01	2015-02	2015-03		xxxx-53
3	30000	2014	23.12.2014	08.01.2015										10588					5294
4	30000	2015	23.12.2014	08.01.2015											7059	7059
5	30000	2014	23.12.2014	08.01.2015										10588	5294
6	30000	2015	23.12.2014	08.01.2015											7059	7059
7
8	33	2014	23.01.2014	24.02.2014		4	7	7	7	7	1
9	33	2014	23.01.2014	24.02.2014		4	7	7	7	7	1
10
11	36	2014	26.12.2014	12.01.2015										6					6
12	36	2015	26.12.2014	12.01.2015											8	14	2
13	36	2014	26.12.2014	12.01.2015										6	6
14	36	2015	26.12.2014	12.01.2015											8	14	2

Zelle	Formel
G1	=7*DM((7&1-LINKS(G2;4))/7+RECHTS(G2;2);)+177
H1	=G1+7
O1	=7*DM((7&1-LINKS(O2;4))/7+RECHTS(O2;2);)+177
P1	=O1+7
O3	=RUNDEN($C3/($F3-$E3+1)*(WENN(JAHR(DATUM($D3;1;7*WERT(TEIL(O;6;2))-3-WOCHENTAG("2/"&$D3;3))+6)>$D3;DATUM($D3;12;31);WENN(KALENDERWOCHE($F3)=WERT(TEIL(O;6;2));$F3;DATUM($D3;1;7*WERT(TEIL(O;6;2))-3-WOCHENTAG("2/"&$D3;3))+6))-WENN(JAHR(DATUM($D3;1;7*WERT(TEIL(O;6;2))-3-WOCHENTAG("2/"&$D3;3)))<$D3;DATUM($D3;1;1);WENN(KALENDERWOCHE($E3;2)=WERT(TEIL(O;6;2));$E3;DATUM($D3;1;7*WERT(TEIL(O;6;2))-3-WOCHENTAG("2/"&$D3;3))))+1)*WENN(UND(WERT(TEIL(O;6;2))>=KALENDERWOCHE(WENN(JAHR($E3)<$D3;DATUM($D3;1;1);$E3);2);WERT(TEIL(O;6;2))<=KALENDERWOCHE(WENN(JAHR($F3)>$D3;DATUM($D3;12;31);$F3);2));1;0);0)
O5	=RUNDEN($C5/($F5+1-$E5)*MAX(;MIN(O+7;$F5+1;1&-($D5+1))-MAX(O;$E5;1&-$D5));)
G8	=RUNDEN($C8/($F8-$E8+1)*(WENN(JAHR(DATUM($D8;1;7*WERT(TEIL(G;6;2))-3-WOCHENTAG("2/"&$D8;3))+6)>$D8;DATUM($D8;12;31);WENN(KALENDERWOCHE($F8)=WERT(TEIL(G;6;2));$F8;DATUM($D8;1;7*WERT(TEIL(G;6;2))-3-WOCHENTAG("2/"&$D8;3))+6))-WENN(JAHR(DATUM($D8;1;7*WERT(TEIL(G;6;2))-3-WOCHENTAG("2/"&$D8;3)))<$D8;DATUM($D8;1;1);WENN(KALENDERWOCHE($E8;2)=WERT(TEIL(G;6;2));$E8;DATUM($D8;1;7*WERT(TEIL(G;6;2))-3-WOCHENTAG("2/"&$D8;3))))+1)*WENN(UND(WERT(TEIL(G;6;2))>=KALENDERWOCHE(WENN(JAHR($E8)<$D8;DATUM($D8;1;1);$E8);2);WERT(TEIL(G;6;2))<=KALENDERWOCHE(WENN(JAHR($F8)>$D8;DATUM($D8;12;31);$F8);2));1;0);0)
H8	=RUNDEN($C8/($F8-$E8+1)*(WENN(JAHR(DATUM($D8;1;7*WERT(TEIL(H;6;2))-3-WOCHENTAG("2/"&$D8;3))+6)>$D8;DATUM($D8;12;31);WENN(KALENDERWOCHE($F8)=WERT(TEIL(H;6;2));$F8;DATUM($D8;1;7*WERT(TEIL(H;6;2))-3-WOCHENTAG("2/"&$D8;3))+6))-WENN(JAHR(DATUM($D8;1;7*WERT(TEIL(H;6;2))-3-WOCHENTAG("2/"&$D8;3)))<$D8;DATUM($D8;1;1);WENN(KALENDERWOCHE($E8;2)=WERT(TEIL(H;6;2));$E8;DATUM($D8;1;7*WERT(TEIL(H;6;2))-3-WOCHENTAG("2/"&$D8;3))))+1)*WENN(UND(WERT(TEIL(H;6;2))>=KALENDERWOCHE(WENN(JAHR($E8)<$D8;DATUM($D8;1;1);$E8);2);WERT(TEIL(H;6;2))<=KALENDERWOCHE(WENN(JAHR($F8)>$D8;DATUM($D8;12;31);$F8);2));1;0);0)
G9	=RUNDEN($C9/($F9+1-$E9)*MAX(;MIN(G+7;$F9+1;1&-($D9+1))-MAX(G;$E9;1&-$D9));)
H9	=RUNDEN($C9/($F9+1-$E9)*MAX(;MIN(H+7;$F9+1;1&-($D9+1))-MAX(H;$E9;1&-$D9));)
O13	=RUNDEN($C13/($F13+1-$E13)*MAX(;MIN(O+7;$F13+1;1&-($D13+1))-MAX(O;$E13;1&-$D13));)
O14	=RUNDEN($C14/($F14+1-$E14)*MAX(;MIN(O+7;$F14+1;1&-($D14+1))-MAX(O;$E14;1&-$D14));)

 

Hi,
und hier eine Lösung für Monate:

	C	D	E	F	G	H	I	J	K	L	M	N
1	Menge gebucht	Jahr	Laufzeit Start	Laufzeit Ende	2014-10	2014-11	2014-12	2015-01	2015-02	2015-03	2015-04	<- hier stehen die Monate
2	30000	2014	23.12.2014	08.01.2015			15.882,00					(Datum mit Format JJJJ-MM)
3	30000	2015	23.12.2014	08.01.2015				14.118,00
4	18	2014	26.12.2014	12.01.2015			6,00
5	18	2015	26.12.2014	12.01.2015				12,00
6	72	2014	01.12.2014	10.02.2015			31,00
7	72	2015	01.12.2014	10.02.2015				31,00	10,00

Zelle	Formel
H1	=DATUM(JAHR(G1);MONAT(G1)+1;1)
I1	=DATUM(JAHR(H1);MONAT(H1)+1;1)
G2	=RUNDEN($C2/($F2+1-$E2) * MAX(0;MIN(DATUM(JAHR(G);MONAT(G)+1;1);$F2+1;(1&-($D2+1)))-MAX(G;$E2;0+(1&-$D2)));)

Diese Tabelle und auch die KW-Lösungen stehen in der hochgeladenen Mappe.

 

Guten Morgen Erich G.,

Bei dem Thema Kalenderwoche gebe ich Dir absolut recht! Die Formeln, die nur mit einem Parameter angegeben sind, sollten natürlich zur Sicherheit noch mit dem 2. Parameter für den ersten Wochentag versehen werden!

Zum Thema mit der KW53, wenn ich eine Aufteilung des Jahres nach KW vornehme und diesen auch als abgeschlossen betrachte, dann würde das Ergebnis für die KW1 falsch dargestellt werden, weil eben der letzte Teil des Jahres in die erste KW rutschen würde!
Wie würdest Du das bei einer Auflösung eines abgeschlossenen Jahres umsetzen?

 

Hi Danny, *wink.gif*
(ich heiße übrigens nicht "Erich G." - das ist nur meine MOF-UserID -, sondern einfach Erich...)

"zur Sicherheit noch mit dem 2. Parameter für den ersten Wochentag versehen"
reicht mir nicht so ganz aus. Es muss schon die 21 sein, nicht z. B. die 2, damit KALENDERWOCHE() richtig rechnet.

Was meinst du damit?
"wenn ich eine Aufteilung des Jahres nach KW vornehme und diesen auch als abgeschlossen betrachte"

"würde das Ergebnis für die KW1 falsch dargestellt werden, weil eben der letzte Teil des Jahres in die erste KW rutschen würde"
Was wäre daran falsch? Es ist einfach die Realität, dass einige Tage der KW 2015-01 noch zum Jahr 2014 gehören.
Das kannst du doch nicht ändern wollen.

Sieh dir mal in Zelle 'KW gerundet'!Q5 (oder in der Tabelle in meinem Beitrag #25 hier) die grün unterlegte Zahl 5294 an.
Das ist der Anteil, der auf den in 2014 liegenden Teil der KW 2015-01 entfällt.

In Q6 steht dann der Anteil in der KW 2015-*01, der in 2015 liegt: 7059.

So habe ich das umgesetzt, oder nicht?

 

Hallo Erich,

ich sage ja nicht, dass deine Umsetzung nicht richtig ist oder falsch!

In den Ausgangsposts von m8dungel war eine Auflösung des Jahres nach KW in 52/53 Spalten vorgesehen, ohne für jedes neue Jahr wieder Spalten anfügen zu müssen, sondern jahresübergreifend im selben Bereich.
Damit fällt die Eingrenzung der KW um das Jahr, so wie es in Deinem Ansatz geschieht, weg.

Es wird jedes Jahr in den Spalten KW1 bis KW52/53 dargestellt und genau bei dieser Darstellung wäre die KW1 falsch! Somit ist Deine pauschale Aussage, dass der Parameter "2 - Montag" grundsätzlich falsch ist nicht richtig! Es kommt eben darauf an, was genau dargestellt werden soll! Da muss der Nutzer entscheiden, wie er es darstellen möchte.

 

Hi Danny,
hier habe ich mal die KWen für einige Jahre jeweils zum Jahresbeginn und -ende dargestellt.
Da kann man schön sehen, was so alles vorkommen kann.

	A	B	C	D	E	F	G	H	I
1	Jahr	Beg	KW(Beg)	2. KW		End	vorletzte KW	KW(End)
2	2010	Fr 01.01.10	2009-53	2010-01		Fr 31.12.10	2010-51	2010-52
3	2011	Sa 01.01.11	2010-52	2011-01		Sa 31.12.11	2011-51	2011-52
4	2012	So 01.01.12	2011-52	2012-01		Mo 31.12.12	2012-52	2013-01	54 Wochen
5	2013	Di 01.01.13	2013-01	2013-02		Di 31.12.13	2013-52	2014-01
6	2014	Mi 01.01.14	2014-01	2014-02		Mi 31.12.14	2014-52	2015-01
7	2015	Do 01.01.15	2015-01	2015-02		Do 31.12.15	2015-52	2015-53
8	2016	Fr 01.01.16	2015-53	2016-01		Sa 31.12.16	2016-51	2016-52
9	2017	So 01.01.17	2016-52	2017-01		So 31.12.17	2017-51	2017-52
10	2018	Mo 01.01.18	2018-01	2018-02		Mo 31.12.18	2018-52	2019-01
11	2019	Di 01.01.19	2019-01	2019-02		Di 31.12.19	2019-52	2020-01
12	2020	Mi 01.01.20	2020-01	2020-02		Do 31.12.20	2020-52	2020-53
13	2021	Fr 01.01.21	2020-53	2021-01		Fr 31.12.21	2021-51	2021-52
14	2022	Sa 01.01.22	2021-52	2022-01		Sa 31.12.22	2022-51	2022-52

Zelle	Formel
B2	=--(1&-A2)
C2	=JAHR(B2+3-REST(B2-2;7))&TEXT(KALENDERWOCHE(B2;21);"-00")
D2	=A2&TEXT(KALENDERWOCHE(B2+7;21);"-00")
F2	=(1&-A2-1)-1
G2	=A2&TEXT(KALENDERWOCHE(F2-7;21);"-00")
H2	=JAHR(F2+3-REST(F2-2;7))&TEXT(KALENDERWOCHE(F2;21);"-00")

Fast jedes Jahr wird von 53 Wochen berührt, ab und zu eines von 54 Wochen - wie das Jahr 2012 (und 1984 und 2040).
Bedeutet: Man braucht 53 oder 54 Spalten für die Verteilung auf die Wochen innerhalb eines Jahres.

Sicher kann man die im Jahr liegenden Wochen ab 01 "durchnummerieren".
Das wäre dann für dass Jahr 2012 vielleicht so zu interpretieren:
Die 1. Woche im Jahr 2012 ist die DIN-KW 2011-52
die 2. Woche im Jahr 2012 ist die DIN-KW 2012-01
...
die 52. Woche im Jahr 2012 ist die DIN-KW 2012-51
die 53. Woche im Jahr 2012 ist die DIN-KW 2012-52
die 54. Woche im Jahr 2012 ist die DIN-KW 2013-01

KALENDERWOCHE("01.01.2012";2) ist übrigens wirklich 1,
KALENDERWOCHE("31.12.2012";2) ist 54.

Es kommt darauf an, was man will. Klar sollte aber sein, dass die Nummern 1 bis 54 nichts zu tun haben mit der üblichen DIN-KW.
Ich habe nicht geschrieben, dass der Parameter "2 - Montag" grundsätzlich falsch ist, sondern:
"Erst ab XL2010 gibt es den 2. Parameter 21 - und nur damit rechnet KALENDERWOCHE() die nach DIN/EN korrekte KW."