Hej i forumet

Jeg mangler en meget krævende formel, den giver mig grå hår....

 

Jeg skal bruge et regneark som kan beregne logistiktillæg

 

hvis man leverer mellem             0 - 1.000.000 kg = + 0 øre/kg

hvis man leverer mellem 1.000.001 - 1.500.000 kg = +21 øre/kg

hvis man leverer mellem 1.500.001 - 2.000.000 kg = + 28 øre/kg

 

Man skal kunne indtaste antal kg for hhv Januar, Februar osv. og det er summen af året som udløser tillæg.

 

Dvs. leverer man 800.000 kg i januar = intet tillæg

Leverer man også 800.000 kg i Februar = 1.600.000 kg = tillæg for de 500.000 kg til 21 øre og 28 øre for 100.000 kg.

 

Det der giver mig grå hår er ikke at regne tillægget ud for det som ligger over grænsen, men det som ligger mellem perioderne leverede man nu kun 200.000 i marts og er oppe på 1.800.000 skal man jo ikke regne tillæg for de 300.000 som ligger over 1.500.000 men kun de 200.000 som er leveret i marts....

 

Forvirret - det er jeg.....  Stor æske chokolade til den som kan løse opgaven

 

Super at du lige vil hjælpe.

 

Jeg har lavet skemaet så det virker, men jeg synes at der er for mange mellemregninger.

 

Prøv at kigge det igennem og kom med din mening.

 

 

Vh Anders

 

Hans

 

Så forklar venligst hvis/hvordan i gjorde.

 

Advarsel: Det bliver langt det her.

 

Det første jeg gør, er, at indtaste 0 i celle F2. Herefter i F3: =(D3-D2)/100 som kopieres nedad til og med F9. Tallene udtrykker det marginale ørebeløb pr. Tillægsgruppe. Derfor overskriften delta.

 

I forhold til dansbjergs oprindelige fil har jeg tilføjet endnu en hjælpekolonne, nemlig kolonne M, akkumuleret. I M17 findes matriksformlen:

 

=HVIS(IKKE(ER.TAL(B17));0;SUMPRODUKT(1*(C17>($B$2:$B$9));(C17-($B$2:$B$9));($F$2:$F$9)))

 

Bemærk, at hvis du ikke allerede kender til matriksformler, så er dette her ikke det rigtige sted, at begynde at studere dem!

 

Lad os se nærmere på formlen. Det, der er interessant her er følgende del:

SUMPRODUKT(1*(C17>($B$2:$B$9));(C17-($B$2:$B$9));($F$2:$F$9))

 

Det der står i det første sæt parenteser danner en matrice med otte elementer, som foretager sammenligningen: Er første måneds indvejet total, akkumuleret =C17 > første element i matricen B2:B9 (altså B2), hvor B2:B9 er tærskelværdierne. Dernæst er anden måneds indvejet total, akkumuleret større end andet element i matricen B2:B9 (altså B3) osv. Det giver følgende matrice af booleske værdier {SAND\FALSK\FALSK\FALSK\FALSK\FALSK\FALSK\FALSK}. Når denne matrice ganges med 1 får vi følgende nye matrice: {1\0\0\0\0\0\0\0}. Prøv selv at chekke det ved hjælp af Evaluer formel værktøjet.

 

I den del af formlen der står i det andet sæt parenteser dannes igen en matrice med otte elementer.

Her fratrækkes de forskellige tærskelværdier fra Indvejet total, akkumuleret. Resultatet bliver følgende matrice:

{500000\-500000\-1000000\-1500000\-2000000\-2500000\-3000000\-3500000}

 

Når de to matricer:

{1\0\0\0\0\0\0\0} og

 

1*(C23>($B$2:$B$9)) returnerer: {1\1\0\0\0\0\0\0}

 

mens (C23-($B$2:$B$9)) returnerer:

{1041007\41007\-458993\-958993\-1458993\-1958993\-2458993\-2958993}

 

Når de to matricer ganges sammen får vi:

1*1041007 + 1*41007 + 0*(-458993) ++ (en masse ganget med 0)

 

Når dette til slut ganges med delta-værdierne (F2:F9) får vi:

1*1041007*0 + 1*41.007*0,021 = 861,15, det vil sige, at der der ligger ud over den anden tærskelværdi på 1000000 ganges med det marginale ørebeløb mellem første og anden tærskelværdi.

 

Hvis vi endelig tager formlen i celle M28:

SUMPRODUKT(1*(C28>($B$2:$B$9));(C28-($B$2:$B$9));($F$2:$F$9))

 

så returnerer 1*(C28>($B$2:$B$9)) matricen {1\1\1\0\0\0\0\0} som udtryk for indvejet total, akkumuleret (1.514084) overstiger de tre første tærskelværdier (0, 1000000, 1500000), derfor tre SAND = 1  som de tre første elementer i matricen. 

 

(C28-($B$2:$B$9)) returnerer matricen:

{1514084\514084\14084\-485916\-985916\-1485916\-1985916\-2485916}

som udtryk for at den indvejede total, akkumuleret, 1514084 er større end førsste tærskelværdi, 514084 er større end anden tærskelværdi, 14084 er større end tredje tærskelværdi osv.

1*1514084*0 +1*514084*0,021 +1*14084*0,011 ++(gange nul) giver resultatet 10.950.69.

 

Jeg er langt fra sikker på, at det her er udtrykt krystalklart, men det er altså også temmelig komplekst. Jeg har selv måttet arbejde hårdt for at begribe det. Du må formentlig påregne det samme.

 

Med venlig hilsen

Hans

 

 

 

Selv om ordet matrice forekommer adskillige gange i indlægget så er det ikke korrekt, som jeg skriver i sjette linje, at det er en matriksformel. Det vil sige, i den uploadede fil er formlen faktisk indtastet som en matriksformel (ved at Ctrl og Shift nede før man trykker på Enter), men det er en smutter fra min side. Det havde ikke været nødvendigt at gøre det. Almindelig indtastning virker lige så godt. SUMPRODUKT-funktionen udmærker sig blandt andet ved, at den virker på samme måde som en matriksformel, men den kræver altså ikke den specielle indtastningsmåde (Ctrl+Shift+Enter) som matriksformler ellers gør.

 

Hans