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

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 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)

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

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.