Версия для печати

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

52707
Написать комментарий К комментариям
Что умеет Excel

 

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

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

Через VBA:

    MsgBox Range("Диапазон1").Address
    MsgBox [Диапазон1].Address
	MsgBox Range("Диапазон1").Address
	MsgBox [Диапазон1].Address

В формулах:

  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
Динамические именованные диапазоны



Поддержать автора сайта
  1. 3 Июнь 2011 в 10:31 | #1

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

    SBegin = Range("Y2").Value
    SEnd = Range("Z2").Value  
    Rows("[SBegin]:[SEnd]").Select
    SBegin = Range("Y2").Value
    SEnd = Range("Z2").Value  
    Rows("[SBegin]:[SEnd]").Select

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

  2. 3 Июнь 2011 в 10:40 | #2

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

    SBegin = Range("Y2").Value
    SEnd = Range("Z2").Value
    Rows(SBegin & ":" & SEnd).Select
    SBegin = Range("Y2").Value
    SEnd = Range("Z2").Value
    Rows(SBegin & ":" & SEnd).Select

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

  3. Александр
    7 Июнь 2011 в 11:41 | #3

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

  4. 7 Июнь 2011 в 12:03 | #4

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

  5. 31 Октябрь 2011 в 01:00 | #5

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

  6. 18 Февраль 2013 в 21:22 | #6

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

  7. 12 Август 2013 в 12:22 | #7

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


Комментарии, не имеющие отношения к комментируемой статье, будут удаляться без уведомления и объяснения причин. Если есть вопрос по проблеме в Excel- добро пожаловаться на Форум

Комментарий будет добавлен после проверки администратором.