KURZUS: Mérnöki számítási módszerek
MODUL: Táblázatkezelés
4. lecke: Keresőfüggvények és lapműveletek
Cél: A lecke elsősorban a keresőfüggvények használatát tárgyalja, de ide csatoltuk a több munkalap használatát tárgyaló rövidebb fejezetet is. Az anyag elsajátítása - hasonlóan a korábbiakhoz - itt is csak akkor tekinthető sikeresnek, ha a tananyagban bemutatott gyakorlati feladatokat is bizonyos jártassággal meg tudja oldani a tanuló. | |||
A keresőfüggvények használata nem egyszerű, de ha megérti a hallgató, akkor sokkal könnyebben, rövidebb idő alatt, hiba nélkül fog tudni megoldani olyan feladatokat, ahol a megoldáshoz ki kell keresni egy blokkból az adatokat. | |||
Ennél a leckénél külön felhívjuk a figyelmet, hogy a problémától függően gondolja át a hallgató, hogy az adott szituációban keresőfüggvényt vagy adatbázis-kezelő alkalmazást érdemesebb-e használni. | |||
Követelmények: Ön akkor sajátította el megfelelően a tananyagot, ha képes az Excel segítségével | |||
| |||
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 | |||
| |||
Keresőfüggvények | |||
A keresőfüggvényeket olyan típusú feladatok megoldására használjuk, amikor ismerjük egy logikailag összetartozó adatsor egy elemét, és ehhez keresünk valamely másik elemet ugyanabból az adatsorból. A feladat megfogalmazása táblázatos formában: keresünk egy adattáblázatban, a sorok első celláiban egy ismert értéket, és ha megtaláltuk, szükségünk lenne a sor további mezőinek tartalmára is. Ugyanez a feladat oszlopokra is előfordulhat. | |||
A keresőfüggvényeket a Mátrixfüggvények csoportban találjuk meg. | |||
Egy cég dolgozóinak nevét és fizetését tárolja egy táblázatban. Meg kell határozni egy adott nevű dolgozó fizetését (feltehetjük, hogy csak egy ilyen nevű dolgozó van). | |||
Táblázat első sorában való keresésre a VKERES illetve első oszlopában történő keresésre az FKERES függvény használható. | |||
Az FKERES függvény általános alakja a következő (a VKERES teljesen hasonló): | |||
FKERES(mit keresünk; hol; oszlopszám; [tartományban keres]) | |||
A függvények első paramétere a keresett érték (ez általában egy cella értéke, de lehet akár egy kifejezés értéke is). Ezt keresünk a második paraméterként megadott tábla, az ún. keresési tábla első oszlopában vagy sorában. Vigyázzunk arra, hogy a második paraméterrel definiált tartomány ne tartalmazzon fejlécet, mert ez hibát okozhat. A harmadik paraméter egy sorszám, amely megadja azt, hogy keresett értéket tartalmazó oszlop vagy sor hányadik sorából illetve oszlopából kell kivenni a visszaadott értéket. A negyedik paraméter egy logikai érték, ezt azonban nem kell minden esetben megadni. | |||
Ha harmadik paraméterként olyan sor-, illetve oszlopsorszámot adunk meg, ami kívül esik a második paraméterben definiált területen akkor a #HIV! hibaértéket kapjuk. | |||
A keresőfüggvényeket kétféle módon használhatjuk: az egyik lehetőség a tartományban keresés, a másik a nem tartományban történő, pontos keresés. Ezt a választást a negyedik paraméter értéke szabályozza. | |||
Ezen paraméter értéke alapértelmezésben 1 vagy IGAZ, ami azt jelenti, hogy ha nem adjuk meg, akkor ez az érték lesz a negyedik paraméter. Ha ez nem megfelelő, akkor a 0 vagy HAMIS értéket kell megadni. | |||
A táblázatkezelő a 0 számértéket HAMIS logikai értéknek tekinti, az összes többi szám IGAZ logikai értéket jelent. | |||
| |||
A negyedik paraméter tehát nem kötelező kitöltésű, alapértelmezett értéke igaz. Ha elhagyjuk, vagy igazra állítjuk akkor a keresés típusa tartományban keresés lesz. Ami azt jelenti, hogy a keresendő értéket a függvény a keresési táblázat első oszlopában megadott értékekkel határolt, balról zárt intervallumokban keresi. Egy-egy intervallum határait a táblázat egymást követő sorainak első oszlopban lévő értéke adja, kivéve az utolsó intervallumot, amelynek jobboldali határa az adott adattípus lehető legnagyobb értéke lesz. Ennek következményeképpen az intervallumoknak nem lehet közös része, és nem lehet közöttük kimaradó rész sem. Természetesen ezt csak úgy tudjuk elérni, ha a táblázat az első oszlop szerint növekvően rendezett. Ha a keresett értéket nem tartalmazza egyetlen - az első oszlop által meghatározott - intervallum se, akkor a #HIÁNYZIK hibaüzenetet kapjuk. Ugyancsak ezt az üzenetet kapjuk gyakran (de nem minden esetben) akkor is, ha az első oszlop adatai nem rendezettek, azaz nem adnak megfelelő intervallumokat! | |||
Ha diszkrét értékeket keresünk, akkor ezeket az értékeket intervallum-határolóként is megadhatjuk, és pontos egyezésre keresünk. Ez a keresőfüggvények használatának logikailag legegyszerűbb esete (mint később látni fogjuk, ez a megközelítés "bolondbiztosság tekintetében" támadható). | |||
Ha a negyedik értéke hamis, akkor a keresés pontosan az első oszlop értékei között történik. Rendezettség nem szükséges. Ha a keresett érték nincs az első oszlopban, akkor a #HIÁNYZIK hibaüzenetet kapjuk. | |||
Példák: | |||
1. A legegyszerűbb esetben az adatok a keresési tábla első oszlopában, sorában névsorba rendezettek, és a keresett érték pontosan megtalálható közöttük. Ha az előző feladatban a dolgozók neveit névsorba rendezve adták meg, akkor ezt a keresési módszert használjuk. | |||
2. A következő dolgozatos feladatban azt vizsgáljuk, hogy egy érték melyik megadott intervallumba esik. Ez a tartományban keresés tipikus esete. Ilyenkor a keresési tábla első oszlopában, sorában az adatok rendezettek, és pontos egyezést általában nem fogunk találni. | |||
A keresés folyamatát ekkor logikailag úgy képzelhetjük el, hogy ha a függvény a keresett értéknél nagyobbat talál (magyar táblázatkezelőnél magyar ábécé szerinti rendezést feltételezve - szöveges adatok esetén), akkor a keresés megszakad, és a függvény az utolsó még nem nagyobb értékhez tartozó, a harmadik paraméter által meghatározott adatot adja vissza. Az utolsó intervallum esetén az adott adattípushoz tartozó lehető legnagyobb értékét "képzelhetjük" az első oszlop után következő első üres cellába. A korrekt működéshez tehát az intervallumok alsó határát kell megadni. | |||
3. Ha az előző dolgozói nyilvántartásban az adatok a keresési tábla első oszlopában nincsenek névsorba rendezve, akkor nem szabad a tartományban keresés módszerét alkalmazni, mivel az eredmény ezzel a módszerrel hibás lehet. | |||
A feladat megoldása névsorba rendezett táblázat esetében: | |||
=FKERES(D3;A2:B9;2). | |||
| |||
Ha nem névsorban követik egymást az A oszlopban a nevek, akkor át is rendezhetjük a keresési tábla adatait (és ekkor a logikailag legegyszerűbb esethez jutunk), ha erre jogunk van. Jegyezzük meg, ha a keresett név nem szerepelne a névsorban, szintén hibás eredményt kapnánk. | |||
De névsorba való rendezés nélkül is megoldható a feladat a következő képlettel: | |||
=FKERES(D3;A2:B9;2;hamis). | |||
| |||
Mi történik akkor, ha a keresendő érték megadása hibás? Ha a "tartományban keresés" típussal dolgozunk, akkor a függvény úgy dolgozik, mintha rendezett lenne az első oszlop szerint a táblázat. Eredmény is születhet, ami persze nem mindig helyes, de a felhasználó ezt nem feltétlenül veszi észre - hibaüzenetet csak akkor kapunk, ha a keresendő érték az első névnél is kisebb. Ha a "nem tartományban történő keresés"-t alkalmazzuk, akkor viszont minden hibásan megadott paraméterérték hibaüzenethez vezet. Ez az utóbbi megközelítés a korrekt. | |||
| |||
Egy dolgozat értékelésekor adottak a jegyekhez tartozó sávhatárok. Határozzuk meg a hallgatók jegyeit a százalékban megadott teljesítmény alapján. | |||
Ez a feladat példa a fenti keresési problémák kezelésére numerikus környezetben is. | |||
A megoldásához a hagyományosan megadott intervallumokat át kell írni oly módon, hogy az alsó sávhatár legyen a sáv azonosítója. Így garantálható az, hogy az alsó és felső határok közé eső értéket az Excel meg fogja találni a megfelelő helyen. | |||
Ha a keresési érték nagyobb, mint tábla legnagyobb értéke (például ha 95-öt keresünk), akkor a legutolsó sorból veszi az adatot (itt a 85 sorából, tehát ötöst ad eredményül). | |||
| |||
Visszautalva a korábbiakra, blokkhivatkozást tartalmazó kifejezés másolásakor érdemes a hivatkozott blokkot elnevezni, mert így nem kell rögzítéseket alkalmazni. | |||
Vannak olyan feladatok is, ahol alaphelyzet szerint a keresőtábla valamely magasabb sorszámú oszlopában kell keresni, mint ahonnan az eredményt várnák. Ilyenkor az eredmény oszlopának értékeit a keresőtáblába közvetlenül az utolsó oszlop mögé, megfelelő hivatkozással elhelyezzük, és a keresőtáblát úgy adjuk meg, hogy a hivatkozásban az legyen az első oszlop, ahol keresni akarunk. Ezután a feladat a szokásos módon megoldható. A segédoszlopot, sort célszerű elrejteni. | |||
| |||
Ilyen típusú feladatok keresőfüggvények nélkül is megoldhatók. A használható eszközök különböző indexelő és helymeghatározó függvények (például: Hol.van, Index, Oszlop, Sor). | |||
Segítség: a megoldáshoz 3 darab keresőfüggvényt kell használni. | Egy klasszikus többkulcsos (3 vagy több kulcs) adórendszert áttanulmányozva és elemezve készítsen táblázatot a következő adatokkal: sávhatár, adó a sávhatárig, a sávhatár feletti rész adója százalékosan (éves adatok). Az adó a sávhatárig oszlop elemeit számítással határozza meg a százalék és a sávhatár oszlop alapján. Vegyen fel néhány dolgozót havi fizetés adatokkal, és határozza meg a havi szinten általuk megfizetendő adót az előző táblázat szerint. | ||
Műveletek munkalapokkal | |||
Több munkalap használata, kapcsolt táblázatok | |||
Egy munkafüzet általában több munkalapból áll, ezek füleit a képernyő bal alsó részén címkékkel jelezve láthatjuk. A munkalapok közötti váltás a címkékre kattintással, illetve a Ctrl+PgUp illetve Ctrl+PgDn billentyűkkel hajtható végre. A nem látható füleket a fülcsoport mellett elhelyezkedő nyilakkal jeleníthetjük meg. | |||
A munkalapokkal a következő műveletek végezhetők el: átnevezés, törlés, beszúrás, másolás és áthelyezés. Ezeket a funkciókat legegyszerűbben a helyi menüből választhatjuk ki, amelyet úgy aktivizálhatunk, hogy a megfelelő munkalap címkéjére az egér jobb gombjával rákattintunk, de a táblázatkezelők menüjéből is elérhetők (Kezdőlap/Cellák csoporton belül Formátum/Munkalap átnevezése, Törlés/Munkalap törlése, Beszúrás/Munkalap beszúrása, Formátum/Lap áthelyezése vagy másolása...). | |||
A gyakorlatban sokszor előfordul, hogy valamely munkalapon elhelyezkedő táblázatra vagy adatra egy másik munkalapról kell hivatkozni. Egy-egy bonyolultabb probléma megoldása ugyanis sok - különböző jellegű adatokat tartalmazó - táblázatot igényelhet, és ezeket nem mindig célszerű - vagy nem lehet -, egy munkaterületen elhelyezni. | |||
Egy másik munkalap cellájára vagy blokkjára úgy tudunk hivatkozni, hogy először megadjuk a munkalap nevét, majd egy felkiáltójel után következhet a cellahivatkozás. Ha például a dolgozói fizetéseket nyilvántartó feladatban a dolgozók nevét és fizetését tartalmazó táblázat egy másik - Adat nevű - munkalapon helyezkedik el, akkor így módosul a hivatkozás: | |||
=FKERES(A3;Adat!A$2:B$8;2). | |||
| |||
Amennyiben munkafüzet szintű elnevezett blokkokat használunk, akkor a program megtalálja a hivatkozást úgy is, ha másik munkalapra vonatkozik. | |||
| |||
Munkalapszintű blokkok használatánál ki kell tenni a felkiáltójelet, ha másik munkalapra hivatkozunk. | |||
Megjegyzés: A különböző szintű névmegadásokkal a Számítási módszerek című tárgyban bővebben fogunk foglalkozni. | |||
Egyes feladatoknál arra is szükség lehet, hogy különböző munkafüzetek között teremtsünk kapcsolatot. Ilyenkor Excelben a fájl nevét szögletes zárójelben adjuk meg a hivatkozásban, például: [A.xls]Munka1!A2. | |||
Módosítsa az előző keresőfüggvényes feladatokat úgy, hogy a keresési érték és az adattábla külön munkalapon legyen! | |||
Láthatóság és védelem | |||
A táblázatkezelő ablakában több munkafüzet megjelenítésére is lehetőség van. Azt, hogy hogyan mutassa a munkafüzeteket szabályozható (menüvel, gyorsbillentyűvel stb.). A beállítási változatokból a Nézet/Ablak/Mozaik, Nézet/Ablak/Párhuzamos megjelenítés menüpontokkal lehet választani. | |||
Lehetőségünk van arra is, hogy a táblázat egyes részeit - például a feliratokat - a képernyőn rögzítsük. Így nagy táblázat esetén ezek több képernyőoldalt ellapozva is láthatóak maradnak Rögzítést kérve (Nézet/Ablak/Panelek rögzítése/Ablaktábla rögzítése) az aktuális cella feletti sorok és a balra levő oszlopok a munkalapon való mozgáskor mindig láthatóak maradnak a képernyőn - már ha elférnek. A rögzített terület határát vékony fekete vonalak jelzik. A rögzítés feloldható a Nézet/Ablak/Panelek rögzítése/Ablaktábla feloldása paranccsal. | |||
Nagy táblázat különböző részeit egyszerre áttekinthetjük a táblázat felosztásával (Nézet/Ablak/Felosztás). Használata hasonló a rögzítéshez. | |||
Időnként szükség lehet arra, hogy a táblázat egyes részeit illetéktelenek ne tudják módosítani. Ez a védelem beállítható a cellákra, a munkalapra és a teljes munkafüzetre is. A beállítást a Kezdőlap/Cellák/Formátum/Védelem paranccsal vagy a Korrektúra menüszalag Változások blokkjának a segítségével érjük el. Munkalapvédelem esetén megadhatjuk, hogy milyen műveletek engedélyezettek a védett területen (pl. kijelölés, formázás, törlés, szűrés). A védelem feloldását célszerű jelszóhoz kötni. | |||
Lássa el jelszavas védelemmel az előző feladatokban létrehozott adattáblákat tartalmazó munkalapokat! |