Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel vba именованный диапазон

Excel vba именованный диапазон

Сообщения: 105
Благодарности: 10

Профиль | Отправить PM | Цитировать

Здраствуйте всем.
Мне нужно создать именованный диапазон с привязкой на конкретную ячейку (на листе в эксель)
если делать это через Вставка — имя — присвоить то в коде даётся выражение activeworkbook.names.add name . referstoR1C1 = R100C5
а мне нужно чтобы вместо ссылки вида R1C1 было в виде (А100:Е5) или cells(100,5).
Пробовал сам писать выражение Activeworkbook.Names.Add Name:= .
VBA — пишет ошибку 1004 — недопустимое имя или выражение.

Имя диапазона берётся из значения переменной FIO
координаты ячейки узнаются по activecell.row
мне нужно подставить значения FIO и activecell.row в выражение Activeworkbook.names.add name.

Как это правильно написать?
К сожалению в английском не силён.

——-
коллекционирую выдвижные столики. для кофе.

Сообщения: 25778
Благодарности: 7508

Если же вы забыли свой пароль на форуме, то воспользуйтесь данной ссылкой для восстановления пароля.

Сообщения: 105
Благодарности: 10

Изображения

прмер.jpg
(273.1 Kb, 12 просмотров)

На рисунке указана ячейка от которой будет строится следующая именованная область, или именованный диапазон при появлении нового персонажа стада.
координаты место положения ячейки относительно предидущего созданного именованного диапазона — всегда одни и теже: в первом столбце, через две строки.
Имя именованного диапазона — задаем через инпутбокс — и далее в переменную
координаты каждой новой ячейки — через activecell.row — и далее в переменную
Вопрос как при создании именованной области (именованного диапазона) увязать имя диапазона выраженное в значении переменной, и координаты ячейка выраженные через переменную значение которой получено через activecell.row

вот так — код работает
ActiveWorkbook.Names.Add Name:=»буйвол_Дортмунд», RefersToR1C1:= _
«=Лист1!R31C1»

по другому — пишет ошибка 1004 — недопустимое значение.

——-
коллекционирую выдвижные столики. для кофе.

Сообщения: 105
Благодарности: 10

Я готов уточнить условия задачи

ActiveWorkbook.Names.Add Name:=»буйвол_Дортмунд», RefersToR1C1:= _
«=Лист1!R31C1»

с помощью этого — задается именованная область.

я хочу уйти от refersToR1C1 и задавать этот диапазон в виде:

ActiveWorkbook.Names.Add Name:=»буйвол_Дортмунд», RefersTo.. := _
«=Лист1!cells(row,column).select

ActiveWorkbook.Names.Add Name:=perenennaya, RefersTo := _
«=Лист1!range(«A» & «peremennaya2»)

где peremennaya принимает значения имени будующей именованной области
а peremennaya2 — номер строки будущей именованной области.

——-
коллекционирую выдвижные столики. для кофе.

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

Для чего вообще нужны именованные диапазоны? Обращение к именованному диапазону гораздо удобнее, чем прописывание адреса в формулах и VBA:

  • Предположим, что в формуле мы ссылаемся на диапазон A1:C10 (возможно даже не один раз). Для примера возьмем простую функцию СУММ(суммирует значения указанных ячеек):
    =СУММ( A1:C10 ; F1:K10 )
    Затем нам стало необходимо суммировать другие данные(скажем вместо диапазона A1:C10 в диапазоне D2:F11 ). В случае с обычным указанием диапазона нам придется искать все свои формулы и менять там адрес диапазона на новый. Но если назначить своему диапазону A1:C10 имя(к примеру ДиапазонСумм ), то в формуле ничего менять не придется — достаточно будет просто изменить ссылку на ячейки в самом имени один раз. Я привел пример с одной формулой — а что, если таких формул 10? 30?
    Примерно такая же ситуация и с использованием в кодах: указав имя диапазона один раз не придется каждый раз при изменении и перемещении этого диапазона прописывать его заново в коде.
  • Именованный диапазон не просто так называется именованным. Если взять пример выше — то отображение в формуле названия ДиапазонСумм куда нагляднее, чем A1:C10 . В сложных формулах куда проще будет ориентироваться по именам, чем по адресам. Почему удобнее: если сменить стиль отображения ссылок (подробнее про стиль), то диапазон A1:C10 будет выглядеть как-то вроде этого: R1C1:R10C3 . А если назначить имя — то оно как было ДиапазонСумм , так им и останется.
  • При вводе формулы/функции в ячейку, можно не искать нужный диапазон, а начать вводить лишь первые буквы его имени и Excel предложит его ко вводу:

    Данный метод доступен лишь в версиях Excel 2007 и выше

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

MsgBox Range(«ДиапазонСумм»).Address MsgBox [ДиапазонСумм].Address

Обращение к именованному диапазону в формулах/функциях

Если при указании диапазона в формуле выделить именованный диапазон, то его имя автоматически подставится в формулу вместо фактического адреса ячеек:

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

  • В качестве имени диапазона не могут быть использованы словосочетания, содержащие пробел. Вместо него лучше использовать нижнее подчеркивание _ или точку: 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)


либо:
Жмем Ctrl + F3
либо:

  • 2007-2016 Excel : вкладка Формулы (Formulas)Диспетчер имен (Name Manager)Создать (New) (либо на той же вкладке сразу — Присвоить имя (Define Name) )
  • 2003 Excel : ВставкаИмяПрисвоить

Появляется окно создания имени

Имя (Name) — указывается имя диапазона. Необходимо учитывать ограничения для имен, которые я описывал в начале статьи.
Область (Scope) — указывается область действия создаваемого диапазона — Книга , либо Лист1 :

  • Лист1 (Sheet1) — созданный именованный диапазон будет доступен только из указанного листа. Это позволяет указать разные диапазоны для разных листов, но указав одно и тоже имя диапазона
  • Книга (Workbook) — созданный диапазон можно будет использовать из любого листа данной книги

Примечание (Comment) — здесь можно записать пометку о созданном диапазоне, например для каких целей планируется его использовать. Позже эту информацию можно будет увидеть из диспетчера имен ( Ctrl + F3 )
Диапазон (Refers to) — при данном способе создания в этом поле автоматически проставляется адрес выделенного ранее диапазона. Его можно при необходимости тут же изменить.

Изменение диапазона
Чтобы изменить имя Именованного диапазона, либо ссылку на него необходимо всего лишь вызывать диспетчер имен( Ctrl + F3 ), выбрать нужное имя и нажать кнопку Изменить (Edit. ) .
Изменить можно имя диапазона (Name) , ссылку (RefersTo) и Примечание (Comment) . Область действия (Scope) изменить нельзя, для этого придется удалить текущее имя и создать новое, с новой областью действия.

Удаление диапазона
Чтобы удалить Именованный диапазон необходимо вызывать диспетчер имен( Ctrl + F3 ), выбрать нужное имя и нажать кнопку Удалить (Delete. ) .

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

Статья помогла? Поделись ссылкой с друзьями!

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

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

​Смотрите также​Использовать в формуле​ одно очень полезное​ содержащим данные, описательные​ комиссионных с продаж.​Урок подготовлен для Вас​,​Formulas​ сам подсказывать имя​

​ один и тот​B11.​в поле Область выберите​ задачи, можно, конечно,​ не написали формулу​ на любом листе​Присвоим Имя Продажи диапазону​

​ конечно, диапазону​Обычно ссылки на диапазоны​, который находится на​ преимущество – возможность​ имена. Например, назначим​ На рисунке ниже​ командой сайта office-guru.ru​высота:​(Формулы) выберите​ диапазона! Для этого​​ же диапазон:​​Затем, с помощью​ лист​ создать 4 именованных​=СУММ(Продажи) – суммирование​ книги;​

​B2:B10​​B2:B10​ ячеек вводятся непосредственно​ вкладке​ быстро выделять эти​ диапазону B2:В13 имя​ представлена таблица, которая​

​Источник: http://www.excel-easy.com/examples/dynamic-named-range.html​COUNTA($A:$A)​Name Manager​ достаточно ввести первую​=СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8)​ Маркера заполнения, скопируем​4сезона​

Задача1 (Именованный диапазон с абсолютной адресацией)

​ диапазона с абсолютной​ будет производиться по​убедитесь, что в поле​. При создании имени​

​присвоено имя Продажи),​​ в формулы, например​​Формулы​ области. Например, чтобы​Продажи_по_месяцам​

​ содержит объем продаж​

  • ​Перевел: Антон Андронов​​или​​(Диспетчер имен).​​ букву его имени.​​Если нам потребуется изменить​
  • ​ ее в ячейки​(имя будет работать​ адресацией, но есть​ одному и тому​
  • ​ Диапазон введена формула​ будем использовать абсолютную​
  • ​ но иногда проще​ =СУММ(А1:А10). Другим подходом​​.​​ выделить область, у​, а ячейке В4​ по месяцам, а​Автор: Антон Андронов​СЧЕТЗ($A:$A)​Нажмите кнопку​Excel добавит к именам​
  • ​ ссылку на диапазон​С11D11E11​ только на этом​
  • ​ решение лучше. С​

​ же диапазону​ =’1сезон’!$B$2:$B$10​​ адресацию.​​ работать не напрямую​ является использование в​Итак, в данном уроке​ которой есть имя,​ имя​​ в ячейке D2​​Создавать и применять формулы​

​,​Edit​ формул, начинающихся на​

​ данных, то это​, и получим суммы​ листе);​ использованием относительной адресации​B1:B10​​нажмите ОК.​Для этого:​ с диапазонами, а​​ качестве ссылки имени​ Вы узнали, что​ достаточно щелкнуть по​Комиссионные​​ хранится процент комиссионных.​​ в Excel гораздо​

​ширина:​(Изменить).​ эту букву, еще​ придется сделать 3​

Задача2 (Именованный диапазон с относительной адресацией)

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

​1​Кликните по полю​ и имя диапазона!​ раза. Например, ссылку ​ из 4-х сезонов.​ Диапазон введена формула​ только​Иногда выгодно использовать не​ листа​B2:B10​​Совет​​ рассмотрим какие преимущества​

  • ​Имя​​ можно записать в​​ сколько мы заработали​ адресов ячеек и​.​Refers to​Динамический именованный диапазон автоматически​E2:E8​ Формула в ячейках​
  • ​ =’4сезона’!B$2:B$10​одного​ абсолютную, а относительную​1сезон​
  • ​на листе​: Узнать на какой диапазон​
  • ​ дает использование имени.​ Excel. Если желаете​​и из раскрывающегося​​ следующем виде:​ за прошедший год.​ диапазонов в них​
  • ​Формула COUNTA($A:$A) или СЧЕТЗ($A:$A)​(Диапазон) и введите​ расширяется при добавлении​
  • ​поменять на ​

​B11, С11D11E11​нажмите ОК.​Именованного диапазона Сезонные_продажи.​ ссылку, об этом​можно написать формулу​1сезон​​ ячеек ссылается Имя можно​​Назовем Именованным диапазоном в​ получить еще больше​ списка выбрать нужное.​Как видите, новая форма​Для того чтобы подсчитать​ используются имена. Имя​ – подсчитывает число​

​ формулу:​ значения в диапазон.​​J14:J20​​одна и та​Мы использовали смешанную адресацию​Для этого:​​ ниже.​​ в простом и​;​ через Диспетчер имен​ MS EXCEL, диапазон​​ информации об именах,​​Диапазон будет выделен:​ записи формулы стала​

​ наш заработок, необходимо​
​ выступает как бы​ значений в столбце​=OFFSET($A$1,0,0,COUNTA($A:$A),1)​Например, выберите диапазон​​.​​ же!​ B$2:B$10 (без знака​выделите ячейку​Теперь найдем сумму продаж​

Использование именованных диапазонов в сложных формулах

​ наглядном виде: =СУММ(Продажи).​на вкладке Формулы в​ расположенный в меню​ ячеек, которому присвоено​ читайте следующие статьи:​Существует несколько способов вставить​

​ более очевидной и​

​ просуммировать объемы продаж​ идентификатором какого-либо элемента​ А. Когда вы​=СМЕЩ($A$1;0;0;СЧЕТЗ($A:$A);1)​A1:A4​​Но, если перед составлением​​СОВЕТ:​​ $ перед названием​​B11​

​ товаров в четырех​ Будет выведена сумма​ группе Определенные имена​​ Формулы/ Определенные имена/​​ Имя (советуем перед​Как присвоить имя ячейке​ имя в формулу​​ простой для восприятия.​​ за весь год,​ рабочей книги. Имя​ добавляете значение к​

​и присвойте ему​ сложной формулы мы​Если выделить ячейку,​ столбца). Такая адресация​, в которой будет​ сезонах. Данные о​

​ значений из диапазона​ выберите команду Присвоить​ Диспетчер имен.​ прочтением этой статьи​

Динамический именованный диапазон в Excel

​ или диапазону в​ Excel, Вы можете​ Можно пойти еще​

  1. ​ а затем полученный​​ может присваиваться ячейкам,​​ диапазону, количество элементов​Функция​​ имя​​ присвоим диапазону ​
  2. ​ содержащую формулу с​
  3. ​ позволяет суммировать значения​ находится формула суммирования​ продажах находятся на​

​B2:B10​ имя;​Ниже рассмотрим как присваивать​ ознакомиться с правилами​

  1. ​ Excel?​​ использовать любой из​​ дальше и для​​ результат умножить на​​ диапазонам, таблицам, диаграммам,​
  2. ​ увеличивается. В результате,​​OFFSET​​Prices​
  3. ​E2:E8​​ именем диапазона, и​​ находящиеся в строках​ (при использовании относительной​

​в поле Имя введите:​​ имя диапазонам. Оказывается,​​ создания Имен).​​5 полезных правил и​ предложенных ниже:​

  • ​ значения комиссионных создать​​ комиссионные. Наша формула​​ фигурам и т.д.​
  • ​ именованный диапазон расширяется.​​(СМЕЩ) принимает 5​​.​
  • ​ какое-нибудь имя (например, Цены),​​ нажать клавишу​​2 310​
  • ​ адресации важно четко​​4сезона​​Также можно, например, подсчитать​​ Продажи;​​ что диапазону ячеек​
  • ​Преимуществом именованного диапазона является​​ рекомендаций по созданию​​Выделите именованный диапазон мышью​

​ именованную константу. В​ будет выглядеть следующим​ Мы же рассмотрим​Нажмите​ аргументов:​Рассчитайте сумму.​ то ссылку на​F2​

  • ​, в том столбце,​​ фиксировать нахождение активной​​(см. файл примера)​​ среднее значение продаж,​​в поле Область выберите​
  • ​ можно присвоить имя​ его информативность. Сравним​ имен в Excel​ и имя подставится​
  • ​ этом случае исчезнет​ образом:​
    ​ только имена, назначаемые​
    ​ОК​

    Знакомство с именами ячеек и диапазонов в Excel

    ​Когда вы добавляете значение​ диапазон придется менять​, то соответствующие ячейки​ в котором размещена​ ячейки в момент​ в диапазонах:​ записав =СРЗНАЧ(Продажи).​ лист​ по разному: используя​ две записи одной​Диспетчер имен в Excel​ в формулу автоматически.​ необходимость выделять под​Такая формула будет вычислять​ ячейкам, диапазонам и​, а затем​$A$1​

    ​ к диапазону, Excel​только 1 раз​ будут обведены синей​ формула суммирования. Формулу​ создания имени);​B2:B10 C2:C10 D2:D10 E2:E10​Обратите внимание, что EXCEL при создании​1сезон​ абсолютную или смешанную​ формулы для суммирования,​Как присваивать имена константам​Начните вводить название имени​ нее отдельную ячейку​ правильный результат, но​

    ​ константам применительно к​Close​,​ не обновляет сумму.​и даже не​ рамкой (визуальное отображение​ суммирования можно разместить​на вкладке Формулы в​. Формулы поместим соответственно​

    ​ имени использовал абсолютную адресацию​(имя будет работать​ адресацию.​ например, объемов продаж:​ в Excel?​ вручную, и оно​ на рабочем листе​ аргументы, используемые в​ формулам Excel.​(Закрыть).​​смещение по строкам:​​Чтобы автоматически расширять именованный​ в формуле, а​​ Именованного диапазона).​​ в любой строке​ группе Определенные имена​ в ячейках​

    ​ $B$1:$B$10. Абсолютная ссылка​ только на этом​Пусть необходимо найти объем​ =СУММ($B$2:$B$10) и =СУММ(Продажи).​Урок подготовлен для Вас​ отобразится в списке​ Excel.​ ней, не совсем​Приведем небольшой пример. Представим,​Теперь, когда вы добавляете​0​ диапазон при добавлении​ в Диспетчере имен!​

    Простой способ выделить именованный диапазон в Excel

    ​Предположим, что имеется сложная​ ниже десятой (иначе​ выберите команду Присвоить​B11C11 D11E11​ жестко фиксирует диапазон​ листе) или оставьте​ продаж товаров (см.​ Хотя формулы вернут​ командой сайта office-guru.ru​ автозавершения формул.​Назначая имена ячейкам и​​ очевидны. Чтобы формула​​ что мы продаем​ значение в диапазон,​

    Как вставить имя ячейки или диапазона в формулу

    ​ значения, выполните следующие​=СУММ(Цены)+СРЗНАЧ(Цены)/5+10/СУММ(Цены)​ (длинная) формула, в​ возникнет циклическая ссылка).​ имя;​

    1. ​.​ суммирования:​ значение Книга, чтобы​
    2. ​ файл примера лист​ один и тот​Автор: Антон Андронов​Вставьте имя из раскрывающегося​
    3. ​ диапазонам в Excel,​ стала более понятной,​​ элитную косметику и​​ Excel автоматически обновляет​смещение по столбцам:​​ несколько шагов:​​Более того, при создании​

    ​ которой несколько раз​Теперь введем формулу =СУММ(Сезонные_Продажи)​в поле Имя введите:​По аналогии с абсолютной​в какой ячейке на​ имя было доступно​ 1сезон):​ же результат (если,​

    • ​Автор: Антон Андронов​ списка​ мы приобретаем еще​
    • ​ необходимо назначить областям,​ получаем фиксированный процент​ сумму.​
    • ​0​
    • ​На вкладке​ формул EXCEL будет​

    ​ используется ссылка на​ в ячейку​
    ​ Сезонные_Продажи;​

    Excel vba именованный диапазон

    Если в активной рабочей книге используются имена, но Вы не видите их в списке имён стандартного диалогового окна, которое появится, если выполнить следующии действия :

    XL2003 меню Вставка пункт Имя и команда Присвоить
    XL2007 закладка Формулы группа Определённые имена и кнопка Диспетчер имён

    То, чтобы «найти» все скрытые имена и сделать их видимыми, достаточно выполнить нижеопубликованный макрос.
    Ответ :

    Если в активной рабочей книге используются имена и Вы хотите их все скрыть, т.е. сделать так, чтобы их не было видно в списке имён стандартного диалогового окна, которое появится, если выполнить следующии действия :

    XL2003 меню Вставка пункт Имя и команда Присвоить
    XL2007 закладка Формулы группа Определённые имена и кнопка Диспетчер имён

    То, просто выполните нижеопубликованный макрос.
    Ответ :

    Если Вам необходимо иметь «универсальный» макрос, который может скрывать/делать видимыми имена в любой открытой книге, то используйте нижеопубликованный макрос.Примеры вызова :

    1) Скрыть имена в текущей книге2) Отобразить имена в активной книге3) Отобразить имена в открытой книге с именем «Файл_с_именами.xls»
    Ответ : Актуально для MS Excel 2007

    Если необходимо, чтобы в диспетчере имён, напротив нужного имени ячейки/диапазона, появилось примечание, которое, предоставляет дополнтельные сведения (текст не более 255 символов) об этом об’екте, то такой комментарий можно создать так :

  • Ответ :
    Для рабочего листа мы можем узнать количество количество имён только уровня рабочего листа
  • Ответ :

    Вариант I.Вариант II.Bonus.
    Ответ :

    Если при работе с об’ектом Name, который ссылается на диапазон, необходимо получить доступ к этому диапазону, то это можно осуществить с помощью свойства .RefersToRange или добавив функцию EvaluateЕсли же понадобится противоположное действие, т.е. используя диапазон, добраться до имени, то :
    Ответ :

    Как известно имена бывают двух уровней : уровня рабочей книги (по умолчанию глобальные) и уровня рабочего листа (локальные) и если необходимо программно определить уровень имени, то Вы можете использовать любой из трёх вариантов.

    Вариант I.Примечание : Если необходимо поменять местами порядок выполнение инструкций, то используйте :Вариант II.Примечание : Если необходимо поменять местами порядок выполнение инструкций, то используйте :Вариант III.Примечание : Если необходимо поменять местами порядок выполнение инструкций, то используйте :Комментарий : Во всех примерах управляющую инструкцию If Then Else можно заменить на функцию IIf :Вариант IV. Microsoft Excel 2007 (и старше)
    Ответ :

    Как известно присвоить имя можно не только ячейке или диапазону ячеек, но и формуле, а также константе. И если потребуется узнать ссылается ли имя на ячейку/диапазон, то можно использовать один из предложенных вариантов, учитывая, что :

  • формулы, которые возвращают ссылку на ячейку/диапазон, также считаются Range
  • однако, если формула ссылается на другую рабочую книгу (внешняя ссылка), то в случае, если другая рабочая книга открыта, то это Range, в противном случае Error
  • трёхмерные ссылки как Range не определяются

    Вариант I.Вариант II.
    Ответ :

    Как известно имена могут содержать ссылки на ячейки и диапазоны ячеек, но после удаления этих ячеек, вместо ссылки появится значение ошибки #ССЫЛКА! И если возникнет необходимость в определении наличия подобных имён, например, для удаления, то можно использовать любой из двух предложенных вариантов.

    Читать еще:  Как сделать документ word
  • Ссылка на основную публикацию
    Adblock
    detector