Jak vytvořit pojmenované rozsahy v aplikaci Excel (Podrobný průvodce)

Co je v názvu?

Pokud pracujete s tabulkami aplikace Excel, může to znamenat spoustu času a efektivity.

V tomto tutoriálu se naučíte, jak vytvořit pojmenované rozsahy v aplikaci Excel a jak jej použít k úspoře času.

Pojmenované rozsahy v Excelu - úvod

Pokud mi někdo musí zavolat nebo se na mě obrátit, použije mé jméno (místo toho, aby muž řekl, že se zdržuje na tom a takovém místě s tak vysokou hmotností a výškou).

Že jo?

Podobně v Excelu můžete pojmenovat buňku nebo rozsah buněk.

Nyní místo odkazu na buňku (například A1 nebo A1: A10) můžete jednoduše použít název, který jste jí přiřadili.

Předpokládejme například, že máte sadu dat, jak je uvedeno níže:

Pokud v této sadě dat musíte odkazovat na rozsah s datem, budete muset ve vzorcích použít A2: A11. Podobně pro obchodní zástupce a prodeje budete muset použít B2: B11 a C2: C11.

Je to v pořádku, když máte jen pár datových bodů, ale v případě, že máte obrovské komplexní datové sady, může být použití odkazů na buňky na data časově náročné.

Rozsahy pojmenované v Excelu usnadňují odkazování na datové sady v Excelu.

V aplikaci Excel můžete pro každou datovou kategorii vytvořit pojmenovaný rozsah a poté tento název použít místo odkazů na buňky. Data lze například pojmenovat „Datum“, data obchodního zástupce lze pojmenovat „SalesRep“ a údaje o prodeji lze pojmenovat „prodeje“.

Můžete také vytvořit název pro jednu buňku. Pokud například máte v buňce procento provize z prodeje, můžete tuto buňku pojmenovat jako „Provize“.

Výhody vytváření pojmenovaných rozsahů v aplikaci Excel

Zde jsou výhody používání pojmenovaných rozsahů v Excelu.

Místo odkazů na buňky použijte jména

Při vytváření pojmenovaných rozsahů v aplikaci Excel můžete tyto odkazy použít místo odkazů na buňky.

Pro výše uvedenou sadu dat můžete například použít místo = SUM (SALES) místo = SUM (C2: C11).

Podívejte se na níže uvedené vzorce. Místo použití odkazů na buňky jsem použil Pojmenované rozsahy.

• Počet prodejů s hodnotou vyšší než 500: = COUNTIF (tržby, „> 500 ″)
• Součet všech prodejů provedených Tomem: = SUMIF (SalesRep, „Tom“, prodeje)
• Provize vydělané Joe (tržby Joe vynásobené provizí v procentech):
  = SUMIF (SalesRep, ”Joe”, Prodej)*Provize

Souhlasíte s tím, že tyto vzorce lze snadno vytvořit a jsou snadno srozumitelné (zvláště když je sdílíte s někým jiným nebo si ho sami znovu prohlédnete.

K výběru buněk se nemusíte vracet zpět do datové sady

Další významnou výhodou používání pojmenovaných rozsahů v aplikaci Excel je, že se nemusíte vracet a vybírat rozsahy buněk.

Stačí zadat několik abeced pojmenovaného rozsahu a Excel zobrazí odpovídající pojmenované rozsahy (jak je uvedeno níže):

Pojmenované rozsahy dělají vzorce dynamickými

Pomocí pojmenovaných rozsahů v aplikaci Excel můžete dynamicky vytvářet vzorce aplikace Excel.

Například v případě provize z prodeje můžete místo hodnoty 2,5%použít Pojmenovaný rozsah.

Pokud se vaše společnost později rozhodne zvýšit provizi na 3%, můžete jednoduše aktualizovat pojmenovaný rozsah a veškerý výpočet se automaticky aktualizuje, aby odrážel novou provizi.

Jak vytvořit pojmenované rozsahy v aplikaci Excel

Zde jsou tři způsoby, jak vytvořit pojmenované rozsahy v aplikaci Excel:

Metoda č. 1 - Použití definovaného jména

Zde jsou kroky k vytvoření pojmenovaných rozsahů v aplikaci Excel pomocí Definovat název:

• Vyberte rozsah, pro který chcete v aplikaci Excel vytvořit pojmenovaný rozsah.
• Přejděte na Vzorce -> Definovat jméno.
• V dialogovém okně Nový název zadejte Název, který chcete přiřadit vybranému rozsahu dat. Rozsah můžete zadat jako celý sešit nebo konkrétní list. Pokud vyberete konkrétní list, název nebude na jiných listech k dispozici.
• Klikněte na OK.

Tím se vytvoří pojmenovaný rozsah SALESREP.

Metoda č. 2: Použití pole Název

• Vyberte rozsah, pro který chcete vytvořit název (nevybírejte záhlaví).
• Přejděte do pole Název na levé straně lišty vzorců a zadejte název, pomocí kterého chcete pojmenovaný rozsah vytvořit.
• Zde vytvořené jméno bude k dispozici pro celý sešit. Pokud jej chcete omezit na list, použijte metodu 1.

Metoda č. 3: Použití možnosti Vytvořit z výběru

Toto je doporučený způsob, pokud máte data v tabulkové formě a chcete pro každý sloupec/řádek vytvořit pojmenovaný rozsah.

Pokud například v níže uvedené datové sadě chcete rychle vytvořit tři pojmenované rozsahy (Datum, Prodej_Rep a Prodej), můžete použít níže uvedenou metodu.

Zde jsou kroky k rychlému vytvoření pojmenovaných rozsahů z datové sady:

• Vyberte celou sadu dat (včetně záhlaví).
• Přejít na Vzorce -> Vytvořit z výběru (Klávesová zkratka - Control + Shift + F3). Otevře se dialogové okno „Vytvořit jména z výběru“.
• V dialogovém okně Vytvořit jména z výběru zaškrtněte možnosti, kde máte záhlaví. V tomto případě vybereme pouze horní řádek, protože záhlaví je v horním řádku. Pokud máte záhlaví v horním řádku i levém sloupci, můžete vybrat obojí. Podobně, pokud jsou vaše data uspořádána, když jsou záhlaví pouze v levém sloupci, zaškrtnete pouze možnost Levý sloupec.

Tím se vytvoří tři pojmenované rozsahy - datum, Sales_Rep a Sales.

Všimněte si toho, že automaticky vybírá jména z hlaviček. Pokud mezi slovy existuje mezera, vloží podtržítko (protože v pojmenovaných rozsazích nemůžete mít mezery).

Konvence pojmenování pojmenovaných rozsahů v aplikaci Excel

Při vytváření pojmenovaných rozsahů v aplikaci Excel musíte znát určitá pravidla pojmenování:

• První znak pojmenovaného rozsahu by měl být znak písmene a podtržítka (_) nebo zpětné lomítko (\). Pokud je to cokoli jiného, ​​zobrazí se chyba. Zbývající znaky mohou být písmena, číslice, speciální znaky, tečka nebo podtržítko.
• V aplikaci Excel nemůžete používat názvy, které také představují odkazy na buňky. Například nemůžete použít AB1, protože je to také odkaz na buňku.
• Při vytváření pojmenovaných rozsahů nemůžete používat mezery. Nemůžete například mít obchodního zástupce jako pojmenovaný rozsah. Pokud chcete spojit dvě slova a vytvořit pojmenovaný rozsah, použijte k jeho vytvoření podtržítko, tečku nebo velká písmena. Můžete mít například Sales_Rep, SalesRep nebo SalesRep.
  • Při vytváření pojmenovaných rozsahů aplikace Excel zachází s velkými i malými písmeny stejným způsobem. Pokud například vytvoříte pojmenovaný rozsah PRODEJ, pak nebudete moci vytvořit další pojmenovaný rozsah, například „prodej“ nebo „prodej“.
• Pojmenovaný rozsah může mít až 255 znaků.

Příliš mnoho pojmenovaných rozsahů v aplikaci Excel? Nebojte se

Někdy u velkých datových sad a složitých modelů můžete nakonec v aplikaci Excel vytvořit mnoho pojmenovaných rozsahů.

Co když si nepamatujete název pojmenovaného rozsahu, který jste vytvořili?

Nedělej si starosti - zde je několik užitečných tipů.

Získání názvů všech pojmenovaných rozsahů

Zde jsou kroky k získání seznamu všech pojmenovaných rozsahů, které jste vytvořili:

• Přejděte na kartu Vzorce.
• Ve skupině Definované pojmenované klikněte na Použít ve vzorci.
• Klikněte na „Vložit jména“.

Získáte tak seznam všech pojmenovaných rozsahů v tomto sešitu. Chcete -li použít pojmenovaný rozsah (ve vzorcích nebo buňce), dvakrát na něj klikněte.

Zobrazení odpovídajících pojmenovaných rozsahů

• Pokud máte nějakou představu o názvu, zadejte několik počátečních znaků a Excel zobrazí rozevírací seznam odpovídajících jmen.

Jak upravit pojmenované rozsahy v aplikaci Excel

Pokud jste již vytvořili pojmenovaný rozsah, můžete jej upravit pomocí následujících kroků:

• Přejděte na kartu Vzorce a klikněte na Správce jmen.
• Dialogové okno Správce názvů zobrazí seznam všech pojmenovaných rozsahů v tomto sešitu. Poklepejte na pojmenovaný rozsah, který chcete upravit.
• V dialogovém okně Upravit název proveďte změny.
• Klikněte na OK.
• Zavřete dialogové okno Správce jmen.

Užitečné zkratky pojmenovaného dosahu (síla F3)

Zde je několik užitečných klávesových zkratek, které se vám budou hodit při práci s pojmenovanými rozsahy v Excelu:

• Chcete -li získat seznam všech pojmenovaných rozsahů a vložit jej do vzorce: F3
• Chcete -li vytvořit nové jméno pomocí dialogového okna Správce jmen: Ovládání + F3
• Vytvoření pojmenovaných rozsahů z výběru: Control + Shift + F3

Vytváření dynamicky pojmenovaných rozsahů v aplikaci Excel

V tomto kurzu jsme zatím vytvořili statické pojmenované rozsahy.

To znamená, že tyto pojmenované rozsahy by vždy odkazovaly na stejnou datovou sadu.

Pokud byl například A1: A10 pojmenován jako „Prodej“, vždy by odkazoval na A1: A10.

Pokud přidáte další údaje o prodeji, budete muset ručně přejít a aktualizovat odkaz v pojmenovaném rozsahu.

Ve světě neustále se rozšiřujících datových sad vám to může zabrat spoustu času. Pokaždé, když získáte nová data, budete možná muset aktualizovat pojmenované rozsahy v Excelu.

K vyřešení tohoto problému můžeme v aplikaci Excel vytvořit dynamicky pojmenované rozsahy, které by automaticky účtovaly další data a zahrnovaly je do stávajícího pojmenovaného rozsahu.

Například pokud například přidám dva další datové body prodeje, dynamický pojmenovaný rozsah by automaticky odkazoval na A1: A12.

Tento druh dynamicky pojmenovaného rozsahu lze vytvořit pomocí funkce Excel INDEX. Namísto zadávání odkazů na buňky při vytváření pojmenovaného rozsahu zadáváme vzorec. Vzorec se automaticky aktualizuje při přidání nebo odstranění dat.

Pojďme se podívat, jak v aplikaci Excel vytvářet dynamicky pojmenované rozsahy.

Předpokládejme, že máme údaje o prodeji v buňce A2: A11.

Zde jsou kroky k vytvoření dynamických pojmenovaných rozsahů v aplikaci Excel:

1. 1. Přejděte na kartu Vzorec a klikněte na Definovat jméno.
   2. V dialogovém okně Nový název zadejte následující:
      • Název: Prodej
      • Rozsah: Sešit
      • Odkazuje na: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))
   3. Klikněte na OK.

Hotovo!

Nyní máte dynamický pojmenovaný rozsah s názvem „Prodej“. To by se automaticky aktualizovalo, kdykoli do něj přidáte data nebo z něj odeberete data.

Jak dynamické pojmenované rozsahy fungují?

Chcete -li vysvětlit, jak to funguje, potřebujete vědět něco více o funkci Excel INDEX.

Většina lidí používá INDEX k vrácení hodnoty ze seznamu na základě čísla řádku a sloupce.

Funkce INDEX má ale i další stránku.

Dá se na to zvyknout vrátit odkaz na buňku když je použit jako součást odkazu na buňku.

Zde je například vzorec, který jsme použili k vytvoření dynamického pojmenovaného rozsahu:

= $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "" & ""))

INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””) -> Očekává se, že tato část vzorce vrátí hodnotu (což by byla desátá hodnota ze seznamu, vzhledem k tomu, že existuje deset položek).

Při použití před referencí (=$ A $ 2:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) vrátí místo hodnoty odkaz na buňku.

Zde tedy vrací = $ A $ 2: $ A $ 11

Pokud do sloupce prodeje přidáme další dvě hodnoty, vrátí se = $ A $ 2: $ A $ 13

Když do seznamu přidáte nová data, funkce Excel COUNTIF vrátí počet neprázdných buněk v datech. Toto číslo používá funkce INDEX k načtení odkazu na buňku poslední položky v seznamu.

Poznámka:

• To by fungovalo pouze v případě, že v datech nejsou žádné prázdné buňky.
• Ve výše uvedeném příkladu jsem pro vzorec Pojmenovaný rozsah přiřadil velký počet buněk (A2: A100). Můžete to upravit na základě vaší sady dat.

Funkci OFFSET můžete také použít k vytvoření dynamicky pojmenovaných rozsahů v aplikaci Excel, ale protože funkce OFFSET je nestálá, může vést k pomalému sešitu aplikace Excel. INDEX je na druhé straně částečně volatilní, což z něj činí lepší volbu pro vytváření dynamických pojmenovaných rozsahů v aplikaci Excel.

Také se vám mohou líbit následující prostředky Excelu:

• Šablony aplikace Excel zdarma.
• Online školení Excel zdarma (7dílný online video kurz).
• Užitečné příklady kódu makra v Excelu.
• 10 Pokročilých příkladů VLOOKUP pro Excel.
• Vytvoření rozevíracího seznamu v aplikaci Excel.
• Vytvoření pojmenovaného rozsahu v Tabulkách Google.
• Jak odkazovat na jiný list nebo sešit v aplikaci Excel