Pomenované rozsahy sú užitočnou, ale často nedostatočne využívanou funkciou programu Microsoft Excel. Pomenované rozsahy môžu pomôcť pochopiť vzorce (a ladiť), zjednodušiť vytváranie komplikovaných tabuliek a zjednodušiť makrá.
Pomenovaný rozsah je len rozsah (buď jedna bunka alebo rozsah buniek), ku ktorému priraďujete názov. Potom môžete použiť tento názov namiesto bežných odkazov na bunky vo vzorcoch, makrách a na určenie zdroja pre grafy alebo overenie údajov.
Použitie názvu rozsahu, napríklad TaxRate, namiesto štandardného odkazu na bunku, ako napríklad Sheet2! $ C $ 11, umožňuje ľahšie pochopiť tabuľku a ladiť / overiť.
Používanie pomenovaných rozsahov v programe Excel
Pozrime sa napríklad na jednoduchý objednávkový formulár. Náš súbor obsahuje vyplniteľný objednávkový formulár s rozbaľovacou ponukou na výber spôsobu doručenia a druhý list s tabuľkou nákladov na dopravu a sadzbou dane.
Verzia 1 (bez pomenovaných rozsahov) používa vo svojich vzorcoch bežné odkazy na bunky typu A1 (zobrazené vo formulári nižšie).
Verzia 2 používa pomenované rozsahy, takže jeho vzorce sú oveľa ľahšie pochopiteľné. Pomenované rozsahy tiež uľahčujú zadávanie vzorcov, pretože v programe Excel sa zobrazí zoznam mien vrátane názvov funkcií, z ktorých si môžete vybrať, vždy, keď začnete písať meno vo vzorci. Dvakrát kliknite na meno v zozname na výber a pridajte ho do vzorca.
Otvorením okna Správca mien z karty Vzorové zobrazuje zoznam názvov oblastí a rozsahov buniek, ktoré odkazujú.
Avšak pomenované rozsahy majú aj iné výhody. V našich príkladoch je metóda odosielania vybratá pomocou rozbaľovacieho okna (validácia údajov) v bunke B13 na hárku1. Vybraná metóda sa potom použije na vyhľadanie nákladov na dopravu na hárku 2.
Bez pomenovaných rozsahov musia byť ručne zadané voľby v rozbaľovacej ponuke, pretože validácia údajov neumožňuje vybrať zdrojový zoznam na inom hárku. Takže všetky voľby musia byť zadané dvakrát: raz v rozbaľovacom zozname a opäť na hárku2. Okrem toho sa dva zoznamy musia zhodovať.
Ak sa v jednej z položiek v jednom zo zoznamov vyskytne chyba, potom vzorec nákladov na dodanie vygeneruje chybu # N / A, keď je vybratá chyba. Pomenovanie zoznamu na Sheet2 ako ShippingMethods eliminuje oba problémy.
Pri definovaní overenia údajov v rozbaľovacom zozname môžete spomenúť pomenovaný rozsah jednoduchým zadaním napríklad = DeliveryMethods do zdrojového poľa. To vám umožňuje použiť zoznam možností, ktoré sú na inom hárku.
A ak rozbaľovací zoznam odkazuje na skutočné bunky použité vo vyhľadávaní (pre vzorec nákladov na dopravu), potom sa rozbaľovacie voľby vždy zhodujú so zoznamom vyhľadávania, čím sa zabráni chybám # N / A.
Vytvorte pomenovaný rozsah v programe Excel
Ak chcete vytvoriť pomenovaný rozsah, stačí vybrať bunku alebo rozsah buniek, ktoré chcete pomenovať, potom kliknite na pole Názov (kde sa normálne zobrazuje vybratá adresa bunky, vľavo od lišty vzorcov) zadajte meno, ktoré chcete použiť a stlačte kláves Enter .
Môžete tiež vytvoriť pomenovaný rozsah kliknutím na tlačidlo Nové v okne Správca mien. Otvorí sa okno Nové meno, do ktorého môžete zadať nové meno.
Štandardne je rozsah, ktorý sa má pomenovať, nastavený na akýkoľvek rozsah, ktorý sa zvolí po kliknutí na tlačidlo Nové, ale tento rozsah môžete upraviť pred uložením nového mena alebo po jeho uložení.
Upozorňujeme, že názvy rozsahov nemôžu obsahovať medzery, aj keď môžu obsahovať podčiarknuté a periódy. Vo všeobecnosti by sa názvy mali začínať písmenom a obsahovať iba písmená, čísla, obdobia alebo podčiarknuté.
Názvy nie sú citlivé na veľké a malé písmená, ale pomocou reťazca veľkých písmen, napríklad taxRate alebo December2018Sales, uľahčuje čítanie a rozpoznanie mien. Nemôžete použiť názov rozsahu, ktorý napodobňuje platný odkaz na bunku, napríklad Dog26.
Môžete upraviť názvy rozsahu alebo zmeniť rozsahy, na ktoré odkazujú, pomocou okna Správca mien.
Upozorňujeme tiež, že každý pomenovaný rozsah má definovaný rozsah. Za normálnych okolností bude predvolené nastavenie pre pracovný zošit, čo znamená, že názov rozsahu môže byť odkazovaný odkiaľkoľvek v rámci pracovného zošita. Je však tiež možné mať dva alebo viac rozsahov s rovnakým názvom na samostatných hárkoch, ale v rámci toho istého zošita.
Napríklad môžete mať súbor údajov o predaji so samostatnými listami pre január, február, marec atď. Každý list by mohol mať bunku (pomenovaný rozsah) nazvanú MonthlySales, ale zvyčajne rozsah každého z týchto mien by bol iba list obsahujúci ono.
Takže vzorec = ROUND (MonthlySales, 0) by priniesol februárový predaj, zaokrúhlený na najbližší celý dolár, ak je vzorec na februárovom liste, ale marcové tržby, ak je na marcovom liste atď.
Aby ste sa vyhli nedorozumeniu v pracovných knižkách s viacerými rozsahmi na samostatných hárkoch s rovnakým názvom alebo jednoducho zložitými pracovnými knižkami s desiatkami alebo stovkami pomenovaných rozsahov, môže byť užitočné zahrnúť meno listu ako súčasť každého názvu rozsahu.
To tiež robí každý názov rozsahu jedinečný, takže všetky mená môžu mať rozsah zošity. Napríklad January_MonthlySales, February_MonthlySales, Budget_Date, Order_Date, atď.
Dve upozornenia týkajúce sa rozsahu pomenovaných rozsahov: (1) Nemôžete upraviť rozsah pomenovaného rozsahu po jeho vytvorení a (2) môžete určiť rozsah nového pomenovaného rozsahu len vtedy, ak ho vytvoríte pomocou tlačidla Nové v okno Správca mien .
Ak vytvoríte nový názov rozsahu tým, že ho zadáte do poľa Názov, rozsah bude predvolene buď v zošite (ak neexistuje žiadny iný rozsah s rovnakým názvom) alebo v hárku, na ktorom sa názov vytvára. Preto, ak chcete vytvoriť novú pomenovanú oblasť, ktorej rozsah je obmedzený na určitý hárok, použite tlačidlo Správca mena "Nové".
Nakoniec, pre tých, ktorí píšu makrá, je možné ľahko odkazovať na názvy rozsahu v kóde VBA jednoduchým umiestnením názvu rozsahu do zátvoriek. Napríklad namiesto ThisWorkbook.Sheets (1) .Cells (2, 3) môžete jednoducho použiť [SalesTotal], ak sa toto meno týka tejto bunky.
Začnite používať pomenované rozsahy v pracovných hárkoch programu Excel a budete rýchlo oceniť výhody! Užite si to!
