Хитрости »

Именованные диапазоны

 

Именованным диапазоном может быть как одна отдельная ячейка, так и диапазон. Создать можно несколькими способами. Но независимо от метода создания есть общие правила для имен в Excel. Для чего это может быть нужно: обращение к именованному диапазону гораздо удобнее, чем прописывание адреса в формулах и VBA. Например, Вы ссылаетесь на диапазон A1:C10 в своем коде VBA. Затем у Вы переместили эти данные в другое место листа(скажем в диапазон D2:F11). Теперь Вам придется идти в код и менять адрес диапазона. А если бы Вы задали имя диапазону A1:C10(к примеру Диапазон1), то в коде ничего менять не пришлось бы. Вам надо было бы просто обращаться в коде к имени диапазона и это никак не влияло бы на его адрес.

Как обратиться к именованному дипазону.

Через VBA:

В формулах:

  1. =СУММ(Диапазон1)
  2. =ВПР("Критерий";Диапазон1;2;0)

Так же см.: Как обратиться к диапазону из VBA
Если Вы при указании диапазона в формуле просто выделяете именованный диапазон, то его имя автоматически подставится в формулу вместо адреса.

Некоторые ограничения, накладываемые на создание имен:

  • В качестве имени диапазона не может быть использованы словосочетания, содержащие пробел. Вместо него лучше использовать нижнее подчеркивание _ или точку(например Name_1, Name.1);
  • Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (\). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания;
  • Нельзя в качестве имени использовать зарезервированные константы – R, C и RC(как прописные, так и строчные). А нельзя потому, что данные буквы используются самим Excel для адресации ячеек при использовании стиля R1C1. Имена не могут быть такими же, как ссылки на ячейки, например, B$100 или R1C1;
  • Длина имени не может превышать 255 символов.

Итак, создаем:

Способ первый – обычно при создании простого именованного диапазона я использую именно его. Выделяем ячейку или группу ячеек, имя которым хотим присвоить. Затем щелкаем левой кнопкой мыши в окне адреса(рис.1) и вписываем имя. Жмем Enter. Диапазон создан.

Создание меню через окно адресарис.1

Способ второй-  Выделяем ячейку или группу ячеек. Жмем правую кнопку мыши для вызова контекстного меню ячеек. Выбираем пункт – Имя диапазона(рис.2). Появляется диспетчер создания имен(рис.3). В поле Имя вписываете имя диапазона, в поле Область выбираете область действия создаваемого диапазона – Книга, либо Лист. При выборе Лист, созданный именованный диапазон будет доступен только из выбранного листа. При выборе Области Книга созданный диапазон можно будет использовать из любого листа данной книги. В поле Примечание можно записать пометку о созданном диапазоне, например для каких целей Вы планируете его использовать. Позже эту информацию можно будет посмотреть из диспетчера имен(о нем далее). Диапазон – при данном способе создания в этом поле автоматически проставляется адрес выделенного ранее диапазона. Его можно изменить.

Создание через контекстное менюрис.2

Диспетчер создания именрис.3

Способ третий – жмем Ctrl+F3, либо

в 2007-2010 Excel вкладка Формулы-Диспетчер имен-Создать(либо на той же вкладке сразу – Присвоить имя);
в 2003 Excel - Вставка-Имя-Присвоить.

Появляется Диспетчер создания имен(рис.3). Далее все так же как во втором способе, но необходимо еще указать Диапазон. Можно просто поставить курсор в поле Диапазон и затем просто выделить диапазон на листе, которому хотите присвоить имя.
Так же можно создавать списки с автоматическим определением его размера. Такие диапазоны называют динамическими.

Так же см.:
Как обратиться к диапазону из VBA
Динамические именованные диапазоны

Обсуждение: оставлено 11 коммент.
  1. Может кто то сталкивался с задачей присвоить дипазон ячеек через переменные. Нигде не могу найти способ присвоить функции переменную.
    Например:

    такой способ не работает. Может кто то подскажет как правильнее сделать?

  2. Правильнее будет так, как того требует синтаксис VBA :-)

    Это при условии, что Вам действительно нужно брать адреса строки именно из ячеек. Т.е. если в ячейке Y2 будет 3, а в Z2 – 5, то будут выделены строки с 3 по 5-ую.

  3. Александр:

    Выделить диапазон:
    [“Диапазон”].Select
    А как будет выглядеть строка кода для выделения конкретной ячейки конкретного именного диапазона? (скажем, со значением “Лук”)

  4. Как обычно к ячейкам обращаетесь – так и обращайтесь:
    [Диапазон].Cells(2,1).Select
    И обратите внимание, что при обращении к диапазону, используя квадратные скобки, имя должно быть без кавычек.

  5. Very good topic. I have discovered a good deal something totally new below. Continue.

  6. Добрый день! А как можно изменить диапазон списка? Где они вообще хранятся?

  7. @Алексей
    В меню не знаю, а горячими клавишами Ctrl+F3. Кстати, если хранить массив в переменной типа Variant, то можно задавать многомерный массив также квадратными скобками.

  8. Юлия:

    Добрый день!
    Создала список. В какой-то момент понадобилось изменить лист со списком. Сделала копию листа, внесла нужные изменения. Тут нужно пояснить – диапазон и место расположение на листе самих списков не менялось. Удалила первоначальный лист со списком. Переименовала копию. В диспетчере имен при копировании листа отразились дубли именованных диапазонов. При удалении первоначального листа у дублей появилось #ССЫЛКА. Удалила эти #ССЫЛКИ.
    Предполагала, что связанный список заработает, ан нет. Не заработал пока заново не создала эти же списки (сначала удалила имеющийся список – потом заново его же создала). Когда удаляла списки заметила, что поле «Область:» в окне «Создание имени» стала серенькой.
    Проблема решена, но интересно, почему не заработал с первого раза!
    В таких случая всегда нужно заново создавать списки или есть другой вариант?
    PS
    Копией листа пришлось воспользоваться так как на нем в свою очередь также были настроены выпадающие списки и они получились более совершенные чем в первом варианте листа со списком.

    • Не избежать этого, если не использовать ДВССЫЛ для ссылки на имя листа. Иначе при переименовании листа все будет работать, а вот при удалении – получите #ССЫЛКА!, т.к. лист с данными для списков и формул был удален.

  9. Андрей:

    Дмитрий, а вот такой вопрос. Как изменить Область действия уже созданного диапазона. Например, при создании диапазона, определили область действия Книга, затем потребовалось изменить эту область на Лист. Однако при использовании стандартного способа Изменить не дает такой возможности.
    Как быть?

Поделитесь своим мнением

Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Много работаете в Excel, но понимаете, что используете его не на полную?
Пройдите один из тренингов и работайте в Excel эффективно!Заказать

Вы постоянно выполняете однотипные действия в Excel или Word?
Вам надоела рутина?
Закажите создание макроса(программы) - быстро, качественно, недорого!Заказать

Реклама
Логин
Счетчики
Анализ сайта
Рейтинг@Mail.ru
Яндекс.Метрика
© 2014 Excel для всех  Войти