Strukturált hivatkozások használata Excel-táblázatokban

Amikor Excel-táblázatot hoz létre, az Excel nevet társít a táblázathoz és a táblázat mindegyik oszlopfejlécéhez. Amikor képleteket ír az Excel-táblázatba, a program automatikusan megjelenítheti ezeket a neveket, amikor képletet ad meg és cellahivatkozásokat választ ki. Példa az Excel által végzett műveletre:

A közvetlen cellahivatkozások használata helyett	az Excel táblázat- és oszlopneveket használ
=SZUM(C2:C7)	=SZUM(Bevételek[Értékesítési mennyiség])

A táblázat- és oszlopnevek kombinációját strukturált hivatkozásnak nevezzük. A hivatkozásban lévő nevek mindig változnak, amikor adatokkal bővíti a táblázatot, vagy adatokat töröl belőle.

Strukturált hivatkozások jelennek meg akkor is, amikor az Excel-táblázaton kívül hoz létre olyan képletet, amely a táblázat adataira hivatkozik. A hivatkozások segítségével könnyebb megtalálni a táblázatokat a nagyméretű munkafüzetekben.

Ha strukturált hivatkozásokat szeretne használni a képletben, a cellahivatkozások képletbe írása helyett jelölje ki a hivatkozni kívánt táblázatcellákat. Az alábbi példaadatokat felhasználva írjunk be egy képletet, amely automatikusan strukturált hivatkozásokat használ az értékesítési jutalék összegének kiszámítására.

Értékesítő Személy	Régió	Értékesítési mennyiség	Jutalék%	Jutalék összege
András	Észak	260	10%
Péter	Dél	660	15%
Balázs	Kelet	940	15%
Szabolcs	Nyugat	410	12%
Ágnes	Észak	800	15%
Zoltán	Dél	900	15%

1. Másolja a vágólapra a fenti táblázat mintaadatait az oszlopfejlécekkel együtt, majd illessze be egy új Excel-munkalap A1 cellájába.
2. A táblázat létrehozásához jelölje ki az adattartomány bármelyik celláját, és nyomja le a Ctrl+T billentyűkombinációt.
3. Győződjön meg arról, hogy a Táblázat rovatfejekkel jelölőnégyzet be van jelölve, és válassza az OK gombot.
4. Az E2 cellába írjon be egy egyenlőségjelet (=), és jelölje ki a C2 cellát.
   Ekkor a szerkesztőlécen, az egyenlőségjel után megjelenik az [@[Értékesítési mennyiség]] strukturált hivatkozás.
5. Írjon be egy csillagot (*) közvetlenül a záró zárójel után, és jelölje ki a D2 cellát.
   Ekkor a szerkesztőlécen, a csillag után megjelenik a [@[Jutalék%]] strukturált hivatkozás.
6. Nyomja meg az Enter billentyűt.
   Az Excel automatikusan létrehoz egy számított oszlopot, és az oszlop minden egyes cellájába bemásolja a képletet, az egyes sorokhoz igazítva.

Mi történik, ha közvetlen cellahivatkozásokat használok?

Ha egy számított oszlopba közvetlen cellahivatkozásokat ír, akkor nehezebb lehet meghatározni, hogy mit számít ki a képlet.

1. A mintamunkalapon jelölje ki az E2 cellát
2. Írja be a szerkesztőlécen az =C2*D2 képletet, és nyomja le az Enter billentyűt.

Figyelje meg, hogy bár az Excel az oszlop minden cellájára alkalmazza a képletet, mégsem használ strukturált hivatkozást. Ha most beszúrna egy oszlopot a meglévő C és D oszlop közé, akkor át kellene írnia a képletet.

Hogyan tudom módosítani a táblázatok nevét?

Az Excel minden létrehozott Excel-táblázathoz társít egy alapértelmezett táblázatnevet (Táblázat1, Táblázat2 stb.). Ezt a nevet módosíthatja, hogy leíróbb legyen.

1. A táblázat bármelyik celláját kijelölve jelenítse meg a Táblatervező lapot a menüszalagon.
2. Írja be a kívánt nevet a Táblanév mezőbe, és nyomja le az Enter billentyűt.

A példaadatokban az ÉrtékesítésRészleg nevet használtuk.

A névre az alábbi szabályok vonatkoznak:

• Érvényes karakterek használata A névnek betűvel, aláhúzásjellel (_) vagy fordított perjellel ()\ kell kezdődnie. A név további részében lehetnek betűk, számok, pontok és aláhúzásjelek. Nem használhat "C", "c", "R", "r" vagy ehhez hasonló karaktert névnek, mert ezek le vannak már foglalva azonosítónak: az aktív cellához tartozó oszlop vagy sor kijelölésére szolgálnak, amikor beírja őket a Név vagy az Ugrás mezőbe.

• Cellahivatkozások használatának mellőzése A nevek nem lehetnek azonosak cellahivatkozásokkal (például Z$100 vagy R1C1).
• Ne használjon szóközt a szavak elválasztására A névben nem használhatók szóközök. Szóelválasztóként használhat aláhúzásjelet (_) és pontot (.). Például Bevételek, Sales_Tax vagy Első.negyedév.
• Legfeljebb 255 karakterből állhat Egy tábla neve legfeljebb 255 karakterből állhat.
• Használjon egyedi táblázatneveket Ismétlődő nevek nem engedélyezettek. Az Excel nem tesz különbséget a kis- és nagybetűk között a nevekben, ezért ha beírja az "Értékesítés" szót, de a munkafüzetben már van egy másik "ÉRTÉKESÍTÉS" név, a program kérni fogja, hogy adjon meg egy egyedi nevet.
• Objektumazonosító használata Ha táblázatokat, kimutatásokat és diagramokat is szeretne használni, érdemes a nevek elé beállítani az objektumtípust. Például: tbl_Sales értékesítési táblázathoz, pt_Sales értékesítési kimutatáshoz és chrt_Sales értékesítési diagramhoz, vagy ptchrt_Sales értékesítési kimutatásdiagramhoz. Ez a művelet az összes nevét rendezett listában tartja a Névkezelőben.

A strukturált hivatkozások szintaktikai szabályai

A strukturált hivatkozásokat manuálisan is beírhatja és módosíthatja, de nem árt előbb megérteni a strukturált hivatkozások szintaxisát. Vegyük a következő képletet:

=SZUM(Bevételek[[#Összegek];[Értékesítési mennyiség]];Bevételek[[#Adatok];[Jutalékok]])

A képlet a strukturált hivatkozások alábbi összetevőit tartalmazza:

• **Táblázatnév:**DeptSales egy egyéni táblanév. Ez a táblázat adataira hivatkozik, az esetleges fejlécsort és összesítősort kivéve. Használhatja az alapértelmezett táblázatnevet (például Táblázat1), illetve egyéni névre is módosíthatja azt.
• Oszlopkijelölő:[Értékesítési mennyiség] és [Jutalékok összege] olyan oszlopkijelölők, amelyek az általuk képviselt oszlopok nevét használják. Ezek az oszlopadatokra hivatkoznak, az esetleges fejlécsort és összesítősort kivéve. A kijelölőket mindig zárójelek között kell megadni, ahogy az ábrán látható.
• Tételkijelölő:[#Totals] és [ #Data] olyan speciális elemkijelölők, amelyek a táblázat adott részeire, például az összegsorra hivatkoznak.
• Táblázatkijelölő: Az [[#Összegek];[Értékesítési mennyiség]] és [[#Adatok];[Jutalékok]] táblázatkijelölők, amelyek a strukturált hivatkozás külső részeire hivatkoznak. A külső hivatkozások a táblázat nevét követik, és szögletes zárójelek közé kell tenni őket.
• Strukturált hivatkozás:(Bevételek[[#Totals],[Értékesítési mennyiség]] és Bevételek[[#Data],[Jutalékok]] strukturált hivatkozások, amelyeket egy olyan karakterlánc jelöl, amely a tábla nevével kezdődik és az oszlopkijelölővel végződik.

A strukturált hivatkozások manuális létrehozásakor és szerkesztésekor az alábbi szintaktikai szabályokat kell alkalmaznia:

• Szögletes zárójelek a kijelölők körül A táblázatok, az oszlopok és a speciális elemek mindegyikét szögletes zárójelek ([ ]) közé kell tenni. Ha egy kijelölő más kijelölőket is tartalmaz, külső szögleteszárójel-párt kell használni, amely beágyazza az adott kijelölőben hivatkozott kijelölők belső szögletes zárójelpárjait. Példa: =Bevételek[[Értékesítő]:[Körzet]]
• Minden oszlopfejléc szöveges karakterlánc Azonban nincs szükség idézőjelekre, ha strukturált hivatkozásban használják őket. A számok és dátumok, például 2014 vagy 2014.01.01., szintén karakterláncnak számítanak. Nem használhat oszlopfejléceket tartalmazó kifejezéseket. Például a PénzügyiÉvekBevételei[[2014]:[2012]] kifejezés nem fog működni.

Szögletes zárójelek használata a speciális karaktereket tartalmazó oszlopfejlécek körül Ha speciális karakterek szerepelnek a karakterekben, a teljes oszlopfejlécet szögletes zárójelek közé kell foglalni, ami azt jelenti, hogy dupla szögletes zárójeleket kell használni az oszlopkijelölőben. Például: =PénzügyiÉvekBevételei[[Összesen $ Összeg]]

Az alábbi listában szereplő speciális karakterek esetén van szükség szögletes zárójelre:

• Tab
• Új soremelés
• Kocsivissza
• Vessző (,)
• Kettőspont (:)
• Pont (.)
• Bal oldali szögletes zárójel ([)
• Jobb oldali szögletes zárójel (])
• Kettős kereszt (#)
• Aposztróf (')
• Dupla idézőjel (")
• Bal oldali kapcsos zárójel ({)
• Jobb oldali kapcsos zárójel (})
• Dollárjel ($)
• Kalap (^)
• És-jel (&)
• Csillag (*)
• Pluszjel (+)
• Egyenlőségjel (=)
• Mínuszjel (-)
• Nagyobb jel (>)
• Kisebb, mint szimbólum (<)
• Osztásjel (/)
• Kukac jel (@)
• Fordított perjel (\)
• Felkiáltójel (!)
• Bal zárójel (()
• Jobb zárójel ())
• Százalékjel (%)
• Kérdőjel (?)
• Backtick (')
• Pontosvessző (;)
• Tilde (~)
• Aláhúzás (_)

• Escape-karakter használata bizonyos speciális karaktereknél az oszlopfejlécekben Bizonyos karakterek különleges jelentéssel bírnak, és ezért aposztróf (') karaktert kell használni eléjük escape-karakterként. Például: =PénzügyiÉvekBevételei['#Tétel]

Az alábbi listában szereplő speciális karakterek esetén van szükség escape-karakterre ('):

• Bal oldali szögletes zárójel ([)
• Jobb oldali szögletes zárójel (])
• Kettős kereszt (#)
• Aposztróf (')
• Kukac jel (@)

Szóköz karakter használata a strukturált hivatkozások olvashatóságának javítására Szóköz karakterek használatával javíthatja a strukturált hivatkozások olvashatóságát. Például: =Bevételek[ [Értékesítő]:[Körzet] ] vagy =Bevételek[[#Headers], [#Data], [Jutalék%]]

Egy szóköz használata ajánlott:

• Az első bal oldali szögletes zárójel ([) után
• Az utolsó jobb oldali szögletes zárójel (]) elé.
• Vessző után.

Hivatkozási operátorok

A cellatartományok rugalmasabb megadását segítik az oszlopkijelöléseket kombináló alábbi hivatkozási operátorok.

Strukturált hivatkozás	Hivatkozott elem	Operátor	Megfelelő cellatartomány
=Bevételek[[Értékesítő]:[Körzet]]	Szomszédos oszlopok összes cellája	: (kettőspont) tartományoperátor	A2:B7
=Bevételek[Értékesítési mennyiség];Bevételek[Jutalékok]	Oszlopok együttese	; (pontosvessző) összevonási operátor	C2:C7; E2:E7
=Bevételek[[Értékesítő]:[Értékesítési mennyiség]] Bevételek[[Körzet]:[Jutalék%]]	Oszlopok metszete	(szóköz) metszetoperátor	B2:C7

Hivatkozás speciális táblázatelemekre

Ha a táblázat bizonyos részeire akar hivatkozni, például csak az összesítősorra, használja az alábbi speciális elemkijelölőket a strukturált hivatkozásában:

Speciális kijelölő	Hivatkozott elem
#Minden	A teljes táblázat az oszlopfejlécekkel, adatokkal és összesítésekkel együtt (ha vannak).
#Adatok	Csak az adatsorok.
#Fejlécek	Csak a táblázat fejlécsora.
#Összegek	Csak az összesítősor. Ha nincs összesítősor, null a visszaadott érték.
#Ez a sor vagy @ vagy @[Oszlopnév]	Csak a képlettel egy sorban lévő cellák. Ezek a kijelölők nem kombinálhatók más speciális elemkijelölőkkel. Ezekkel kényszerítheti ki az implicit metszetviselkedést a hivatkozáshoz, vagy felülbírálhatja az implicit metszetviselkedést, és egy oszlop egyes értékeire hivatkozhat. Az Excel automatikusan módosítja az #Ez a sor kijelölőket a @ kijelölőre a táblázatokban, amelyekben több adatsor található. De ha táblázata egyetlen sorból áll, az Excel nem cseréli le a #This sorkijelölőt, ami több sor hozzáadásakor váratlan számítási eredményekkel járhat. A számítási problémák elkerülése végett a strukturált hivatkozásokat tartalmazó képletek megadása előtt vegyen fel több sort a táblázatába.

Strukturált hivatkozások minősítése számított oszlopokban

A számított oszlopokban célszerű strukturált hivatkozással megadni a képleteket. A strukturált hivatkozás minősítés nélküli vagy teljesen minősített lehet. A jutalékot forintban kiszámító Jutalékok számított oszlop képlete például a következő táblázatban használható:

Strukturált hivatkozás típusa	Példa	Megjegyzés
Nem minősített	=[Értékesítési mennyiség]*[Jutalék%]	Az aktuális sor megfelelő értékeinek szorzata
Teljesen minősített	=Bevételek[Értékesítési mennyiség]*Bevételek[Jutalék%]	A két oszlop megfelelő értékeinek soronkénti szorzata

Az általánosan követendő szabály a következő: ha egy táblázaton belül strukturált hivatkozásokat használ, például számított oszlop létrehozásakor, használhat nem minősített strukturált hivatkozást, de ha a strukturált hivatkozást a táblázaton kívül használja, teljesen minősített strukturált hivatkozásra van szüksége.

Példák a strukturált hivatkozások használatára

Az alábbi példák bemutatják, hogyan használhatja a strukturált hivatkozásokat.

Strukturált hivatkozás	Hivatkozott elem	Melyik a cellatartomány?
=Bevételek[[#Minden];[Értékesítési mennyiség]]	Az Értékesítési mennyiség oszlopban lévő összes cella.	C1:C8
=Bevételek[[#Fejlécek];[Jutalék%]]	A Jutalék% oszlop fejléce.	D1
=Bevételek[[#Összegek];[Körzet]]	A Körzet oszlop összesítése. Ha nincs összesítősor, a kifejezés eredménye null.	B8
=Bevételek[[#Minden];[Értékesítési mennyiség]:[Jutalék%]]	Az Értékesítési mennyiség és a Jutalék% oszlopban lévő összes cella.	C1:D8
=Bevételek[[#Adatok];[Jutalék%]:[Jutalékok]]	Csak a Jutalék% és a Jutalékok oszlop adatai.	D2:E7
=Bevételek[[#Fejlécek];[Körzet]:[Jutalékok]]	Csak a Körzet és a Jutalék oszlop közötti oszlopok fejlécei.	B1:E1
=Bevételek[[#Összegek];[Értékesítési mennyiség]:[Jutalékok]]	Az Értékesítési mennyiség és a Jutalékok oszlop összesítése. Ha nincs összesítősor, a visszaadott érték null.	C8:E8
=Bevételek[[#Fejlécek];[#Adatok];[Jutalék%]]	Csak a Jutalék% oszlop fejléce és adatai.	D1:D7
=Bevételek[[#Ez a sor]; [Jutalékok]] vagy =Bevételek[@Jutalékok]	Az aktuális sor és a Jutalékok oszlop metszeténél található cella. Ha a fejléc- vagy összesítősorral egy sorban használja, ez #VALUE! hibát ad vissza. Ha ennek a strukturált hivatkozásnak (#Ez a sor) a hosszabb formáját írja be egy több adatsort tartalmazó táblázatba, az Excel automatikusan helyettesíti azt a rövidebb formával (@). Mindkettő ugyanúgy működik.	E5 (ha az aktuális sor az 5.)

Stratégiák a strukturált hivatkozások használatára

A strukturált hivatkozások használatakor vegye figyelembe az alábbiakat.

• Automatikus képletkiegészítés használata: Az automatikus képletkiegészítési funkció nagyon hasznos a strukturált hivatkozások használata során is, mivel segít a helyes szintaxis használatában. További információt a Képletek automatikus kiegészítése funkció használata című témakörben talál.

• Döntés a strukturált hivatkozások létrehozásáról, a félig kijelölt táblázatokhoz Alapértelmezés szerint, amikor képletet hoz létre, amikor kijelöl egy cellatartományt a táblázaton belül, azzal kijelöli a cellákat, és automatikusan megad egy strukturált hivatkozást a képletben szereplő cellatartomány helyett. Ez a funkció nagymértékben megkönnyíti a strukturált hivatkozások megadását. Ezt a viselkedést a Fájlbeállítások>>párbeszédpanel>Táblázatnevek használata képletekben jelölőnégyzetének bejelölésével vagy jelölésének törlésével kapcsolhatja ki.

• Excel-táblázatokra külső hivatkozást tartalmazó munkafüzetek használata más munkafüzetekben Ha egy munkafüzet egy másik munkafüzet Excel-táblázatára mutató külső hivatkozást tartalmaz, akkor a csatolt forrásmunkafüzetet meg kell nyitni az Excelben, hogy a hivatkozásokat tartalmazó célmunkafüzetben ne léphessenek fel #REF! hibák. Ha először megnyitja a célmunkafüzetet, és #REF! hibák jelennek meg, akkor a forrásmunkafüzet megnyitásával meg fogja oldani őket. Ha a forrás munkafüzetet nyitja meg előbb, akkor nem szabad hibakódnak megjelennie.

• Tartomány átalakítása táblázattá, illetve táblázat átalakítása tartománnyá Amikor egy táblázatot tartománnyá alakít, az összes cellahivatkozás a megfelelő abszolút A1 stílusú hivatkozássá alakul át. Amikor egy tartományt alakít át táblázattá, az Excel nem módosítja automatikusan a tartomány cellahivatkozásait a megfelelő strukturált hivatkozásokra.

• Az oszlopfejlécek kikapcsolása A táblázat oszlopfejléceit a Táblázattervezés lap >Rovatfejsorában kapcsolhatja be és ki. Ha kikapcsolja a táblázat oszlopfejléceit, az oszlopneveket használó strukturált hivatkozásokat ez nem érinti, és továbbra is használhatja azokat képletekben. A táblázatfejlécekre közvetlenül hivatkozó strukturált hivatkozások (pl. =Bevételek[[#Headers],[%Jutalék]])#REF eredményeznek.

• Táblázatoszlopok és -sorok felvétele és törlése Mivel a táblázatok adattartományai gyakran változnak, a strukturált hivatkozások cellahivatkozásai automatikusan módosulnak. Ha például táblázatnevet használ egy képletben egy táblázat összes adatot tartalmazó cellájának megszámolására, majd ezután felvesz egy adatsort, akkor a cellahivatkozás automatikusan figyelembe veszi az új adatokat.

• Táblázat vagy oszlop átnevezése: Ha egy oszlopot vagy táblázatot átnevez, az Excel automatikusan módosítja az adott táblázat és oszlopfejléc nevét a munkafüzet összes érintett strukturált hivatkozásában.

• Strukturált hivatkozások áthelyezése, másolása és kitöltése A strukturált hivatkozásokat használó képletek másolásakor vagy áthelyezésekor minden strukturált hivatkozás ugyanaz marad.

  Megjegyzés

  A strukturált hivatkozások másolása és a strukturált hivatkozások kitöltése nem ugyanaz. Másoláskor az összes strukturált hivatkozás változatlan marad, míg a képlet kitöltésekor a teljesen minősített strukturált hivatkozások az oszlopkijelölőket az alábbi táblázatban összegzett sorozathoz hasonlóan módosítják.

A kitöltés iránya	Kitöltés közben használandó billentyű	Eredmény
Fel vagy le	(Nincs)	Nem módosulnak az oszlopkijelölők
Fel vagy le	Ctrl	Az oszlopkijelölők sorozatszerűen változnak meg
Jobb vagy bal	(Nincs)	Az oszlopkijelölők sorozatszerűen változnak meg
Fel, le, jobb vagy bal	Shift	Az aktuális cellák értékeinek felülírása helyett a program áthelyezi az aktuális cellaértékeket, és beszúrja őket az oszlopkijelölőkbe.

További segítségre van szüksége?

Kérdéseivel mindig felkeresheti az Excel technikai közösség egyik szakértőjét, vagy segítséget kérhet a közösségekben.

Kapcsolódó témakörök

Excel-táblázatok – áttekintés
Táblázatok létrehozása és formázása
Adatok összegzése Excel-táblázatban
Excel-táblázat formázása
Táblázat átméretezése sorok és oszlopok hozzáadásával és eltávolításával
Adatok szűrése tartományban vagy táblázatban
Táblázat átalakítása tartománnyá
Az Excel-táblázatokkal kapcsolatos kompatibilitási problémák
Excel-táblázat exportálása a SharePointba
Az Excel képleteinek áttekintése