Office: (Office 2010) Beträge auf Kalenderwochen verteilen

Hallo alle zusammen,

dieses Forum ist wirklich praktisch und hat mir in letzter Zeit oft super geholfen - die Experten hier sind echt genial! =)

Deshalb würde ich kurz wieder eure Hilfe benötigen - es geht um die Modifikation einer Formel, die einen Betrag über einen vorgegebenen Zeitraum nach Monaten aufteilt (ich habe sie in diesem Thread gefunden und selber schon leicht geändert: http://www.ms-office-forum.net/forum...d.php?t=274966)

Cashflow

	F	G	H	I	L	M	N	O
31	Betrag (Brutto)	Start	Ende		Mai 14	Juni 14	Juli 14	August 14
32	7 899,50 €	15.05.2014	18.08.2014		1 398,87 €	2 468,59 €	2 550,88 €	1 481,16 €

Zelle	Formel
L31	=DATUM($J;SPALTE(A31)+4;1)
M31	=DATUM($J;SPALTE(B31)+4;1)
N31	=DATUM($J;SPALTE(C31)+4;1)
O31	=DATUM($J;SPALTE(D31)+4;1)
F32	=D32*$C*(1+$E)+D32*$C*(1+$E)
L32	=RUNDEN(($F32/($H32-$G32+1)*ABRUNDEN(SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($G32&":"&$H32)))=JAHR(L))*(MONAT(ZEILE(INDIREKT($G32&":"&$H32)))=MONAT(L))*1);0));9)
M32	=RUNDEN(($F32/($H32-$G32+1)*ABRUNDEN(SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($G32&":"&$H32)))=JAHR(M))*(MONAT(ZEILE(INDIREKT($G32&":"&$H32)))=MONAT(M))*1);0));9)
N32	=RUNDEN(($F32/($H32-$G32+1)*ABRUNDEN(SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($G32&":"&$H32)))=JAHR(N))*(MONAT(ZEILE(INDIREKT($G32&":"&$H32)))=MONAT(N))*1);0));9)
O32	=RUNDEN(($F32/($H32-$G32+1)*ABRUNDEN(SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($G32&":"&$H32)))=JAHR(O))*(MONAT(ZEILE(INDIREKT($G32&":"&$H32)))=MONAT(O))*1);0));9)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

(Wobei die Formel hier natürlich so nicht funktioniert und nur als Platzhalter dient)

Da ich mich in der Formel leider nicht so gut auskenne, würde ich mich sehr über einen Tipp freuen! =)

Beste Grüße,
Martin

:)

 

Hi Martin,
vielleicht hilft dir schon das hier weiter:
http://www.ms-office-forum.net/forum...d.php?t=312258

ABRUNDEN, INDIREKT, SUMMENPRODUKT, ... - wird alles nicht gebraucht!

Wie kommst du auf den Parameter 13 bei der KALENDERWOCHE? 21 hätte mich nicht gewundert...

Was bei der KW nicht unwichtig ist: Ohne Angabe des KW-Jahres ist die KW nur die Hälfte wert.

 

Hallo Martin,

wenn Du die Monatsberechnung auf Kalenderwochenberechnung umstellen willst, dann musst Du konsequenterweise auch die Formel MONAT auf KALENDERWOCHE ändern (und natürlich die dann dazugehörigen Parameter setzen)!

Formel für AL5:

PHP:

         =RUNDEN(($R5/($AH5-$AG5+1)*ABRUNDEN(SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($AG5&":"&$AH5)))=JAHR(AL$3))*(KALENDERWOCHE(ZEILE(INDIREKT($AG5&":"&$AH5));13)=KALENDERWOCHE(AL$3;13))*1);0));9)


 

Hallo Erich,

vielen Dank für den Link! Ich habe versucht, es auf mein Formular zu implementieren - aber die 4.000,00 Euro werden auf 3.996,00 Euro aufgeteilt.
Hab ich irgendwo bei den Formeln einen Fehler eingebaut?

Kalkulation

	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX
2				Wo-Beg	Vj
3						01.01.2014	08.01.2014	15.01.2014	22.01.2014	29.01.2014	05.02.2014	12.02.2014	19.02.2014	26.02.2014	05.03.2014	12.03.2014	19.03.2014	26.03.2014
4						1	2	3	4	5	6	7	8	9	10	11	12	13
5	2014	01.01.2014	20.03.2014	23.12.2013	#NV	253,00 €	354,00 €	354,00 €	354,00 €	354,00 €	354,00 €	354,00 €	354,00 €	354,00 €	354,00 €	354,00 €	203,00 €	-   €
6						354,4303797	354,4303797	354,4303797	354,4303797	354,4303797	354,4303797	354,4303797	354,4303797	354,4303797	354,4303797	354,4303797	101,2658228	0

Zelle	Formel
AL4	=KALENDERWOCHE(AL3;21)
AL5	=RUNDEN($AF5/($AI5+1-$AH5)*MAX(;MIN($AJ5+7*(AL+1);$AI5+1;1&-($AG5+1))-MAX($AJ5+7*AL;$AH5;1&-$AG5))

Excel Tabellen im Web darstellen >>

 

Hi Martin,
wenn die KWs variabel sind (und zu verschiedenen Jahren gehören können), muss man auch das KW-Jahr
mit angeben. Die Spalte "Jahr" kann entfallen.

	C	D	E	F	G	H
1			Wo-Beg	16.12.13	23.12.13	30.12.13
2	Menge	Start	Ende	2013-51	2013-52	2014-01
3	30000	20.12.2013	08.01.2015	233,766	545,455	545,455
4	4000	01.01.2014	20.03.2014			253
5	4000	01.01.2014	20.03.2014			253,16
6	8	22.12.2013	05.01.2014	1	4	4

Zelle	Formel
F1	=7*DM((7&1-LINKS(F2;4))/7+RECHTS(F2;2);)+177
G1	=F1+7
H1	=G1+7
F2	=JAHR(MIN(D3:D999)+3-REST(MIN(D3:D999)-2;7))&TEXT(KALENDERWOCHE(MIN(D3:D999);21);"-00")
G2	=JAHR(G1+3-REST(G1-2;7))&TEXT(KALENDERWOCHE(G1;21);"-00")
H2	=JAHR(H1+3-REST(H1-2;7))&TEXT(KALENDERWOCHE(H1;21);"-00")
F3	=RUNDEN($C3/($E3+1-$D3)*MAX(;MIN(F+7;$E3+1)-MAX(F;$D3));8)
G3	=RUNDEN($C3/($E3+1-$D3)*MAX(;MIN(G+7;$E3+1)-MAX(G;$D3));8)

Die erste KW in F2 wird berechnet - als erste benötigte KW.

Durch den Wegfall der Jahres-Begrenzung sind die Formeln etwas einfacher geworden.

Dass da in der Summe 3996 rauskommt, obwohl doch 4000 verteilt werden sollten,
liegt einzig und allein an der Rundung auf 0 Nachkommastellen bei jedem Einzelergebnis.
Mit verschiedenen Rundungen kannst du unterschiedlich gute Annäherungen an die "exakte" Summe erreichen.

Eine Mappe dazu habe ich hochgeladen.