A Goal Seek használata Excelben

A Goal Seek az Excel egyik „mi lenne, ha” elemző eszköze, amely segít megtalálni a képlet megfelelő bemeneti értékét a kívánt kimenet eléréséhez. Megmutatja, hogy a képlet egyik értéke hogyan hat a másikra. Más szóval, ha van egy célérték, amelyet el szeretne érni, akkor a célkeresés segítségével megtalálhatja a megfelelő bemeneti értéket az eléréséhez.

Tegyük fel például, hogy összesen 75 pontot ért el egy tárgyból, és legalább 90-re van szüksége ahhoz, hogy S osztályzatot kapjon a tárgyból. Szerencsére van még egy utolsó tesztje, amely segíthet az átlagpontszám növelésében. A Goal Seek segítségével kiszámolhatja, mennyi pontszámra van szüksége az utolsó teszten az S osztályzat megszerzéséhez.

A Goal Seek próbálkozás-hiba módszert használ a probléma visszakövetésére úgy, hogy sejtéseket ír be, amíg el nem érkezik a megfelelő eredményre. A Goal Seek pillanatok alatt megtalálja a képlet legjobb beviteli értékét, amelyet sok időbe telt volna manuálisan kitalálni. Ebben a cikkben néhány példán keresztül bemutatjuk a Célkereső eszköz használatát az Excelben.

A célkereső funkció összetevői

A célkereső funkció három paraméterből áll, nevezetesen:

  • Állítsa be a cellát – Ez az a cella, amelybe a képlet be van írva. Megadja azt a cellát, amelyben a kívánt kimenetet szeretné elérni.
  • Értékel – Ez a cél/kívánt érték, amelyet a célkeresési művelet eredményeként szeretne.
  • A cella megváltoztatásával – Meghatározza azt a cellát, amelynek az értékét módosítani kell a kívánt kimenet eléréséhez.

A Goal Seek használata Excelben: 1. példa

Hogy egy egyszerű példán bemutassa, hogyan működik, képzelje el, hogy egy gyümölcsbódét vezet. Az alábbi képernyőképen a B11-es cella (lent) azt mutatja, hogy mennyibe került, ha gyümölcsöt vásárol az istállóba, a B12-es cella pedig a gyümölcsök eladásából származó teljes bevételét. A B13 cella pedig a nyereség százalékos arányát mutatja (20%).

Ha szeretné „30%-ra” növelni a nyereségét, de nem tudja növelni a befektetését, akkor növelnie kell bevételét, hogy profitot szerezzen. De mennyiért? A cél keresése segít megtalálni.

A profitszázalék kiszámításához a következő képletet használja a B13 cellában:

=(B12-B11)/B12

Most úgy szeretné kiszámítani a haszonkulcsot, hogy a haszon százaléka 30%. Ezt megteheti a Goal Seek segítségével az Excelben.

Első lépésként válassza ki azt a cellát, amelynek értékét módosítani szeretné. Minden alkalommal, amikor a Célkeresést használja, ki kell választania egy képletet vagy függvényt tartalmazó cellát. Esetünkben a B13 cellát választjuk, mert ez tartalmazza a százalékos arány kiszámításához szükséges képletet.

Ezután lépjen az „Adatok” fülre, kattintson a „Mi lenne, ha elemzés” gombra az Előrejelzés csoportban, és válassza a „Célkeresés” lehetőséget.

Megjelenik a Célkeresés párbeszédpanel 3 mezővel:

  • Állítsa be a cellát – Adja meg a képletet tartalmazó cellahivatkozást (B13). Ez az a cella, amely a kívánt kimenettel rendelkezik.
  • Értékel – Adja meg az elérni kívánt eredményt (30%).
  • A cella megváltoztatásával – Szúrja be a módosítani kívánt bemeneti érték cellahivatkozását (B12), hogy elérje a kívánt eredményt. Egyszerűen kattintson a cellára, vagy írja be manuálisan a cellahivatkozást. Amikor kiválasztja a cellát, az Excel hozzáadja a „$” jelet az oszlop betűje és sorszáma elé, hogy abszolút cellává váljon.

Ha végzett, kattintson az „OK” gombra a teszteléshez.

Ezután egy „Célkeresés állapota” párbeszédpanel jelenik meg, és tájékoztatja Önt, ha talált valamilyen megoldást az alábbiak szerint. Ha sikerült megoldást találni, a „Celling Cella (B12)” bemeneti értéke új értékre lesz állítva. Ezt akarjuk. Tehát ebben a példában az elemzés megállapította, hogy a 30%-os profitcél eléréséhez 1635,5 USD (B12) bevételt kell elérnie.

Kattintson az „OK” gombra, és az Excel módosítja a cellaértékeket, vagy kattintson a „Mégse” gombra a megoldás elvetéséhez és az eredeti érték visszaállításához.

A B12 cellában lévő bemeneti értéket (1635,5) a Goal Seek segítségével találtuk ki célunk elérése érdekében (30%).

Amire emlékezni kell a Goal Seek használatakor

  • A Set Cell-nek mindig tartalmaznia kell egy olyan képletet, amely a „Cella megváltoztatásával” cellától függ.
  • Egyszerre csak egy cella bemeneti értéken használhatja a Célkeresést
  • A „Cella megváltoztatásával” értéknek és nem képletnek kell lennie.
  • Ha a Célkeresés nem találja meg a megfelelő megoldást, akkor a legközelebbi értéket mutatja, amit képes előállítani, és közli, hogy a „Célkeresés nem talált megoldást” üzenet.

Mi a teendő, ha az Excel Goal Seek nem működik

Ha azonban biztos abban, hogy létezik megoldás, néhány dolgot megpróbálhat kijavítani.

Ellenőrizze a Célkeresés paramétereit és értékeit

Két dolgot érdemes ellenőrizni: először ellenőrizze, hogy a „Cella beállítása” paraméter a képletcellára vonatkozik-e. Másodszor, győződjön meg arról, hogy a képlet cellája (Set cell) közvetlenül vagy közvetve függ a változó cellától.

Az iterációs beállítások módosítása

Az Excel beállításaiban módosíthatja az Excel által a megfelelő megoldás megtalálására irányuló lehetséges próbálkozások számát és annak pontosságát.

Az iterációs számítási beállítások módosításához kattintson a „Fájl” elemre a laplistában. Ezután kattintson alul az „Opciók” gombra.

Az Excel Beállítások ablakában kattintson a „Képletek” elemre a bal oldali ablaktáblában.

A „Számítási beállítások” részben módosítsa a következő beállításokat:

  • Maximális iterációk – jelzi az Excel által kiszámított lehetséges megoldások számát; minél nagyobb a szám, annál több iterációt tud végrehajtani. Ha például a „Maximális iterációk” értéket 150-re állítja, az Excel 150 lehetséges megoldást tesztel.
  • Maximális változás – az eredmények pontosságát jelzi; a kisebb szám nagyobb pontosságot ad. Például, ha a bemeneti cella értéke „0”, de a célkeresés leállítja a számítást „0,001”-nél, akkor ennek „0,0001”-re történő módosítása megoldja a problémát.

Növelje a „Maximális iterációk” értéket, ha azt szeretné, hogy az Excel több lehetséges megoldást teszteljen, és csökkentse a „Maximális változtatás” értéket, ha pontosabb eredményt szeretne.

Az alábbi képernyőképen az alapértelmezett érték látható:

Nincsenek körlevelek

Ha egy képlet a saját cellájára hivatkozik, azt körkörös hivatkozásnak nevezzük. Ha azt szeretné, hogy az Excel Goal Seek megfelelően működjön, a képletek nem használhatnak körkörös hivatkozást.

Excel célkeresés 2. példa

Tegyük fel, hogy „25 000 USD” kölcsönt vesz fel valakitől. A pénzt havi 7%-os kamattal adják kölcsön 20 hónapos időtartamra, ami havi „1327 USD” törlesztést tesz ki. De csak havi „1000 dollárt” engedhet meg magának 20 hónapig 7%-os kamattal. A Goal Seek segíthet megtalálni azt a hitelösszeget, amely „1000 USD” havi törlesztést (EMI) eredményez.

Adja meg a három bemeneti változót, amelyekre a PMT-számítás kiszámításához szüksége lesz, azaz a 7%-os kamatlábat, a 20 hónapos futamidőt és a 25 000 dolláros tőkeösszeget.

Írja be a következő PMT-képletet a B5 cellába, amely 1327,97 USD EMI-összeget kap.

A PMT függvény szintaxisa:

=PMT (kamatláb/12, futamidő, tőke)

A képlet:

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

Válassza ki a B5 cellát (képlet cella), és lépjen az Adatok –> Mi van, ha elemzés –> Célkeresés menüpontra.

Használja ezeket a paramétereket a „Célkeresés” ablakban:

  • Állítsa be a cellát – B5 (az EMI-t kiszámító képletet tartalmazó cella)
  • Értékel – 1000 (a keresett képlet eredménye/célja)
  • A cella megváltoztatásával – B2 (ez az a hitelösszeg, amelyet a célérték elérése érdekében módosítani kíván)

Ezután nyomja meg az „OK” gombot a megtalált megoldás megtartásához, vagy a „Mégse” gombot az elvetéséhez.

Az elemzés azt mutatja, hogy a maximálisan felvehető pénzösszeg 18825 USD, ha meg akarja haladni a költségvetését.

Így használja a Goal Seek funkciót az Excelben.