Tikslo paieška yra vienas iš „Excel“ analizės „What-if“ įrankių, padedančių rasti teisingą formulės įvesties reikšmę, kad gautumėte norimą rezultatą. Tai parodo, kaip viena formulės reikšmė veikia kitą. Kitaip tariant, jei turite tikslinę vertę, kurią norite pasiekti, galite naudoti tikslo siekimą, kad surastumėte tinkamą įvesties vertę jai gauti.
Pavyzdžiui, tarkime, kad iš viso dalyko surinkote 75 balus ir jums reikia bent 90, kad gautumėte to dalyko S pažymį. Laimei, turite paskutinį testą, kuris gali padėti padidinti vidutinį balą. Galite naudoti tikslo siekimą, kad išsiaiškintumėte, kiek balų jums reikia baigiamajame teste, kad gautumėte S pažymį.
Tikslo siekimas naudoja bandymų ir klaidų metodą, kad atsektų problemą, įvesdama spėliones, kol bus pasiektas tinkamas rezultatas. Tikslo paieška per kelias sekundes gali rasti geriausią formulės įvesties reikšmę, kuriai būtų prireikę daug laiko išsiaiškinti rankiniu būdu. Šiame straipsnyje su kai kuriais pavyzdžiais parodysime, kaip naudoti tikslo paieškos įrankį programoje „Excel“.
Tikslo siekimo funkcijos komponentai
Tikslo siekimo funkcija susideda iš trijų parametrų, ty:
- Nustatyti langelį – Tai langelis, kuriame įvedama formulė. Jis nurodo langelį, kuriame norite gauti norimą išvestį.
- Vertinti – Tai yra tikslinė / pageidaujama vertė, kurios norite atlikti kaip tikslo paieškos operacijos rezultatas.
- Keičiant ląstelę – Tai nurodo langelį, kurio vertę reikia koreguoti, kad būtų gauta norima išvestis.
Kaip naudoti tikslo paiešką programoje „Excel“: 1 pavyzdys
Norėdami pademonstruoti, kaip tai veikia paprastu pavyzdžiu, įsivaizduokite, kad valdote vaisių kioską. Toliau esančioje ekrano kopijoje langelyje B11 (toliau) rodoma, kiek jums kainavo nusipirkti vaisių prekystaliui, o langelyje B12 rodomos visos jūsų pajamos iš tų vaisių pardavimo. O langelyje B13 rodomas jūsų pelno procentas (20%).
Jei norite padidinti savo pelną iki „30 %“, bet negalite padidinti savo investicijų, turite padidinti savo pajamas, kad gautumėte pelno. Bet iki kiek? Tikslo paieška padės jums jį rasti.
Norėdami apskaičiuoti pelno procentą, langelyje B13 naudokite šią formulę:
=(B12-B11)/B12Dabar norite apskaičiuoti pelno maržą, kad jūsų pelno procentas būtų 30%. Tai galite padaryti naudodami tikslo paiešką programoje „Excel“.
Pirmas dalykas, kurį reikia padaryti, yra pasirinkti langelį, kurio vertę norite koreguoti. Kiekvieną kartą, kai naudojate tikslo paiešką, turite pasirinkti langelį, kuriame yra formulė arba funkcija. Mūsų atveju pasirinksime langelį B13, nes jame yra procentų skaičiavimo formulė.
Tada eikite į skirtuką „Duomenys“, spustelėkite mygtuką „O kas, jei analizė“ grupėje Prognozė ir pasirinkite „Tikslo siekimas“.
Pasirodys tikslo paieškos dialogo langas su 3 laukais:
- Nustatyti langelį – Įveskite langelio nuorodą, kurioje yra formulė (B13). Tai yra ląstelė, kuri turės norimą išvestį.
- Vertinti – Įveskite norimą rezultatą, kurį bandote pasiekti (30%).
- Keičiant ląstelę – Įveskite langelio nuorodą į įvesties reikšmę, kurią norite pakeisti (B12), kad gautumėte norimą rezultatą. Tiesiog spustelėkite langelį arba rankiniu būdu įveskite langelio nuorodą. Kai pasirenkate langelį, „Excel“ pridės „$“ ženklą prieš stulpelio raidę ir eilutės numerį, kad jis būtų absoliutus langelis.
Baigę spustelėkite „Gerai“, kad patikrintumėte.
Tada pasirodys dialogo langas „Tikslo siekimo būsena“ ir informuos jus, ar radote kokį nors sprendimą, kaip parodyta toliau. Jei sprendimas buvo rastas, įvesties reikšmė „kintančiame langelyje (B12)“ bus pakoreguota į naują. Tai mes norime. Taigi šiame pavyzdyje analizė nustatė, kad norėdami pasiekti 30 % pelno tikslą, turite gauti 1635,5 USD (B12) pajamų.
Spustelėkite „Gerai“ ir „Excel“ pakeis langelių reikšmes arba spustelėkite „Atšaukti“, kad atmestumėte sprendimą ir atkurtumėte pradinę reikšmę.
Įvesties reikšmę (1635,5) langelyje B12 sužinojome naudodami tikslo siekimą, kad pasiektume tikslą (30 %).
Ką reikia atsiminti naudojant tikslo siekimą
- Nustatyti langelį visada turi turėti formulę, kuri priklauso nuo langelio „Keičiant langelį“.
- Tikslo paiešką vienu metu galite naudoti tik vienoje langelio įvesties reikšmei
- „Keičiant langelį“ turi būti reikšmė, o ne formulė.
- Jei tikslo siekimas negali rasti tinkamo sprendimo, jis parodo artimiausią vertę, kurią gali sukurti, ir praneša, kad pranešimas „Tikslo siekimas gali nerastas sprendimo“.
Ką daryti, kai „Excel“ tikslo paieška neveikia
Tačiau, jei esate tikri, kad sprendimas yra, yra keletas dalykų, kuriuos galite pabandyti išspręsti šią problemą.
Patikrinkite tikslo siekimo parametrus ir reikšmes
Turite patikrinti du dalykus: pirma, patikrinkite, ar parametras „Nustatyti langelį“ reiškia formulės langelį. Antra, įsitikinkite, kad formulės langelis (Nustatyti langelį) tiesiogiai arba netiesiogiai priklauso nuo besikeičiančios ląstelės.
Iteracijos nustatymų reguliavimas
„Excel“ nustatymuose galite pakeisti galimų „Excel“ bandymų rasti tinkamą sprendimą skaičių ir jo tikslumą.
Norėdami pakeisti iteracijos skaičiavimo nustatymus, skirtukų sąraše spustelėkite „Failas“. Tada apačioje spustelėkite „Parinktys“.
„Excel“ parinkčių lange kairiojoje srityje spustelėkite „Formulės“.
Skiltyje „Skaičiavimo parinktys“ pakeiskite šiuos nustatymus :
- Maksimalios iteracijos – nurodo galimų sprendimų skaičių, kurį „Excel“ apskaičiuos; kuo didesnis skaičius, tuo daugiau iteracijų jis gali atlikti. Pavyzdžiui, jei nustatote „Maksimalūs pakartojimai“ į 150, „Excel“ išbandys 150 galimų sprendimų.
- Maksimalus pokytis – rodo rezultatų tikslumą; mažesnis skaičius suteikia didesnį tikslumą. Pavyzdžiui, jei jūsų įvesties langelio reikšmė lygi „0“, bet tikslo paieška nustoja skaičiuoti ties „0,001“, pakeitus ją į „0,0001“, problema turėtų būti išspręsta.
Padidinkite „Maksimalių pakartojimų“ reikšmę, jei norite, kad „Excel“ išbandytų daugiau galimų sprendimų, ir sumažinkite „Maksimalaus pakeitimo“ reikšmę, jei norite tikslesnio rezultato.
Toliau pateiktoje ekrano kopijoje rodoma numatytoji vertė:
Nėra aplinkinių nuorodų
Kai formulė nurodo atgal į savo langelį, ji vadinama žiedine nuoroda. Jei norite, kad „Excel Goal Seek“ veiktų tinkamai, formulėse neturėtų būti naudojama žiedinė nuoroda.
„Excel“ tikslo paieškos 2 pavyzdys
Tarkime, kad iš ko nors skolinatės „25 000 USD“ pinigų. Jie paskolina jums pinigus už 7% palūkanų normą per mėnesį 20 mėnesių laikotarpiui, o tai sudaro „1327 USD“ per mėnesį. Bet jūs galite sau leisti mokėti tik „1000 USD“ per mėnesį 20 mėnesių su 7% palūkanomis. Tikslo paieška gali padėti jums rasti paskolos sumą, kuri sudaro 1000 USD mėnesinį įmoką (EMI).
Įveskite tris įvesties kintamuosius, kurių jums reikės norint apskaičiuoti PMT, ty 7% palūkanų normą, 20 mėnesių terminą ir 25 000 USD pagrindinę sumą.
Įveskite šią PMT formulę langelyje B5, kuri suteiks jums 1 327,97 USD EMI sumą.
PMT funkcijos sintaksė:
=PMT (palūkanų norma/12, terminas, pagrindinė suma)Formulė:
=PMT(B3/12,B4,-B2)
Pasirinkite langelį B5 (formulės langelį) ir eikite į Duomenys -> Ką daryti, jei analizė -> Tikslo paieška.
Lange „Tikslo siekimas“ naudokite šiuos parametrus:
- Nustatyti langelį – B5 (ląstelė, kurioje yra EMI apskaičiuojanti formulė)
- Vertinti – 1000 (formulės rezultatas / tikslas, kurio ieškote)
- Keičiant ląstelę – B2 (tai paskolos suma, kurią norite pakeisti, kad pasiektumėte tikslo vertę)
Tada paspauskite „Gerai“, kad rastumėte sprendimą, arba „Atšaukti“, kad jį išmestumėte.
Analizė rodo, kad maksimali pinigų suma, kurią galite pasiskolinti, yra 18825 USD, jei norite viršyti savo biudžetą.
Taip „Excel“ naudojate tikslo siekimą.