Definované názvy - pojmenováné oblasti - Excel

Úvod do definovaných názvu

Pro zpřehlednění práce v Excelu, můžete buňkám dávat specifické názvy (třeba FANTOMAS ;). Jaké to má výhody, jak tyto definované názvy použít se dozvíte v tomto článku.

Pro přehlednost je článek rozdělen do několika kapitol.

Kapitoly

• Proč Definované názvy
• Pravidla pro přidělování názvu - třeba název A1 nelze použít ;)
• Postřehy pro názvy - jak si ještě více ulehčit práci
• Vložit název definované oblastí
• Seznam názvu oblastí
• Úprava definovaného názvu
• Smazání definovaného názvu
• Odkazování na definovaný název - hlavně pokud jde o oblast buněk
• Dynamický definovaný název - pokud se velikost oblasti mění...
• Definovaný název pomocí VBA
• Smíšené odkazování v definovaném názvu - není tak jednoduché jak si myslíte ;)
• Ke stažení - hotové řešení

Proč Definované názvy (pojmenování oblasti)

Microsoft Excel umožňuje přiřadit buňce nebo oblasti buněk jméno. Místo odkazování se na buňku pomocí odkazu =A1 se odkážete přímo na její název (který si můžete zvolit např. DPH). Poté použijete jako odkaz toto jméno =DPH.

Ze začátku může být pochopení složitější. Na druhou stranu pokud pochopíte smysl stane se pro vás tvorba excelovských dokumentů jednoduší a přehlednější.

Poznámka: Někde se můžete setkat místo s pojmem definované názvy s pojmem pojmenování oblasti.

Pravidla pro přidělování názvu

Pro přidělování názvů oblasti v Excelu existuje pár pravidel:

• Název nesmí obsahovat mezeru
• Název nesmí začínat číslem
• Název musí začínat písmenem, nebo podtržítkem
• Nelze používat symboly (kromě podtržítka)
• Max délka 255 znaků
• Lze použít i jedno písmeno (mimo C a R)
• Velikost písmen nerozhoduje
• Nesmí být shodný s názvem funkce

Doporučení pro definované názvy

Doporučuji si v názvech oblastí vytvořit systém. Pokud si předem neujasníte jak buňky pojmenovávat, můžete narazit na problém, kdy název oblastí bude podobný názvu nějaké funkce. Při psaní vzorce se překlepnete a Excel bude chybně počítat. Případně pokud se k tabulce (šabloně) vrátíte po roce a bude potřeba něco doplnit.

Budete-li si chtít názvy zpřehlednit můžete využít napříkald předpon pro konstantu DPH, constDPH, třeba vám bude vyhovovat. Osobně jsem nepoužíval u jednoduchých tabulek, kde bylo pár definovaných názvu, ale se snažím používat i pro jednoduché tabulky.

Za doplnění v komentářích děkuji Petr Pecháček a doporučuji jeho web excelplus.net , kde naleznete také mnoh zajímavých informací k Excel.

Vložit název definované oblastí

Z karty Vzorce skupina Definované názvy klikneme na Definovat název.

Obdržíme okno: Nový název

V tomto okně již můžeme zadat požadované údaje:

• Název: - požadovaný název respektující pravidla
• Obor: - zda je platný pro celý sešit nebo jen daný list
• Komentář: - poznámka...
• Odkaz na: - která buňka nebo oblast buněk bude mít výše uvedený název.

Seznam názvu oblastí

Ať víte jaké názvy mate použity a pro které buňky (oblasti buněk platí).

Z karty Vzorce skupina Definované názvy klikneme na Správce názvu.

V zobrazeném okně Správce názvu můžeme:

• Nový... - vloží nový definovaný název
• Upravit... - upraví vybraný název
• Odstranit - odstraní vybraný název
• Filtr - v případě více nazvu, lze názvy podle vybraných kritérií filtrovat a najít požadovaný název.

Úprava definovaného názvu

Z karty Vzorce skupina Definované názvy klikneme na Správce názvu. V zobrazeném okně vybereme název, který chceme smazat a klikneme na tlačítko Upravit...

V zobrazeném okně Upravit název můžeme změnit:

• Název:
• Obor:
• Komentář:
• Odkaz na:

Smazání definovaného názvu (oblastí)

Z karty Vzorce skupina Definované názvy klikneme na Správce názvu. V zobrazeném okně vybereme název, který chceme smazat a klikneme na tlačítko Odstranit.

A svou volbu potvrdíme.

Případně v listu odstraníme řádek/ sloupec ve kterém se název nachází.

Poznámka: Při mazání dojde k tomu, že se název smaže, ale odkaz ve vzorcích zůstane, tím pádem bude jejich výpočet chybný. Podobně pokud smažete sloupec (řádek) obsahující pojmenované oblasti. O tyto názvy přijdete.

Odkazování na definovaný název

Pokud je pojmenována jedna buňka odkáže se jednoduše rovná se a její název (např. = constDPH).

Praktická ukázka v kapitole ke stažení.

Pokud máte definovanou název pro oblast buněk a chcete se na ní odkázat musíte použít maticový zápis. Označíte stejně velkou oblast (kam se má přenést) a použijete klávesovou zkratku Ctrl+Shift+Enter, která provede zápis maticového vzorce. Více o maticích v samostatném článku matice úvod, kde se dozvíte více o tom jak funguje maticový počet v Microsoft Excelu.

Doplněno na základě dotazů čtenářů. Díky Filipe

Dynamický definovaný název

Excel umožňuje vytvářet i dynamické definované oblasti. Tj. daná oblast mění svou velikost dle aktuální situace. Jak vytvořit dynamickou oblast a jak jí využít v článcích:

• Jak na dynamicky definované jméno oblasti v Microsoft Excelu.
• Dynamicky definovaný graf v Excel - jak vytvářet dynamické grafy

Definovaný název pomocí VBA

Jak na definovaný název v aktivním sešitě, buňka B2. V koemnáři bude: Komentář k názvu.

ActiveWorkbook.Names.Add Name:="Pokus", RefersToR1C1:="=List1!R2C2" ActiveWorkbook.Names("Pokus").Comment = "Komentář k názvu"

Poznámka: Jde u základy, podrobněji se budu zabývat vkládáním pomocí VBA zabývat v samostatném článku v sekci o VBA.

Ke stažení

Soubor definovaný název - praktické ukázky - Excel ukázkový soubor ke stažení zdarma. Pro Excel verze 2007 a novější.

Smíšené odkazování v definovaném názvu

Jak se smíšeně v definovaném názvu, pokud například využívate v SVYHLEDAT, SUMIFS atd.

Mějme tabulku s definovaným názvem Osoby

Odkazování

Data v tabulce

=Osoby

Celá tabulka

=Osoby[#Vše]

Řádek záhlaví

=Osoby[#Záhlaví]

Položka záhlaví

=Osoby[[#Záhlaví];[Jméno]] celé záhlaví =Osoby[[#Záhlaví];[Příjmení]:[Věk]] pokud je část záhlaví

Položka dat v řádku

=[@Jméno] =Osoby[@Plat] =Osoby[@[Příjmení]:[Pohlaví]] Část dat v řádku

Sloupec dat dynamicky relativně

=Osoby[Jméno] kopírováním se změní název [Plat] pod sloupcem

Sloupec dat (jméno) zamknut nad sloupcem jméno:

=Osoby[[Jméno]:[Jméno]] kopírováním se nezmění název sloupce

Ukázka

Využití v SVYHLEDAT

Smíšený odkaz mezi dvěma tabulkami, nadefinovaními jako Tabulky.

=SVYHLEDAT(Faktura[[Id]:[Id]];Sluzby;POZVYHLEDAT(Faktura[[#Záhlaví];[Název]];Sluzby[#Záhlaví];0);NEPRAVDA)

aneb je potřeva "zamknou" sloupec, kdy Faktura je název tabulky a id název sloupce.

Faktura[[Id]:[Id]]

Závěrem

Pokud jsem něco o definovaných názvech neřekl? Můžete doplnit v komentářích.

Článek byl aktualizován: 19.09.2020 10:57

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.