Používání strukturovaných odkazů v tabulkách aplikace Excel

Důležité :  Tento článek je strojově přeložený – přečtěte si toto upozornění. Anglickou verzi tohoto článku pro referenci najdete tady.

Po Vytvoření Excelové tabulkyse Excel přiřadí název tabulky a záhlaví sloupce v tabulce. Po přidání vzorce do tabulky Excelu tyto názvy se mohou zobrazovat automaticky zadejte vzorec a vyberte odkazy na buňky v tabulce místo ručně zadat. Tady je příklad udělá tohle Excelu:

Namísto použití explicitních odkazů

Excel použije názvy tabulky a sloupců

=Suma(C2:C7)

=SUMA(Oddělení-prodej[Prodej-částka])

Pro kombinaci názvů tabulky a sloupců se používá označení strukturovaný odkaz. Při přidání nebo odebrání dat v tabulce dochází u strukturovaných názvů ke změně.

Strukturované odkazy se zobrazují také při vytvoření vzorce mimo excelovou tabulku, který odkazuje na data tabulky. Tyto odkazy umožňují snazší lokalizaci tabulky ve velkém sešitu.

Pokud chcete do vzorce zahrnout strukturované odkazy, klikněte na buňky tabulky, na které chcete odkazovat – nemusíte odkazy zadávat do vzorce. V následujícím příkladu použijeme data k vytvoření vzorce, který automaticky používá strukturované odkazy k výpočtu provize z prodeje.

Prodej Osoba

Oblast

Prodej-částka

Provize-%

Provize-částka

Josef

Sever

260

10 %

Robert

Jih

660

15 %

Míša

Východ

940

15 %

Emil

Západ

410

12 %

Dafna

Sever

800

15 %

Robert

Jih

900

15 %

  1. Zkopírujte ukázková data v tabulce uvedené výše včetně záhlaví sloupců a vložte je do buňky A1 nového listu aplikace Excel.

  2. Pokud chcete vytvořit tabulku, vyberte libovolnou buňku v oblasti dat a stiskněte Kombinaci kláves Ctrl + T.

  3. Ujistěte se, že je zaškrtnuté políčko tabulka obsahuje záhlaví a klikněte na OK.

  4. Do buňky E2 napište rovnítko (=) a klikněte na buňku C2.

    V řádku vzorců se za rovnítkem zobrazí strukturovaný odkaz [@[Prodej-částka]].

  5. Hned za pravou hranatou závorku zadejte hvězdičku (*) a klikněte na buňku D2.

    V řádku vzorců se za hvězdičkou zobrazí strukturovaný odkaz [@[Provize-%]].

  6. Stiskněte klávesu Enter.

    Excel automaticky vytvoří počítaný sloupec, zkopíruje za váš vzorec do celého sloupce a pro každý řádek ho upraví.

Co se stane, pokud použiji explicitní odkazy na buňky?

Pokud ve výpočtovém sloupci nastavíte explicitní odkazy na buňky, půjde hůře poznat, co vzorec počítá.

  1. V ukázkové listu klikněte na buňku E2

  2. Do řádku vzorců zadejte = C2 * D2 a stiskněte klávesu Enter.

Všimněte si, že přestože Excel zkopíruje vzorec do celého sloupce, nepoužívá strukturované odkazy. Pokud třeba přidáte sloupec mezi stávající sloupce C a D, bude potřeba vzorec opravit.

Jak můžu změnit název tabulky?

Při vytvoření excelové tabulky Excel přiřadí výchozí název tabulky (Tabulka1, Tabulka2 atd.). Název ale můžete změnit, aby byl pro vás smysluplnější.

  1. Vyberte libovolnou buňku v tabulce a zobrazte Nástroje tabulky > karta Návrh na pásu karet.

  2. Zadejte název, který se má v poli Název tabulky a stiskněte klávesu Enter.

V příkladu jsme použili název Oddělení-prodej.

Pro názvy tabulek použijte následující pravidla:

  • Používejte platné znaky  Vždycky začínají písmenem, název znaku podtržítka (_) nebo zpětné lomítko (\). Použití písmen, čísla, tečky a podtržítka pro zbytek název. Nelze použít "C"; "c", "R" nebo "r" název, protože máte už určený jako místní pro vybrání sloupce nebo řádku aktivní buňku při zadávání do pole název nebo Přejít na .

  • Nepoužívejte odkazy na buňky  Názvy nemůžou být stejné jako odkaz na buňku, třeba Z$ 100 nebo R1C1.

  • Nepoužívejte mezery k oddělení slov  Mezery nelze použít v názvu. Podtržítka (_) a období (.) slouží jako oddělovače Wordu. Například oddělení-Prodej, Daň_z_prodeje nebo první.čtvrtletí.

  • Nepoužívejte víc než 255 znaků Název tabulky může obsahovat maximálně 255 znaků.

  • Používejte jedinečné názvy tabulek Duplicitní názvy nejsou povolené. Excel nerozlišuje malá a velká písmena v názvech, takže pokud zadáte „Prodej“ a už máte v sešitu jinou tabulku s názvem „PRODEJ“, zobrazí se vám výzva k zadání jedinečného názvu.

  • Použití identifikátor objektu  Pokud nebudete chtít s kombinaci tabulkách, kontingenčních tabulek a grafů, je vhodné předponu názvech typ objektu. Příklad: tbl_Sales pro prodejní tabulky, pt_Sales pro prodejní kontingenční tabulky a chrt_Sales pro prodejní graf nebo ptchrt_Sales pro prodejní kontingenční graf. To zachová veškeré jmen v uspořádaných seznamů v okně Správce názvů.

Pravidla syntaxe strukturovaného odkazu

Můžete také zadat nebo změnit strukturované odkazy ručně ve vzorci, ale aby je dostala, vám pomůže pochopit syntaxe strukturovaného odkazu. Projděme si následující příklad vzorce:

=SUMA(Oddělení-prodej[[#Totals],[Prodej-částka]],Oddělení-prode[[#Data],[Provize-částka]])

Strukturovaný odkaz tohoto vzorce má tyto prvky:

  • Název tabulky:   Oddělení-Prodej je název vlastní tabulky. Odkazuje na data tabulky bez všechny řádky záhlaví nebo součet. Můžete použít výchozí název tabulky, například Tabulka1, nebo ji změnit na článek použití názvu vlastní.

  • Specifikátor sloupce:   [Prodej-částka]a[Provize-částka] jsou specifikátory sloupců, které používají názvy sloupců představují. Odkazují data ve sloupci, bez řádku záhlaví nebo součtu sloupce. Jak je vidět, uzavřete vždy specifikátory v závorkách.

  • Specifikátor položky:    Specifikátory zvláštních položek, které odkazují na určité části obsahu, například řádek Součet se [#Totals] a [#Data] .

  • Specifikátor tabulky:   [#Totals], [Prodej-částka] a [#Data], [#Provize-částka] jsou zvláštní specifikátory tabulky, které zastupují vnější části strukturovaného odkazu. Vnější odkazy následují za názvem tabulky a jsou uzavřené do hranatých závorek.

  • Strukturovaný odkaz:   (Oddělení-prodej [[#Totals], [Prodej-částka]] a Oddělení-prodej [[#Data], [Provize-částka]] jsou strukturované odkazy představované řetězcem, který začíná názvem tabulky a končí specifikátorem sloupce.

Pokud chcete ručně vytvořit nebo upravit strukturované odkazy, řiďte se následujícími pravidly syntaxe:

  • Použití závorek kolem specifikátorů    Všechny tabulky, sloupce a specifikátory zvláštních položek musíte být uzavřeny do uvozovek ([]). Specifikátor, který obsahuje jiné specifikátory vyžaduje vnějších uvozovek potřeba uzavřít vnitřních uvozovek dalších specifikátorů. Příklad: = Oddělení-prodej [[prodejce]: [Oblast]]

  • Všechna záhlaví sloupců jsou textové řetězce    Ale nevyžadují nabídek, byli zvyklí na strukturovaného odkazu. Čísla nebo kalendářní data, například 2014 nebo 1/1/2014 jsou také považované za textové řetězce. Nelze používat výrazy se záhlavími sloupců. Příklad výrazu DeptSalesFYSummary [[2014]: [2012]] nebude fungovat.

Kolem záhlaví sloupců se speciálními znaky používejte závorky    V případě speciálních znaků je potřeba celé záhlaví sloupce uzavřít do závorek, což znamená, že ve specifikátoru sloupce se vyžadují hranaté závorky. Příklad: =Souhrn-fisk.-roku-Oddělení-prodej[[Celková-částka-v-$]]

Tady je seznam speciálních znaků, které potřebují ve vzorci závorky navíc:

  • Tab

  • Posun řádku

  • Návrat na začátek řádku

  • Čárka (,)

  • Dvojtečka (:)

  • Tečka (.)

  • Levá hranatá závorka ([)

  • Pravá hranatá závorka (])

  • Křížek (#)

  • Jednoduchá uvozovka (')

  • Dvojitá uvozovka (")

  • Levá složená závorka ({)

  • Pravá složená závorka (})

  • Znak dolaru ($)

  • Stříška (^)

  • Ampersand (&)

  • Hvězdička (*)

  • Znaménko plus (+)

  • Rovnítko (=)

  • Znaménko minus (-)

  • Symbol větší než (>)

  • Symbol menší než (<)

  • Znaménko dělení (/)

  • Pro některé speciální znaky v záhlavích sloupců používejte řídicí znak    Některé znaky mají zvláštní význam a vyžadují použití jednoduchých uvozovek (') jako řídicího znaku. Například: =Souhrn-fisk.-roku-Oddělení-prodej['#Položek]

Tady je seznam speciálních znaků, které potřebují ve vzorci řídicí znak ('):

  • Levá hranatá závorka ([)

  • Pravá hranatá závorka (])

  • Křížek (#)

  • Jednoduchá uvozovka (')

Používejte znak mezery ke zlepšení čitelnosti ve strukturovaném odkazu    Pomocí znaků mezery můžete zlepšit čitelnost strukturovaného odkazu. Například: =Oddělení-prodej[ [Prodejce]:[Oblast] ] nebo =Oddělení-prodej[[#Headers], [#Data], [Provize-%]]

Doporučujeme použít jednu mezeru:

  • Po první levé hranaté závorce ([)

  • Před poslední pravou hranatou závorkou (])

  • Za čárkou.

Odkazovací operátory

Rozsahy buněk můžete přidávat ještě flexibilněji použitím následujících odkazovacích operátorů ke kombinování specifikátorů sloupců.

Strukturovaný odkaz

Odkazovaná položka

Použitý operátor

Odpovídající oblast buněk:

=Oddělení-prodej[[Prodejce]:[Oblast]]

Všechny buňky ve dvou či víc sousedních sloupcích

: (dvojtečka) – operátor oblasti

A2:B7

=Oddělení-prodej[Prodej-částka],Oddělení-prodej[Provize-částka]

Spojení dvou či víc sloupců

, (čárka) – operátor sjednocení

C2:C7, E2:E7

=Oddělení-prodej[[Prodejce]:[Prodej-částka]] Oddělení-prodej[[Oblast]:[Provize-%]]

Průnik dvou či víc sloupců

 (mezera) operátor průniku

B2:C7

Specifikátory zvláštních položek

Pokud chcete odkazovat na specifické části tabulky, třeba na řádek souhrnů, můžete ve strukturovaných odkazech použít kterýkoli z následujících specifikátorů zvláštních položek.

Specifikátor zvláštní položky

Odkazovaná položka

#All

Celá tabulka, včetně záhlaví sloupců, dat a součtů (pokud existují)

#Data

Jenom řádky dat

#Headers

Jenom řádek záhlaví

#Totals

Jenom řádek součtů. Pokud žádný neexistuje, vrátí hodnotu Null.

#This Row

nebo

@

nebo

@[Název sloupce]

Jenom buňky ve stejném řádku jako vzorec Tyto specifikátory nejde kombinovat s žádným jiným specifikátorem zvláštních položek. Můžete pomocí nich vynutit implicitní chování průniku pro odkaz a přepsat implicitní chování průniku a odkazovat na jednotlivé hodnoty ze sloupce.

V tabulkách, které mají víc než jeden řádek dat, Excel automaticky mění specifikátor #This Row na krátký specifikátor @. Pokud má ale vaše tabulka jenom jeden řádek, Excel specifikátor #This Row nebude nahrazovat, což může mít v případě, že přidáte další řádky, za následek neočekávané výsledky výpočtů. Pokud chcete problémům s výpočty předejít, před zadáním jakýchkoli vzorců se strukturovaným odkazem vložte do tabulky několik řádků.

Kvalifikování strukturovaných odkazů v počítaných sloupcích

U počítaného sloupce budete často používat k vytvoření vzorce strukturovaný odkaz. Tento strukturovaný odkaz může být nekvalifikovaný nebo plně kvalifikovaný. Pokud chcete vytvořit třeba výpočtový sloupec s názvem Provize-částka, ve kterém se bude vypočítávat částka provize v dolarech, můžete použít následující vzorce:

Typ strukturovaného odkazu

Příklad

Komentář

Nekvalifikovaný

=[Prodej-částka]*[Provize-%]

Vynásobí odpovídající hodnoty z aktuálního řádku.

Plně kvalifikovaný

=Oddělení-prodej[Prodej-částka]*Oddělení-prodej[Provize-%]

Vynásobí odpovídající hodnoty pro každý řádek pro oba sloupce.

Obecně platí toto pravidlo: Pokud v rámci tabulky používáte strukturované odkazy, třeba při vytváření výpočtového sloupce, můžete použít nekvalifikovaný strukturovaný odkaz. Pokud ale strukturovaný odkaz použijete mimo tabulku, musíte použít plně kvalifikovaný strukturovaný odkaz.

Příklady použití strukturovaných odkazů

Následuje několik příkladů použití strukturovaných odkazů.

Strukturovaný odkaz

Odkazovaná položka

Odpovídající oblast buněk:

=Oddělení-prodej[[#All],[Prodej-částka]]

Všechny buňky ve sloupci Prodej-částka

C1:C8

=Oddělení-prodej[[#Headers],[Provize-%]]

Záhlaví sloupce Provize-%

D1

=Oddělení-prodej[[#Totals],[Oblast]]

Součet sloupce Oblast. Pokud neexistuje řádek součtů, vrátí hodnotu Null.

B8

=Oddělení-prodej[[#All],[Prodej-částka]:[Provize-%]]

Všechny buňky ve sloupcích Prodej-částka a Provize-%

C1:D8

=Oddělení-prodej[[#Data],[Provize-%]:[Provize-částka]]

Jenom data ze sloupců Provize-% a Provize-částka

D2:E7

=Oddělení-prodej[[#Headers],[Oblast]:[Provize-částka]]

Jenom záhlaví sloupců mezi buňkami Oblast a Provize-částka

B1:E1

=Oddělení-prodej[[#Totals],[Prodej-částka]:[Provize-částka]]

Součty sloupců Prodej-částka až Provize-částka. Pokud neexistuje řádek součtů, vrátí hodnotu Null.

C8:E8

=Oddělení-prodej[[#Headers],[#Data],[Provize-%]]

Jenom záhlaví a data sloupce Provize-%

D1:D7

=Oddělení-prodej[[#This Row], [Provize-částka]]

nebo

=Oddělení-prodej[@Provize-částka]

Buňka na průsečíku aktuálního řádku a sloupce Provize-částka. Při použití do stejného řádku jako záhlaví a řádku souhrnů, to se vraťte se #VALUE! chyby.

Pokud zadáte delší podobu tohoto strukturovaného odkazu (#This Row) do tabulky s víc řádky dat, Excel ji automaticky nahradí kratší podobou (@). Obě fungují stejně.

E5 (pokud je aktuální řádek 5)

Strategie pro práci se strukturovanými odkazy

Při práci se strukturovanými odkazy je potřeba zvážit následující:

  • Použití automatického dokončování vzorce    Můžete zjistit pomocí funkce Automatické dokončování vzorce je velmi užitečná při zadávání strukturovaných odkazů a k zajištění použití správné syntaxe. Další informace najdete v tématu Použití funkce Automatické dokončování vzorce.

  • Rozhodněte se, zda generovat strukturované odkazy pro tabulky v polovičním výběru    Ve výchozím nastavení při vytváření vzorce, kliknutím na buňku v tabulce polovičním vybere oblasti buněk a automaticky vloží strukturovaného odkazu místo na oblast buněk ve vzorci. Toto chování polovičním výběru usnadňuje mnohem zadejte odkaz na strukturovaného. Chcete-li toto chování nebo vypnout zaškrtnutím nebo zrušením zaškrtnutí políčka použít názvy tabulek ve vzorcích v souboru > Možnosti > vzorce > Práce se vzorci dialogového okna.

  • Použití sešitů obsahujících externí odkazy na Excelové tabulky v jiných sešitech    Pokud sešit obsahuje externí odkaz na tabulku aplikace Excel v jiném sešitu, musí být tento propojené zdrojový sešit otevřít v aplikaci Excel a vyhněte se #REF! chyb v cílovém sešitu, který obsahuje propojení. Pokud nejprve otevřete cílový sešit a #REF! chyb se zobrazí, budou přeložena, pak otevřete zdrojový sešit. Pokud nejprve otevřete zdrojový sešit byste měli vidět žádné kódy chyb.

  • Převedení rozsahu na tabulku a tabulky rozsah    Po převedení tabulky na oblast všechny odkazy na buňky změnit jejich odpovídající absolutní odkazy ve stylu A1. Převést oblast na tabulku aplikace Excel všechny odkazy na buňky z této oblasti automaticky nezmění k jejich odpovídající strukturované odkazy.

  • Vypnutí záhlaví sloupců    Přepnout záhlaví sloupců tabulky a vypnutí z karty Návrh tabulky > Řádek záhlaví. Vypnutí záhlaví sloupců tabulky nemá vliv strukturované odkazy, které používají názvy sloupců a můžete dál používat ve vzorcích. Strukturované odkazy, které přímo odkazují na záhlaví (například = Oddělení-prodej [[#Headers], [Provize-%]]) bude mít za následek #REF.

  • Přidání nebo odstranění sloupců a řádků v tabulce    Protože rozsahy dat tabulky často mění, odkazy na buňky pro strukturované odkazy se automaticky upraví. Například pokud používáte název tabulky ve vzorci počítat všechny buňky dat v tabulce a pak přidat řádek dat, odkaz na buňku automaticky upraví.

  • Přejmenování tabulky nebo sloupce    Pokud přejmenujete sloupec nebo tabulku, aplikace Excel automaticky změní použití záhlaví sloupce a tabulky ve všech strukturovaných odkazech, které se v sešitě používají.

  • Přesunout, zkopírovat a vyplnění strukturovaných odkazů    Všechny strukturované odkazy, zůstávají stejné při kopírování nebo přesunutí vzorce používajícího strukturovaného odkazu.

    Poznámka : Kopírování strukturovaného odkazu a udělat výplň strukturovaného odkazu není totéž. Když zkopírujete, všech strukturovaných odkazech, zůstane, při při vyplnit vzorcem, plně kvalifikovaný strukturované odkazy nastavit specifikátory sloupců jako řady popsaná v následující tabulce.

Směr vyplňování

Klávesa stisknutá během vyplňování :

Výsledek

Nahoru nebo dolů

Žádná

Nedojde k žádné úpravě specifikátoru sloupce.

Nahoru nebo dolů

Ctrl

Specifikátory sloupců jsou upraveny jako řady.

Doprava nebo doleva

Žádné

Specifikátory sloupců jsou upraveny jako řady.

Nahoru, dolů, doprava nebo doleva

SHIFT

Místo přepisování hodnot v aktuálních buňkách jsou hodnoty aktuálních buněk přesunuté a jsou vložené specifikátory sloupců.

Potřebujete další pomoc?

Kdykoli se můžete zeptat některého odborníka v technické komunitě Excelu, získat podporu v komunitě pro odpovědi, případně navrhnout novou funkci nebo vylepšení ve fóru Excel User Voice.

Příbuzná témata

Základní informace o tabulkách aplikace Excel
Video: vytvoření a formátování tabulky aplikace Excel
součty dat v tabulce aplikace Excel
formátování Excelové tabulky
Změna velikosti tabulky přidáním nebo odebráním řádků a sloupců
Filtrování dat v oblasti nebo tabulce
Převést tabulku na oblast
problémy s kompatibilitou tabulek Excelu
Export Excelové tabulky do služby SharePoint
chcete získat přehled vzorců v Excel

Poznámka : Upozornění ke strojovému překladu: Tento článek přeložil počítačový systém bez zásahu člověka. Společnost Microsoft nabízí tyto strojové překlady proto, aby umožnila uživatelům, kteří nemluví anglicky, získat informace o produktech, službách a technologiích této společnosti. Protože je tento článek strojově přeložený, může obsahovat slovní, syntaktické nebo gramatické chyby.

Rozšiřte své znalosti a dovednosti
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor. Vypadá to, že bude užitečné, když vás spojíme s některým z našich agentů z podpory Office.

×