Hur man ställer in ett lösarkalkylblad i Excel

Här är en Excel-arbetsbok inrättad för att lösa ett optimeringsmodelleringsproblem för ett enmansföretag. Om du väljer att konstruera Solver-arbetsboksexemplet själv (en bra idé), vill du berätta för Excel att visa faktiska formler snarare än formelresultat i arbetsboken.

Detta är vad den här arbetsboken gör förresten. För att göra detta, välj det kalkylbladsområde där du vill visa de faktiska formlerna snarare än formelresultaten och tryck sedan på tangenterna Ctrl och ` (allvarlig accent). Genom att trycka på Ctrl+` ber du Excel att visa formeln snarare än formelresultatet inom det valda intervallet.

Att ställa in en Solver-arbetsbok kräver tre steg:

Identifiera Solver-variablerna.

Först vill du identifiera variablerna i ditt optimeringsmodelleringsproblem. I fallet med att försöka räkna ut antalet böcker att skriva och seminarier att ge för att tjäna mest pengar i ditt enmansföretag, är de två Solver-variablerna böcker och seminarier.

Du skulle ange etiketterna som visas i intervall A1:A3 och sedan startvariabelvärdena som visas i intervall B2:B3. Den här delen av arbetsbladet är inget magiskt. Den identifierar helt enkelt vilka variabler som ingår i objektivfunktionen. Objektivfunktionen är formeln som du vill maximera eller minimera. Värdena som lagras i kalkylbladsintervallet B2:B3 är startgissningarna om vad de optimala variabelvärdena bör vara.

Det här är bara en gissning att det optimala antalet böcker att skriva är två och att det optimala antalet seminarier att ge är åtta. Du vet inte vad det optimala antalet böcker och seminarier faktiskt är förrän du har löst problemet.

Även om du inte behöver namnge cellerna som innehåller variabelvärdena - i det här fallet celler B2 och B3 - gör det mycket lättare att förstå din objektiva funktionsformel och dina begränsningsformler genom att namnge dessa celler. Så du bör namnge cellerna.

Om du ställer in en arbetsbok som den här kan du namnge cellerna med variabelvärde genom att välja kalkylbladsintervallet A2:B3 och sedan klicka på kommandoknappen Skapa från urval på fliken Formel. När dialogrutan Skapa namn från urval visas i Excel, markerar du kryssrutan Vänster kolumn och klickar på OK.

Detta talar om för Excel att använda etiketterna i den vänstra kolumnen: Detta skulle vara intervallet A2:A3 — för att namnge intervallet B2:B3. Med andra ord, genom att följa dessa steg namnger du cell B2-böcker och du namnger cell B3-seminarier.

Beskriv den objektiva funktionen.

Objektivfunktionen, som visas i cell B5, ger formeln som du vill optimera. När det gäller en vinstformel vill man maximera en funktion för att man vill maximera vinsten förstås.

Alla objektiva funktioner bör inte maximeras. Vissa objektiva funktioner bör minimeras. Om du till exempel skapar en objektiv funktion som beskriver kostnaden för något reklamprogram eller risken för något investeringsprogram, kan du logiskt välja att minimera dina kostnader eller minimera dina risker.

För att beskriva målfunktionen, skapa en formel som beskriver värdet som du vill optimera. När det gäller en vinstfunktion för enmansföretaget tjänar du 15 000 USD för varje bok du skriver och 20 000 USD för varje seminarium du ger. Du kan beskriva detta genom att ange formeln =15000*Böcker+20000*Seminarier .

Med andra ord kan du beräkna vinsten för ditt enmansföretag genom att multiplicera antalet böcker som du skriver gånger $15 000 och antalet seminarier som du ger gånger $20 000. Detta är vad som visas i cell B5.

Identifiera eventuella objektiva funktionsbegränsningar.

I kalkylbladsintervallet A8:C11 beskrivs och identifieras begränsningarna på objektivfunktionen. Fyra begränsningar kan begränsa vinsterna som du kan göra i ditt företag:

• Gräns ​​för kontantkrav: Den första begränsningen (cell A8) kvantifierar begränsningen för kontanter som krävs. I det här exemplet kräver varje bok $500 kontanter, och varje seminarium kräver $2500 kontanter. Om du har $20 000 kontanter att investera i böcker och seminarier, är du begränsad i antalet böcker som du kan skriva och antalet seminarier som du kan ge av den kontanta, initiala investeringen som du behöver göra.

  Formeln i cell B8 beskriver de kontanter som ditt företag kräver. Värdet som visas i cell C8, 20000, identifierar den faktiska begränsningen.

• Arbetstidsgräns: Arbetstidsgränsen kvantifieras genom att ha formeln i cell B9 och värdet 1880 i cell C9. Använd dessa två uppgifter, formeln och det konstanta värdet, för att beskriva en arbetstidsgräns. I ett nötskal säger denna begränsning att antalet timmar du spenderade på böcker och seminarier måste vara mindre än 1880.

• Policy för minsta antal böcker: Begränsningen att du måste skriva minst en bok per år ställs in i cellerna B10 och C10. Formeln =Böcker går in i cell B10. Minsta antalet böcker, 1, hamnar i cell C10.

• Minsta antal seminarier policy: Begränsningen att du måste ge minst fyra seminarier per år ställs in i cellerna B11 och C11. Formeln går in i cell B11. Minsta antal seminarier konstant värde, 4, går in i cell C11.

När du har gett begränsningsformlerna och angett konstanterna som formelresultaten kommer att jämföras med, är du redo att lösa ditt problem med optimeringsmodellering. Med arbetsboken inställd är det faktiskt väldigt enkelt att lösa funktionen.

Att sätta upp arbetsboken och definiera problemet med objektiv funktion och formler för begränsningar är den svåra delen.