Excel-kisokos - Adatok kezelése
Sorozatunk legújabb részében megnézzük, hogy miként tudjuk hatékonyabbá tenni az adatok kezelését.
Kategóriák:
Szerző: Tatai István
2011. július 21.
Használhatjuk úgy is az Excelt, mint egy egyszerű adatbázis-kezelő programot, de ne felejtsük el, hogy valójában egy táblázatkezelő programmal dolgozunk, amely tartalmaz néhány adatkezelési funkciót is. Egy adatbázis létrehozásának célja elsősorban a rögzítés és a visszakereshetőség biztosítása, másrészt az adatkezelés (beszúrás, csoportosítás, törlés, módosítás) lehetősége. Cél lehet még emellett, hogy az adatbázisból további információkat nyerjünk. Az ismétlés kedvéért elevenítsünk fel néhány alapvető fogalmat: az adatbázis sorait rekordoknak, a celláit pedig mezőknek nevezzük. A mezők neve a táblázat fejlécében található. A rekordoknak egységes szerkezetűnek kell lenniük, vagyis az azonos típusú adatoknak a táblázatban egymás alatt kell elhelyezkedniük. Mielőtt nekifogunk a munkának, fontos, hogy átgondoljuk a feladatunkat, és ennek megfelelően készítsük el az adatbázis tervét. A tervezés során meg kell határoznunk az egyes rekordok (sorok) tartalmát és a mezők tartalmát is. Gyakori hiba például, hogy egy dolgozói adatbázisban a dolgozók teljes címét egyetlen mezőben tárolják. Ezzel azonban a cím szerinti szűrési és csoportosítási lehetőségek elvesznek. Törekedjünk a mezők tartalmának egységesítésére! Az adatokat lehetőleg kis egységekben, külön mezőkben tároljuk, így több lehetőségünk nyílik a keresésre és csoportosításra. Ha szükséges, akkor a mezőket könnyen egyesíthetjük, míg a mezők tartalmának szétbontása sokkal nehezebb. Ismerkedjünk meg most néhány, az Excel által biztosított lehetőséggel, amely az adatok kezelését teszi lehetővé!
Ismétlődések kiszűrése
Gyakori kérdés és probléma, hogy egy sok rekordot tartalmazó adatbázisban hogyan állapíthatjuk meg, hogy minden adat csak egyszer szerepel-e, vagy sem. Ha csak az ismétlődő elemektől akarunk megszabadulni, és mindenből egy darabot megtartani, akkor lehetőségünk van az Ismétlődések eltávolítása funkciót használni, amit az Adatok fülön találhatunk. A témához tartozó mintapéldánkban (amely a mellékletben található Excel munkafüzet „Ismétlődés eltávolítása" munkalapján található) ennek segítségével egy sorszámozott alkatrészleltár után például megkaphatjuk azt a listát, amely minden alkatrészsorszámot tartalmaz, de csak egyszer. Ha ezeket a számokat sorba rakjuk, akkor könnyen megállapíthatjuk, hogy egy új alkatrész érkezésekor annak milyen következő sorszámot kell adnunk.
Nem ilyen egyszerű azonban a helyzetünk, ha minden egyes sorra szükségünk van, de a sorok elején az első mezőben lévő adatot kell ellenőriznünk egyediség szempontjából. Példaként említhetjük egy vállalat szigorúan egyedi számlaszámait, amelyekben nem lehet két azonos szám, csak hiba esetén. Ilyenkor a feladat több lépésből áll: elsőként meg kell állapítanunk, hogy melyik adat (számlaszám) ismétlődik. Ezt követően ki kell derítenünk, hogy hol találhatók az azonosak, végül ki kell választanunk, melyik a hibás adat (számlaszám).
A szám ismétlődését a DARABTELI függvény segítségével állapíthatjuk meg. A függvény argumentumainál tartományként a teljes számlaszámoszlopot jelöljük ki, kritériumként pedig az aktuális sorban lévő számlaszámot adjuk meg. Ha nincs ismétlődés, akkor a függvény minden számot csak egyszer fog megtalálni, amennyiben viszont hiba van valahol, akkor a függvény ennek megfelelően kétszer vagy akár többször fogja a számot megtalálni az adott oszlopban. A hiba helyét úgy állapíthatjuk meg, hogy a rekordokat (sorokat) a számlaszámok szerint sorba rendezzük, így az azonos számok egymás alá kerülnek, és a függvénnyel előállított jelzőszám is segíti a hibás sor felderítését. Már csak azt kell eldöntenünk, melyik számlaszámot javítsuk.
Keresés
Ha egy bizonyos rekordot szeretnénk megtalálni az adatbázisban, ahhoz valamely egyedi mezőjének tartalmára van szükségünk. Ha erre az adatra végezzük a keresést, könnyen megkaphatjuk a keresett teljes rekordot. Amennyiben nincs ilyen egyedi adat, akkor csak a rekordok olyan csoportját tudjuk megtalálni, amelyre igaz a keresési feltételünk. Egyszerű manuális keresést a CRTL+Fbillentyűkombináció lenyomásával végezhetünk.
Keresés
A megjelenő párbeszédablakban megadhatjuk a keresendő kifejezést vagy akár a keresendő formátumot is, ha egy adatot például előzőleg pirossal kiemeltünk. Az Egyebek nyomógomb lenyomásával további beállítási lehetőségekhez juthatunk, ahol megadhatjuk a keresés hatókörét, sorrendjét, helyét és érzékenységét a tartalomra és a betűnagyságra vonatkozóan. Az eredményt kérhetjük egy listába vagy a Következő nyomógombbal lépegethetünk sorban végig az egyes találatokon.
Gyakori feladat ezenkívül, hogy egy rekord egyik elemének ismeretében kell megtalálnunk például a rekord sorszámát vagy egy másik elemét. Keressük meg például a „Keresztnév" adat alapján a „Beosztást". Ezt a feladatot két lépcsőben oldhatjuk meg: első lépésként állapítsuk meg, hogy a keresett keresztnév melyik sorban van. Erre alkalmas a HOL.VAN (keresési érték; tábla; egyezés) függvény, amellyel megállapíthatjuk, hogy a keresett adat a táblázat melyik oszlopában (Keresztnév) és hányadik sorban található. A kapott sorszám felhasználható a második lépéshez,
az INDEX(tömb;sorszám;oszlopszám) függvényhez, amellyel a már ismert sorból választhatjuk ki a szükséges adatot (Beosztás). Ha az első függvénynél a táblába beleértettük a táblázat fejlécét is, akkor konzekvensen a második függvénynél is értsük bele a tömb változóba a fejlécet, különben egysornyi elcsúszást fogunk tapasztalni. (A témához tartozó mintapélda Excel-munkafüzet Keresés munkalapján - letölthető innen)
Sorba rendezés
A táblázatban a rekordokat több szempont szerint is sorba rendezhetjük: ha kijelöljük a táblázatot, és lenyitjuk a Kezdőlap fülön belül a Szerkesztés csoportban megtalálható Rendezés és szűrésikonját, akkor a növekvő és csökkenő rendezés a táblázat első oszlopában található értékek szerint történik. Lehetőségünk adódik más oszlop szerinti sorba rendezésre is, ekkor válasszuk ki az ugyanitt található Egyéni sorrend menüpontot. Ebben az ablakban beállíthatjuk a rendezés szempontját (azt, hogy melyik oszlop szerint történjen a rendezés), a rendezés alapját (azt, hogy milyen kritérium szerint történjen a rendezés), továbbá a sorrendet. Amennyiben a sorokat például úgy szeretnénk sorba rendezni, hogy külön legyenek a férfiak és a nők, akkor az alábbi beállításokat kell végeznünk: „Rendezés: Nem"; „A rendezés alapja: Érték"; „Sorrend: A-Z".
Rendezés
Többszintű rendezés esetén az Újabb szint gomb megnyomásával juthatunk a párbeszédablakban egy újabb sorhoz, amelyben megadhatjuk a rendezés további paramétereit. Ebben a rendezési formában fontos a rendezési szintek sorrendje, ugyanis más táblázatot kapunk, ha a rendezés első szempontja a dolgozó neme és második az életkora, és mást, ha első az életkora és a második a neme. A rendezési szintek sorrendét a fel és le nyilakkal lehet megváltoztatni. A többszintű rendezést tekinthetjük egyfajta csoportosításnak is. (A témához tartozó mintapéldákat lásd a mellékletben található Excel-munkafüzet Sorbarendezés és Több szintű sorbarendezés munkalapjain.)
Tagolás
A tagolás segítségével hosszú táblázatokat tehetünk átláthatóvá és a részletek elrejtésével „összecsukhatóvá". A tagolás elvégzéséhez több, a tagolandó táblázat szerkezetére vonatkozó szabályt is be kell tartanunk. Elsőként a tagolandó sorokban lévő adatoknak csoportosítva és sorba rendezve kell állniuk; ezt az előző részben ismertetett Sorbarendezés funkcióval végezhetjük el. Másrészt automatikus tagolást csak akkor végezhetünk, ha előtte már létrehoztunk valamilyen, a tagolás csoportjaira vonatkozó összesítő függvényt (például részösszeg vagy darab). Fontos, hogy az összesítő függvényeknek összességükben a csoport minden tagjára vonatkozniuk kell, valamint az egy oszlopban lévő adatoknak homogénnek kell lenniük (például mindegyik egy számla végösszege forintban). Emellett a táblázat nem lehet előre formázott a Formázás táblázatként funkcióval, a tagolandó táblázatban nem lehet üres sor, végezetül minden oszlop legfelső cellájában szerepelnie kell egy egyedi oszlopcímnek. A tagolás kibontható, illetve összecsukható a tagolásban szereplő „+" és „-" jelekkel, illetve az oszlopazonosítókkal egy sorban található, a tagolás szintjeit jelképező nyomógombok segítségével.
Tagolás
Látványos és gyors tagolást készíthetünk a Részösszeg függvény segítségével. Az előzetesen rendezett és a követelményeknek megfelelő táblázatban jelöljünk ki egy cellát, majd az Adatok fülön belül a Tagolás csoport Részösszeg nyomógombjával egyetlen lépésben elkészíthetjük a tagolást, valamint a csoportok részösszegeinek beillesztését.
Részösszeg
A megjelenő párbeszédablakban állíthatjuk be a csoportosítás alapját, az összegző (vagy például számláló) függvényt, az összegzendő oszlopokat és az összeg elhelyezkedését. A tagolásokat az Adatok fülön belül a Tagolás menüponton belül a Csoportbontás gomb legördítésével megjelenő Tagolás eltávolítása utasítással távolíthatjuk el. (A témához tartozó mintapéldákat lásd a mellékletben található Excel-munkafüzet Részösszeg és Tagolás munkalapjain.)
Transzponálás
Transzponálással egy táblázat oszlopait és sorait cserélhetjük fel egy lépésben, ha például egy dokumentumban felsorolták, hogy a táblázatunknak milyen sorai legyenek, akkor ezt a sort a dokumentumból kimásolva és a Kezdőlap fülön belül a Vágólap csoport Beillesztés ikon Irányított beillesztés menüpontjának Transzponálás jelölőnégyzetét bejelölve rögtön egy oszlopot kapunk eredményül. (A témához tartozó mintapéldát lásd a mellékletben található Excel munkafüzet Transzponálás munkalapján.)
