Tamás Ferenc: A táblázatkezelés alapjai – 2. rész


Egyszerű hivatkozások

A cikk-sorozat ezen részében az Excel legfontosabb függvényei jönnek sorra, de előbb a hivatkozásokat kell alaposan megismerni!
A legtöbb táblázatkezelőben az alapvető munka egy óriási mezőn folyik, amit az Excel munkalapnak hív. Amint arról szó esett az előző részben, ennek a munkalapnak vannak (betűvel jelölt) oszlopai és (számmal jelölt) sorai, amik így együtt egy koordináta-rendszert adnak meg. De nem csupán egyetlen munkalap létezik, hanem (a memória szabta korlátokon belül) elég sok lehet. Ha az alapvetően adott munkalapot egy igen nagy méretű lapnak vesszük, akkor a többi munkalapra nyugodtan gondolhatunk úgy, mint ugyanolyan méretű lapokra, amik a legelső munkalap „felett” vannak, mint a könyv lapjai. Ilyen esetben már egy térbeli koordináta-rendszerrel van dolgunk.
Ha csak egy munkalapon belül gondolkozunk és azon belül csak egy cellára hivatkozunk, akkor azt nyugodtan megtehetjük a megszokott +A2 minta alapján, ami most az A2-es cellára való hivatkozást jelenti. Ha ezt a +A2 tartalmat beírjuk egy cellába, akkor pontosan azt az eredményt kapjuk, amit az A2-es cella tartalmaz. Ez minden más cellára igaz, kivéve az A2-esre, mert egy cella soha nem hivatkozhat önmagára! Ha másik munkalapból szeretnénk hivatkozni az A2-es cellára, akkor előbb meg kell adnunk a munkalap nevét, majd csak utána a cella megszokott hivatkozását, tehát a helyes forma: +Munkalap1!A2. Begépeléskor van egy kis segítségünk, mivel nem kell mindenképpen a pontos koordinátákat bepötyögnünk, hanem a megszokott + leírása után elegendő a kívánt koordinátájú cellára kattintani és így a képlet kiegészíti magát. (Megjegyzés: egyes táblázatkezelőkben a képlet nem beírását nem + jellel kell kezdeni, hanem = jellel. Szerencsére az Excel ezt a formátumot is megengedi.)
Már a cikksorozat előző részében is szó volt arról, hogy nem csupán egyetlen cellára hivatkozhatunk, hanem egész tartományra is. Ilyen esetben meg kell adni a tartomány két sarkát, pl.: A1:C3. Fontos, hogy ilyenkor a tartomány két ellentétes sarkát adjuk meg, különben a program nem megfelelő eredményt ad. Szerencsére a rendszer elég rugalmas, mivel bár a cellába bal felsőtől a jobb alsó sarokig kerül be az eredmény, de nekünk nem kell pontosan ilyen sorrendben kijelölni. Apropó, kijelölés! Ezt a legegyszerűbben a SHIFT+nyilak kombinációjával tehetjük meg, de itt is lehetőség nyílik az egérrel való kijelölésre is, ha az egyik választott saroktól az ellentétes sarokig lenyomva végighúzzuk az egeret a munkalap felett. További könnyebbség, hogy az Excel általában befejezi a képleteket, mivel pl. a +SZUM(A1:C3 képlet esetén nem kötelező beírni az összegzést lezáró zárójelet, hiszen ezt az alkalmazás megteszi helyettünk!
 
Kicsit bonyolítja a helyzetet, ha nem egybefüggő a hivatkozni kívánt tartomány. Ilyen esetben a további tagok bevitele előtt egy pontosvesszőt kell a képletbe illeszteni. Az előző példánál maradva az újabb verzió: +SZUM(A1:C3;D5).
Nagyon komoly segítség a már megismert képlet-másolás. Nézzük meg, hogy ez mit is jelent pontosan! Ha például az A1:A10 mezők A12-ben lévő átlagát, tehát a +ÁTLAG(A1:A10) képletet szeretnénk másolnia B12-be, illetve a C12-be, akkor nem kell a képletet újra leírni, mivel a megszokott másolás-beillesztés kettőssel megoldódik a képlet-átvitel. Tehát a B12-be a következő képlet kerül: +ÁTLAG(B1:B10), míg a C12-ben ez lesz: +ÁTLAG(C1:C10). Szerencsére ez egészen komoly formulákkal is teljesen megbízhatóan működik.
Vegyünk most egy másik példát! Az A1:F10 cellákban legyenek számok, melyeket soronként kell kiátlagolnunk. Ehhez a G1-es cellában készítsük el a megszokott +ÁTLAG(A1:F1) képletet. Ezt a megszokott módokon is másolhatjuk lefelé (G10-ig), de az Excel szerencsére nyújt egy újabb lehetőséget is: az aktuális cella fénymutatóját vigyük rá a G1-es cellára, az egérrel pedig álljunk a G1-es cella jobb alsó sarka fölé (pontosan oda, ahol a megszokott nagy + egérmutató átvált sokkal kisebbre). Ha itt lenyomjuk az egér bal gombját, majd visszük lefelé az egeret, akkor a képlet értelemszerűen másolódik, amint a bal gombot elengedtük.
 

Abszolút és relatív hivatkozások

Időnként szükség van arra, hogy egyes képletből ne minden cella másolódjon automatikusan, hanem csak egyes elemei. Vegyünk példának egy banki betétekkel és kamatokkal foglalkozó táblázatot. Az A oszlopban legyenek az egyes betétek fantázianevei, a B oszlopban a kamat 1 éves elhelyezés esetén, míg a D oszlopban lássuk a lehetséges betétek összegét, majd az E oszlopban a lejárat utáni kamatot. Elég könnyen belátható, hogy ha ilyen esetben a megszokott módon másoljuk a képletet, akkor nem a valódi kamatokat kapjuk az E oszlopban. Tehát ebben a példában szükség van arra, hogy a képlet egyes eleme, nevezetesen a kamatláb ne változzon másolás közben. Ezt úgy érhetjük el, hogy a kamatlábat mutató cella-hivatkozás minden egyes eleme elé $ jelet teszünk. Tehát a példánál maradva az eddigi, helytelenül másolódó képlet: +D2*B2/100. Ezt javítsuk át a következőre: +D2*$B$2/100. (Megjegyzés: javítani a képletre kattintva lehet, vagy az aktuális cella képlete az F2-vel is szerkeszthetővé válik.) Így a képletben a relatív hivatkozás (D2) simán másolódik, viszont az abszolút hivatkozás ($B$2) mindig változatlan marad.
 
Időnként szükség lehet arra is, hogy csak a sor vagy csak az oszlop maradjon változatlan. Ezt vegyes hivatkozásnak nevezik. Példa: +$A1. Ilyenkor másoláskor az A oszlop változatlan marad, viszont az 1-es sor átcserélhető. Másik példa: +A$1. Ebben az esetben az 1-es sor változatlan marad, ám az A oszlop átváltozik.
Lássunk most egy másik példát! Készítsünk egy egyszerű valutaváltó táblázatot. Lényege: adott három valuta (CHF, USD, EUR), amiket a pillanatnyi árfolyamon át kell váltanunk magyar Forintra (HUF). A feladat lényege: az átváltási árfolyamokat rögzítve segítsük az egyes összegek HUF-ra váltását! Itt meggondolandó, hogy abszolút vagy vegyes hivatkozásokat kell-e alkalmaznunk.
 

Fontosabb eszköztárak

Valamennyi mostanában használt Microsoft program lényege az eszköztárak használata. A régebbi legördülő menükkel szemben a redmond-i óriás eszköztárakban látja a megoldást. A Kezdőlap menühöz tartozó eszköztárban használt elemek: Vágólap, Betűtípus, Igazítás, Szám, Stílusok, Cellák és Szerkesztés. (Ez a verziótól és az egyéni beállításoktól függően változhat!)
 
Minden elem jobb alsó sarkában van egy kis lefelé mutató nyíl, melyre kattintva lenyitható a teljes vezérlőpanel.
Én személy szerint nem rokonszenvezek a Microsoft mindent uralni akaró lendületével és marketingjével, de a táblázatkezelőben több megoldásuk is kifejezetten tetszik. Ezek közül a tényleg jól működő Vágólap az első. Tapasztalatból állítom, hogy az Office csomag programjai közötti átmenet kifejezetten gördülékeny és az egyik programban bemásolt részletet a másik alkalmazás  egész jól beilleszti.
A Betűtípus a megszokott menüket és lehetőségeket tartalmazza. A cellák formázása alatt igen sok beállítási lehetőség van, közte például az egyedi formátum időnként sok fejtörést okoz a nem kellőképpen felvértezett felhasználónak.  A többi fül nagyjából értelemszerűen használható, bár arra nem árt felkészülni, hogy 1-2 rossz húzása itt is szokott lenni mindenkinek.  Az Igazítás semmi újdonságot nem tartalmaz, bár mindenképpen ki kell emelni a cellaegyesítés és a „Sortöréssel több sorba” nevű beállításokat. Mivel a redmond-i központ elég alaposan szokta frissíteni az Office program verzióit, ezért egy-egy új verziónál mindig érdemes 10-20 percet áldozni az éppen aktuális beállítások megismerésére. A Szám menüpontról már szó esett, de azért külön figyelmet érdemel a finomhangolások igen nagy száma!
A maradék három almenü már nem alakul át kisebb ablakká, így a Stílusok, a Cellák és a Szerkesztés esetén meg kell elégednünk a helyben legördülő lehetőségek garmadájával.

Grafikon

Jelentőségében fontosabbnak érzem a 2. főmenü, a Beszúrás alatti eszköztárat. Nagyon fontos, hogy igen látványos grafikonokat tudunk pár kattintással (és türelmes gyakorlással) létrehozni.
Példaként dolgozzunk fel tortadiagramba egy osztály matematika dolgozatainak eredményeit! A feldolgozandó táblázat a következő:
 
Az adatok bevitele után első lépésként jelöljük ki azt a területet, amelyről grafikont szeretnénk készíteni: ez az A1:B6 terület lesz. Most lépjünk át a Beszúrás főmenübe, ott pedig keressük ki a Diagramok csíkrészletét. A képen látható torta-diagramra kell kattintani a létrehozásért!
 
A kibukkanó menünél már láthatóvá válnak a lehetséges képek, amik között igen gyorsan ugrál a rendszer. Válasszuk ki a térbeli látványt nyújtó variánst. Most már egyből megjelenik a kész (tortaszerű) grafikon. A felbukkanó diagrameszközök csíkon még finomhangolást hajthatunk végre, illetve a diagram melletti kis ikonokat is érdemes átnézni!
 
A régebbi Excel-verziókban itt a diagram-varázsló lépéseit kellett egyesével megtenni, de ezt mostanra egy újragondolt menürendszer váltotta fel. Ízlés kérdése, hogy az újítás hasznosabb-e, mint a régi rendszer…
Javaslat: most a saját munkájával kapcsolatosan állítson össze 1-2 grafikont!

Gyakori függvények


Vegyünk most egy példát: egy fiktív cég legfontosabb adatait! Vegyük fel a bevételt és a kiadást havonkénti bontásban. (Lásd a képen!)
 
Első lépésként képezzük a havonkénti egyenleget. Így a D2-es cellába a következő képlet kell: +B2-C2. Ezt kell másolni a D3:D13-as területre. Következő lépésként vegyük az egyenleget. Ha a naptári évet 0-s alapegyenlegről számítjuk, akkor az előző havi egyenleget meg kell növelni az adott havi nyereséggel. Tehát az E2-es cellába simán be kell írni a D2 értékét (+D2), viszont az alatta lévő E3-as cellába már a következő képlet kerül: +E2+D3.
 
Ezek alapvető kis számítások voltak. Most mutassuk ki, hogy a hónap nyereséges volt, vagy nem. Ehhez az F oszlopban kell egy függvényt készíteni. Ezt legegyszerűbben a függvény beszúrása gombbal lehet megoldani.
 
Az itt felbukkanó ablakból ki kell választani a „HA” függvényt. A függvény precíz kitöltéséhez a program újabb ablakban nyújt segítséget.
 
Az F2-be írandó képlet a következő: =HA(D2>0;"igen";"nem"). Ezt persze másolni kell a megszokott módon lefelé. Így láthatóvá válik, hogy az első két hónap volt veszteséges, míg a többi nyereséges. Szeretném kiemelni, hogy ez bár a számok láttán azonnal is leolvasható, de ez a számolási táblázat tetszőleges számokkal is kiválóan működik.
Most számoljuk össze, hogy hány nyereséges hónap volt! Ehhez megint szúrjunk be egy függvényt. Itt az összes felkínált függvény közül a „DARABTELI”-t kell kiválasztani. Ez egy tartományban összeszámolja azon nem üres cellákat, melyek egy megadott feltételnek eleget tesznek. Ez a megadott feltétel ezúttal legyen az „igen”, tehát az, hogy a cella értéke igen legyen.
 
Most nézzük meg, hogy melyik hónapban volt a legtöbb a nyereség! Ezt a jól megszokott „MAX” függvénnyel kell elkészíteni. A teljes beírandó képlet: +MAX(D2:D13).

Következőleg szeretnénk kimutatni, hogy az előző havihoz képest hány százalékkal változott a nyereség. Ehhez a B és C oszlopok közé szúrjunk be egy új oszlopot. Ilyenkor az egész C oszlopot ki kell jelölni, majd az egér jobb gombjára kattintva a „Beszúrás” menüpontot kell kijelölni.
 
Az új C1-es cellába írjuk be, hogy „Növekmény”, majd szerkesszünk egy képletet. Mivel százalékos növekményt szeretnénk kimutatni, ezért képezni kell C3-ban a következőt: +B3/B2. Viszont ez még nem lesz százalékos formátumú, ráadásul alapértelmezésben túl sok a kiírt tizedes jegy. Először is hajtsunk végre egy kerekítést! A C3-ban lévő képletet szerkesszük át (F2-vel) a következőre: +KEREKÍTÉS(B3:B2;2). Ám ekkor még nem lesz százalék-formátumú az eredmény, ezért újra forduljunk a jobb gombos menühöz, ahol ezúttal a cellaformázásra kattintsunk, majd a legelső (Szám feliratú) fülön válasszuk ki a Százalék formátumot, de 0 tizedes jeggyel. Ekkor már megfelelő lesz a kiírási formátum.

Persze még rengeteg függvény van, így ez a cikk nem mer vállalkozni az összes bemutatására; csupán arra, hogy a leggyakrabban használtak közül párat bemutasson! Van viszont egy javaslatom: ha nem tudja a megfelelő függvény nevét, sőt még azt sem, hogy egyáltalán létezik-e ilyen függvény, akkor bátran kattintson a „Képletek” főmenüre, ahol a legfontosabb függvények értelemszerűen vannak csoportokba szedve. Az alsó képen egyik ilyen csoport kibontva is látható!

 
Egy osztály év végi statisztikája


A következőben egy régi osztályom év végi statisztikájának elkészítését szeretném lépésenként megmutatni. A nevek természetesen nem valódiak, hanem a NATO fonetikus ABC-jéből származnak. Ráadásul 1-2 eredményt is változtattam a jobb (érdekesebb) feldolgozhatóságért.
Az adathalmaz a következő: adott 26 ember, mindenkinek van 11 tantárgya, melyből kapott év végi osztályzatot. Pár ember már érettségizett (bejegyzés: ér.), illetve felmentett (fm.), így náluk 1-2 tantárgynak nincsen év végi jegye. Ezen kívül mindenkinél van igazolt és igazolatlan hiányzás. Célok: 
-    egy olyan osztály-statisztika előállítása, melyben mindenkinek minden jegye szerepel
-    mindenkinek számoljuk ki a személyi átlagát (sima számtani átlag)
-    számoljunk úgy is átlagot, ha valaki legalább egy tárgyból bukott, akkor az átlaga 1,00.
-    nézzük meg, hogy ki hány tárgyból bukott
-    nézzük meg, hogy ki jogosult osztályozó vizsgára és ki nem (ha 3 vagy több tárgyból bukott, akkor nem pótvizsgázhat)
-    számoljuk ki a tantárgyi átlagokat
-    számoljuk össze tárgyanként hány és milyen osztályzat született
-    számoljuk össze, hogy hány fő bukott és összesen hány tárgyból
-    számoljunk osztályátlagot, igazolt és igazolatlan átlagot

Első lépésként az első oszlopba vigyük fel a sorszámokat. Ezt a legegyszerűbben úgy tehetjük meg, hogy az A1-es cellába 1-et írunk, míg az A2-be a következőt: +A1+1. Most az A2-t lemásoljuk az alatta lévő cellákba, egészen A26-ig. Most vigyük fel a neveket szépen egymás alá a B oszlopba. Mivel a B oszlop időnként túl szűk (pl.: November és Whiskey nem férnek ki egy sorba), ezért növeljük meg a méretét értelemszerűen. Ha most a megfelelő sorok nem húzódtak vissza eredeti méretükre, akkor csökkentsük a renitens sorok magasságát a megszokottra.
 
Most fel kell vinni a tantárgyakat, de ehhez szükségünk van egy újabb sorra az 1-es sor felett. Ehhez jelöljük ki az 1-es sort, majd pl. az egér jobb gombjával hívjuk elő a lokális menüt és válasszuk ki a „Beszúrás” menüpontot. Most már nyugodtan felvihetjük az egyes tantárgyak neveit!
 
Sajnos itt megint rossznak bizonyul az alapértelmezett sormagasság. Később látni fogjuk, hogy sokkal célszerűbb az egyes tantárgyak neveit függőlegesen írni, sorban egymás mellé. Ehhez jelöljük ki az első sort, majd a „Jobb gomb\Cellaformázás\Igazítás” sorrendjében válasszuk ki az „Elforgatás”-t, ahol a képen látható módot adjuk meg!
 
Ez most már kezd jó lenni, de az egyes tantárgyak cellái túl szélesek, így jelöljük ki a C-M oszlopokat, majd csökkentsük a szélességüket megfelelőre. Érdemes a magatartás-szorgalom után egy üres oszlopot beszúrni, illetve az A oszlop szélességét is csökkenteni, valamint az A oszlop celláit középre rendezni. Az O1-be írjuk be az „Igazolt”, majd a P1-be az „Igazolatlan” neveket.
 
Most már kialakult az alapvető nézet, így nyugodtan fel lehet vinni az egyes eredményeket (jobb híján kézzel). Tapasztalatom szerint ez el szokott tartani némi ideig…
 
Elsőnek számoljuk ki a személyi átlagokat! Ehhez az R1-be írjuk be az „Átlag” szót, majd az R2-be a megszokott képletet: +ÁTLAG(F2:N2), mivel a magatartás és a szorgalom nem számít bele az átlagba.
 
Most már másolhatjuk az átlag számítását az R3:R27 tartományba. Ha nem 2 tizedes jegyre jött ki az átlag, akkor javítsunk a formulán a megszokott +KEREKÍTÉS függvénnyel; valamint a cellaformázással adjuk meg, hogy 2 tizedes jegyet jelenítsen meg mindenkinél.
 
Mivel van egy másik számítású átlag is, ezért az S oszlopban jelenítsük meg az 1-et, ha valakinek van egyese, viszont ha mindenből átment, akkor a sima átlag jöjjön. Az S2-es formula a következő: =+HA(MIN(F2:N2)=1;1;R2).
 
A fenti képet esetleg lehet finomítani azzal, hogy a másik átlagot csak akkor íratjuk ki, ha az elsőtől eltér. Így a megfelelő formula a következő: =+HA(MIN(F2:N2)=1;1;”ua”).
A T oszlopba a bukások számát illesszük be, tehát össze kell számolni a sorban lévő 1-es jegyeket. Ezt a következő képlettel tudjuk megtenni legegyszerűbben: =SZUMHA(F2:N2;1). Ha már másoltuk a képletet, akkor ennek felhasználásával lehet a következő, U oszlopba kiírni az egyes tanulók sorsát. Ha nem bukott, akkor semmi, ha 1 vagy 2 tantárgyból bukott, akkor pótvizsga, különben évismétlés. Ez egy összetett függvény. A gyakorlatlan ezt úgy érdemes elkészíteni, hogy először csak az egyik feltételt írjuk meg, majd csak utána készítsük el az előzőben benne foglalt másodikat is. A végeredmény lehet például ez is: =+HA(T2>2;"évismétlés";HA(T2=0;" ";"pótvizsga")).
 
Most, hogy végeztünk az egyes emberek feldolgozásával, jöhetnek a tantárgyak! Elsőnek is a 29-es sorba készítsük el az átlagokat. Ehhez a B29-es cellába írjuk be az „Átlagok” feliratot. A legegyszerűbben a C29-es képletet hozhatjuk létre: =+ÁTLAG(C2:C27). Ezt majd másolhatjuk jobbra, de láthatjuk, hogy nem férnek ki a megszokott tizedes jegyek. Éppen ezért ezt a sort ki kell tágítani, tehát a sor magasságát meg kell növelni, majd az egyes tárgyak átlagait (C29:N29 tartományban) be kell forgatni (cellaformázással). Végül az egységes kinézet kedvéért szintén cellaformázással állítsunk be 2 tizedes jegyet.
 
Most számoljuk össze, hogy az egyes tárgyakból hány és milyen osztályzat született! Ehhez egy olyan jól másolható formulát kell találni, ami ezt végrehajtja. Ilyen esetekben az a javaslatom, hogy első lépésként egy sima, egyetlen esetben működő formulát állítsunk össze, ami a C30-as cella esetén a következő lesz: =DARABTELI(C2:C27;B30). Ez korrektül összeszámolja az 5-ös magatartást kapott tanulókat, ám kudarcot vall a másolásnál. Mivel az összegzendő oszlopot másoláskor csak vízszintesen kell csúsztatni, ám függőlegesen nem, így a C2:C27 helyett vegyes hivatkozás kell: C$2:C$27. További probléma, hogy megadott jegyeket sem kellene átvinni máshová. Itt az előzővel ellentétben a sorokat kell változtatni, ám az oszlopot nem, tehát a B30 helyett a B$30-at kell beírni. Így a végleges tartalom ez lesz: =DARABTELI(C$2:C$27;$B30).
 
Most már a végső összesítés van hátra! Elsőnek írjuk ki a J36-os cellába az osztálylétszámot. Ez látszólag egyszerű, mivel csak az A oszlop utolsó nem üres elemét kell átmásolni, de számolni kell azzal is, hogy ezt a táblázatot nem ekkora osztálylétszámmal is szeretnék használni. Tehát a megfelelő formula: =+MAX(A2:A1048576).
A J37-be írjuk ki a bukott tanulók számát. Gyakorlatilag itt össze kell számolni mindazokat, akik 0-nál több tárgyból buktak. Ezt a „Darabteli” függvénnyel tehetjük meg, mégpedig a T oszlopon. A pontos képlet a következő (26-os osztálylétszámra): =DARABTELI(T2:T27;">0"). A J38-ba írassuk ki, hogy a fentiekből hányan tehetnek pótvizsgát. Megoldás: =DARABTELI(U2:U27;"pótvizsga"). Most a J39-be írassuk ki az évismétlők számát: =DARABTELI(U2:U27;"évismétlés").
Mivel kiemelt jelentőséggel bír az osztályátlag, ezért azt külön érdemes kiíratni, most a J40-be: +R29. Mivel ez megint nem fér el az összeszűkített cellába, így jelöljük ki a J40:K40-es területet, majd egyesítsük a cellákat! Hasonlóan járjunk el az alatta szereplő igazolt és igazolatlan átlagokkal. Itt sem szabad elfelejtkezni a cellaegyesítésről. Mivel itt gyakran előfordulhat, hogy nem a megszokott 2 tizedes jegyre történik a kerekítés, ezért ezt tegyük meg most a cellaformázással (a J40:K42-es területen soronként).
 
Most már készen vagyunk minden eltervezett feladattal, csupán az van hátra, hogy ízlés szerint keretekkel lássuk el a kész kimutatást!

A cikksorozat következő részében egy komplett középiskolai érettségi Excel feladatnak megoldását fogom bemutatni lépésenként!



© TFeri.hu, 2015. márc.