Ako vytvoriť pomenované rozsahy v programe Excel (Podrobný sprievodca)

Čo je v názve

Ak pracujete s tabuľkami programu Excel, môže to znamenať veľa úspory času a efektivity.

V tomto tutoriále sa naučíte, ako vytvoriť pomenované rozsahy v programe Excel a ako ho použiť na úsporu času.

Pomenované rozsahy v programe Excel - úvod

Ak mi má niekto zavolať alebo sa na mňa obrátiť, použije moje meno (namiesto toho, aby sa muž zdržiaval na takom a takom mieste s tak vysokou hmotnosťou a výškou).

Správny?

Podobne v programe Excel môžete bunke alebo rozsahu buniek dať názov.

Teraz namiesto odkazu na bunku (napríklad A1 alebo A1: A10) môžete jednoducho použiť názov, ktorý ste jej priradili.

Predpokladajme napríklad, že máte množinu údajov, ako je uvedené nižšie:

Ak v tomto súbore údajov musíte odkazovať na rozsah s dátumom, budete musieť vo vzorcoch použiť A2: A11. Podobne aj pre obchodného zástupcu a predaj budete musieť použiť B2: B11 a C2: C11.

Je to v poriadku, keď máte iba niekoľko dátových bodov, ale v prípade, že máte obrovské komplexné súbory údajov, použitie odkazov na bunky na odkazovanie na údaje môže byť časovo náročné.

Rozsahy pomenované v Exceli uľahčujú odkazovanie na súbory údajov v programe Excel.

V programe Excel môžete pre každú kategóriu údajov vytvoriť pomenovaný rozsah a potom ho použiť namiesto odkazov na bunky. Dátumy môžu byť napríklad pomenované „Dátum“, údaje obchodného zástupcu môžu mať názov „SalesRep“ a údaje o predaji môžu mať názov „predaj“.

Môžete tiež vytvoriť názov pre jednu bunku. Ak napríklad máte v bunke percento provízie z predaja, môžete túto bunku pomenovať ako „Provízia“.

Výhody vytvárania pomenovaných rozsahov v programe Excel

Tu sú výhody používania pomenovaných rozsahov v programe Excel.

Namiesto odkazov na bunky používajte mená

Pri vytváraní pomenovaných rozsahov v programe Excel môžete tieto odkazy použiť namiesto odkazov na bunky.

Pre vyššie uvedenú množinu údajov môžete napríklad použiť = SUM (SALES) namiesto = SUM (C2: C11).

Pozrite sa na nižšie uvedené vzorce. Namiesto použitia odkazov na bunky som použil pomenované rozsahy.

• Počet predajov s hodnotou viac ako 500: = COUNTIF (tržby, “> 500 ″)
• Súčet všetkých tržieb realizovaných Tomom: = SUMIF (SalesRep, „Tom“, predaj)
• Provízia, ktorú Joe získal (tržby od Joea vynásobené percentom provízie):
  = SUMIF (SalesRep, „Joe“, tržby)*Provízia

Súhlasíte s tým, že tieto vzorce sa dajú ľahko vytvoriť a sú zrozumiteľné (najmä keď ich zdieľate s niekým iným alebo si ich znova pozriete.

Na výber buniek sa nemusíte vracať späť do množiny údajov

Ďalšou významnou výhodou použitia pomenovaných rozsahov v programe Excel je, že sa nemusíte vracať späť a vyberať rozsahy buniek.

Stačí zadať niekoľko abeced pomenovaného rozsahu a Excel zobrazí zodpovedajúce pomenované rozsahy (ako je uvedené nižšie):

Pomenované rozsahy robia vzorce dynamickými

Použitím pomenovaných rozsahov v programe Excel môžete dosiahnuť, aby boli vzorce programu Excel dynamické.

Napríklad v prípade provízie z predaja môžete namiesto hodnoty 2,5%použiť pomenovaný rozsah.

Ak sa vaša spoločnosť neskôr rozhodne zvýšiť províziu na 3%, môžete jednoducho aktualizovať pomenovaný rozsah a všetky výpočty sa automaticky aktualizujú tak, aby odrážali novú províziu.

Ako vytvoriť pomenované rozsahy v programe Excel

Tu sú tri spôsoby, ako vytvoriť pomenované rozsahy v programe Excel:

Metóda č. 1 - Použitie definície mena

Tu sú kroky na vytvorenie pomenovaných rozsahov v programe Excel pomocou definície názvu:

• Vyberte rozsah, pre ktorý chcete v programe Excel vytvoriť pomenovaný rozsah.
• Prejdite na Vzorce -> Definovať meno.
• V dialógovom okne Nový názov zadajte názov, ktorý chcete priradiť vybratému rozsahu údajov. Rozsah môžete zadať ako celý zošit alebo konkrétny pracovný hárok. Ak vyberiete konkrétny list, názov by nebol k dispozícii na iných listoch.
• Kliknite na tlačidlo OK.

Tým sa vytvorí pomenovaný rozsah SALESREP.

Metóda č. 2: Použitie poľa s názvom

• Vyberte rozsah, pre ktorý chcete vytvoriť názov (nevyberajte hlavičky).
• Prejdite do poľa Názov vľavo na paneli vzorcov a zadajte názov, pomocou ktorého chcete vytvoriť pomenovaný rozsah.
• Tu vytvorené meno bude k dispozícii pre celý zošit. Ak ho chcete obmedziť na pracovný hárok, použite metódu 1.

Metóda č. 3: Použitie možnosti Vytvoriť z výberu

Toto je odporúčaný spôsob, keď máte údaje v tabuľkovej forme a chcete vytvoriť pomenovaný rozsah pre každý stĺpec/riadok.

Napríklad v nižšie uvedenej množine údajov, ak chcete rýchlo vytvoriť tri pomenované rozsahy (dátum, predajná_rozsah a predaj), môžete použiť metódu uvedenú nižšie.

Tu sú kroky na rýchle vytvorenie pomenovaných rozsahov z množiny údajov:

• Vyberte celý súbor údajov (vrátane hlavičiek).
• Prejdite na Vzorce -> Vytvoriť z výberu (Klávesová skratka - Ctrl + Shift + F3). Otvorí sa dialógové okno „Vytvoriť mená z výberu“.
• V dialógovom okne Vytvoriť mená z výberu zaškrtnite možnosti, kde máte hlavičky. V tomto prípade vyberáme iba horný riadok, pretože hlavička je v hornom riadku. Ak máte hlavičky v hornom riadku aj v ľavom stĺpci, môžete vybrať obe. Podobne, ak sú vaše údaje usporiadané, keď sú hlavičky iba v ľavom stĺpci, začiarknete iba možnosť Ľavý stĺpec.

Tým sa vytvoria tri pomenované rozsahy - dátum, predajná_zastávka a predaj.

Všimnite si toho, že automaticky vyberá názvy z hlavičiek. Ak je medzi slovami medzera, vloží podčiarkovník (pretože v pomenovaných rozsahoch nemôžete mať medzery).

Konvencia pomenovania pomenovaných rozsahov v programe Excel

Pri vytváraní pomenovaných rozsahov v programe Excel musíte poznať určité pravidlá pomenovania:

• Prvý znak pomenovaného rozsahu by mal byť znak písmena a podčiarkovníka (_) alebo spätné lomítko (\). Ak je to niečo iné, zobrazí to chybu. Zostávajúce znaky môžu byť písmená, číslice, špeciálne znaky, bodka alebo podčiarkovník.
• V programe Excel nemôžete používať názvy, ktoré tiež predstavujú odkazy na bunky. Nemôžete napríklad použiť AB1, pretože je to tiež odkaz na bunku.
• Pri vytváraní pomenovaných rozsahov nemôžete používať medzery. Nemôžete napríklad mať obchodného zástupcu ako pomenovaný rozsah. Ak chcete spojiť dve slová a vytvoriť pomenovaný rozsah, vytvorte ho podčiarkovníkom, bodkou alebo veľkými písmenami. Môžete mať napríklad Sales_Rep, SalesRep alebo SalesRep.
  • Pri vytváraní pomenovaných rozsahov zaobchádza Excel s veľkými a malými písmenami rovnako. Ak napríklad vytvoríte pomenovaný rozsah PREDAJ, nebudete môcť vytvoriť ďalší pomenovaný rozsah, napríklad „predaj“ alebo „predaj“.
• Pomenovaný rozsah môže mať až 255 znakov.

Príliš veľa pomenovaných rozsahov v programe Excel? Nerob si starosti

Niekedy vo veľkých množinách údajov a v komplexných modeloch môžete v programe Excel vytvoriť veľa pomenovaných rozsahov.

Čo keď si nepamätáte názov pomenovaného rozsahu, ktorý ste vytvorili?

Nerob si starosti - tu je pár užitočných rád.

Získanie názvov všetkých vymenovaných rozsahov

Tu je postup, ako získať zoznam všetkých pomenovaných rozsahov, ktoré ste vytvorili:

• Prejdite na kartu Vzorce.
• V skupine Definované pomenované kliknite na položku Použiť vo vzorci.
• Kliknite na položku „Prilepiť mená“.

Získate tak zoznam všetkých pomenovaných rozsahov v tomto zošite. Ak chcete použiť pomenovaný rozsah (vo vzorcoch alebo v bunke), dvakrát naň kliknite.

Zobrazenie zodpovedajúcich pomenovaných rozsahov

• Ak máte predstavu o názve, zadajte niekoľko počiatočných znakov a Excel zobrazí rozbaľovací zoznam zodpovedajúcich mien.

Ako upraviť pomenované rozsahy v programe Excel

Ak ste už pomenovaný rozsah vytvorili, môžete ho upraviť podľa týchto krokov:

• Prejdite na kartu Vzorce a kliknite na položku Správca mien.
• Dialógové okno Správca mien zobrazí zoznam všetkých pomenovaných rozsahov v tomto zošite. Dvakrát kliknite na pomenovaný rozsah, ktorý chcete upraviť.
• V dialógovom okne Upraviť názov vykonajte zmeny.
• Kliknite na tlačidlo OK.
• Zatvorte dialógové okno Správca mien.

Užitočné skratky pomenovaného rozsahu (sila F3)

Tu je niekoľko užitočných klávesových skratiek, ktoré budú vhodné pri práci s pomenovanými rozsahmi v programe Excel:

• Ak chcete získať zoznam všetkých pomenovaných rozsahov a prilepiť ho do vzorca: F3
• Ak chcete vytvoriť nové meno pomocou dialógového okna Správca mien: Control + F3
• Vytvorenie pomenovaných rozsahov z výberu: Ctrl + Shift + F3

Vytváranie dynamicky pomenovaných rozsahov v programe Excel

V tomto návode sme zatiaľ vytvorili statické pomenované rozsahy.

To znamená, že tieto pomenované rozsahy by vždy odkazovali na rovnakú množinu údajov.

Ak bol napríklad A1: A10 pomenovaný ako „Predaj“, bude sa vždy vzťahovať na A1: A10.

Ak pridáte ďalšie údaje o predaji, budete musieť manuálne aktualizovať referenciu v pomenovanom rozsahu.

Vo svete neustále sa rozširujúcich súborov údajov vám to môže zaberať veľa času. Zakaždým, keď získate nové údaje, možno budete musieť aktualizovať pomenované rozsahy v programe Excel.

Na vyriešenie tohto problému môžeme v programe Excel vytvoriť dynamické pomenované rozsahy, ktoré by automaticky účtovali ďalšie údaje a zahrnuli ich do existujúceho pomenovaného rozsahu.

Napríklad, ak pridám ďalšie dva body údajov o predaji, dynamický pomenovaný rozsah by sa automaticky vzťahoval na A1: A12.

Tento druh dynamicky pomenovaného rozsahu je možné vytvoriť pomocou funkcie Excel INDEX. Namiesto určovania odkazov na bunky pri vytváraní pomenovaného rozsahu zadávame vzorec. Vzorec sa automaticky aktualizuje po pridaní alebo odstránení údajov.

Pozrime sa, ako vytvoriť dynamické pomenované rozsahy v programe Excel.

Predpokladajme, že máme údaje o predaji v bunke A2: A11.

Tu sú kroky na vytvorenie dynamicky pomenovaných rozsahov v programe Excel:

1. 1. Prejdite na kartu Vzorec a kliknite na položku Definovať meno.
   2. V dialógovom okne Nové meno zadajte nasledujúci text:
      • Názov: Predaj
      • Rozsah: Pracovný zošit
      • Vzťahuje sa na: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))
   3. Kliknite na tlačidlo OK.

Hotový!

Teraz máte dynamický pomenovaný rozsah s názvom „Predaj“. Táto informácia sa automaticky aktualizuje vždy, keď k nim pridáte údaje alebo z nich odstránite údaje.

Ako fungujú dynamicky pomenované rozsahy?

Aby ste vysvetlili, ako to funguje, potrebujete vedieť viac o funkcii Excel INDEX.

Väčšina ľudí používa INDEX na vrátenie hodnoty zo zoznamu na základe čísla riadka a stĺpca.

Funkcia INDEX má však aj inú stránku.

Dá sa na to použiť vrátiť odkaz na bunku keď sa používa ako súčasť odkazu na bunku.

Tu je napríklad vzorec, ktorý sme použili na vytvorenie dynamického pomenované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čakáva sa, že táto časť vzorca vráti hodnotu (čo by bola desiata hodnota zo zoznamu, vzhľadom na to, že existuje desať položiek).

Ak sa však používa pred referenciou (=$ A $ 2:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) vráti hodnotu namiesto hodnoty.

Preto sa tu vracia = $ A $ 2: $ A $ 11

Ak do stĺpca predaja pridáme dve ďalšie hodnoty, vráti sa = $ A $ 2: $ A $ 13

Keď do zoznamu pridáte nové údaje, funkcia Excel COUNTIF vráti počet prázdnych buniek v údajoch. Toto číslo používa funkcia INDEX na načítanie odkazu na bunku poslednej položky v zozname.

Poznámka:

• To by fungovalo iba vtedy, ak v údajoch nie sú žiadne prázdne bunky.
• V uvedenom príklade som pre vzorec pomenovaného rozsahu priradil veľký počet buniek (A2: A100). Môžete to upraviť na základe svojej množiny údajov.

Na vytvorenie dynamicky pomenovaných rozsahov v programe Excel môžete použiť aj funkciu OFFSET, pretože funkcia OFFSET je nestála, preto môže viesť k pomalému zošitu programu Excel. INDEX je na druhej strane poloprchavý, čo z neho robí lepšiu voľbu na vytváranie dynamických pomenovaných rozsahov v programe Excel.

Tiež by sa vám mohli páčiť nasledujúce zdroje Excelu:

• Bezplatné šablóny programu Excel.
• Bezplatné online školenie o programe Excel (7-dielny online video kurz).
• Užitočné príklady kódu makra v Exceli.
• 10 Rozšírených príkladov VLOOKUP pre Excel.
• Vytvorenie rozbaľovacieho zoznamu v programe Excel.
• Vytvorenie pomenovaného rozsahu v Tabuľkách Google.
• Ako odkazovať na iný list alebo zošit v programe Excel