hivatkozás-függvények - A
2021-10-09 Excel 2019
fogalmak
Ebbe a családba tartoznak a hivatkozást képző és a hivatkozásokat vizsgáló függvények. Mielőtt sorba vesszük őket tisztázzunk néhány fogalmat.
A cellák azonosítása történhet a pozíciójuk vagy a tartalmuk alapján. Az előbbi rendszerben a cella oszlopát és sorát, utóbbiban a nevét adjuk meg. A pozíció szerinti azonosítás is kétféle: „<oszlop betűjele><sor indexe>” vagy „S<sor indexe>O<oszlop indexe>”. Például a munkalap első cellájának azonosítója az előbbi rendszerben A1, az utóbbiban S1O1. A „sorszám” helyett az „index” kifejezést használom, mert a „sor sorszáma” és az „oszlop sorszáma” elég hülyén hangzik.
A képletben álló cella és tartomány-azonosítókat hivatkozásnak nevezzük. A hivatkozások lehetnek közvetlenek vagy közvetettek. Másként fogalmazva a hivatkozásokat beírhatjuk a képletbe, de a képlet részeként képezhetjük is őket.
A pozíció szerinti hivatkozás lehet relatív, abszolút vagy vegyes. A tartalom szerinti általában abszolút.
indirekt függvény
Először vegyük sorra a hivatkozás-képző függvényeket. És nézzünk egy feladatot, amelyből azonnal kiderül, hogy miért is kell a hivatkozást képeznünk.
A táblázat egy vállalat bevételeit tartalmazza havi bontásban. A második oszlopban az elképzelt, a harmadikban a valóságos bevételek állnak. Most szeptember van ezért az utolsó bejegyzés az augusztusi. Hozzunk létre képletet, amelynek segítségével hónapról-hónapra, követni tudjuk a terv-teljesítés állapotát, százalékban.
A tényleges bevételek összegét el kell osztanunk az azonos időszakra tervezett bevételek összegével. A számláló tehát, SZUM(C2:C13) lesz. A nevező pedig SZUM(B2:B?). Magyarul, az idő-arányos tervezett bevételek tartományának csak az első celláját ismerjük (B2), de az utolsónak csak az oszlopát (B?). Szóval az utolsó cella hivatkozását képeznünk kell, mert az hónapról-hónapra változik.
Apropó hónap. Most szeptember van, augusztusig tudjuk ellenőrizni a terv végrehajtását. Hányadik sorban állnak az augusztusi adatok? A kilencedikben. Tehát, az aktuális hónap számával egyező indexű sorban. Excelesen fogalmazva: HÓNAP( MA()) » 9. Októberben majd szeptemberig bezárólag vizsgálódhatunk. Akkor már a tízedik hónapban leszünk. És akkor majd HÓNAP( MA()) » 10 lesz. Ezt a felfedezést felhasználva, már képezni tudjuk az utolsó cella azonosítóját: ”B”&HÓNAP( MA()).
Szuper! Csak van egy probléma: ha ezt így beírnánk a képletbe, akkor a program „látna” egy szöveget és nem tudná, hogy mit kell vele csinálnia. Ezért közölnünk kell vele, hogy ezt a karakterláncot tekintse hivatkozásnak. Ezt a deklarációt az INDIREKT függvénnyel kell megtennünk.
Az „indirekt” szó jelentése „közvetett”. Az INDIREKT függvény kétargumentumos. Az első argumentuma a karakterláncot létrehozó kifejezés vagy az azt tartalmazó cella hivatkozása. Második argumentuma nem kötelező, csak akkor kell megadni, ha az S1O1 típusú cella-azonosítást szeretnénk. Értéke: HAMIS.
cím függvény
Tessék, tessék Hölgyek és Urak! Itt a hivatkozás! Csak tessék, csak tessék! Hivatkozásra van szüksége Kisasszony? Igen? Akkor, tessék csak közelebb fáradni! Hányadik sorban áll a cella? És hányadik oszlopban? És milyen hivatkozás legyen? Relatív, abszolút, esetleg vegyes? És A1-est vagy S1O1-est tetszik kérni? Hozzá tudjuk írni a munkalap-nevét is! Kéri?
A nem kötelező argumentumok üresen is maradhatnak, ha csak a lapnevet akarjuk megadni! Például, CÍM( 1 ; 1 ; ; ; ”Munka1” ). Figyelem! Figyelem! A CÍM függvény se hivatkozást ad eredményül, hanem csak a hivatkozást megtestesítő karakterláncot. Tehát, a hivatkozás létrehozása a CÍM esetében is az INDIREKT függvénnyel történik!
Vegyünk egy példát a függvény alkalmazására! Egy iskola a tanárait „státusz” és „szint” szerint fizeti. A tanárok besorolását és leadott óráik számát az első, a kategóriák szerinti órabéreket a második munkalap tartalmazza. Az órabér-táblázat sorai a szintek, emelkedő sorrendben, oszlopai a státuszok, ABC sorrendben. Tehát, a D2-es cella a „D” státuszú, 2-es besorolású tanár órabérét tartalmazza. A „tanárok” lap E oszlopában a leadott órákért járó bért kellett kiszámolnunk.
A feladat nem nehéz: az óraszámot kell összeszorozni az órabérrel. Az adott tanár órabérét tartalmazó cella hivatkozását a CÍM és az INDIREKT függvénnyel képeztük. A CÍM függvénybe ágyazott KÓD függvény az egyetlen argumentumával deklarált karakter ASCII (American Standard Code for Information Interchange) kódját adja eredményül. Ebben a kódtáblában a 65-ös számtól kezdődnek az angol ABC nagybetűi.
A sor-indexről és az oszlop-indexről jut eszembe, minek ide INDIREKT meg CÍM? Az órabéreket az INDEX függvénnyel is kiválaszthattuk volna!
index függvény
Most lehet csodálkozni! Hát az INDEX nem kereső-függvény? De, igen. De, közben azért hivatkozás-képző is. Megmagyarázom. Először oldjuk meg újra az előző feladatot, de most az INDEX függvénnyel. Az órabérek táblázatot kicsit átalakítottam, hogy jobban látszódjon a különbség a két függvény között.
A képlet, most így alakul a tanárok lap F2-es cellájában: =D2*INDEX( 'órabérek (2)'!$B$3:$F$5 ; C2 ; KÓD( B2 )-64 ). Tehát az INDEX függvény eredményül adta az argumentumaival meghatározott cella tartalmát! De ez nem mindig van így!
Térjünk vissza a terv-teljesítős feladathoz. Nevezzük el a tervezett bevételek időarányos értékeit tartalmazó tartományt: Képletek, Definiált nevek, Név megadása.
Ebben a képletben az INDEX mindig egy cella-hivatkozást fog eredményül adni. És nem is karakterláncot, hanem igazi hivatkozást! Ezért nincs szükség az INDIREKT függvényre. Az INDEX eredményét, tehát a környezete határozza meg. Viccesen mondhatjuk azt is, mindig azt adja, amire éppen szükségünk van.
Az INDEX függvénynek csak az első két argumentuma kötelező. Az adat-tartomány és a sor-index. A harmadik, az oszlop-index egyoszlopos tartományoknál elhagyható. Viszont az első argumentuma kibővíthető! Gömbölyű zárójelek között és pontosvesszővel elválasztva, több adat-tartományt is megadhatunk, de ha élünk ezzel a lehetőséggel, akkor deklarálnunk kell a használandó tartomány sorszámát is. Az adat-tartományok számozása megadásuk sorrendjében történik. A függvény argumentum-listája tehát a következő: INDEX(( <adat-tartomány1> ; <adat-tartomány2> ; <adat-tartomány3> ; <adat-tartományx> ) ; <sor-index> ; <oszlop-index> ; <adat-tartomány index> ). Egyoszlopos adat-tartományok deklarálásakor a harmadik argumentum üresen maradhat.
eltolás függvény
Na tessék, itt van még egy két-funkciós függvény. Mint az INDEX, ő is tartalmat vagy hivatkozást ad eredményül, de nem egyetlen celláét, hanem egy egész tartományét. De, miért ez a furcsa név: „eltolás”? Azért mert, a szóban forgó tartomány pozícióját egy cellához képest kell megadnunk, úgy mintha, a tartomány első cellája eredetileg ebben a cellában „állt” volna, aztán eltolták onnan. Mármint az egész tartományt! Tehát két cella játsza a főszerepet. Az egyik, a viszonyítási pontként szolgáló cella, amit nevezzünk bázis cellának. A másik, a tartomány bal felső sarkában álló cella, amelynek neve legyen egyszerűen első cella.
A képen a függvény argumentumait szemléltető modellt látjuk. A F7:G9 tartomány pozícióját a B2-es cellához viszonyítva határoztuk meg az ELTOLÁS függvény argumentumaival. Ebben a képletben a függvény a tartomány tartalmát, tehát a hat darab számot adja eredményül. Nyílván a SZUM miatt.
Nézzünk egy példát a függvény másik funkciójára is. Tehát a hivatkozás-képzésre. Töröljük az előbb létrehozott „terv” név képletét és adjuk meg a tartományát, most az ELTOLÁS függvénnyel!
A bázis- és az első cella ebben a feladatban azonos, így nincs se függőleges, se vízszintes eltolás. Ezért a második és a harmadik argumentum nulla. A függvény negyedik argumentumában álló kifejezés, a HÓNAP( MA() ), a tartomány utolsó cellájának sorát adja eredményül. De nekünk a tartomány magassága kell, ezért vontam le belőle egyet.
hivatkozás függvény
Ezzel a függvénnyel ellenőrizhetjük egy kifejezés eredményét, vajon hivatkozást adott-e? Egyetlen argumentuma a kifejezés, illetve az azt tartalmazó cella hivatkozása. Eredménye logikai érték. Ha hivatkozást ad a kifejezés, akkor IGAZ, különben HAMIS.
Az alábbi táblázatban hallgatók pontszámai állnak. Az előzőekben tárgyalt műveletek közül vizsgáljunk meg néhányat a HIVATKOZÁS függvénnyel!
Először szöveg-összefűzéssel képeztem hivatkozást (melyik cellában áll a „Magyar történelem 1” tantárgy legmagasabb pontszáma), majd a CÍM függvénnyel (melyik cellában áll Angyal Örs neve). Ezek az eljárások, mint láttuk, karakter-láncot adnak eredményül és csak az INDIREKT függvénnyel történő deklaráció után válnak valóságos hivatkozássá. Ezt bizonyítja a HIVATKOZÁS függvénnyel végzett ellenőrzésük is.
A harmadik művelet egy tartalom-keresés az INDEX függvénnyel (melyik Angyal Örs legsikeresebb tantárgya). A „Világtörténelem 2”-t kaptam eredményül, mert ebben érte el a legmagasabb pontszámot. Ezt a kifejezést vizsgálva a HIVATKOZÁS függvény a „VT-2” eredmény ellenére is, IGAZ értéket adott. Az INDEX és az ELTOLÁS függvények eredménye, mint láttuk, vagy a cella tartalma vagy a hivatkozása. Tehát, „akár hivatkozás is lehetne” mondja a HIVATKOZÁS függvény ebben az esetben.
margitfalvi.arpad@gmail.com
