Què és Excel Solver?

2024 Autora: Abigail Brown | [email protected]. Última modificació: 2023-12-17 06:42

El complement de solucionador d'Excel realitza una optimització matemàtica. Normalment s'utilitza per ajustar models complexos a dades o trobar solucions iteratives als problemes. Per exemple, és possible que vulgueu ajustar una corba a través d'alguns punts de dades mitjançant una equació. El solucionador pot trobar les constants de l'equació que s'ajusten millor a les dades. Una altra aplicació és on és difícil reorganitzar un model per fer que la sortida requerida sigui objecte d'una equació.

On és Solver a Excel?

El complement Solver s'inclou amb Excel, però no sempre es carrega com a part d'una instal·lació predeterminada. Per comprovar si està carregat, seleccioneu la pestanya DATA i cerqueu la icona Solver a la secció Anàlisi.

Si no trobeu Solver a la pestanya DADES, haureu de carregar el complement:

1. Seleccioneu la pestanya FILE i després seleccioneu Opcions.

   

2. Al quadre de diàleg Opcions seleccioneu Complements a les pestanyes del costat esquerre.

   

3. A la part inferior de la finestra, seleccioneu Complements d'Excel al menú desplegable Gestiona i seleccioneu Vés…

   

4. Marqueu la casella de selecció al costat de Complement de solucionador i seleccioneu D'acord.

   

5. L'ordre Solver ara hauria d'aparèixer a la pestanya DATA. Ja esteu preparat per utilitzar Solver.

   

Ús de Solver a Excel

Comencem amb un exemple senzill per entendre què fa el Solver. Imagineu que volem saber quin radi donarà un cercle amb una àrea de 50 unitats quadrades. Coneixem l'equació de l'àrea d'un cercle (A=pi r²). Per descomptat, podríem reorganitzar aquesta equació per donar el radi necessari per a una àrea determinada, però per exemple, fingim que no sabem com fer-ho.

Creeu un full de càlcul amb el radi a B1 i calculeu l'àrea a B2 utilitzant l'equació =pi()B1^2.

Podríem ajustar manualment el valor a B1 fins que B2 mostri un valor prou proper a 50. Depenent de la precisió que fem cal ser, aquest podria ser un enfocament pràctic. Tanmateix, si hem de ser molt exactes, es trigarà molt de temps a fer els ajustos necessaris. De fet, això és essencialment el que fa Solver. Fa ajustos als valors de determinades cel·les i comprova el valor d'una cel·la objectiu:

1. Seleccioneu la pestanya DATA i Solver per carregar el quadre de diàleg Solver Parameters

2. Estableix l'objectiu cel·la perquè sigui l'àrea, B2. Aquest és el valor que es comprovarà, ajustant altres cel·les fins que aquesta arribi al valor correcte.

   

3. Seleccioneu el botó per a Valor de: i estableix un valor de 50. Aquest és el valor que hauria d'aconseguir B2.

   

4. Al quadre titulat En canviar les cel·les variables: introduïu la cel·la que conté el radi, B1.

   

5. Deixa les altres opcions com estan per defecte i selecciona Resol. Es realitza l'optimització, el valor de B1 s'ajusta fins que B2 sigui 50 i es mostra el diàleg Resultats del solucionador..

   

6. Seleccioneu D'acord per mantenir la solució.

   

Aquest exemple senzill mostra com funciona el solucionador. En aquest cas, podríem haver aconseguit més fàcilment la solució d' altres maneres. A continuació, veurem alguns exemples en què Solver dóna solucions que serien difícils de trobar d'una altra manera.

Ajust d'un model complex amb el complement de solucionador d'Excel

Excel té una funció integrada per realitzar una regressió lineal, ajustant una línia recta a través d'un conjunt de dades. Moltes funcions no lineals comunes es poden linealitzar, el que significa que la regressió lineal es pot utilitzar per ajustar funcions com ara les exponencials. Per a funcions més complexes, el Solver es pot utilitzar per realitzar una "minimització de mínims quadrats". En aquest exemple, considerarem ajustar una equació de la forma ax^b+cx^d a les dades que es mostren a continuació.

Això implica els passos següents:

1. Disposeu el conjunt de dades amb els valors x a la columna A i els valors y a la columna B.
2. Creeu els 4 valors de coeficients (a, b, c i d) en algun lloc del full de càlcul, es poden donar valors inicials arbitraris.

3. Creeu una columna de valors Y ajustats, utilitzant una equació de forma ax^b+cx^d que fa referència als coeficients creats al pas 2 i als valors x a la columna A. Tingueu en compte que per copiar la fórmula cap avall la columna, les referències als coeficients han de ser absolutes mentre que les referències als valors x han de ser relatives.

   

4. Tot i que no és essencial, podeu obtenir una indicació visual de com de bo s'ajusta l'equació traçant les dues columnes y amb els valors x en un únic gràfic de dispersió XY. Té sentit utilitzar marcadors per als punts de dades originals, ja que són valors discrets amb soroll, i utilitzar una línia per a l'equació ajustada.

   

5. A continuació, necessitem una manera de quantificar la diferència entre les dades i la nostra equació ajustada. La forma estàndard de fer-ho és calcular la suma de les diferències al quadrat. En una tercera columna, per a cada fila, el valor de les dades originals de Y es resta del valor de l'equació ajustada i el resultat es quadrat. Així, a D2, el valor ve donat per =(C2-B2)^2 A continuació es calcula la suma de tots aquests valors quadrats. Com que els valors són al quadrat, només poden ser positius.

   

6. Ara esteu preparat per realitzar l'optimització amb Solver. Hi ha quatre coeficients que cal ajustar (a, b, c i d). També teniu un únic valor objectiu per minimitzar, la suma de les diferències al quadrat. Inicieu el solucionador, com es mostra a d alt, i configureu els paràmetres del solucionador per fer referència a aquests valors, tal com es mostra a continuació.

   

7. Desmarqueu l'opció per Fer que les variables sense restriccions no siguin negatives, això obligaria a tots els coeficients a prendre valors positius.

   

8. Seleccioneu Resol i reviseu els resultats. El gràfic s'actualitzarà donant una bona indicació de la bondat d'ajust. Si el solucionador no produeix un bon ajust al primer intent, podeu provar de tornar-lo a executar. Si l'ajust ha millorat, proveu de resoldre a partir dels valors actuals. En cas contrari, podeu provar de millorar manualment l'ajust abans de resoldre'l.

   

9. Un cop s'hagi obtingut un bon ajust, podeu sortir del solucionador.

Resolució iterativa d'un model

De vegades hi ha una equació relativament simple que dóna una sortida en termes d'alguna entrada. Tanmateix, quan intentem invertir el problema no és possible trobar una solució senzilla. Per exemple, la potència consumida per un vehicle ve donada aproximadament per P=av + bv^3 on v és la velocitat, a és un coeficient per a la resistència al rodament i b és un coeficient per a arrossegament aerodinàmic. Tot i que aquesta és una equació bastant senzilla, no és fàcil reorganitzar-la per donar una equació de la velocitat que arribarà el vehicle per a una entrada de potència determinada. Tanmateix, podem utilitzar Solver per trobar iterativament aquesta velocitat. Per exemple, trobeu la velocitat assolida amb una potència d'entrada de 740 W.

1. Configureu un full de càlcul senzill amb la velocitat, els coeficients a i b i la potència calculada a partir d'ells.

   

2. Llenceu el Solver i introduïu el poder, B5, com a objectiu. Establiu un valor objectiu de 740 i seleccioneu la velocitat, B2, com a cel·les variables a canviar. Seleccioneu solve per iniciar la solució.

   

3. El solucionador ajusta el valor de la velocitat fins que la potència és molt propera a 740, proporcionant la velocitat que necessitem.

   

4. Sovint, resoldre models d'aquesta manera pot ser més ràpid i menys propens a errors que invertir models complexos.

Entendre les diferents opcions disponibles al solucionador pot ser força difícil. Si teniu dificultats per obtenir una solució raonable, sovint és útil aplicar condicions de límit a les cel·les canviables. Aquests són valors límit més enllà dels quals no s'han d'ajustar. Per exemple, a l'exemple anterior, la velocitat no hauria de ser inferior a zero i també seria possible establir un límit superior. Aquesta seria una velocitat que segur que el vehicle no pot anar més ràpid. Si podeu establir límits per a les cel·les variables canviables, també fa que altres opcions més avançades funcionin millor, com ara l'inici múltiple. Això executarà una sèrie de solucions diferents, començant per diferents valors inicials per a les variables.

També pot ser difícil escollir el mètode de resolució. Simplex LP només és adequat per a models lineals, si el problema no és lineal, fallarà amb un missatge que aquesta condició no s'ha complert. Els altres dos mètodes són adequats per a mètodes no lineals. GRG Nonlinear és el més ràpid, però la seva solució pot dependre molt de les condicions inicials inicials. Té la flexibilitat que no requereix que les variables tinguin límits establerts. El solucionador evolutiu és sovint el més fiable, però requereix que totes les variables tinguin límits superiors i inferiors, cosa que pot ser difícil de resoldre per endavant.

El complement Excel Solver és una eina molt potent que es pot aplicar a molts problemes pràctics. Per accedir completament al poder d'Excel, proveu de combinar Solver amb macros d'Excel.