Adatok áttekintését segítő eszközök

Az itt bemutatott eszközök használata - beépített funkciók lévén - nagyon egyszerű, az eredmény pedig látványos. Meg van azonban a hátrányuk is: nincsenek tekintettel az adatok módosulására, vagyis nem adatkövetőek. Ez alól csak a részösszeg kivétel, de az is csak részben: a kiszámított adatok frissülnek ugyan, de sorok törlésére és hozzáadására, sajnos, nem reagál jól.

Az eszközök használatának bemutatásán túl, a legtöbb esetben szó esik majd arról is, hogy hogyan lehet az adott eszközt függvényekkel kiváltani, és így adatkövető megoldást készíteni.

1. Rendezés

Mivel a rendezés az egyik leglátványosabb és egyben legkönnyebben elvégezhető művelet a táblázatokkal, tanítására már a korai szakaszban sor kerül. Emiatt versenyen ritkán kérnek egy az egyben rendezést, inkább csak amolyan szükséges lépésként jelenik meg, egy komolyabb feladat részeként. A teljesség kedvéért először mégis egy egyszerű példán keresztül mutatom be használatát, hogy sorra vehessem a hibázási lehetőségeket, érdekességeket.

Ebben a példában nincs szükség importálásra, az adatokat xls-formátumban kapjuk meg. A feladat a munkalap elnevezése, és a kapott táblázat rendezése, két szempont alapján. Először is a locsolók nevét (Ki locsolt?) kell abc szerint növekvően rendezni. Mivel egy locsoló általában több helyen is járt, ugyanazon név több, egymás alatti sorban is szerepelhet. Az ilyen esetekben van értelme annak, hogy még egy szempont alapján is rendezzük az adatokat: a feladat azt kéri, hogy a locsolókon belül a meglocsoltak nevei (Kit locsolt?) is legyenek abc rendben.

A kép (nagyobb változata) külön ablakban is megtekinthető.33_full.jpg33. ábra: Rendezés két szempont alapján, a fejlécek megfelelő kezelése mellett.

A legújabb Excel mellett már sokkal könnyebb dolgunk van, mint a régebbi verziók esetén. Nincs szükség a rendezendő táblázat kijelölésére, elég annak egyik celláján állni. A rendezésre kattintva ugyanis automatikusan kijelölésre kerül az az összefüggő rész (táblázat), ahol álltunk. Kézi kijelölésre csak akkor van szükség, ha valamilyen speciális helyzetből adódóan a szokásostól eltérő módon helyezkednek el rendezendő adataink.

• A táblázatban állva kattintsunk az adatok fülön a rendezés gombra!
• A rendezés ablakot töltsük ki az ábrának megfelelően, majd hajtsuk végre a rendezést!

A feladatot ezzel megoldottuk.

A gyakorlatlanabbak könnyen eshetnek abba a hiába, hogy véletlenül a fejlécet is beleveszik a rendezésbe. Ennek a hibának az elkerülését egy jelölőnégyzet segíti, illetve árulkodó jel lehet a nem megfelelő beállításról az is, hogy az oszlopok kiválasztására szolgáló legördülő listában nem az oszlopok neve, hanem betűjele szerepel.

A rendezés gomb mellett megtalálható két másik gomb is. Ezek arra hivatottak, hogy meggyorsítsák az előbb leírt folyamatot, amennyiben csak egy szintű rendezést szeretnénk.

Jó, ha tudjuk, hogy a hagyományos rendezési módszereken túl lehetőség van úgynevezett egyéni lista szerinti rendezésre is. Például, ha egy oszlop hónapneveket tartalmaz, könnyen lehet, hogy azokat nem ábécé szerint szeretnénk sorba rakni, hanem aszerint, hogy hogyan követik egymást az évben. Konkrétan hónapok esetén van is egy beépített lista erre az esetre, speciális igények esetén pedig definiálhatunk mi magunk is "sorrendet".

A kép (nagyobb változata) külön ablakban is megtekinthető.34_full.jpg34. ábra: Egyéni lista szerinti rendezés. A négy beépített egyéni lista választásán túl lehetőség van új lista megadására is.

Ügyeljünk, hogy sose kövessük el azt a hibát, hogy úgy kattintunk a három rendezés gomb valamelyikére, hogy csak pár oszlop van kijelölve a táblázatból! Ha egyetlen oszlopot jelöltünk ki, még kapunk egy figyelmeztető üzenetet az Exceltől, de a nem megfelelő rádiógombot kiválasztva így is végérvényesen elronthatjuk, összekeverhetjük adatainkat. Sőt, ha több oszlopot is kijelölünk, de nem az összeset, akkor még csak hibaüzenetet sem kapunk.

A kép (nagyobb változata) külön ablakban is megtekinthető.35_full.jpg35. ábra: Ha nem a kijelölés bővítését választjuk, az adatok összekeveredhetnek.

Egyedi adatok esetén - tehát amikor a rendezendő adatok közt nincs két egyforma - azért van módszer arra, hogy a rendezés az adatok változása esetén automatikusan aktualizálódjon, ekkor azonban nem ezt a beépített funkciót kell használni, hanem magunk kell, hogy elvégezzük a feladatot. A következő példa ezt mutatja be.

A feladat egy automatikusan frissülő vizsgarangsor készítése, amit ráadásul egy másik munkafüzet adataiból kell elkészíteni. Az eddig tárgyalt rendezés funkció most nem lesz segítségünkre, hiszen azzal a kettő közül egyik feltétel sem valósítható meg. A feladatot képletekkel kell megoldanunk.

Nézzük előbb, hogy is néz ki ez a fájl, és az elkészítendő rangsor!

A kép (nagyobb változata) külön ablakban is megtekinthető.36_full.jpg36. ábra: Az ECDL.xls egy részlete.	A kép (nagyobb változata) külön ablakban is megtekinthető.37_full.jpg37. ábra: Az ECDL.xls adataiból készült vizsgarangsor, az NJSZT.xlsx egy munkalapján.

Akik használták már a NAGY() függvényt, azoknak biztosan egyből beugrik, hogy ez lesz a megoldás kulcsa. A továbbiakban bemutatom a végeredményhez vezető okoskodás egy lehetséges lépéssorozatát, tehát a végleges megoldás csak a végén fog kijönni.

Mivel az NJSZT.xlsx-ben is szerepelnek ezek az adatok, először dolgozzunk ebből, elhagyva azt a feltételt, hogy az ECDL.xls adatait használjuk fel.

• Határozzuk meg A30-ba a 2008-as év maximális részvételi számát, a NAGY() függvény használatával!
  =NAGY(M2:M21;1)
• Módosítsuk az előbbi képletet, hogy tudjuk másolni egészen A49-ig!
  =NAGY($M$2:$M$21;1)
• Most azt kell kiokoskodni, hogyan érhető el, hogy a NAGY() függvény második argumentuma a rangsor első sorában 1, a másodikban 2 stb. értéket vegyen fel. Nyilván ez az érték attól függ, hányadik sorban állunk, az első sorhoz képest. Ebből kitalálható, hogy a SOR() függvényt kell használnunk, figyelembe véve, hogy a rangsor a 30. sorban kezdődik:
  =NAGY($M$2:$M$21;SOR()-29)
• Jöhet a már jól begyakorolt INDEX() és HOL.VAN() függvénypáros, hogy a vizsgázók száma helyett a megye nevét jelenítsük meg:
  =INDEX(
  $A$2:$A$21;
  HOL.VAN(
  NAGY($M$2:$M$21;SOR()-29);
  $M$2:$M$21;
  0
  )
  )
• Nincs más hátra, mint a helyi tartományhivatkozásokat lecserélni az ECDL.xls fájl megfelelő tartományaira. Ehhez nyissuk meg a fájlt ugyanabban az Excel-programban, amiben dolgozunk, és az előbbi képlet szerkesztésekor - a lecserélendő hivatkozás kijelölését követően - egyszerűen csak kattintsunk át a másik munkafüzetre, és jelöljük ki a megfelelő munkalap szükséges tartományait!

A kép (nagyobb változata) külön ablakban is megtekinthető.38_full.jpg38. ábra: A helyi hivatkozások lecserélése egy másik munkalap celláira mutató hivatkozásokra.

Ismét hangsúlyoznám, hogy ez a módszer csak akkor működik, ha a vizsgázók száma mind különböző. Ha ez nem teljesül, és például Budapesten és Győrben is a 33584-en vizsgáztak, akkor a képlet mindkét esetben Budapestet fogja visszaadni, hiszen az szerepel előbb a táblázatban. Érdemes ezt kipróbálni, és azt is, hogy mi történik, ha megváltoztatunk egy eredményt az ECDL.xls fájl megfelelő tartományában, majd mentést követően megnyitjuk az elkészült NJSZT.xlsx-t.

Sajnos nem kizárt, hogy időnként nem működik helyesen az Excel rendezés funkciója: az adatok átrendezése elronthatja a hivatkozásokat. A probléma egy kis odafigyeléssel megelőzhető, de akkor sem lenne szabad ennek előfordulnia.

Nyissuk meg az elkészített munkafüzetet a megoldas mappából, a Telefonkönyv_rossz munkalapon! A táblázatban egy cégnél dolgozók neve, beosztása és telefonmellékük van megadva, a hozzájuk tartozó országot, illetve az összes hívás adatai pedig függvények adják.

Az ügyintézőkhöz tartozó országot így számoljuk:
=HA(
B2="ügyintéző";
INDEX(
Hívások!$B$2:$B$1119;
HOL.VAN(Telefonkönyv_rossz!C2;Hívások!$A$2:$A$1119;0)
);
""
)

• Rendezzük a táblázatot mellékek szerinti növekvő sorrendbe!

Elsőre talán fel sem tűnik, de jobban megnézve a képleteket, megdöbbenve tapasztalhatjuk, hogy a mellékekre történő hivatkozások összekeveredtek, és így az egész táblázat elromlott. Ha ezt nem vesszük észre időben, rengeteg munkánk veszhet kárba, vagy akár adatokat is veszíthetünk.

A kép (nagyobb változata) külön ablakban is megtekinthető.39_full.jpg39. ábra: Összekeveredett hivatkozások.

Látható, hogy az előbbi képletben a C2 előtt ott van a munkalap neve is. Ez teljesen felesleges, de gondot nem okozhat - gondolná az ember. Arról nem is beszélve, hogy a hivatkozásokban a munkalapok nevét ritkán adjuk meg szándékosan, begépeléssel, hiszen ezt az Excel maga is elvégzi a cellák kijelölésekor. Itt például annyi történt, hogy a Hívások munkalapról visszakattintottam az aktuális munkalapra, és a munkalap megjelölése máris odakerült a cella azonosítója elé. A probléma forrása azonban éppen ez a felesleges munkalap-hivatkozás. Hogy miért, nem tudom. Mindenesetre, ha ezeket kitöröljük, már rendezhetjük a táblázatot, minden gond nélkül.

Nem árt tehát, ha rendezést követően mindig ellenőrizzük, minden rendben van-e. Ha ehhez hasonló hibát tapasztalunk, megoldás lehet a képlet átírása is: a cellákra történő hivatkozás megadható SOR() függvénnyel is, a hagyományos módszer helyett:
=HA(
INDIREKT("B"&SOR())="ügyintéző";
INDEX(
Hívások!$B$2:$B$1119;
HOL.VAN(INDIREKT("C"&SOR());Hívások!$A$2:$A$1119;0)
);
""
)

Ez azonban nem egy univerzális módszer, és több munkát is igényel, mint ha odafigyelnénk a kéretlen munkalap-hivatkozások feltűnésére, csupán érdekességképp mutattam meg.

Vissza a tartalomjegyzékhez