Excel Solver: Optimalisere resultater, legge til begrensninger og lagre løsninger som scenarier

Du konfigurerer Excel Solver- modellen ved å bruke dialogboksen Solver Parameters. Du bruker Set Objective-boksen for å spesifisere målcellen, og du bruker To-gruppen til å fortelle Excel Solver hva du vil ha fra målcellen: maksimalt mulig verdi; minst mulig verdi; eller en bestemt verdi. Til slutt bruker du By Changing Variable Cells-boksen for å spesifisere cellene som Solver kan bruke til å plugge inn verdier for å optimalisere resultatet.

Optimalisering av Excel Solver-resultater

Når Solver finner en løsning, kan du velge enten Keep Solver Solution eller Gjenopprett opprinnelige verdier. Hvis du velger Keep Solver Solution, endrer Excel regnearket permanent. Du kan ikke angre endringene.

Med din Solver-klare regnearkmodell klar til bruk, her er trinnene du må følge for å finne et optimalt resultat for modellen din ved å bruke Solver:

Velg Data → Løser.
Excel åpner dialogboksen Solver Parameters.

I Set Objective-boksen skriver du inn adressen til modellens målcelle.
Merk at hvis du klikker på cellen for å skrive den inn, angir Solver automatisk en absolutt celleadresse (for eksempel $B$14 i stedet for B14). Løser fungerer fint uansett.

I Til-gruppen velger du et alternativ:

• Maks: Returnerer maksimalt mulig verdi.
• Min: Returnerer minimumsverdien.
• Verdi av: Skriv inn et tall for å sette målcellen til det tallet.

  For eksempelmodellen er Value Of valgt og 0 lagt inn i tekstboksen.

I By Changing Variable Cells-boksen skriver du inn adressene til cellene du vil at Solver skal endre mens den leter etter en løsning.
I eksemplet er de skiftende cellene B4 og C4. Følgende bilde viser den fullførte dialogboksen Solver Parameters.

Den fullførte Solver Parameters dialogboksen.

Klikk Løs.
Solver går i gang. Ettersom Solver jobber med problemet, kan du se dialogboksene Vis prøveløsning vises en eller flere ganger.

I en hvilken som helst dialogboks for Vis prøveløsning som vises, klikker du på Fortsett for å flytte ting.
Når optimaliseringen er fullført, viser Excel dialogboksen Løserresultater.

Dialogboksen Løserresultater og løsningen på break-even-problemet.

Velg alternativet Keep Solver Solution.
Hvis du ikke vil godta resultatet, velger du alternativet Gjenopprett opprinnelige verdier i stedet.

Klikk OK.

Du kan be Solver om å vise en eller flere rapporter som gir deg ekstra informasjon om resultatene. I dialogboksen Løserresultater bruker du rapportlisten til å velge hver rapport du vil vise:

• Svar: Viser informasjon om modellens målcelle, variable celler og begrensninger. For målcellen og variabelcellene viser Solver de opprinnelige og endelige verdiene.
• Sensitivitet: Forsøker å vise hvor følsom en løsning er for endringer i modellens formler. Oppsettet til sensitivitetsrapporten avhenger av typen modell du bruker.
• Begrensninger: Viser målcellen og dens verdi, samt variabelcellene og deres adresser, navn og verdier.

Excel Solver kan bruke en av flere løsningsmetoder. I dialogboksen Løserparametere bruker du listen Velg en løsningsmetode for å velge ett av følgende:

• Simplex LP: Bruk hvis regnearkmodellen din er lineær. På enklest mulig måte er en lineær modell en modell der variablene ikke heves til noen potenser og ingen av de såkalte transcendente funksjonene - som SIN og COS - brukes.
• GRG ikke-lineær: Bruk hvis regnearkmodellen din er ikke-lineær og jevn. Generelt sett er en jevn modell en der en graf av ligningen som brukes ikke viser skarpe kanter eller brudd.
• Evolusjonær: Bruk hvis regnearkmodellen din er ikke-lineær og ikke-jevn.

Må du bekymre deg for noe av dette? Nesten absolutt ikke. Excel Solver bruker som standard GRG ikke-lineær, og det skal fungere for nesten alt du gjør med Solver.

Legge til begrensninger til Excel Solver

Den virkelige verden setter begrensninger og betingelser på formler. En fabrikk kan ha en maksimal kapasitet på 10 000 enheter per dag, antall ansatte i et selskap kan ikke være et negativt tall, og annonseringskostnadene dine kan være begrenset til 10 prosent av totale utgifter.

På samme måte antar du at du kjører en break-even-analyse på to produkter. Hvis du kjører optimaliseringen uten noen begrensninger, kan Solver oppnå en total fortjeneste på 0 ved å sette ett produkt med et lite tap og det andre med en liten fortjeneste, der tap og fortjeneste opphever hverandre. Faktisk, hvis du ser nærmere på det forrige bildet, er dette akkurat det Solver gjorde. For å få en ekte break-even-løsning, foretrekker du kanskje å se begge produktfortjenesteverdiene som 0.

Slike restriksjoner og betingelser er eksempler på det Solver kaller begrensninger. Å legge til begrensninger ber Solver finne en løsning slik at disse betingelsene ikke brytes.

Slik kjører du Solver med begrensninger lagt til optimaliseringen:

Velg Data → Løser.
Excel åpner dialogboksen Solver Parameters.

Bruk boksen Set Objective, Til-gruppen og By Changing Variable Cells-boksen for å sette opp Solver som beskrevet ovenfor.

Klikk på Legg til.
Excel viser dialogboksen Legg til begrensning.

I Cellereferanse-boksen skriver du inn adressen til cellen du vil begrense.
Du kan skrive inn adressen eller velge cellen på regnearket.

I rullegardinlisten velger du operatøren du vil bruke.
Mesteparten av tiden bruker du en sammenligningsoperator, for eksempel lik (=) eller større enn (>). Bruk operatøren int (heltall) når du trenger en begrensning, for eksempel totalt antall ansatte, for å være en heltallsverdi i stedet for et reelt tall (det vil si et tall med en desimalkomponent; du kan ikke ha 10,5 ansatte!). Bruk binær-operatoren når du har en begrensning som enten må være SANN eller USANN (eller 1 eller 0).

Hvis du valgte en sammenligningsoperator i trinn 5, i Begrensningsboksen, skriv inn verdien du vil begrense cellen med.
Dette bildet viser et eksempel på en fullført Add Constraint-dialogboks. I eksempelmodellen forteller denne begrensningen Solver å finne en løsning slik at produktfortjenesten til den oppblåsbare dartskiven (celle B12) er lik 0.

Den fullførte dialogboksen Add Constraint.

For å spesifisere flere begrensninger, klikk på Legg til og gjenta trinn 4 til 6 etter behov.

For eksempelet legger du til en begrensning som ber om at fortjenesten for hundepoleringsmaskinen (celle C12) skal være 0.

Klikk OK.

Excel går tilbake til dialogboksen Solver Parameters og viser begrensningene dine i listen Subject to the Constraints.

Klikk Løs.

I en hvilken som helst dialogboks for Vis prøveløsning som vises, klikker du på Fortsett for å flytte ting.
Bildet nedenfor viser eksempelet break-even løsning med begrensningene lagt til. Legg merke til at ikke bare er Total Profit-cellen (B14) satt til 0, men det samme er de to Product Profit-cellene (B12 og C12).

Dialogboksen Løserresultater og den endelige løsningen på break-even-problemet.

Velg alternativet Keep Solver Solution.
Hvis du ikke vil godta resultatet, velger du alternativet Gjenopprett opprinnelige verdier i stedet.

Klikk OK.

Du kan legge til maksimalt 100 begrensninger. Hvis du trenger å gjøre en endring i en begrensning før du begynner å løse, velger du begrensningen i listen Subject to the Constraints, klikker på Endre og gjør justeringene i dialogboksen Change Constraint som vises. Hvis du vil slette en begrensning du ikke lenger trenger, velger du begrensningen og klikker på Slett.

Lagre en Excel Solver-løsning som et scenario

Når du har en regnearkmodell som bruker et sammenhengende sett med inngangsverdier – kjent som celleskiftende – har du det Excel kaller et scenario. Med Solver er disse skiftende cellene dens variable celler, så en Solver-løsning utgjør et slags scenario i Excel . Solver gir deg imidlertid ikke en enkel måte å lagre og kjøre en bestemt løsning på. For å omgå dette problemet kan du lagre en løsning som et scenario som du senere kan hente frem ved å bruke Excels Scenario Manager-funksjon.

Følg disse trinnene for å lagre en Solver-løsning som et scenario:

Velg Data → Løser.
Excel åpner dialogboksen Solver Parameters.

Bruk Angi mål-boksen, Til-gruppen, Ved å endre variable celler-boksen og Subject to the Constraints-listen for å sette opp Solver som beskrevet ovenfor.

Klikk Løs.

Når som helst dialogboksen Vis prøveløsning vises, velger du Fortsett.
Når optimaliseringen er fullført, viser Excel dialogboksen Løserresultater.

Klikk Lagre Scenario.
Excel viser dialogboksen Lagre scenario.

I dialogboksen Scenarionavn skriver du inn et navn for scenariet og klikker OK.
Excel returnerer deg til dialogboksen Løserresultater.

Velg alternativet Keep Solver Solution.
Hvis du ikke vil godta resultatet, velger du alternativet Gjenopprett opprinnelige verdier i stedet.

Klikk OK.