Telefonszámunk: 1-472-0679

Struktúrált hivatkozás adattáblák használata esetén

2013-08-21 - horvimi - Megtekintések száma: 2,201 - Kategória: Táblázatok, adattáblák
Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013

A cikk megértéséhez tisztában kell lenni a TÁBLÁZAT fogalmával az Excel-ben. Erről részletesen egy korábbi cikkben lehet olvasni: Táblázatok az Excelben

A táblázatok használatának rengeteg előnye van. Ezek közül az egyik, hogy ha új sorokat vagy oszlopokat fűzünk hozzá, automatikusan kiterjeszti a táblázatot. Emiatt Dinamikus névtartományként is kiválóan használható.

Tartományi hivatkozás vs. Struktúrált hivatkozás

A legegyszerűbben ezt egy példán lehet bemutatni. Az alábbi tartomány táblázattá lett alakítva. A táblázat neve: ‘Forgalom

minta-adatok

Hogyan lehetne meghivatkozni az ID oszlop összes adatát?

Tartományi hivatkozással: =$A$2:$A$10

Struktúrált hivatkozással: =Forgalom[ID]

Ugye mennyivel olvashatóbb? Ráadásul nem kell foglalkozni azzal, hogy hány sora van a táblázatnak, mert a fenti hivatkozás mindig a teljes oszlopra fog vonatkozni.

Tehát a struktúrált hivatkozás azt jelenti, hogy a táblázat részeire nevekkel és nem címekkel hivatkozunk.

Minősített vs. nem minősített hivatkozás

Ez csupán annyit jelent, hogy ha a hivatkozás minősített, akkor tartalmazza a táblázat nevét is. Ekkor a képlet nem feltétlenül abban a táblázatban van, amire hivatkozunk, vagy egy táblázatból hivatkozunk egy másik táblázat valamely részére. nyilván ilyenkor meg kell mondani azt, hogy melyik táblázatról van szó.

Pl.: A fenti minta szerinti munkalap H1-es cellájába (ami ugye kívül esik a táblázaton) ki szeretném számolni, hogy hány terméknév van a D oszlopban:

=COUNTA(Forgalom[Termék])

Ha a hivatkozás az aktuális táblázatban van, pl. egy számított oszlop, ami másik, ugyanabban a táblázatban lévő oszlopok adatait használja, akkor a hivatkozáshoz elég az oszlop neve. Ez lesz a nem minősített hivatkozás.

Pl.: A ‘mennyiség’ és az ‘egységár’ oszlopokból szeretnénk számítani az ‘Érték’ oszlopot, közvetlenül a ‘G’ oszlopba:

=[mennyiseg]*[egysegar]

Ezt a G oszlop bármely cellájába beírva, az egész oszlopot kitölti a megfelelő eredménnyel.

Struktúrált hivatkozások bevitele (Bővebben a videóban)

  • Egérrel kattintgatva a megfelelő helyeken, vagy
  • Billentyűzetről gépelve, amihez azért jelentős támogatást kapunk. Vannak olyan hivatkozások, amelyeket csak billentyűzettel lehet megfelelően bevinni.

Speciális hivatkozások

A következő példákban a táblázat neve: ‘Forgalom

Hivatkozott rész Angol Excel Magyar Excel
A teljes táblázat az oszlopfejlécekkel, adatokkal és
összesítésekkel (ha van) együtt.
Forgalom[#All] Forgalom[#Mind]
Csak az adatok fejléc nélkül Forgalom[#Data] Forgalom[#Adatok]
Csak a táblázat fejlécsora Forgalom[Header] Forgalom[#Fejlécek]
Csak az ‘Összeg’ oszlop fejléccel együtt Forgalom[[#All],[Összeg]] Forgalom[[#Mind],[Összeg]]
Több, összefüggő oszlop =Forgalom[[Termék]:[mennyiseg]] =Forgalom[[Termék]:[mennyiseg]]

Ha a hivatkozásban csak a táblázat nevét használjuk, az megfelel a [#Data] használatának.

tehát az =Forgalom ugyanaz, mint az =Forgalom[#Data]

Hivatkozás az aktuális sorban

Ez Excel 2007 esetén a [#This Row] magyar verzióban [#Ez a sor] jelölővel, Excel 2010-től pedig a @ jellel történik.

Ezt a jelölőt az Én tapasztalataim szerint nem kell használni, attól még egy számított mezőnél felfogja, hogy az aktuális sorban kell dolgoznia, de vannak esetek, amikor ez lényeges lesz, például gördülő összeg számításakor, ha ilyen hivatkozást szeretnénk használni.

Aktuális sorban egy adott
oszlop cellája
Angol Excel Magyar Excel
Excel 2007 =Forgalom[[#This Row];[mennyiseg]] =Forgalom[[#Ez a sor];[mennyiseg]]
Excel 2010-től =[@mennyiseg] =[@mennyiseg]

Figyelem! Visszafelé nem kompatibilis! Az Excel 2007 nem fogja érteni a @ jelet!

Táblázat visszaalakítása tartománnyá. Mi lesz a hivatkozásokkal?

A struktúrált hivatkozások átalakulnak tartományi hivatkozássá. Ha a tartományt újra táblázattá alakítjuk, akkor ezt már nem követi vissza.

A kapcsolódó videóban mindez bővebben, néhány további érdekességgel, beállítási lehetőséggel megtekinthető.

Letölthető munkafüzet a gyakorláshoz: strukturalt_hivatkozas_nyers

  • Struktúrált hivatkozások alapjai

Vélemény, hozzászólás?