Goal Seek on üks Exceli mis-kui-analüüsi tööriistu, mis aitab teil soovitud väljundi saamiseks leida valemi õige sisendväärtuse. See näitab, kuidas üks väärtus valemis mõjutab teist. Teisisõnu, kui teil on sihtväärtus, mida soovite saavutada, võite kasutada eesmärgi otsimist, et leida selle saamiseks õige sisendväärtus.
Oletame näiteks, et saite aines kokku 75 punkti ja teil on selles aines S-hinne saamiseks vaja vähemalt 90. Õnneks on teil viimane test, mis võib aidata teil keskmist skoori tõsta. Eesmärgiotsingu abil saate välja selgitada, kui palju punkte vajate S-hinne saamiseks viimases testis.
Goal Seek kasutab katse-eksituse meetodit, et probleemile tagasi pöörduda, sisestades oletusi, kuni jõuab õige tulemuseni. Goal Seek suudab leida valemi jaoks parima sisendväärtuse mõne sekundiga, mille käsitsi väljaselgitamine oleks võtnud kaua aega. Selles artiklis näitame teile mõne näite abil, kuidas kasutada Excelis eesmärgiotsingu tööriista.
Eesmärgiotsingu funktsiooni komponendid
Eesmärgiotsingu funktsioon koosneb kolmest parameetrist, nimelt:
- Määra lahter – See on lahter, kuhu valem sisestatakse. See määrab lahtri, kuhu soovite soovitud väljundit.
- Hindama – See on siht-/soovitav väärtus, mida soovite eesmärgi otsimise toimingu tulemusena.
- Lahtrit vahetades – See määrab lahtri, mille väärtust tuleb soovitud väljundi saamiseks kohandada.
Kuidas kasutada eesmärgiotsingut Excelis: näide 1
Selle toimimise demonstreerimiseks lihtsas näites kujutage ette, et juhite puuviljaletti. Alloleval ekraanipildil kuvatakse lahtris B11 (allpool), kui palju maksis teile kioski jaoks puuviljade ostmine, ja lahtris B12 kuvatakse teie kogutulu nende puuviljade müügist. Ja lahter B13 näitab teie kasumi protsenti (20%).
Kui soovite suurendada oma kasumit 30%-ni, kuid te ei saa oma investeeringut suurendada, peate kasumi saamiseks suurendama oma tulusid. Aga kui palju? Eesmärgi otsimine aitab teil seda leida.
Kasumiprotsendi arvutamiseks kasutate lahtris B13 järgmist valemit:
=(B12-B11)/B12Nüüd soovite arvutada kasumimarginaali nii, et teie kasumiprotsent oleks 30%. Seda saate teha Exceli eesmärgiotsinguga.
Esimene asi, mida teha, on valida lahter, mille väärtust soovite kohandada. Iga kord, kui kasutate eesmärgiotsingut, peate valima lahtri, mis sisaldab valemit või funktsiooni. Meie puhul valime lahtri B13, kuna see sisaldab protsendi arvutamise valemit.
Seejärel minge vahekaardile "Andmed", klõpsake rühmas Prognoos nuppu "Mis siis, kui analüüs" ja valige "Eesmärgiotsing".
Eesmärgi otsimise dialoogiboks kuvatakse kolme väljaga:
- Määra lahter – Sisestage valemit sisaldav lahtriviide (B13). See on rakk, millel on soovitud väljund.
- Hindama – Sisestage soovitud tulemus, mida soovite saavutada (30%).
- Lahtrit vahetades – Sisestage soovitud tulemuse saavutamiseks sisendväärtuse lahtriviide, mida soovite muuta (B12). Klõpsake lihtsalt lahtril või sisestage käsitsi lahtri viide. Lahtri valimisel lisab Excel veeru tähe ja rea numbri ette märgi $, et muuta see absoluutseks lahtriks.
Kui olete lõpetanud, klõpsake selle testimiseks nuppu "OK".
Seejärel ilmub dialoogiboks „Eesmärgi otsimise olek” ja teavitab teid, kui leiate allpool näidatud lahenduse. Kui lahendus on leitud, kohandatakse sisendväärtus lahtris 'muutav (B12)' uuele väärtusele. Seda me tahame. Selles näites tegi analüüs kindlaks, et 30% kasumieesmärgi saavutamiseks peate teenima 1635,5 dollari (B12) tulu.
Klõpsake "OK" ja Excel muudab lahtrite väärtusi või klõpsake "Tühista", et lahendusest loobuda ja algne väärtus taastada.
Sisendväärtus (1635,5) lahtris B12 on see, mille saime eesmärgi (30%) saavutamiseks teada eesmärgiotsingu abil.
Asjad, mida Goal Seeki kasutamisel meeles pidada
- Lahtri määramine peab alati sisaldama valemit, mis sõltub lahtrist „Muutades lahtrit”.
- Saate kasutada Goal Seeki korraga ainult ühe lahtri sisendväärtuse korral
- Lahter „Muutades” peab sisaldama väärtust, mitte valemit.
- Kui Goal Seek ei leia õiget lahendust, näitab see lähimat väärtust, mida see suudab luua, ja teatab, et teade „Eesmärgi otsimine ei pruugi lahendust leida”.
Mida teha, kui Excel Goal Seek ei tööta?
Kui olete aga kindel, et lahendus on olemas, võite proovida seda probleemi lahendada mõne asjaga.
Kontrollige Goal Seek parameetreid ja väärtusi
Kontrollige kahte asja: kõigepealt kontrollige, kas parameeter „Määra lahter” viitab valemilahtrile. Teiseks veenduge, et valemilahter (Set cell) sõltuks otseselt või kaudselt muutuvast lahtrist.
Iteratsiooniseadete reguleerimine
Exceli sätetes saate muuta nii mitut võimalikku katset, mida Excel õige lahenduse leidmiseks teha saab, kui ka selle täpsust.
Iteratsiooni arvutamise seadete muutmiseks klõpsake vahekaartide loendis nuppu „Fail”. Seejärel klõpsake allosas "Valikud".
Klõpsake Exceli suvandite akna vasakpoolsel paanil valikut „Valemid”.
Muutke jaotises „Arvutusvalikud” järgmisi seadeid:
- Maksimaalsed iteratsioonid – näitab võimalike lahenduste arvu, mida Excel arvutab; mida suurem arv, seda rohkem iteratsioone suudab see sooritada. Näiteks kui määrate valiku „Maksimaalsed iteratsioonid” väärtuseks 150, testib Excel 150 võimalikku lahendust.
- Maksimaalne muutus – näitab tulemuste täpsust; väiksem arv annab suurema täpsuse. Näiteks kui teie sisendlahtri väärtus on võrdne '0'ga, kuid Goal Seek lõpetab arvutamise väärtusel '0,001', peaks selle muutmine väärtuseks 0,0001 probleemi lahendama.
Suurendage väärtust „Maksimaalne iteratsioon”, kui soovite, et Excel testiks rohkem võimalikke lahendusi, ja vähendage väärtust „Maksimaalne muudatus”, kui soovite täpsemat tulemust.
Allolev ekraanipilt näitab vaikeväärtust:
Ringkirja viited puuduvad
Kui valem viitab tagasi oma lahtrile, nimetatakse seda ringviiteks. Kui soovite, et Excel Goal Seek töötaks korralikult, ei tohiks valemid kasutada ringviiteid.
Exceli eesmärgi otsimise näide 2
Oletame, et laenate kelleltki 25 000 dollarit. Nad laenavad teile raha intressimääraga 7% kuus 20 kuuks, mis teeb tagasimakseks 1327 dollarit kuus. Kuid saate endale lubada maksta ainult 1000 dollarit kuus 20 kuu jooksul 7% intressiga. Goal Seek aitab teil leida laenusummat, mille igakuine makse (EMI) on 1000 dollarit.
Sisestage kolm sisendmuutujat, mida vajate PMT arvutuse arvutamiseks, st intressimäär 7%, tähtaeg 20 kuud ja põhisumma 25 000 dollarit.
Sisestage lahtrisse B5 järgmine PMT valem, mis annab teile EMI summaks 1327,97 dollarit.
Funktsiooni PMT süntaks:
=PMT (intressimäär/12, tähtaeg, põhisumma)Valem:
=PMT(B3/12,B4,-B2)
Valige lahter B5 (valemi lahter) ja avage Andmed -> Mis siis, kui analüüs -> Eesmärgiotsing.
Kasutage neid parameetreid aknas „Eesmärgiotsing“:
- Määra lahter – B5 (lahter, mis sisaldab valemit, mis arvutab EMI)
- Hindama – 1000 (valemi tulemus/eesmärk, mida otsite)
- Lahtrit vahetades – B2 (see on laenusumma, mida soovite eesmärgiväärtuse saavutamiseks muuta)
Seejärel vajutage leitud lahenduse säilitamiseks nuppu OK või sellest loobumiseks nuppu Tühista.
Analüüs ütleb, et kui soovite oma eelarvet ületada, on maksimaalne laenatav summa 18825 dollarit.
Nii kasutate Excelis Goal Seeki.