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

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 kattintson az E2 cellára

2. A szerkesztőlécen írja be az =C2*D2 szöveget, é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. Jelöljön ki egy cellát a táblázatból, hogy megjelenjen a Táblázateszközök > Tervezés lap a menüszalagon.

2. Írja be a kívánt nevet a Táblázat neve mezőbe, és nyomja le az Enter billentyűt.

A példaadatokban ez a név a Bevételek.

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

• Érvényes karakterek használata  A nevet mindig betűvel, aláhúzásjellel (_) vagy fordított perjellel (\) kezdje. 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ó oszlopot vagy sort azonosítják a Név vagy az Ugrás mezőbe beírva.

• Ne használjon cellahivatkozásokat  A nevek nem lehetnek azonosak a cellahivatkozásokkal, például Z$100 vagy R1C1.

• Ne használjon szóközt a szavak elválasztásához  A névben nem használhatók szóközök. Használhatja az aláhúzásjelet (_) és a pontot (.) szóelválasztóként. Például: Bevételek, Sales_Tax vagy Első negyedév.

• Legfeljebb 255 karakter használata Egy táblanév legfeljebb 255 karakterből állhat.

• Egyedi táblanevek használata A duplikált nevek nem engedélyezettek. Az Excel nem tesz különbséget a kis- és a nagybetűk között a nevekben, így ha a "Sales" (Értékesítés) kifejezést adja meg, de ugyanabban a munkafüzetben már szerepel egy "SALES" nevű név, a rendszer kérni fogja, hogy válasszon egyedi nevet.

• Objektumazonosító használata  Ha táblák, kimutatások és diagramok kombinációját tervezi, érdemes a neveket előtagként hozzáadni az objektumtípushoz. Például: tbl_Sales egy értékesítési táblához, pt_Sales egy értékesítési kimutatáshoz, chrt_Sales egy értékesítési diagramhoz, vagy ptchrt_Sales egy értékesítési kimutatásdiagramhoz. Ezzel az összes nevét egy rendezett listában tárolja a Névkezelőben.

A strukturált hivatkozások szintaktikai szabályai

A strukturált hivatkozásokat manuálisan is beírhatja vagy módosíthatja a képletben, de ehhez segít megérteni a strukturált hivatkozási szintaxist. Vegyük az alábbi képletet példaként:

=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áblanév:    A Bevételek 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.

• Az oszlopkijelölő:    [Értékesítési összeg] és [Jutalék] 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ó.

• Az elemkijelölő:    [#Totals] és [#Data] speciális elemkijelölők, amelyek a tábla bizonyos 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ék]] strukturált hivatkozások, amelyeket egy olyan sztring 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 a következő szintaktikai szabályokat alkalmazza:

• Szögletes zárójelek a kijelölők körül:    A táblázatok, az oszlopok és a speciális elemek megadásánál szögleteszárójel-párokat ([ ]) kell használni. 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éldául: =Bevételek[[Értékesítő]:[Régió]]

• Minden oszlopfejléc szöveges karakterlánc:    Strukturált hivatkozáshoz azonban nincs szükség idézőjelekre. 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.

Speciális karaktereket tartalmazó oszlopfejlécek köré szögletes zárójelek használata    Ha vannak speciális karakterek, a teljes oszlopfejlécet szögletes zárójelek közé kell tenni, ami azt jelenti, hogy dupla szögletes zárójelekre van szükség egy oszlopkijelölőben. Például: =BevételekFYSummary[[Teljes $ összeg]]

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

• Tab

• Vonalcsatorna

• Kocsivissza

• Vessző (,)

• Kettőspont (:)

• Pont (.)

• Bal oldali szögletes zárójel ([)

• Jobb oldali szögletes zárójel (])

• Kettős kereszt (#)

• Szimpla idézőjel (')

• Dupla idézőjel (")

• Bal oldali kapcsos zárójel ({)

• Jobb oldali kapcsos zárójel (})

• Dollárjel ($)

• Simító (^)

• És-jel (&)

• Csillag (*)

• Pluszjel (+)

• Egyenlőségjel (=)

• Mínuszjel (-)

• Nagyobb, mint szimbólum (>)

• Kisebb, mint szimbólum (<)

• Osztásjel (/)

• At sign (@)

• Fordított perjel (\)

• Felkiáltójel (!)

• Bal oldali zárójel (()

• Jobb oldali zárójel ())

• Százalékjel (%)

• Kérdőjel (?)

• Backtick (')

• Pontosvessző (;)

• Tilde (~)

• Aláhúzás (_)

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

Íme azoknak a speciális karaktereknek a listája, amelyeknek feloldókarakterekre (') van szükségük a képletben:

• Bal oldali szögletes zárójel ([)

• Jobb oldali szögletes zárójel (])

• Kettős kereszt (#)

• Szimpla idézőjel (')

• At sign (@)

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

Az alábbi helyeken ajánlott egy szóközkarakter használata:

• Az első bal oldali szögletes zárójel ([) után

• Az utolsó jobb oldali szögletes zárójel (]) előtt.

• Egy 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.

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:

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. Ha például a Jutalék nevű számított oszlopot szeretné létrehozni, amely dollárban számítja ki a jutalék összegét, az alábbi képleteket használhatja:

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 fel, 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.

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

Strukturált hivatkozások használatakor vegye figyelembe a következőket.

• 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ó: Képletek automatikus kiegészítésének használata.

• Annak eldöntése, hogy strukturált hivatkozásokat hoz-e létre a táblákhoz a félkijelölésekben    Ha képletet hoz létre, a táblázaton belüli cellatartományra kattintva alapértelmezés szerint a cellákat félig kijelöli, és automatikusan strukturált hivatkozást ad meg a képlet cellatartománya helyett. Ez a funkció nagymértékben megkönnyíti a strukturált hivatkozások megadását. Ezt a viselkedést be- és kikapcsolhatja, ha bejelöli vagy törli a Képletek használata párbeszédpanelEn a Táblázatnevek használata képletekben jelölőnégyzetet, vagy törölje belőle a jelet a Fájl > Beállítások > a Képletek > A képletek használata párbeszédpanelen.

• Más munkafüzetek Excel-táblázataira mutató külső hivatkozásokat tartalmazó munkafüzetek használata:    Ha egy munkafüzet egy másik munkafüzetben lévő Excel-táblázatra mutató külső hivatkozást tartalmaz, a csatolt forrásmunkafüzetnek meg kell nyitnia az Excelben, hogy elkerülje a hivatkozásokat tartalmazó célmunkafüzet #REF! hibáit. Ha először a célmunkafüzetet nyitja meg, és #REF! hibaüzenetek jelennek meg, a forrásmunkafüzet megnyitásakor a program megoldja ő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 tartománysá alakít át egy táblázatot, az összes cellahivatkozás a megfelelő abszolút A1-stílusú hivatkozássá változik. Amikor táblázattá alakít át egy tartományt, az Excel nem módosítja automatikusan ennek a tartománynak a cellahivatkozásait a megfelelő strukturált hivatkozásokká.

• Oszlopfejlécek kikapcsolása    A táblázat oszlopfejléceit be- és kikapcsolhatja a tábla Tervezés lapján > Fejlécsor. Ha kikapcsolja a táblázat oszlopfejléceit, az oszlopneveket használó strukturált hivatkozásokra nincs hatással, és továbbra is használhatja őket a képletekben. A táblázatfejlécekre közvetlenül hivatkozó strukturált hivatkozások (pl. =Bevételek[[#Headers],[%Bizottság]]) #REF eredményeznek.

• Táblázatoszlopok és -sorok felvétele és törlése:    Mivel a táblázat 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ás másolása és a strukturált hivatkozás kitöltése nem ugyanaz. Másoláskor az összes strukturált hivatkozás változatlan marad, míg a képletek kitöltésekor a teljesen minősített strukturált hivatkozások az oszlopkijelölőket úgy módosítják, mint egy adatsort az alábbi táblázatban összefoglalt módon.

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éseVideó: Excel-táblázat
létrehozása és formázásaExcel-táblázatban
szereplő adatok összesenExcel-táblázat
formázásaTáblázat átméretezése sorok és oszlopok hozzáadásával vagy eltávolításával
Tartomány vagy tábla adatainak szűrése
Táblázat átalakítása tartománnyá
Excel-táblázat kompatibilitási problémái
Excel-táblázat exportálása a SharePointba
Az Excel képleteinek áttekintése