Goal Seek este unul dintre instrumentele Excel de analiză care vă ajută să găsiți valoarea de intrare corectă a unei formule pentru a obține rezultatul dorit. Arată cum o valoare dintr-o formulă o afectează pe alta. Cu alte cuvinte, dacă aveți o valoare țintă pe care doriți să o atingeți, puteți utiliza căutarea obiectivului pentru a găsi valoarea de intrare potrivită pentru a o obține.
De exemplu, să presupunem că ați obținut un total de 75 de note la o materie și aveți nevoie de cel puțin 90 pentru a obține o notă S la materia respectivă. Din fericire, aveți un ultim test care vă poate ajuta să vă creșteți scorul mediu. Puteți folosi Goal Seek pentru a afla de cât punctaj aveți nevoie la acel test final pentru a obține nota S.
Goal Seek folosește o metodă de încercare și eroare pentru a urmări problema, introducând ipoteze până când ajunge la rezultatul corect. Goal Seek poate găsi cea mai bună valoare de intrare pentru formulă în câteva secunde, ceea ce ar fi durat mult timp pentru a o descoperi manual. În acest articol, vă vom arăta cum să utilizați instrumentul Goal Seek în Excel cu câteva exemple.
Componentele funcției de căutare a obiectivelor
Funcția de căutare a obiectivelor constă din trei parametri, și anume:
- Setați celula – Aceasta este celula în care este introdusă formula. Specifică celula în care doriți rezultatul dorit.
- A valorifica – Aceasta este valoarea țintă/dorită pe care o doriți ca rezultat al operațiunii de căutare a obiectivului.
- Prin schimbarea celulei – Aceasta specifică celula a cărei valoare trebuie ajustată pentru a obține rezultatul dorit.
Cum să utilizați căutarea obiectivelor în Excel: Exemplul 1
Pentru a demonstra cum funcționează într-un exemplu simplu, imaginați-vă că conduceți o tarabă de fructe. În captura de ecran de mai jos, celula B11 (mai jos) afișează cât te-a costat să cumperi fructe pentru tarabă, iar celula B12 arată venitul tău total din vânzarea acestor fructe. Și celula B13 arată procentul din profitul tău (20%).
Dacă doriți să vă creșteți profitul la „30%”, dar nu puteți crește investiția, așa că trebuie să vă creșteți veniturile pentru a obține profit. Dar până la cât? Căutarea obiectivului vă va ajuta să-l găsiți.
Utilizați următoarea formulă în celula B13 pentru a calcula procentul de profit:
=(B12-B11)/B12Acum, doriți să calculați marja de profit, astfel încât procentul de profit să fie de 30%. Puteți face acest lucru cu Goal Seek în Excel.
Primul lucru de făcut este să selectați celula a cărei valoare doriți să o ajustați. De fiecare dată când utilizați Goal Seek, trebuie să selectați o celulă care conține o formulă sau o funcție. În cazul nostru, vom selecta celula B13 deoarece conține formula pentru a calcula procentul.
Apoi accesați fila „Date”, faceți clic pe butonul „Și ce se întâmplă dacă analiză” din grupul Prognoză și selectați „Căutare obiectiv”.
Caseta de dialog Căutare obiectiv va apărea cu 3 câmpuri:
- Setați celula – Introduceți referința de celulă care conține formula (B13). Aceasta este celula care va avea rezultatul dorit.
- A valorifica – Introduceți rezultatul dorit pe care încercați să-l obțineți (30%).
- Prin schimbarea celulei – Introduceți referința de celulă pentru valoarea de intrare pe care doriți să o modificați (B12) pentru a ajunge la rezultatul dorit. Pur și simplu faceți clic pe celulă sau introduceți manual referința celulei. Când selectați celula, Excel va adăuga semnul „$” înaintea literei coloanei și a numărului rândului pentru a face din aceasta o celulă absolută.
Când ați terminat, faceți clic pe „OK” pentru a-l testa.
Apoi va apărea o casetă de dialog „Starea de căutare a obiectivului” și vă va informa dacă a găsit vreo soluție, așa cum se arată mai jos. Dacă a fost găsită o soluție, valoarea de intrare în „celula în schimbare (B12)” va fi ajustată la o nouă valoare. Asta ne dorim. Deci, în acest exemplu, analiza a determinat că, pentru a vă atinge obiectivul de profit de 30%, trebuie să obțineți un venit de 1635,5 USD (B12).
Faceți clic pe „OK” și Excel va schimba valorile celulei sau faceți clic pe „Anulați” pentru a renunța la soluție și a restabili valoarea inițială.
Valoarea de intrare (1635,5) din celula B12 este ceea ce am aflat folosind Goal Seek pentru a ne atinge obiectivul (30%).
Lucruri de reținut când utilizați Goal Seek
- Celula Set trebuie să conțină întotdeauna o formulă care depinde de celula „Prin schimbarea celulei”.
- Puteți utiliza Goal Seek numai pentru o singură valoare de intrare în celulă la un moment dat
- „Prin schimbarea celulei” trebuie să conțină o valoare și nu o formulă.
- Dacă Goal Seek nu găsește soluția corectă, arată cea mai apropiată valoare pe care o poate produce și vă spune că mesajul „Căutarea obiectivului poate să nu fi găsit o soluție”.
Ce trebuie să faceți când Excel Goal Seek nu funcționează
Cu toate acestea, dacă sunteți sigur că există o soluție, există câteva lucruri pe care le puteți încerca să remediați această problemă.
Verificați parametrii și valorile Goal Seek
Există două lucruri pe care ar trebui să le verificați: mai întâi, verificați dacă parametrul „Setare celulă” se referă la celula formulei. În al doilea rând, asigurați-vă că celula formulei (celula Set) depinde, direct sau indirect, de celula în schimbare.
Ajustarea setărilor de iterație
În setările Excel, puteți modifica numărul de încercări posibile pe care Excel le poate face pentru a găsi soluția potrivită, precum și acuratețea acesteia.
Pentru a modifica setările de calcul al iterației, faceți clic pe „Fișier” din lista de file. Apoi, faceți clic pe „Opțiuni” în partea de jos.
În fereastra Opțiuni Excel, faceți clic pe „Formule” în panoul din stânga.
În secțiunea „Opțiuni de calcul”, modificați aceste setări:
- Iterații maxime – Indică numărul de soluții posibile pe care Excel le va calcula; cu cât numărul este mai mare, cu atât poate efectua mai multe iterații. De exemplu, dacă setați „Maximum Iterations” la 150, Excel testează 150 de soluții posibile.
- Modificare maximă – Indică acuratețea rezultatelor; numărul mai mic oferă o precizie mai mare. De exemplu, dacă valoarea celulei de intrare este egală cu „0”, dar Căutarea obiectivului nu mai calculează la „0,001”, schimbarea acesteia la „0,0001” ar trebui să rezolve problema.
Măriți valoarea „Maximum Iterations” dacă doriți ca Excel să testeze mai multe soluții posibile și micșorați valoarea „Maximum Change” dacă doriți un rezultat mai precis.
Captura de ecran de mai jos arată valoarea implicită:
Fără referințe circulare
Când o formulă se referă la propria sa celulă, se numește referință circulară. Dacă doriți ca Excel Goal Seek să funcționeze corect, formulele nu ar trebui să utilizeze referințe circulare.
Exemplul 2 de căutare a obiectivelor în Excel
Să presupunem că împrumuți bani de „25.000 USD” de la cineva. Îți împrumută banii la o rată a dobânzii de 7% pe lună pentru o perioadă de 20 de luni, ceea ce face rambursarea „1327 USD” pe lună. Dar vă puteți permite să plătiți doar „1.000 USD” pe lună timp de 20 de luni cu o dobândă de 7%. Goal Seek vă poate ajuta să găsiți suma împrumutului care produce o plată lunară (EMI) de „1000 USD”.
Introduceți cele trei variabile de intrare de care veți avea nevoie pentru a calcula calculul PMT, adică rata dobânzii de 7%, termen de 20 de luni și suma principală de 25.000 USD.
Introduceți următoarea formulă PMT în celula B5, care vă va oferi o sumă EMI de 1.327,97 USD.
Sintaxa funcției PMT:
=PMT(Rata dobânzii/12, Termen, Principal)Formula:
=PMT(B3/12;B4;-B2)
Selectați celula B5 (celula formulă) și accesați Date –> What If Analysis –> Goal Seek.
Utilizați acești parametri în fereastra „Căutare obiectiv”:
- Setați celula – B5 (celula care conține formula care calculează EMI)
- A valorifica – 1000 (rezultatul formulei/obiectivul pe care îl căutați)
- Prin schimbarea celulei – B2 (aceasta este suma împrumutului pe care doriți să o modificați pentru a atinge valoarea obiectivului)
Apoi, apăsați „OK” pentru a păstra soluția găsită sau „Anulați” pentru a o renunța.
Analiza vă spune că suma maximă de bani pe care o puteți împrumuta este de 18825 USD dacă doriți să depășiți bugetul.
Așa folosești Goal Seek în Excel.