Академия наук

Именованные диапазоны в Microsoft Excel как средство прописывания адреса в формулах и VBA

Автор: Буланова Т.Г., к.ф-м.н., Государственный Университет Управления

Именованный диапазон представляет собой способ описать используемые формулы. При этом обращение к именованному диапазону гораздо удобнее, чем прописывание адреса в формулах и VBA. Другими словами, выражение в круглых скобках:

= SUM(B2:B4)

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

= SUM(MonthlyBenefits)

За «MonthlyBenefits» скрываются ссылки на ячейки. К слову сказать, заголовки столбцов могут отображаться не буквами, а цифрами. Если Вы предпочитаете буквенные заголовки столбцов, вернуть это отображение очень просто.

Для Excel 2003:

Сервис- Параметры- вкладка Общие- Стиль ссылок R1C1. Снять галку.

Для Excel 2007:

Кнопка Офис- Параметры Excel (Excel Options)- вкладка Формулы(Formulas)- Стиль ссылок R1C1 (R1C1 reference style). Снять галку.

Для Excel 2010 и 2013:

Файл (File)- Параметры (Options)- вкладка Формулы(Formulas)- Стиль ссылок R1C1 (R1C1 reference style). Снять галку.

При потребности частого изменения стиля ссылок можно воспользоваться макросом:

Private Sub ChangeStyleRef_ReferenceStyle()

If Application.ReferenceStyle = xlA1 Then

Application.ReferenceStyle = xlR1C1

Else

Application.ReferenceStyle = xlA1

End If

End Sub

Присвоение имени

«Настройка» именованного диапазона представляет собой двухэтапный процесс. Сначала присваиваем имя, а затем его применяем- обращаемся к нему. Для присвоения имени выполняем следующее (убедитесь, что формула находится в ячейке B5):

Выделяем ячейки с B2 до B4 (НЕ B5)

Excel 2003:

Вставка -Имя -Присвоить

Для Excel 2010 и 2013:

Кликаем меню «Формулы» (Formulas) -Диспетчер имен (Name Manager) -Создать (New)(либо на той же вкладке сразу - Присвоить имя (Define Name))

По-другому:

Выделяем ячейку или группу ячеек, имя которым хотим присвоить -щелкаем левой кнопкой мыши в окне адреса и вписываем имя, которое хотим присвоить. Жмем Enter. Этот способ « работает» для Excel 2003, 20010 и 2013.

Обращение к именованному диапазону

Из VBA к именованному диапазону можно обратиться следующим образом:

MsgBox Range("CellRange").Address

MsgBox [CellRange].Address

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

=СУММ(CellRange)

=ВПР("Критерий"; CellRange;2;0)

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

Обращаем внимание на то, что на имя диапазона накладываются определенные ограничения, как например:

- Не могут быть использованы словосочетания, содержащие пробел;

- Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (\);

- В качестве имени нельзя использовать зарезервированные в Excel константы - R, C и RC(как прописные, так и строчные);

- Нельзя давать именам названия, совпадающие с адресацией ячеек;

- Длина имени не должна превышать 255 символов.

Литература:

1.macros-vba.ru›nadstrojki/excel/183-diapazon-excel

2.excelbox.ru›rangenames/7-createnamedranges.html

3.http://exceltip.ru/

4.http://www.excel-vba.ru/chto-umeet-excel/imenovannye-diapazony/