Именуваните диапазони са полезна, но често недостатъчно използвана функция на Microsoft Excel . Именуваните диапазони могат да направят формулите по-лесни за разбиране (и отстраняване на грешки), да опростят създаването на сложни електронни таблици и да опростят вашите макроси.

Именуван диапазон е просто диапазон (или една клетка, или диапазон от клетки), на който присвоявате име. След това можете да използвате това име вместо нормални препратки към клетки във формули, макроси и за дефиниране на източника за графики или валидиране на данни.

Използването на име на диапазон, като TaxRate , вместо стандартна препратка към клетка, като Sheet2 !$C$11, може да направи електронната таблица по-лесна за разбиране и отстраняване на грешки/одит.

Използване на именувани диапазони в Excel

Например, нека разгледаме прост формуляр за поръчка.  Нашият файл включва формуляр за поръчка за попълване с падащо меню за избор на метод на доставка, плюс втори лист с таблица с разходите за доставка и данъчната ставка.

Версия 1 (без именувани диапазони) използва нормални препратки към клетки в стил A1^(A1-style) във формулите си (показани в лентата с формули по-долу).

Версия 2 използва именувани диапазони, което прави формулите й много по-лесни за разбиране. Наименуваните диапазони също улесняват въвеждането на формули, тъй като Excel ще покаже списък с имена, включително имена на функции, от които можете да избирате, когато започнете да въвеждате име във формула.  Щракнете двукратно върху^{(Double-click)} името в списъка за избор, за да го добавите към вашата формула.

Отварянето на прозореца на Мениджър на имена от раздела ^{(Name Manager)}Формули^(Formulas) показва списък с имената на диапазоните и диапазоните от клетки, които те препращат.

Но посочените диапазони имат и други предимства. В нашите примерни файлове методът на доставка е избран с помощта на падащо меню (проверка на данни) в клетка B13 на Sheet1 . След това избраният метод се използва за търсене на разходите за доставка на Sheet2 .

Без именувани диапазони, падащите опции трябва да бъдат въведени ръчно, тъй като проверката на данните няма да ви позволи да изберете списък с източници на друг лист. Така че всички опции трябва да бъдат въведени два пъти: веднъж в падащия списък и отново в Sheet2 . Освен това двата списъка трябва да съвпадат.

Ако е направена грешка в един от записите в който и да е списък, тогава формулата за разходите за доставка ще генерира грешка #N/A, когато бъде избран грешен избор. Именуването на списъка на Sheet2 като ShippingMethods елиминира и двата проблема.

Можете да се позовавате на именуван диапазон, когато определяте валидирането на данни за падащ списък, като например просто въведете =ShippingMethods в полето източник. Това ви позволява да използвате списък с опции, които са на друг лист.

И ако падащото меню препраща към действителните клетки, използвани в търсенето (за формулата за разходите за доставка), тогава изборът на падащото меню винаги ще съответства на списъка за търсене, като се избягват грешки #N/A.

Създайте наименован диапазон в Excel

За да създадете наименуван диапазон, просто изберете клетката или диапазона от клетки, които искате да наименувате, след това щракнете в полето за име^{( Name Box)} (където обикновено се показва избраният адрес на клетка, точно вляво от лентата с формули^{(Formula Bar)} ), въведете името, което искате да използвате и натиснете Enter .

Можете също да създадете именуван диапазон, като щракнете върху бутона Нов в прозореца ^(New)Мениджър^(Manager) на имената . Това отваря прозорец за ново име^{(New Name)}  , където можете да въведете новото име.

По подразбиране диапазонът, който трябва да бъде наименуван, е зададен на всеки диапазон, избран, когато щракнете върху бутона Нов^(New) , но можете да редактирате този диапазон преди или след запазване на новото име.

Имайте предвид, че имената на диапазони не могат да включват интервали, въпреки че могат да включват долни черти и точки. Обикновено имената трябва да започват с буква и след това да съдържат само букви, цифри, точки или долни черти.

Имената не са чувствителни към главни букви, но използването на низ от думи с главни букви, като например TaxRate или December2018Sales , прави имената по-лесни за четене и разпознаване. Не можете да използвате име на диапазон, което имитира валидна препратка към клетка, като Dog26 .

Можете да редактирате имената на вашите диапазони или да промените диапазоните, към които се отнасят, като използвате прозореца на Мениджър^(Manager) на имена .

Имайте предвид също, че всеки наименуван диапазон има дефиниран обхват. Обикновено обхватът по подразбиране ще бъде Работна книга^(Workbook) , което означава, че името на диапазона може да се препраща от всяко място в работната книга. Възможно е също така да имате два или повече диапазона със същото име на отделни листове, но в рамките на една и съща работна книга.

Например, може да имате файл с данни за продажбите с отделни листове за януари^(January) , февруари^(February) , март^(March) и т.н. Всеки лист може да има клетка (именуван диапазон), наречена MonthlySales , но обикновено обхватът на всяко от тези имена ще бъде само листът, съдържащ то.

По този начин формулата =ROUND(MonthlySales,0) ще даде продажби за февруари^(February) , закръглени до най-близкия цял долар, ако формулата е на февруарския^(February) лист, но продажбите през март, ако е на мартенския ^(March)лист^(March) и т.н.

За да избегнете объркване в работни книги с множество диапазони на отделни листове със същото име или просто сложни работни книги с десетки или стотици наименувани диапазони, може да бъде полезно да включите името на листа като част от всяко име на диапазон.

Това също прави всяко име на диапазон уникално, така че всички имена да имат обхват на работната книга^(Workbook) . Например януари_месечни продажби^{(January_MonthlySales)} , февруари_месечни^{(February_MonthlySales)} продажби , бюджет_дата^{(Budget_Date)} , дата_на поръчка^(Order_Date) и т.н.

Две предупреждения относно обхвата на наименуваните диапазони:^{(Two cautions regarding the scope of named ranges:)} (1) Не можете да редактирате обхвата на наименуван диапазон, след като е създаден, и (2) можете да посочите обхвата на нов наименуван диапазон само ако го създадете с помощта на бутона Нов^(New) в прозореца Мениджър^{( Name Manager)} на имената .

Ако създадете ново име на диапазон, като го напишете в полето за име ,^(Box) обхватът по подразбиране ще бъде или Работна книга^(Workbook) (ако не съществува друг диапазон със същото име), или към листа, където се създава името. Следователно, за да създадете нов именуван диапазон, чийто обхват е ограничен до конкретен лист, използвайте бутона „Ново“ на мениджъра на имена.

И накрая, за тези, които пишат макроси, имената на диапазони могат лесно да бъдат посочени във VBA код, като просто поставите името на диапазона в скоби. Например, вместо ThisWorkbook.Sheets (1).Cells(2,3) можете просто да използвате [ SalesTotal ], ако това име се отнася за тази клетка.

Започнете^(Start) да използвате наименувани диапазони във вашите работни листове на Excel и бързо ще оцените предимствата! Наслади се!

Why You Should Be Using Named Ranges in Excel

Named ranges are a useful, but often underutilized, featυre of Microsoft Excel.  Named ranges can make formulas easier to understand (and debug), simplify the creation of complicаted spreadsheets, and simplifу your macros.

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

• Как да изтриете празни редове в Excel

• Как да използвате функцията "Говорете клетки" на Excel

• Как да вмъкнете работен лист на Excel в Word Doc

• Как да използвате анализа какво-ако на Excel

• Как да коригирате ред в Excel

• Запазване на препратки към клетки при копиране на формула в Excel

• Как да премахнете дублиращи се редове в Excel

• Разлики между Microsoft Excel онлайн и Excel за настолен компютър

• Как да използвате изявления If и вложени If в Excel

• Как да обедините клетки, колони и редове в Excel

• Кога да използвате Index-Match вместо VLOOKUP в Excel

• Как да създадете множество свързани падащи списъци в Excel

• Как да отворите Word и Excel в безопасен режим

• Бързо изписвайте числа в Word и Excel

• Използвайте имена на динамични диапазони в Excel за гъвкави падащи менюта

• Използване на инструмента за търсене на цел на анализа какво-ако

• Разширено VBA ръководство за MS Excel

• 4 начина да използвате отметка в Excel

• Как да споделяте файл на Excel за лесно сътрудничество

• Как да създадете VBA макрос или скрипт в Excel