Excel-táblázat létrehozásakor az Excel nevet rendel a táblázathoz és a táblázat minden 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 kattintson a hivatkozni kívánt táblázatcellákra. Használjuk az alábbi példaadatokat egy olyan képlet beírásához, amely automatikusan strukturált hivatkozásokat használ az értékesítési jutalék összegének kiszámításához.
Értékesítő |
Régió |
Értékesítési mennyiség |
Jutalék% |
Jutalékok |
---|---|---|---|---|
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% |
-
Másolja a fenti táblázat mintaadatait az oszlopfejlécekkel együtt, és illessze be azokat egy új Excel-munkalap A1 cellájába.
-
A táblázat létrehozásához jelöljön ki egy cellát az adattartományon belül, és nyomja le a Ctrl+T billentyűkombinációt.
-
Győződjön meg arról, hogy a Táblázat rovatfejekkel jelölőnégyzet be van jelölve, majd kattintson az OK gombra.
-
Az E2 cellába írjon be egy egyenlőségjelet (=), és kattintson a C2 cellára.
Ekkor a szerkesztőlécen, az egyenlőségjel után megjelenik az [@[Értékesítési mennyiség]] strukturált hivatkozás.
-
Írjon be egy csillagot (*) közvetlenül a záró szögletes zárójel után, és kattintson a D2 cellára.
Ekkor a szerkesztőlécen, a csillag után megjelenik a [@[Jutalék%]] strukturált hivatkozás.
-
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.
-
A mintamunkalapon kattintson az E2 cellára
-
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.
-
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.
-
Í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.
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éplet sorában lévő cellák. Ezek a kijelölők nem kombinálhatók más speciális elemkijelölőkkel. Ezekkel kényszerítheti a hivatkozás implicit metszeti viselkedését, vagy felülbírálhatja az implicit metszet viselkedését, és egyetlen értékre hivatkozhat egy oszlopból. 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ázatában egyetlen sor található, az Excel nem cseréli le az #Ez a sor kijelö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. 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:
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 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.
Strukturált hivatkozás |
Hivatkozott elem |
Megfelelő 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 ugyanabban a sorban használja, mint egy fejléc vagy összegsor, 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
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.
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 az alkalmazás áthelyezi az aktuális cellaértékeket, és ennek során beilleszti a megfelelő oszlopkijelölőket |
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ávalTartomány vagy tábla adatainak szűréseTáblázat átalakítása tartománnyáExcel-táblázat kompatibilitási problémáiExcel-táblázat exportálása a SharePointbaAz Excel képleteinek áttekintése