Adatok kezelése

Adatok kezelése

Az Excelt használhatjuk úgy is, 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ásodsorban 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 adatbázis sorait rekordoknak, 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 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, 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 tudjuk megállapítani, 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 (mely a mellékletben található Excel munkafüzet Ismétlődés eltávolításamunkalapján található) ennek segítségével egy sorszámozott alkatrész-leltár után például megkaphatjuk azt  a listát, amely minden alkatrész-sorszá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ű 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, melyekben nem lehet két azonos, csak valamilyen hiba esetén. Ilyenkor a feladat több lépésből áll:

1. Meg kell állapítanunk, melyik adat (számlaszám) ismétlődik.
2. Meg kell keresnünk, hol vannak az azonosak.
3. 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ám oszlopot jelöljük ki, kritériumként pedig az aktuális sorban lévő számlaszámot. Ha nincs ismétlődés, akkor a függvény minden számot csak egyszer fog megtalálni, ha 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 végezhetünk a CRTL+F billentyűkombináció lenyomásával.

A megjelenő párbeszédablakban megadhatjuk a keresendő kifejezést vagy akár a keresendő formátumot, 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. Beállíthatjuk 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, 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 meg kell állapítanunk, hogy a keresett keresztnév melyik sorban van. Erre alkalmas a HOL.VAN (keresési érték; tábla; egyezés) függvény, mellyel megállapíthatjuk, hogy a keresett adat a táblázat melyik oszlopában (Keresztnév), 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 kiválasztjuk a nekünk 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 a tömb változóba értsük bele a fejlécet, különben egy sornyi elcsúszást fogunk tapasztalni. (A témához tartozó mintapéldát lásd a mellékletben található Excel munkafüzet Keresésmunkalapján.)

Sorbarendezé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 ► Szerkesztéscsoport ► Rendezés és szűrés ikonjá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. Van lehetőségünk 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, valamint a rendezés alapját: azt, hogy milyen kritérium szerint történjen a rendezés, továbbá a sorrendet. Ha 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

Több szintű rendezés esetén az Újabb szint gomb megnyomásával juthatunk a párbeszédablakban egy újabb sorhoz, amelyben beállíthatjuk a rendezés további paramétereit. Ebben a rendezési formában fontos a rendezési szintek sorrendje! 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 mutató nyilakkal lehet megváltoztatni. A több szintű 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ésmunkalapjain.)

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 a tagolandó táblázat szerkezetére vonatkozó több szabályt is be kell tartanunk:

1. A tagolandó sorokban lévő adatoknak csoportosítva és sorba rendezve kell állniuk. Ezt elvégezhetjük az előző részben ismertetett Sorbarendezés funkcióval.
2. Automatikus tagolást csak akkor végezhetünk, ha előtte manuálisan már létrehoztunk valamilyen, a tagolás csoportjaira vonatkozó összesítő függvényt (például részösszeg vagy darab).
3. Az összesítő függvényeknek összességükben a csoport minden tagjára vonatkozniuk kell.
4. Az egy oszlopban lévő adatoknak homogénnek kell lenniük (például mindegyik egy számla végösszege forintban).
5. A táblázat nem lehet előre formázott a Formázás táblázatként funkcióval.
6. A tagolandó táblázatban nem lehet üres sor.
7. 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.

Látványos és gyors tagolást készíthetünk a RÉSZÖSSZEGfüggvény segítségével. Az előzetesen rendezett és a követelményeknek megfelelő táblázatban kijelölünk egy cellát, majd az Adatok fül ► 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.

A megjelenő párbeszédablakban beállíthatjuk 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ások eltávolítását az Adatok fül ► Tagolás csoport ► Csoportbontás gomb legördítésével megjelenő Tagolás eltávolítása menüponttal hajthatjuk végre. (A témához tartozó mintapéldákat lásd a mellékletben található Excel munkafüzet Részösszegés Tagolásmunkalapjain.)

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 fel van sorolva, hogy táblázatunknak milyen sorai legyenek, akkor ez a sort a dokumentumból kimásolva és a Kezdőlap fül ► Vágólapcsoport  ► Beillesztésikon ► 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ásmunkalapján.)

Külső adatok exportálása és importálása CSV formátumban

Feldolgozásra váró táblázataink némely esetben nem a megszokott Excel formátumúak, hanem egy más program által előállított (exportált) formátumban vannak. Ezek a formátumok is tartalmazzák az adatokat és a táblázat struktúráját csak más „nyelven”. Ahhoz, hogy ezekkel az adatokkal Excelben tudjunk dolgozni, a táblázat konvertálására van szükségünk. Ha van rá lehetőség, akkor már az adatok exportálásakor úgy állítsuk be a file-formátumot, hogy az az Excel számára értelmezhető legyen. Ilyen általánosan használt formátum a CSV formátum.

A CSV (Comma Separated Value) fájlformátum egy ASCII szabványnak megfelelő, speciális szövegformátum, amelyben a rekordok egyes mezőit egy elválasztó karakter – rendszerint vessző vagy pontosvessző – választja el. Minden rekord végére „soremelés-új sor” karakter kerül. Ez a szövegformátum a legtöbb szövegszerkesztővel és más programokkal (például adatbáziskezelőkkel) elkészíthető, illetve kezelhető. E tulajdonsága miatt alkalmas arra, hogy az egymással egyéb módon nem kommunikáló programok között mégis biztosítsa az adatok átvitelét.

Egy táblázatot többféle CSV formátumban is elmenthetünk. Az első szövegdobozban lévő adatok a Mentés másként ► Egyéb formátumok ► File formátuma: CSV (pontosvesszővel tagolt) módon lett elmentve, a második táblázatban lévő adatok pedig CSV (MS-DOS)formátumban. Látható, hogy az adatok szerkezete és a tagoló szimbólumok azonosak, de az MS-DOS verzióban elvesztek az ékezetes karakterek. További megkötést jelent, hogy elvesznek a formázások, és egyszerre csak egyetlen munkalap menthető, nem az egész munkafüzet.

A mentés eredményét legkönnyebben úgy ellenőrizhetjük, ha a mentett állományt megnyitjuk a Notepad programmal, amely a szövegdobozokban látható módon jeleníti meg a táblázatunkat. Ha az adatokat fogadó program kizárólag vesszővel elválasztott adatokat tud fogadni, akkor a Notepad program segítségével cseréljük ki a pontosvesszőt vesszőre. Ennek azonban az a feltétele, hogy az adatállományban a pontosvessző kizárólag elválasztó funkcióval bírjon, és az adatokban más helyen ne szerepeljen.

CSV formátumú adatokat importálhatunk is az Excelbe. Ha a formátum „rendben van”, akkor az állományt közvetlenül megnyithatjuk, és rögtön táblázatos formában, formázások nélküli állapotban fogjuk látni adatainkat.

Ha formátum nem egészen szabványos, és az Excel nem tudja megnyitni vagy hibásan olvassa be a táblázatot, előfordulhat például, hogy  minden rekord összes adatmezője egyetlen cellába kerül. Ebben az esetben válasszuk ki az Adatok fül ► Külső adatok átvételecsoport ► Szövegből menüpontját. Az első megnyíló párbeszédablakban kiválaszthatjuk a megnyitandó file-t, az ezt követő ablakban pedig beállíthatjuk a file tagoltságát és kódolását.

A program igyekszik automatikusan felismerni a tagoló szimbólumokat, de ha ez nem sikeres, akkor a következő ablakban pontosan beállíthatjuk a határoló karaktert. Az alsó ablakban ellenőrizhetjük beállításaink eredményét. Az első lépést ábrázoló képen látszik, hogy az adatok olyan módon vannak megjelenítve, mint ahogyan azokat a Notepad is meg tudja jeleníteni. A második lépésben már megtettük a szükséges beállításokat, aminek eredményeképpen a Megtekintés ablakban az adatmezők már a kívánságunknak megfelelő tagolásban láthatóak.

Sajnos a dolgunk nem mindig ilyen egyszerű és problémamentes. Ha idegen programból származó táblázatot szeretnénk megnyitni, szükségünk lehet az előzőek kombinációjára, sőt még az eszközök kombinálására is. Ha az adatok a helyükön megjelennek ugyan, de a cellán belüli formátumuk nem megfelelő, akkor a kívánt eredmény eléréséhez szükség lehet cellaformázásra, függvények alkalmazására vagy karaktercserékre.