Office: Umsatzberechnung auf Monate verteilen

Liebe Excel-Freunde,

ich bin verzweifelt auf der suche nach einer Formel, die mir meinen Umsatz automatisch auf eine Laufzeit, d.h. auf die entprechenden Monate aufteilt. Es geht hier um eine Werbekampagne die am 20.01.2017 startet und bis Ende März läuft. Jetzt möchte ich periodengerecht abrechnen und müsste daher automatisch den Umsatz auf die Monate verteilen.
Ich habe euch eine Beispieldatei angehängt, in der ich es manuell berechnet habe. Dies ist allerdings bei der Masse an Kampagnen, die ich berechnen muss nicht mehr möglich.

Ich danke Euch für Eure Hilfe.

Viele Grüße

Florian

:)

 

Hallo Florian,

das ist aufwendig, da das Datum als Text eingegeben wurde.
Ich betreibe nicht den Aufwand um aus dem Text ein Datum zu machen.

 

Hallo Florian,

probier mal folgendes
Code:

 

Hallo Florian,

das als Text eingegebene Datum, stellt kein wirklich großes Problem dar. Es reicht dieses dann mit WERT() zu umklammern, wenn man sowieso nicht schon damit rechnen will (dann geht es auch mit Direktansprache).
Daher hier eine Lösungsformel für F3:

PHP:

         =$D3/($C3-$B3)*MAX(0; MIN(WERT($C3);DATUM(WENN(F$1="Dez";LINKS($B3;4)+1;LINKS($B3;4));WENN(F$1="Dez";1;SPALTE()-4);1)-1) -MAX(WERT($B3);DATUM(LINKS($B3;4);SPALTE()-5;1)-1) )


 

Hallo Florian,

nur einmal als kleines Zwischenstatement möchte ich feststellen, dass deine Laufzeitberechnung um einen 1 Tag falsch ist. Wenn eine Kampagne vom 20. bis zum 31. Jan. läuft, dann umfasst diese 12 anstatt 11 Tage. Nur wenn du die Differenz zwischen den 2 Daten feststellen willst, dann ist 11 korrekt.

Zur Ermittlung der Laufzeit musst du somit immer noch eine 1 hinzu addieren.

 

hallo, ich würde so tun..:

Arbeitsblatt mit dem Namen 'Tabelle1'

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

*

Start

Ende

Umsatz

Laufzeit

Jan

Feb

Mrz

Apr

Mai

Jun

Jul

Aug

Sep

Okt

Nov

Dez

Gesamt

2

Werbekampagne ABC

2017-01-03

2017-01-16

25.000 €

14

25.000,00

*

*

*

*

*

*

*

*

*

*

*

25.000 €

3

Werbekampagne XYZ

2017-01-20

2017-03-31

45.000 €

71

7.714,29

18.000,00

19.285,71

*

*

*

*

*

*

*

*

*

45.000 €

Zelle	Formel
F2	=DM($D2/($C2-$B2)*MAX(0;MIN(MONATSENDE(("1."&F);0)+1;$C2+0)-MAX(("1."&F);$B2+0)))*1

Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

 

Hallo Frank,

irgendwie habe ich das Gefühl, dass Du von mir abgeschrieben hast *wink.gif*
Aber ich will das mal als Optimierung meiner Grundgedanken ansehen.
Ja, zugegeben eine nette Optimierung, mit dem Nachteil, dass nun alte Jahre nicht mehr betrachtet werden können. Denn wenn Du die Datei nun abspeicherst und mit dem Ergebnis hochzufrieden bist, so darfst Du die Datei in 2018 nicht mehr öffnen und berechnen, denn dann bist Du es ganz und gar nicht mehr. *uff*

Ob hier der Hinweis von WS-53 berücksichtigt werden muss oder nicht, oder Dein erster Tag mitgerechnet wird und dafür Dein letzter Tag nicht, kann letztendlich nur der TS entscheiden. Ich bin hier der Logik des TS gefolgt. Er hat ja seine Wunschergebnisse vorgegeben und wird wohl wissen warum das so sein muss.

LG Ingo

 

12.11.2006 als Beitrag (und kurz davor im Juli hier)

Jegliche "Zeit im Intervall" ist damit darstellbar.

 

hallo Ingo, abgeschrieben habe ich von dir sicher nicht... eher von lupo - aber da bin ich mir auch nicht sicher. weil ich mich eher selten (eigentlich gar nicht - bis auf einmal in letzter zeit auf seine seite(n) verirre {und da ging's um eingabe von zeiten ohne doppelpunkt. da habe ich aber (auch) eine andere formel)...

ob's derr ( @ lupo1) erfunden hat, weiß ich also nicht

 

Vielen Dank für Eure Antworten. Das hat aufjedenfall in meiener Beispieldatei schonmal geklappt.

Jetzt habe ich aber das Problem, dass ich diese Formal auf zwei Zellen anwenden muss, die in einer Pivottabelle stecken. D.h. es ist kein Textformat, sondern in der Tat ein Datumsformat. So bekomme ich leider nur einen fehler angezeigt.

Habt ihr hierfür auch eine Idee?
Danke für die Hilfe

 

Hallo Frank Furter,

ich versuche gerade die Formel zu verstehen. Muss gestehen, dass mir dies nch nicht so ganz gelungen ist. Aber ich habe dabei festgestellt, dass die Ergebnisse falsch sind.

Der Zeitraum 20.1.2017 - 31.3.2017 umfasst ja 71 Tage (12 / 28 / 31). Wenn du nun 71.000 anstatt 45.000 Euro als aufzuteilenden Betrag vorgibst, dann müsste ja das Ergebnis 12.000 / 28.000 / 31.000 sein. Die Formel ermittelt aber: 12.171,43 / 28.400 / 30428,57.

Der Fehler resultiert daraus, dass du zwar in Spalte E (Laufzeit) auf die Differenz 1 addierst, dies aber nicht in der Formel der Aufteilung berücksichtigst.

Es hat eine Weile gedauert bis ich es hinbekommen habe, denn der Endmonat muss abweichend behandlet werden, aber nun stimmen die Ergebnisse, wie du in der hochgeladenen Mappe sehen kannst.

Ich habe den Fehler aber auch nur entdeckt, weil ich die Formel so genial finde, und diese verstehen wollte. Am verstehen der Formel muss ich allerdings immer noch etwas arbeiten.

 

Hallo Ingo,

Ich gehe ganz stark davon aus, dass das Wunschergebnis nachlässig berechnet wurde und einfach falsch ist.

Denn wenn du von Montag bis Freitag Urlaub hast. dann ergibt sich daraus eine Tagesdifferenz von 4 Tage, du musst aber 5 Tage Urlaub einreichen.

Und os ist es auch mit Kampangnen. Die beginnen am Morgen des Ersten Tages und enden am Abend des letzten Tages. Ansosnten hätte ja eine Kampange, die von Heute bis Heute geht, eine Laufzeit von 0 Tagen.

 

So,

nachdem ich zuerst einmal damit zufrieden war, die etwas fehlerhafte Formel:

PHP:

         =DM($D7/($C7-$B7)*MAX(0;MIN(MONATSENDE(("1."&F$6);0)+1;$C7+0)-MAX(("1."&F$6);$B7+0)))*1