Hvordan sette opp et regneark for løser i Excel

Her er en Excel-arbeidsbok satt opp for å løse et optimaliseringsmodelleringsproblem for en enkeltpersonsbedrift. Hvis du velger å konstruere Solver-arbeidsbokeksemplet selv (en god idé), vil du fortelle Excel å vise faktiske formler i stedet for formelresultater i arbeidsboken.

Dette er hva denne arbeidsboken gjør, forresten. For å gjøre dette, velg regnearkområdet der du vil vise de faktiske formlene i stedet for formelresultatene, og trykk deretter på Ctrl og ` (alvorlig aksent)-tastene samtidig. Ved å trykke Ctrl+` ber du Excel vise formelen i stedet for formelresultatet innenfor det valgte området.

Å sette opp en Solver-arbeidsbok krever tre trinn:

Identifiser Solver-variablene.

Først vil du identifisere variablene i optimaliseringsmodelleringsproblemet. I tilfelle du prøver å finne ut hvor mange bøker du skal skrive og seminarer du skal gi for å tjene mest mulig penger i din enmannsbedrift, er de to Solver-variablene bøker og seminarer.

Du vil angi etikettene vist i område A1:A3 og deretter startvariabelverdiene vist i område B2:B3. Denne delen av regnearket er ikke noe magisk. Den identifiserer ganske enkelt hvilke variabler som går inn i objektivfunksjonen. Objektivfunksjonen er formelen du ønsker å maksimere eller minimere. Verdiene som er lagret i regnearkområdet B2:B3 er startgjettingene om hva de optimale variabelverdiene bør være.

Dette er bare en gjetning om at det optimale antallet bøker å skrive er to og at det optimale antallet seminarer å gi er åtte. Du vil ikke vite hva det optimale antallet bøker og seminarer faktisk er før du har løst problemet.

Selv om du ikke trenger å navngi cellene som inneholder variabelverdiene - i dette tilfellet, cellene B2 og B3 - gjør det å navngi disse cellene din objektive funksjonsformel og begrensningsformlene dine mye lettere å forstå. Så du bør navngi cellene.

Hvis du setter opp en arbeidsbok som denne, kan du navngi variabelverdicellene ved å velge regnearkområdet A2:B3 og deretter klikke på kommandoknappen Opprett fra utvalg i Formel-fanen. Når Excel viser dialogboksen Opprett navn fra utvalg, merker du av for venstre kolonne og klikker OK.

Dette forteller Excel å bruke etikettene i venstre kolonne: Dette vil være området A2:A3 - for å navngi området B2:B3. Med andre ord, ved å følge disse trinnene navngir du celle B2-bøker og du kaller celle B3-seminarer.

Beskriv den objektive funksjonen.

Objektivfunksjonen, vist i celle B5, gir formelen du ønsker å optimalisere. Når det gjelder en profittformel, vil du maksimere en funksjon fordi du vil maksimere fortjenesten, selvfølgelig.

Ikke alle objektive funksjoner bør maksimeres. Noen objektive funksjoner bør minimeres. Hvis du for eksempel oppretter en objektiv funksjon som beskriver kostnadene for et reklameprogram eller risikoen for et investeringsprogram, kan du logisk velge å minimere kostnadene eller minimere risikoen.

For å beskrive målfunksjonen, lag en formel som beskriver verdien du ønsker å optimalisere. Når det gjelder en profittfunksjon for enmannsbedriften, tjener du $15 000 for hver bok du skriver og $20 000 for hvert seminar du holder. Du kan beskrive dette ved å skrive inn formelen =15000*Bøker+20000*Seminarer .

Med andre ord kan du beregne fortjenesten til enmannsbedriften din ved å multiplisere antall bøker du skriver ganger $15 000 og antall seminarer du gir ganger $20 000. Dette er det som vises i celle B5.

Identifiser eventuelle objektive funksjonsbegrensninger.

I regnearkområdet A8:C11 er begrensningene beskrevet og identifisert på objektivfunksjonen. Fire begrensninger kan begrense fortjenesten du kan tjene i virksomheten din:

• Kontantkravsgrense: Den første begrensningen (celle A8) kvantifiserer begrensningen for kontantkrav. I dette eksemplet krever hver bok $500 kontanter, og hvert seminar krever $2500 kontanter. Hvis du har $20 000 kontanter til å investere i bøker og seminarer, er du begrenset i antall bøker du kan skrive og antall seminarer du kan gi av den kontante forhåndsinvesteringen du må gjøre.

  Formelen i celle B8 beskriver kontanter som kreves av virksomheten din. Verdien vist i celle C8, 20000, identifiserer den faktiske begrensningen.

• Arbeidstidsgrense: Arbeidstidsgrensen kvantifiseres ved å ha formelen i celle B9 og verdien 1880 i celle C9. Bruk disse to opplysningene, formelen og konstantverdien, for å beskrive en arbeidstidsgrense. I et nøtteskall sier denne begrensningen at antall timer du brukte på bøker og seminarer må være mindre enn 1880.

• Minimum antall bøker policy: Begrensningen om at du må skrive minst én bok i året er satt opp i celle B10 og C10. Formelen =Bøker går inn i celle B10. Minste antall bøker, 1, går inn i celle C10.

• Minimum antall seminarer policy: Begrensningen om at du må gi minst fire seminarer i året er satt opp i celle B11 og C11. Formelen går inn i celle B11. Minimum antall seminarer konstant verdi, 4, går inn i celle C11.

Etter at du har gitt begrensningsformlene og gitt konstantene som formelresultatene skal sammenlignes med, er du klar til å løse problemet med optimaliseringsmodellering. Med arbeidsboken satt opp, er det faktisk veldig enkelt å løse funksjonen.

Å sette opp arbeidsboken og definere problemet med objektiv funksjon og begrensningsformler er den vanskelige delen.