Jste zde: Úvodní stránka » excel » funkce » definovane-nazvy

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

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak na Excel - naučte se efektivně využívat Excel - nejoblíbenější (Hodnocení 97%, přes 13.000 studentů, + 750 pozitivních zpětných vazeb) - Získat kurz
MaxiKurz Excel - získejte lepší práci, více peněz, staňte se nepostradatelní (přes 6 hodin videí, 160 lekcí) - Získat kurz
G tabulky... - pokud nemáte rádi Excel využijte kurz na Google-tabulky

Zpřehledněte si vzorce v Excelu. Využitím definovaných názvů.

Doplněno: 5.2.2019

Ú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

Zjednodušte si následné hledání při doplňování vašich starších šablon.

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

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). 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 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

Komentáře

Petr

Přidáno: 11.12.12 10:06

"Doporučuji si v názvech funkcí utvořit nějaký systém." Předpokládám, že jde o systém v názvech "oblastí".

Pavel Lasák

Přidáno: 11.12.12 16:45

To Petr: Přesně tak opraveno, aby nemátlo další čtenáře. Děkuji za postřeh.

Filip

Přidáno: 03.04.13 14:13

Dobry den, prosim, poradite mi, jak je mozne se odkazovat na pojmenovanou oblast bunek z jineho listu? Dejme tomu, ze na jednom listu mam oblast bunek nazvanou oblastA a potrebuju hodnoty z teto oblasti zobrazit pro dalsi pouziti na jinem listu. Pocet bunek ve zdrojove i cilove oblasti bunek je stejny. Jde mi o to, abych po uprave hodnoty jakekoli bunky v oblastiA nemusel nasledne rucne vsude upravovat tuto hodnotu. Dekuju za odpoved

Pavel Lasák

Přidáno: 03.04.13 16:19

To Filip: Zadat jako maticový vzorec. Ctrl+Shift+Entr Vice o maticových vzorcích http://office.lasakovi.com/excel/matice/ms-excel-matice-uvod/

Filip

Přidáno: 03.04.13 16:33

Parada, to je presne ono! diky

Pavel Lasák

Přidáno: 03.04.13 20:31

To Filip: Rádo se stalo, doplnil jsem do článku, ať poslouží dalším čtenářům.

Petr Pecháček

Přidáno: 07.04.13 12:58

Pár poznámek: V tuto chvíli jsem pro pojem "vlastní názvy". Po vlastním názvem je možné mít vzorec, odkazovat se přímo na buňku/oblast (vzorcem i dynamicky podle počtu hodnot), mít přímo uloženou hodnotu (řetězec, číslo) nebo i celou dvourozměrnou tabulku (maticovou konstantu). Vlastní názvy podporují i ne úplně mrtvé Excel 4 Macro funkce. Vlastní názvy mají svůj obor platnosti/dostupnosti (list, sešit) a jsou problematické při kopírování listů.

Petr Pecháček

Přidáno: 07.04.13 13:10

K těm názvům - stejně jako by měly mít předpony názvy objektů ve VBA, tak podobně by tomu mělo být i u vlastních názvů - pro objekty typu Shapes (shpBublina), oblasti buněk Range (rngVstupniOblast), pro řetězce String (strNazevFirmy), konstanty (constDPH). To je jen návrh, neexistují doporučení, ale je dobré mít v tom řád.

Pavel Lasák

Přidáno: 07.04.13 17:37

To Petr Pecháček: Děkuji za perfektní doplnění. Při psaní článku přemýšlím nad správným pojmenování (což ne vždy je úplně jednoduché). Jednak nějaké názvy používá Microsoft, nějaké názvy používá člověk co to hledá a nějaký název pro pojmenování by byl vhodnější (lépe by charakterizoval).
Děkuji zkusím do článku doplnit.

Jinak skvělý web o Excelu v češtině je http://excelplus.net/.

Petr F.

Přidáno: 02.12.13 11:08

Potřeboval bych definovat pojmenovanou oblast z jiného sešitu (souboru). Ručně (přes menu) to není problém, ale v kódu VBA? Předem díky za odpověď.

Veronika

Přidáno: 14.04.14 17:14

Dobrý den, Zajímalo by mě, jak je možné se "nepřímo" odkazovat na názvy oblastí např při použití funkce index. Př. Když chci za pomocí funkce index zobrazit určitou buňku v tabulce s definovaným názvem, stačí zadat tento název do parametru "pole" INDEX(definovaný_název, 1, 4), ale musí se to zadávat ručně. V případě kdy potřebuji tento název přečíst z jiné buňky, vzorec nefunguje, protože tato hodnota se převádí jako text v uvozovkách INDEX("definovaný_název", 1, 4) a v tomto případě se mi neodkáže na potřebnou tabulku. Je toto možné nějak obejít? Děkuji za odpověď, Veronika

zdeněk

Přidáno: 16.06.14 14:51

Lze propojit oblasti/tabulky o stejném počtu sloupců i označení na více listech ("třírozměrná oblast"), tak aby následně fce svyhledat vyhledávala na všech listech? Při všech mých pokusech fce pracuje pouze na prvním listě. Děkuji předem za info i Tvůj čas. Zdeněk

Milan

Přidáno: 01.08.14 12:47

Poraďte mi prosím ako upraviť vzorec v ktorom sa odkazujem na pomenovanú oblasť dát, ale excel to vyhodnocuje ako text: mám takúto funkciu: =INDEX("Vyber_"& TEXT(RokSprávy;0);MATCH(DATEVALUE(TEXT(DeňSpráv y;0)&"/"&TEXT(ČísloMesiaca;0)&"/"&TEXT(RokSprávy;0));Dni!$A:$A;0);3) Excel ju vyhodnotí takto: INDEX("výber_2014";1;3) A tu je problém, potrebujem aby výraz "vyber_2014" bral ako premennú a nie ako text, lebo je to oblasť dát z ktorej vyberám údaje. ďakujem :-)

Jojo

Přidáno: 02.09.14 14:13

To Petr Pecháček (Přidáno: 07.04.13 13:10): -- Pre inšpiráciu: -- http://excelplus.net/madarska-notace-nazvoslovi-ve-vba/

Jojo

Přidáno: 03.09.14 14:30

To Petr Pecháček: ...inšpirácia je pre čitateľov, nie pre administrátora ...

Petr Pecháček

Přidáno: 17.11.14 22:21

ad Petr F. - Nerozumím požadavku. ad Veronika - Také moc nerozumím. Funguje jak =INDEX(MojeTabulka;1;4), tak =INDEX(NEPŘÍMÝ.ODKAZ(A1);1;4), kde A1 obsahuje prostý text MojeTabulka.

Petr Pecháček

Přidáno: 17.11.14 22:34

ad Zdeněk: Pokud nevadí angličtina (tady se špatně popisuje, pak http://www.xl-central.com/lookup-single-criteria-multiple-sheets.html nebo http://superuser.com/questions/437419/apply-vlookup-formula-to-multiple-sheets

Petr Pecháček

Přidáno: 17.11.14 22:37

ad Milan: Nejspíš i pro vás platí rada, abyste užil funkci NEPPŘÍMÝ.ODKAZ. ad Jojo: Bez komentáře :-)

Marian

Přidáno: 20.01.16 15:42

Dobrý deň. Píšete, že vlastne názvy sú problematické pri kopírovaní. Mám práve tento problém. Mám vytvorený hárok s dátami, vzorcami, pomenovanými vzorcami aj oblasťami, na ktoré sa odkazuje pri výpočte, plus graf. Potreboval by som vytvoriť kópiu hárku tak, aby tie odkazy odkazovali na pomenovanú oblasť/vzorec v novom hárku, nie v starom. Alebo sa v prípade takejto požiadavky treba úplne vyhnúť pomenovaným vzorcom a oblastiam?

Lenka

Přidáno: 04.02.16 10:50

Dobrý den, jak je možné definovat si v tabulce že např. A znamená auto, B motocykl atd a z písmen A,B je pak převést na názvy??

Lubek

Přidáno: 01.08.16 16:05

Mariane, názvy je pro tento účel třeba definovat ve správci názvů (ne v okénku adres oblastí nahoře v řádku vzorců pod pásem nabídek při práci na listu) a určit mu platnost (obor) pro daný list. Pak při kopírování listu (tažením záložky listu při stisknuté klávese Ctrl) se zkopírují také názvy, které budou mít platnosti pro vytvořenou kopii listu.

Lubek

Přidáno: 01.08.16 16:21

Lenko, je možné definovat prakticky jakýkoliv název "A" pro jakýkoliv text ve Správci názvů, např. pro slovo "automobil", kde při definování se zapíše vzorec ="automobil". Tabulku lze použít volbou z pásu karet Vzorce u správce názvů (skupina Definované názvy) volbou Vytvořit z výběru, kde se volí, které krajní řádky a sloupce (zpravidla horní a nejlevější) obsahují názvy. Takže do levého přijdou ty názvy (u jednopísmenových nelze užít C a R) a do pravého ta slova. A tuhle tabuličku vyznačíš a volíš na tom pásu karet. Ale nevzniknou tím (Definováním názvů podle nadpisů) přímo názvy pro slova, nýbrž názvy pro oblasti (tj. zde pro buňky), které ta slova obsahují.