Excel Solver: Optimering af resultater, tilføjelse af begrænsninger og lagring af løsninger som scenarier

Du opsætter din Excel Solver- model ved at bruge dialogboksen Solver Parameters. Du bruger boksen Sæt mål til at angive målcellen, og du bruger gruppen Til til at fortælle Excel Solver, hvad du vil have fra målcellen: den maksimalt mulige værdi; den mindst mulige værdi; eller en bestemt værdi. Til sidst bruger du boksen Ved at ændre variable celler til at angive de celler, som Solver kan bruge til at tilslutte værdier for at optimere resultatet.

Optimering af Excel Solver-resultater

Når Solver finder en løsning, kan du vælge enten Keep Solver Solution eller Restore Original Values. Hvis du vælger Keep Solver Solution, ændrer Excel arbejdsarket permanent. Du kan ikke fortryde ændringerne.

Med din Solver-klare regnearksmodel klar til brug, er her trinene, du skal følge for at finde et optimalt resultat for din model ved hjælp af Solver:

Vælg Data → Løser.
Excel åbner dialogboksen Solver Parameters.

Indtast adressen på din models målcelle i feltet Set Objective.
Bemærk, at hvis du klikker på cellen for at indtaste den, indtaster Solver automatisk en absolut celleadresse (f.eks. $B$14 i stedet for B14). Solver fungerer fint begge veje.

I gruppen Til skal du vælge en mulighed:

• Max: Returnerer den maksimalt mulige værdi.
• Min: Returnerer den mindst mulige værdi.
• Værdi af: Indtast et tal for at indstille målcellen til det nummer.

  For eksempelmodellen er Value Of blevet valgt og 0 indtastet i tekstboksen.

I feltet Ved at ændre variable celler skal du indtaste adresserne på de celler, du vil have Solver til at ændre, mens den leder efter en løsning.
I eksemplet er de skiftende celler B4 og C4. Følgende billede viser den udfyldte Solver Parameters dialogboks.

Den udfyldte Solver Parameters dialogboks.

Klik på Løs.
Solver går i gang. Da Solver arbejder på problemet, kan du muligvis se dialogboksen Vis prøveløsning dukke op en eller flere gange.

Klik på Fortsæt i enhver dialogboks Vis prøveløsning, der vises, for at flytte tingene videre.
Når optimeringen er fuldført, viser Excel dialogboksen Løserresultater.

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

Vælg indstillingen Keep Solver Solution.
Hvis du ikke vil acceptere resultatet, skal du i stedet vælge indstillingen Gendan oprindelige værdier.

Klik på OK.

Du kan bede Solver om at vise en eller flere rapporter, der giver dig ekstra information om resultaterne. I dialogboksen Løserresultater skal du bruge listen Rapporter til at vælge hver rapport, du vil se:

• Svar: Viser information om modellens målcelle, variable celler og begrænsninger. For målcellen og variable celler viser Solver de oprindelige og endelige værdier.
• Sensitivitet: Forsøg på at vise, hvor følsom en løsning er over for ændringer i modellens formler. Layoutet af følsomhedsrapporten afhænger af den type model, du bruger.
• Grænser: Viser målcellen og dens værdi samt variable celler og deres adresser, navne og værdier.

Excel Solver kan bruge en af ​​flere løsningsmetoder. I dialogboksen Solver Parameters skal du bruge listen Vælg en løsningsmetode til at vælge en af ​​følgende:

• Simplex LP: Brug hvis din regnearksmodel er lineær. På de enklest mulige termer er en lineær model en, hvor variablerne ikke hæves til nogen potenser, og ingen af ​​de såkaldte transcendente funktioner - såsom SIN og COS - bruges.
• GRG ikke-lineær: Brug hvis din regnearksmodel er ikke-lineær og glat. Generelt set er en glat model en, hvor en graf af den anvendte ligning ikke viser skarpe kanter eller brud.
• Evolutionær: Brug hvis din regnearksmodel er ikke-lineær og ikke-glat.

Behøver du at bekymre dig om noget af dette? Næsten bestemt ikke. Excel Solver bruger som standard GRG ikke-lineær, og det burde fungere til næsten alt, hvad du gør med Solver.

Tilføjelse af begrænsninger til Excel Solver

Den virkelige verden sætter restriktioner og betingelser på formler. En fabrik kan have en maksimal kapacitet på 10.000 enheder om dagen, antallet af ansatte i en virksomhed kan ikke være et negativt tal, og dine annonceringsomkostninger kan være begrænset til 10 procent af de samlede udgifter.

Antag på samme måde, at du kører en break-even-analyse på to produkter. Hvis du kører optimeringen uden nogen begrænsninger, kan Solver opnå en samlet fortjeneste på 0 ved at sætte det ene produkt med et lille tab og det andet med et lille overskud, hvor tabet og fortjenesten ophæver hinanden. Faktisk, hvis du ser nærmere på det forrige billede, er det præcis, hvad Solver gjorde. For at få en ægte break-even-løsning foretrækker du måske at se begge produktfortjenesteværdier som 0.

Sådanne begrænsninger og betingelser er eksempler på, hvad Solver kalder begrænsninger. Tilføjelse af begrænsninger fortæller Solver at finde en løsning, så disse betingelser ikke bliver overtrådt.

Sådan kører du Solver med begrænsninger tilføjet til optimeringen:

Vælg Data → Løser.
Excel åbner dialogboksen Solver Parameters.

Brug boksen Sæt mål, gruppen Til og boksen Ved at ændre variable celler til at konfigurere Solver som beskrevet ovenfor.

Klik på Tilføj.
Excel viser dialogboksen Tilføj begrænsning.

Indtast adressen på den celle, du vil begrænse, i feltet Cellereference.
Du kan indtaste adressen eller vælge cellen på regnearket.

I rullelisten skal du vælge den operatør, du vil bruge.
Det meste af tiden bruger du en sammenligningsoperator, såsom lig med (=) eller større end (>). Brug operatoren int (heltal), når du har brug for en begrænsning, som f.eks. antal ansatte, til at være en heltalsværdi i stedet for et reelt tal (det vil sige et tal med en decimalkomponent; du kan ikke have 10,5 ansatte!). Brug binær-operatoren, når du har en begrænsning, der skal være enten SAND eller FALSK (eller 1 eller 0).

Hvis du valgte en sammenligningsoperator i trin 5, skal du i feltet Begrænsning indtaste den værdi, som du vil begrænse cellen med.
Dette billede viser et eksempel på en fuldført Tilføj begrænsning dialogboks. I eksempelmodellen fortæller denne begrænsning, at Solver skal finde en løsning, så produktfortjenesten for den oppustelige dartskive (celle B12) er lig med 0.

Den udfyldte Add Constraint dialogboks.

For at angive flere begrænsninger skal du klikke på Tilføj og gentage trin 4 til 6 efter behov.

I eksemplet tilføjer du en begrænsning, der beder om, at hundepoleringsmaskinens produktfortjeneste (celle C12) skal være 0.

Klik på OK.

Excel vender tilbage til dialogboksen Solver Parameters og viser dine begrænsninger i listen Subject to the Constraints.

Klik på Løs.

Klik på Fortsæt i enhver dialogboks Vis prøveløsning, der vises, for at flytte tingene videre.
Billedet nedenfor viser eksemplet på break-even-løsningen med de tilføjede begrænsninger. Bemærk, at ikke kun den samlede overskudscelle (B14) er sat til 0, men det samme er de to produktoverskudsceller (B12 og C12).

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

Vælg indstillingen Keep Solver Solution.
Hvis du ikke vil acceptere resultatet, skal du i stedet vælge indstillingen Gendan oprindelige værdier.

Klik på OK.

Du kan maksimalt tilføje 100 begrænsninger. Hvis du også skal lave en ændring af en begrænsning, før du begynder at løse, skal du vælge begrænsningen i listen Subject to the Constraints, klikke på Change og derefter foretage dine justeringer i dialogboksen Change Constraint, der vises. Hvis du vil slette en begrænsning, som du ikke længere har brug for, skal du vælge begrænsningen og derefter klikke på Slet.

Gem en Excel Solver-løsning som et scenarie

Når du har en regnearksmodel, der bruger et sammenhængende sæt af inputværdier - kendt som skiftende celler - har du, hvad Excel kalder et scenarie. Med Solver er disse skiftende celler dens variable celler, så en Solver-løsning svarer til en slags scenarie i Excel . Solver giver dig dog ikke en nem måde at gemme og køre en bestemt løsning på igen. For at omgå dette problem kan du gemme en løsning som et scenarie, som du senere kan genkalde ved hjælp af Excels Scenario Manager-funktion.

Følg disse trin for at gemme en Solver-løsning som et scenarie:

Vælg Data → Løser.
Excel åbner dialogboksen Solver Parameters.

Brug boksen Sæt mål, gruppen Til, boksen Ved at ændre variable celler og listen med forbehold for begrænsninger til at konfigurere Solver som beskrevet ovenfor.

Klik på Løs.

Når som helst dialogboksen Vis prøveløsning vises, skal du vælge Fortsæt.
Når optimeringen er fuldført, viser Excel dialogboksen Løserresultater.

Klik på Gem scenarie.
Excel viser dialogboksen Gem scenarie.

Indtast et navn til scenariet i dialogboksen Scenarionavn, og klik derefter på OK.
Excel vender tilbage til dialogboksen Løserresultater.

Vælg indstillingen Keep Solver Solution.
Hvis du ikke vil acceptere resultatet, skal du i stedet vælge indstillingen Gendan oprindelige værdier.

Klik på OK.