Office: (Office 2007) Tage und Betrag je Monat errechnen

Hallo zusammen,

Ich habe eine excel abelle mitrechnungen. Diese beinhalten eine summe und einem vertragsbeginn und ende. Nun möchte ich den betrag linear auf die monate verteilen. Das jedoch auch nur für das aktule jahr. Beispiel: rechnung 1 mit 500 euro und einer lwufzeit vom 01.01.11 bis zum 28.02.11. Hiervon benötige ich nun den preis je monat, aufgeteilt je nach anzahl der tage im monat. Läuft die rechnung nur bis zum 10.02.11, dann ist dementsprechend für den februar weniger zu verteilen. Auch gibt es rechnungen, die seit 2010 laufen, hier jedoch auch nur die laufzeit in 2011 beachtet werden darf. Gibt es hier eine entsprechende formel, die mir das ergebnis liefert oder hat jemand eine idee wie ich das lösen kann mit excel?

Vielen Dank.

Gruß

:)

 

Hallo Manuel,

Summe für ein Monat
leer wird als Januar interpretiert / Excel bzw. Billy spinnen
um dieses auszuschließen:
=SUMMENPRODUKT((A1:A100"")*(MONAT(A1:A100)=1)*B1:B100)
hier werden die Werte aus B addiert wenn in A der Monat Januar steht (egal, welches Jahr)
Mit Jahreseingrenzung auf 2003 so:
=SUMMENPRODUKT((A1:A100"")*(MONAT(A1:A100)=1)*(JAHR( A1:A100)=2003)*B1:B100)

 

Moin Manuel,

da ich Deinen Aufbau der Datei nicht kenne, habe ich mir erlaubt, (m)eine Beispielmappe zu erstellen.
Schau mal hier:
Beispiel

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	RgNr.	Betrag	von	bis		Jahr		Januar	Februar	März	April	Mai	Juni	Juli	August	September	Oktober	November	Dezember
2	1	500,00	01.01.2011	28.02.2011		2011		262,71	237,29	0	0	0	0	0	0	0	0	0	0
3	2	1.000,00	01.11.2010	15.04.2011				295,24	266,67	295,24	142,86	0	0	0	0	0	0	0	0
4	3	2.000,00	01.03.2011	31.03.2011				0	0	2000	0	0	0	0	0	0	0	0	0
5	4	3.000,00	15.04.2011	31.03.2012				0	0	0	183,91	356,32	344,83	356,32	356,32	344,83	356,32	344,83	356,32

Zelle	Formel
H1	=DATUM($F;SPALTE(A1);1)
H2	=RUNDEN(($B2/(DATEDIF(MAX(DATUM($F;1;1);$C2);MIN(DATUM($F;12;31);$D2);"D")+1)*ABRUNDEN(SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($C2&":"&$D2)))=JAHR(H))*(MONAT(ZEILE(INDIREKT($C2&":"&$D2)))=MONAT(H))*1);0));2)

Excel Tabellen im Web darstellen >>

 

Moin Manuel,

der Fehler liegt hier:
Code:

ändere mal BU2 in D ab, dann läuft die Formel wieder rund *wink.gif*

Code:

 

Hallo mücke,

vielen Dank für Deine Hilfe, genauso habe ich es mir vorgestellt *Smilie!

Eine Frage dazu habe ich aber noch... Grundlage ist eine Datei mit mehreren 1000 Datensätze. Jetzt kann es natürlich vorkommen, dass bestimmte Positionen keinen Betrag haben oder das Vertragsjahr vor / nach 2012 war. Als Ergebnis wird hier dann #BEZUG! bzw. #ZAHL! ausgegeben, was ja auch nicht falsch ist.

Jedoch möchte ich über das ganze eine Pivot legen um weitere Auswertungen zu erstellen. Jedoch berechnet er mir für die Monate nicht die Summen, wenn die oben genannte Inhalte enthalten sind. Gibt es eine Möglichkeit, diese zwei Meldungen auszublenden, bzw. zu deaktivieren?

Danke.

Gruß

 

Moin Manuel,

Danke für die nette Rückmeldung *Smilie

Mit einer kleinen Ergänzung sollten die Fehlermeldungen behoben sein.
Beispielmappe anbei.
Beispiel

	EL
2	262,71

Zelle	Formel
EL2	=WENN((JAHR($BU2)>$EH)+(JAHR($BW2)<$EH);0;RUNDEN(($CH2/(DATEDIF(MAX(DATUM($EH;1;1);$BU2);MIN(DATUM($EH;12;31);$BW2);"D")+1)*ABRUNDEN(SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($BU2&":"&$BW2)))=JAHR(EL))*(MONAT(ZEILE(INDIREKT($BU2&":"&$BW2)))=MONAT(EL))*1);0));2))

Excel Tabellen im Web darstellen >>

 

Moin,

die Formel hat mir auf jeden Fall schon mal den Tag gerettet.

Jetzt wird's komplizierter: Kann ich die Werte auch auf Kalenderwochen umrechnen?

Anwendungsbeispiel:
A2 Startdatum
B2 Enddatum
C2 Betrag
D1 Kalenderwoche (vermutlich erster Tag der KW)
D2 Anteil des Betrages, der auf die KW in D1 abfällt

DANKE für jede Hilfe!

 

Hallo,

ich habe ein ähnliches Problem. Ich möchte die genau wie die Ersteller Werte auf einen Zeitraum verteilen, jedoch nicht auf Monate sondern Tage . Wenn ich die oben genannte Formel benutze schreibt er bei alles in den Januar und ignoriert den jeweiligen Monat. Hat einer eine Idee was ich anpassen muss um auf die genauen Tage die Werte zu verteilen?

vielen Dank!

Gruß

gnar87