Excel: positieve waardes doorschuiven naar negatieve cellen

[g0tanks]

In een Excel bereken ik de uren waarmee ik drie verschillende diensten (Shift 1 t/m 3) kan vullen. Shift 1 heeft vaak uren over die ik ook kan inzetten voor Shift 2 en 3. In een oogopslag is vaak te zien of dat past, maar ik wil die excercitie automatiseren.

Een randvoorwaarde is dat Shift 1 minder waard is dan Shift 2 en 3 met een factor 2. Met andere woorden: 1 uur doorschuiven van Shift 1 levert maar 0,5 uur op in Shift 2 of 3. Een aantal voorbeelden ter illustratie:

Voorbeeld 1: Uit de eerste berekening komt dat Shift 1 +30 uren over heeft en Shift 2 en 3 resp. -10 en -5 te kort komen (B4 t/m D4). Met een factor 2 (in D1) heeft Shift 1 precies genoeg om Shift 2 en Shift 3 te vullen. Regel 5 bevat hulpcellen en geven de delta's aan door verschuiving. Het resultaat (B6 t/m D6) is netto nul.


Voorbeeld 2: Als Shift 1 maar +10 uren over heeft is dat slechts voldoende om een deel van Shift 2 te vullen.


Voorbeeld 3: Maar stel de factor wordt 0,10 dan past het makkelijk en houden we over.


Voorbeeld 4: Of als Shift 2 ook ineens +10 over heeft past het.


Ik heb dit nu opgelost met in C5 de formule:

code:

=IF(AND(B4>0;C4<0);IF(ABS(C4)<B4/D1;C4;-B4/D1);0)

En in D5:

code:

=IF(D4<0;IF(IF(B4/D1+C5>0;(B4/D1+C5)*-1;0)<D4;D4;IF(B4/D1+C5>0;(B4/D1+C5)*-1;0));0)

Dat werkt prima, maar is verre van elegant en is niet makkelijk uit te breiden voor extra shifts. Mijn vraag is of jullie nog creatieve oplossingen weten, los van VBA gebruiken. Ik zit er te dicht op en kan wel een frisse blik gebruiken.

[siddler]

Wat zijn je eisen aan het uitbreidbaar maken? Gaan er alleen uren van shift 1 naar shift x overgeboekt worden? Of wil je de flexibiliteit om bijv van shift 3 naar shift 1 over te boeken?
En maakt het uit of je alle shifts gedeeltelijk oplost of dat je een aantal shifts helemaal oplost en de rest helemaal niet?

[g0tanks]

siddler schreef op maandag 12 oktober 2020 @ 23:10:
Wat zijn je eisen aan het uitbreidbaar maken? Gaan er alleen uren van shift 1 naar shift x overgeboekt worden? Of wil je de flexibiliteit om bijv van shift 3 naar shift 1 over te boeken?
En maakt het uit of je alle shifts gedeeltelijk oplost of dat je een aantal shifts helemaal oplost en de rest helemaal niet?

In principe is de logica: als shift 1 over heeft, dan verschuiven naar eerstvolgende shift die te kort komt (2 of 3). In 95% van de gevallen zal het niet voorkomen dat shift 1 te kort komt en juist 2 of 3 over hebben, dus andere richtingen hoeven van mij niet meegenomen te worden (bijv. 2 naar 3 of 3 naar 1).

Makkelijk een extra shift toevoegen door de formule door te trekken zou leuk zijn, maar is ook geen vereiste.

[ Voor 7% gewijzigd door g0tanks op 12-10-2020 23:16 ]

[Lustucru]

Er staat me iets bij van een topic over belastingverrekening wat qua probleem hier wel op lijkt. Effe zoeken.

Gevonden: Lustucru in "Excel tabel verrekenbare verliezen" dit werd interessant door een beperking van 9 jaar, maar dat is bij jou niet van toepassing. Een lopend totaal rij dus.

[ Voor 48% gewijzigd door Lustucru op 12-10-2020 23:44 ]

[Lustucru]

Zoiets:


Je kunt de formules gewoon doortrekken naar een onbeperkt aantal shifts, en per shift aangeven wat de omrekenfactor is. Voor de duidelijkheid heb ik wat rijen (5 t/m 7) toegevoegd, maar die kun je gewoon weglaten. Het idee is dat je iedere shift eerst terug rekent naar 'standaarduren. Iedere shift kan uren 'opnemen uit' een bufferpotje, het lopend totaal. De berekening daarvoor is (bv C3: =MAX(0;MIN(B8;-1*C2*C3)). De rest is simpelweg optellen en aftrekken.

[g0tanks]

Excuus voor de late reactie. Ik kom er nu pas aan toe om er weer naar te kijken.

De oplossing van @Lustucru werkt goed, ook bij het toevoegen van shifts. Dank voor het meedenken!

[ Voor 3% gewijzigd door g0tanks op 21-10-2020 22:58 ]