KURZUS: Informatikai rendszerek alapjai

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

  • az elrendezésnek megfelelő keresőfüggvényt alkalmazni,
  • a keresőfüggvényeket a feladatnak megfelelően felparaméterezni,
  • egyik munkalapról áthivatkozni egy másik munkalapra,
  • egyik munkafüzetből áthivatkozni egy másik munkafüzetbe,
  • a cellák, munkalapok láthatóságát és védelmét beállítani.

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
  • vízszintes keresés
  • függőleges keresés
  • tartományban keresés
  • pontos egyezés keresése
  • cellavédelem
  • lapvédelem
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.

Szemléltetés: a 0-tól különböző számértékeket a táblázatkezelő logikai igaznak tekinti
1. ábra

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).

Fizetések meghatározása rendezett táblázatból
2. ábra

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).

Fizetések meghatározása nem rendezett táblázat esetén. Adél fizetésének megkeresésére adott függvény hibásan van paraméterezve.
3. ábra

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.

Rendezett táblázatban különböző keresési típusok és válaszok felhasználói hiba esetén
4. ábra

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).

Teljesítményhez tartozó jegy meghatározása
5. ábra

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.

Segédtáblázat végére másolt első sor VKERES függvény alkalmazásához
6. ábra

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).

Hivatkozás másik munkalap celláira
7. ábra

Amennyiben munkafüzet szintű elnevezett blokkokat használunk, akkor a program megtalálja a hivatkozást úgy is, ha másik munkalapra vonatkozik.

Hivatkozás másik munkalap celláira elnevezett blokkokkal
8. ábra

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!

Önellenőrző kérdések
1. Mi lesz a keresőfüggvény értéke az alábbi táblázat esetén, ha a képletet az A7 cellába írjuk? (Próbálja fejben megadni a választ, úgy, hogy nem használja a táblázatkezelőt.)

Képlet: =FKERES("Szilva";$A$1:F6;2;HAMIS)



Megoldás:

2. Mi lesz a keresőfüggvény értéke az alábbi táblázat esetén, ha a képletet a D8 cellába írjuk? (Próbálja fejben megadni a választ, úgy, hogy nem használja a táblázatkezelőt.)

Képlet: =VKERES("szilva";$A$2:E$4;2;HAMIS)



Megoldás:

3. Tudjuk, hogy egy VKERES függvényt használó képletet írtak be a táblázatba, amelynek második paramétere az ábrán látható A1:E3 blokk. Melyek igazak az alábbi állítások közül?
A függvény válasza lehet "eper".
A függvény válasza lehet "mangó".
A függvény válasza biztosan nem lehet "alma".
A függvényt biztosan a "tartományban keres" opcióval kell megadni.
A függvény csak akkor találja meg a "barack" szót az első sorban, ha a "nem tartományban történő keresést" adjuk meg.
4. Megnyitottunk egy olyan táblázatot, amelynek első oszlopát korábban elrejtették. A következő műveletek közül melyik alkalmas az oszlop megjelenítésére?
A hozzáférési jelszó ismeretében oldjuk fel a lapvédelmet.
A B oszlop kijelölésekor - a gomb lenyomva tartásával - húzzuk az egeret balra, a táblázaton kívülre, ezután az oszlop a helyi menüből felfedhető.
Szúrjunk be egy új oszlopot a B oszlop elé, így az adatok ismét láthatóvá válnak.
Egyik művelet sem, mert a feladat nem oldható meg.

5. Nyissa meg a DVD.xlsx munkafüzetet, majd oldja meg a következő feladatokat! A munkafüzet egy DVD kölcsönző 2012 október 22-én készült nyilvántartását tartalmazza.

Oldja fel az Árak munkalap védelmét! A jelszó: "titok"

Töltse ki a Kölcsönzések munkalap Kategória oszlopát az Árak munkalap segítségével! Mennyi a Kódok munkalapon megjelenő Sárga ellenőrző kód értéke?

Sárga kód:

Töltse ki a Kölcsönzések munkalap Díj/nap oszlopát az Árak munkalap segítségével! Figyeljen arra, hogy a keresőfüggvény a keresési tartomány első sorában/oszlopában tud csak keresni! Mennyi a Kódok munkalapon megjelenő Piros ellenőrző kód értéke?

Piros kód:

Az ügyfelek előre megmondják, hogy hány napra szeretnék kikölcsönözni a filmet (Időtartam) és ennek megfelelő összeget fizetnek. Számolja ki a Kölcsönzések munkalap Befizetett összeg oszlopát az Időtartam és a Díj/Nap segítségével! Mennyi a Kódok munkalapon megjelenő Kék ellenőrző kód értéke?

Kék kód:

Ha a kikölcsönző nem hozta vissza idejében a kazettát (túllépte az előre "bemondott" kölcsönzési időt), akkor büntetést kell fizetnie. Büntetési kötelezettség adódhat úgy, hogy az illető már visszahozta a kazettát, de késett, illetve úgy is, hogy még vissza sem hozta. A büntetés összege naponta 500 Ft.

Számolja ki a tényleges időt, majd az alapján a Kölcsönzések munkalap Büntetés oszlopát! Mennyi a Kódok munkalapon megjelenő Zöld ellenőrző kód értéke?

Zöld kód:

A kivett napok száma szerint a kölcsönzéseket különböző kategóriákba sorolják, amiket a Besorolás munkalapon talál. Töltse ki a Kölcsönzések munkalap Értékelés oszlopát! Figyeljen arra, hogy ne az előre bemondott időtartammal, hanem a ténylegesen kivett idővel kalkuláljon! Mennyi a Kódok munkalapon megjelenő Lila ellenőrző kód értéke?

Lila kód: