Függvények

Eredetileg nem állt szándékomban függvényekről külön is írni, hiszen az Excel nagyon segítőkész e téren. A függvény beszúrása ablakban például lehetőség van a függvények közti tematikus keresésre, ami elsősorban akkor jön jól, ha van sejtésünk arról, hogy mire van szükségünk, de nem tudjuk, pontosan melyik függvénnyel érhető az el. A másik eset, amikor emlékszünk a függvény nevére, de a paramétereire nem. Ekkor első körben segítségünkre van az argumentumlista, ami a szerkesztőléc alatt jelenik meg a függvény nevének megadását követően, másrészt - ha ez nem lenne elég - elindíthatjuk a függvényvarázslót is. Ha pedig még csak most ismerkedünk egy függvénnyel, érdemes megnézni a súgót, mert ott sokkal részletesebb a leírás, és gyakran példák is segítik a megértést.

A kép (nagyobb változata) külön ablakban is megtekinthető.65_full.jpg65. ábra: A függvényvarázsló, a HOL.VAN() függvény szerkesztésekor. A harmadik argumentum azért nem félkövér, mert elhagyható.

Nem látom tehát szükségét annak, hogy külön is bemutassam a versenyeken legtöbbet használt függvényeket. A megoldásfájlokban meg lehet nézni, milyen függvényekkel oldható meg az adott feladat, és akinek szüksége van rá, utánaolvashat a súgóban vagy az Office honlapján.

Az ok, amiért mégis készült egy ilyen fejezet, az, hogy érdemesnek találtam pár módszer és észrevétel ismertetését. Talán ezekkel is elősegítem a megoldások minőségének javulását vagy a ráfordított idő csökkentését. Az általam ismert típushibákat pedig konkrét példákon keresztül mutatom be.

1. Összetett képletek szerkesztése

Középfokú érettségin még csak 2-3 képlet egymásba ágyazása az elvárt, versenyeken ennél jóval magasabb az igény. Ha az ember nem elég trükkösen fog hozzá a feladathoz, könnyen elveszhet a különböző függvények argumentumainak sokaságában, vagy belekeveredhet a zárójelezésbe.

Adott egy cukrászda megrendelhető tortáinak listája. Az egyszerűség kedvéért, most más oszloppal nem is foglalkozunk, mint a torták nevét és árát tartalmazókkal.

• Adjuk meg a legdrágább torta nevét!

A függvények egymásba ágyazásának általam használt technikáját ezen a nagyon egyszerű példán mutatom be, hogy a feladat nehézsége ne terelje el a figyelmet. Egy ilyen szintű feladat akár középfokú érettségin is előfordulhat, de mivel a technika alkalmazható ennél sokkalta összetettebb képletek esetén is, nem érzem szükségét egyből nehéz példával kezdeni.

A módszer a következő: ahelyett, hogy nekiesnénk az egész feladat megoldásának, részeire szedjük azt, és azokat sorban oldjuk meg, külön segédcellákban, kihasználva mindig egy korábbi lépés eredményét. Ha végeztünk, a segédcellák eredményeit egybeépítjük, hogy aztán ki is törölhessük őket.

A kép (nagyobb változata) külön ablakban is megtekinthető.66_full.jpg66. ábra: E12-ben a kész képlet, B oszlopban a részfeladatok megoldása.

• Számítsuk ki B10-ben, hogy mennyibe kerül a legdrágább torta!
  =MAX(E3:E10)
• Határozzuk meg B11-ben, hogy az előbbi eredmény az oszlop hányadik eleme!
  =HOL.VAN(B10;E3:E10;0)
• B12-ben adjuk vissza a torták közül az előbbi cella értékével megegyező sorszámút!
  =INDEX(D3:D10;B11)

E12-ben készítjük el az előbbiek összeépítéséből adódó, segédcellák nélkül is működő képletet.

• Álljunk B12-re, és másoljuk a szerkesztőléc képletét a vágólapra!
• Illesszük be E12-be! B12-re már nincs szükség (sőt eredetileg se lett volna, csupán az átláthatóság kedvéért különítettem el ezt a két cellát).
• Most másoljuk B11 képletét a vágólapra!
• Kattintsunk E12-re, és a szerkesztőlécen jelöljük ki a képletben a B11-re történő hivatkozást! Illesszük a helyére a vágólapra másolt képletet! Így B11-re sincs már szükség.
• Az előbb leírtakhoz hasonlóan, illesszük az E12 képletében található, B10-re történő hivatkozás helyére az ottani képletet!

Ezzel a három függvényt összeépítettük, a segédcellák törölhetőek.

Még egy példát mutatnék a módszer használatára. A képlet sokkal összetettebb lesz, ami nehezíti a feladatot, ugyanakkor a kiszámított részeredmények kisebb mértékben épülnek egymásra.

Egy cég telefonhívásainak adatai alapján kell egy cellában a következő szöveget megjeleníteni:

• "Az ügyintézők beszélgetései alkalmanként átlagosan (ennyi) Ft-ba, a közép- és felsővezetők beszélgetései pedig átlagosan (ennyi) Ft-ba kerülnek. Ennek alapján megállapíthatjuk, hogy a vezetők átlagosan (drágábban/olcsóbban) beszélnek, mint a beosztottjaik."

Szerepel továbbá a feladat szövegében, hogy: "mivel nagyon kevéssé valószínű, kivételesen tekints el attól az esettől, hogy a két átlag meg is egyezhetne".

• Számoljuk ki az ügyintézők hívásainak átlagos díját például A72-ben! Az ÁTLAGHA() függvény most nem lesz segítségünkre, mert a táblázatban szereplő hívások különböző súllyal számolandók. Az osztást tehát magunknak kell elvégezni, hogy ne csak a táblázat megfelelő sorainak számával, hanem a hívások számának összegével osszunk:
  =INT(
  SZUMHA($B$2:$B$63;"ügyintéző";$G$2:$G$63)/
  SZUMHA($B$2:$B$63;"ügyintéző";$E$2:$E$63)
  )
• A73-ba, az előző képlet mintájára, számoljuk ki a nem ügyintőzők átlagos hívásdíját!
  =INT(
  SZUM(
  SZUMHA($B$2:$B$63;"felsővezető";$G$2:$G$63);
  SZUMHA($B$2:$B$63;"középvezető";$G$2:$G$63)
  )
  /
  SZUM(
  SZUMHA($B$2:$B$63;"felsővezető";$E$2:$E$63);
  SZUMHA($B$2:$B$63;"középvezető";$E$2:$E$63)
  )
  )

• Készítsünk egy olyan képletet, ami az előző két eredmény viszonya alapján a "drágábban", illetve "olcsóbban" kifejezések egyikét jeleníti meg!
  =HA(A73>A72;"drágábban";"olcsóbban")
• A feladat által megjelölt egyesített cellában a képlet az előbb készített cellákra fog hivatkozni:
  =ÖSSZEFŰZ(
  "Az ügyintézők beszélgetései alkalmanként átlagosan ";
  A72;
  " Ft-ba, a közép- és felsővezetők beszélgetései pedig átlagosan ";
  A73;
  " Ft-ba kerülnek. Ennek alapján megállapíthatjuk, hogy a vezetők átlagosan ";
  A74;
  " beszélnek, mint a beosztottjaik."
  )

Az eredmény így már helyes, de hogy szebb legyen a megoldás, tüntessük el a segédcellákat! Ehhez megint a hivatkozásokat kell cserélni, a megfelelő képletekre. Vigyázzunk, a harmadik segédcella is hivatkozik A72-re és A73-ra!

• A vágólap segítségével végezzük el a cseréket, majd töröljük a feleslegessé vált cellákat!

A kapott eredményt inkább el sem helyeztem ebben a leírásban, olyan hosszú és átláthatatlan lett. A feladatkiírás most egyébként kijelölt egy külön tartományt is, ahol a részeredményeket kiszámíthatjuk, így nem is lett volna muszáj a képletet összeépíteni. Számtalan esetben azonban a segédcellák használata pontlevonással jár.

Elgondolkodtató, vajon a mindennapokban hogyan járunk jobban. Ha nincsenek segédcelláink, nyilván sokkal szebb lesz a munkafüzetünk, a képlet viszont lehet, hogy kevésbé átlátható. Természetesen mindkét eljárás jó eredményhez vezet, versenyeken viszont sok esetben csak a segédcellák nélküli megoldásra adnak maximális pontszámot. Ugyanakkor, mivel a módszer visszafele nem működik, komoly problémát jelenthet, ha egy korábban készített, több soros képleten változtatni kell valamit: valószínűleg még a képlet készítője se fog rajta először kiigazodni. A mindennapokban gyakori módszer, hogy a segédcellákat "eltüntetjük" egy alkalmas cellaformázással, sorok elrejtésével stb. A versenyzőket viszont óva inteném ettől: nem valószínű, hogy túljárnak a feladatot javító pedagógus eszén, így a segédcellák használata miatt ez esetben is jár a pontlevonás.

Vissza a tartalomjegyzékhez