Když vytvoříte excelovou tabulku, Přiřadí excel tabulce název a každému záhlaví sloupců v tabulce. Když do excelové tabulky přidáte vzorce, můžou se tyto názvy zobrazit automaticky, když zadáte vzorec a vyberete odkazy na buňky v tabulce, a nemusíte je zadávat ručně. Tady je příklad toho, co Excel dělá:
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. Pojďme použít následující ukázková data k zadání vzorce, který automaticky použije strukturované odkazy k výpočtu výše provize z prodeje.
Prodejce |
Oblast |
Prodej-částka |
Provize-% |
Částka provize |
---|---|---|---|---|
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 % |
-
Zkopírujte ukázková data z výše uvedené tabulky včetně záhlaví sloupců a vložte je do buňky A1 nového excelového listu.
-
Tabulku vytvoříte tak, že vyberete libovolnou buňku v oblasti dat a stisknete Ctrl+T.
-
Ujistěte se, že je zaškrtnuté políčko Tabulka obsahuje záhlaví , a klikněte na OK.
-
Do buňky E2 zadejte rovnítko (=) a klikněte na buňku C2.
V řádku vzorců se za rovnítkem zobrazí strukturovaný odkaz [@[Prodej-částka]].
-
Přímo za pravou 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-%]].
-
Stiskněte 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á.
-
V ukázkovém listu klikněte na buňku E2.
-
Na řádku vzorců zadejte =C2*D2 a stiskněte 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ší.
-
Vyberte libovolnou buňku v tabulce, aby se na pásu karet zobrazila karta Nástroje tabulky > Návrh .
-
Do pole Název tabulky zadejte požadovaný název a stiskněte Enter.
V příkladu jsme použili název Oddělení-prodej.
Pro názvy tabulek použijte následující pravidla:
-
Použití platných znaků Název vždy začněte písmenem, podtržítkem (_) nebo zpětným lomítkem (\). Zbývajícími znaky názvu můžou být písmena, čísla, tečky a podtržítka. Jako název nemůžete použít „C“, „c“, „R“ nebo „r“, protože tato písmena jsou vyhrazená jako zkratky pro výběr sloupce nebo řádku aktivní buňky, pokud je zadáváte do pole Název nebo Přejít na.
-
Nepoužívat odkazy na buňky Názvy nemůžou být stejné jako odkaz na buňku, například Z$100 nebo R1C1.
-
Nepoužívejte mezeru k oddělení slov. V názvu nelze použít mezery. Můžete použít podtržítko (_) a tečku (.) jako oddělovače slov. Například Oddělení prodejů, Sales_Tax nebo First.Quarter.
-
Nepoužívejte více než 255 znaků. Název tabulky může mít až 255 znaků.
-
Použití jedinečných názvů tabulek Duplicitní názvy nejsou povolené. Excel nerozlišuje velká a malá písmena v názvech, takže pokud zadáte "Prodej", ale ve stejném sešitu už máte jiný název s názvem "PRODEJ", zobrazí se výzva k výběru jedinečného názvu.
-
Použití identifikátoru objektu Pokud plánujete kombinaci tabulek, kontingenčních tabulek a grafů, je vhodné před názvy zadat předponu typu objektu. Například: tbl_Sales pro tabulku prodejů, pt_Sales pro kontingenční tabulku prodejů a chrt_Sales pro graf prodeje nebo ptchrt_Sales pro kontingenční graf prodeje. Tím se všechna vaše jména budou ve Správci názvů zobrazovat v seřazených názvech.
Pravidla syntaxe strukturovaného odkazu
Strukturované odkazy můžete do vzorce zadat nebo změnit ručně, ale pomůže vám to pochopit syntaxi strukturovaného odkazu. Pojďme si projít 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íSales je vlastní název tabulky. Odkazuje na data tabulky bez použití záhlaví nebo řádků souhrnů. Můžete použít výchozí název tabulky, třeba Tabulka1, nebo název změnit na vlastní.
-
Specifikátor sloupce: [Sales Amount] a [Commission Amount] jsou specifikátory sloupců, které používají názvy sloupců, které představují. Odkazují na data sloupce bez záhlaví sloupce nebo řádku souhrnů. Specifikátory vždycky uvádějte v závorkách.
-
Specifikátor položky: [#Totals] a [#Data] jsou speciální specifikátory položek, které odkazují na konkrétní části tabulky, například na řádek souhrnů.
-
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é odkazy: (Oddělení prodej[[[#Totals],[Částka prodeje]] a Oddělení-prodej[[#Data],[Částka provize]] jsou strukturované odkazy reprezentované ř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:
-
Kolem specifikátorů používejte hranaté závorky Všechny specifikátory tabulek, sloupců a zvláštních položek musí být uzavřené do dvojice závorek ([ ]). V případě specifikátoru obsahujícího další specifikátory je potřeba použít dvojici vnějších závorek, do kterých je uzavřená dvojice vnitřních závorek těchto dalších specifikátorů. Příklad: =OdděleníSales[[Prodejce]:[Oblast]]
-
Všechna záhlaví sloupců jsou textové řetězce. Pokud se ale používají ve strukturovaném odkazu, nevyžadují uvozovky. Čísla nebo kalendářní data, například 2014 nebo 1.1.2014, se také považují za textové řetězce. Se záhlavími sloupců nemůžete používat výrazy. Například výraz 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
-
Podávání řádků
-
Návrat na začátek řádku
-
Čárka (,)
-
Dvojtečka (:)
-
. (tečka)
-
Levá hranatá závorka ([)
-
Pravá hranatá závorka (])
-
Znaménko libra (#)
-
Jednoduché uvozovky (')
-
Dvojité uvozovky (")
-
Levá složená závorka ({)
-
Pravá složená závorka (})
-
Znak dolaru ($)
-
Stříšku (^)
-
Ampersand (&)
-
Hvězdička (*)
-
Znaménko plus (+)
-
Rovnítko (=)
-
Znaménko minus (-)
-
Symbol větší než (>)
-
Symbol menší než (<)
-
Znaménko dělení (/)
-
Zavináč (@)
-
Zpětné lomítko (\)
-
Vykřičník (!)
-
Levá závorka (()
-
Pravá závorka ())
-
Znak procenta (%)
-
? (otazník)
-
Backtick (')
-
Středník (;)
-
Vlnovka (~)
-
Podtržítko (_)
-
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é ve vzorci potřebují řídicí znak ('):
-
Levá hranatá závorka ([)
-
Pravá hranatá závorka (])
-
Znak libry(#)
-
Jednoduché uvozovky (')
-
Zavináč (@)
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:
-
Za první levou závorku ([)
-
Před poslední pravou 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 nelze kombinovat s žádnými jinými specifikátory speciálních položek. Použijte je k vynucení implicitního chování průniku pro odkaz nebo k přepsání implicitního chování průniku a odkazování 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 například chcete vytvořit počítaný sloupec s názvem Provize Částka, který vypočítá výši 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 v průsečíku aktuálního řádku a sloupce Provize Částka Pokud se použije ve stejném řádku jako záhlaví nebo řádek souhrnů, vrátí se chyba #VALUE! . 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 vezměte v úvahu následující skutečnosti.
-
Použití funkce Automatické dokončování vzorce Funkce automatického dokončování vzorců by mohla být 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í automatického dokončování vzorců.
-
Rozhodnutí, jestli se mají generovat strukturované odkazy pro tabulky v polovičních výběrech Když vytvoříte vzorec, ve výchozím nastavení se po kliknutí na oblast buněk v tabulce částečně vyberou buňky a automaticky se místo oblasti buněk ve vzorci zadají strukturované odkazy. Toto chování (poloviční výběr) do značné míry usnadňuje zadání strukturovaného odkazu. Toto chování můžete zapnout nebo vypnout zaškrtnutím nebo zrušením zaškrtnutí nebo zrušením zaškrtnutí políčka Používat názvy tabulek ve vzorcích v dialogovém okně Možnosti > > Vzorce > Práce se vzorci.
-
Použití sešitů s externími odkazy na excelové tabulky v jiných sešitech Pokud sešit obsahuje externí odkaz na excelovou tabulku v jiném sešitu, musí být tento propojený zdrojový sešit otevřený v Aplikaci Excel, aby se zabránilo chybám #REF! v cílovém sešitu, který obsahuje odkazy. Pokud nejprve otevřete cílový sešit a zobrazí se #REF! chyby, budou vyřešeny, pokud pak otevřete zdrojový sešit. Pokud nejprve otevřete zdrojový sešit, neměly by se zobrazit žádné kódy chyb.
-
Převedení rozsahu na tabulku a tabulky na rozsah Při převodu tabulky na oblast se všechny odkazy na buňky změní na ekvivalentní absolutní odkazy ve stylu A1. Při převodu oblasti na tabulku Excel automaticky nezmění žádné odkazy na buňky této oblasti na ekvivalentní strukturované odkazy.
-
Vypnutí záhlaví sloupců Záhlaví sloupců tabulky můžete zapnout nebo vypnout na kartě Návrh tabulky > Řádek záhlaví. Pokud záhlaví sloupců tabulky vypnete, nebudou strukturované odkazy, které používají názvy sloupců, ovlivněny a můžete je dál používat ve vzorcích. Strukturované odkazy, které odkazují přímo na záhlaví tabulky (např. =OdděleníSales[[#Headers],[%Commission]]), budou mít za následek #REF.
-
Přidání nebo odstranění sloupců a řádků v tabulce Vzhledem k tomu, že se oblasti dat tabulky často mění, odkazy na buňky pro strukturované odkazy se automaticky upraví. Pokud třeba použijete název tabulky ve vzorci pro výpočet všech datových buněk v tabulce a přidáte řádek dat, odkaz na buňku se 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řesouvání, kopírování a vyplňování strukturovaných odkazů Při kopírování nebo přesouvání vzorce, který používá strukturovaný odkaz, zůstanou všechny strukturované odkazy stejné.
Poznámka: Kopírování strukturovaného odkazu a vyplnění strukturovaného odkazu není totéž. Při kopírování zůstanou všechny strukturované odkazy stejné, zatímco při vyplňování vzorce upraví plně kvalifikované strukturované odkazy specifikátory sloupců jako řady, jak je shrnuto 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 odborníka z komunity Excel Tech nebo získat podporu v komunitách.
Příbuzná témata
Přehled excelových tabulek Video: Vytvoření a formátování excelové tabulky Souhrn dat v excelové tabulce 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řevod tabulky na oblast Problémy s kompatibilitou excelových tabulekExport excelové tabulky do SharePointuPřehledy vzorců v Excelu