Excel pojmenované oblasti – jak definovat a používat názvy v aplikaci Excel

tutorial vysvětluje, co aplikace Excel jméno je a ukazuje, jak definovat název buňky, rozsah, konstanta nebo vzorec. Dozvíte se také, jak upravovat, filtrovat a mazat definovaná jména v aplikaci Excel.

Názvy v Excelu jsou paradoxní věc: jsou jednou z nejužitečnějších funkcí, jsou často považovány za zbytečné nebo nerdy. Důvodem je, že jen velmi málo uživatelů chápe podstatu názvů aplikace Excel., Tento tutoriál vás nejen naučí, jak vytvořit pojmenovaný rozsah v aplikaci Excel, ale také ukáže, jak tuto funkci využít, aby vaše vzorce byly mnohem snazší psát, číst a znovu používat.,možnost

Co znamená název znamená v Excelu?,

v každodenním životě jsou jména široce používána k označení lidí, objektů a geografických míst. Například místo toho, abyste řekli „město ležící na zeměpisné šířce 40.7128 ° N A délce 74.0059 ° W, jednoduše řeknete „New York City“.

podobně v aplikaci Microsoft Excel můžete dát lidsky čitelné jméno jedné buňce nebo řadě buněk a odkazovat na tyto buňky spíše jménem než odkazem.,

například, najít celkových tržeb (B2:B10) pro konkrétní položky (E1), můžete použít následující vzorec:

=SUMIF($A$2:$A$10, $E$1, $B$2:$B$10)

Nebo můžete dát smysluplné názvy rozsahů a jednotlivých buněk a zásobování těchto názvů vzorce:

=SUMIF(items_list, item, sales)

při Pohledu na screenshot níže, které dva vzorce jsou jednodušší pro vás pochopit?,

Excel jméno typů

V aplikaci Microsoft Excel, můžete vytvořit a používat dva typy názvů:

Definovaný název – název, který odkazuje na jednu buňku, oblast buněk, konstantní hodnotu nebo vzorec. Například, když definujete název pro řadu buněk, nazývá se pojmenovaný rozsah nebo definovaný rozsah. Tato jména jsou předmětem dnešního tutoriálu.

název tabulky-název tabulky aplikace Excel, která se vytvoří automaticky při vložení tabulky do listu (Ctrl + T). Další informace o tabulkách aplikace Excel naleznete v části Jak vytvořit a používat tabulku v aplikaci Excel.,

jak vytvořit Excel s názvem range

celkově existují 3 způsoby, jak definovat název v aplikaci Excel: Name Box, Define Name button a Excel Name Manager.

Zadejte název do Pole Název

Název Pole v aplikaci Excel je nejrychlejší způsob, jak vytvořit pojmenovanou oblast:

1. Vyberte buňku nebo oblast buněk, kterou chcete pojmenovat.
2. do pole Název zadejte název.
3. stiskněte klávesu Enter.

Voila, nový Excel s názvem rozsah je vytvořen!,

Vytvořte název pomocí Definovat Název možnost

Další způsob, jak vytvořit pojmenovanou oblast v aplikaci Excel, je toto:

1. Vyberte buňku(y).
2. na kartě Vzorce Klikněte ve skupině definovat jména na tlačítko Definovat název.
3. v dialogovém okně Nový název zadejte tři věci:
   • do pole Název zadejte název rozsahu.
   • v rozevíracím seznamu rozsah nastavte rozsah názvu (ve výchozím nastavení sešit).
   • v poli odkazuje na zaškrtněte odkaz a v případě potřeby jej opravte.
4. klepnutím na tlačítko OK uložte změny a zavřete dialogové okno.,

ve Srovnání s předchozí metody, pomocí Definovat Název v aplikaci Excel trvá několik kliknutí navíc, ale to také nabízí několik dalších možností, jako je nastavení jméno je rozsah a přidávat komentář, který vysvětluje něco o jméno., Funkce definovat název aplikace Excel navíc umožňuje vytvořit název konstanty nebo vzorce.

vytvořte pojmenovaný rozsah pomocí Excel Name Manager

Správce jmen v Excelu se obvykle používá pro práci s existujícími názvy. Může vám však také pomoci vytvořit nové jméno. Zde je návod:

1. přejděte na kartu vzorce > definovaná skupina jmen, klikněte na Správce jmen. Nebo stačí stisknout Ctrl + F3 (můj preferovaný způsob).,
2. V levém horním rohu Název Manager dialogové okno, klepněte na tlačítko Nový…:
   
3. otevře se Nový Název dialogové okno, kde můžete nastavit název, jak bylo prokázáno v předchozí části.

Jak vytvořit Excel název pro konstantní

kromě pojmenovaných oblastí, Microsoft Excel umožňuje definovat jméno, aniž odkaz na buňku, která bude fungovat jako pojmenované konstanty. Chcete-li vytvořit takový název, použijte buď funkci Excel Define Name nebo Správce jmen, jak je vysvětleno výše.

A teď, tento název můžete použít kdekoli ve vašem vzorce převést USD na EUR:

jakmile se změn směnných kurzů, můžete aktualizovat hodnotu pouze v jednom centrálním místě, a všechny vaše vzorce bude přepočítána v jediném kroku!,

Jak definovat název pro vzorec

V stejným způsobem, můžete dát jméno na vzorce aplikace Excel, například, ten, který vrací počet neprázdných buněk ve sloupci A, kromě záhlaví řádku (-1):

=COUNTA(Sheet5!$A:$A)-1

Nyní, kdykoli budete chtít vědět, jak mnoho položek, které jsou ve sloupci A na Sheet5, a to včetně záhlaví sloupců, zadejte rovnítko následuje název vašeho vzorec v jakékoliv buňce, jako je tento: =Items_count

Jak název sloupce v aplikaci Excel (jména z výběru)

Pokud vaše údaje jsou uspořádány ve formě tabulky, můžete rychle vytvořit názvy pro každý sloupec a/nebo řádek na základě jejich etikety:

1. Vyberte celou tabulku včetně záhlaví řádků a sloupců.,
2. přejděte na kartu vzorce > Definujte skupinu jmen a klikněte na tlačítko Vytvořit z výběru. Nebo stiskněte klávesovou zkratku Ctrl + Shift + F3.
3. Ať tak či onak, otevře se dialogové okno Vytvořit jména z výběru. Vyberete sloupec nebo řádek s záhlaví, nebo obojí, a klepněte na tlačítko OK.,

V tomto příkladu, budeme mít záhlaví na horní řádek a levý sloupec, takže jsme vybrali tyto dvě možnosti:

Jako výsledek, aplikace Excel vytvoří 7 pojmenované oblasti, vyzvednutí názvy ze záhlaví automaticky:

• Jablka, Banány, Citrony a Pomeranče pro řádky, a
• Leden, Únor a Březen pro sloupce.

aplikace Excel dynamické pojmenované oblasti

Ve všech předchozích příkladech jsme se zabývali statickou pojmenované rozsahy, které se vždy vztahují ke stejné buňky, což znamená, že budete muset aktualizovat referenční rozmezí ručně, kdykoli budete chtít přidat nové údaje do pojmenované oblasti.

Pokud pracujete s rozšiřitelné datové sady, je logické vytvořit dynamicky pojmenovanou oblast, která pojme nově přidaná data automaticky.,

podrobné pokyny, jak vytvořit dynamické pojmenované oblasti v aplikaci Excel lze nalézt zde:

• aplikace Excel vzorec POSUN vytvořit dynamický rozsah
• INDEX vzorec vytvořit dynamický rozsah

aplikace Excel pojmenování pravidla

Při vytváření názvu v aplikaci Excel, existuje několik pravidel mít na paměti:

aplikace Excel název rozsah

pokud jde o Excel jména, rozsah je umístění nebo úroveň, ve které jméno je rozpoznán., To může být buď:

• Konkrétní list – místní listu úrovni
• Sešitu – na globální úrovni sešitu

List úrovni názvy

list-úroveň jméno je uznáván v rámci listu, kde se nachází. Pokud například vytvoříte pojmenovaný rozsah a nastavíte jeho rozsah na List1, bude rozpoznán pouze v Listu1.

Chcete-li použít název úrovně listu v jiném listu, musíte prefixovat název listu následovaný vykřičníkem (!), takto:

Sheet1!,items_list

referenční list-úroveň název v jiném sešitu, měli byste také zahrnovat název sešitu v hranatých závorkách:

List1!items_list

Pokud název listu nebo název sešitu obsahuje mezery, měly by být uzavřeny v jednotlivých uvozovkách:

‚Sheet1‘!items_list

názvy úrovní sešitu

název úrovně sešitu je rozpoznán v celém sešitu a můžete na něj odkazovat jednoduše podle názvu z libovolného listu ve stejném sešitu.,

použití názvu na úrovni sešitu v jiném sešitu předchází názvu s názvem sešitu (včetně rozšíření) a následuje vykřičník:

Book1.xlsx!items_list

rozsah precedence

definovaný název musí být ve svém rozsahu jedinečný. Můžete použít stejný název v různých oblastech, ale to by mohlo vytvořit konflikt jmen. Aby se tomu zabránilo, ve výchozím nastavení má úroveň listu přednost před úrovní sešitu.,

Pokud existuje několik shodně pojmenovaných rozsahů s různými rozsahy a chcete použít název úrovně sešitu, Předpona názvu s názvem sešitu, jako byste odkazovali na jméno v jiném sešitu, např.: Book1.xlsx!datum. Tímto způsobem může být konflikt názvů přepsán pro všechny listy s výjimkou prvního listu, který vždy používá název úrovně místního listu.,

Excel Name Manager-rychlý způsob, jak upravovat, mazat a filtrovat názvy

Jak již název napovídá, Excel Name Manager je speciálně navržen pro správu jmen: změna, filtr, nebo odstranit stávající názvy, stejně jako vytvářet nové.

Existují dva způsoby, jak se dostat do Správce názvů v aplikaci Excel:

• Na kartě Vzorce, Definovat Názvy skupiny, klikněte na Jméno Správce
  
• Stiskněte klávesu Ctrl + F3 zástupce.

v každém případě se otevře dialogové okno Správce jmen, které vám umožní zobrazit všechna jména v aktuálním sešitu na první pohled., Nyní můžete vybrat název, se kterým chcete pracovat, a kliknutím na jedno ze 3 tlačítek v horní části okna proveďte odpovídající akci: Upravit, Odstranit nebo filtrovat.

Jak upravit pojmenovanou oblast v aplikaci Excel

změnit existující Excel název, otevřete Správce názvů, vyberte název a klikněte na tlačítko Upravit…. Otevře se dialogové okno Upravit název, kde můžete změnit název a odkaz. Rozsah názvu nelze změnit.

Tip. Chcete-li upravit odkaz na jméno, nemusíte otevřít dialogové okno Upravit název., Stačí si vybrat jméno z úroků v Excelu Jméno Správce, a zadejte nový odkaz přímo odkaz na pole, nebo klepněte na tlačítko v pravém a vyberte požadovanou oblast v listu. Po kliknutí na tlačítko Zavřít se aplikace Excel zeptá, zda chcete změny uložit, a kliknete na Ano.

Jak filtrovat názvy v aplikaci Excel

Pokud máte spoustu jmen v určité sešitu, klikněte na tlačítko Filtr v pravém horním rohu aplikace Excel Jméno Správce oken pro zobrazení pouze názvy relevantní v daném čase., Tyto filtry jsou k dispozici:

• Názvy s rozsahem na úrovni listu nebo sešitu
• Názvy, s nebo bez chyb
• Definované názvy nebo názvy tabulek

Jak odstranit pojmenované oblasti v aplikaci Excel

Chcete-li odstranit pojmenované oblasti, vyberte ji ve Jménu Správce a klepněte na tlačítko Odstranit v horní části.

Chcete-li odstranit několik jmen, klikněte na křestní jméno, poté stiskněte klávesu Ctrl a podržte ji při klepnutí na jiná jména, která chcete odstranit. Poté klikněte na tlačítko Odstranit a všechna vybraná jména budou smazána najednou.,

Chcete-li odstranit všechna definovaná jména v sešitu, vyberte křestní jméno v seznamu, Stiskněte a podržte klávesu Shift a poté klikněte na příjmení. Uvolněte klávesu Shift a klikněte na Odstranit.

Jak odstranit definované názvy s chybami,

Pokud máte číslo neplatné názvy s odkazem chyby, klepněte na tlačítko Filtr > Názvy s Chybami, aby je filtrovat:

Po tom, vyberte všechny filtrované jména, jak je vysvětleno výše (pomocí Shift), a klepněte na tlačítko Odstranit.

Top 5 výhody použití jména v aplikaci Excel

zatím v tomto kurzu se zaměřujeme hlavně na tom, jak -, aby se věci, které pokrývají různé aspekty vytváření a používání pojmenované oblasti v aplikaci Excel. Ale možná budete zvědaví, co je tak zvláštního na jménech aplikace Excel,díky nimž stojí za to? Pět nejlepších výhod používání definovaných jmen v aplikaci Excel následuje níže.,

Názvy Excel usnadňují vytváření a čtení vzorců

nemusíte zadávat složité odkazy nebo jít tam a zpět výběrem rozsahů na listu. Stačí začít psát jméno, které chcete použít ve vzorci, a Excel zobrazí seznam odpovídajících jmen, ze kterých si můžete vybrat., Dvakrát klikněte na požadované jméno a Excel bude-li vložit do vzorce rovnou:

Excel jména umožňují vytváření rozšiřitelné vzorce

pomocí dynamicky pojmenované oblasti, můžete vytvořit „dynamický“ vzorec, který automaticky zahrnuje nová data do výpočtů, aniž byste museli aktualizovat každý odkaz ručně.

názvy aplikací Excel usnadňují opětovné použití

názvy aplikací Excel usnadňují kopírování vzorce do jiného listu nebo Portování vzorce do jiného sešitu., Jediné, co musíte udělat, je vytvořit stejná jména v cílovém sešitu, zkopírovat/vložit vzorec tak, jak je, a okamžitě jej spustíte.

pojmenované rozsahy zjednodušují navigaci

Chcete-li se rychle dostat do určitého pojmenovaného rozsahu, stačí kliknout na jeho název v poli Název. Pokud je pojmenovaný rozsah umístěn na jiném listu, aplikace Excel vás k tomuto listu automaticky přenese.

Pojmenované rozsahy umožňují vytváření dynamických seznamů drop-down

vybudovat rozšiřitelné a aktualizovat rozevírací seznam, dynamicky pojmenovanou oblast první, a pak vytvořit seznam ověření dat na základě, které sahají. Podrobné pokyny krok za krokem naleznete zde: jak vytvořit dynamické rozbalovací okno v aplikaci Excel.,

aplikace Excel pojmenované oblasti – tipy a triky

Nyní, když víte základy vytváření a používání názvů v aplikaci Excel, dovolte mi, abych sdílet několik tipů, které mohou být užitečné ve vaší práci.

Jak získat seznam všech názvů v sešitu

Chcete-li získat více hmatatelné seznam všech jmen v aktuálním sešitu, proveďte následující kroky:

1. Vyberte nejvyšší cele oblast, kde chcete, jména se objeví.
2. přejděte na kartu vzorce > Definujte skupinu jmen, klikněte na Použít ve vzorcích a poté klikněte na Vložit jména… nebo jednoduše stiskněte klávesu F3.,
3. v dialogovém okně Vložit jména klikněte na Vložit seznam.

toto vloží všechna jména aplikace Excel spolu s jejich odkazy do aktuálního listu, který začíná ve vybrané buňce.

absolutní Názvy Excel vs. relativní Názvy Excel

ve výchozím nastavení se Názvy Excel chovají jako absolutní odkazy-uzamčené na konkrétní buňky. Je však možné vytvořit pojmenovaný rozsah vzhledem k poloze aktivní buňky v době, kdy je název definován., Relativní názvy se chovají jako relativní odkazy-změní se, když je vzorec přesunut nebo zkopírován do jiné buňky.

ve skutečnosti nemohu myslet na žádný důvod, proč by člověk chtěl vytvořit relativní pojmenovaný rozsah, s výjimkou případů, kdy se rozsah skládá z jedné buňky. Jako příklad vytvoříme relativní název, který odkazuje na buňku jeden sloupec vlevo od aktuální buňky, ve stejném řádku:

1. Vyberte buňku B1.
2. stisknutím kláves Ctrl + F3 otevřete Správce názvů aplikace Excel a klikněte na nové …
3. do pole Název zadejte požadovaný název, řekněme item_left.,
4. v poli odkazuje na typ =A1.
5. klikněte na OK.

Nyní, pojďme se podívat, co se stane, když budeme používat item_left názvu do vzorce, například:

Kde items_list odkazuje k $A$2:$A$10 a prodeje se vztahuje na $B$2:$B$10 v tabulce níže.,

Když zadáte vzorec v buňce E2, a pak kopírovat ve sloupci, vypočítá celkový prodej pro každý produkt individuálně, protože item_left je relativní název a jeho odkaz upravuje na základě relativní polohy sloupce a řádku, kde vzorec je zkopírován:

Jak použít Excel názvy stávajících vzorců

Pokud jste definovali oblasti, které jsou již používány ve vašich vzorcích aplikace Excel nezmění odkazy na odpovídající názvy automaticky., Ačkoli, namísto nahrazení odkazů jmény ručně, můžete mít Excel dělat práci za vás. Zde je návod:

1. Vyberte jednu nebo více buněk vzorce, které chcete aktualizovat.
2. Přejděte na kartu Vzorce > Definovat Jména skupiny, a klepněte na tlačítko Definovat Název > Použít Jména…
   
3. V seznamu Použít Názvy dialogové okno, klepněte na jména, který chcete použít, a potom klepněte na tlačítko OK., Pokud aplikace Excel je schopen přizpůsobit některý ze stávajících názvů s odkazy použité ve vašem vzorce, názvy budou vybrány automaticky za vás:
   

Navíc, další dvě možnosti jsou k dispozici (vybrané ve výchozím nastavení):

• Ignorovat Relativní/Absolutní – mějte toto políčko zaškrtnuto, pokud chcete, aby aplikace Excel použije pouze jména se stejnou referenční typ: nahradit relativní odkazy s relativní jména a absolutní odkazy s absolutní jména.,
• použijte názvy řádků a sloupců – pokud je vybrána, Excel přejmenuje všechny odkazy na buňky, které lze identifikovat jako průsečík pojmenovaného řádku a pojmenovaného sloupce. Pro více možností, klepněte na tlačítko Možnosti

Excel název zkratky

Jak je často případ v aplikaci Excel, nejvíce populární funkce lze přistupovat několika způsoby: prostřednictvím pásu karet, klepněte pravým tlačítkem myši menu a klávesové zkratky. Rozsahy s názvem Excel nejsou výjimkou. Zde jsou tři užitečné zkratky pro práci s názvy v aplikaci Excel:

• Ctrl + F3 pro otevření Správce názvů aplikace Excel.,
• Ctrl + Shift + F3 pro vytvoření pojmenovaných rozsahů z výběru.
• F3 Chcete-li získat seznam všech jmen aplikace Excel v sešitu.

Excel název chyby (#REF a #NÁZEV)

ve výchozím nastavení, Microsoft Excel dělá jeho nejlepší, aby se vaše definované názvy v souladu a platnou úpravou rozmezí odkazy automaticky při vložení nebo odstranění buněk do existující pojmenovanou oblast. Například, pokud jste vytvořili pojmenovaný rozsah buněk A1:A10, a pak vložit nový řádek mezi řádky 1 a 10, rozsah se změní na A1:A11., Podobně, pokud odstraníte všechny buňky mezi A1 a A10, váš jmenovaný rozsah se odpovídajícím způsobem uzavře.

Pokud však odstraníte všechny buňky, které tvoří rozsah s názvem Excel, název se stává neplatným a zobrazí #REF! chyba ve Správci jmen. Stejná chyba se zobrazí v vzorec odkazující na jméno:

Pokud vzorec odkazuje na neexistující jméno (překlep nebo odstraněny), #NÁZEV? objeví se chyba. V obou případech otevřete Správce názvů aplikace Excel a zkontrolujte platnost definovaných jmen (nejrychlejším způsobem je filtrování jmen s chybami).,

takto vytváříte a používáte názvy v aplikaci Excel. Děkuji vám za přečtení a doufám, že vás uvidíme na našem blogu příští týden!

• vytvoření a použití dynamicky pojmenované oblasti v aplikaci Excel
• Základní aplikace Excel vzorce & funkce s příklady
• Microsoft Excel vzorce s příklady