Именованные диапазоны являются полезной, но часто недостаточно используемой функцией Microsoft Excel. Именованные диапазоны могут облегчить понимание (и отладку) формул, упростить создание сложных электронных таблиц и упростить ваши макросы.
Именованный диапазон - это просто диапазон (либо одна ячейка, либо диапазон ячеек), которому вы назначаете имя. Затем вы можете использовать это имя вместо обычных ссылок на ячейки в формулах, в макросах и для определения источника для графиков или проверки данных.
Использование имени диапазона, например TaxRate, вместо стандартной ссылки на ячейку, например Sheet2! $ C $ 11, может упростить понимание и отладку / аудит электронной таблицы.
Использование именованных диапазонов в Excel
Например, давайте посмотрим на простую форму заказа. Наш файл содержит заполняемую форму заказа с раскрывающимся списком для выбора способа доставки, а также второй лист с таблицей стоимости доставки и ставкой налога.
Версия 1 (без именованных диапазонов) использует обычные ссылки на ячейки в стиле A1 в своих формулах (показано на панели формул ниже).
Версия 2 использует именованные диапазоны, что значительно упрощает понимание формул. Именованные диапазоны также упрощают ввод формул, поскольку Excel будет отображать список имен, включая имена функций, из которых можно выбирать, всякий раз, когда вы начинаете вводить имя в формуле. Дважды щелкните имя в списке выбора, чтобы добавить его в формулу.
При открытии окна « Диспетчер имен» на вкладке « Формулы » отображается список имен диапазонов и диапазонов ячеек, на которые они ссылаются.
Но у названных диапазонов есть и другие преимущества. В наших файлах примеров метод доставки выбирается с использованием раскрывающегося списка (проверка данных) в ячейке B13 на листе 1. Выбранный метод затем используется для поиска стоимости доставки на Листе 2.
Без именованных диапазонов, выпадающий выбор должен быть введен вручную, так как проверка данных не позволит вам выбрать исходный список на другом листе. Таким образом, все варианты должны быть введены дважды: один раз в раскрывающемся списке и снова на листе 2. Кроме того, два списка должны совпадать.
Если в одной из записей в любом списке будет допущена ошибка, то при выборе ошибочного выбора формула стоимости доставки сгенерирует ошибку # Н / Д. Обозначение списка на Sheet2 как ShippingMethods устраняет обе проблемы.
Вы можете ссылаться на именованный диапазон при определении проверки данных для выпадающего списка, просто введя, например, = ShippingMethods в поле источника. Это позволяет вам использовать список вариантов, которые находятся на другом листе.
И если раскрывающийся список ссылается на фактические ячейки, использованные в поиске (для формулы стоимости доставки), то раскрывающиеся варианты всегда будут соответствовать поисковому списку, избегая ошибок # N / A.
Создать именованный диапазон в Excel
Чтобы создать именованный диапазон, просто выберите ячейку или диапазон ячеек, которые вы хотите назвать, затем щелкните в поле « Имя» (где обычно отображается выбранный адрес ячейки слева от панели формул) и введите имя, которое вы хотите использовать. и нажмите Enter .
Вы также можете создать именованный диапазон, нажав кнопку « Создать» в окне диспетчера имен. Откроется окно « Новое имя», где вы можете ввести новое имя.
По умолчанию для именованного диапазона устанавливается любой диапазон, выбранный при нажатии кнопки «Создать», но вы можете редактировать этот диапазон до или после сохранения нового имени.
Обратите внимание, что имена диапазонов не могут включать пробелы, хотя они могут включать подчеркивания и точки. Как правило, имена должны начинаться с буквы, а затем содержать только буквы, цифры, точки или подчеркивания.
Имена не чувствительны к регистру, но использование строки заглавных слов, таких как TaxRate или December2018Sales, облегчает чтение и распознавание имен. Вы не можете использовать имя диапазона, имитирующее действительную ссылку на ячейку, например Dog26.
Вы можете редактировать имена диапазонов или изменять диапазоны, к которым они относятся, с помощью окна диспетчера имен.
Также обратите внимание, что каждый именованный диапазон имеет определенную область видимости. Обычно в качестве области по умолчанию используется Рабочая книга, что означает, что на имя диапазона можно ссылаться из любой точки рабочей книги. Однако также возможно иметь два или более диапазонов с одинаковыми именами на разных листах, но в одной и той же книге.
Например, у вас может быть файл данных о продажах с отдельными листами за январь, февраль, март и т. Д. Каждый лист может иметь ячейку (именованный диапазон) с именем MonthlySales, но обычно областью действия каждого из этих имен будет только лист, содержащий Это.
Таким образом, формула = ROUND (MonthlySales, 0) даст февральские продажи, округленные до ближайшего целого доллара, если формула на февральском листе, а мартовские продажи на мартовском листе и т. Д.
Во избежание путаницы в книгах, имеющих несколько диапазонов на отдельных листах с одинаковым именем, или просто в сложных книгах с десятками или сотнями именованных диапазонов, может быть полезно включить имя листа в качестве части имени каждого диапазона.
Это также делает каждое имя диапазона уникальным, так что все имена могут иметь область действия Workbook. Например, January_MonthlySales, February_MonthlySales, Budget_Date, Order_Date и т. Д.
Два предостережения относительно области именованных диапазонов: (1) Вы не можете редактировать область именованного диапазона после того, как он создан, и (2) вы можете указать область действия нового именованного диапазона, только если вы создаете его с помощью кнопки Создать в окно диспетчера имен .
Если вы создадите новое имя диапазона, введя его в поле «Имя», в качестве области по умолчанию будет задана рабочая книга (если другого диапазона с таким именем не существует) или лист, на котором создается имя. Поэтому, чтобы создать новый именованный диапазон, область действия которого ограничена конкретным листом, используйте кнопку «Создать» в Диспетчере имен.
Наконец, для тех, кто пишет макросы, на имена диапазонов можно легко ссылаться в коде VBA, просто поместив имя диапазона в скобки. Например, вместо ThisWorkbook.Sheets (1) .Cells (2, 3) вы можете просто использовать [SalesTotal], если это имя относится к этой ячейке.
Начните использовать именованные диапазоны в ваших таблицах Excel, и вы быстро оцените преимущества! Наслаждайтесь!
