Pomenované rozsahy sú užitočnou, ale často nedostatočne využívanou funkciou programu Microsoft Excel . Pomenované rozsahy môžu uľahčiť pochopenie vzorcov (a ladenie), zjednodušiť vytváranie zložitých tabuliek a zjednodušiť vaše makrá.

Pomenovaný rozsah je len rozsah (buď jedna bunka alebo rozsah buniek), ktorému priradíte názov. Tento názov potom môžete použiť namiesto bežných odkazov na bunky vo vzorcoch, v makrách a na definovanie zdroja pre grafy alebo overenie údajov.

Using a range name, like TaxRate, in place of a standard cell reference, like Sheet2!$C$11, can make a spreadsheet easier to understand and debug/audit.

Using Named Ranges in Excel

For example, let’s look at a simple order form.  Our file includes a fillable order form with a dropdown to select the shipping method, plus a second sheet with a table of shipping costs, and the tax rate.

Version 1 (without named ranges) uses normal A1-style cell references in its formulas (shown in the formula bar below).

Verzia 2 používa pomenované rozsahy, vďaka čomu sú jeho vzorce oveľa jednoduchšie na pochopenie. Pomenované rozsahy tiež uľahčujú zadávanie vzorcov, pretože Excel zobrazí zoznam názvov vrátane názvov funkcií, z ktorých si môžete vybrať, kedykoľvek začnete zadávať názov do vzorca.  Dvakrát kliknite^{(Double-click)} na názov vo výberovom zozname a pridajte ho do svojho vzorca.

Otvorením okna Správca názvov na karte ^{(Name Manager)}Vzorce^(Formulas) sa zobrazí zoznam názvov rozsahov a rozsahov buniek, na ktoré odkazujú.

Ale pomenované rozsahy majú aj iné výhody. V našich vzorových súboroch sa spôsob dopravy vyberá pomocou rozbaľovacej ponuky (overenie údajov) v bunke B13 na Háre1^(Sheet1) . Vybraná metóda sa potom použije na vyhľadanie nákladov na dopravu na Hárok2^(Sheet2) .

Bez pomenovaných rozsahov musia byť rozbaľovacie možnosti zadané ručne, pretože overenie údajov vám neumožní vybrať zdrojový zoznam na inom hárku. Takže všetky možnosti musia byť zadané dvakrát: raz v rozbaľovacom zozname a znova na Hárok2^(Sheet2) . Okrem toho sa tieto dva zoznamy musia zhodovať.

Ak dôjde k chybe v niektorom zo záznamov v niektorom zo zoznamov, vzorec prepravných nákladov vygeneruje pri výbere chybnej voľby chybu #N/A. Pomenovanie zoznamu na Sheet2 ako ShippingMethods odstraňuje oba problémy.

Pri definovaní overenia údajov pre rozbaľovací zoznam môžete odkazovať na pomenovaný rozsah, napríklad jednoducho zadaním =ShippingMethods do zdrojového poľa. To vám umožní použiť zoznam možností, ktoré sú na inom hárku.

A ak rozbaľovacia ponuka odkazuje na skutočné bunky použité pri vyhľadávaní (pre vzorec prepravných nákladov), potom sa možnosti rozbaľovacej ponuky budú vždy zhodovať s vyhľadávacím zoznamom, čím sa zabráni chybám #N/A.

Vytvorte v Exceli pomenovaný rozsah

Ak chcete vytvoriť pomenovaný rozsah, jednoducho vyberte bunku alebo rozsah buniek, ktoré chcete pomenovať, potom kliknite do poľa Názov^{( Name Box)} (kde sa zvyčajne zobrazuje adresa vybratej bunky, len naľavo od panela vzorcov^{(Formula Bar)} ), zadajte názov, ktorý chcete použiť. a stlačte Enter .

Pomenovaný rozsah môžete vytvoriť aj kliknutím na tlačidlo Nový v okne ^(New)Správca^(Manager) názvov . Otvorí sa okno Nové meno^{(New Name)}  , kde môžete zadať nový názov.

V predvolenom nastavení je rozsah, ktorý sa má pomenovať, nastavený na ľubovoľný rozsah vybratý po kliknutí na tlačidlo Nový^(New) , ale tento rozsah môžete upraviť pred alebo po uložení nového názvu.

Všimnite si, že názvy rozsahov nemôžu obsahovať medzery, hoci môžu obsahovať podčiarkovníky a bodky. Vo všeobecnosti by mená mali začínať písmenom a potom by mali obsahovať iba písmená, čísla, bodky alebo podčiarkovníky.

V názvoch sa nerozlišujú veľké a malé písmená, ale použitie reťazca slov s veľkými písmenami, ako je TaxRate alebo December2018Sales , uľahčuje čítanie a rozpoznávanie mien. Nemôžete použiť názov rozsahu, ktorý napodobňuje platný odkaz na bunku, ako napríklad Dog26 .

Pomocou okna Správca názvov môžete upraviť názvy rozsahov alebo zmeniť rozsahy, na ktoré sa vzťahujú.^(Manager)

Všimnite si tiež, že každý pomenovaný rozsah má definovaný rozsah. Normálne bude rozsah predvolene nastavený na Workbook , čo znamená, že na názov rozsahu možno odkazovať odkiaľkoľvek v zošite. 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.

Môžete mať napríklad súbor s údajmi o predaji so samostatnými hárkami pre január^(January) , február^(February) , marec^(March) atď. Každý hárok môže mať bunku (pomenovaný rozsah) s názvom Mesačný predaj^{(MonthlySales)} , ale za normálnych okolností by rozsah každého z týchto názvov bol iba hárok obsahujúci to.

Vzorec =ROUND(MonthlySales,0) by teda poskytol februárové^(February) tržby zaokrúhlené na najbližšie celé doláre, ak je vzorec na februárovom^(February) hárku, ale marcové^(March) tržby, ak je marcový^(March) atď.

Aby ste sa vyhli nejasnostiam v zošitoch s viacerými rozsahmi na samostatných hárkoch s rovnakým názvom alebo jednoducho v komplikovaných zošitoch s desiatkami alebo stovkami pomenovaných rozsahov, môže byť užitočné zahrnúť názov hárka ako súčasť názvu každého rozsahu.

Vďaka tomu je každý názov rozsahu jedinečný, takže všetky názvy môžu mať rozsah zošita . ^(Workbook)Napríklad Január_MesačnýPredaj^{(January_MonthlySales)} , Február_MesačnýPredaj^{(February_MonthlySales)} , Dátum_rozpočtu^{(Budget_Date)} , Dátum_objednávky^(Order_Date) atď.

Dve upozornenia týkajúce sa rozsahu pomenovaných rozsahov:^{(Two cautions regarding the scope of named ranges:)} (1) Rozsah pomenovaného rozsahu po jeho vytvorení nemôžete upraviť a (2) rozsah nového pomenovaného rozsahu môžete určiť iba vtedy, ak ho vytvoríte pomocou tlačidla Nový^(New) v okno Správca mien^{( Name Manager)} .

Ak vytvoríte nový názov rozsahu jeho zadaním do poľa Názov ,^(Box) rozsah sa predvolene nastaví buď na Zošit^(Workbook) (ak neexistuje iný rozsah s rovnakým názvom), alebo na hárok, kde sa názov vytvára. Preto na vytvorenie nového pomenovaného rozsahu, ktorého rozsah je obmedzený na konkrétny hárok, použite tlačidlo „Nový“ správcu názvov.

Nakoniec, pre tých, ktorí píšu makrá, môžu byť názvy rozsahov ľahko odkazované 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 tento názov vzťahuje na danú bunku.

Začnite^(Start) používať pomenované rozsahy v pracovných hárkoch programu Excel^(Excel) a rýchlo oceníte výhody! Užite si to!

Why You Should Be Using Named Ranges in Excel

Named ranges are a useful, but оften underutіlized, feature of Microѕoft Excel.  Named ranges can make formulas easier to understand (and debug), simplify the creation of complicated ѕрrеadsheets, and simplify your macroѕ.

A named range is just a range (either a single cell, or a range of cells) to which you assign a name.  You can then use that name in place of normal cell references in formulas, in macros, and for defining the source for graphs or data validation.

Using a range name, like TaxRate, in place of a standard cell reference, like Sheet2!$C$11, can make a spreadsheet easier to understand and debug/audit.

Using Named Ranges in Excel

For example, let’s look at a simple order form.  Our file includes a fillable order form with a dropdown to select the shipping method, plus a second sheet with a table of shipping costs, and the tax rate.

Version 1 (without named ranges) uses normal A1-style cell references in its formulas (shown in the formula bar below).

Version 2 uses named ranges, making its formulas much easier to understand.  Named ranges also make it easier to enter formulas, since Excel will display a list of names, including function names, that you can pick from, whenever you start to type a name in a formula.  Double-click the name in the pick list to add it to your formula.

Opening the Name Manager window from the Formulas tab displays a list of the range names and the cell ranges they reference.

But named ranges have other benefits also.  In our example files, the shipping method is selected using a dropdown (data validation) in cell B13 on Sheet1.  The selected method is then used to lookup the shipping costs on Sheet2.

Without named ranges, the dropdown choices must be manually entered since data validation will not allow you to select a source list on a different sheet.  So all of the choices must be entered twice: once in the dropdown list, and again on Sheet2.  In addition, the two lists must match.

If an error is made in one of the entries in either list, then the shipping cost formula will generate an #N/A error when the erroneous choice is selected.  Naming the list on Sheet2 as ShippingMethods eliminates both problems.

You can reference a named range when defining the data validation for a dropdown list by simply entering =ShippingMethods in the source field, for example.  This allows you to use a list of choices that are on another sheet.

And if the dropdown is referencing the actual cells used in the lookup (for the shipping cost formula), then the dropdown choices will always match the lookup list, avoiding #N/A errors.

Create a Named Range in Excel

To create a named range, simply select the cell or range of cells you want to name, then click in the Name Box (where the selected cell address is normally displayed, just left of the Formula Bar), type the name you want to use, and press Enter.

You can also create a named range by clicking the New button in the Name Manager window.  This opens a New Name window where you can enter the new name.

By default, the range to be named is set to whatever range is selected when you click the New button, but you can edit that range before or after saving the new name.

Note that range names cannot include spaces, although they can include underscores and periods.  Generally, names should begin with a letter and then contain only letters, numbers, periods, or underscores.

Names are not case-sensitive, but using a string of capitalized words, such as TaxRate or December2018Sales, makes the names easier to read and recognize.  You cannot use a range name that mimics a valid cell reference, such as Dog26.

You can edit your range names or change the ranges they refer to using the Name Manager window.

Note also that each named range has a defined scope.  Normally, the scope will default to Workbook, which means the range name can be referenced from anywhere within the workbook.  However, it is also possible to have two or more ranges with the same name on separate sheets, but within the same workbook.

For example, you might have a sales data file with separate sheets for January, February, March, etc.  Each sheet could have a cell (named range) called MonthlySales, but normally the scope of each of those names would only be the sheet containing it.

Thus, the formula =ROUND(MonthlySales,0) would give February sales, rounded to the nearest whole dollar, if the formula is on the February sheet, but March sales if on the March sheet, etc.

To avoid confusion in workbooks having multiple ranges on separate sheets with the same name or simply complicated workbooks with dozens or hundreds of named ranges, it can be helpful to include the sheet name as part of each range name.

This also makes each range name unique, so that all the names can have a Workbook scope.  For example, January_MonthlySales, February_MonthlySales, Budget_Date, Order_Date, etc.

Two cautions regarding the scope of named ranges: (1) You cannot edit the scope of a named range after it is created, and (2) you can only specify the scope of a new named range if you create it using the New button in the Name Manager window.

If you create a new range name by typing it in the Name Box, the scope will default to either Workbook (if no other range with the same name exists), or to the sheet where the name is being created.  Therefore, to create a new named range whose scope is limited to a particular sheet, use the Name Manager “New” button.

Finally, for those who write macros, range names can be easily referenced in VBA code by simply placing the range name within brackets.  For example, instead of ThisWorkbook.Sheets(1).Cells(2,3) you can simply use [SalesTotal] if that name refers to that cell.

Start using named ranges in your Excel worksheets and you will quickly being to appreciate the benefits! Enjoy!

Related posts

• Ako odstrániť prázdne riadky v Exceli

• Ako používať funkciu Speak Cells v Exceli

• Ako vložiť pracovný hárok programu Excel do dokumentu programu Word

• Ako používať analýzu What-If v Exceli

• Ako opraviť riadok v Exceli

• Vycentrujte údaje pracovného hárka v Exceli na tlač

• Ako zlúčiť údaje vo viacerých súboroch programu Excel

• Pripojenie Excelu k MySQL

• Ako vytvoriť jednoduchú kontingenčnú tabuľku v Exceli

• Ako opraviť chyby #N/A vo vzorcoch programu Excel, ako je VLOOKUP

• 4 spôsoby, ako previesť Excel na Tabuľky Google

• Ako triediť podľa dátumu v Exceli

• Ako napísať vzorec / vyhlásenie IF v Exceli

• Ako rýchlo vložiť viac riadkov v Exceli

• Ako zlúčiť bunky, stĺpce a riadky v Exceli

• Ako otvoriť Word a Excel v núdzovom režime

• 4 spôsoby použitia značky začiarknutia v Exceli

• Ako zdieľať súbor Excel pre jednoduchú spoluprácu

• Základné jednostĺpcové a viacstĺpcové triedenie údajov v tabuľkách Excel

• Ako abecedne písať v Exceli