Hvad er Excel Solver?

2024 Forfatter: Abigail Brown | [email protected]. Sidst ændret: 2024-01-07 19:01

Excel Solver-tilføjelsen udfører matematisk optimering. Dette bruges typisk til at tilpasse komplekse modeller til data eller finde iterative løsninger på problemer. For eksempel vil du måske passe en kurve gennem nogle datapunkter ved hjælp af en ligning. Solver kan finde de konstanter i ligningen, der giver den bedste tilpasning til dataene. En anden applikation er, hvor det er vanskeligt at omarrangere en model for at gøre det nødvendige output til genstand for en ligning.

Hvor er Solver i Excel?

Solver-tilføjelsesprogrammet er inkluderet i Excel, men det indlæses ikke altid som en del af en standardinstallation. For at kontrollere, om den er indlæst, skal du vælge fanen DATA og se efter Solver-ikonet i Analysis-sektionen.

Hvis du ikke kan finde Solver under fanen DATA, skal du indlæse tilføjelsen:

1. Vælg fanen FILE, og vælg derefter Options.

   

2. I dialogboksen Options skal du vælge Add-Ins fra fanerne i venstre side.

   

3. Vælg Excel-tilføjelser nederst i vinduet fra rullemenuen Manage, og vælg Go…

   

4. Markér afkrydsningsfeltet ud for Solver Add-in, og vælg OK.

   

5. Kommandoen Solver skulle nu vises på fanen DATA. Du er klar til at bruge Solver.

   

Brug af Solver i Excel

Lad os starte med et simpelt eksempel for at forstå, hvad Solver gør. Forestil dig, at vi vil vide, hvilken radius der giver en cirkel med et areal på 50 kvadratenheder. Vi kender ligningen for arealet af en cirkel (A=pi r²). Vi kunne selvfølgelig omarrangere denne ligning for at give den radius, der kræves for et givet område, men lad os for eksemplets skyld lade os som om, vi ikke ved, hvordan man gør det.

Opret et regneark med radius i B1 og beregn arealet i B2 ved hjælp af ligningen =pi()B1^2.

Vi kunne manuelt justere værdien i B1, indtil B2 viser en værdi, der er tæt nok på 50. Afhængigt af hvor nøjagtige vi skal være, kan dette være en praktisk tilgang. Men hvis vi skal være meget præcise, vil det tage lang tid at foretage de nødvendige justeringer. Faktisk er dette i bund og grund, hvad Solver gør. Den foretager justeringer af værdier i bestemte celler og kontrollerer værdien i en målcelle:

1. Vælg DATA fanen og Solver for at indlæse Solver Parameters dialogboksen

2. Sæt mål celle til at være området, B2. Dette er den værdi, der vil blive kontrolleret, idet andre celler justeres, indtil denne når den korrekte værdi.

   

3. Vælg knappen for Værdi af:, og indstil en værdi på 50. Dette er den værdi, som B2 skal opnå.

   

4. I boksen med titlen Ved at ændre variable celler: indtast cellen, der indeholder radius, B1.

   

5. Forlad de andre muligheder, som de er som standard, og vælg Solve. Optimeringen udføres, værdien af B1 justeres, indtil B2 er 50, og dialogen Solver Results vises.

   

6. Vælg OK for at beholde løsningen.

   

Dette simple eksempel viste, hvordan løseren fungerer. I dette tilfælde kunne vi lettere have fået løsningen på andre måder. Dernæst vil vi se på nogle eksempler, hvor Solver giver løsninger, der ville være svære at finde på nogen anden måde.

Tilpasning af en kompleks model ved hjælp af Excel Solver Add-In

Excel har en indbygget funktion til at udføre lineær regression ved at tilpasse en lige linje gennem et sæt data. Mange almindelige ikke-lineære funktioner kan lineariseres, hvilket betyder, at lineær regression kan bruges til at tilpasse funktioner som eksponentialer. Til mere komplekse funktioner kan Solver bruges til at udføre en 'mindste kvadraters minimering'. I dette eksempel vil vi overveje at tilpasse en ligning med formen ax^b+cx^d til dataene vist nedenfor.

Dette involverer følgende trin:

1. Arranger datasættet med x-værdierne i kolonne A og y-værdierne i kolonne B.
2. Opret de 4 koefficientværdier (a, b, c og d) et sted på regnearket, disse kan gives vilkårlige startværdier.

3. Opret en kolonne med tilpassede Y-værdier ved hjælp af en ligning med formen ax^b+cx^d, som refererer til koefficienterne oprettet i trin 2 og x-værdierne i kolonne A. Bemærk, at for at kopiere formlen ned kolonnen, skal referencerne til koefficienterne være absolutte, mens referencerne til x-værdier skal være relative.

   

4. Selv om det ikke er vigtigt, kan du få en visuel indikation af, hvor god ligningen passer, ved at plotte begge y-kolonner mod x-værdierne på et enkelt XY-punktdiagram. Det giver mening at bruge markører for de oprindelige datapunkter, da disse er diskrete værdier med støj, og at bruge en linje til den tilpassede ligning.

   

5. Dernæst har vi brug for en måde at kvantificere forskellen mellem dataene og vores tilpassede ligning. Standardmåden at gøre dette på er at beregne summen af de kvadrerede forskelle. I en tredje kolonne, for hver række, trækkes den oprindelige dataværdi for Y fra den tilpassede ligningsværdi, og resultatet kvadreres. Så i D2 er værdien givet ved =(C2-B2)^2 Summen af alle disse kvadrerede værdier beregnes derefter. Da værdierne er kvadreret, kan de kun være positive.

   

6. Du er nu klar til at udføre optimeringen ved hjælp af Solver. Der er fire koefficienter, der skal justeres (a, b, c og d). Du har også en enkelt objektiv værdi at minimere, summen af de kvadrerede forskelle. Start solveren som ovenfor, og indstil solverparametrene til at referere til disse værdier som vist nedenfor.

   

7. Fjern markeringen for at Make Unconstrained Variables Non-negative, dette ville tvinge alle koefficienter til at tage positive værdier.

   

8. Vælg Solve, og gennemgå resultaterne. Diagrammet opdateres og giver en god indikation af god pasform. Hvis løseren ikke giver en god pasform ved første forsøg, kan du prøve at køre den igen. Hvis pasformen er blevet bedre, kan du prøve at løse fra de aktuelle værdier. Ellers kan du prøve at forbedre pasformen manuelt, før du løser det.

   

9. Når en god pasform er opnået, kan du afslutte solveren.

Løsning af en model iterativt

Nogle gange er der en relativt simpel ligning, som giver et output i form af noget input. Men når vi forsøger at vende problemet om, er det ikke muligt at finde en simpel løsning. For eksempel er den effekt, der forbruges af et køretøj, tilnærmelsesvis givet ved P=av + bv^3 hvor v er hastigheden, a er en koefficient for rullemodstanden og b er en koefficient for aerodynamisk modstand. Selvom dette er en ganske simpel ligning, er det ikke let at omarrangere for at give en ligning for den hastighed, køretøjet vil nå for en given effektindgang. Vi kan dog bruge Solver til iterativt at finde denne hastighed. Find for eksempel den opnåede hastighed med en effektindgang på 740 W.

1. Opstil et simpelt regneark med hastigheden, koefficienterne a og b, og effekten beregnet ud fra dem.

   

2. Start Solver og indtast power, B5, som målet. Indstil en objektiv værdi på 740, og vælg hastigheden, B2, som de variable celler, der skal ændres. Vælg solve for at starte løsningen.

   

3. Løseren justerer værdien af hastigheden, indtil effekten er meget tæt på 740, hvilket giver den hastighed, vi kræver.

   

4. At løse modeller på denne måde kan ofte være hurtigere og mindre udsat for fejl end at vende komplekse modeller.

Det kan være ret svært at forstå de forskellige muligheder, der er tilgængelige i solveren. Hvis du har svært ved at finde en fornuftig løsning, er det ofte nyttigt at anvende grænsebetingelser for de foranderlige celler. Disse er grænseværdier, ud over hvilke de ikke bør justeres. For eksempel, i det foregående eksempel, bør hastigheden ikke være mindre end nul, og det ville også være muligt at sætte en øvre grænse. Dette ville være en hastighed, du er ret sikker på, at køretøjet ikke kan køre hurtigere end. Hvis du er i stand til at sætte grænser for de udskiftelige variable celler, så får det også andre mere avancerede muligheder til at fungere bedre, såsom multistart. Dette vil køre en række forskellige løsninger, startende ved forskellige begyndelsesværdier for variabler.

Valg af løsningsmetode kan også være svært. Simplex LP er kun egnet til lineære modeller, hvis problemet ikke er lineært vil det fejle med en meddelelse om, at denne betingelse ikke var opfyldt. De to andre metoder er begge velegnede til ikke-lineære metoder. GRG ikke-lineær er den hurtigste, men dens løsning kan være meget afhængig af de indledende startbetingelser. Det har den fleksibilitet, at det ikke kræver variabler at have sat grænser. Den evolutionære løser er ofte den mest pålidelige, men den kræver, at alle variable har både øvre og nedre grænser, hvilket kan være svært at regne ud på forhånd.

Excel Solver-tilføjelsesprogrammet er et meget kraftfuldt værktøj, som kan anvendes til mange praktiske problemer. For at få fuld adgang til Excels kraft, prøv at kombinere Solver med Excel-makroer.