Office: Betrag auf Tage und Monate verteilen

Hallo Leute,

ich brauche da mal Unterstützung:

Ich habe einen Betrag, den ich auf einen Zeitraum verteilen muss.
Z.B. 1200 € auf den Zeitraum 01.01.2011 - 31.12.2011
Es gibt eine Tabelle mit den Monaten Jan. - Dez. 2011, jetzt soll in jedem Monat (in diesem Fall) 1/12 stehen.
Denkbar ist aber auch z.B. ein Zeitraum vom 01.04.2011 - 30.09.2011 in dem Fall muss der Betrag auf die 6 Monate verteilt werden und in der Tabelle muss in den vorherigen und nachfolgenden Monaten 0,00€ stehen.
Weiterhin wäre es natürlich auch möglich, dass es Teilmonate gibt, d.h. Zeiträume z.B. vom 18.02.2011 bis 17.05.2011. Die Verteilung sollte entsprechend den Tagen im Monat sein.

Könnt ihr mir weiterhelfen, wie die Formel aussehen muss, damit automatisch in jedem Monat ein Wert steht? Es muss natürlich auch so sein, dass nachdem der Betrag auf die Monate verteilt wurde, keine Differenz mehr übrig bleibt. Am besten wäre es, einen Betrag ohne Nachkommastellen pro Monat zu haben.

Habt ihr da eine Idee - ich würde mich echt riesig freuen.
Besten Dank schonmal im Voraus.

Grüße

Ammi

:)

 

hallo Ammi
schau mal hier, Formel von Muecke:
http://www.ms-office-forum.net/forum...d.php?t=274966
gruss hary

 

Hey Hary,

vielen Dank, aber bei mir funktioniert das irgendwie nicht. In dem Beispiel wird der Gesamtbetrag nur auf das aktuelle Jahr verteilt, ich benötige eine Verteilung auf die gesamte Laufzeit, auch wenn diese jahresübergreifend ist.

Bsp.:

Laufzeit: 15.08.2011 - 14.03.2012; 700 EUR

Jetzt muss in der Tabelle für 2011 in den Monaten August bis Dezember je 1/7 stehen und in der neuen Tabelle für 2012 in den Monaten Januar bis März ebenfalls.
Ganze Monate sollten mit 30 Tagen gerechnet werden, Teilmonate anteilig, kriegt man sowas hin?
Ich wäre dir echt dankbar, wenn du da eine Idee hättest, ich tüfftele schon seit 2 Tagen *frown.gif*

Grüße

Ammi

 

Moin Ammi,

die vollen Monate werden mit 30 Tagen gerechnet, anteilige Monate werden anteilig zu 30 Tagen gerechnet.
Da ich Deine Struktur nicht kenne, schau mal ob es so passt. Beispielmappe anbei.
Beispiel

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

von

bis

Betrag

Jan 2011

Feb 2011

Mrz 2011

Apr 2011

Mai 2011

Jun 2011

Jul 2011

Aug 2011

Sep 2011

Okt 2011

Nov 2011

Dez 2011

Jan 2012

Feb 2012

Mrz 2012

Apr 2012

2

15.02.2011

14.03.2012

700,00

0,00

28,72

53,85

53,85

53,85

53,85

53,85

53,85

53,85

53,85

53,85

53,85

53,85

53,85

25,13

0,00

Zelle	Formel
D1	=DATUM(2011;SPALTE(A1);1)
D2	=$C2/(TAGE360($A2;$B2;1)+1)*WENN(SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($A2&":"&$B2)))=JAHR(D))*(MONAT(ZEILE(INDIREKT($A2&":"&$B2)))=MONAT(D))*1)=TAG(MONATSENDE(D;0));30;WENN((JAHR(ZEILE(INDIREKT($A2&":"&$B2)))=JAHR(D))*(MONAT(ZEILE(INDIREKT($A2&":"&$B2)))=MONAT(D))=1;SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($A2&":"&$B2)))=JAHR(D))*(MONAT(ZEILE(INDIREKT($A2&":"&$B2)))=MONAT(D))*1)+MAX(0;30-(TAG(MONATSENDE(D;0))));SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($A2&":"&$B2)))=JAHR(D))*(MONAT(ZEILE(INDIREKT($A2&":"&$B2)))=MONAT(D))*1)))

Excel Tabellen im Web darstellen >>

 

Moin Dirk
Hab es doch gewusst, dass Du darauf reagierst. ;-))
Sehr schoen.
gruss hary

 

Hi Dirk,
hi Hary,

Hammer Formel, nicht schlecht *Smilie
Ich teste die Formel morgen mal aus, sage dir dann Bescheid, jetzt schonmal vielen Dank.

Grüße

Ammi

 

Hey Dirk,

die Formel macht in der Regel schon das, was ich will, nur ich möchte in einer Tabelle die zu ermittelnden Felder als Vorlage fest hinterlegen. D.h. wenn jemand den Betrag und die Laufzeit eingibt, dann soll pro Monat der zu verteilende Betrag automatisch ermittelt werden. Jedoch habe ich in der Vorlage die Felder mit Betrag und Laufzeit noch nicht befüllt, diese sollen eingegeben werden.
Wenn ich jetzt die Formel rüberkopiere, damit bei Eingabe des Betrages und der Laufzeit die Werte automatisch verteilt werden, kommt #Bezug!, kann man diese Meldung unterdrücken? Wenn ja, wie sieht dann die Formel aus? Kannst du mir da auch noch helfen?

Danke.

Greetzzzz

Ammi

 

Moin Ammi,

mit WENNFEHLER(...;"") bekommst Du die (alle) Fehlermeldung(en) eliminiert, schau mal hier:
Beispiel

	A	B	C	D
1	von	bis	Betrag	Jan 2011
2	15.02.2011

Zelle	Formel
D1	=DATUM(2011;SPALTE(A1);1)
D2	=WENNFEHLER($C2/(TAGE360($A2;$B2;1)+1)*WENN(SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($A2&":"&$B2)))=JAHR(D))*(MONAT(ZEILE(INDIREKT($A2&":"&$B2)))=MONAT(D))*1)=TAG(MONATSENDE(D;0));30;WENN((JAHR(ZEILE(INDIREKT($A2&":"&$B2)))=JAHR(D))*(MONAT(ZEILE(INDIREKT($A2&":"&$B2)))=MONAT(D))=1;SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($A2&":"&$B2)))=JAHR(D))*(MONAT(ZEILE(INDIREKT($A2&":"&$B2)))=MONAT(D))*1)+MAX(0;30-(TAG(MONATSENDE(D;0))));SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($A2&":"&$B2)))=JAHR(D))*(MONAT(ZEILE(INDIREKT($A2&":"&$B2)))=MONAT(D))*1)));"")

Excel Tabellen im Web darstellen >>

 

Hallo Dirk
Erstmal *kniefall* ;-)
Waere es nicht besser das Jahr mit Min aus SpalteA zu holen? Denn wenn erst im Jahr '12 begonnen wird, faengt es trotzdembei '11 an.
verbessere mich ruhig.

Tabellenblattname: Tabelle1

	A	B	C	D
1				01.01.2012
2	01.01.2012	14.03.2012	1000,00
3	16.02.2013	15.04.2014	1000

Benutzte Formeln:
D1: =DATUM(MIN(JAHR($A$2:$A$3));SPALTE(A1);1)
Matrixformel Strg+Shift+Enter
gruss hary

 

Guten Morgen hary!

Erst mal Danke für die Blumen, da werd ich ja ganz *redface

Dein Hinweis/Einwand bzgl. Beginn der Zeitreihe ist gerechtfertigt und eine sehr schöne Erweiterung, Danke! *hands
Um die MATRIX-Formel (Strg+Shift+Enter) zu umgehen, habe ich mal eine Variante über SVERWEIS gebastelt, hier wird das Jahr und der Monat berücksichtigt. (wenn schon, denn schon *biggrin.gif* )
Beispiel

	A	B	C	D	E
1	von	bis	Betrag	Apr 1999	Mai 1999
2	15.02.2011
3	04.04.1999
4	02.10.2012

Zelle	Formel
D1	=DATUM(JAHR(SVERWEIS(MIN($A:$A);$A:$A;1;FALSCH));MONAT(SVERWEIS(MIN($A:$A);$A:$A;1;FALSCH))+SPALTE(A1)-1;1)

Excel Tabellen im Web darstellen >>

 

Hallo Dirk,

kannst du dir das nocheinmal angucken, wenn ich deine Tabelle nehme und einen Zeitraum vom 05.05.2011 bis 16.12.2011 mit 10.000 € ansetze, wird nach Verteilung ein Gesamtbetrag i.H.v. 10.045,05 verteilt, kann das sein oder mache ich da was falsch?

Grüße

Ammi

 

Moin Ammi,

nein, Du machst nichts falsch, ist mein Fehler, sorry.
Ändere die Formel mal wie folgt ab, dann sollte es keine Differenzen mehr geben.
...+MAX(-1

 

Hi Leute,

besten Dank, so wie es aussieht, funktioniert alles perfekt.
Falls noch eine kleine Optimierung notwendig sein sollte, würde ich gerne nocheinmal auf euch zukommen.

Danke.

Grüße

 

\@mücke: bin gerade auch dabei, deine formel bei mir einzubauen. allerdings habe ich das problem, dass bei uns das datum immer nur als voller monat angegeben wird, also z.b. nur April 2011. Ich möchte dass die Formel genau die gleichen Sachen berechnet wie bisher, nur alle Monate ganz zählt (d.h. alle Monate haben 30 Tage und werden immer nur als ganze Monate gezählt, auch wenn da 1.4.2011 oder 10.4.2011 steht)
Weißt du was ich meine?

 

Moin ben,

meinst Du so?
Beispiel

	A	B	C	D	E	F	G	H	I	J	K
1	von	bis	Betrag	Aug 2011	Sep 2011	Okt 2011	Nov 2011	Dez 2011	Jan 2012	Feb 2012	Mrz 2012
2	15.08.2011	01.10.2011	1.500,00	500,00	500,00	500,00
3	22.09.2011	30.09.2011	200,00		200,00
4	31.10.2011	22.02.2012	750,00			150,00	150,00	150,00	150,00	150,00

Zelle	Formel
D1	=DATUM(JAHR(SVERWEIS(MIN($A:$A);$A:$A;1;FALSCH));MONAT(SVERWEIS(MIN($A:$A);$A:$A;1;FALSCH))+SPALTE(A1)-1;1)
D2	=WENN((D>=DATUM(JAHR($A2);MONAT($A2);))*(D<=$B2);RUNDEN($C2/((JAHR($B2)-JAHR($A2))*12+MONAT($B2)-MONAT($A2)+1);2);"")

Excel Tabellen im Web darstellen >>