Lehetőségelemzés

A következőkben olyan eszközöket mutatok be, amiket az Excel súgója nagyon találóan "mi lenne, ha" típusúaknak nevez.

1. Célérték keresése

Gyakran találkozhatunk a mindennapi életben olyan esettel, hogy tudjuk, hogy milyen értéket akarunk kapni, csak azt nem, hogy milyen kiinduló adatok szükségesek hozzá. Leggyakoribb példa, hogy ha kölcsönt veszünk fel: tudjuk, mekkora összeget kell majd visszafizetni, és azt is, hogy mennyi pénzt tudunk havonta megtakarítani. A kérdés, hogy így mennyi ideig tart majd visszafizetni. Ha a kölcsön kamatmentes lenne, könnyedén rendezhetnénk az egyenletet, és egy alkalmas osztással megkapnánk az eredményt. Általában viszont nem ilyen egyszerű a dolgunk (például az előbbi példát továbbgondolva: kamatos kölcsön esetén), mert a kívánt értéket tartalmazó cella képletét hosszadalmas lenne explicit alakra hozni, a hibázási lehetőségekről és a megoldhatóság kérdéséről nem is beszélve.

Jöhet egy másik stratégia: próbálgatás. Sajnos ez is elég hosszadalmas, és sokszor nem is elég pontos. Éppen ezért tartalmaz az Excel egy olyan funkciót, mely "helyettünk próbálgat", egyre jobban közelítve a kívánt érték felé: az Adatok/ Adateszközök panelen, a lehetőségelemzés csoportban található a célértékkeresés.

Lássunk a használatára egy egyszerű példát!

A feladat, hogy meghatározzuk, hány óra földi kiképzésen volt az a főhadnagy, aki azt állítja, hogy "pontosan tizedannyi óra földi kiképzésben részesült, mint ahány percet töltött a levegőben, és éppen 999 pontja lett". A pontokat a rang, a földi kiképzéseken, illetve levegőben töltött idő függvényében lehet kiszámolni, ennek pontos leírására a feladat korábbi részében került sor (F).

• B1-ben lesz a földi képzés óráinak száma, vagyis amit keresünk. Habár ez nem kötelező, írjunk ide egy tetszőleges számot, egy tippet! Ez segíti a funkció működésének megértését, fejleszti a becslési készséget.

Most a B2-es cellába kellene a kiszámított pontnak kerülnie, mely a Képzés munkalap AF oszlopának képletéből készíthető el a legkönnyebben. Egyrészről egyszerűsödik a képlet, mivel kihagyható az INDEX() függvény harmadik paramétere, mely a rangok közt keres, másrészt kicsit bonyolódik, mert a levegőben töltött idő most a földi képzési órák függvénye. Ami pedig még tovább nehezíti a dolgot, az az, hogy főhadnagyunk a repülési időt nem órában, hanem percben adta meg, holott a pontszám a levegőben töltött egész órákból számolódik.

Mielőtt még bárki is kétségbe esne, elárulom, hogy a képletet két lépésben – két cellában fogjuk megadni. Ennek oka azonban nem annak összetettsége, hanem az, hogy a köztes cellát a következő feladatban használni fogjuk. Persze aki ezt nem tudja, az mindent megtesz majd az esetlegesen több cellában kiszámított képletek összeépítéséért, hogy nehogy pontot veszítsen segédcellák használatáért. A rutinos versenyző azonban végigolvassa a teljes feladatleírást, mielőtt nekilátna a feladatok megoldásának.

A B2-be tehát még nem a pontszámot, hanem a levegőben töltött egész órákat fogjuk kiszámítani. Ha a földi képzési órák száma B1, akkor a főhadnagy állítása alapján a levegőben töltött percek száma B1*10. Ez órába átváltva: B1*10/60=B1/6. Ennek azonban csak az egész részét kell venni, melyre használható a KEREK.LE() vagy az INT() függvény.

• Adjuk meg B2-be az elkészített képletet:
  =INT(B1/6)!
• B3-ba jöjjön a pontszám, aminek 999-nek kell majd lennie! Mint már írtam, a korábban használt képletet kell átalakítani: kivenni az INDEX() függvény harmadik paraméterét, javítani a cellahivatkozásokat.
  =INDEX(
  Képzés!AI3:AI7;
  HOL.VAN(B1;Képzés!AH3:AH7;1)
  )
  +HA(B1>400;B1-400;0)
  +B2*10

• Válasszuk a célértékkeresés funkciót!
• A célcella B3, ennek kívánt értéke 999 (célérték). A módosuló cella az a cella, ami értéket tartalmaz (nem pedig képletet), és amitől függ a célcella értéke, tehát B1.
• Az elvégzett számítás jóváhagyása előtt az ablakot le is kell fotózni, illetve képként beilleszteni a munkalapra. A legegyszerűbb módszer Alt+PrintScreen billentyűkombinációval vágólapra helyezni az ablak fotóját, majd Ctrl+V-vel beilleszteni, de Win7 alatt rendelkezésünkre áll a Képmetsző is.

A kép (nagyobb változata) külön ablakban is megtekinthető.71_full.jpg71. ábra: A célértékkeresés ablaka.

Látható, hogy az eszköz használata milyen egyszerű, de nem szabad szem elől téveszteni, hogy a kívánt célértékhez tartozó bemenő adatot az Excel is csak próbálgatással számolja, így gyakran nem olyan pontos, mint azt várnánk. Például könnyen ellenőrizhető, hogy az x²-0,5x-0,5=0 egyenlet megoldásai az 1 és a -0,5. Ha azonban az egyenletet célérték-kereséssel oldjuk meg, akkor a gyök 1,0001697 lesz, és a célérték sem a megadott nulla: 0,0002519. Ez egyrészről egy kicsit pontatlan, másrészről semmit nem tudunk arról, hogy van-e más megoldása az egyenletnek, és ha igen, az mennyi.

Tehát a módszer nagyon hasznos, de nem mindegy, mire akarjuk használni: például egy egyenlet összes gyökeinek megkeresésére nem való. Az eszköz funkciójából adódóan "nem adatkövető", vagyis ha megváltozik a képlet, a számítást természetesen újra el kell végeztetni az eszközzel, hogy a kívánt eredményt kapjuk.

Vissza a tartalomjegyzékhez