Rezolvați funcția în Excel. Activați, caz de utilizare cu capturi de ecran

„Căutați o soluție” este un program de completare Excel, prin care este posibilă alegerea celei mai bune soluții la probleme pe baza restricțiilor specificate. Funcția face posibilă programarea angajaților, distribuirea costurilor sau investițiilor. Cunoașterea modului în care funcționează această funcție vă va economisi timp și efort.

Ce este Căutarea de soluții

În combinație cu diverse alte opțiuni în Excel, există o funcție mai puțin populară, dar extrem de necesară „Căutare soluție”. În ciuda faptului că găsirea acestuia nu este ușoară, cunoașterea și utilizarea lui ajută la rezolvarea multor probleme. Opțiunea prelucrează datele și oferă soluția optimă dintre cele permise. Articolul descrie modul în care funcţionează în mod direct Căutarea unei soluţii.

Cum să activați funcția „Căutați o soluție”.

În ciuda eficienței, opțiunea în cauză nu se află într-un loc proeminent în bara de instrumente sau meniul contextual. Majoritatea utilizatorilor care lucrează în Excel nu sunt conștienți de prezența acestuia. În mod implicit, această funcție este dezactivată, pentru a o afișa, efectuați următoarele acțiuni:

1. Deschideți „Fișierul” făcând clic pe numele corespunzător.
2. Faceți clic pe secțiunea „Setări”.
3. Apoi selectați subsecțiunea „Suplimente”. Toate suplimentele programului vor fi afișate aici, mai jos va apărea inscripția „Management”. În partea dreaptă a acestuia va apărea un meniu pop-up unde ar trebui să selectați „Suplimente Excel”. Apoi faceți clic pe „Go”.
   

   

4. O fereastră suplimentară „Suplimente” va fi afișată pe monitor. Bifați caseta de lângă funcția dorită și faceți clic pe OK.
5. Funcția dorită va apărea pe panglica din dreapta secțiunii „Date”.

Despre Modele

Aceste informații vor fi extrem de utile pentru cei care tocmai se familiarizează cu conceptul de „model de optimizare”. Înainte de a utiliza „Căutarea unei soluții”, se recomandă studierea materialelor privind metodele de construire a modelelor:

• varianta luata in considerare va face posibila identificarea celei mai bune metode de alocare a fondurilor pentru investitii, incarcarea spatiului, aprovizionare cu bunuri sau alte actiuni in care este necesara gasirea celei mai bune solutii.
• „Metoda optimă” într-o astfel de situație ar însemna: creșterea veniturilor, reducerea costurilor, îmbunătățirea calității etc.

Sarcini tipice de optimizare:

• Stabilirea unui plan de producție, timp în care profitul din vânzarea mărfurilor eliberate va fi maxim.
• Determinarea hărților de transport, timp în care costurile de transport sunt minimizate.
• Cauta distributia mai multor utilaje pentru diverse tipuri de lucrari, astfel incat costurile de productie sa fie reduse.
• Stabilirea celui mai scurt timp de finalizare a lucrărilor.

Important! Pentru a oficializa sarcina, este necesar să se creeze un model care să reflecte principalii parametri ai domeniului de studiu. În Excel, un model este un set de formule care utilizează variabile. Opțiunea luată în considerare caută astfel de indicatori în care funcția obiectiv este mai mare (mai mică) sau egală cu valoarea specificată.

Etapa pregătitoare

Înainte de a plasa o funcție pe panglică, trebuie să înțelegeți cum funcționează opțiunea. De exemplu, există informații despre vânzarea mărfurilor indicate în tabel. Sarcina este de a atribui o reducere pentru fiecare articol, care ar fi de 4.5 milioane de ruble. Parametrul este afișat în interiorul unei celule numită țintă. Pe baza acestuia, se calculează alți parametri.

Sarcina noastră va fi să calculăm reducerea cu care se înmulțesc sumele pentru vânzarea diferitelor produse. Aceste 2 elemente sunt conectate printr-o formulă scrisă astfel: =D13*$G$2. Unde în D13 este scrisă cantitatea totală pentru implementare și $G$2 este adresa elementului dorit.

Utilizarea funcției și configurarea acesteia

Când formula este gata, trebuie să utilizați funcția în sine direct:

1. Trebuie să comutați la secțiunea „Date” și să faceți clic pe „Căutați o soluție”.

2. Se vor deschide „Opțiuni”, unde sunt setate setările necesare. În linia „Optimizează funcția obiectiv:” trebuie să specificați celula în care este afișată suma reducerilor. Este posibil să prescrieți singur coordonatele sau să alegeți din document.

3. Apoi, trebuie să mergeți la setările altor parametri. În secțiunea „Către:”, este posibil să setați limitele maxime și minime sau un număr exact.

4. Apoi câmpul „Modificarea valorilor variabilelor:” este completat. Aici se introduc datele celulei dorite, care conține o anumită valoare. Coordonatele sunt înregistrate independent sau se face clic pe celula corespunzătoare din document.

5. Apoi se editează fila „Conform restricțiilor:”, unde sunt setate restricțiile asupra datelor aplicate. De exemplu, sunt excluse fracțiile zecimale sau numerele negative.

6. După aceea, se deschide o fereastră care vă permite să adăugați restricții în calcule. Linia inițială conține coordonatele unei celule sau ale unui întreg interval. În conformitate cu condițiile sarcinii, sunt indicate datele celulei dorite, unde este afișat indicatorul de reducere. Apoi se determină semnul de comparație. Este setat la „mai mare sau egal cu”, astfel încât valoarea finală să nu aibă semnul minus. „Limita” stabilită în rândul 3 este 0 în această situație. De asemenea, este posibil să setați o limită cu „Adăugați”. Următorii pași sunt aceiași.

7. Când pașii de mai sus sunt finalizați, limita setată apare pe cea mai mare linie. Lista poate fi mare și va depinde de complexitatea calculelor, cu toate acestea, într-o anumită situație, 1 condiție este suficientă.

8. În plus, este posibil să selectați și alte setări avansate. În partea dreaptă jos există o opțiune „Opțiuni” care vă permite să faceți acest lucru.

9. În setări, puteți seta „Precizia limitării” și „Limitele soluției”. În situația noastră, nu este nevoie să folosim aceste opțiuni.

10. Când setările sunt finalizate, funcția în sine începe - faceți clic pe „Găsiți o soluție”.

11. După ce programul efectuează calculele necesare și emite calculele finale în celulele necesare. Apoi se deschide o fereastră cu rezultatele, unde rezultatele sunt salvate / anulate, sau parametrii de căutare sunt configurați în funcție de unul nou. Când datele îndeplinesc cerințele, soluția găsită este salvată. Dacă bifați în avans caseta „Reveniți la caseta de dialog cu opțiuni de căutare a soluției”, se va deschide o fereastră cu setările funcției.

12. Există posibilitatea ca calculele să se dovedească a fi eronate sau este necesară modificarea datelor inițiale pentru a obține alți indicatori. Într-o astfel de situație, trebuie să redeschideți fereastra de setări și să verificați de două ori informațiile.
13. Când datele sunt exacte, se poate folosi o metodă alternativă. În aceste scopuri, trebuie să faceți clic pe opțiunea curentă și să selectați metoda cea mai potrivită din lista care apare:

• Găsirea unei soluții folosind un gradient generalizat pentru probleme neliniare. În mod implicit, această opțiune este utilizată, dar este posibil să se utilizeze altele.
• Găsirea de soluții pentru probleme liniare pe baza metodei simplex.
• Utilizarea căutării evolutive pentru a finaliza o sarcină.

Atenție! Când opțiunile de mai sus nu au reușit să facă față sarcinii, ar trebui să verificați din nou datele din setări, deoarece aceasta este adesea principala greșeală în astfel de sarcini.

14. Când se primește reducerea dorită, rămâne să o aplici pentru a calcula valoarea reducerilor pentru fiecare articol. În acest scop, este evidențiat elementul inițial al coloanei „Suma reducere”, se scrie formula «=D2*$G$2» și apăsați „Enter”. Semnele dolarului sunt puse jos, astfel încât atunci când formula este întinsă la liniile adiacente, G2 nu se schimbă.

15. Acum va fi obținută suma reducerii pentru articolul inițial. Apoi ar trebui să mutați cursorul peste colțul celulei, când devine un „plus”, LMB este apăsat și formula este întinsă la liniile necesare.
16. După aceea, masa va fi în sfârșit gata.

Încărcare/Salvare opțiuni de căutare

Această opțiune este utilă atunci când se aplică diferite opțiuni de constrângere.

1. În meniul Opțiuni de căutare de soluții, faceți clic pe Încărcare/Salvare.
2. Introduceți intervalul pentru zona modelului și faceți clic pe Salvare sau Încărcare.

La salvarea modelului, se introduce o referință la 1 celulă dintr-o coloană goală unde va fi plasat modelul de optimizare. În timpul încărcării modelului, se introduce o referință la întreaga gamă care conține modelul de optimizare.

Important! Pentru a salva ultimele setări în meniul Opțiuni soluție, este salvat un registru de lucru. Fiecare foaie din ea are propriile opțiuni de completare Solver. În plus, este posibil să setați mai mult de 1 sarcină pentru o foaie făcând clic pe butonul „Încărcare sau Salvare” pentru a salva sarcini individuale.

Un exemplu simplu de utilizare a Solverului

Este necesar să încărcați containerul cu containere, astfel încât masa acestuia să fie maximă. Rezervorul are un volum de 32 de metri cubi. m. O cutie umplută are o greutate de 20 kg, volumul ei este de 0,15 metri cubi. m. Cutie – 80 kg și 0,5 cu. m. Este necesar ca numărul total de containere să fie de cel puțin 110 buc. Datele sunt organizate astfel:

Variabilele modelului sunt marcate cu verde. Funcția obiectiv este evidențiată cu roșu. Restricții: după cel mai mic număr de containere (mai mare sau egal cu 110) și după greutate (=SUMA PRODUS(B8:C8;B6:C6) – greutatea totală în recipient.

Prin analogie, considerăm volumul total: =SUMA PRODUS(B7:C7;B8:C8). O astfel de formulă este necesară pentru a stabili o limită a volumului total al containerelor. Apoi, prin intermediul „Căutare soluție”, se introduc link-uri către elemente cu variabile, formule și indicatorii înșiși (sau link-uri către anumite celule). Desigur, numărul de containere este un întreg (este și o limitare). Apăsăm „Găsiți o soluție”, în urma căreia găsim un astfel de număr de containere atunci când masa totală este maximă și sunt luate în considerare toate restricțiile.

Căutarea soluției nu a reușit să găsească soluții

O astfel de notificare apare atunci când funcția în cauză nu a găsit combinații de scoruri variabile care să satisfacă fiecare constrângere. Când utilizați metoda Simplex, este foarte posibil să nu existe o soluție.

Când se folosește o metodă de rezolvare a problemelor neliniare, în toate cazurile pornind de la indicatorii inițiali ai variabilelor, aceasta indică faptul că soluția posibilă este departe de astfel de parametri. Dacă rulați funcția cu alți indicatori inițiali ai variabilelor, atunci probabil că există o soluție.

De exemplu, la utilizarea metodei neliniare, elementele tabelului cu variabile nu au fost completate, iar funcția nu a găsit soluții. Asta nu înseamnă că nu există o soluție. Acum, ținând cont de rezultatele unei anumite evaluări, alte date sunt introduse în elementele cu variabile care sunt apropiate de cele primite.

În orice situație, ar trebui să examinați inițial modelul pentru absența unui conflict de constrângeri. Adesea, acest lucru este interconectat cu selecția necorespunzătoare a raportului sau a indicatorului de limitare.

În exemplul de mai sus, indicatorul de volum maxim este de 16 metri cubi. m în loc de 32, deoarece o astfel de restricție contrazice indicatorii pentru numărul minim de locuri, deoarece va corespunde numărului de 16,5 metri cubi. m.

Concluzie

Pe baza acestui lucru, opțiunea „Căutare soluție” în Excel va ajuta la rezolvarea unor probleme specifice care sunt destul de dificil sau imposibil de rezolvat în mod obișnuit. Dificultatea în aplicarea acestei metode este că inițial această opțiune este ascunsă, motiv pentru care majoritatea utilizatorilor nu sunt conștienți de prezența ei. În plus, funcția este destul de dificil de învățat și utilizat, dar cu o cercetare adecvată, va aduce mari beneficii și va facilita calculele.