Igazán sokrétűen használhatjuk az Excelt a mindennapi munkánk során. A program alkalmas különböző számítások elvégzésére, adatok szűrésére, rendezésre vagy az adatok összefüggéseinek grafikus megjelenítésére diagramok formájában. Ebben a cikksorozatban áttekintjük ezeket a lehetőségeket, és számos mintapélda bemutatásával az Excelt hasznos segítőtársunkká tesszük. A bemutatott példa táblázatok letölthetőek weboldalunkról.
Abszolút és relatív hivatkozások
Habár az Excelt alkalmazhatjuk csupán adatok rögzítésére és kinyomtatására, de akkor nem léptük túl azokat a lehetőségeket, amelyet egy egyszerű „kockás füzet” adhat számunkra. Az Excelben végezhetünk számításokat és létrehozhatunk logikai összefüggéseket is. Az egyes celláknak adhatunk értékeket, de a cellában található értékeket hivatkozások segítségével függővé tehetjük más cellák értékeitől.
Ha például az B1 cellába beírjuk, hogy: =123, ezzel értéket adtunk a cellának, ha viszont azt írjuk be, hogy: =A1, akkor hivatkozást hoztunk létre. A B1 cella értéke ekkor az A1 cella értékével lesz egyenlő.
A hivatkozásokat másolhatjuk is, ekkor viszont a másolás következtében meg fog változni a hivatkozó cella. Az ilyen típusú hivatkozást relatív hivatkozásnak nevezzük, mivel másoláskor mind az oszlop (A), mind a sor (1) azonosítója megváltozhat. Fogalmazhatnánk úgy is, hogy ez a fajta hivatkozás a hivatkozott és a hivatkozó cella relatív elhelyezkedésére vonatkozik.
A fenti esetben ez azt jelenti, hogy a cella értéke legyen egyenlő a balra mellette található cella értékével. Ha ezt a hivatkozást ebben a formájában másik cellába másoljuk, akkor ez a szabály a másolással tovább öröklődik, és a másik cella értéke is a balra mellette lévőével lesz azonos. Ha a másolás során olyan pozícióba kerül a hivatkozás, amely túlmutat a munkalap határain, akkor: #HIV! hibajelzést fogunk kapni.
Relatív és abszolút hivatkozások
Van azonban lehetőség olyan hivatkozás létrehozására is, amely másolás során megőrzi az eredetileg hivatkozott cella oszlop- és sorazonosítóját. Az ilyen típusú hivatkozást abszolút hivatkozásnak nevezzük. Ilyenkor a hivatkozásban az oszlop- és sorazonosítók előtt egy dollárjelet („$”) láthatunk. Az ilyen hivatkozás a másolás során megőrzi az eredetileg hivatkozott cella azonosítóit, tehát mindig ugyanoda fog mutatni. Abszolút hivatkozás például: =$A$1, ekkor az adott cella értéke – bárhová is másoljuk – mindig az A1 cella értékével lesz azonos.
Létrehozhatunk vegyes hivatkozásokat is, amikor egy cellának vagy csak az oszlop-, vagy csak a sorazonosítóját rögzítjük. Ilyen esetben a cella másik azonosítója másolás során a relatív hivatkozásban látottaknak megfelelően módosul. Vegyes hivatkozás például: =E43/I$42. Ilyen típusú hivatkozást olyan esetekben használhatunk, ha például egy táblázat számos elemét el kell osztanunk a táblázat egy adott cellájában található értékkel. Fenti példánkban másoláskor a sorazonosító rögzített marad.
A dollárjelet beírhatjuk kézzel is, de praktikusabb, ha a hivatkozás létrehozása után a szerkesztőlécen a hivatkozásba kattintva az [F4] funkcióbillentyű többszöri megnyomásával hozzuk létre a hivatkozás végleges alakját. Az alaphivatkozás: =E43/I42. Az egérkurzorral kattintsunk az I42 cellába, majd az [F4] billentyű többszöri megnyomásával megjelenő lehetőségek közül (rendre: $I$42, I$42, $I42, I42,) válasszuk ki a kellő variációt. Látható, hogy a negyedik gombnyomásra kimerítettük a lehetőségeket, és visszajutottunk az eredeti relatív hivatkozáshoz.
Függvények
A cellákba nemcsak hivatkozásokat írhatunk, hanem képleteket és függvényeket is, amely ekben a hivatkozások is felhasználhatók. A következőkben a függvény működését a matematikában megszokottnál némileg lazábban magyarázzuk el, ezért jobb, ha a matematikusok inkább átugorják a most következő részt.
Egy függvénynek van kiindulási értéke (argumentum), van végeredménye, a kettő között pedig egy szabályrendszer, ami meghatározza, hogy a kiindulási értékből hogyan kapjuk meg a végeredményt. Egy egyszerű példa: GYÖK(225) = 15. Itt a 225 a kiindulási érték, a végeredmény a 15, a szabály pedig a gyökvonás.
Ha a szabály és a kiindulási adat valamilyen módon nem illik össze, nem kapunk eredményt. Lássunk erre is egy példát! Legyen a feladat a következő: „Kérem, adja össze a következő három számot: 4 és 7.” De hát hol a harmadik szám? Így nem lehet kiszámolni az eredményt! Az Excel is hibát fog jelezni, nem is hagyja beírni a függvényt, ha hiányzik valamely kiindulási érték.
Egyes függvényeknek nincsenek argumentumaik, látszólag nem kell hozzájuk semmilyen egyéb adat. Ilyen például a π (Pi) értéke, amelyet az Excelben egy függvény formájához hasonló módon kaphatunk meg: =PI(). Ennek eredménye rögzített: 3,1415927. Ilyen továbbá a mai dátum függvénye is: =MA(), amely a rendszerben tárolt dátumértéket adja vissza eredményként.
A függvényeket és a képleteket többféle módon is megadhatjuk a cellában:
- beírjuk kézzel, hogy =C3+12
- vagy begépeljük, hogy =HA(D4>$H$7;5;HA(D4>$H$6;4;HA(D4>$H$5;3;HA(D4>$H$4;2;1))))
Valljuk be, ez utóbbi azért ritkán szokott elsőre sikerülni.
Függvények egymásba ágyazása
Ha bonyolultabb, több kiindulási adattal rendelkező, vagy több egymásba ágyazott függvényt tartalmazó képletet szeretnénk rögzíteni, akkor praktikus, ha az fx (függvény beszúrása) gombot használjuk a szerkesztőléc elején. Ezzel megkapjuk a függvények listáját, amelyből a számunkra megfelelő függvény kiválasztása után a hozzá tartozó argumentumok kitöltésében és azok jelentéstartalmának értelmezéséhez is kapunk segítséget.
Érdemes tehát a párbeszédpanelen megjelenő szöveget figyelmesen elolvasni. Ha mindez mégsem elegendő, a párbeszédpanel bal alsó sarkában lévő súgóra kattintva további útmutatáshoz és példákhoz juthatunk. Ha jól adjuk meg az adatokat, akkor a párbeszédablakban már a függvény szerkesztése közben láthatjuk az előzetes eredményt, illetve ha mégsem, akkor sejthetjük, hogy hiba van valahol.
Függvényargumentumok és az eredmény
A többszörösen egymásba ágyazott függvények esetében azonban ez nem segít. Ha ugyanazt a függvényt kell többször egymásba ágyazni, akkor az alapfüggvényt elkészítve, azt a szerkesztőlécről kimásolva önmagába többször beilleszthetjük. Utólag persze kisebb módosításokat kell még elvégezni, de a nehezén ezzel már túl vagyunk.
Nézzünk egy példát! A diákok dolgozatot írtak. A feladat egy olyan képlet létrehozása, amely egy segédtáblázat alapján az elért pontszámuknak megfelelő érdemjegyet írja a cellába.
A példában szereplő logikai HA függvény felépítése: =HA(logikai feltétel;igaz ág;hamis ág). Ennek alapján legyen az eredeti (kiindulási) függvényünk: =HA(D4>$H$7;5;4).
A kiindulási függvényt a hamis ágba a 4-es szám helyére többször bemásolva ágyazzuk egymásba: =HA(D4>$H$7;5;HA(D4>$H$7;5;HA(D4>$H$7;5;HA(D4>$H$7;5;4)))).
Végül, a ponthatárok hivatkozásait és az érdemjegyeket kiigazítva megkapjuk a kész képletet: =HA(D4>$H$7;5;HA(D4>$H$6;4;HA(D4>$H$5;3;HA(D4>$H$4;2;1)))).
Lássuk a hivatkozásokat!
A függvény argumentumait úgy is módosíthatjuk, hogy kijelöljük a függvényt tartalmazó cellát, majd megnyomjuk az [F2] funkcióbillentyűt. Ennek hatására azok a cellák, amelyekre a függvényben hivatkoztunk, színes keretet kapnak, a függvényt magát tartalmazó cellában pedig szerkesztési módba lépünk.
Az [F2] lenyomására színekkel emeli ki a szoftver a hivatkozott cellákat
Elődök és utódok mutatása nyilakkal
A színes keret a cellaáthelyezéshez hasonlóan mozgatható, így a függvényargumentum hivatkozása grafikusan is módosítható. Ahogy a keretet mozgatjuk, úgy változik a hivatkozás a cellában. Ha ennél még szemléletesebbé szeretnénk tenni a függvény felépítését és kiindulási adatait, akkor használhatjuk a Nyilak megjelenítése funkciót, amelyet a Képletek -> Képletvizsgálat -> Elődök mutatása és Utódok mutatása menüpontokban érhetünk el. E funkciók bekapcsolásával jól látható nyilakkal jelöli ki a szoftver azokat a cellákat, amelyekhez az adott képlet hivatkozásokkal kapcsolódik. Az Elődök mutatása azokra az adatokra mutat, amelyek a képlet kiszámításához szükségesek, míg az Utódok mutatása azt mutatja meg, hogy egy adatot vagy eredményt hol használ fel a program. Egy cella tartalmának törlése előtt érdemes megnézni, hogy felhasználásra került-e valahol az adott cellában található adat, mert ha igen, és mégis töröljük, akkor a hiánya biztosan hibát fog okozni.
Körkörös hivatkozás hibaként
Hivatkozások létrehozásakor tipikus hibalehetőség a körkörös hivatkozás, amikor egy képlet közvetlenül vagy közvetve a saját cellájára hivatkozik. Ha például a C2 cellába beírjuk, hogy =A2+C2, akkor ennek értéke kiszámíthatatlan lesz. Szerencsére az Excel már a cellába való beíráskor hibajelzést ad, ha körkörös hivatkozást készülünk létrehozni. Ha ennek ellenére erőltetjük a dolgot, és figyelmen kívül hagyjuk a hibajelzést, akkor rögzíthetjük ugyan ezt az állapotot, de az elődök és utódok mutatásához hasonló figyelmeztető nyíl azért látható marad a munkalapon.
Hivatkozás másik munkalapra, munkafüzetre
Hivatkozásokat nemcsak egy munkalapon belül hozhatunk létre, hanem van mód további oldalakra való hivatkozásra is. Ha egy hivatkozás az aktuális munkalapon túlmutat, akkor egy további adat, a hivatkozott munkalap nevének magadása is szükséges az alábbi formában: =Munka2!A1. Az egyenlőségjel után ilyenkor a munkalap neve és egy felkiáltójel látható, majd ez után következik az oszlop- és sorazonosító.
Amennyiben egy másik munkafüzetből szeretnénk adatokat átvenni, akkor a hivatkozás további kiterjesztésére van szükség: ='[Tudásszint bemutató.xls]Alapadatok'!$C$7. Itt szögletes zárójelben megjelenik annak a munkafüzetnek neve is, ahol a hivatkozott cella található. Másik munkafüzetre mutató hivatkozást is létrehozhatunk egyszerűen, néhány kattintással. Nyissuk meg mindkét munkafüzetet, jelöljük ki azt a cellát, ahová a hivatkozást szeretnénk beilleszteni, írjunk be egy egyenlőségjelet, majd váltsunk át a másik munkafüzetre, itt kattintsunk bele a hivatkozni szándékozott cellába, és üssük le az [Enter] billentyűt. Másik munkafüzetre történő hivatkozásnál azonban ügyelni kell arra, hogy a hivatkozott munkafüzetet nehogy töröljük vagy átnevezzük a későbbiekben!
Minta összetett függvényekre
A függvényeket a megfelelő cél érdekében kombinálhatjuk is, ennek az eredménye az alábbihoz hasonló többszörösen összetett függvény lehet: =ÖSSZEFŰZ(BAL(D5;2);$E$11;KÖZÉP(D5;3;$E$13);$E$12;KÖZÉP(D5;$E$13+3;$E$14))
Ez a függvény a folyamatos számsorként megjelenő telefonszámokat (például 201234567) egy jobban olvasható formára (például 20/123-4567) alakítja. Ehhez az ÖSSZEFŰZ, a BAL és a KÖZÉP nevű szövegfüggvényeket használtuk fel. A függvények argumentumainak megadásánál éltünk azzal a lehetőséggel, hogy nem feltétlenül muszáj konkrét értéket megadnunk, hanem lehetőség van hivatkozás megadására is, sőt a fenti függvénybe még egy kis számítás is ($E$13+3) belekerült.
Tatai István
www.excelexpert.hu
Sorozatunk további elemei:
- Excel-kisokos - Adatok kezelése
