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ű adattartomány, amelynek oszlopait is névvel azonosítjuk. Az oszlop-neveket a tartomány első sora tartalmazza. Az adatbázis-táblázat deklará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 belső- é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 szintjé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 tartalmazó 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önbö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ámjegyű, 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 ismé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 Visszavoná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áblázatot, 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ílusok, 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 feliratú 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 bejegyzése azonos 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üggvé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 statisztikai moduljával ismerkedjünk meg, amelyet a Táblázateszközök, Tervezés, Összegsor feliratú jelölőnégyzettel 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üggő formázással, első cellá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 állnak, 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ákattintunk 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 vizsgálatot 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), átlaguk (Átlag), összegük (Összeg), szórásuk (Szórás) és varianciájuk (Var) megállapítását valamint a mező bejegyzéseinek megszámlá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 összegsor szolgáltatás érzékeny a szűrésre: mindig csak a leválogatott rekordok bejegyzéseit vizsgálja. A szokásos statisztikai függvények (DARAB, 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. Megjelení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 meghatározott 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 megszámlá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ő argumentuma 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 ismé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évvel 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-javításokat. A program 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 évszá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áblázatné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 statisztikai érté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ó alakokat 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 terület-azonosí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 figyelmet felhívni 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. negyedé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 automatikusan 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. Kattintsunk 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állí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ő. Jelö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. negyedé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 pontosággal: =KEREKÍ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]] formulá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 bejegyzésre 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 feladat: 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. Természetesen 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. Ellenőrizzü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ítottunk: =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 viselkednek, 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épletek, Definiá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 feldolgozhatjuk. 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évkiegé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 azonosítókra. 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 adatbázis-kezelő függvények címmel mesélem el. Kedves Gyerekek! Búcsúzik tőletek Josef K.!
margitfalvi.arpad@gmail.com