Wat is Excel-oplosser?

2024 Outeur: Abigail Brown | [email protected]. Laas verander: 2023-12-17 06:37

Die Excel Solver-byvoeging voer wiskundige optimalisering uit. Dit word tipies gebruik om komplekse modelle by data te pas of iteratiewe oplossings vir probleme te vind. Byvoorbeeld, jy sal dalk 'n kromme deur sommige datapunte wil pas deur 'n vergelyking te gebruik. Oplosser kan die konstantes in die vergelyking vind wat die beste pas by die data gee. 'n Ander toepassing is waar dit moeilik is om 'n model te herrangskik om die vereiste uitset die onderwerp van 'n vergelyking te maak.

Waar is Oplosser in Excel?

Die Solver-byvoeging is by Excel ingesluit, maar dit word nie altyd as deel van 'n verstekinstallasie gelaai nie. Om te kyk of dit gelaai is, kies die DATA-oortjie en soek die Solver-ikoon in die Analysis-afdeling.

As jy nie Solver onder die DATA-oortjie kan vind nie, sal jy die byvoeging moet laai:

1. Kies die FILE-oortjie en kies dan Options.

   

2. In die Options dialoogkassie kies Add-Ins van die oortjies aan die linkerkant.

   

3. Aan die onderkant van die venster, kies Excel-byvoegings van die Manage aftreklys en kies Gaan…

   

4. Merk die merkblokkie langs Solver-byvoeging en kies OK.

   

5. Die Solver-opdrag behoort nou op die DATA-oortjie te verskyn. Jy is gereed om Solver te gebruik.

   

Gebruik Solver in Excel

Kom ons begin met 'n eenvoudige voorbeeld om te verstaan wat die Oplosser doen. Stel jou voor dat ons wil weet watter radius 'n sirkel met 'n oppervlakte van 50 vierkante eenhede sal gee. Ons ken die vergelyking vir die oppervlakte van 'n sirkel (A=pi r²). Ons kan natuurlik hierdie vergelyking herrangskik om die radius te gee wat benodig word vir 'n gegewe area, maar ter wille van voorbeeld laat ons maak asof ons nie weet hoe om dit te doen nie.

Skep 'n sigblad met die radius in B1 en bereken die oppervlakte in B2 deur gebruik te maak van die vergelyking =pi()B1^2.

Ons kan die waarde handmatig in B1 aanpas totdat B2 'n waarde toon wat naby genoeg aan 50 is. Afhangende van hoe akkuraat ons moet wees, kan dit 'n praktiese benadering wees. As ons egter baie presies moet wees, sal dit lank neem om die nodige aanpassings te maak. Eintlik is dit in wese wat Solver doen. Dit maak aanpassings aan waardes in sekere selle, en kontroleer die waarde in 'n teikensel:

1. Kies DATA oortjie en Solver, om die Solver Parameters dialoogkassie te laai

2. Stel doelwit sel om die Area te wees, B2. Dit is die waarde wat nagegaan sal word, wat ander selle aanpas totdat hierdie een die korrekte waarde bereik.

   

3. Kies die knoppie vir Waarde van: en stel 'n waarde van 50. Dit is die waarde wat B2 moet bereik.

   

4. In die blokkie getiteld Deur veranderlike selle te verander: voer die sel in wat die radius bevat, B1.

   

5. Los die ander opsies soos hulle by verstek is en kies Solve. Die optimering word uitgevoer, die waarde van B1 word aangepas totdat B2 50 is en die Solver Results dialoog word vertoon.

   

6. Kies OK om die oplossing te behou.

   

Hierdie eenvoudige voorbeeld het gewys hoe die oplosser werk. In hierdie geval kon ons die oplossing makliker op ander maniere gekry het. Vervolgens sal ons na 'n paar voorbeelde kyk waar Solver oplossings gee wat moeilik sal wees om op enige ander manier te vind.

Pas 'n komplekse model met behulp van die Excel Solver-byvoeging

Excel het 'n ingeboude funksie om lineêre regressie uit te voer, wat 'n reguit lyn deur 'n stel data pas. Baie algemene nie-lineêre funksies kan gelineariseer word, wat beteken dat lineêre regressie gebruik kan word om funksies soos eksponensiële te pas. Vir meer komplekse funksies kan die Oplosser gebruik word om 'n 'kleinste vierkante minimalisering' uit te voer. In hierdie voorbeeld sal ons dit oorweeg om 'n vergelyking met die vorm ax^b+cx^d by die data hieronder getoon te pas.

Dit behels die volgende stappe:

1. Rangskik die datastel met die x-waardes in kolom A en die y-waardes in kolom B.
2. Skep die 4 koëffisiëntwaardes (a, b, c en d) iewers op die sigblad, dit kan arbitrêre beginwaardes gegee word.

3. Skep 'n kolom van toegeruste Y-waardes deur 'n vergelyking van vorm ax^b+cx^d te gebruik wat verwys na die koëffisiënte wat in stap 2 geskep is en die x-waardes in kolom A. Let daarop dat om die formule af te kopieer die kolom, moet die verwysings na die koëffisiënte absoluut wees terwyl die verwysings na x-waardes relatief moet wees.

   

4. Alhoewel dit nie noodsaaklik is nie, kan jy 'n visuele aanduiding kry van hoe goed die vergelyking pas deur beide y-kolomme teen die x-waardes op 'n enkele XY-verspreidingsgrafiek te plot. Dit maak sin om merkers vir die oorspronklike datapunte te gebruik, aangesien dit diskrete waardes met geraas is, en om 'n lyn vir die pasgemaakte vergelyking te gebruik.

   

5. Volgende het ons 'n manier nodig om die verskil tussen die data en ons gepaste vergelyking te kwantifiseer. Die standaard manier om dit te doen is om die som van die kwadraatverskille te bereken. In 'n derde kolom, vir elke ry, word die oorspronklike datawaarde vir Y van die aangepaste vergelykingswaarde afgetrek, en die resultaat word gekwadraat. Dus, in D2, word die waarde gegee deur =(C2-B2)^2 Die som van al hierdie kwadraatwaardes word dan bereken. Aangesien die waardes kwadraat is, kan hulle net positief wees.

   

6. Jy is nou gereed om die optimalisering met Solver uit te voer. Daar is vier koëffisiënte wat aangepas moet word (a, b, c en d). Jy het ook 'n enkele objektiewe waarde om te minimaliseer, die som van die kwadraatverskille. Begin die oplosser, soos hierbo, en stel die oplosserparameters om hierdie waardes te verwys, soos hieronder getoon.

   

7. Ontmerk die opsie om Onbeperkte veranderlikes nie-negatief te maak, dit sal alle koëffisiënte dwing om positiewe waardes te neem.

   

8. Kies Solve en hersien die resultate. Die grafiek sal bywerk en 'n goeie aanduiding gee van die goeie pas. As die oplosser nie goed pas by die eerste poging nie, kan jy probeer om dit weer te laat loop. As die passing verbeter het, probeer om uit die huidige waardes op te los. Andersins kan jy probeer om die passing met die hand te verbeter voordat dit opgelos word.

   

9. Sodra 'n goeie passing verkry is, kan jy die oplosser verlaat.

Om 'n model iteratief op te los

Soms is daar 'n relatief eenvoudige vergelyking wat 'n uitset gee in terme van een of ander toevoer. Wanneer ons egter probeer om die probleem om te keer, is dit nie moontlik om 'n eenvoudige oplossing te vind nie. Byvoorbeeld, die krag wat 'n voertuig verbruik word ongeveer gegee deur P=av + bv^3 waar v die snelheid is, a 'n koëffisiënt vir die rolweerstand is en b 'n koëffisiënt vir aërodinamiese sleur. Alhoewel dit 'n redelik eenvoudige vergelyking is, is dit nie maklik om te herrangskik om 'n vergelyking te gee van die snelheid wat die voertuig vir 'n gegewe kragtoevoer sal bereik nie. Ons kan egter Solver gebruik om hierdie snelheid iteratief te vind. Soek byvoorbeeld die snelheid wat bereik word met 'n kragtoevoer van 740 W.

1. Stel 'n eenvoudige sigblad op met die snelheid, die koëffisiënte a en b, en die drywing wat daaruit bereken word.

   

2. Begin die Oplosser en voer die krag, B5, in as die doelwit. Stel 'n objektiewe waarde van 740 en kies die snelheid, B2, as die veranderlike selle om te verander. Kies solve om die oplossing te begin.

   

3. Die oplosser pas die waarde van die snelheid aan totdat die drywing baie naby aan 740 is, wat die snelheid verskaf wat ons benodig.

   

4. Om modelle op hierdie manier op te los kan dikwels vinniger en minder foutgevoelig wees as om komplekse modelle om te keer.

Om die verskillende opsies beskikbaar in die oplosser te verstaan, kan nogal moeilik wees. As jy sukkel om 'n sinvolle oplossing te kry, is dit dikwels nuttig om grensvoorwaardes op die veranderlike selle toe te pas. Dit is beperkende waardes waarbuite dit nie aangepas moet word nie. Byvoorbeeld, in die vorige voorbeeld moet die snelheid nie minder as nul wees nie en dit sal ook moontlik wees om 'n boonste grens te stel. Dit sal 'n spoed wees wat jy redelik seker is dat die voertuig nie vinniger as kan ry nie. As jy grense vir die veranderlike veranderlike selle kan stel, laat dit ook ander meer gevorderde opsies beter werk, soos multistart. Dit sal 'n aantal verskillende oplossings laat loop, wat by verskillende beginwaardes vir veranderlikes begin.

Die keuse van die oplossingsmetode kan ook moeilik wees. Simplex LP is slegs geskik vir lineêre modelle, as die probleem nie lineêr is nie, sal dit misluk met 'n boodskap dat hierdie voorwaarde nie nagekom is nie. Die ander twee metodes is albei geskik vir nie-lineêre metodes. GRG Nie-lineêr is die vinnigste, maar die oplossing kan hoogs afhanklik wees van die aanvanklike begintoestande. Dit het wel die buigsaamheid dat dit nie vereis dat veranderlikes perke stel nie. Die evolusionêre oplosser is dikwels die mees betroubare, maar dit vereis dat alle veranderlikes beide boonste en onderste grense moet hê, wat moeilik kan wees om vooraf uit te werk.

Die Excel Solver-byvoeging is 'n baie kragtige hulpmiddel wat op baie praktiese probleme toegepas kan word. Om ten volle toegang tot die krag van Excel te kry, probeer om Oplosser met Excel-makro's te kombineer.