V dnešním videu si ukážeme, co jsou to definované názvy v Excelu, někdy rovněž nazývané jako pojmenované oblasti nebo v angličtině tzv. named ranges. Zároveň si ukážeme, jak pracovat se Správcem názvů v Excelu. Definovat název můžeme v Excelu buď pro jednu buňku nebo pro celou oblast buněk. Definované názvy mají mnoho využití, mimo jiné dělají excelové vzorce a funkce čitelnějšími a umožňují tak lehčí navigaci v Excelu. Jak definované názvy využít v praxi si ukážeme v dnešním videu.
Excelový soubor ke stažení
Definované názvy v Excelu | Pojmenované oblasti
Definovaný název je buňka nebo oblast buněk, kterým dáme speciální jméno. Máme několik možností, jak můžeme pojmenovanou oblast vytvořit. Nejjednodušší způsob je, že označíme buňku nebo skupinu buněk, kterým chceme název přiřadit, a do pole názvů napíšeme jméno, které chceme buňkám dát. Oblast v ukázce pojmenujeme třeba Data. Název potvrdíme klávesou ENTER. Pokud teď označíme hodnoty, tak se místo klasického rozpětí buněk objeví v poli názvů jméno, které jsme buňkám přiřadili.
Můžeme tak klidně napsat funkci SUMA, ve které chceme hodnoty sečíst a pokud označíme hodnoty, kterým jsme dali název Data, tak se ve funkci SUMA neobjeví rozpětí buněk, ale název Data. Stejně tak můžeme do funkce SUMA rovnou pojmenovanou oblast napsat.
Pokud zapomeneme, jak jsme danou oblast pojmenovali, tak si můžeme pomoci klávesou F3, která vyvolá okno oblastí, kde dostaneme na výběr ze všech pojmenovaných oblastí, které v sešitu máme.
Druhou možností, jak pojmenovanou oblast vytvořit je pomocí správce názvů na kartě Vzorce. Vedle máme další hodnoty, pro které chceme vytvořit název. Hodnoty opět označíme a na kartě Vzorce najdeme Správce názvů. Klikneme na Správce názvů a ve vyskakovacím okně vidíme první pojmenovanou oblast, kterou jsme vytvořili. Pro vytvoření nové oblasti vybereme Nový název. V oblasti máme označené buňky, pro které definujeme název. Do pole název napíšeme zvolený název buněk. Tentokrát použijeme název Hodnoty. Potvrdíme.
Když teď klikneme do pole odkaz na název Hodnoty, tak se nám označí oblast, pro kterou patří vybrané jméno. Potvrdíme a máme v Excelu vytvořené dvě pojmenované oblasti.
Jednou z výhod práce s pojmenovanými oblastmi je to, že můžete mezi nimi lehce přeskakovat, a to pomocí pole názvů. Rozklikneme pole názvů a zde se nám zobrazí seznam definovaných názvů. Vybereme název Data a Excel nám okamžitě označí oblast buněk, která spadá pod název Data. Rozklikneme pole znovu a vybereme Hodnoty. Teď se nám označila oblast pro Hodnoty.
Pojmenování buněk
Jedním ze způsobů, jak můžete pojmenované oblasti v Excelu použít je nahradit odkazy na buňky názvy. Zejména složitější vzorce to činí pro koncového uživatele daleko srozumitelnějšími. Řekněme, že chceme v dalším příkladu spočítat měsíční splátku hypotéky. K výpočtu nám stačí pouze tři údaje, a to je výše úroku, splatnost úvěru a výše úvěru. K výpočtu použijeme funkci PLATBA, anglicky funkce PMT. Místo odkazování se na jednotlivé buňky ve vzorci ovšem použijeme pojmenované oblasti. Nejprve označíme buňku s úrokem a v poli pro pojmenování napíšeme Úrok. Potvrdíme klávesou ENTER. Následně označíme buňku s dobou a pojmenujeme ji jako Splatnost a opět nezapomeneme potvrdit klávesou ENTER. Poslední buňkou je úvěr, takže označíme buňku a pojmenujeme ji jako Úvěr. Teď když bychom se někde vedle odkázali na jednu z těchto buněk, tak se místo jejich koordinace jako je B3 objeví název buňky. To teď využijeme ve funkci PLATBA. Napíšeme funkci. Ve funkci PLATBA je prvním parametrem úrok, můžeme kliknout klasicky na buňku s úrokem a místo koordinace se objeví název Úrok. Úrok nesmíme zapomenout vydělit 12, jelikož počítáme měsíční splátku. Druhým parametrem je doba splatnosti. Teď neoznačíme buňku napřímo, ale zkusíme napsat Splatnost, a vidíme, že se název v nabídce objevil, takže na něj můžeme kliknout a tím se přenese do vzorce. Opět musíme splatnost převést na měsíce, takže tuto hodnot vynásobíme 12. Posledním parametrem je výše úvěru, a jelikož nechceme splátku s mínusem, tak musíme napsat mínus a napíšeme úvěr a opět na nás vyskočí název buňky v nabídce. Funkci ukončíme a potvrdíme. Teď máme funkci PLATBA, kde máme místo jednotlivých buněk názvy oblastí. Jediný rozdíl je v tomto případě v tom, že se to některým uživatelům Excelu lépe čte a funkce je pro ně pochopitelnější, než když jsou v ní pouze odkazy na buňky.
Pojmenování oblastí buněk + vytvořit z výběru
V dalším příkladu máme tabulku s produkty a tržbami. Naším úkolem je sečíst tržby z tabulky pro vybraný měsíc a produkt. Místo označování buněk chceme použít názvy oblastí. Nemusíme ovšem manuálně označovat každou oblast zvlášť. Oblasti chceme pojmenovat podle názvů v záhlaví. V takovém případě stačí kliknout do tabulky, zmáčknout klávesovou kombinaci CTRL+A pro označení celé tabulky a na kartě Vzorce vybrat Vytvořit z výběru. Vyskočí na nás okno, které se ptá, jakou oblast chceme použít pro pojmenování. Chceme použít Horní řádek a potvrdíme. Teď když označíme například sloupec tržeb, tak v poli pro pojmenování vidíme, že je sloupec pojmenovaný jako Tržba.
Teď tyto pojmenované oblasti můžeme použít ve funkci SUMIFS, pomocí které sečteme tržby na základě více podmínek. Napíšeme funkci SUMIFS, kde nejprve označujeme, co chceme sčítat. Chceme sčítat tržby, takže napíšeme Tržba a potvrdíme výběr. Následuje první sloupec s kritérium, což je sloupec s produkty, takže napíšeme název oblasti Produkt, teď označíme kritérium, což je hledaný produkt. Následuje druhý sloupec s kritériem, což je sloupec Měsíc a jako poslední hledaný měsíc. Funkci ukončíme a potvrdíme. A máme sečtené tržby pomocí funkce, ve které jsme použili pojmenované oblasti.
Další z výhod definovaných názvů je to, že nemusíte řešit fixace buněk. Ze své podstaty mají pojmenované oblasti vždy plnou fixaci. Pokud teď tento vzorec stáhneme i pro druhý produkt o jeden řádek níže, tak se vzorec přenese a vrátí správný výsledek, a to i bez fixace sloupců.
Stejně tak můžeme použít existující názvy v následující tabulce, kde máme obchodníky a jejich tržby. Označíme tabulku, jména a tržby bez záhlaví tabulky, a na kartě Vzorce vybereme Vytvořit z výběru a zde vybereme pouze Levý sloupec.
Teď se jednotlivé tržby pojmenovali podle obchodníka a my tak můžeme vedle použít funkci SUMA, kde jen vybereme jména obchodníků, jejichž tržby chceme sečíst. Chceme sečíst tržby Petra, Hany a Pavla. Napíšeme jména do funkce a potvrdíme. Tržby se sečetli a pro každého je hned jasné, čí tržby se ve funkci SUMA sčítají.
Pravidla pro definované názvy
Základním pravidlem je, že definovaný název nesmí obsahovat mezery. Pokud bychom v názvu použili mezeru, tak na nás vyskočí upozornění. Místo mezery se tak nejčastěji používá podtržítko a nebo kombinace malých a velkých písmen. Název oblasti musí začínat písmenem, podtržítkem nebo obráceným lomítkem. Názvy oblastí nesmí být shodné s referencemi na buňky, nesmíte tedy použít název jako A1 nebo B2. Názvy oblastí nejsou citlivé na malá a velká písmena. Stejně tak se nesmí v jednom excelovém sešitu shodovat názvy oblastí. Musíte tedy vždy použít unikátní název.
Úprava definovaných názvů
Úpravu a správu definovaných názvů provádíte z karty Vzorce, na záložce Správce názvů. Když záložku rozklikneme, tak zde vidíme všechny názvy, které jsme vytvořili. Vidíme název, který jsme buňce nebo oblasti přiřadili, vidíme hodnotu v buňce a odkaz na buňku. Pokud zapomenete, kde název máte nebo budete chtít zkontrolovat rozsah, nebo pole upravit, tak kliknete dolů do řádku a tím se aktivně označí buňka nebo oblast na kterou se název odkazuje.
Rozbalovací seznam
Pojmenované oblasti se dají využít jako zdroj rozbalovacího seznamu. V následujících buňkách máme seznam produktů. Místo toho abychom ve zdroji rozbalovacího seznamu označovali buňky, tak označíme buňky a dáme jim název, třeba Seznam. Teď klikneme do buňky, kde chceme mít rozbalovací seznam, na kartě Data vybereme Ověření dat a vybereme Seznam. Zde místo zdroje použijeme název oblasti. Napíšeme rovná se a název Seznam. Potvrdíme vytvoření seznamu a do buňky se vložil rozbalovací seznam.
Na rozdíl od excelových tabulek nejsou pojmenované oblasti dynamické. Pokud tedy do tabulky přidáme nová data, tak se pojmenovaná oblast automaticky nerozšíří o nově přidaná data. Museli bychom se k pojmenované oblasti vrátit na kartě Vzorce a Správce názvů a upravit rozsah oblasti o další řádek. Po potvrzení by se rozbalovací seznam aktualizoval.
Pojmenování u listů
Názvy oblastí se vám mohou hodit například tehdy, pokud se často odkazujete na více listů a nechcete se neustále překlikávat z listu na list. V excelovém sešitu máme tři listy leden, únor a březen. Na těchto listech jsou tabulky. Jednotlivé tabulky si můžeme pojmenovat jako leden, únor a březen. Označíme tabulku a v buňce ji pojmenujeme jako Leden. Následně se překlikneme na list únor, označíme tabulku a pojmenujeme ji jako Únor. Ještě zbývá březen. Klikneme na list březen, označíme tabulku a pojmenujeme ji jako Březen. Teď když se překlikneme na list Přehled a rozklikneme si výběr v buňce, tak zde vidíme pojmenované tabulky, pokud vybereme například Březen, tak se okamžitě překlikneme na tabulku s březnem.
Řekněme, že teď chceme sečíst hodnoty z tabulek na listech leden, únor a březen. Vrátíme se k listům a postupně označíme pouze sloupec s hodnotami, který pojmenujeme jako Leden_tržba. V únoru uděláme to samé, označíme hodnoty a oblast pojmenujeme jako Únor_tržba. Nakonec ještě označíme hodnoty v březnu a pojmenujeme je jako Březen_tržba. Teď se vrátíme na list přehled, kde napíšeme SUMA, a postupně napíšeme leden, najdeme pojmenovanou oblast Leden_tržba a název vybereme, napíšeme středník a pokračujeme s únorovými tržbami, středník a nakonec březnové tržby. Ukončíme závorku a potvrdíme a funkce SUMA sečetla všechny hodnoty bez nutnosti se neustále překlikávat z listu na list.
Použít ve vzorci
Pokud byste v sešitu chtěli seznam všech pojmenovaných oblastí, tak můžete vybrat buňku, kam chcete seznam vložit a na kartě Vzorce vybereme Použít ve vzorci a sjedeme dolů a vybereme Vložit názvy.
Pro vložení všech názvů vybereme Vložit seznam.
Pojmenovaná buňka jako konstanta
Pojmenovanou oblast můžete použít i pro konstantu. Klikneme na kartu Vzorce a vybereme Správce názvů. Zde vybereme Nový název a jako název zvolíme Konstanta. Místo odkazu na buňku nebo buňky můžeme do odkazu napsat libovolné číslo, třeba 5,55. Potvrdíme a teď, kdykoliv budeme chtít počítat s tímto číslem, tak stačí napsat název, který jsme pro číslo zadali. Hodnoty třeba můžeme konstantou dělit. Rovná se, buňka, děleno a název pole tedy Konstanta. Potvrdíme a máme hodnoty v tabulce vydělené číslem 5,55.
Nahrazení oblastí ve vzorci
Pokud už máte existující vzorce s rozpětím buněk a chtěli byste je nahradit pojmenovanými oblastmi, tak to uděláte tak, že nejprve označíme vzorec, ve kterém chceme rozpětí buněk nahrazovat. Máme zde funkci SUMA, která sčítá hodnoty z tabulky. Ve vzorci SUMA máme rozpětí buněk C21 až C41. Mezitím jsme ovšem tuto oblast pojmenovali jako Tržby. Chceme tedy i toto rozpětí buněk nahradit názvem Tržby. Klikneme na vzorec a na kartě Vzorce najdeme Definovat název a zde vybereme Použít názvy. Zde mezi názvy vybereme správnou oblast a potvrdíme. Rozpětí buněk se nahradilo pojmenovanou oblastí.
Jedna odpověď
😊👍