Všetko o rozsahoch pomenovaných v programe Excel - Názov rozsahu programu Excel

Anonim

Pri práci na Exceli ste museli počuť o pomenovaných rozsahoch v Exceli. Možno od priateľa, kolegu alebo z nejakého online tutoriálu. Dokonca som to už mnohokrát spomenul vo svojich článkoch. V tomto článku sa dozvieme o pomenovaných rozsahoch v programe Excel a preskúmame ich všetky aspekty.

Čo sú pomenované rozsahy v programe Excel?

Pomenované rozsahy nie sú nič iné ako niektoré rozsahy programu Excel, ktoré sú označené nejakým zmysluplným názvom. Ak napríklad máte bunku, napríklad B1, ktorá obsahuje každodenný cieľ, môžete túto bunku pomenovať konkrétne ako „Cieľ“. Teraz môžete použiť „Cieľ“ na odkaz na A1 namiesto písania B1.
Stručne povedané, pomenovaný rozsah je iba pomenovanie rozsahov.

Ako pomenovať rozsah v programe Excel?

Manuálne definujte meno:

Na definovanie názvu pre rozsah môžete použiť skratku CTRL+F3. Alebo môžete postupovať podľa týchto krokov.

    • Prejdite na kartu Vzorec
    • Vyhľadajte časť Definované mená a kliknite na položku Definovať mená. Otvorí sa správca mien.

    • Kliknite na Nový.
    • Napíšte Meno.

  • Vyberte rozsah (zošit alebo list)
  • Ak chceš, napíš komentár.
  • Do poľa Odkazuje na napíšte referenciu alebo vyberte rozsah pomocou myši.
  • Kliknite na OK. Je to hotové.

Teraz sa na neho môžete odvolať zadaním jeho názvu.
Pri vytváraní mien je potrebné dodržiavať niekoľko pravidiel. Oni sú

  1. Názvy by nemali začínať inými číslicami alebo špeciálnymi znakmi ako podčiarkovník (_) a spätné lomítko (\).
  2. Mená nemôžu obsahovať medzery a žiadne špeciálne znaky okrem _ a \.
  3. Rozsah by nemal byť pomenovaný ako odkazy na bunky. Napríklad názvy A1, B1 alebo AZ100 atď. Sú neplatné.
  4. Rozsah nemôžete pomenovať ako „r“ a „c“, pretože sú vyhradené pre odkazy na riadky a stĺpce.
  5. Dva pomenované rozsahy nemôžu mať v zošite rovnaký názov.
  6. Rovnaký rozsah môže mať viacero názvov.

Automaticky definujte meno

Väčšinu času budete pracovať s tabuľkou štruktúrovaných dát. Budú mať stĺpce a riadky s nadpismi stĺpcov a nadpismi riadkov. Tieto názvy majú pre údaje väčšinou význam a svoj rozsah by ste chceli pomenovať ako tieto nadpisy stĺpcov. Excel poskytuje nástroj na automatické pomenovanie rozsahov pomocou názvov. Nasleduj tieto kroky.

    • Rozsahy, ktoré chcete pomenovať ako nadpisy
    • Stlačte CTRL+SHIFT+F3, alebo vyhľadajte sekciu Definované mená na karte Vzorec a kliknite na položku Vytvoriť z výberu.

    • Zobrazí sa nižšie uvedené pole s možnosťami. Vybral som iba horný riadok, pretože chcem pomenovať tento rozsah ako nadpis a nechcem pomenovať riadky.

  • Kliknite na tlačidlo OK.

Teraz je každý stĺpec pomenovaný ako nadpis. Kedykoľvek zadáte vzorec, tieto názvy budú uvedené v možnosti, ktorá sa má použiť.

Pomenovanie rozsahu pomocou tabuliek programu Excel

Keď organizujeme údaje ako tabuľku v Exceli pomocou CTRL + T, nadpisy stĺpcov sa automaticky priradia ako názov príslušného stĺpca. Mali by ste preskúmať tabuľky programu Excel a ich výhody.

Ako zobraziť všetky pomenované rozsahy?

Nastanú chvíle, kedy by ste chceli v zošite vidieť všetky dostupné pomenované rozsahy. Ak chcete zobraziť všetky rozsahy mien, stlačte kombináciu klávesov CTRL+F3. Alebo môžete ísť na Karta Vzorec> Správca mien. Zobrazí sa zoznam všetkých pomenovaných rozsahov, ktoré sú k dispozícii v zošite. Dostupné dostupné pomenované rozsahy môžete upravovať, odstraňovať a pridávať nové názvy.

Jeden rozsah viac mien

Excel umožňuje používateľom pomenovať rovnaký rozsah rôznymi názvami. Napríklad rozsah A2: A10 možno pomenovať „zákazníci“ a „klienti“ súčasne. Oba názvy budú odkazovať na rovnaký rozsah A2: A10.
Ale nemôžete mať rovnaké názvy pre dva rôzne rozsahy. Eliminuje to možnosť nejednoznačnosti.

Získajte zoznam pomenovaných rozsahov na hárku

Ak teda chcete mať zoznam pomenovaných rozsahov a rozsahov, ktoré pokrývajú, môžete použiť túto skratku na ich prilepenie na miesto v hárku.

    • Vyberte bunku, do ktorej chcete získať zoznam pomenovaných rozsahov.
    • Stlačte kláves F3. Tým sa otvorí a Paste meno dialógové okno.
    • Kliknite na prilepiť zoznam tlačidlo.

  • Zoznam bude vložený do vybratej bunky a ďalej.


Ak dvakrát kliknete na názov pomenovaného rozsahu v poli pre vloženie názvu, zapíšu sa do bunky ako vzorce. Skús to.

Aktualizujte pomenované rozsahy ručne

Keď vložíte bunku do pomenovaného rozsahu, automaticky sa aktualizuje a rozšíri. Ak však na koniec tabuľky pridáte údaje, budete musieť pomenované pole aktualizovať. Ak chcete aktualizovať pomenované rozsahy, postupujte nasledovne.

  • Stlačte CTRL+F3, otvorte správcu mien.
  • Kliknite na pomenovaný rozsah, ktorý chcete upraviť. Kliknite na Upraviť.
  • Do stĺpca Odkazuje na stĺpec zadajte rozsah, do ktorého chcete rozšíriť, a kliknite na tlačidlo OK.

A je hotovo. Toto je manuálna aktualizácia pomenovaných rozsahov. Môžeme to však urobiť dynamickým pomocou niektorých vzorcov.

Aktualizovať pomenované rozsahy dynamicky

Je múdre, aby boli vaše pomenované rozsahy dynamické, aby ste ich nemuseli upravovať vždy, keď vaše údaje pretečú preddefinovaným rozsahom.
Pokryl som to v samostatnom článku s názvom Dynamické pomenované rozsahy. Tu sa môžete podrobne naučiť a porozumieť jeho výhodám.

Odstránenie pomenovaných rozsahov

Keď odstránite časť pomenovaného rozsahu, rozsah sa automaticky upraví. Keď však vymažete celý rozsah mien, zmizne zo zoznamu mien. Akýkoľvek vzorec v závislosti od týchto rozsahov zobrazí chybu #REF alebo poskytne nesprávny výstup (funkcie počítania).
Ak chcete z akéhokoľvek dôvodu odstrániť pomenované rozsahy, postupujte nasledovne.

  • Stlačte kombináciu klávesov CTRL+F3. Otvorí sa správca mien.
  • Vyberte Pomenované rozsahy, ktoré chcete odstrániť.
  • Kliknite na tlačidlo Odstrániť alebo kliknite na tlačidlo Odstrániť na klávesnici.


Pozor: Pred odstránením pomenovaných rozsahov sa uistite, že na týchto názvoch nie sú závislé žiadne vzorce. Ak nejaké existujú, najskôr ich preveďte na rozsahy. V opačnom prípade sa zobrazí chyba #REF.

Vymazanie mien s chybami

Excel poskytuje nástroj na odstránenie názvov, ktoré obsahujú iba chyby. Nie je potrebné identifikovať každého z nich sami. Ak chcete odstrániť mená s chybami, postupujte takto:

    • Otvorte Správcu mien (CTRL+F3).
    • Kliknite na rozbaľovaciu ponuku Filter v pravom hornom rohu.
    • Vyberte „Meno s chybami“

  • Vyberte všetko a kliknite na tlačidlo Odstrániť.

A sú preč. Všetky mená s chybami budú ihneď vymazané zo záznamu.

Pomenované rozsahy so vzorcami

Najlepšie použitie pomenovaných rozsahov sa dosahuje pomocou vzorcov. Vďaka pomenovaným rozsahom sú vzorce flexibilnejšie a čitateľnejšie. Pozrime sa ako.

Jednoduché písanie vzorcov

Teraz povedzme, že ste rozsah pomenovali ako „Položky“. Teraz je zoznam položiek, ktoré chcete počítať, „ceruzky“. S názvom je ľahké napísať tento vzorec COUNTIF. Stačí napísať

= COUNTIF (položka, „ceruzka“)

Hneď ako napíšete úvodnú zátvorku vzorca, zobrazí sa zoznam dostupných pomenovaných rozsahov. Bez názvu by ste napísali funkciu gi COUNTIF programu Excel s rozsahmi, pre ktorú sa možno budete musieť najskôr pozrieť na rozsah a potom vybrať rozsah alebo ho napísať do vzorca.

Excel obsluhuje dostupné rozsahy názvov.

Názvy rozsahov sa zobrazia ako návrhy, keď za znak = napíšete akékoľvek písmeno. Rovnako ako Excel zobrazuje zoznam vzorcov. Ak napríklad zadáte = u, každá metóda a pomenovaný rozsah sa zobrazia od písmena u, aby ste ich mohli ľahko použiť.

Vykonávajte konštanty pomocou pomenovaných rozsahov

Doteraz sme sa dozvedeli o pomenovaní rozsahov, ale v skutočnosti môžete pomenovať aj hodnoty. Ak je napríklad vaše klientske meno Sunder Pichai, môžete zadať meno „klient“ a bude odkazovať na zápis „Sundar Pichai“. Teraz, kedykoľvek napíšete = Klient do akejkoľvek bunky, zobrazí sa Sundar Pichai.


Nielen text, ale číslo, ktorému môžete pracovať, môžete priradiť aj ako konštantu. Napríklad definujete cieľ. Alebo hodnota niečoho, čo sa nezmení.
Absolútne a relatívne referencie s pomenovanými rozsahmi

Referencie s pomenovanými rozsahmi v sú veľmi flexibilné. Ak napríklad napíšete názov pomenovaného rozsahu do relatívnej bunky, bude sa správať ako zodpovedajúca referencia. Viď obrázok nižšie.

Ale keď ho použijete so vzorcami, bude sa správať ako absolútny. Väčšinu času ich budete používať so vzorcami, takže môžete povedať, že sú predvolene absolútne, ale v skutočnosti sú flexibilné.

Ale môžeme ich tiež urobiť relatívnymi.

Ako vytvoriť relatívne pomenované rozsahy v programe Excel?

Povedzme, či chcem pomenovať rozsah „Pred“, ktorý sa bude vzťahovať na bunku ponechanú kdekoľvek, kde je napísaná. Ako to urobím? Nasleduj tieto kroky:

  • Stlačte kombináciu klávesov CTRL+F3
  • Kliknite na Nový
  • Do sekcie „Názov“ zadajte „Befor“.
  • V časti „Odkazuje na:“ napíšte adresu bunky vľavo. Ak ste napríklad v bunke B1, napíšte „= A2“ do sekcie „Odkazuje na:“. Uistite sa, že neobsahuje znak $.


Teraz, kdekoľvek vo vzorci napíšete „Pred“, bude odkazovať na bunku, ktorá mu je ponechaná.

Tu som predtým používal vo funkcii COLUMN. Vzorec vráti číslo stĺpca ľavej bunky, kde je napísaný. Na moje prekvapenie, A1 zobrazuje číslo stĺpca posledného stĺpca. To znamená, že list je v obehu. Myslel som si, že sa zobrazí chyba #REF.

Pomenujete často používané vzorce?

Teraz je tento úžasný. V pracovnom hárku mnohokrát použijete rovnaký vzorec znova a znova. Môžete napríklad chcieť skontrolovať, či je meno vo vašom zozname zákazníkov alebo nie. A táto potreba sa môže vyskytnúť mnohokrát. Za týmto účelom napíšete zakaždým rovnaký komplexný vzorec.

= IF (COUNTIF (zákazník, I3), „v zozname“, „nie je v zozname“)

Čo keby ste do bunky zadali „= IsInCustomer“ a zobrazilo by sa vám, či je hodnota v ľavej bunke v zozname zákazníkov alebo nie?

Tu som napríklad pripravil stôl. Teraz chcem iba napísať „= IsInCustomer“ do J5 a chcel by som zistiť, či je hodnota v I5 je v zozname zákazníkov alebo nie. Postupujte podľa týchto krokov.

    • Stlačte kombináciu klávesov CTRL+F3
    • Kliknite na Nový
    • Do poľa Name napíšte „IsInCustomer“
    • Do poľa „Vzťahuje sa na“ napíšte svoj vzorec. = IF (COUNTIF (zákazník, I5), „v zozname“, „nie je v zozname“)

  • Kliknite na tlačidlo OK.

Teraz, kdekoľvek zadáte „IsInCustomer“, skontroluje hodnotu v ľavej bunke v zozname zákazníkov.

To vám zabráni opakovať svoje ja znova a znova.

Aplikujte pomenované rozsahy na vzorce

Mnohokrát definujeme názvy pre naše rozsahy potom, čo sme už napísali vzorce na základe rozsahov. Mám napríklad celkovú cenu ako bunky = E2*F2. Ako to môžeme zmeniť? Jednotky*Unit_Cost.

    • Vyberte vzorce.
    • Prejdite na kartu vzorcov. Kliknite na rozbaľovaciu ponuku Definovať meno.

  • Kliknite na položku Použiť mená.
  • Zobrazí sa zoznam všetkých pomenovaných rozsahov. Vyberte správne mená a kliknite na tlačidlo OK.


A názvy sú teraz použité. Môžete to vidieť na paneli vzorcov.

Ľahko čitateľné vzorce s pomenovanými rozsahmi

Ako ste videli, pomenované rozsahy uľahčujú čítanie vzorcov. Ak napíšem = COUNTIF („A2: A100“, B2), nikto nepochopí, čo sa pokúšam spočítať, kým neuvidí údaje alebo im to niekto nevysvetlí.

Ale ak napíšem = COUNTIF (región, „východ“), väčšina používateľov okamžite pochopí, že počítame výskyt „východu“ v regióne s názvom rozsah.

Prenosné vzorce

Pomenované rozsahy uľahčujú kopírovanie a vkladanie vzorcov bez starostí so zmenou referencií. Môžete tiež prevziať jeden vzorec z jedného zošita do druhého a bude to fungovať dobre, pokiaľ cieľový zošit nebude mať rovnaké názvy.

Príklad, ak máte vzorec = COUNTIF (región, východ) v Distribučnej tabuľke a máte ďalší zošit zákazníkov ktorý má tiež pomenovaný rozsah „Región“. Teraz, keď skopírujete tento vzorec priamo kdekoľvek v tomto zošite, zobrazí sa vám správne informácie. Na štruktúre dát nezáleží. Nezáleží na tom, kde je sakra ten stĺpček vo vašom zošite. Bude to fungovať správne.

Na vyššie uvedenom obrázku som použil presne ten istý vzorec v dvoch rôznych súboroch na spočítanie počtu alebo východov vyskytujúcich sa v zozname regiónov. Teraz sú v rôznych stĺpcoch, ale keďže obidva sú pomenované ako región, bude to fungovať perfektne.

Jednoduchá navigácia v zošite

Navigácia v zošite s pomenovanými rozsahmi je teraz jednoduchšia. Stačí zadať meno menovaného do poľa pre meno. Program Excel vás prevedie rozsahom bez ohľadu na to, kde sa v zošite nachádzate. Vzhľadom na to, že pomenovaný rozsah je v rozsahu zošita.
Ak ste napríklad na hárku 10 a chcete získať zoznam zákazníkov, a neviete, na ktorom hárku sa nachádza. Choďte do poľa s názvom a zadajte „zákazník“. V zlomku sekundy budete presmerovaní na pomenovaný rozsah.


Zníži to námahu zapamätať si rozsahy.

Navigujte pomocou hypertextových odkazov s pomenovaným rozsahom

Keď je váš list veľký a často prechádzate z jedného bodu do druhého, radi na jednoduchú navigáciu používate hypertextové odkazy. Dobre pomenované rozsahy môžu perfektne fungovať s hypertextovými odkazmi. Ak chcete pridať hypertextové odkazy pomocou pomenovaných rozsahov, postupujte podľa týchto krokov.

    • Vyberte bunku, kde chcete hypertextový odkaz
    • Stlačte CTRL+K alebo prejdite na kartu Vložiť> HyperLink a otvorte dialógové okno Vložiť hypertextový odkaz.

    • Kliknite na položku Miesto v tomto dokumente.
    • Posuňte sa nadol a v zozname definovaných mien zobrazte dostupné pomenované rozsahy

  • Vyberte Pomenovaný rozsah, do ktorého chcete vložiť hypertextový odkaz.

A je hotovo. Máte hypertextový odkaz na vami zvolený pomenovaný rozsah. Pomocou toho môžete vytvoriť register pomenovaných rozsahov, ktoré môžete vidieť a kliknúť na ne, aby ste sa k nim priamo dostali. Vďaka tomu bude váš zošit skutočne užívateľsky prívetivý.

Pomenovaný rozsah a validácia údajov

Pomenované rozsahy a validácia údajov sú ako stvorené pre seba. Pomenované rozsahy robia validáciu údajov vysoko prispôsobiteľnou. Pridanie overenia zo zoznamu pomocou pomenovaného rozsahu je oveľa jednoduchšie. Pozrime sa, ako…

    • Prejdite na kartu Údaje
    • Kliknite na Overenie údajov
    • Vyberte položku Zoznam v časti „Povoliť:“
    • V časti „Zdroj:“ zadajte „= zákazník“ (napíšte ľubovoľný pomenovaný rozsah)

  • Kliknite na OK

Teraz bude mať táto bunka mená zákazníkov, ktorí sú súčasťou rozsahu s názvom Zákazník. Ľahké, nie?

Závislé alebo kaskádové overenie údajov s pomenovanými rozsahmi

Teraz čo keď chcete kaskádové alebo závislé overenie údajov. Ak napríklad chcete rozbaľovací zoznam s kategóriami, ovocie a zelenina. Ak teraz vyberiete ovocie, v inom rozbaľovacom zozname by sa mala zobraziť iba možnosť ovocia a ak zvolíte zeleninu, potom iba zeleninu.

To sa dá ľahko dosiahnuť pomocou pomenovaných rozsahov. Zistite ako.

  • Závislá rozbaľovacia ponuka pomocou pomenovaného rozsahu
  • Iné spôsoby kaskádovej validácie údajov

Žiadne overenie údajov s názvami tabuľkových údajov

Tabuľky programu Excel síce poskytujú štruktúrované názvy, ale nemožno ich použiť na validáciu údajov a podmienené formátovanie. Neviem, prečo to Excel neumožňuje.
To však neznamená, že sa to nedá. Rozsahy môžete pomenovať v tabuľke a potom ich použiť na overenie. Excel s ním nemá žiadny problém.

Rozsah pomenovaných rozsahov

Doteraz sme hovorili o pomenovaných rozsahoch, ktoré mali rozsah zošita. Čo? Nerozprávali sme sa o tom? Dobre, tak rýchlo pochopme, aký je rozsah pomenovaných rozsahov.

Aký je rozsah rozsahu názvov?

Rozsah dobre definuje, kde je možné rozpoznať rozsah názvov. Akýkoľvek názov nemožno rozpoznať mimo jeho rozsah. Napríklad meno v zošite 1 nemôže byť rozpoznané v inom zošite. Excel ponúka dve možnosti pre rozsah pomenovaných rozsahov hárok a zošit.

Ako definovať rozsah pomenovaného rozsahu?

Keď vytvoríte nový rozsah názvov, zobrazí sa sekcia „Rozsah:“. Kliknite na rozbaľovaciu ponuku a vyberte rozsah rozsahu svojich mien. Po vytvorení pomenovaného rozsahu nemôžete rozsah zmeniť. Preto je lepšie to urobiť predtým. Štandardne je to zošit.

Rozsah zošita

Toto je predvolený rozsah pre pomenovaný rozsah. Názov definovaný s rozsahom zošita je možné použiť v celom zošite, v ktorom je definovaný (nie v iných zošitoch).
Všetky vyššie uvedené príklady mali rozsah zošita.

Rozsah pracovného listu

Názov, ktorý je definovaný s rozsahom pracovného hárka, je možné použiť iba na definovaní pracovného hárka. Napríklad, ak definujem „Celkom“ pre celkovú bunku s rozsahom listu1. Potom bude súčet vykázaný iba na hárku 1. Ostatné listy nerozpoznajú.

Chcem rozsah v programe Excel

Excel nemá globálny alebo povedzme rozsah programu Excel. V skutočnosti by som chcel definovať niektoré mená, ktoré je možné rozpoznať vo všetkých pracovných zošitoch v mojom systéme. Ak niekto vie, ako to môžeme urobiť, dajte mi vedieť.

Úprava rozsahu po vytvorení mien

Nemôžeš. Excel vám po vytvorení neumožňuje upravovať rozsah pomenovaného rozsahu. Pretože všetky pomenované rozsahy na hárku sú v predvolenom nastavení zošitom rozsahu, možno budete chcieť zmeniť ich rozsah na hárok.
Ak to chcete urobiť, urobte kópiu tohto hárka a program Excel urobí každé meno v tomto hárku lokálnym, aby sa predišlo nejednoznačnosti. Teraz môžete pôvodný hárok odstrániť, ak chcete.

Vystrihnúť rozsah názvov pasty

Keď vystrihnete a prilepíte pomenovaný rozsah z jedného cieľa do druhého, odkaz sa zmení na nové miesto. Napríklad, ak máte pomenovaný rozsah „Zákazník“ v A2: A10 a vystrihnete a prilepíte ho na B2: B10, meno zákazníka bude odkazovať na nové umiestnenie B2: B10.

Dynamické pomenované rozsahy v programe Excel

17 úžasných funkcií tabuliek programu Excel

Populárne články:

50 skratiek programu Excel na zvýšenie produktivity

Ako používať funkciu VLOOKUP v programe Excel

Ako používať funkciu COUNTIF v programe Excel

Ako používať funkciu SUMIF v programe Excel