KURZUS: Informatikai rendszerek alapjai

MODUL: Táblázatkezelés

2. lecke: Címzési módok és függvények

Cél: A lecke először a címzési módokat tárgyalja, majd a függvények használatának első részét tekinti át. Az anyag elsajátítása - a téma rendkívül szerteágazó volta miatt - csak akkor tekinthető sikeresnek, ha a gyakorlati feladatokat is bizonyos jártassággal meg tudja oldani a tanuló. Ennek megfelelően a lecke önálló feldolgozására fordítandó idő erősen függ az előképzettségtől, diákonként akár többszörös szorzók is adódhatnak.

A különböző hivatkozási típusok elsajátítása nélkül a táblázatkezelő használhatatlan, ezek szükségesek ahhoz, hogy a hallgató ne csak elrendezés táblaként, hanem számolótáblaként is tudja használni az Excelt. A leckében a címzések mellett bemutatjuk a képleteket, kifejezéseket leegyszerűsítő, áttekinthetőbbé tévő nevek használatát is.

A lecke második részében mutatjuk be, hogyan kell a függvényeket felépíteni, használni, majd a mérnöki, gazdasági feladatokban gyakran előforduló függvények egy csoportjával ismerkedhet meg a hallgató.

Követelmények: Ön akkor sajátította el megfelelően a tananyagot, ha képes az Excel segítségével

  • az abszolút, relatív, vegyes hivatkozási módokat alkalmazni a képletek felépítésénél,
  • új neveket létrehozni, módosítani és törölni,
  • a táblázatkezelőben használható neveket kiválasztani,
  • függvényeket felépíteni,
  • egy adott feladat megoldásához szükséges függvényt megkeresni,
  • matematikai, logikai, statisztikai függvényeket a célnak megfelelően használni,
  • a Ha függvény paramétereit felsorolni,
  • megadott egyszerűbb függvényeket táblázatkezelő nélkül kiértékelni.

Időszükséglet: A tananyag elsajátításához (a feladatok megoldásával együtt) hozzávetőlegesen 3 órára lesz szüksége.

Kulcsfogalmak

  • relatív hivatkozás
  • abszolút hivatkozás
  • vegyes hivatkozás
  • függvények
  • függvényvarázsló
  • paraméterlista
  • argumentum
  • Névkezelő
  • matematikai, logikai, statisztikai függvények
Relatív, vegyes és abszolút címek

Ha olyan cellatartalmat másolunk, amely hivatkozást is tartalmaz, akkor azt tapasztaljuk, hogy a hivatkozás a másolás során bizonyos esetekben módosul.

Írjunk be például az A1 cellába egy egyest, az A2-be pedig a következő képletet: =A1+1.

Ennek eredményeképpen A2 cellaterületén eggyel nagyobb értéket látunk (a cella tartalma nem a 2 szám, hanem egy képlet, ami hivatkozást tartalmaz).

Másoljuk ezt a képletet lefelé néhány sorba! Az első néhány természetes számot fogjuk látni az oszlopban.

Ennek az az oka, hogy a hivatkozás a cellák között egy logikai kapcsolatot határoz meg, ami a másolás során öröklődik. Így A2 cella ugyanolyan "kapcsolatban" áll az A1 cellával, mint A3 az A2-vel, A4 az A3-mal és így tovább. Az A3 cella értéke A2+1=3 lesz, az A4 értéke 4 stb.

Természetesen ez a feladat a táblázatkezelőkben más egyszerű eszközökkel is megoldható:

  • Beírjuk a sorozat első két elemét két cellába, kijelöljük őket, és az egér bal gombjával lehúzzuk a kitöltőfület.
  • Írjuk be a sorozat első elemét egy cellába, jelöljük ki a cellát, majd válasszuk ki a Kezdőlap/Szerkesztés/Kitöltés menüpontot, és adjuk meg sorozat paramétereit (számtani vagy mértani, lépésköz stb.).

A cellák közötti logikai kapcsolatmegadás ezen - nagyon gyakran alkalmazott - módját relatív hivatkozásnak nevezzük. A hivatkozás természetesen nemcsak szomszédos, hanem egymástól távoli cellákra is használható, erre a későbbiekben látunk még példákat.

A relatív hivatkozás másoláskor mindig megváltozik. Függőleges másoláskor csak a sorkoordináta, vízszintes másoláskor csak az oszlopkoordináta, mindkét irányú másoláskor mindkettő.

Sok feladat megoldásához a relatív hivatkozás a megfelelő eszköz, más esetekben azonban szükség lehet arra, hogy ezt a logikai kapcsolatot felülbíráljuk.

Oldja meg a leírás segítségével a következő feladatot!

Egy autó különböző utakat tesz meg (ezek az adatok kilométerben egy táblázatoszlopban adottak), és ki szeretnénk számolni az utakhoz tartozó benzinköltséget. Egy-egy cellában található a benzinár és az autó fogyasztása 100 km-en, amelyeket a megoldást előállító képlet használ majd.

A képlet a B2-es cellára a következő: =A2/100*C2*D2 lásd a következő ábrán.

Ha lefelé lemásoljuk a képletet a többi adat mellé, akkor meglepetésünkre az eredmény az összes másolással feltöltött cellában 0 lesz. A problémát elemezve láthatjuk, hogy a relatív hivatkozás miatt a harmadik sorban =A3/100*C3*D3-ra, a negyedikben =A4/100*C4*D4-re, ... változik a képlet, de ezek a C és D cellák már üresek, így értékük 0. Ez az oka annak, hogy rossz eredményt kaptunk.

Első közelítésben gondolhatunk arra, hogy lemásoljuk a benzinár és a fogyasztás adatokat a megfelelő C és D cellákba. Ez azonban úgynevezett adatredundanciát okozna, és így például benzinár változása nehezen lenne nyomon követhető: egyesével kellene megkeresni és átírni azon cellákat, ahol ezt az adatot újra elhelyeztük. Ez komoly hibalehetőséget rejt magában.

Különböző címzési módok
1. ábra

Így a következő fontos szabályt fogalmazzuk meg:

Egy adott tulajdonságot leíró adat a táblázatban csak egyszer helyezhető el. Más előfordulásait csak hivatkozással állíthatjuk elő.

Vigyázat, ez nem azt jelenti, hogy ugyanaz az érték nem szerepelhet többször is a táblázatban! Ez természetesen előfordulhat akkor, ha az érték minden előfordulását más tulajdonság leírására használjuk (pl. van két diák, akik ugyanazon a napon születtek, ekkor a születési dátumuk azonos, mégis mindkettőt külön tároljuk).

Az előző probléma megoldásához tehát szükség lenne arra, hogy a C és a D oszlop megfelelő celláira való hivatkozás ne "vándoroljon" lejjebb, hanem mindig a második sorra mutasson, azaz rögzíteni kell a hivatkozásban a második sort. Ez a táblázatkezelőkben úgy valósítható meg, hogy a rögzíteni kívánt koordináta elé egy $ jelet írunk. A lefelé is szabadon másolható megoldó képletet tehát a következő: =A2/100*C$2*D$2.

Most ez volt a megoldás, de természetesen a feladat jellegétől függően szükség lehet az oszlophivatkozás rögzítésére is. Ekkor az oszlopkoordináta elé írunk $ jelet. A hivatkozásnak ezt a módját abszolút sor- vagy oszlophivatkozásnak nevezzük. Ha a sor- és az oszlophivatkozás egyaránt rögzített, akkor abszolút hivatkozásról beszélünk, ha csak az egyik koordinátára vonatkozik a rögzítés, akkor azt vegyes hivatkozásnak nevezzük.

Ha egy hivatkozást abszolúttá tettünk (sor- és oszlopkoordinátáját egyaránt rögzítettük), akkor már nem a cellák egymáshoz viszonyított (relatív) elhelyezkedését adjuk meg, hanem egy olyan általánosan érvényes képletet/képletrészt kaptunk, amely tetszőleges másolás után is pontosan ugyanarra a cellára vonatkozik.

Mikor milyen hivatkozást használjunk?

A lehetséges hivatkozástípusok a következők:

A1, A$1, $A1, $A$1.

Egy megoldásban alkalmazandó rögzítést a feladat jellege határozza meg. Át kell gondolni, hogy egy cellára már jó képlet relatív hivatkozásai a másolás során hogyan változnak meg, és a változások közül melyek jók számunkra, ill. melyek okoznak hibát. Ha egy koordináta változása hibát okoz, akkor azt rögzíteni kell. Ezt szükséges rögzítésnek nevezzük.

Ha egy koordináta rögzítése nem okoz hibát, de a feladat megoldása szempontjából nem indokolt, akkor felesleges rögzítésnek minősül. Például: ha egy képletet csak függőlegesen fogunk másolni, akkor a hivatkozott cella oszlopazonosítóját felesleges rögzíteni, mert az a másoláskor úgysem fog változni.

Ha a rögzítés hibát okoz, akkor hibás rögzítésről beszélünk.

Készítsük el az XOR művelet igazságtábláját. Gondolja át, hogy mikor, milyen értéket ad eredményül az XOR művelet! A következő leírás alapján ön is próbálja ki a megoldást!

Az XOR kapcsolat ugyanazokat az értékeket adja eredményül, mint a <> művelet.

A nyers táblázat
2. ábra

Vegyük fel Excelben a nyers táblázatot (lásd előző ábra) egy új munkalapra, majd készítsünk a C3-as cellába egy másolható képletet. Figyeljük meg, hogy a másolhatóság érdekében az A operandushoz a 2. sort, a B-hez pedig a B oszlopot kell rögzíteni. Tehát a C3-as cellába a =C$2<>$B3 képletet kell írni.

XOR igazságtábla
3. ábra
Segítség: az n szám faktoriálisát most rekurzív képlettel határozza meg, így n! = n*(n - 1)!

Készítsen faktoriális táblázatot! Az A oszlopot töltse fel pozitív egész számokkal (pl. 1-től 15-ig). A B1-es cellába írja be az 1-es számot (ez az 1!). A B2-es cellába készítsen olyan képletet, amely az A2-ben található szám (a 2) faktoriálisát állítja elő. A képlet legyen lefelé másolható!

Milyen hibajelenséget tapasztalunk, ha túl nagy egész szám faktoriálisát akarjuk kiszámoltatni?

Készítsen szorzótáblát! Az A1 cellába írjon be egy * jelet, majd az A2-től A11-ig és a B1-től K1-ig terjedő tartományt töltse fel növekedő számsorozattal egytől kezdve. A táblázat további celláit töltse ki egy darab (!) másolható képlet előállításával.

Szorzótábla
4. ábra
Nevek használata

Cellahivatkozások/blokkok esetében azért használunk neveket, mert így a hivatkozások egyszerűbbé válnak (különösen kényelmes lehet ez másolás során, esetleg a táblázatkezeléshez kevésbé értő felhasználók számára). A kijelölt blokk úgy nevezhető el, hogy a szerkesztőléc bal oldali Név mezőjére kattintunk, majd beírjuk a nevet és Entert ütünk.

A nevekhez tartozó blokkok címe módosítható a Képletek menüszalagon, a Definiált nevek csoportban a Névkezelő nyomógombra kattintva. A nevek törlése is ugyanezen pontok alatt lehetséges.

Nevek kezelése
5. ábra

Ha a Név mező segítségével nevezünk el egy cellát vagy blokkot, akkor ezzel olyan nevet hozunk létre, ami abszolút módon hivatkozik az adott cellára vagy blokkra. Ha a Névkezelőt használjuk az új név létrehozásához, akkor nem csak abszolút, hanem relatív és vegyes hivatkozással is hozhatunk létre nevet. Ebben az esetben a program a hivatkozást ahhoz a cellához viszonyítja, amelyiken állva a Névkezelőt elindítottuk.

Írjon be az A1-es cellába egy számot, majd lássa el a cellát a Név mező segítségével "kód" névvel. Próbálja ki a nevet a B1-es cellában az =kód képlet segítségével! Törölje a nevet a Névkezelő segítségével!

A cella/blokkhivatkozásokon kívül képleteket és adatokat is tudunk nevesíteni.

Hozzon létre neveket a következő ábra szerint, majd próbálja is ki!

Nevek
6. ábra

A névadási szabadság nem teljes. Természetes, hogy már kiadott név még egyszer pontosan ugyanúgy más blokkra nem szerepelhet, emellett egyes táblázatkezelőkben lehetnek olyan speciális védett, beépített nevek, amelyeket nem használhatunk.

Név csak számot, betűt és aláhúzást tartalmazhat, az első karaktere csak betű vagy aláhúzás lehet.

Függvények használata
A függvények megadása

A táblázatkezelők függvényfogalma lényegében megfelel a matematikai függvényfogalomnak. (de ez az általános számítástechnikai értelmezés is). Eszerint nulla, egy vagy több bemenő adat - más néven paraméter - felhasználásával a gép előállítja a függvényértéket, azaz a visszaadott értéket.

A függvények általános alakja a következő (a paramétereket argumentumoknak is nevezzük):

függvénynév(paraméter1; paraméter2; ... paraméterN).

A függvények akár többszörösen is egymásba ágyazhatók, ilyenkor a belső függvény általában a külső paramétere.

Függvények némelyikének 0, másik csoportjuknak 1, a harmadik csoporthoz tartozóknak több paramétere lehet. Ha nincs paraméter, akkor egy üres zárójelpárt kell írni a függvény neve után - pl. Pi(). Előfordulhat az is, hogy egy függvény egyes paramétereit nem kötelező minden esetben megadni.

Ugyan a speciálisabb használati esetek közé tartozik, de a hibás működés elkerülése érdekében érdemes külön figyelnünk arra, hogy egyes (általában a több paraméterrel rendelkező) függvényeknél a paraméterek megadása elmaradhat, de az elválasztó pontosvessző nem. A táblázatkezelő az elmaradt értéket ilyenkor egy alapértelmezettnek tekinthetővel helyettesíti, és így számol. Pl. az =Ha(A2>0;1;) képlet értéke 1 lesz, ha az A2 értéke nagyobb, mint nulla, de 0 (ez a 3. paraméter alapértéke) ha nem!

A függvények használatához az Excel 2010 jelentős segítséget nyújt azzal, hogy beépített Függvényvarázslóval rendelkezik. Ez a hasznos segédprogram a szerkesztőlécen elhelyezkedő fx ikonnal indítható legegyszerűbben. Menüből az aktiválás a Képletek/Függvénytár/Függvény beszúrása pont segítségével történik. Excelben - beállítástól függően - a varázsló sok esetben indítható még a Képletek/Függvénytár csoportból, az AutoSzum ikonhoz tartozó lenyíló listából a További függvények... pontot kell választani.

Függvényvarázsló
7. ábra

Indítás után először ki kell választani azt a függvényt, amellyel dolgozni szeretnénk. Egymásba ágyazott függvények esetén először a legkülső függvényt.

A függvények kategóriánként csoportosítva találhatók meg. Ezek az ablak felső részében láthatók. Az első kettő a legutóbb használt függvények illetve az összes függvény csoportja, utána logikailag összetartozó függvények kisebb-nagyobb csoportjai következnek. Az ablakban látható a kiválasztott kategória összes függvénye névsor szerint. Ezek közül az egyik ki van jelölve. Erről függvényről az ablakban rövid tájékoztatást is olvashatunk.

Keresse meg a szövegfüggvények között az Érték típuskonverziós függvényt, majd olvassa el a rövid tájékoztatót!

A megfelelő függvényt kattintással választhatjuk ki. Ezután meg kell adnunk a paramétereit, ehhez kattintsunk az OK gombra, ami után új ablakot kapunk. Ebben a lépésben az egyes paramétereket a megfelelő mezőkbe beírhatjuk, illetve - cellák vagy blokkok esetén - rákattintva vagy a kijelölést elvégezve a program beírja a megfelelő hivatkozásokat. Ha befejeztük a függvény megadását, akkor a Kész gombra kattintva az aktuális cellában megjelenik a megkomponált függvény, és befejeződik a Függvényvarázsló működése.

A Függvényvarázsló
8. ábra

Ha varázslóval dolgozunk, akkor a mezők kitöltésén kívül nem kell a paraméterek elválasztásával és a külső zárójelek megadásával foglalkozni, mert ezt a varázsló, tündér megoldja helyettünk. Ha a varázslónak, tündérnek a paraméterek megadására szolgáló mezőjében újabb függvényt akarunk megadni, akkor a mező kézi kitöltésekor a belső függvény paramétereinek elválasztására már be kell gépelni a pontosvesszőket és meg kell adni a függvény zárójeleit is.

Keresse meg a Szum függvényt a matematikai függvények között, és töltse ki az argumentumait az 1; 2; 3; 4 számokkal. Figyelje meg, hogyan változik a képlet a szerkesztőlécen!

Ha a függvénynek nincs paramétere, akkor a kiválasztás után már nincs más teendőnk, mint a Kész gombra kattintani.

Az egyik legegyszerűbb függvény az összegzést végző Szum, amely a paraméterként megadott blokk(ok) ill. cellák számtípusú celláit összegzi. Használata közvetlenül a menüszalagról is lehetséges.

Speciálisan az Excel 2010 lehetősége még, hogy a menüszalagról (Függvénytár csoport) közvetlenül is hívhatunk az egyes függvénykategóriákat.

A 2003-as Excelben még meglévő, de 2010-es verzióban már nem használatos függvények az utóbbi programban a Kompatibilitási kategóriában találhatóak. Azért nem törölték ki az elavultnak vélt függvényeket, hogy a 2003-as változatban készült munkafüzeteket is további átalakítás nélkül meg lehessen nyitni 2010-es programban.

Az Excel 2010-es változata sok új függvényt tartalmaz, amelyek a korábbi verziókba még nem voltak beépítve. A változások miatt problémák, veszteségek adódhatnak olyan esetekben, ha új Excel munkafüzetet szeretnénk menteni egy régebbi verzió formátumába, illetve hasonlóan, egy újabb verzióval készült munkafüzetnek egy régebbi verzióban történő megnyitási kísérleténél. (Az ilyen megnyitáshoz esetleg szükség lehet egy konverziós programbővítésre is.)

Kompatibilitási probléma Excel 2010-ből 2003-ba való áttéréskor (Az eredeti táblázat B4 cellájában a Szór.s függvényt használtuk.)
9. ábra

A továbbiakban részletesebben is megismerkedünk az összetartozó függvények csoportjaival. Néhány fontosabb tagot részletesen is bemutatunk, feladatok és példák megoldásával, elsősorban gyakorlati alkalmazásukra helyezve a hangsúlyt. Emlékeztetünk arra, hogy mindig olyan másolható képletet kell előállítani, ami átalakítás nélkül az ugyanúgy számítandó összes cellatartalom kiszámítására alkalmas.

A táblázatkezelőkbe beépített összes függvény áttekintésére itt nincs mód, de ez talán nem is szükséges, hiszen az azonos csoportba tartozó függvények között sok esetben nagy a hasonlóság. Egy-egy jellemző függvény megismerésével (a Súgó vagy a Függvényvarázsló segítségével) a hasonlóak kezelése is könnyen elsajátítható lesz.

Matematikai, logikai és statisztikai függvények

A matematikai függvények a következő részcsoportokba sorolhatók:

  • trigonometriai függvények és inverzeik;
  • alapvető nem trigonometriai függvények (például abszolút érték, előjel, faktoriális, négyzetgyök stb.);
  • kerekítéseket elvégző függvények;
  • összegző függvények;
  • néhány más speciális függvény.

Ezen függvények túlnyomó többségének kezelése nem jelenthet problémát, mert működésük megfelel matematikai elvárásainknak. Az utolsó csoport néhány tagja okozhat csak meglepetést.

A logikai függvényeket feltételek megfogalmazására és kiértékelésére használjuk (Nem, És, Vagy, Igaz, Hamis, Ha).

A statisztikai függvényekkel összefoglaló jelleggel foglalkozunk.

Véletlenszámok használata

Egy játékos kockadobási statisztikát készít. Határozza meg a következő leckerészek segítségével a dobások átlagát, döntse el, hogy az átlag nagyobb-e (-nél, és határozza meg, hogy szerepelt-e egyes vagy hatos a dobások között. A dobások 1 és 6 közötti véletlen egész számok legyenek!

Véletlenszámokat a Vél és a Véletlen.között függvénnyel tudunk előállítani Excel 2010-ben.

1. Megoldás a Vél függvénnyel:

  • Ennek a matematikai függvénynek nincs paramétere, tehát a név mögött kötelező megadni egy üres zárójelpárt, ez jelzi az üres paraméterlistát. Ez a függvény egy 0 és 1 közötti (0-val lehet egyenlő, 1-nél azonban kisebb) véletlen valós számot állít elő, ebből kell az 1..6 intervallumba eső véletlen egész számot készítenünk. Ehhez az eredeti véletlenszámot először megszorozzuk hattal (így olyan x számot kapunk, amelyre 1x<6 ), hozzáadunk egyet ( 1x<7 ), majd az így kapott érték egészrészét vesszük például az Int függvénnyel. Az Int helyett használható más kerekítést végző függvény is, pl. Kerek.le. A végeredmény tehát
  • =Int((Vél()*6)+1).
  • Ezt a kifejezést írjuk be a B2-es cellába, majd az eredményt másoljuk végig az oszlopban, ameddig kell, esetünkben a B9-es cella az utolsó; lásd az ábrán lent.

2. Megoldás a Véletlen.között függvénnyel:

  • Két paramétere van, az alsó és felső határ (ahol alsó <= felső), amelyek között a véletlenszámokat generálja. Az egyenlőség az alsó és felső határnál egyaránt lehetséges. Így a megoldás:
  • =Véletlen.között(1;6).
  • A képletet hasonló módon másoljuk végig az oszlopban.

Megjegyzés: A 2010 előtti Excelekbe (2003, 2007), illetve a Calc programba még csak a Vél függvényt építették be. Ha olyan táblázatot készítünk, amit korábbi táblázatkezelőkben is meg szeretnénk nyitni, akkor ne használjuk a Véletlen.között függvényt.

Véletlenszámok használata esetén vigyáznunk kell arra is, hogy alapértelmezésben a gép a kifejezéseket, így a Vél vagy Véletlen.között függvényt tartalmazókat is minden egyes Enter leütés után újraszámolja. Ez, mint jelen esetben is, zavaró lehet. Ha akarjuk, az újraszámoltatási mód menü segítségével kikapcsolható. Ezután az F9 billentyűvel kérhetünk újraszámolást.

Az újraszámolási mód ki- ill. visszakapcsolása a Képletek/Számolási beállítások paranccsal lehetséges. Az alapértelmezés az Automatikus illetve az Automatikus számolás, választható a Csak kérésre illetve Újraszámolás vagy Manuális opció.

Feltételek

A dobások átlagát az Átlag függvénnyel határozzuk meg, amelynek használata lényegében megegyezik a Szum függvényével.

A második kérdéshez először el kell döntenünk, hogy az átlag nagyobb volt-e π-nél, majd ettől függően szöveggel beírni a választ. Ezt a választást a táblázatkezelő a Ha függvénnyel valósítja meg.

A függvény általános alakja a következő:

Ha(logikai kifejezés; érték, ha a kif. igaz; érték, ha a kif. hamis).

A logikai kifejezésben az Átlag függvény eredményét a π-vel hasonlítjuk, amit a Pi paraméter nélküli függvény ad meg. A HA visszaadott értéke igaz illetve hamis logikai feltétel esetén egyaránt szöveg lesz, amit mindig idézőjelek közé kell tenni. A D2 cellába tehát a következő képlet kerül:

=Ha(B10>Pi();"igen";"nem").

Összetett feltételek

Az utolsó kérdésre szintén a Ha függvény felhasználásával válaszolunk. Az elvégzendő tevékenységek ugyanazok, mint az előző részfeladatban.

A nehezebb rész a feltétel megfogalmazása. Úgy dolgozunk, hogy meghatározzuk a legkisebb dobás értékét, és megnézzük, hogy az egyes-e, majd a legnagyobb dobás értékét a hatossal hasonlítjuk.

Ehhez a Min és a Max függvényt használjuk, amelyek a paraméterként megadott blokk(ok) illetve cellasorozat legkisebb vagy legnagyobb elemét adják vissza. Egyszerre két feltételt kell vizsgálni. Az a számunkra megfelelő, ha valamelyik feltétel IGAZ értékű. Olyan képletet kell előállítanunk, amelyik akkor és csak akkor IGAZ, ha a két feltétel valamelyike IGAZ. Ezt a logikai vagy műveletet alkalmazva készíthetjük el. Így:

Min(B2:B9)=1 Max(B2:B9)=6.

Az Excel a vagy műveletet a Vagy függvény használatával valósítja meg, ennek általános alakja:

=Vagy(logikai kif1; logikai kif2; ... logikai kifN).

A feltételeket tehát a paraméterlistában felsorolva kell megadni. Az eredmény IGAZ lesz, ha valamelyik paraméter-kifejezés értéke IGAZ (logikai és művelethez az És függvényt ugyanígy használjuk, ilyenkor az eredmény csak abban az esetben IGAZ, ha az összes paraméter-kifejezés értéke IGAZ).

Az utolsó kérdésre választ adó képlet tehát:

=Ha(Vagy(Min(B2:B9)=1;Max(B2:B9)=6);"igen";"nem").

Kockadobási statisztika
10. ábra

Az És függvény használatánál mindig HAMIS lesz a függvényérték, ha egy "üres paramétert" (több pontosvessző, mint amennyi a paraméterek elválasztásához kellene) írunk be az argumentumok közé. Ez a paraméter nyilván HAMIS értékű, így az eredmény is HAMIS lesz (a Vagy függvény paramétereinél elkövetett ilyen elírás nem hiba, mivel a vagy művelet eredménye nem függ a HAMIS értékű operandustól!).

Járjon utána, hogy miért nem!

Logikai függvény hibás használata
11. ábra
Segítség: A döntetlen állás vizsgálatát úgy tudjuk megoldani, hogy egy Ha függvénybe egy másik Ha függvényt ágyazunk.

Két játékos, Adél és Béla kockát dobálva azon versenyez, hogy 10 dobásból ki ér el több találatot. Készítsen egy másolható képletet a kockadobások szimulálására, majd függvény segítségével válaszoljon arra a kérdésre, hogy ki nyert. A képlet jelezze azt is, ha döntetlen az állás.

A matematikai és logikai függvénykategóriák további elemezése

A logikai függvényeket, ezen belül is a Ha függvényt nagyon gyakran használjuk a táblázatkezelőkkel való problémamegoldás során, az egyszerű "igen-nem" típusú válaszokon át egészen a bonyolult feltételek megfogalmazásáig. Sok esetben az is előfordul, hogy a megoldás viszonylag egyszerű elemekből építkezik ugyan, de összességében mégsem igazán könnyű felépíteni.

Vegye fel a következő ábrán található táblázat A és B oszlopát egy új munkalapra!

Adjon megoldást a két kulcsot alkalmazó egyszerű adószámítási feladatra a táblázat alapján a következők szerint: Rögzített egy (éves) jövedelemhatár (2 000 000 Ft), amely felett a magasabb kulccsal, alatta pedig az alacsonyabb kulccsal adóznak a dolgozók. Eszerint tehát az alacsony jövedelműek a fizetésük fix százalékát fizetik be adóként az alacsonyabb kulcs szerint, a magasabb jövedelműek adója pedig két részből áll: az első része a sávhatárig megfizetendő teljes adó, a második rész pedig a sávhatár fölé eső jövedelem magasabb kulccsal számított adója. A kulcsok szintén adottak (alsó: 20%, felső: 30%). Meghatározandó az egyes jövedelmek után fizetendő adó (havi szinten).

Adó meghatározása kétkulcsos rendszer szerint
12. ábra
A statisztikai függvények áttekintése

Gyakran előfordul, hogy mérési adatokat szeretnénk elemezni a táblázatkezelő segítségével. Ilyen elemzésekben leggyakrabban a következő kérdések fordulnak elő: mi az adatok átlaga, mekkora az adatok szórása, mekkora a középső, mekkora a k-adik legkisebb és legnagyobb elem, hány adat kerül egy intervallumrendszer egyes intervallumaiba, melyik adat fordul elő leggyakrabban, stb. Ezekre a kérdésekre megfelelő beépített függvények segítségével kaphatunk választ.

  • Átlagszámítás: Átlag(átlagolandó adatsor). A függvény értéke az argumentumban megadott számok átlaga lesz.
  • Mértani átlag: Mértani.Közép(adatok). Az adatok mértani közepe lesz a függvényérték (csak pozitív adatokra működik!).
  • Középső elem keresése: Medián(számok). A függvény értéke az argumentumban specifikált számok közül a középső szám, ha a számok páratlan sokan vannak. Párosan sok elem esetén bontsuk kétfelé a számhalmazt. Mindkét félbe ugyanannyi szám kerüljön. Az egyik fél a kisebb a másik fél a nagyobb számokat tartalmazza. A Medián a kisebb számok legnagyobbikának és a nagyobb számok legkisebbikének átlaga lesz.
  • K-adik legkisebb elem keresése: Kicsi(Adatsor;k). A függvényérték az adatsor elemeinek növekvően rendezett sorából a k-adik elem lesz.
  • K-adik legnagyobb elem keresése: Nagy(adatsor;k). A függvény értéke az adatsor csökkenően rendezett sorából a k-adik érték.
  • Adatrendszer szórása: Szórás(adatok). Az adatok szórásértékét adja. (A kiszámító algoritmus közelítő módszerrel számol.)
  • Legtöbbször előforduló adat meghatározása: Módusz(adatok). A függvényérték az adatok között legtöbbször előforduló érték lesz. Ha minden elem csak egyszer szerepel, akkor válaszként a #HIÁNYZIK vagy az #ÉRTÉK üzenetet kapjuk.

A Gyakoriság(adatsor;intervallumok) függvénnyel a megadott adatsor elemeinek előfordulási gyakoriságát határozhatjuk meg. Ez egy tömbfüggvény (bevitelét a Ctrl+Shift+Enter-rel jelezzük), eggyel több értéket ad válaszul, mint ahány intervallumot a második paraméterével megadtunk. A tömböt oszlopba rendezve adja meg. Az intervallumok jobbról zárt, egymáshoz illeszkedő, egymást követő intervallumok lesznek, amiket a második paraméterben megadott végpontok definiálnak. Az első intervallum bal végpontja a mínusz végtelen, az utolsó intervallum jobb végpontja a plusz végtelen. Ezeket nem kell megadni.

Megjegyzés: A normál függvényeket is lehet tömbfüggvényként használni, hogy a képlet egyszerűbb legyen. Lásd következő ábrán, ahol egy adatsor 5 legnagyobb elemét adtuk össze kétféle módon.

Az 5 legnagyobb szám összege
13. ábra

A következő ábra alapján készítsük el a táblázatot a függvények megismerésének elmélyítésére!

Statisztikai függvények
14. ábra

A statisztikai függvényeknél is tapasztalhatunk változásokat az Excel 2010-es verziójában a korábbi állapothoz képest. Pl. a Szórás függvényt meghagyták a régebbi programokkal való kompatibilitás miatt, az új Szór.s függvény a szórás pontos megállapítására szolgáló algoritmussal számol. A Módusz függvényt is felerősítették, felkészítve arra, hogy több leggyakoribb elem is lehet (Módusz.egy, Módusz.több).

Olvassa el a Súgóban, hogy mi a különbség a Szórás, Szórásp, Szór.m, Szór.S, Szórása, Szóráspa függvények között!

Önellenőrző kérdések
1. Jelölje meg a következő kifejezések közül azokat, amelyek használhatók névként a táblázatkezelőben:
A1
alma
körte
név
A1:B1
_C
a_b
2A
2. Jelölje meg a következő kifejezések közül azokat, amelyek értéke egyenlő az =A1+B1 kifejezéssel:
=SZUM(A1:B1)
=SZUM(A1;B1)
=SZUM(B1:A1)
=SZUM(A1;A1:B1;B1)
=SZUM(A1)+B1
3. Adjuk meg a táblázatkezelő válaszát a következő képlet esetén:

=HA(ABS(PI())>4;IGAZ;3)

válasz:

4. Jelölje meg a következő kifejezések közül azokat, amelyek alkalmasak véletlenszámok előállítására:
=VÉL()
=VÉL(3)
=VÉL(3;4)
=VÉL*()
=VÉLETLEN.KÖZÖTT(0;2)
=VÉLETLEN.KÖZÖTT(20;2)
=VÉLETLEN.KÖZÖTT(0;5^2)
=VÉLETLEN.KÖZÖTT(2*10;5)
5. Az A2 cellában az =A1+B1 képlet szerepel. Adjuk meg, hogy mi lesz az A2 cella tartalma, ha...

a B oszlop elé beszúrunk egy új oszlopot:
az első sor elé beszúrunk egy sort:
töröljük a munkalapról B oszlopot:
töröljük a B1-es cella tartalmát:

6. Az A2 cellában az =$A1+B1 képlet szerepel, az A1 cella tartalma 3, a B1 celláé 2, a B2 celláé 1, az A3 celláé pedig 0. Adjuk meg, hogy mi lesz az A2 cella értéke, ha...

a B oszlop elé beszúrunk egy új oszlopot:
az első sor elé beszúrunk egy sort:
töröljük a munkalapról B oszlopot:
töröljük a munkalapról az A oszlopot:
töröljük a munkalapról az első sort:
töröljük a B1-es cella tartalmát:

A képletben ne használjon felesleges zárójelet vagy rögzítést!
7. A B2:C5 blokkban egyetlen másolható képlettel állítottuk elő. Mi a C5-ös cella tartalma, ha a képletben nincs függvény?

C5 tartalma:

8. A következő táblázat egy futóverseny eredményeit összegzi.

NévIdő
Bognár Hágár0:16:41
Vindisch Zsombor0:24:51
Vieg Várkony0:16:23
Kerekes Arlen0:25:32
Gavanszki Bandó0:17:58
Szekeres Nadin0:18:59
Oroszlán Nesztor0:18:11
Villecz Zombor0:25:49
Bakk Adél0:21:08
Lóska Koridon0:27:02
Alsó Balambér0:19:46
Gayduschek Bátor0:17:57
Pfandler Martina0:27:09
Fajkusz Irén0:18:55
Határozza meg a futók átlagos idejét!

Átlag:

Mi volt a 3. legrosszabb eredmény?

3. legrosszabb idő:

Mi volt a legrosszabb idő?

Legrosszabb idő:

Mi volt a legjobb idő?

Legjobb idő:

Mennyi az idők mediánja?

Medián:

Mennyi a legrosszabb 5 idő összege?

Legrosszabb 5 idő összege:

Mennyi a legjobb 5 idő átlaga?

Legjobb 5 idő átlaga: