|
Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:
Zpřehledněte si vzorce v Excelu. Využitím definovaných názvů.
Doplněno: 5.2.2019
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.
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.
Pro přidělování názvů oblasti v Excelu existuje pár pravidel:
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.
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:
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:
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:
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.
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
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 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.
Soubor
definovaný název - praktické ukázky - Excel
ukázkový soubor ke stažení zdarma. Pro Excel verze 2007 a novější.
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
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
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]]
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
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.
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ů.
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í".
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.
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
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/
Přidáno: 03.04.13 16:33
Parada, to je presne ono! diky
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.
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ů.
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.
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/.
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ěď.
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
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
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 :-)
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/
Přidáno: 03.09.14 14:30
To Petr Pecháček: ...inšpirácia je pre čitateľov, nie pre administrátora ...
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.
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
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 :-)
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?
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??
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.
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í.
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
LinkedIn... |
Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2021 |