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