Používanie štruktúrovaných odkazov v tabuľkách programu Excel

Čo sa deje pri používaní explicitných odkazov na bunky?

Ak do vypočítavaného stĺpca zadáte explicitné odkazy na bunky, môže byť komplikovanejšie určiť, čo daný vzorec počíta.

1. Vo vzorovom hárku vyberte bunku E2

2. Do riadka vzorcov zadajte =C2*D2 a stlačte kláves Enter.

Všimnite si, že hoci Excel skopíruje vzorec do stĺpca, nepoužíva štruktúrované odkazy. Ak napríklad medzi existujúce stĺpce C a D pridáte stĺpec, budete musieť vzorec skontrolovať.

Ako zmením názov tabuľky?

Ak vytvoríte excelovú tabuľku, Excel vytvorí predvolený názov tabuľky (Tabuľka1, Tabuľka2 atď.), môžete ho však zmeniť na zmysluplnejší názov.

1. Vyberte ľubovoľnú bunku v tabuľke a na páse s nástrojmi sa zobrazí karta Návrhtabuľky.

2. Do poľa Názov tabuľky zadajte požadovaný názov a stlačte kláves Enter.

V našom príklade sme použili názov ObchOdd.

V názvoch tabuliek používajte nasledujúce pravidlá:

• Používajte platné znaky  Názov vždy začínajte písmenom, znakom podčiarknutia (_) alebo opačnou lomkou (\). V zostávajúcej časti názvu používajte písmená, čísla, bodky a znaky podčiarknutia. Názov nemôže byť C, c, R ani r, pretože tieto písmená sú už určené ako skratka na výber stĺpca alebo riadka aktívnej bunky po zadaní do poľa Názov alebo Prejsť na.

• Nepoužívajte odkazy na bunky  Názvy nemôžu byť rovnaké ako odkazy na bunky, napríklad Z$100 alebo R1C1.

• Neoddeľujte slová medzerou  Názov nemôže obsahovať medzery. Môžete použiť znak podčiarknutia (_) a bodku (.). Príklad: ObchOdd, Daň_z_predaja alebo Prvý.štvrťrok.

• Používajte najviac 255 znakov Názov tabuľky môže obsahovať najviac 255 znakov.

• Používanie jedinečných názvov tabuliek Duplicitné názvy nie sú povolené. Excel nerozlišuje medzi veľkými a malými písmenami v názvoch, takže ak zadáte výraz Predaj, ale v tom istom zošite už máte iný názov s názvom PREDAJ, zobrazí sa výzva na výber jedinečného názvu.

• Použitie identifikátora objektu  Ak plánujete použiť kombináciu tabuliek, kontingenčných tabuliek a grafov, pred názvy je vhodné priradiť typ objektu. Príklad: tbl_Sales pre tabuľku predaja, pt_Sales pre kontingenčnú tabuľku predaja a chrt_Sales pre graf predaja alebo ptchrt_Sales pre kontingenčný graf predaja. Všetky vaše mená sa tak budú nachádzať v zoradenom zozname v Správcovi názvov.

Pravidlá syntaxe štruktúrovaných odkazov

Štruktúrované odkazy môžete do vzorca zadať alebo zmeniť aj manuálne, pomôže to však pochopiť syntax štruktúrovaných odkazov. Pozrime sa na nasledujúci príklad vzorca:

=SUM(ObchOdd[[#Súčty],[Suma predaja]],ObchOdd[[#Údaje],[Suma provízie]])

Tento vzorec obsahuje nasledujúce časti štruktúrovaného odkazu:

• Názov tabuľky:    ObchOdd je názov vlastnej tabuľky. Odkazuje na údaje v tabuľke bez riadka hlavičky či riadka súčtu. Môžete použiť predvolený názov tabuľky, napríklad Tabuľka1, alebo ho môžete zmeniť na vlastný názov.

• Špecifikátor stĺpca:    [Suma predaja] a [Suma provízie] sú špecifikátory stĺpcov, ktoré používajú názvy stĺpcov, ktoré predstavujú. Odkazujú na údaje v stĺpci bez riadka hlavičky stĺpcov či riadka súčtu. Špecifikátory sa vždy uzatvárajú do hranatých zátvoriek, ako je to znázornené na obrázku.

• Špecifikátor položky:    [#Totals] a [#Data] sú špecifikátory špeciálnej položky, ktoré odkazujú na konkrétne časti tabuľky, ako je napríklad riadok súčtu.

• Špecifikátor tabuľky:    [[#Súčty], [Suma predaja]] a [[#Údaje], [Suma provízie]] sú špecifikátory tabuľky, ktoré zastupujú vonkajšie časti štruktúrovaného odkazu. Vonkajšie odkazy nasledujú po názve tabuľky a uzatvárajú sa do hranatých zátvoriek.

• Štruktúrovaný odkaz:    (ObchOdd[[#Totals],[Suma predaja]] a ObchOdd[[#Data],[Suma provízie]] sú štruktúrované odkazy vyjadrené reťazcom, ktorý začína názvom tabuľky a končí špecifikátorom stĺpca.

Pri manuálnom vytváraní a úprave štruktúrovaných odkazov dodržiavajte tieto pravidlá pre syntax:

• Uvádzanie špecifikátorov v zátvorkách    Všetky špecifikátory tabuľky, stĺpca a špeciálnej položky musia byť uzatvorené párom hranatých zátvoriek ([ ]). Špecifikátor, ktorý obsahuje ďalšie špecifikátory, vyžaduje vonkajšie uzatvorené hranaté zátvorky na uzatvorenie vnútorných hranatých zátvoriek ďalších špecifikátorov. Príklad: =ObchOdd[[Predajca]:[Oblasť]]

• Všetky hlavičky stĺpcov sú textové reťazce    Pri použití v štruktúrovanom odkaze však úvodzovky nevyžadujú. Za textové reťazce sa považujú aj čísla alebo dátumy, napríklad 2014 alebo 1/1/2014. V hlavičkách stĺpcov nie je možné používať výrazy. Výraz SúhrnFRObchOdd[[2014]:[2012]] napríklad nebude fungovať.

Použitie zátvoriek okolo hlavičiek stĺpcov so špeciálnymi znakmi    Ak existujú špeciálne znaky, celá hlavička stĺpca musí byť uzavretá v hranatých zátvorkách, čo znamená, že v špecifikátore stĺpca sa vyžadujú dvojité zátvorky. Napríklad: =ObchOddFYSúhrn[[Celková suma v $]]

Tu je zoznam špeciálnych znakov, ktoré vo vzorci vyžadujú zátvorky navyše:

• Tab

• Posun riadka

• Medzera

• Čiarka (,)

• Dvojbodka (:)

• Bodka (.)

• Ľavá hranatá zátvorka ([)

• Pravá hranatá zátvorka (])

• Znak mriežky (#)

• Jednoduché úvodzovky (')

• Dvojité úvodzovky (")

• Ľavá zložená zátvorka ({)

• Pravá zložená zátvorka (})

• Znak dolára ($)

• Znak vsuvky (^)

• Znak &

• Hviezdička (*)

• Znamienko plus (+)

• Znamienko rovnosti (=)

• Znamienko mínus (-)

• Symbol Väčšie ako (>)

• Symbol menší ako (<)

• Znak delenia (/)

• Na znak (@)

• Opačná lomka (\)

• Výkričník (!)

• Ľavá zátvorka (()

• Pravá zátvorka ())

• Znak percenta (%)

• Otáznik (?)

• Opačné apostrof (')

• Bodkočiarka (;)

• Vlnovka (~)

• Podčiarkovník (_)

• Niektoré špeciálne znaky v hlavičkách stĺpcov vyžadujú použitie znaku ukončenia    Niektoré znaky majú špeciálny význam a vyžadujú použitie apostrofu (') ako znaku ukončenia. Príklad: =SúhrnFRObchOdd['#Položiek]

Tu je zoznam špeciálnych znakov, ktoré vo vzorci vyžadujú znak ukončenia ('):

• Ľavá hranatá zátvorka ([)

• Pravá hranatá zátvorka (])

• Znak mriežky (#)

• Jednoduché úvodzovky (')

• Na znak (@)

Používanie znaku medzery na zlepšenie prehľadnosti v štruktúrovanom odkaze    Na zlepšenie prehľadnosti štruktúrovaného odkazu môžete použiť znaky medzery. Príklad: =ObchOdd[ [Predajca]:[Oblasť] ] alebo =ObchOdd[[#Hlavičky], [#Údaje], [% provízie]]

Použitie jednej medzery sa odporúča:

• Za prvou ľavou zátvorkou ([)

• Pred poslednou pravou zátvorkou (]).

• Po čiarkach.

Odkazové operátory

Na zvýšenie flexibility pri zadávaní rozsahov buniek môžete použiť nasledujúce odkazové operátory na kombinovanie špecifikátorov stĺpcov.

Špecifikátory špeciálnych položiek

Ak chcete odkazovať na určité časti tabuľky, napríklad iba na riadok súčtov, môžete v štruktúrovanom odkaze použiť nasledujúce špecifikátory špeciálnych položiek.

Vymedzovanie štruktúrovaných odkazov vo vypočítaných stĺpcoch

Pri vytváraní vypočítaného stĺpca spravidla na vytvorenie vzorca použijete štruktúrovaný odkaz. Tento štruktúrovaný odkaz môže byť neúplný alebo úplný. Ak chcete napríklad vytvoriť vypočítaný stĺpec s názvom Suma provízie, ktorý vypočíta výšku provízie v dolároch, môžete použiť nasledujúce vzorce:

Je potrebné dodržiavať nasledujúce všeobecné pravidlo: Ak použijete štruktúrované odkazy v tabuľke, napríklad pri vytváraní vypočítaného stĺpca, môžete použiť neúplný štruktúrovaný odkaz. Ak však používate štruktúrovaný odkaz mimo tabuľky, je potrebné použiť úplný štruktúrovaný odkaz.

Príklady používania štruktúrovaných odkazov

Nasleduje niekoľko spôsobov používania štruktúrovaných odkazov.

Postupy pri práci so štruktúrovanými odkazmi

Pri práci so štruktúrovanými odkazmi zvážte nasledujúce skutočnosti.

• Používanie funkcie Automatické dokončovanie vzorca    Používanie funkcie Automatické dokončovanie vzorca môže byť veľmi užitočné, ak chcete zadať štruktúrovaný odkaz a zabezpečiť použitie správnej syntaxe. Ďalšie informácie nájdete v téme Používanie funkcie Automatické dokončovanie vzorca.

• Rozhodnite sa, či sa majú generovať štruktúrované odkazy pre tabuľky v semi-selections    Keď vytvoríte vzorec, pri výbere rozsahu buniek v tabuľke sa predvolene vyberú bunky a namiesto rozsahu buniek do vzorca automaticky zadá štruktúrovaný odkaz. Toto správanie pri výbere častí zjednodušuje zadávanie štruktúrovaného odkazu. Toto správanie môžete zapnúť alebo vypnúť začiarknutím alebo zrušením začiarknutia políčka Použiť názvy tabuliek vo vzorcoch v dialógovom okne > >Vzorce > Práca so vzorcami.

• Používanie zošitov s externými prepojeniami na excelové tabuľky v iných zošitoch    Ak zošit obsahuje externé prepojenie na excelovú tabuľku v inom zošite, tento prepojený zdrojový zošit musí byť otvorený v programe Excel, aby sa predišlo chybám #REF! v cieľovom zošite, ktorý obsahuje prepojenia. Ak najprv otvoríte cieľový zošit a zobrazia sa chyby #REF! , odstránia sa, ak potom otvoríte zdrojový zošit. Ak najprv otvoríte zdrojový zošit, nemali by sa zobraziť žiadne kódy chýb.

• Konverzia rozsahu na tabuľku a naopak    Keď konvertujete tabuľku na rozsah, všetky odkazy na bunky sa zmenia na ekvivalentné absolútne odkazy štýlu A1. Keď konvertujete rozsah na tabuľku, Excel automaticky nezmení odkazy na bunky v tomto rozsahu na ekvivalentné štruktúrované odkazy.

• Vypnutie hlavičiek stĺpcov    Hlavičky stĺpcov tabuľky môžete zapnúť alebo vypnúť na karte Návrh tabuľky > riadok hlavičky. Ak vypnete hlavičky stĺpcov tabuľky, štruktúrované odkazy, ktoré používajú názvy stĺpcov, nie sú ovplyvnené a stále ich môžete použiť vo vzorcoch. Štruktúrované odkazy, ktoré odkazujú priamo na hlavičky tabuľky (napr. =ObchOdd[[#Headers],[%Komisia]]) budú mať za následok #REF.

• Pridanie alebo odstránenie stĺpcov alebo riadkov v tabuľke    Keďže rozsahy údajov tabuľky sa často menia, odkazy na bunky pre štruktúrované odkazy sa automaticky upravia. Ak použijete napríklad názov tabuľky vo vzorci na spočítanie všetkých buniek údajov v tabuľke a potom pridáte riadok s údajmi, odkaz na bunku sa automaticky upraví.

• Premenovanie tabuľky alebo stĺpca    Ak premenujete tabuľku alebo stĺpec, Excel automaticky zmení použitie tejto tabuľky a hlavičky stĺpca vo všetkých štruktúrovaných odkazoch, ktoré sú v použité zošite.

• Presúvanie, kopírovanie a vypĺňanie štruktúrovaných odkazov    Ak kopírujete alebo presúvate vzorec, ktorý používa štruktúrovaný odkaz, všetky štruktúrované odkazy ostanú rovnaké.

  Poznámka: Kopírovanie štruktúrovaného odkazu a vyplnenie štruktúrovaného odkazu nie je to isté. Pri kopírovaní zostanú všetky štruktúrované odkazy rovnaké, zatiaľ čo pri vypĺňaní vzorca úplne kvalifikované štruktúrované odkazy upravia špecifikátory stĺpcov ako rad, ako je to zhrnuté v nasledujúcej tabuľke.

Potrebujete ďalšiu pomoc?

Vždy sa môžete opýtať odborníka v komunite Excel Tech Community alebo získať podporu v komunitách.

Súvisiace témy

Prehľad excelových tabuliek
Video: Vytvorenie a formátovanie excelovej tabuľky
Súčet údajov v excelovej tabuľke
Formátovanie excelovej tabuľky
Zmena veľkosti tabuľky pridaním alebo odstránením riadkov a stĺpcov
Filtrovanie údajov v rozsahu alebo tabuľke
Konverzia tabuľky na rozsah
Problémy
s kompatibilitou excelovej tabuľkyExportovanie excelovej tabuľky do SharePointu
Prehľady vzorcov v Exceli