Именованные диапазоны — полезная, но часто недостаточно используемая функция Microsoft Excel . Именованные диапазоны могут упростить понимание (и отладку) формул, упростить создание сложных электронных таблиц и упростить макросы.

Именованный диапазон — это просто диапазон (либо одна ячейка, либо диапазон ячеек), которому вы назначаете имя. Затем вы можете использовать это имя вместо обычных ссылок на ячейки в формулах, в макросах и для определения источника для графиков или проверки данных.

Использование имени диапазона, например TaxRate , вместо стандартной ссылки на ячейку, например Sheet2 !$C$11, может упростить понимание и отладку/аудит электронной таблицы.

Использование именованных диапазонов в Excel

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

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

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

При открытии окна « Диспетчер имен » на вкладке « ^{(Name Manager)}Формулы^(Formulas) » отображается список имен диапазонов и диапазонов ячеек, на которые они ссылаются.

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

Без именованных диапазонов варианты раскрывающегося списка необходимо вводить вручную, поскольку проверка данных не позволит вам выбрать исходный список на другом листе. Таким образом, все варианты должны быть введены дважды: один раз в раскрывающемся списке и еще раз на Листе2^(Sheet2) . Кроме того, два списка должны совпадать.

Если в одном из пунктов любого из списков допущена ошибка, то формула стоимости доставки выдаст ошибку #Н/Д при выборе ошибочного варианта. Именование списка на 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, bυt often underutilized, feature of Microsoft Excеl.  Named ranges can make formulas eaѕier to υnderstand (and debug), sіmplify the сreation of complicated spreadsheets, and simрlify 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

• Как использовать анализ «что, если» в Excel

• Как исправить строку в Excel

• Как использовать Flash Fill в Excel

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

• Сохранять ссылки на ячейки при копировании формулы в Excel

• Как создать простую сводную таблицу в Excel

• Используйте имена динамических диапазонов в Excel для гибких раскрывающихся списков

• Как написать формулу/оператор ЕСЛИ в Excel

• Как использовать функцию PMT в Excel

• Группировать строки и столбцы на листе Excel

• Центрируйте данные рабочего листа в Excel для печати

• 2 способа использования функции транспонирования Excel

• 5 способов преобразовать текст в числа в Excel

• 4 способа конвертировать Excel в Google Таблицы

• Как использовать абсолютные ссылки в Excel

• Как рассчитать Z-показатель в Excel

• Что такое массив VBA в Excel и как его запрограммировать