Microsoft Excel a evoluat de-a lungul anilor prin introducerea sau îmbunătățirea noilor funcții și formule cu scopul de a facilita și mai ușor de gestionat datele. Acest lucru se datorează faptului că putem confrunta cu cantități mari atât de date numerice, cât și de date sau de date text, în cazul în care dacă una dintre ele eșuează sau sunt configurate incorect, se declanșează o serie de erori care pot duce la o durere de cap.
Însă Microsoft Excel depășește cu mult funcțiile și formulele. Pentru mulți utilizatori, nu este conștient faptul că Excel va integra soluții practice și veridice prin care va fi posibilă realizarea unei ipoteze bazate pe funcția datelor introduse. Acesta este ceva cu adevărat util pentru tot ceea ce trebuie să fim siguri de cât putem cheltui, vinde sau gestiona într-un mod specific.
Acest lucru este realizat datorită funcției Solver pe care o putem instala în Microsoft Excel 2016 sau Excel 2019 și va fi de mare ajutor pentru acest tip de sarcini.
Solvetic va explica în detaliu cum să folosiți Solver în Excel 2019 și astfel să obțineți încă un punct de administrare și control în această aplicație valoroasă a suitei Office. Acest proces este similar în Microsoft Excel 2016.
Ce este Solver
Solver este un add-on dezvoltat de Microsoft ca un supliment Excel prin care va fi posibil să rulați o analiză și dacă (ce-dacă). Când implementăm și folosim Solver, va fi posibilă detectarea unei valori optime, minimă sau maximă, pentru o formulă dintr-o celulă. Această celulă (numită celulă țintă) este supusă limitărilor valorilor altor celule cu formulă dintr-o foaie de calcul.
Scopul principal al Solver este simularea și optimizarea diferitelor modele de afaceri și inginerie. Solver funcționează cu un grup de celule numite celule variabile de decizie, care sunt utilizate pentru a calcula formule în celulele țintă.
Solver este responsabil de ajustarea valorilor celulelor variabile de decizie, astfel încât acestea să îndeplinească limitele celulelor de restricție și să genereze în final rezultatul pe care îl așteptăm în celula țintă. Practic, Solver va fi un excelent aliat pentru a determina valoarea maximă sau minimă a unei celule prin modificarea valorii altor celule. Solver este format din trei elemente care sunt:
- Celule variabile
- Celulă restricționată
- Celula țintă
1. Cum să activați Solver în Excel 2016 sau 2019
Pasul 1
Primul pas pe care trebuie să-l realizăm va fi activarea suplimentului Solver în Microsoft Excel, pentru aceasta mergem în meniul Fișier unde vom vedea următoarele:
Pasul 2
Acolo facem clic pe categoria „Opțiuni”, iar în fereastră vom merge în secțiunea „Adăugări”, iar în panoul central selectăm „Rezolvare”:
Pasul 3
În partea de jos vom face clic pe butonul "Du-te" situat în câmpul "Gestionați", iar în fereastra pop-up activăm caseta "Solver":
Pasul 4
Faceți clic pe OK pentru a aplica modificările. Acum, în meniul „Date”, grupul „Analiză”, vom găsi opțiunea „Solver”:
2. Utilizați Solver în Excel 2016 sau 2019
Pasul 1
Pentru a utiliza Solver în Excel 2019 avem următoarele informații:
- O listă de sisteme sau aplicații
- O coloană cu prețul fiecăruia dintre ei
- O listă de unități pentru fiecare articol
- Cost total
Pasul 2
Acum, cu aceste date, în coloana Cost total vom înmulți prețul unitar cu unitățile cu următoarele formule:
= B7 * C7$config[ads_text5] not found
notă
Putem trage această formulă în toate celulele inferioare pentru a copia formulele.
Pasul 3
Acum, vom adăuga un nou rând numit Buget total, unde vom adăuga întreaga gamă a coloanei Total cu următoarele formule:
= SUM (D3: D8)
Pasul 4
Acum mergem în meniul Date, grup de analiză și acolo facem clic pe Solver și va fi afișat următorul asistent:
Pasul 5
Acolo vom indica celula țintă, câmpul „Set target” și în acest caz selectăm celula C11. Apoi, va fi posibil să ajustați câmpul „To”, fie valoarea maximă, minimă sau specifică, în funcție de criteriile rezultatelor, de exemplu vom activa caseta „Maxim”. Următorul pas este stabilirea celulelor variabile, pentru aceasta mergem la câmpul „Schimbarea celulelor variabile” și acolo selectăm intervalul dorit care va fi în acest caz coloana prețului unitar USD:
Pasul 6
Apoi, este timpul să definim restricțiile, de exemplu presupunem că avem o limită de 10.000 USD pentru achiziții, pentru a seta această restricție, facem clic pe butonul „Adăugare” din câmpul „Sub rezerva restricțiilor” și în fereastra pop-up Definim următoarele:
- În câmpul „Referință celulă”, introduceți celula Buget total.
- Alocăm restricția Mai puțin sau egală cu (<=).
- În câmpul Restricție alocăm valoarea maximă de utilizat în acest caz 10000.
Pasul 7
Faceți clic pe „Adăugare” pentru a aplica modificările. Acum, următoarea restricție va fi aceea că atât sistemele cât și aplicațiile sunt vândute complete, acolo facem clic pe Adăugare iar de data aceasta selectăm intervalul de Unități și selectăm valoarea „int (integer)”:
notă
Acest ultim parametru este opțional.
Pasul 8
Faceți clic pe Adăugare și, în sfârșit, vom defini cantitatea minimă a fiecărui sistem sau aplicație de utilizat, pentru aceasta avem următoarele:
- 3 Office 2019
- 2 Windows 10
- 1 macOS Mojave
- 1 Adobe Suite
- 2 Windows Server
- 2 Camtasia
Pasul 9
Pentru a face acest lucru, faceți clic pe Adăugați și faceți următoarele:
- În câmpul „Referință celulară” introducem celula pentru fiecare sistem sau aplicație din coloana Unități, de exemplu, pentru Office 2019 va fi C3, pentru Windows 10 va fi C4 etc.
- Alocăm parametrul mai mic sau egal cu (<=) și atribuim suma maximă în câmpul „Restricție”.
Pasul 7
Repetăm acest proces pentru fiecare element. Odată ce acest proces este finalizat, vom vedea ceva similar cu acesta:
notă
Opțiunile de restricție disponibile sunt:
- <= (mai mic sau egal cu
- =: la fel ca
- > =: mai mare sau egal cu
- int: număr întreg
- coș: binar
- dif: diferență
Pasul 8
Odată ce acest lucru este definit, faceți clic pe butonul „Rezolvați” pentru a executa analiza și va fi afișată următoarea fereastră:
Pasul 9
Avem următoarele opțiuni:
- Dacă dorim să păstrăm valorile soluției în foaia de calcul, vom face clic pe „Keep Solver Solution”.
- Dacă dorim să restaurăm valorile originale înainte de a face clic pe Rezolvare, vom face clic pe „Restaurarea valorilor originale”.
- Pentru a întrerupe procesul de rezoluție, apăsăm tasta Esc, Excel va actualiza foaia de calcul cu ultimele valori găsite pentru celulele variabile de decizie.
- Pentru a crea un raport bazat pe soluții după ce Solver a găsit soluția, selectăm un tip de raport în caseta Rapoarte și facem clic pe OK. Raportul este creat într-o nouă foaie de calcul a cărții, în cazul în care Solver nu găsește o soluție, opțiunea de a crea un raport nu va fi disponibilă.
- Pentru a salva valorile celulei variabilei de decizie ca scenariu de utilizat mai târziu, trebuie să faceți clic pe Salvare scenariu în caseta de dialog Rezultate Rezolvător și apoi să introduceți un nume pentru scenariu în caseta Nume scenariu.
Pasul 10
În acest caz, selectăm opțiunea „Keep Solver Solution” și facem clic pe OK pentru a vedea rezultatele:
Pasul 11
După cum putem vedea, Solver analizează automat suma maximă pe baza criteriilor selectate. Să vedem un alt exemplu despre modul în care Solver este util pentru întregul proces de analiză. În acest caz, avem următoarele date:
Pasul 12
Avem următoarele informații:
- Prețuri de articole precum procesoare, SSD și RAM.
- Subtotalul fiecărui articol a fost definit prin înmulțirea cantității cu prețul unitar.
- Am alocat vânzări totale prin adăugarea tuturor subtotalelor.
- Restricțiile au fost aplicate printr-un număr maxim de vânzări, cantitate maximă a fiecărui articol și maximum dispozitive interne (discuri și memorie).
- În partea finală am adăugat suma tuturor elementelor pe care Solver le va calcula, precum și numai filtrul elementelor interne.
Pasul 13
Ca și punctul precedent, mergem în meniul Date și în grupul de analiză selectăm Solver și acolo vom defini următorii parametri:
- În câmpul „Set target”, introduceți celula dorită care este în acest caz F5 (Vânzări totale).
- În câmpul „Schimbarea celulelor variabile” intrăm în fiecare celulă atribuită subtotalelor.
- În restricții adăugăm următoarele.
Pasul 14
În domeniul restricțiilor, utilizăm următoarele opțiuni pentru a înțelege operația:
- $ B $ 13 <= $ F $ 14: acolo indicăm că cantitatea de memorie care trebuie vândută trebuie să fie mai mică sau egală cu suma indicată în celula F14 (memorie RAM maximă).
- $ B $ 9 <= $ F $ 13: acolo indicăm că cantitatea de discuri care urmează să fie vândute trebuie să fie mai mică sau egală cu cantitatea indicată în celula F13 (discuri SSD maxime).
- $ F $ 18 <= $ F $ 11: acolo indicăm că totalul articolelor care vor fi vândute trebuie să fie mai mic sau egal cu suma indicată în celula F11 (total total de articole).
- $ F $ 19 <= $ F $ 15: acolo indicăm că cantitatea de articole interne vândute trebuie să fie mai mică sau egală cu suma indicată în celula F15 (articole interne maxime).
Pasul 15
Faceți clic pe „Rezolvați” și Solver va fi responsabil de analiză care, dacă este corect, va lansa următorul mesaj:
Pasul 16
Acolo putem selecta dacă dorim tipul de raport utilizat. Faceți clic pe OK și vom vedea analiza efectuată de Solver în Excel:
Pasul 17
Dacă am ales să utilizăm opțiunea de raport, acesta va fi disponibil pe o foaie separată și formatul acestuia va fi următorul:
Pasul 18
Solver are următoarele metode de rezoluție:
GRG neliniar
Acest tip de metodă este utilizat pentru probleme neliniare, adică în care cel puțin una dintre constrângeri este o funcție neliniară uniformă a variabilelor de decizie.
LP Simplex
Se bazează pe algoritmul Simplex dezvoltat de matematicianul american George Dantzig, această metodă este utilizată pentru rezolvarea problemelor de programare liniară, acolo modelele matematice sunt caracterizate prin relații liniare, adică constau dintr-un singur obiectiv reprezentat de o ecuație liniară. care ar trebui maximizate sau reduse la minimum.
evoluționistă
Este utilizat pentru rezolvarea celui mai complex tip de probleme de optimizare, deoarece unele dintre funcții pot fi discontinue și, în acest fel, va fi mai complex să se determine direcția în care o funcție crește sau descrește.
Pasul 19
Folosind oricare dintre aceste metode, putem vedea că partea din față este butonul „Opțiuni” care ne permite să configurăm variabilele sale după cum considerăm necesare:
Pasul 20
După executarea soluției prin Solver va fi posibilă salvarea proiectului respectiv sau încărcarea unuia deja stocat, pentru aceasta facem clic pe butonul „Încărcare / Salvare”:
Pasul 21
Următoarea fereastră va fi afișată unde definim intervalul cu modelul Solver de salvat. Faceți clic pe Salvați pentru a aplica modificările.
Astfel, am văzut cum Solver este o soluție mai mult decât practică pentru analiza și proiecția datelor care va fi foarte necesară pentru gestionarea și administrarea viitoare.
Articolul