Vše o rozsahech pojmenovaných v Excelu - Název rozsahu aplikace Excel

Anonim

Při práci na Excelu jste určitě slyšeli o pojmenovaných rozsazích v Excelu. Třeba od přítele, kolegy nebo nějakého online tutoriálu. Dokonce jsem to mnohokrát zmínil ve svých článcích. V tomto článku se seznámíme s Pojmenovanými rozsahy v Excelu a prozkoumáme všechny jeho aspekty.

Co jsou pojmenované rozsahy v Excelu?

Pojmenované rozsahy nejsou nic jiného než některé rozsahy aplikace Excel, které jsou označeny nějakým smysluplným názvem. Pokud například máte buňku, například B1, která obsahuje každodenní cíl, můžete tuto buňku pojmenovat konkrétně jako „Cíl“. Nyní můžete použít „Target“ k odkazu na A1 místo psaní B1.
Stručně řečeno, pojmenovaný rozsah je pouze pojmenování rozsahů.

Jak pojmenovat rozsah v Excelu?

Ručně definujte jméno:

Chcete -li definovat název pro rozsah, můžete použít zkratku CTRL+F3. Nebo můžete postupovat podle těchto kroků.

    • Přejděte na kartu Vzorec
    • Vyhledejte sekci Definovaná jména a klikněte na Definovat jména. Otevře se Správce jmen.

    • Klikněte na Nový.
    • Zadejte Název.

  • Vyberte rozsah (sešit nebo list)
  • Pokud chcete, napište komentář.
  • Do pole Odkazuje na napište referenci nebo vyberte rozsah pomocí myši.
  • Stiskněte OK. Hotovo.

Nyní se na něj můžete odkazovat pouhým zadáním jeho názvu.
Při vytváření jmen je třeba dodržovat některá pravidla. Oni jsou

  1. Jména by neměla začínat jinými číslicemi nebo speciálními znaky než podtržítko (_) a zpětné lomítko (\).
  2. Jména nesmí obsahovat mezery a žádné speciální znaky kromě _ a \.
  3. Rozsah by neměl být pojmenován jako odkazy na buňky. Například názvy A1, B1 nebo AZ100 atd. Jsou neplatné.
  4. Nemůžete pojmenovat rozsah jako „r“ a „c“, protože jsou vyhrazeny pro odkazy na řádky a sloupce.
  5. Dva pojmenované rozsahy nemohou mít v sešitu stejný název.
  6. Stejný rozsah může mít více jmen.

Automaticky definujte jméno

Většinu času budete pracovat s tabulkou strukturovaných dat. Budou mít sloupce a řádky se záhlavími sloupců a nadpisy řádků. A většinou jsou tato jména pro data smysluplná a vy byste chtěli svůj rozsah pojmenovat jako tato záhlaví sloupců. Excel poskytuje nástroj k automatickému pojmenování rozsahů pomocí nadpisů. Následuj tyto kroky.

    • Rozsahy, které chcete pojmenovat jako jejich nadpisy
    • lis CTRL+SHIFT+F3, nebo Na kartě Vzorec najděte sekci Definovaná jména a klikněte na Vytvořit z výběru.

    • Zobrazí se níže uvedené pole možností. Vybral jsem pouze horní řádek, protože chci pojmenovat tento rozsah jako nadpis a nechci pojmenovat řádky.

  • Klikněte na OK.

Nyní je každý sloupec pojmenován jako jejich nadpis. Kdykoli napíšete, zadáte vzorec, budou tyto názvy uvedeny v možnosti, kterou chcete použít.

Pojmenování rozsahu pomocí tabulek aplikace Excel

Když organizujeme data jako tabulku v Excelu pomocí CTRL + T, záhlaví sloupců se automaticky přiřadí jako název příslušného sloupce. Měli byste prozkoumat tabulky aplikace Excel a jejich výhody.

Jak zobrazit všechny pojmenované rozsahy?

Mohou nastat chvíle, kdy byste chtěli v sešitu vidět všechny dostupné pojmenované rozsahy. Chcete -li zobrazit všechny rozsahy názvů, stiskněte CTRL+F3. Nebo můžete jít na Karta Vzorec> Správce jmen. Zobrazí seznam všech pojmenovaných rozsahů, které jsou k dispozici v sešitu. Můžete upravit dostupné pojmenované rozsahy, odstranit je, přidat nová jména.

Jeden rozsah více jmen

Excel umožňuje uživatelům pojmenovat stejný rozsah s různými názvy. Například rozsah A2: A10 lze pojmenovat „zákazníci“ a „klienti“ současně. Oba názvy budou odkazovat na stejný rozsah A2: A10.
Ale nemůžete mít stejná jména pro dva různé rozsahy. Eliminuje možnost nejasností.

Získejte seznam pojmenovaných rozsahů na listu

Pokud tedy chcete mít seznam pojmenovaných rozsahů a rozsahů, které pokrývají, můžete použít tuto zkratku pro jejich vložení na místo v listu.

    • Vyberte buňku, do které chcete získat seznam pojmenovaných rozsahů.
    • Stiskněte klávesu F3. Tím se otevře a Paste jméno dialogové okno.
    • Klikněte na vložit seznam knoflík.

  • Seznam bude vložen do vybrané buňky a dále.


Pokud dvakrát kliknete na název pojmenovaného rozsahu v poli pro vložení názvu, budou zapsány jako vzorce v buňce. Zkus to.

Aktualizujte pojmenované rozsahy ručně

Když vložíte buňku do pojmenovaného rozsahu, automaticky se aktualizuje a rozšíří. Pokud ale přidáte data na konec tabulky, budete muset pojmenované pole aktualizovat. Chcete -li aktualizovat pojmenované rozsahy, postupujte takto.

  • lis CTRL+F3, otevřete správce jmen.
  • Klikněte na pojmenovaný rozsah, který chcete upravit. Klikněte na Upravit.
  • Do pole Odkazuje na sloupec zadejte rozsah, do kterého chcete rozbalit, a stiskněte OK.

A je hotovo. Toto je ruční aktualizace pojmenovaných rozsahů. Můžeme to však udělat dynamickým pomocí některých vzorců.

Aktualizovat pojmenované rozsahy dynamicky

Je rozumné, aby byly vaše pojmenované rozsahy dynamické, abyste je nemuseli upravovat, kdykoli vaše data přetékají předdefinovaný rozsah.
Pokryl jsem to v samostatném článku s názvem Dynamicky pojmenované rozsahy. Zde se můžete podrobně naučit a porozumět jeho výhodám.

Mazání pojmenovaných rozsahů

Když odstraníte některou část pojmenovaného rozsahu, automaticky upraví jeho rozsah. Ale když odstraníte celý rozsah jmen, zmizí ze seznamu jmen. Jakýkoli vzorec v závislosti na těchto rozsazích zobrazí chybu #REF nebo poskytne nesprávný výstup (funkce počítání).
Pokud chcete z jakéhokoli důvodu odstranit pojmenované rozsahy, postupujte takto.

  • Stiskněte CTRL+F3. Otevře se správce jmen.
  • Vyberte pojmenované rozsahy, které chcete odstranit.
  • Klikněte na tlačítko Odstranit nebo stiskněte tlačítko Odstranit na klávesnici.


Pozor: Před odstraněním pojmenovaných rozsahů se ujistěte, že na těchto názvech nejsou závislé žádné vzorce. Pokud nějaké existují, nejprve je převeďte na rozsahy. Jinak uvidíte chybu #REF.

Mazání jmen s chybami

Excel poskytuje nástroj k odstranění názvů, které mají pouze chyby. Není nutné, abyste každého z nich identifikovali sami. Chcete -li odstranit jména s chybami, postupujte takto:

    • Otevřete Správce jmen (CTRL+F3).
    • Klikněte na rozevírací nabídku Filtr v pravém horním rohu.
    • Vyberte „Název s chybami“

  • Vyberte Vše a stiskněte tlačítko Odstranit.

A jsou pryč. Všechna jména s chybami budou ze záznamu okamžitě odstraněna.

Pojmenované rozsahy se vzorci

Nejlepší použití pojmenovaných rozsahů je se vzorci. Vzorce jsou díky pojmenovaným rozsahům flexibilnější a čitelnější. Uvidíme jak.

Snadné psaní vzorců

Nyní řekněme, že jste pojmenovali řadu jako „Položky“. Nyní seznam položek, které chcete počítat „Tužky“. S názvem je snadné napsat tento vzorec COUNTIF. Jen piš

= COUNTIF (položka, „tužka“)

Jakmile napíšete úvodní závorku vzorce, zobrazí se seznam dostupných pojmenovaných rozsahů. Bez názvu byste napsali funkci gi COUNTIF aplikace Excel s rozsahy, pro které se možná budete muset nejprve podívat na rozsah a poté vybrat rozsah nebo jej zadat do vzorce.

Excel obsluhuje dostupné rozsahy názvů.

Názvy rozsahů se zobrazují jako návrhy při psaní libovolného písmene za znak =. Stejně jako Excel zobrazuje seznam vzorců. Pokud například zadáte = u, každá metoda a pojmenovaný rozsah se zobrazí počínaje u, takže je můžete snadno použít.

Vytvářejte konstanty pomocí pojmenovaných rozsahů

Zatím jsme se dozvěděli o pojmenování rozsahů, ale ve skutečnosti můžete také pojmenovat hodnoty. Pokud je například vaše klientské jméno Sunder Pichai, můžete zadat jméno „Klient“ a odkazuje na zápis „Sundar Pichai“. Nyní, kdykoli napíšete = Klient do jakékoli buňky, zobrazí se Sundar Pichai.


Nejen text, ale můžete také přiřadit číslo jako konstantu, se kterou chcete pracovat. Například definujete cíl. Nebo hodnota něčeho, co se nezmění.
Absolutní a relativní odkazování s pojmenovanými rozsahy

Odkazování pomocí pojmenovaných rozsahů v je velmi flexibilní. Pokud například napíšete název pojmenovaného rozsahu do relativní buňky, bude se chovat jako odpovídající odkaz. Viz obrázek níže.

Když ho ale použijete se vzorci, bude se chovat jako absolutní. Většinu času je budete používat se vzorci, takže můžete říci, že jsou ve výchozím nastavení Absolutní, ale ve skutečnosti jsou flexibilní.

Ale můžeme je také relativní.

Jak vytvořit relativní pojmenované rozsahy v aplikaci Excel?

Řekněme, jestli chci pojmenovat rozsah „Před“, který bude odkazovat na buňku vlevo tam, kde je napsána. Jak to udělám? Následuj tyto kroky:

  • Stiskněte CTRL+F3
  • Klikněte na Nový
  • Do části „Název“ napište „Před“.
  • V části „Odkazuje na:“ napište adresu buňky vlevo. Pokud jste například v buňce B1, napište „= A2“ do části „Odkazuje na:“. Ujistěte se, že neobsahuje znak $.


Nyní, kdekoli ve vzorci napíšete „Před“, bude odkazovat na buňku, která mu byla ponechána.

Zde jsem použil dříve ve funkci COLUMN. Vzorec vrací číslo sloupce levé buňky, kde je zapsán. K mému překvapení ukazuje A1 číslo sloupce posledního sloupce. To znamená, že list je v oběhu. Myslel jsem, že se zobrazí chyba #REF.

Pojmenovat často používané vzorce?

Nyní je tento úžasný. Mnohokrát používáte stejný vzorec znovu a znovu v listu. Můžete například chtít zkontrolovat, zda je jméno ve vašem seznamu zákazníků nebo ne. A tato potřeba může nastat mnohokrát. K tomu napíšete pokaždé stejný složitý vzorec.

= IF (COUNTIF (zákazník, I3), "v seznamu", "není v seznamu")

Co kdybyste do buňky zadali „= IsInCustomer“ a ukázalo by se vám, zda je hodnota v levé buňce v seznamu zákazníků nebo ne?

Zde jsem například připravil stůl. Nyní chci do J5 zadat „= IsInCustomer“ a chtěl bych zjistit, zda je hodnota v I5 je v seznamu zákazníků nebo ne. Postupujte takto.

    • Stiskněte CTRL+F3
    • Klikněte na Nový
    • Do pole Name napište „IsInCustomer“
    • Do pole „Odkazuje na“ napište svůj vzorec. = IF (COUNTIF (zákazník, I5), „v seznamu“, „není v seznamu“)

  • Stiskněte tlačítko OK.

Nyní, kamkoli zadáte „IsInCustomer“, zkontroluje hodnotu v levé buňce v seznamu zákazníků.

To vám zabrání opakovat své já znovu a znovu.

Aplikujte pojmenované rozsahy na vzorce

Tolikrát definujeme názvy pro naše rozsahy poté, co jsme již napsali vzorce na základě rozsahů. Například mám celkovou cenu jako buňky = E2*F2. Jak to můžeme změnit na Units*Unit_Cost.

    • Vyberte vzorce.
    • Přejděte na kartu vzorce. Klikněte na rozevírací seznam Definovat jméno.

  • Klikněte na Použít jména.
  • Zobrazí se seznam všech pojmenovaných rozsahů. Vyberte správná jména a stiskněte OK.


A názvy jsou nyní použity. Můžete to vidět na řádku vzorců.

Snadno čitelné vzorce s pojmenovanými rozsahy

Jak jste viděli, pojmenované rozsahy usnadňují čtení vzorců. Pokud napíšu = COUNTIF („A2: A100“, B2), nikdo nepochopí, co se snažím spočítat, dokud neuvidí data nebo jim to někdo nevysvětlí.

Ale když napíšu = COUNTIF (region, ‘východ‘), většina uživatelů okamžitě pochopí, že počítáme výskyt ‚východu‘ v oblasti pojmenované rozsah.

Přenosné formule

Pojmenované rozsahy umožňují velmi snadné kopírování a vkládání vzorců bez obav ze změny odkazů. A můžete vzít jeden vzorec z jednoho sešitu do druhého a bude to fungovat, dokud a pokud cílový sešit nebude mít stejné názvy.

Příklad, pokud máte vzorec = COUNTIF (region, východ) v Distribuční tabulce a máte další sešit zákazníky který má také pojmenovaný rozsah „Region“. Pokud tento vzorec zkopírujete přímo kamkoli do sešitu, zobrazí se vám správné informace. Na struktuře dat nezáleží. Nezáleží na tom, kde je sakra ten sloupec ve vašem sešitu. To bude fungovat správně.

Na výše uvedeném obrázku jsem použil přesně stejný vzorec ve dvou různých souborech pro počítání čísel nebo východů vyskytujících se v seznamu regionů. Nyní jsou v různých sloupcích, ale protože oba jsou pojmenovány jako oblast, bude to fungovat perfektně.

Snadná navigace v sešitu

Navigace v sešitu s pojmenovanými rozsahy bude snazší. Stačí zadat název pojmenovaného do pole název. Excel vás přenese do rozsahu, bez ohledu na to, kde jste v sešitu. Vzhledem k tomu, že pojmenovaný rozsah má rozsah sešitu.
Pokud jste například na listu 10 a chcete získat seznam zákazníků a nevíte, na kterém listu to je. Přejděte do pole se jménem a zadejte „zákazník“. Ve zlomku sekundy budete přesměrováni na pojmenovaný rozsah.


Sníží se námaha zapamatování rozsahů.

Navigujte pomocí hypertextových odkazů s pojmenovaným rozsahem

Když je váš list velký a často přecházíte z jednoho bodu do druhého, rádi pro snadnou navigaci používáte hypertextové odkazy. Dobře pojmenované rozsahy mohou perfektně fungovat s hypertextovými odkazy. Chcete -li přidat hypertextové odkazy pomocí pojmenovaných rozsahů, postupujte takto.

    • Vyberte buňku, kde chcete hypertextový odkaz
    • Stisknutím kláves CTRL+K nebo přejděte na kartu Vložit> HyperLink otevřete dialogové okno Vložit hypertextový odkaz.

    • Klikněte na Umístit do tohoto dokumentu.
    • Přejděte dolů a zobrazte dostupné pojmenované rozsahy pod definovanými názvy

  • Vyberte Pojmenovaný rozsah a vložte do něj hypertextový odkaz.

A je hotovo. Máte hypertextový odkaz na vámi zvolený pojmenovaný rozsah. Pomocí toho můžete vytvořit rejstřík pojmenovaných rozsahů, které můžete zobrazit a kliknutím na ně přejít přímo. Díky tomu bude váš sešit opravdu uživatelsky přívětivý.

Pojmenovaný rozsah a ověření dat

Pojmenované rozsahy a Ověření dat jsou jako stvořené pro sebe navzájem. Pojmenované rozsahy umožňují vysoce přizpůsobitelné ověřování dat. Je mnohem snazší přidat ověření ze seznamu pomocí pojmenovaného rozsahu. Podívejme se, jak…

    • Přejděte na kartu Data
    • Klikněte na Ověření dat
    • V části „Povolit:“ vyberte Seznam
    • V sekci „Zdroj:“ zadejte „= Zákazník“ (napište libovolný pojmenovaný rozsah)

  • Stiskněte OK

Nyní bude tato buňka obsahovat jména zákazníků, kteří jsou součástí rozsahu pojmenovaného zákazníkem. Snadné, že?

Závislé nebo kaskádové ověření dat s pojmenovanými rozsahy

Co teď, když chcete kaskádové nebo závislé ověření dat. Pokud například chcete rozevírací seznam s kategoriemi, Ovoce a Zelenina. Nyní, pokud zvolíte ovoce, jiný rozevírací seznam by měl zobrazovat pouze možnost ovoce a pokud zvolíte zeleninu, pak pouze zeleninu.

Toho lze snadno dosáhnout pomocí pojmenovaných rozsahů. Zjistěte jak.

  • Závislé rozevírání pomocí pojmenovaného rozsahu
  • Jiné způsoby kaskádové validace dat

Žádné ověřování dat s názvy dat v tabulkách

Ačkoli tabulky aplikace Excel poskytují strukturované názvy, nelze je však použít s ověřováním dat a podmíněným formátováním. Nevím, proč to Excel neumožňuje.
Ale to neznamená, že to nelze udělat. Rozsahy můžete pojmenovat v tabulce a poté je použít k ověření. Excel s tím nemá žádný problém.

Rozsah pojmenovaných rozsahů

Zatím jsme mluvili o pojmenovaných rozsazích, které měly rozsah sešitu. Co? Nemluvili jsme o tom? Dobře, pojďme tedy rychle pochopit, jaký je rozsah pojmenovaných rozsahů.

Co je rozsah rozsahu jmen?

Rozsah dobře definuje, kde lze rozpoznat rozsah jmen. Jakýkoli název nelze rozpoznat mimo jeho rozsah. Například název v sešitu 1 nelze rozpoznat v jiném sešitu. Excel nabízí dvě možnosti pro rozsah pojmenovaných rozsahů sešit a sešit.

Jak definovat rozsah pojmenovaného rozsahu?

Když vytvoříte nový rozsah názvů, uvidíte sekci „Rozsah:“. Klikněte na rozevírací seznam a vyberte rozsah pro svůj rozsah jmen. Jakmile vytvoříte pojmenovaný rozsah, nemůžete změnit rozsah. Takže je lepší to udělat dříve. Ve výchozím nastavení je to sešit.

Rozsah sešitu

Toto je výchozí rozsah pro pojmenovaný rozsah. Název definovaný s rozsahem sešitu lze použít v celém sešitu, ve kterém je definován (nikoli v jiných sešitech).
Všechny výše uvedené příklady měly rozsah sešitu.

Rozsah pracovního listu

Název, který je definován s rozsahem listu, lze použít pouze na definování listu. Například pokud definuji „Celkem“ pro celkovou buňku s rozsahem listu1. Poté bude součet uznán pouze na listu1. Ostatní listy nerozpoznají.

Chci rozsah aplikace Excel

Excel nemá globální nebo řekněme rozsah Excelu. Ve skutečnosti bych chtěl definovat některá jména, která lze rozpoznat ve všech sešitech v mém systému. Pokud někdo ví, jak to můžeme udělat, dejte mi vědět.

Úprava rozsahu po vytvoření jmen

Nemůžeš. Excel po vytvoření neumožňuje upravit rozsah pojmenovaného rozsahu. Protože všechny pojmenované rozsahy na listu jsou ve výchozím nastavení sešitu s rozsahem, a možná budete chtít změnit jejich rozsah na list.
Chcete -li to provést, jednoduše vytvořte kopii tohoto listu a aplikace Excel vytvoří každé jméno na tomto listu místní, aby se předešlo nejednoznačnosti. Nyní můžete původní list odstranit, pokud chcete.

Řez název vložit

Když vyjmete a vložíte pojmenovaný rozsah z jednoho cíle do druhého, odkaz se změní na nové umístění. Pokud například máte pojmenovaný rozsah „Zákazník“ v A2: A10 a vyjmete jej a vložíte do B2: B10, pak bude jméno zákazníka odkazovat na nové umístění B2: B10.

Dynamicky pojmenované rozsahy v aplikaci Excel

17 Úžasné funkce tabulek aplikace Excel

Oblíbené články:

50 zkratek aplikace Excel pro zvýšení produktivity

Jak používat funkci VLOOKUP v Excelu

Jak používat funkci COUNTIF v aplikaci Excel

Jak používat funkci SUMIF v aplikaci Excel