Именованные диапазоны
- Общие сведения об именованных диапазонах
- Как обратиться к именованному диапазону(как использовать в формулах и VBA)
- Ограничения для именованных диапазонов
- Создание именованного диапазона
- Изменение именованного диапазона
- Удаление именованного диапазона
Для чего вообще нужны именованные диапазоны? Обращение к именованному диапазону гораздо удобнее, чем прописывание адреса в формулах и VBA:
- Предположим, что в формуле мы ссылаемся на диапазон
(возможно даже не один раз). Для примера возьмем простую функцию СУММ(суммирует значения указанных ячеек):A1:C10
=СУММ( A1:C10 ;F1:K10 )
Затем нам стало необходимо суммировать другие данные(скажем вместо диапазона в диапазоне D2:F11). В случае с обычным указанием диапазона нам придется искать все свои формулы и менять там адрес диапазона на новый. Но если назначить своему диапазонуA1:C10 имя(к примеруA1:C10 ), то в формуле ничего менять не придется - достаточно будет просто изменить ссылку на ячейки в самом имени один раз. Я привел пример с одной формулой - а что, если таких формул 10? 30?ДиапазонСумм
Примерно такая же ситуация и с использованием в кодах: указав имя диапазона один раз не придется каждый раз при изменении и перемещении этого диапазона прописывать его заново в коде. - Именованный диапазон не просто так называется именованным. Если взять пример выше - то отображение в формуле названия
куда нагляднее, чемДиапазонСумм . В сложных формулах куда проще будет ориентироваться по именам, чем по адресам. Почему удобнее: если сменить стиль отображения ссылок (подробнее про стиль), то диапазонA1:C10 будет выглядеть как-то вроде этого:A1:C10 . А если назначить имя - то оно как былоR1C1:R10C3 , так им и останется.ДиапазонСумм - При вводе формулы/функции в ячейку, можно не искать нужный диапазон, а начать вводить лишь первые буквы его имени и Excel предложит его ко вводу:
Данный метод доступен лишь в версиях Excel 2007 и выше
MsgBox Range("ДиапазонСумм").Address
MsgBox [ДиапазонСумм].Address |
Читать подробнее про обращение к диапазонам из VBA
=СУММ( ДиапазонСумм )=ВПР("Критерий"; ДиапазонСумм ;2;0)
Читать подробнее про функцию ВПР
Если при указании диапазона в формуле выделить именованный диапазон, то его имя автоматически подставится в формулу вместо фактического адреса ячеек:
- В качестве имени диапазона не могут быть использованы словосочетания, содержащие пробел. Вместо него лучше использовать нижнее подчеркивание _ или точку: Name_1, Name.1
- Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (\). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания
- Нельзя в качестве имени использовать зарезервированные в Excel константы - R, C и RC(как прописные, так и строчные). Связано с тем, что данные буквы используются самим Excel для адресации ячеек при использовании стиля ссылок R1C1 (читать подробнее про стили ссылок)
- Нельзя давать именам названия, совпадающие с адресацией ячеек: B$100, D2(для стиля ссылок А1) или R1C1, R7(для стиля R1C1). И хотя при включенном стиле ссылок R1C1 допускается дать имени название вроде A1 или D130 - это не рекомендуется делать, т.к. если впоследствии стиль отображения ссылок для книги будет изменен - то Excel не примет такие имена и предложит их изменить. И придется изменять названия всех подобных имен. Если очень хочется - можно просто добавить нижнее подчеркивание к имени: _A1
- Длина имени не может превышать 255 символов
обычно при создании простого именованного диапазона я использую именно его. Выделяем ячейку или группу ячеек, имя которым хотим присвоить -щелкаем левой кнопкой мыши в окне адреса и вписываем имя, которое хотим присвоить. Жмем Enter:
Выделяем ячейку или группу ячеек. Жмем правую кнопку мыши для вызова контекстного меню ячеек. Выбираем пункт:
- Excel 2007: Имя диапазона
(Range Name) - Excel 2010: Присвоить имя
(Define Name)
Жмем
- 2007-2016 Excel: вкладка Формулы
(Formulas) -Диспетчер имен(Name Manager) -Создать(New) (либо на той же вкладке сразу - Присвоить имя(Define Name) ) - 2003 Excel: Вставка -Имя -Присвоить
Появляется окно создания имени
Имя
Область
- Лист1
(Sheet1) - созданный именованный диапазон будет доступен только из указанного листа. Это позволяет указать разные диапазоны для разных листов, но указав одно и тоже имя диапазона - Книга
(Workbook) - созданный диапазон можно будет использовать из любого листа данной книги
Примечание
Диапазон
Чтобы изменить имя Именованного диапазона, либо ссылку на него необходимо всего лишь вызывать диспетчер имен(
Изменить можно имя диапазона
Чтобы удалить Именованный диапазон необходимо вызывать диспетчер имен(
Так же можно создавать списки с автоматическим определением его размера. Например, если значения в списке периодически пополняются или удаляются и чтобы каждый раз не переопределять границы таких диапазонов. Такие диапазоны называют динамическими.
Так же см.:
Как обратиться к диапазону из VBA
Динамические именованные диапазоны
Статья помогла? Поделись ссылкой с друзьями!
Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Может кто то сталкивался с задачей присвоить дипазон ячеек через переменные. Нигде не могу найти способ присвоить функции переменную.
Например:
такой способ не работает. Может кто то подскажет как правильнее сделать?
Правильнее будет так, как того требует синтаксис VBA :-)
Это при условии, что Вам действительно нужно брать адреса строки именно из ячеек. Т.е. если в ячейке Y2 будет 3, а в Z2 - 5, то будут выделены строки с 3 по 5-ую.
Выделить диапазон:
["Диапазон"].Select
А как будет выглядеть строка кода для выделения конкретной ячейки конкретного именного диапазона? (скажем, со значением "Лук")
Как обычно к ячейкам обращаетесь - так и обращайтесь:
[Диапазон].Cells(2,1).Select
И обратите внимание, что при обращении к диапазону, используя квадратные скобки, имя должно быть без кавычек.
Добрый день! А как можно изменить диапазон списка? Где они вообще хранятся?
В меню не знаю, а горячими клавишами Ctrl+F3. Кстати, если хранить массив в переменной типа Variant, то можно задавать многомерный массив также квадратными скобками.
Добрый день!
Создала список. В какой-то момент понадобилось изменить лист со списком. Сделала копию листа, внесла нужные изменения. Тут нужно пояснить – диапазон и место расположение на листе самих списков не менялось. Удалила первоначальный лист со списком. Переименовала копию. В диспетчере имен при копировании листа отразились дубли именованных диапазонов. При удалении первоначального листа у дублей появилось #ССЫЛКА. Удалила эти #ССЫЛКИ.
Предполагала, что связанный список заработает, ан нет. Не заработал пока заново не создала эти же списки (сначала удалила имеющийся список – потом заново его же создала). Когда удаляла списки заметила, что поле «Область:» в окне «Создание имени» стала серенькой.
Проблема решена, но интересно, почему не заработал с первого раза!
В таких случая всегда нужно заново создавать списки или есть другой вариант?
PS
Копией листа пришлось воспользоваться так как на нем в свою очередь также были настроены выпадающие списки и они получились более совершенные чем в первом варианте листа со списком.
Не избежать этого, если не использовать ДВССЫЛ для ссылки на имя листа. Иначе при переименовании листа все будет работать, а вот при удалении - получите #ССЫЛКА!, т.к. лист с данными для списков и формул был удален.
Дмитрий, а вот такой вопрос. Как изменить Область действия уже созданного диапазона. Например, при создании диапазона, определили область действия Книга, затем потребовалось изменить эту область на Лист. Однако при использовании стандартного способа Изменить не дает такой возможности.
Как быть?
Андрей, стандартно никак не сделать. По сути не все так просто и даже одной строкой кода не отделаться.Управление именами
Я делал такой функционал в одной из команд своей надстройки MulTEx:
у меня есть файл
http://rghost.ru/7r2vZ2Sjc
в М15 стоит формула с именами, при копироании и даже просто при редактировании, имена подменяются своими относитльными адресами. И почти все другие ячейки тоже. Там все ячейки -- копии первой строки, но я не могу их копировать -- пропадают имена. Что делать ?
Здравствуйте!
Подскажите пожалуйста. Имеется именованный диапазон в котором 1 ячейка с данными, если отображать так, то всё показывается.
Sub macro1()
MsgBox ThisWorkbook.Sheets("Data").Range("vlad").Value
End Sub
Если присваивать значение другой переменной и отобразить эту переменную, то ошибка
Sub macro2()
Dim test As Integer
test = ThisWorkbook.Sheets("Data").Range("vlad").Value
MsgBox test
End Sub
В чём может быть проблема отображения переменной по второму варианту? и как присваивать и отображать именованный диапазон, если там реально диапазон а не одна ячейка? Примного благодарен.