Office: Betrag auf Tage und Monate verteilen

Helfe beim Thema Betrag auf Tage und Monate verteilen in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; 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... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von Ammi, 25. Februar 2011.

  1. 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

    :)
     
  2. 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
     
  3. OfficeUser Neuer User

    Betrag auf Tage und Monate verteilen

    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

     ABCDEFGHIJKLMNOPQRS
    1vonbisBetragJan 2011Feb 2011Mrz 2011Apr 2011Mai 2011Jun 2011Jul 2011Aug 2011Sep 2011Okt 2011Nov 2011Dez 2011Jan 2012Feb 2012Mrz 2012Apr 2012
    215.02.201114.03.2012700,000,0028,7253,8553,8553,8553,8553,8553,8553,8553,8553,8553,8553,8553,8525,130,00
    ZelleFormel
    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 >>
     
    Zuletzt von einem Moderator bearbeitet: 12. Januar 2021
    OfficeUser, 28. Februar 2011
    #4
  4. Moin Dirk
    Hab es doch gewusst, dass Du darauf reagierst. ;-))
    Sehr schoen.
    gruss hary
     
  5. 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
     
  6. 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
     
  7. OfficeUser Neuer User

    Betrag auf Tage und Monate verteilen

    Moin Ammi,

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

     ABCD
    1vonbisBetragJan 2011
    215.02.2011   
    ZelleFormel
    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 >>
     
    Zuletzt von einem Moderator bearbeitet: 12. Januar 2021
    OfficeUser, 1. März 2011
    #8
  8. OfficeUser Neuer User
    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
     
    OfficeUser, 1. März 2011
    #9
  9. OfficeUser Neuer User
    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

     ABCDE
    1vonbisBetragApr 1999Mai 1999
    215.02.2011    
    304.04.1999    
    402.10.2012    
    ZelleFormel
    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 >>
     
    Zuletzt von einem Moderator bearbeitet: 12. Januar 2021
    OfficeUser, 1. März 2011
    #10
  10. 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
     
  11. 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
     
    mücke, 2. März 2011
    #12
  12. Betrag auf Tage und Monate verteilen

    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
     
  13. \@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?
     
  14. OfficeUser Neuer User
    Moin ben,

    meinst Du so?
    Beispiel

     ABCDEFGHIJK
    1vonbisBetragAug 2011Sep 2011Okt 2011Nov 2011Dez 2011Jan 2012Feb 2012Mrz 2012
    215.08.201101.10.20111.500,00500,00500,00500,00     
    322.09.201130.09.2011200,00 200,00      
    431.10.201122.02.2012750,00  150,00150,00150,00150,00150,00 
    ZelleFormel
    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 >>
     
    Zuletzt von einem Moderator bearbeitet: 12. Januar 2021
    OfficeUser, 12. September 2011
    #15
Thema:

Betrag auf Tage und Monate verteilen

Die Seite wird geladen...
  1. Betrag auf Tage und Monate verteilen - Similar Threads - Betrag Tage Monate

  2. Beträge zur gleichen ID zusammenfassen

    in Microsoft Excel Hilfe
    Beträge zur gleichen ID zusammenfassen: Hallo zusammen, ich habe eine Tabelle mit zwei Spalten. 1. Spalte: Kunden_ID 2. Spalte: Einkaufsbetrag In der Tabelle werden alle Einkäufe pro Woche dargestellt. Nun kann es vorkommen, dass...
  3. Beträge automatisch teilen

    in Microsoft Excel Hilfe
    Beträge automatisch teilen: Hallo zusammen, ich bin leider noch nicht so der Excel Profi und versuche mich immer mehr in die tiefen einzufinden. Im Moment erstelle ich eine Tabelle und dabei bin ich auf ein Problem...
  4. AfA-Beträge pro Jahr bei degrAfA mit Wechsel zu linAfA

    in Microsoft Excel Tutorials
    AfA-Beträge pro Jahr bei degrAfA mit Wechsel zu linAfA: A2: AH-Datum (bei Anschaffung am 1. eines Monats zählt der Monat, sonst erst der nächste. Somit ist für Quartals- oder Halbjahresregelungen der Quartals- oder Halbjahresbeginn einzutragen.) B2:...
  5. Beträge gleichmäßig auf 2 Konten verteilen

    in Microsoft Excel Hilfe
    Beträge gleichmäßig auf 2 Konten verteilen: Hallo, in meiner Exceltabelle habe ich in Spalte A die Bezeichnungen der Abbuchungskosten stehen. In Spalte C steht die Jahressumme der zugehörigen Abbuchungsbeträge. Nun möchte ich, dass die...
  6. Tage und Betrag je Monat errechnen

    in Microsoft Excel Hilfe
    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...
  7. IST/PLAN Beträge umsetzen

    in Microsoft Access Hilfe
    IST/PLAN Beträge umsetzen: Hi, da mir hier schon so gut geholfen wurde würde ich gern noch ein weiteres Problem mit euch besprechen. Ich hoffe bekomme es gut erklärt. Und zwar Habe ich eine Komplettübersicht welche einige...
  8. Word Userform Beträge addieren

    in Microsoft Word Hilfe
    Word Userform Beträge addieren: Moin. Bin in meiner Userform an einem Knackpunkt angelangt: Ich habe u.a. 4 Optionsbutton, in einem Frame zusammengefasst. Hinter jedem OB liegen je 2 Werte, die abhänging von einer Anzahl...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden