adatbátzs-táblázat, számítások
2018-12-04    Excel 2016
fogalmak
Ahogy azt az adatbázis-táblázat, kezelés című cikkből megtudhattuk, az adatbázis-táblázat egy névvel azonosított, változó méretű a­dat­tar­to­mány, amelynek oszlopait is névvel azonosítjuk. Az oszlop-neveket a tartomány első sora tartalmazza. Az adatbázis-táblázat dek­la­rá­ci­ó­val jön létre. A táblázat sorai a rekordok, oszlopai a mezők, adatai a bejegyzések. Ebben az ismertetőben az adatbázis-táblázat bel­ső- és külső számított objektumairól mesélek.
    A digitális adatfeldolgozás alapkövetelménye az adatok egyértelmű azonosítása. Ez az igény az adatbázis-táblázatban a rekordok szint­jén fogalmazódik meg. Tehát minden rekordnak kell egy azonosító, vagy másként fogalmazva, minden táblában kell egy azonosító mező! Ebben a mezőben minden rekordban bejegyzésnek kell állnia!
    Persze előfordulhat, hogy nem egy, hanem több mező bejegyzése azonosítja a rekordot. Például a vállalat havi termelési adatait tar­tal­ma­zó táblázat „év” és „hónap” mezője. Ebben az esetben mindkét mező minden rekordjában bejegyzés kell, hogy álljon!
    Ha nekünk kell az azonosítókat létrehozni, akkor képezzünk sorszámot! Ez a legegyszerűbb és a legpraktikusabb megoldás. A kü­lön­bö­ző „hosszúságú” sorszámokat egyéni számformátummal azonos méretűvé alakíthatjuk át. Például, ha az azonosító legfeljebb négy szám­je­gyű, akkor a számformátum ”0000” lesz. Így a tizenhárom 0013-ként, a száztizenhárom 0113-ként fog megjelenni.
azonos rekordok megjelölése és törlése
A napi munka során egy rekord többször is az adatbázis-táblázatba kerülhet. Ezeket a felesleges rekordokat a számítások megkezdése előtt törölnünk kell. Vegyük példának az ötmezős, kollégák nevű adatbázis-táblázatot. Mezői a következők. Név: a munkatárs neve. Szem.Szám: a munkatárs személyi azonosító száma. Január, február, március: a munkatárs adott havi jövedelme. Töröljük a táblázat is­métlődő rekordjait.
    A műveletet a Táblázateszközök, Tervezés, Eszközök, Ismétlődések eltávolítása utasítással indíthatjuk. A megjelenő parancstáblán meg kell határoznunk, mely mezökben vizsgálja a program a bejegyzéseket. A szokásos beállítás, az összes mezőben, jóváhagyása után…
adatbázis táblázat, azonos rekordok törlése
…ezt az üzenetet kapjuk. De, Tanító Bácsi! Én látni szeretném a törlésre kerülő rekordokat! Én is Pistike! Még szerencse, hogy a Vissza­vo­nás művelettel helyreállíthatjuk az eredeti állapotot! Jelöljük meg a törlésre kerülő rekordokat feltételes formázással!
    Ha feltételes formázás, akkor először is, törölnünk kell a táblázat automatikus formázását, majd ezt követően rendezni kell a táb­lá­za­tot, mind az öt mező szerint.
adatbázis táblázat, törlésre kerülő, azonos rekordok megjelölése feltételes formázással
Ha ez megtörtént jelöljük ki a táblázat adat-területét és hozzunk létre egy képleten alapuló feltételes formázást! Adjuk ki a Kezdőlap, Stí­lu­sok, Feltételes formázás, Új szabály utasítást, majd a megjelenő parancstáblán válasszuk A formázandó cellák kijelölése (Sic!) képlettel fel­i­ra­tú vezérlőt!
    A képlet a következő utasítást fogja tartalmazni: akkor kell az adott cellát formáznod, ha a cellát tartalmazó rekord minden be­jegy­zé­se a­zo­nos a felette álló rekord, azonos mezőjének bejegyzésével. Ez öt feltétel egyidejű teljesülését jelenti. Magyarán szólva: az ÉS függ­vényt kell használnunk. Ha az adatbázis táblázatunk bal felső cellája A1, akkor a képletünk a következő: =ÉS( $A2=$A1 ; $B2=$B1 ; $C2=$C1 ; $D2=$D1 ; $E2=$E1 ). Végül, kattintsunk a Formátum… nyomógombon és állítsunk be egy szép, pasztell színű kitöltést!
adatbázis táblázat, törlésre kerülő, azonos rekordok megjelölése feltételes formázással
összegsor
Az ismétlődő rekordok eltávolítása után, hozzákezdhetünk a táblázat adatainak feldolgozásához! Először az adatbázis-táblázat sta­tisz­ti­ka­i moduljával ismerkedjünk meg, amelyet a Táblázateszközök, Tervezés, Összegsor feliratú je­lö­lő­négy­zet­tel jeleníthetünk meg. Ahogy azt a neve is mutatja, a szolgáltatás valóban egy sor, mégpedig a táblázat utolsó sora, a beállításoktól füg­gő for­má­zás­sal, első cel­lá­já­ban az Összeg felirattal és utolsó cellájában a mező bejegyzéseinek összegével vagy a bejegyzések szá­má­val. Ha az utolsó mezőben számok áll­nak, akkor összeg, különben darabszám.
adatbázis táblázat, összegsor
A kép hátterében egy összegsor nélküli adatbázis-táblázat áll, előterében pedig ugyanez a táblázat megjelenített összegsorral látható. Ha rá­kat­tin­tunk az összegsor tetszőleges cellájára, beleértve az Összeg felirat celláját is, azonnal megértjük a szolgáltatást. Statisztikai vizs­gá­la­tot indíthatunk a mezőben a megjelenített lista segítségével: kérhetjük a számok megszámlálását (Darabszám), szélső értékeik (Max, Min), át­la­guk (Átlag), összegük (Összeg), szórásuk (Szórás) és varianciájuk (Var) megállapítását valamint a mező be­jegy­zé­se­i­nek meg­szám­lá­lá­sát (Darab). A lista Nincs elemével a statisztikai vizsgálat eredményét törölhetjük a cellából.
    Az összegsorban megjelenített statisztikai érték egy képlet eredménye, amelyet a program helyezett el a cellában. De érdekes mó­don nem a szokásos statisztikai függvényeket, hanem következetesen a RÉSZÖSSZEG függvényt használja! De miért? Mert az összeg­sor szolgáltatás érzékeny a szűrésre: mindig csak a leválogatott rekordok bejegyzéseit vizsgálja. A szokásos statisztikai függ­vé­nyek (DA­RAB, MAX, MIN, ÁTLAG, SZUM…) erre a feladatra nem alkalmasak.
    Az összegsor elemei módosíthatók: felirata (Összeg) átírható vagy törölhető, képletei tetszőleges felhasználói képletre cserélhetők. Meg­je­le­ní­tett összegsor mellett, új rekordot az utolsó rekord utolsó mezőjében a TAB billentyűvel hozhatunk létre.
részösszeg függvény
A RÉSZÖSSZEG függvény az első argumentumával meghatározott statisztikai vizsgálatot hajtja végre, a második argumentumával meg­ha­tá­ro­zott mező bejegyzésein. A rejtett sorokban álló és a szűréssel kizárt rekordok adatait a függvény nem veszi figyelembe.
    A statisztikai vizsgálatok azonosítói számok. (101) Átlagszámítás. (102) Számok megszámlálása. (103) Bejegyzések meg­szám­lá­lá­sa. (104) Legnagyobb szám meghatározása. (105) Legkisebb szám meghatározása. (106) Számok összeszorzása. (107 és 108) Szá­mok szó­rá­sá­nak kiszámítása. (109) Számok összeadása. (110 és 111) Számok varianciájának kiszámítása. Tehát a függvény első ar­gu­men­tu­ma a statisztikai elemzés azonosítója, vagy más szóval, a száma. Ha a rejtett sorok adatait is be kívánjunk vonni a statisztikai vizsgálatba, akkor százzal kisebb azonosítót kell megadnunk (1, 2, 3… 10, 11).
    Természetesen a kívánt műveletet a névkiegészítővel is bevihetjük a képletbe. Az első argumentum módosításakor a művelet-lista is­mé­telt megjelenítését az Alt + Le nyíl billentyűparanccsal kérhetjük.
adatbázis táblázat, RÉSZÖSSZEG függvény
a táblázat elemeinek azonosítása a képletben
Mivel az adatbázis-táblázat egy változó területű adattartomány, ezért célszerű objektumaira, a szokásos oszlop-sor azonosítás helyett, név­vel hivatkoznunk a képletben. Ezzel a megoldással elkerülhetjük az objektumok pozíció-váltását követő kényszerű képlet-ja­ví­tá­so­kat. A prog­ram fejlesztői önálló név-rendszert hoztak létre erre a célra, amit most megpróbálok érthetően bemutatni.
    Vegyünk egy egyszerű példát. Van egy hatrekordos adatbázis-táblázatunk, amely egy vállalat bevételeit tartalmazza. Mezői az év­szá­mok és a negyedévek , rekordjai az évek. A táblázat neve: bevételek.
hivatkozás képletben az adatbázis táblázat egyes elemeire
A teljes adatbázis-táblázat azonosítója a képletben: táblázatnév[#Mind]. Az adatbázis-táblázat három funkcionálisan homogén területe: a mezőneveket tartalmazó sor, a rekordokat tartalmazó terület és az összegsor. Azonosítóik sorrendben: táblanév[#Fejlécek], táblanév vagy táblanév[#Adatok] és táblanév[#Összegek].
hivatkozás képletben az adatbázis táblázat egyes elemeire
A mező azonosítója az adatbázis-táblázat képletében: [[#Mind];[mezőnév]]. Külső képletben a táblázatnevet is meg kell adnunk: táb­lá­zat­név[[#Mind];[mezőnév]]. Ahogy az adatbázis-táblázat, úgy a mező is három egységre bontható: mezőnév, bejegyzések és sta­tisz­ti­ka­i ér­ték. Azonosítóik sorrendben: [[#Fejlécek];[mezőnév]], [mezőnév] vagy [[#Adatok];[mezőnév]] és [[#Összegek];[mezőnév]]. Külső képletben a táblázat nevével kiegészítve: táblázatnév[[#Fejlécek];[mezőnév]]…
Az adatbázis-táblázat belső képleteiben tehát hivatkozhatunk a mezőre a táblanév nélkül is. Itt az ábrán én a külső képletben álló a­la­ko­kat tüntettem fel. A névkiegészítő tartalmazza a hivatkozások minden elemét, a tábla és mezőneveket, valamint a kettőskeresztes te­rü­let-azo­no­sí­tó­kat is. Persze használhatjuk a kattintásos névbevitelt is a képlet szerkesztése közben. Erre a lehetőségre próbáltam a fi­gyel­met fel­hív­ni a kis fekete kijelölő-nyilacskákkal.
    A kettőspont és a pontosvessző ebben a hivatkozás-rendszerben is a megszokott funkciókat látják el. Például az „[I. negyedév]:[III. ne­gyed­év]” karakterlánc az első, a második és a harmadik negyedév bejegyzéseit, a „[I. negyedév];[III. negyedév]” karakterlánc az első és a harmadik negyedév bejegyzéseit azonosítja a képletben.
számított mező
A számított mező az adatbázis-táblázat rekordjait feldolgozó, felhasználói képleten alapuló objektum. A képletet a program a­u­to­ma­ti­ku­san elhelyezi és kiértékeli a táblázat minden rekordjában. A számított mező létrehozása tehát egyenlő a képlet megszerkesztésével. Kat­tint­sunk egy üres mező tetszőleges cellájára vagy az első rekord utolsó mezőjét követő cellára és lássunk hozzá a képlet össze­ál­lí­tá­sá­hoz.
adatbázis táblázat, számított mező
Mivel a számított mező alapvetően a rekordok feldolgozására szolgál, ezért a képletben, a képletet tartalmazó rekord a főszereplő. Je­lö­lé­se a kukac karakter (@). A képen felül látható „bevételek” adatbázis-táblázat „első félév” számított mezőjének képlete: =[@[I. ne­gyed­év]] + [@[II. negyedév]]. Az alatta álló „adatok” táblázat új számított mezője, az egy főre eső bevételt számolja ki tízezer forint pon­to­ság­gal: =KE­RE­KÍ­TÉS( [@bevétel] / [@létszám] ; -4 ).
    De Tanító bácsi! Eltérőek a jelölések! Igazad van Pistike! Előbb a mezőnévben álló szóköz miatt használtam a [@[mezőnév]] for­mu­lát, de ha nincs szóköz elmaradhat a nevet körbezáró szögletes zárójel.
    A számított mező képletének automatikus másolása, arra a téves következtetésre vezethet, hogy a számformátumot is elég csak egy be­jegy­zés­re megadni… Nem, a mezőt a művelet előtt ki kell jelölni.
 
abszolút hivatkozás az adatbázis-táblázatban
Térjünk vissza az összegsor celláinak műveletlistájára. Valami hiányzik! Vegyük számba a program megszámláló függvényeit: DARAB - számok megszámlálása, DARAB2 - nem üres cellák megszámlálása… Na Pistike! Mi következik? Hát az üres cellák megszámlálása! A DARABÜRES függvény, tanító bácsi! Bizony-bizony. És ez hiányzik a listából. De megoldjuk.
    Vegyük a kötések nevű adatbázis-táblázatot, amely az üzletkötök üzlet-kötéseit tartalmazza, havi bontásban. A táblázat első mezője (név) tartalmazza az üzletkötő nevét, majd őt követi a havi adatokat tartalmazó tizenkét mező (jan, febr, márc…).
abszolút hivatkozás adatbázis-táblázatban
Készítsünk statisztikát az összegsor celláiban: az adott hónapban hány üzletkötő nem hozott semmit a konyhára! Ez nem nehéz fel­a­dat: megszámláltatjuk az azonosító mező (név) bejegyzéseinek számát és ebből kivonjuk az adott hónap számainak darabszámát. Ter­mé­sze­te­sen mi is a RÉSZÖSSZEG függvényt fogjuk használni, hogy a statisztikai mindig csak a szűrt rekordokra vonatkozzon.
    Az összegsor jan mezőjében tehát a képletünk így alakul: =RÉSZÖSSZEG( 3 ; [név] ) - RÉSZÖSSZEG( 2 ; [jan] ). Az eredmény hat. El­len­ő­riz­zük! Szuper! Másoljuk a képletet jobbra! Hoppá! Valami nem stimmel: májusban és novemberben a látható üres cellák ellenére nullát kap­tunk, sőt vannak hónapok, amelyekben az üres cellák darabszáma negatív szám! Ellenőrizzük a februári képletet! Nem erre szá­mí­to­ttunk: =RÉSZÖSSZEG( 3 ; [jan] ) - RÉSZÖSSZEG( 2 ; [febr] ).
    Az adatbázis-táblázat név-hivatkozásai tehát relatív hivatkozások! Ez meglepetés, hszen a felhasználói nevek abszolút módon vi­sel­ked­nek, sőt ezt a tulajdonságukat az adatbázis-táblázatban is megtartják. Ebből következően, nevet kell adnunk annak a bejegyzésnek illetve mezőnek, amelyre a képletben abszolút módon akarunk hivatkozni. Jelöljük ki a „név” mező bejegyzéseit és nevezzük el a Kép­le­tek, De­fi­ni­ált nevek, Név megadása paranccsal „abs.név”-re! A januári módosított képlet tehát így alakul: =RÉSZÖSSZEG( 3 ; abs.név ) - RÉSZ­ÖSSZEG( 2 ; [jan] ). És ezt a képletet már nyugodt szívvel másolhatjuk.
külső képletek
Az adatbázis-táblázat adatait nem csak számított mezökkel és az összegsor statisztikai eszközeivel, de tetszőleges külső képlettel is fel­dol­goz­hat­juk. A program ezekben a képletekben is neveket használ a táblázat objektumainak kijelölésekor, de a nevek bevitelére a név­ki­e­gé­szí­tőt is használhatjuk: írjunk egy nyitó szögletes zárójelet a táblázat neve után és…
adatbázis táblázat, külső hivatkozások
Ha a helyzet úgy kívánja, a kattintásos képlet-szerkesztéssel bevitt neveket felülírással cserélhetjük le a szokásos oszlop-sor a­zo­no­sí­tók­ra. Sőt a hivatkozás-rendszert általánosan is letilthatjuk a Fájl, Beállítások, Képletek, Táblanevek használata képletekben utasítással.
    A program egy külön függvény-családot is kínál az adatbázis-táblázat bejegyzéseinek külső feldolgozására, de ezt majd önállóan, az adat­bá­zis-kezelő függvények címmel mesélem el. Kedves Gyerekek! Búcsúzik tőletek Josef K.!
  ismertető letöltése pdf-ben   munkafüzet letöltése
margitfalvi.arpad@gmail.com