Ce este Excel Solver?

2024 Autor: Abigail Brown | [email protected]. Modificat ultima dată: 2023-12-17 06:59

Suplimentul Excel Solver efectuează optimizarea matematică. Acesta este de obicei folosit pentru a adapta modele complexe la date sau pentru a găsi soluții iterative la probleme. De exemplu, este posibil să doriți să potriviți o curbă prin anumite puncte de date, folosind o ecuație. Rezolvatorul poate găsi constantele din ecuație care oferă cea mai bună potrivire datelor. O altă aplicație este în cazul în care este dificil să rearanjați un model pentru a face din rezultatul necesar subiectul unei ecuații.

Unde este Solver în Excel?

Suplimentul Solver este inclus în Excel, dar nu este întotdeauna încărcat ca parte a unei instalări implicite. Pentru a verifica dacă este încărcat, selectați fila DATE și căutați pictograma Solver în secțiunea Analiză.

Dacă nu găsiți Solver în fila DATE, atunci va trebui să încărcați suplimentul:

1. Selectați fila FILE și apoi selectați Opțiuni.

   

2. În caseta de dialog Opțiuni, selectați Suplimente din filele din partea stângă.

   

3. În partea de jos a ferestrei, selectați Suplimente Excel din meniul drop-down Gestionați și selectați Accesați…

   

4. Bifați caseta de selectare de lângă Supliment pentru soluții și selectați OK.

   

5. Comanda Solver ar trebui să apară acum în fila DATE. Sunteți gata să utilizați Solver.

   

Utilizarea Solver în Excel

Să începem cu un exemplu simplu pentru a înțelege ce face Solver. Imaginați-vă că vrem să știm ce rază va da unui cerc cu o suprafață de 50 de unități pătrate. Cunoaștem ecuația pentru aria unui cerc (A=pi r²). Desigur, am putea rearanja această ecuație pentru a da raza necesară pentru o anumită zonă, dar, de dragul exemplului, să ne prefacem că nu știm cum să facem asta.

Creați o foaie de calcul cu raza în B1 și calculați aria în B2 folosind ecuația =pi()B1^2.

Putem ajusta manual valoarea în B1 până când B2 afișează o valoare care este suficient de aproape de 50. În funcție de cât de precis avem trebuie să fie, aceasta ar putea fi o abordare practică. Cu toate acestea, dacă trebuie să fim foarte exacti, va dura mult timp pentru a face ajustările necesare. De fapt, acest lucru este în esență ceea ce face Solver. Face ajustări la valorile din anumite celule și verifică valoarea dintr-o celulă țintă:

1. Selectați DATE fila și Solver, pentru a încărca caseta de dialog Solver Parameters

2. Setare obiectiv celulă care va fi Zona, B2. Aceasta este valoarea care va fi verificată, ajustând celel alte celule până când aceasta atinge valoarea corectă.

   

3. Selectați butonul pentru Valoarea: și setați o valoare de 50. Aceasta este valoarea pe care ar trebui să o atingă B2.

   

4. În caseta intitulată Prin schimbarea celulelor variabile: introduceți celula care conține raza, B1.

   

5. Lăsați celel alte opțiuni așa cum sunt implicite și selectați Rezolvare. Optimizarea este efectuată, valoarea lui B1 este ajustată până când B2 este 50 și se afișează dialogul Rezultatele soluției.

   

6. Selectați OK pentru a păstra soluția.

   

Acest exemplu simplu a arătat cum funcționează soluția. În acest caz, am fi putut obține mai ușor soluția în alte moduri. În continuare ne vom uita la câteva exemple în care Solver oferă soluții care ar fi greu de găsit în alt mod.

Ajustarea unui model complex folosind programul de completare Excel Solver

Excel are o funcție încorporată pentru a efectua regresia liniară, potrivind o linie dreaptă printr-un set de date. Multe funcții neliniare obișnuite pot fi liniarizate, ceea ce înseamnă că regresia liniară poate fi utilizată pentru a se potrivi funcții precum exponențiale. Pentru funcții mai complexe, Solver poate fi folosit pentru a realiza o „minimizare a celor mai mici pătrate”. În acest exemplu, vom lua în considerare potrivirea unei ecuații de forma ax^b+cx^d la datele afișate mai jos.

Acest lucru implică următorii pași:

1. Aranjați setul de date cu valorile x în coloana A și valorile y în coloana B.
2. Creați cele 4 valori ale coeficientului (a, b, c și d) undeva pe foaia de calcul, acestora li se pot da valori inițiale arbitrare.

3. Creați o coloană de valori Y ajustate, folosind o ecuație de forma ax^b+cx^d care face referire la coeficienții creați la pasul 2 și la valorile x din coloana A. Rețineți că pentru a copia formula în jos coloană, referințele la coeficienți trebuie să fie absolute, în timp ce referințele la valorile x trebuie să fie relative.

   

4. Deși nu este esențial, puteți obține o indicație vizuală a cât de bună se potrivește ecuația prin trasarea ambelor coloane y față de valorile x pe o singură diagramă de dispersie XY. Este logic să folosiți marcatori pentru punctele de date originale, deoarece acestea sunt valori discrete cu zgomot și să folosiți o linie pentru ecuația ajustată.

   

5. În continuare, avem nevoie de o modalitate de a cuantifica diferența dintre date și ecuația noastră adaptată. Modul standard de a face acest lucru este de a calcula suma diferențelor pătrate. Într-o a treia coloană, pentru fiecare rând, valoarea inițială a datelor pentru Y este scăzută din valoarea ecuației ajustate, iar rezultatul este pătrat. Deci, în D2, valoarea este dată de =(C2-B2)^2 Se calculează apoi suma tuturor acestor pătrate. Deoarece valorile sunt pătrate, ele pot fi doar pozitive.

   

6. Acum sunteți gata să efectuați optimizarea folosind Solver. Există patru coeficienți care trebuie ajustați (a, b, c și d). De asemenea, aveți o singură valoare obiectivă de minimizat, suma diferențelor pătrate. Lansați soluția, ca mai sus și setați parametrii soluției pentru a face referire la aceste valori, așa cum se arată mai jos.

   

7. Debifați opțiunea pentru Faceți variabilele neconstrânse ca nenegative, acest lucru ar forța toți coeficienții să ia valori pozitive.

   

8. Selectați Rezolvare și examinați rezultatele. Graficul se va actualiza, oferind o bună indicație a bunei potriviri. Dacă rezolutorul nu produce o potrivire bună la prima încercare, puteți încerca să îl rulați din nou. Dacă potrivirea s-a îmbunătățit, încercați să rezolvați din valorile curente. În caz contrar, puteți încerca să îmbunătățiți manual potrivirea înainte de a rezolva.

   

9. Odată ce ați obținut o potrivire bună, puteți părăsi soluția.

Rezolvarea iterativă a unui model

Uneori există o ecuație relativ simplă care oferă o ieșire în ceea ce privește o anumită intrare. Cu toate acestea, atunci când încercăm să inversăm problema, nu este posibil să găsim o soluție simplă. De exemplu, puterea consumată de un vehicul este dată aproximativ de P=av + bv^3 unde v este viteza, a este un coeficient pentru rezistența la rulare și b este un coeficient pentru rezistență aerodinamică. Deși aceasta este o ecuație destul de simplă, nu este ușor de rearanjat pentru a oferi o ecuație a vitezei pe care o va atinge vehiculul pentru o anumită putere de intrare. Cu toate acestea, putem folosi Solver pentru a găsi iterativ această viteză. De exemplu, găsiți viteza atinsă cu o putere de intrare de 740 W.

1. Configurați o foaie de calcul simplă cu viteza, coeficienții a și b și puterea calculată din aceștia.

   

2. Lansați Solver și introduceți puterea, B5, ca obiectiv. Setați o valoare obiectivă de 740 și selectați viteza, B2, ca celule variabile de schimbat. Selectați solve pentru a porni soluția.

   

3. Rezolvatorul ajustează valoarea vitezei până când puterea este foarte aproape de 740, furnizând viteza de care avem nevoie.

   

4. Rezolvarea modelelor în acest fel poate fi adesea mai rapidă și mai puțin predispusă la erori decât inversarea modelelor complexe.

Înțelegerea diferitelor opțiuni disponibile în soluție poate fi destul de dificilă. Dacă întâmpinați dificultăți în a obține o soluție sensibilă, atunci este adesea util să aplicați condiții de limită celulelor modificabile. Acestea sunt valori limită peste care nu ar trebui ajustate. De exemplu, în exemplul anterior, viteza nu ar trebui să fie mai mică decât zero și ar fi, de asemenea, posibil să se stabilească o limită superioară. Aceasta ar fi o viteză de care sunteți destul de sigur că vehiculul nu poate merge mai repede decât. Dacă puteți seta limite pentru celulele variabile modificabile, atunci face, de asemenea, să funcționeze mai bine alte opțiuni mai avansate, cum ar fi multistart. Aceasta va rula o serie de soluții diferite, începând cu valori inițiale diferite pentru variabile.

Alegerea metodei de rezolvare poate fi, de asemenea, dificilă. Simplex LP este potrivit doar pentru modelele liniare, dacă problema nu este liniară, va eșua cu un mesaj că această condiție nu a fost îndeplinită. Celel alte două metode sunt ambele potrivite pentru metodele neliniare. GRG Nonlinear este cel mai rapid, dar soluția sa poate depinde foarte mult de condițiile inițiale de pornire. Are flexibilitatea că nu necesită variabile pentru a avea limite setate. Soluția evolutivă este adesea cea mai fiabilă, dar necesită ca toate variabilele să aibă atât limite superioare, cât și inferioare, ceea ce poate fi dificil de stabilit în avans.

Suplimentul Excel Solver este un instrument foarte puternic care poate fi aplicat la multe probleme practice. Pentru a accesa pe deplin puterea Excel, încercați să combinați Solver cu macrocomenzi Excel.