Jak vytvořit tabulku a pojmenovat ji v aplikaci Excel

V tomto článku se naučíme Jak vytvořit tabulku a pojmenovat ji v Excelu.

Scénář:

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. Ve svých článcích jsem to mnohokrát zmínil. V tomto článku se seznámíme s Pojmenovaným rozsahem v Excelu a prozkoumáme každý jeho aspekt.

Co jsou pojmenované tabulky v Excelu?

Pojmenované rozsahy nejsou nic jiného než některé rozsahy aplikace Excel, které mají nějaký smysluplný název. Pokud například máte buňku, řekněme B1, obsahující každodenní cíle, 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:

K definování názvu pro rozsah můžete použít zkratku CTRL + F3. Nebo můžete postupovat podle těchto kroků.

Jít do Vzorec Tab
Vyhledejte část Definovaná jména a klikněte na Definovat jména. Tím se otevře Jméno Manger.
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. Je to 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. Tady nějaké jsou.

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

Automaticky definujte jméno

Většinu času budete pracovat s tabulkami strukturovaných dat. Budou mít sloupce a řádky se záhlavími sloupců a nadpisy řádků. A většinou tyto názvy mají pro data smysl a vy byste chtěli svůj rozsah pojmenovat jako tato záhlaví sloupců. Excel poskytuje nástroj pro automatické 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 zadáte vzorec, budou tyto názvy uvedeny jako možnosti, ke kterým lze přistupovat.

Pojmenování rozsahu pomocí tabulek aplikace Excel

Když tablise data v aplikaci Excel pomocí CTRL + T, záhlaví sloupce je automaticky přiřazeno 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 Vzorec Tab > Správce jmen. Zobrazí se 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. To je dobré. Tím se eliminuje šance na nejednoznačnost.

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 vložit seznam dialogové okno.
Klikněte na vložit seznam knoflík.
Seznam bude vložen do vybraných buněk 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ý rozsah aktualizovat. Chcete -li aktualizovat pojmenované rozsahy, postupujte takto.

lis CTRL+F3 otevřít 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ž smažete část součtu 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ástroje 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 prozkoumáno pomocí vzorců. Vzorce jsou s pojmenovanými rozsahy opravdu flexibilní a čitelné. Uvidíme jak.

Snadné psaní vzorců Nyní řekněme, že jste rozsah pojmenovali jako „Položky“. Nyní seznam položek, které chcete počítat „Tužky“. S názvy 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 a dát rozsah funkci COUNTIF aplikace Excel, pro kterou se budete muset nejprve podívat na rozsah a poté vybrat rozsah nebo jej zadat do vzorce. Excel slouží k dostupným rozsahům názvů.

Pojmenované rozsahy se zobrazují jako návrhy při psaní libovolného písmene. Jak excel ukazuje seznam vzorců. Pokud například zadáte = u, každý vzorec 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 si vytvořit jméno „Klient“ a bude odkazovat na „Sundar Pichai“. Nyní, kdykoli budete psát = Klient v jakékoli buňce, zobrazí se Sundar Pichai.

Nejen text, ale můžete také přiřadit čísla jako konstanty, se kterými 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 k pojmenovanému rozsahu, bude se chovat jako relativní 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“ zadejte „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 nemá znak $.

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

Zde jsem to 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 pouze „= 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 se 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ýskyty ‘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 dobře, dokud nebude mít cílový sešit stejný název.

Napří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, abych spočítal počet nebo východ vyskytující se v seznamu regionů. Nyní jsou v různých sloupcích, ale protože jsou oba pojmenovány jako oblasti, bude fungovat perfektně.

Snadná navigace v sešitu

Navigace v sešitu s pojmenovanými rozsahy je nyní snazší. Stačí zadat název jména 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 používáte hypertextové odkazy pro snadnou navigaci. 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 s názvem Zákazník. 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. Pokud nyní vyberete ovoce, pak by další rozbalovací nabídka měla 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? Nediskutovali jsme o tom? Dobře, pojďme tedy rychle pochopit, jaký je rozsah pojmenovaných rozsahů.

Jaký je rozsah rozsahu jmen?

Rozsah dobře definuje, kde lze rozpoznat rozsah jmen. Jakékoli jméno nelze rozpoznat mimo jeho rozsah. Například název v sešitu 1 nelze rozpoznat v různých sešitech. 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.

Doufám, že tento článek o tom, jak vytvořit tabulku a pojmenovat ji v aplikaci Excel, je vysvětlující. Další články o výpočtu hodnot a souvisejících vzorcích Excelu najdete zde. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail.

Jak použít zkratku k přepínání mezi absolutními a relativními odkazy v aplikaci Excel : F4 zkratka pro převod absolutní na relativní referenci a stejná zkratka platí i naopak v Excelu.

Jak používat klávesové zkratky pro sloučení a vycentrování v aplikaci Excel : Použití Alt a pak následovat h, m a C sloučit a vycentrovat buňky v Excelu.

Jak vybrat celý sloupec a řádek pomocí klávesových zkratek v aplikaci Excel : Použijte Ctrl + Prostor vyberte celý sloupec a Posun + Prostor vyberte celý řádek pomocí klávesové zkratky v aplikaci Excel

Vložte speciální zkratku pro Mac a Windows : Ve Windows je klávesová zkratka pro vložit speciální je Ctrl + Alt + PROTI. Zatímco v systému Mac použijte Ctrl + PŘÍKAZ + PROTI kombinace kláves k otevření souboru vložit speciální dialog v Excelu.

Jak vložit zástupce řádků v aplikaci Excel : Použití Ctrl + Posun + = otevřít Vložit dialogové okno, do kterého můžete vložit Excel, sloupec nebo buňky.

Oblíbené články:

Jak používat funkci IF v aplikaci Excel : Příkaz IF v aplikaci Excel zkontroluje podmínku a vrátí konkrétní hodnotu, pokud je podmínka PRAVDA, nebo vrátí jinou konkrétní hodnotu, pokud NEPRAVDA.

Jak používat funkci VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.

Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.

Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.