Freefarm
      Hem
Kontakta oss
Optimera foderstater med excel

Det finns en inbyggd optimeringsfunktion i excel. Det är lite halvkrångligt att använda den, men å andra sidan är halva jobbet oftast redan gjort, dvs. man har redan excel installerat i sin dator.

Det första man måste göra är att installera problemlösaren, om det inte redan är gjort sedan förut. Gör så här:

Verktyg -> tillägg

Kryssa i problemlösaren -> OK

ja

Nu ska problemlösaren finnas med på verktygsmenyn

Nu när problemlösaren är på plats går vi vidare med att bygga själva excelarket. Jag ska förklara steg för steg hur det här optimeringsexcelarket är uppbyggt. Ladda hem excelfilen genom att högerklicka på länken och välj spara mål som. Börja med att titta på arket så du ser hur det ser ut.

När man optimerar foderstater i excel är det praktiskt att dela upp excelarket i tre tabeller, en med uppgifter om djuret, en med behoven och en med fodertabellen. I det här exemplet ska vi göra en foderstat för en mjölkko så vi börjar med det:

Här har jag alltså bara kort och gott skrivit in det som står i rutorna.

Därnäst behover man en fodertabell: Jag kompleterar fodertabellen med en extra kolumn som jag kallar giva. Dom här siffrorna är det också bara att skriva in rakt upp och ned. Alltså hitintills finns ingen cell som innehåller någon formel.

Så kommer vi då till sista tabellen med behoven och resultatet av beräkningen. Dom här cellerna innehåller formler. Så här ser sista tabellen ut:

Klicka här för att öppna bilden i ett nytt fönster. Då kan du ha den bredvid fönstret du läser i så blir det lättare att följa med i texten nedan sedan.

Cellen B7 innehåller formeln för energibehov. I blå boken finns alla formler som jag använt. Energibehovet styrs av vikten och mjölkproduktionen. Som vanligt är det vikten upphöjt till 0.75 gånger någon kooffecient som utgör underhållsbehovet. Sedan tillkommer 5 MJ för varje kg ECM (varje lite mjölk). Slutklämmen med 1.11 - 13.6 är ett försök att kompensera för minskat foderutnyttjande vid höga avkastningsnivåer och därmed höga fodergivor. Formeln ser ut så här i excelutförande :

=((0,507*UPPHÖJT.TILL(B3;0,75)) + 5*B4) * 1,11-13,6
De andra behovscellerna är lättare B8 innehåller AAT-behovet som beräknas med formeln
=B7*7,6
dvs. energibehovet gånger 7,6.
B9 innehåller Kalciumbehovet som beräknas med formeln
=(0,03 * B3 + 13) + 2,6*B4
Det är alltså underhållsbehovet först kopplad till vikten i cell B3, sedan behövs det 2,6 gram för varje kg ECM (lite mjölk).
B10 innehåller fosforbehovet som beräknas med formeln
=(0,02 * B3 + 9) + 1,8*B4
PBV min/max och grovfoderandel är bara siffror som är inskrivna.

Sedan kommer vi till vad foderstaten innehåller. I C-kolumnen beräknas summan av näringsinnehållet från varje foders giva. Här använder jag funktionen produktsumma, en av excels alla fiffiga små funktioner. Produktsumma-funktionen multiplicerar ett antal tal med varandra och summerar sedan resultaten. Det är precis vad vi vill i det här fallet. T.ex., Vi vill veta summan av högivan*energi i hå + ensilagegivan * energi i ensilage+ havregivan * energi i havre osv för alla fodren i tabellen. Så här ser formeln för detta ut i cell C7:

=PRODUKTSUMMA($G$4:$G$14;J$4:J$14)
I G4 till G14 står givorna (som nu alla är 0) och i J4 till J14 har vi energivärdena. Men hjälp av $-tecken låser jag G4, G14 och raderna 4 till 14 så att jag enkelt kan kopiera upp den här cellen till de andra näringsämnenas rader C8 till C12.

I C13 är formeln lite annorluna där vi ska få fram andelen grovfoder:

=OM(SUMMA(G4:G14)>0;PRODUKTSUMMA($G$4:$G$14;I$4:I$14)/SUMMA(G4:G14);0)
Vi ska ju summera mängden grovfoder med mängden av allt foder. Men om alla givor är 0 så kommer det bli division med 0. För att undvika det har jag lagt in en OM-sats för att kolla om summan av G4-G14 (dvs summan av givorna) är större än 0. Är den den gör man det som står efter nästa ;-tecken, annars det som står efter nästnästa ;-tecken. Ha för vana att alltid göra en sån här koll om det finns risk för division med 0, annars ser det väldigt slarvigt och amatörmässigt ut tycker jag.
Notera också att jag i fodertabellen angett mängden grovfoder i ett foder som ett tal mellan 0 och 1. Hö och ensilage är 100% grovfoder, dvs 1 resten är inte grovfoder alls, dvs 0.

Slutligen har vi priset i cell C14 som också är en produktsumma på samma sätt som för energicellen C7.

Nu kan man använda excelbaldet för att själv prova med olika givor. Bara att fylla i vilka siffror man vill i giva-kolumen G4 till G14.

Dax för själva optimeringen. Klicka på verktyg -> problemlösaren

Steg 1 är att peka ut målcellen. Målet med optimeringen är ju att få så lågt pris som möjligt. Alltså är målcellen priset, dvs cell C14. Alltså skriv in C14 i målcellsrutan. Eftesom vi vill att foderstaten ska vara så billig som möjligt och inte så dyr som möjligt kryssar vi för Min också.

Steg 2 är att peka ut de justerbara cellerna, alltså de som man kan ändra på för att uppfylla kraven. De justerbara cellerna är alltså fodergivorna, cellerna G4 till G14. Fyll i det också som bilden visar:

Steg 3 är att skriva in kraven. Låt oss börja med energibehovet. Behovet står i cellen B7 och innehållet i cellen C7. Vi vill att behovet ska vara uppfyllt, dvs minst så mycket energi som står i behovscellen B7. Alltså C7 ska vara större än eller lika med B7 (C7>=B7). Det skriver man in i begränsningarna. Gör så här:

Klicka på lägg till

Fyll i dialogrutan så som bilden visar, klicka sedan på lägg till

Upprepa detta för de övriga kraven:

C8  >=  B8
C9  >=  B9
C10 >= B10
C11 >= B11
C12 <= B12 (OBS omvänt tecken eftersom det är en max-begränsning.
C13 >= B13
Nu finns det ytterligare ett krav att ställa på foderstaten och det är att det inte får finnas några negativa fodergivor, annars kommer foderstaten ju att bli billigare ju större negativ giva man ger. Man behöver inte lägga in den begränsingen för alla fodergivor för sig, utan kan skriva in det som G4:G14 >= 0. Så här ser det ut på bild:

Klicka nu på lägg till för sista gången och sedan på OK så är alla begränsningarna inlagda. Dax att optimera!

Klicka på lös!

Lösning hittades, klicka på OK för att läsa av resultatet!

Problemlösaren har nu fyllt i givorna G-kolumnen, och excelkalkylbladet har räknat ut vad foderstaten innehållet i C-kolumen.

Ibland räknar excel fel när den optimerar. Man kan då prova att ändra på precisionen. I problemlösaren klicka på Alternativ. Skriv sedan in en mindre siffra för precisionen. Grundvärdet är 0,000001, men du kan prova med 0,000000001 om du får problem med att excel räknar fel i sin optimering. Särskilt lätt blir det fel om det ingår tal i beräkningen som är väldigt olika stora.