Microsoft Excel

Herbers Excel/VBA-Archiv

mtl Zahlung auf die Jahre verteilen und die | Herbers Excel-Forum


Betrifft: mtl Zahlung auf die Jahre verteilen und die von: Jörg
Geschrieben am: 11.12.2009 12:36:35

Hallo Forum,

ich möchte eine monatliche Zahlung auf die Jahre verteilen und die Jahressumme ausweisen.
Gegeben sind
der Beginn(z.B. 01.05.2010)
die Anzahl der Monate (24)
und der monatliche Betrag (10)

Zelle D1: 2010 in D2 soll 80 (8*10) stehen
Zelle E1: 2011 in E2 soll 120 (12*10) stehen
Zelle F1: 2012 in F2 soll 40 (4*10) stehen

Weiß jemand eine passende Excelfomel?
Ciao Jörg

  

Betrifft: AW: mtl Zahlung auf die Jahre verteilen und die von: Mike
Geschrieben am: 11.12.2009 12:45:42


Hey Jörg,

mal auf die Schnelle so:


Tabelle1

 BCDEF
1Beginn01.05.2010201020112012
2Monate24     
3Betrag108012040
4         
5relevante Monate  8124
6Rest  164 

verwendete Formeln
Zelle Formel
D1 =JAHR(C1)
E1 =D1+1
F1 =E1+1
D3 =D5*$C3
E3 =E5*$C3
F3 =F5*$C3
D5 =12-MONAT(C1)+1
E5 =WENN(D6>=12; 12; D6)
F5 =WENN(E6>=12; 12; E6)
D6 =C2-D5
E6 =D6-E5

Tabellendarstellung in Foren Version 4.28



Bin aber fest davon überzeugt, dass die Formelgurus dies noch massiv kürzen werden .. ;-)

Gruss
Mike


  

Betrifft: AW: mtl Zahlung auf die Jahre verteilen und die von: Jörg
Geschrieben am: 11.12.2009 13:32:35

Hallo Mike,

besten Dank für die schnelle Antwort - habe das ganze übernommen und getestet und dabei sind noch 2 Fragen aufgetaucht (die meine bisherige Fragestellung allerdings nicht hergaben):
a) Laufzeit = 2 Monate (D5 geht momentan immer von den Restlaufzeiten bis Ende des Jahres aus)
b) ich werde später in den Zeilen verschiedene Startjahre haben z.B. 01.05.2011 statt 2010,
d.h. die Jahre in D1 ff. sind fest definiert und die Formel müsste flexibler sein.
Kannst Du mir hier auch weiterhelfen?

Ciao Jörg


  

Betrifft: unterjährig .. gerechnet von: Mike
Geschrieben am: 11.12.2009 13:45:27


Hey Jörg,

a) Unterjährige Laufzeiten solltest Du mit Formel abfangen
b) D1 ist gerechnet und passt sich auf die Eingabe in C1 an.

Gruss
Mike


  

Betrifft: hier mein Versuch. von: Tino
Geschrieben am: 11.12.2009 13:38:28

Hallo,

https://www.herber.de/bbs/user/66524.xls

Gruß Tino


  

Betrifft: AW: hier mein Versuch. von: Jörg
Geschrieben am: 11.12.2009 15:13:03

Hallo Tino,

Danke für Deine Datei, leider konnte ich Sie nicht 100% nutzen.
Gescheitert bin ich ich daran, daß ich später in den Zeilen verschiedene Startjahre haben z.B. 01.05.2011 statt 2010. Die Jahre müssten also fix sein und wenn ich die Jahresformeln durch die Werte ersetze liefern Deine Formel für den 01.05.2011 nicht mehr die korrekten Werte.

Jockels Ansatz war genau der richtige für mich. Nochmals besten Dank,
ciao Jörg


  

Betrifft: mein senf... von: Jockel
Geschrieben am: 11.12.2009 14:41:12

... namensvetter, zu der sache..:

Tabelle2

 ABCDEFGHIJ
1Zeitraum     2010201120122013201420152016
2von bis   8012040       
301.05.201001.05.2012               
424Monate               
510Betrag               

verwendete Formeln
Zelle Formel
D1 =JAHR($A$3)
E1 =D1+1
F1 =E1+1
G1 =F1+1
H1 =G1+1
I1 =H1+1
J1 =I1+1
D2 =WENN(D1>JAHR($B$3);"";RUNDEN(MAX(;MIN($B3;DATUM(D$1;12;31))-MAX(DATUM(D$1;1;1);$A3)+1)/30;0)*$A$5)
E2 =WENN(E1>JAHR($B$3);"";RUNDEN(MAX(;MIN($B3;DATUM(E$1;12;31))-MAX(DATUM(E$1;1;1);$A3)+1)/30;0)*$A$5)
F2 =WENN(F1>JAHR($B$3);"";RUNDEN(MAX(;MIN($B3;DATUM(F$1;12;31))-MAX(DATUM(F$1;1;1);$A3)+1)/30;0)*$A$5)
G2 =WENN(G1>JAHR($B$3);"";RUNDEN(MAX(;MIN($B3;DATUM(G$1;12;31))-MAX(DATUM(G$1;1;1);$A3)+1)/30;0)*$A$5)
H2 =WENN(H1>JAHR($B$3);"";RUNDEN(MAX(;MIN($B3;DATUM(H$1;12;31))-MAX(DATUM(H$1;1;1);$A3)+1)/30;0)*$A$5)
I2 =WENN(I1>JAHR($B$3);"";RUNDEN(MAX(;MIN($B3;DATUM(I$1;12;31))-MAX(DATUM(I$1;1;1);$A3)+1)/30;0)*$A$5)
J2 =WENN(J1>JAHR($B$3);"";RUNDEN(MAX(;MIN($B3;DATUM(J$1;12;31))-MAX(DATUM(J$1;1;1);$A3)+1)/30;0)*$A$5)
B3 =DATUM(JAHR(A3);MONAT(A3)+A4;TAG(A3))

Tabellendarstellung in Foren Version 4.19



cu jörg
ein feedback wär ganz reizend


  

Betrifft: AW: mein senf... von: Jörg
Geschrieben am: 11.12.2009 15:02:19

Hallo Jockel,

das war's, was ich suchte, lediglich die Jahre habe ich noch unabhängig vom Eingabefeld A3 gemacht.

Besten Dank!
Ciao Jörg


  

Betrifft: AW: mtl Zahlung auf die Jahre verteilen und die von: Josef Ehrensberger
Geschrieben am: 11.12.2009 15:05:50

Hallo Jörg,

mein Versuch.

Tabelle1

 ABCDEFGHIJKLM
1StartMonateBetrag2009201020112012201320142015201620172018
201.05.200924108012040       
301.07.2010481500 90001800018000180009000    
419.03.201064500 500060006000600060003000   

Formeln der Tabelle
ZelleFormel
D2=SUMMENPRODUKT((JAHR(DATUM(JAHR($A2); MONAT($A2)+ZEILE(INDIREKT("1:"&$B2))-1;1))=D$1)*($C2))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4




Gruß Sepp



  

Betrifft: einfach (und) klasse... von: Jockel
Geschrieben am: 11.12.2009 15:22:58

hi Sepp, super lösung... ;-) cu jörg


  

Betrifft: AW: mtl Zahlung auf die Jahre verteilen und die von: Jörg
Geschrieben am: 12.12.2009 12:32:14

Hallo Sepp,

habe zum Glück nochmal ins Forum reingeschaut - echt genial Deine Lösung, werde sie übernehmen.
Besten Dank uns schönes Wochenende,
ciao Jörg


Beiträge aus den Excel-Beispielen zum Thema "mtl Zahlung auf die Jahre verteilen und die"