Tavoitteenhaun käyttäminen Excelissä

Tavoitteenhaku on yksi Excelin mitä jos -analyysityökaluista, jonka avulla voit löytää kaavan oikean syöttöarvon halutun tuloksen saamiseksi. Se näyttää, kuinka kaavan yksi arvo vaikuttaa toiseen. Toisin sanoen, jos sinulla on tavoitearvo, jonka haluat saavuttaa, voit käyttää tavoitehakua löytääksesi oikean syöttöarvon sen saamiseksi.

Oletetaan esimerkiksi, että sait aineesta yhteensä 75 pistettä ja tarvitset vähintään 90 saadaksesi S-arvosanan kyseisestä aineesta. Onneksi sinulla on viimeinen testi, joka saattaa auttaa sinua nostamaan keskimääräistä pistemäärääsi. Voit käyttää Tavoitehakua selvittääksesi, kuinka paljon pisteitä tarvitset viimeisessä kokeessa saadaksesi S-arvosanan.

Tavoitteenhaku käyttää yrityksen ja erehdyksen menetelmää ongelman jäljittämiseen syöttämällä arvauksia, kunnes se saavuttaa oikean tuloksen. Goal Seek löytää kaavan parhaan syöttöarvon muutamassa sekunnissa, jonka manuaalinen selvittäminen olisi kestänyt kauan. Tässä artikkelissa näytämme sinulle esimerkkien avulla, kuinka voit käyttää Tavoitehaku -työkalua Excelissä.

Tavoitteenhakutoiminnon osat

Tavoitteenhakutoiminto koostuu kolmesta parametrista, nimittäin:

  • Aseta solu – Tämä on solu, johon kaava syötetään. Se määrittää solun, johon haluat halutun tulosteen.
  • Arvostaa – Tämä on tavoite/toivottu arvo, jonka haluat tavoitteenhakutoiminnon tuloksena.
  • Vaihtamalla solua – Tämä määrittää solun, jonka arvoa on säädettävä halutun lähdön saamiseksi.

Tavoitteenhaun käyttäminen Excelissä: Esimerkki 1

Havainnollistaaksesi, miten se toimii yksinkertaisessa esimerkissä, kuvittele, että käytät hedelmäkojua. Alla olevassa kuvakaappauksessa solu B11 (alla) näyttää, kuinka paljon hedelmien ostaminen kioskillesi maksoi, ja solu B12 näyttää kokonaistulosi näiden hedelmien myynnistä. Ja solu B13 näyttää prosenttiosuuden voitostasi (20 %).

Jos haluat kasvattaa voittoasi "30 prosenttiin", mutta et pysty kasvattamaan sijoituksiasi, sinun on lisättävä tulojasi saadaksesi voittoa. Mutta kuinka paljon? Tavoitteenhaku auttaa sinua löytämään sen.

Voit laskea voittoprosentin käyttämällä seuraavaa kaavaa solussa B13:

=(B12-B11)/B12

Nyt haluat laskea voittomarginaalin niin, että voittoprosenttisi on 30%. Voit tehdä tämän Excelin Goal Seek -sovelluksella.

Ensimmäinen asia on valita solu, jonka arvoa haluat säätää. Joka kerta kun käytät Tavoitehakua, sinun on valittava solu, joka sisältää kaavan tai funktion. Tässä tapauksessa valitsemme solun B13, koska se sisältää kaavan prosenttiosuuden laskemiseksi.

Siirry sitten "Data"-välilehteen, napsauta "Entä jos analyysi" -painiketta Ennuste-ryhmässä ja valitse "Goal Sek".

Näkyviin tulee Tavoitteenhaku-valintaikkuna, jossa on 3 kenttää:

  • Aseta solu – Syötä soluviittaus, joka sisältää kaavan (B13). Tämä on solu, jolla on haluamasi tulos.
  • Arvostaa – Syötä haluttu tulos, jota yrität saavuttaa (30%).
  • Vaihtamalla solua – Syötä soluviittaus sille syöttöarvolle, jota haluat muuttaa (B12), jotta saat halutun tuloksen. Napsauta vain solua tai syötä soluviittaus manuaalisesti. Kun valitset solun, Excel lisää "$"-merkin sarakkeen kirjaimen ja rivinumeron eteen tehdäkseen siitä absoluuttisen solun.

Kun olet valmis, napsauta 'OK' testataksesi sitä.

Sitten "Goal Seek Status" -valintaikkuna avautuu ja ilmoittaa, jos se löysi alla olevan ratkaisun. Jos ratkaisu löytyy, syötearvo muuttuu solussa (B12) säädetään uuteen arvoon. Tätä me haluamme. Joten tässä esimerkissä analyysi päätti, että saavuttaaksesi 30 prosentin voittotavoitteesi sinun on saavutettava 1635,5 dollarin (B12) tuotto.

Napsauta "OK" ja Excel muuttaa solujen arvoja tai napsauta "Peruuta" hylätäksesi ratkaisun ja palauttaaksesi alkuperäisen arvon.

Syötearvon (1635,5) solussa B12 saimme selville Goal Seekin avulla saavuttaaksemme tavoitteemme (30 %).

Muistettavaa tavoitehakua käytettäessä

  • Aseta solun tulee aina sisältää kaava, joka on riippuvainen solusta "Vaihtamalla solua".
  • Voit käyttää Tavoitehakua vain yhden solun syötearvossa kerrallaan
  • Muutamalla solua -kohdan tulee sisältää arvo, ei kaava.
  • Jos Tavoitteenhaku ei löydä oikeaa ratkaisua, se näyttää lähimmän arvon, jonka se voi tuottaa, ja kertoo, että "Tavoitteenhaku ei ehkä ole löytänyt ratkaisua" -viesti.

Mitä tehdä, kun Excel Goal Seek ei toimi

Jos kuitenkin olet varma, että ratkaisu on olemassa, voit yrittää korjata tämän ongelman muutaman asian avulla.

Tarkista tavoitehaun parametrit ja arvot

Tarkista kaksi asiaa: tarkista ensin, viittaako "Aseta solu" -parametri kaavasoluun. Toiseksi varmista, että kaavan solu (Set cell) riippuu suoraan tai epäsuorasti muuttuvasta solusta.

Iterointiasetusten säätäminen

Excel-asetuksissa voit muuttaa sitä, kuinka monta mahdollista yritystä Excel voi löytää oikean ratkaisun löytämiseksi sekä sen tarkkuutta.

Jos haluat muuttaa iteraatiolaskennan asetuksia, napsauta välilehtiluettelosta "Tiedosto". Napsauta sitten "Asetukset" alareunasta.

Napsauta Excelin asetukset -ikkunassa "Kaavat" vasemmasta ruudusta.

Muuta Laskentavaihtoehdot-osiossa näitä asetuksia:

  • Maksimi iteraatiot – Se ilmaisee mahdollisten ratkaisujen määrän, jonka Excel laskee; mitä suurempi luku, sitä enemmän iteraatioita se pystyy suorittamaan. Jos esimerkiksi asetat "Iteraatioiden enimmäismääräksi" 150, Excel testaa 150 mahdollista ratkaisua.
  • Suurin muutos – Se osoittaa tulosten tarkkuuden; pienempi luku antaa suuremman tarkkuuden. Jos esimerkiksi syötesolusi arvo on yhtä suuri kuin 0, mutta Tavoitehaku lopettaa laskemisen arvossa 0,001, tämän vaihtamisen arvoon 0,0001 pitäisi ratkaista ongelma.

Suurenna Iteraatioiden enimmäisarvoa, jos haluat Excelin testaavan useampia mahdollisia ratkaisuja, ja pienennä Suurin muutos -arvoa, jos haluat tarkemman tuloksen.

Alla oleva kuvakaappaus näyttää oletusarvon:

Ei kiertokirjeitä

Kun kaava viittaa takaisin omaan soluunsa, sitä kutsutaan pyöreäksi viittaukseksi. Jos haluat Excel Goal Seekin toimivan oikein, kaavoissa ei saa käyttää pyöreää viittausta.

Excelin tavoitehaku -esimerkki 2

Oletetaan, että lainaat joltakin 25 000 dollaria rahaa. He lainaavat sinulle rahaa 7 prosentin korolla kuukaudessa 20 kuukauden ajaksi, mikä tekee takaisinmaksusta "1327 dollaria" kuukaudessa. Mutta sinulla on varaa maksaa vain "1 000 dollaria" kuukaudessa 20 kuukauden ajan 7 prosentin korolla. Goal Seek voi auttaa sinua löytämään lainasumman, joka tuottaa 1000 dollarin kuukausierän (EMI).

Syötä kolme syöttömuuttujaa, joita tarvitset PMT-laskelman laskemiseen, eli korko 7 %, 20 kuukauden laina-aika ja 25 000 dollarin pääoma.

Kirjoita seuraava PMT-kaava soluun B5, joka antaa sinulle EMI-summan 1 327,97 dollaria.

PMT-funktion syntaksi:

=PMT(korko/12, termi, pääoma)

Kaava:

=PMT(B3/12,B4,-B2)

Valitse solu B5 (kaavasolu) ja siirry kohtaan Tiedot -> Mitä jos -analyysi -> Tavoitteen haku.

Käytä näitä parametreja "Goal Seek" -ikkunassa:

  • Aseta solu – B5 (solu, joka sisältää EMI:n laskevan kaavan)
  • Arvostaa – 1000 (etsimäsi kaavan tulos/tavoite)
  • Vaihtamalla solua – B2 (tämä on lainasumma, jota haluat muuttaa tavoitearvon saavuttamiseksi)

Säilytä sitten löydetty ratkaisu valitsemalla OK tai hylkää se painamalla Peruuta.

Analyysi kertoo, että suurin lainaamasi summa on $18825, jos haluat ylittää budjettisi.

Näin käytät Goal Seekiä Excelissä.