A Goal Seek, az Excel „mi lenne, ha” elemző eszközkészletének része, egy hatékony szolgáltatás, amelyet visszafelé történő számításokhoz használnak. Lényegében, ha ismeri egy képlet kívánt eredményét, de nem biztos abban, hogy milyen bemeneti érték adja ezt az eredményt, használhatja a Célkeresést a megtalálásához. Legyen szó pénzügyi elemzőről, diákról vagy cégtulajdonosról, a Goal Seek használatának megértése jelentősen leegyszerűsítheti a problémamegoldási folyamatot. Ebben az útmutatóban megvizsgáljuk, mi az a Goal Seek, hogyan lehet hozzáférni, és különféle példákon keresztül bemutatjuk gyakorlati alkalmazásait, kezdve a hitelfizetési tervek módosításától a vizsgák minimumpontszámainak kiszámításáig és így tovább.

• 1. példa: Hitelfizetési terv módosítása
• 2. példa: A sikeres vizsga minimális pontszámának meghatározása
• 3. példa: A választási győzelemhez szükséges szavazatok kiszámítása
• 4. példa: Cél profit elérése egy vállalkozásban

Mi az a célkeresés az Excelben?

Célkeresés a Microsoft Excel szerves része, a mi lenne, ha elemző eszközkészletének része. Lehetővé teszi a felhasználók számára, hogy fordított számításokat hajtsanak végre – az adott bemenetek eredményének kiszámítása helyett Ön adja meg a kívánt eredményt, és az Excel beállít egy bemeneti értéket a cél elérése érdekében. Ez a funkció különösen hasznos olyan esetekben, amikor meg kell találnia azt a bemeneti értéket, amely egy adott eredményt eredményez.

• Egyváltozós beállítás:
  A Célkeresés egyszerre csak egy bemeneti cellaértéket módosíthat.
• Képletet igényel:
  A Célkeresés funkció alkalmazásakor a célcellának tartalmaznia kell egy képletet.
• Fenntartja a képlet integritását:
  A célkeresés nem módosítja a képletet a célcellában; megváltoztatja azt az értéket a beviteli cellában, amelytől a képlet függ.

A Goal Seek elérése Excelben

A Célkeresés funkció eléréséhez lépjen a következőre: dátum fülre kattintva Mi lenne, ha elemzés > Célkeresés. Lásd a képernyőképet:

Ezután a Célkeresés párbeszédpanel jelenik meg. Íme az Excel Célkeresés párbeszédpaneljének három lehetőségének magyarázata:

• Állítsa be a cellát: Az elérni kívánt képletet tartalmazó célcella. Ennek a cellának tartalmaznia kell egy képletet, és a Célkeresés egy másik kapcsolódó cellában módosítja az értékeket, hogy ennek a cellának az értéke elérje a kitűzött célt.
• Értékel: A kívánt célértékÁllítsa be a cellát" elérni.
• A cella megváltoztatásával: Az a beviteli cella, amelyet módosítani szeretne a Célkereséssel. Az ebben a cellában lévő érték módosul, hogy biztosítsa a "Állítsa be a cellát"találkozik a"Értékel" cél.
  
  

A következő részben részletes példákon keresztül megvizsgáljuk, hogyan használhatjuk az Excel Célkeresés funkcióját.

A Goal Seek használata példákkal

Ebben a részben négy gyakori példát mutatunk be, amelyek bemutatják a Goal Seek hasznosságát. Ezek a példák a személyes pénzügyi kiigazításoktól a stratégiai üzleti döntésekig terjednek, és betekintést nyújtanak abba, hogy ez az eszköz hogyan alkalmazható különféle valós forgatókönyvekben. Mindegyik példa arra szolgál, hogy jobban megértse, hogyan lehet hatékonyan használni a Célkeresést különböző típusú problémák megoldására.

1. példa: Hitelfizetési terv módosítása

Forgatókönyv: Egy személy 20,000 5 dollár kölcsön felvételét tervezi, és azt 5 éven belül 377.42%-os éves kamattal kívánja visszafizetni. A szükséges havi törlesztőrészlet 500 USD. Később rájön, hogy XNUMX dollárral növelheti a havi törlesztőjét. Tekintettel arra, hogy a teljes hitelösszeg és az éves kamatláb változatlan marad, hány évig tart most a hitel törlesztése?

Ahogy az alábbi táblázat mutatja:

• A B1 tartalmazza a 20000 XNUMX USD hitelösszeget.
• A B2 a kölcsön futamidejét 5 (év) tartalmazza.
• A B3 5%-os éves kamatlábat tartalmazza.
• A B5 a =PMT(B3/12,B2*12,B1) képlettel kiszámított havi törlesztőrészletet tartalmazza.
  
  

Itt megmutatom, hogyan használhatja a Célkeresés funkciót a feladat végrehajtásához.

1. lépés: Aktiválja a Célkeresés funkciót

Menj a dátum fülre kattintva Mi lenne, ha elemzés > Célkeresés.

2. lépés: Konfigurálja a Célkeresés beállításait

1. A Állítsa be a cellát mezőben válassza ki a B5 képletet.
2. A Értékel mezőbe írja be a havi ütemezett törlesztőrészletet -500 (a negatív szám fizetést jelent).
3. A Sejtcserével mezőben válassza ki a módosítani kívánt B2 cellát.
4. Kattints OK.
   
   

Eredmény

A Célkeresés állapota párbeszédpanel jelenik meg, jelezve, hogy sikerült megoldást találni. Ezzel egyidejűleg a „Cella megváltoztatásával” mezőben megadott érték a B2 cellában lecserélődik az új értékre, és a képlet cellája a változó változása alapján megkapja a célértéket. Kattintson OK a változások alkalmazásához

Most körülbelül 3.7 évbe telik, hogy kifizesse a kölcsönt.

2. példa: A sikeres vizsga minimális pontszámának meghatározása

Forgatókönyv: Egy tanulónak 5 vizsgát kell tennie, mindegyik egyenlő súllyal. A sikeres teljesítéshez a hallgatónak 70%-os átlagos pontszámot kell elérnie az összes vizsgán. Az első 4 vizsgát követően és a pontszámok ismeretében a hallgatónak ki kell számítania az ötödik vizsgán megkövetelt minimális pontszámot, hogy az összátlag elérje vagy meghaladja a 70%-ot.

Az alábbi képernyőképen látható módon:

• A B1 tartalmazza az első vizsga pontszámát.
• A B2 a második vizsga pontszámát tartalmazza.
• A B3 a harmadik vizsga pontszámát tartalmazza.
• A B4 a negyedik vizsga pontszámát tartalmazza.
• A B5 tartalmazza az ötödik vizsga pontszámát.
• A B7 az áthaladási átlagpontszám, amelyet a =(B1+B2+B3+B4+B5)/5 képlettel számítanak ki.
  
  

A cél elérése érdekében a következőképpen alkalmazhatja a Célkeresés funkciót:

1. lépés: Aktiválja a Célkeresés funkciót

Menj a dátum fülre kattintva Mi lenne, ha elemzés > Célkeresés.

2. lépés: Konfigurálja a Célkeresés beállításait

1. A Állítsa be a cellát mezőben válassza ki a B7 képletcellát.
2. A Értékel mezőbe írja be az átmenő 70%-os átlagos pontszámot.
3. A Sejtcserével mezőben válassza ki a módosítani kívánt cellát (B5).
4. Kattints OK.
   
   

Eredmény

A Célkeresés állapota párbeszédpanel jelenik meg, jelezve, hogy sikerült megoldást találni. Ezzel egyidejűleg a „Cella megváltoztatásával” mezőben megadott B7 cella automatikusan egy megfelelő értékkel kerül beírásra, és a képlet cellája a változó változása alapján megkapja a célértéket. Kattintson OK hogy elfogadja a megoldást.

Tehát a szükséges általános átlag eléréséhez és az összes vizsga sikeres letételéhez a hallgatónak legalább 71%-ot kell elérnie az utolsó vizsgán.

3. példa: A választási győzelemhez szükséges szavazatok kiszámítása

Forgatókönyv: A választások során a jelöltnek ki kell számítania a többséghez szükséges minimális szavazatok számát. A leadott szavazatok teljes számának ismeretében hány szavazatot kell kapnia a jelöltnek az 50%-ot meghaladó minimum eléréséhez és a győzelemhez?

Az alábbi képernyőképen látható módon:

• A B2 cella a választáson leadott szavazatok teljes számát tartalmazza.
• A B3 cella a jelölt által leadott szavazatok aktuális számát mutatja.
• A B4 cella tartalmazza a jelölt által elérni kívánt szavazatok kívánt többségét (%), amelyet a =B2/B1 képlettel számítanak ki.
  
  

A cél elérése érdekében a következőképpen alkalmazhatja a Célkeresés funkciót:

1. lépés: Aktiválja a Célkeresés funkciót

Menj a dátum fülre kattintva Mi lenne, ha elemzés > Célkeresés.

2. lépés: Konfigurálja a Célkeresés beállításait

1. A Állítsa be a cellát mezőben válassza ki a B4 képletcellát.
2. A Értékel mezőbe írja be a többségi szavazatok kívánt százalékát. Ebben az esetben a választás megnyeréséhez a jelöltnek az összes érvényes szavazat több mint felét (azaz 50%-ot meghaladóan) kell megszereznie, ezért 51%-ot írok be.
3. A Sejtcserével mezőben válassza ki a módosítani kívánt cellát (B2).
4. Kattints OK.
   
   

Eredmény

A Célkeresés állapota párbeszédpanel jelenik meg, jelezve, hogy sikerült megoldást találni. Ezzel egyidejűleg a „Cella módosításával” mezőben megadott B2 cella automatikusan lecserélődik az új értékre, és a képletcella a változó változása alapján megkapja a célértéket. Kattintson OK alkalmazni a változtatásokat.

Tehát a jelöltnek legalább 5100 szavazatra van szüksége a választás megnyeréséhez.

4. példa: Cél profit elérése egy vállalkozásban

Forgatókönyv: A vállalat célja, hogy elérje a konkrét, 150,000 XNUMX dolláros profitcélt az adott évre. Mind a fix, mind a változó költségeket figyelembe véve meg kell határozniuk a szükséges árbevételt. Hány egységnyi értékesítésre van szükség ennek a profitcélnak az eléréséhez?

Az alábbi képernyőképen látható módon:

• A B1 tartalmazza a fix költségeket.
• A B2 tartalmazza az egységenkénti változó költségeket.
• A B3 tartalmazza az egységárat.
• A B4 tartalmazza az eladott egységeket.
• B6 a teljes bevétel, amelyet a =B3*B4 képlettel számítanak ki.
• B7 a teljes költség, amelyet a =B1+(B2*B4) képlettel számítanak ki.
• B9 a jelenlegi értékesítési nyereség, amelyet a =B6-B7 képlettel számítanak ki.
  
  

A célnyereségen alapuló teljes értékesítési egység kiszámításához a következőképpen alkalmazhatja a Célkeresés funkciót.

1. lépés: Aktiválja a Célkeresés funkciót

Menj a dátum fülre kattintva Mi lenne, ha elemzés > Célkeresés.

2. lépés: Konfigurálja a Célkeresés beállításait

1. A Állítsa be a cellát mezőben válassza ki a nyereséget visszaadó képletcellát. Itt van a B9 cella.
2. A Értékel mezőbe írja be az 150000 célnyereséget.
3. A Sejtcserével mezőben válassza ki a módosítani kívánt cellát (B4).
4. Kattints OK.
   
   

Eredmény

Ekkor megjelenik a Célkeresés állapota párbeszédpanel, amely jelzi, hogy sikerült megoldást találni. Ezzel egyidejűleg a "Cella megváltoztatásával" mezőben megadott B4 cella automatikusan új értékre cserélődik, és a képlet cellája a változó változása alapján megkapja a célértéket. Kattintson OK elfogadni a változtatásokat.

Ennek eredményeként a cégnek legalább 6666 darabot kell eladnia ahhoz, hogy elérje a 150,000 XNUMX dolláros cél profitot.

A Goal Seek gyakori problémái és megoldásai

Ez a szakasz felsorol néhány gyakori problémát és a megfelelő megoldásokat a célkereséssel kapcsolatban.

1. A célkeresés nem talál megoldást

• Ok: Ez általában akkor fordul elő, ha a célérték semmilyen lehetséges bemeneti értékkel nem érhető el, vagy ha a kezdeti sejtés túl messze van bármely lehetséges megoldástól.
• Megoldás: Állítsa be a célértéket, hogy biztosítsa a megvalósítható tartományon belül. Ezenkívül próbáljon meg különböző kezdeti találgatásokat, amelyek közelebb állnak a várt megoldáshoz. Győződjön meg arról, hogy a "Set Cell" képlete helyes, és logikusan elő tudja állítani a kívánt "értéket".

2. Lassú számítási sebesség

• Ok: A célkeresés lassú lehet nagy adatkészletekkel, összetett képletekkel, vagy ha a kezdeti sejtés messze van a megoldástól, és több iterációt igényel.
• Megoldás: Lehetőség szerint egyszerűsítse a képleteket, és csökkentse az adatméretet. Győződjön meg arról, hogy a kezdeti tipp a lehető legközelebb áll a várt megoldáshoz, hogy csökkentse a szükséges iterációk számát.

3. Pontossági problémák

• Ok: A Goal seek használatakor észreveheti, hogy az Excel néha azt mondja, hogy talált megoldást, de ez nem pontosan az, amit szeretne – közel, de nem elég közel. Az Excel számítási pontossága és kerekítése miatt a Célkeresés nem mindig ad nagyon pontos eredményt.
  
  Például egy szám adott hatványozásának kiszámításához, és a Célkeresés segítségével keresse meg a kívánt eredményt elérő alapot. Itt megváltoztatom az A1 (Alap) cella bemeneti értékét úgy, hogy az A3 cella (a hatványozás eredménye) megfeleljen a 25-ös céleredménynek.
  
  Amint az alábbi képernyőképen is látható, a Goal Seek egy hozzávetőleges értéket ad meg, nem pedig a pontos gyökeret.
  
• Megoldás: Növelje a tizedesjegyek számát az Excel számítási beállításaiban a nagyobb pontosság érdekében, majd futtassa újra a Célkeresés funkciót. Kérjük, tegye a következőket.
  
  1. Kattints filé > Opciók megnyitni Excel beállítások ablak.
  2. választ képletek a bal oldali ablaktáblán és a Számítási lehetőségek szakaszban, növelje a tizedesjegyek számát a Maximális változás doboz. Itt változok 0.001 nak nek 0.000000001 és kattintson OK.
     
  3. Futtassa újra a Goal Seek programot, és megkapja a pontos gyökeret, ahogy az alábbi képernyőképen látható.
     

4. Korlátozások és alternatív módszerek

• Ok: A Goal Seek csak egy bemeneti értéket tud módosítani, és előfordulhat, hogy nem alkalmas több változó egyidejű módosítását igénylő egyenletekhez.
• Megoldás: Több változó módosítását igénylő forgatókönyvek esetén használja az Excel Solver-jét, amely erősebb és lehetővé teszi a megszorítások beállítását.

A Goal Seek egy hatékony eszköz a fordított számítások végrehajtásához, megkímélve Önt attól a fárasztó feladattól, hogy manuálisan tesztelje a különböző értékeket, hogy megközelítse a célt. Az ebből a cikkből származó információkkal felvértezve most már magabiztosan alkalmazhatja a Goal Seeket, új magasságokba emelve adatelemzését és hatékonyságát. Azok számára, akik szeretnének mélyebbre ásni az Excel képességeit, weboldalunk rengeteg oktatóanyagot kínál. Itt találhat további Excel tippeket és trükköket.