Computer help - file server


[ Blog ] - [ File Server ] - [ Удаленная компьютерная помощь ]

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

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

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

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





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



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



Открытие окна диспетчера имен на вкладке "Формулы" отображает список имен диапазонов и диапазонов ячеек, на которые они ссылаются.



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

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

Если ошибка будет сделана в одной из записей в любом списке, то формула стоимости доставки выдаст ошибку # N/A при выборе ошибочного выбора.Название списка на Листе 2 как 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, и вы быстро оцените преимущества! Наслаждайтесь!

.
SETUP UA COMPUTER BLOG