Opgelost Factuurbedrag over perioden verdelen

[aandemaas]

Goedemorgen, ik wil graag in excel berekenen wat de omzet is verdeeld over de perioden, waarbij de eerste maand het bedrag pro rata gaat, de rest van de maanden het maandbedrag (bij een jaarabonnement gedeeld door 12, kwartaalabonnement gedeeld door 3 en maandabonnementen komen ook voor) en de laatste maand het restbedrag. Ik wil dit weten voor de maanden vallend in 2024 en 2025.

En ik wil eenzelfde tabel waarin de omzet wordt verdeeld in gelijke delen over de maanden die in de abonnementsperiode vallen

Ik heb het nu handmatig in het sheet gezet, maar ik wil dit automatiseren (die twee bovenstaande tabellen) voor een heleboel data.

Bijgaand een voorbeeld bestand.

Ik hoop dat iemand mij kan helpen <3

 

[AHulpje]

Ik kom op andere bedragen voor de eerste en laatste maand.

Aantal hele maanden:

=(JAAR(D4)-JAAR(C4))*12+MAAND(D4)-MAAND(C4)-1

Deel van eerste maand:

=(LAATSTE.DAG(MAAND(C4);0)-DAG(C4)+1)/LAATSTE.DAG(MAAND(C4);0)

Deel van laatste maand:

=DAG(D4)/LAATSTE.DAG(MAAND(C4);0)

 

[aandemaas]

Bedankt voor het meedenken: het klopt inderdaad dat de eerste maand 17 dagen heeft, want de eerste dag telt ook mee. De laatste maand moet wel het restant zijn, want de som van de bedragen moet uitkomen op het totaalbedrag (anders krijg je afrondingen)
Dus met de tweede formule kan het sheet geautomatiseerd worden, maar hoe moet de formule in zijn geheel worden? Ik heb waarschijnlijk wel 500.000 regels welke op deze manier berekend moet worden.

Aangepast voorbeeld bestand bijgevoegd.

 

[AHulpje]

Heb een uitwerking toegevoegd aan #2. Maar met 500.000 rijen en bijvoorbeeld gemiddeld 12 maanden zit je dan aan 6.000.000 best lange formules. Misschien is een VBA oplossing dan wel beter, dan sla je alleen de bedragen op.

 

[aandemaas]

Ik ben zelf niet bekend met VBA, gaat dat ook in excel? En wat moet de inhoud van VBA dan zijn?
Ik heb een collega op development, dus die kan mij daar wel mee helpen als ik weet wat de inhoud van de VBA moet zijn.

 

[aandemaas]

Heb een uitwerking toegevoegd aan #2. Maar met 500.000 rijen en bijvoorbeeld gemiddeld 12 maanden zit je dan aan 6.000.000 best lange formules. Misschien is een VBA oplossing dan wel beter, dan sla je alleen de bedragen op.

Oh wauw, dit werkt al wel goed. Ik heb misschien wel minder dan 500.000 regels, dus misschien lukt het wel.

Ik heb ook nog de tweede tabel nodig. Dit is de manier waarop het boekhoudprogramma het berekend had :-| Dit moet ik dus corrigeren.

 

[aandemaas]

Ik heb even gekeken en ik heb ongeveer 20.000 facturen x gemiddeld 10 regels (aan de hoge kant geschat), dus misschien 200.000 regels?

 

[AHulpje]

Met 200.000 rijen wordt je bestand met formules onhandelbaar groot en tergend traag, zo'n 180 Mb. Een macro lijkt de aangewezen weg. Daarbij moet je wel aangeven of het bedrag in kolom B een jaar-, kwartaal- of maandbedrag is.

 

[aandemaas]

Goedemorgen, ik ben iets verder met het excelsheet gegaan. Zie bijgaand voorbeeld. Ik ga kijken of ik de maandbedragen uit de factuurregels kan halen, zodat ik niet hoef aan te geven of het jaar-, kwartaal- of maandbedragen zijn, zodat ik geen afrondingsverschillen krijg en anders is het maar zo. De bedragen die afwijken zijn dan niet materiaal en dan kan ik toch een correctieboeking maken.

@AHulpje In jouw laatste bericht gaf je aan dat een macro de aangewezen weg is. Kun jij aangeven hoe ik dit zou kunnen maken?

 

[cow18]

je bestandje met een macro

 

[aandemaas]

Heel erg bedankt. Zelfs mijn afronding is opgelost. Ik ga morgen kijken of ik het echte bestand erin kan krijgen en of het dan nog doorloopt.

Heel erg bedankt en prettig weekend nog!

 

[cow18]

nog iets meer hufterproof gemaakt

 

[aandemaas]

Top! Kan ik nu kolommen en rijen toevoegen, zonder dat de macro niet meer werkt?

 

[cow18]

ja, maar ik heb toch nog even een aanpassing gedaan voor je gemak.
Nu staan de 2 berekeningen naast elkaar met als 3e blok het verschil, dan kan je gemakkelijker controleren en vergelijken.
Kolommen A:E = je gegevens

geel bereik = gedefinieerde naam met je datums (reeks van x maanden met telkens de 1e), die mag je uitbreiden of inkrimpen, maar speel anders op veilig zodat je niet zoiets tegenkomt zoals rijen 20:21, dat je even buiten die periode stapt. Je moet enkel het gele bereik aanpassen (ook in de gedefinieerde naam), de 2 blokken ernaast worden in de macro aangepast.
In kolom F:G mag je geen oranje VO-cellen zien.

 

[aandemaas]

Helemaal top! Ik heb ook negatieve bedragen en daar gaat de macro nog niet goed mee om. En ik heb geprobeerd om kolommen toe te voegen aan de linker kolom en dan werkt de macro ook niet meer.
Zou jij een aantal (20?) lege kolommen willen toevoegen, die kan ik altijd verbergen als ik ze niet nodig heb. En kun jij kijken naar de berekening van negatieve bedragen?

 

[cow18]

nu doet hij ook de negatieve getallen en kiest hij zelf zijn datums (zolang dat dat niet meer dan 50 maand is, dus +4 jaar)

 

[aandemaas]

Dankjewel. Ik heb nu de data erin gezet, maar het excelsheet blijft "wielen". Het zijn 40.000 rijen. Misschien een beetje groot.
Bijgaand het bestand.

 

[AHulpje]

Met 41606 rijen en deze voortgang kan het inderdaad wel even duren, ieder stapje van 1000 rijen duurt weer een paar seconden langer:

 1000         19:12:28
 2000         19:12:31
 3000         19:12:34
 4000         19:12:38
 5000         19:12:43
 6000         19:12:50
 7000         19:13:00
 8000         19:13:13
 9000         19:13:31
 10000        19:13:53
 11000        19:14:21
 12000        19:14:52
 13000        19:15:28
 14000        19:16:08
 15000        19:16:53
 16000        19:17:46

Even een beetje zitten rekenen en extrapoleren, zou op mijn PC wel eens meer dan een uur kunnen duren.
En zo zou je de voortgang kunnen volgen:

          For iR = Rij + 1 To iL
               If iR Mod 1000 = 0 Then
                   Application.StatusBar = iR
                   DoEvents
                   Debug.Print iR, Time()
               End If

 

[peter59]

Ik volg dit topic met belangstelling.
Wat mij opvalt is dat er bij alle voorbeelden van @AHulpje en @cow18 verschillen zitten.
Zelf een kleine poging ondernomen met power query voor alleen het stukje pro rata.
Hier verschillen ook weer de bedragen met alle voorbeelden.
Waar zitten de verschillen in de berekeningen? Ik zie ze helaas niet.
M.a.w. zijn alle voorbeelden betrouwbaar?

 

[cow18]

ruw geschat zou dat moeten kunnen in 6-7 minuten ...
foutje, het werd 10 minuten, maar er zitten nog wat afrondingsfoutjes in en ik wou nog even aftoetsen met de resultaten van Peter59.
oei, ik zit aan 1.7 MB ....
dus maar 33K rijen