Ссылка на именованные диапазоны
Диапазоны легче идентифицировать по имени, чем с помощью нотации A1. Чтобы присвоить имя выбранному диапазону, щелкните поле имени с левой стороны строки формул, введите имя и нажмите клавишу ВВОД.
Примечание Существует два типа именованных диапазонов: именованный диапазон книги и именованный диапазон для таблицы.
Именованный диапазон книги
Именованный диапазон книги относится к определенному диапазону в любом месте книги (применяется глобально).
Как создать именованный диапазон книги:
Как указано выше, обычно он создается путем ввода имени в поле «Имя» с левой стороны строки формул. Обратите внимание, что имя не может содержать пробелов.
Именованный диапазон определенного листа
Именованный диапазон определенного листа относится к диапазону конкретного листа и не является глобальным для всех листов в книге. Ссылайтесь на этот именованный диапазон только по имени на том же листе, но на другом листе необходимо использовать имя листа, включая «!», имя диапазона (например: «Имя» «=Лист1! Имя»).
Преимущество заключается в возможности использования кода VBA для создания новых листов с одинаковыми именами для одних и тех же диапазонов на этих листах без возникновения ошибки, сообщающей, что имя уже используется.
Как создать именованный диапазон определенного листа:
- Выделите диапазон, которому нужно присвоить имя.
- Перейдите на вкладку «Формулы» на ленте Excel в верхней части окна.
- Нажмите кнопку «Присвоить имя» на вкладке формул.
- В диалоговом окне «Создание имени» в поле «Область» выберите конкретный лист, где расположен диапазон, которому нужно присвоить имя (например, «Лист1»), чтобы связать имя с этим листом. Если выбрать вариант «Книга», это будет имя книги.
Пример из worksheet Specific Именованный диапазон: выбранный диапазон для имени A1:A10
Выбранное имя диапазона — «Имя». В пределах одного листа ссылайтесь на именованный диапазон, просто введя в ячейку «=Имя». Из другого листа ссылайтесь на диапазон определенного листа, указав в ячейке имя листа: «= Лист1!Имя».
Ссылка на именованный диапазон
В следующем примере выполняется ссылка на диапазон с именем MyRange в книге с именем MyBook.xls.
Sub FormatRange() Range("MyBook.xls!MyRange").Font.Italic = True End Sub В следующем примере выполняется ссылка на диапазон определенного листа с именем Sheet1!Sales в книге с именем Report.xls.
Sub FormatSales() Range("[Report.xls]Sheet1!Sales").BorderAround Weight:=xlthin End Sub Чтобы выбрать именованный диапазон, используйте метод GoTo, который активирует книгу и лист, а затем выбирает диапазон.
Sub ClearRange() Application.Goto Reference:="MyBook.xls!MyRange" Selection.ClearContents End Sub В следующем примере показано, как можно написать эту же процедуру для активной книги.
Sub ClearRange() Application.Goto Reference:="MyRange" Selection.ClearContents End Sub Пример кода, предоставляемый: Деннис Валлентин(Dennis Wallentin), VSTO & .NET & Excel
В этом примере в качестве формулы для проверки данных используется именованный диапазон. В этом примере данные проверки должны быть на листе 2 в диапазоне A2:A100. Они используются для проверки данных, введенных на листе 1 в диапазоне D2:D10.
Sub Add_Data_Validation_From_Other_Worksheet() 'The current Excel workbook and worksheet, a range to define the data to be validated, and the target range 'to place the data in. Dim wbBook As Workbook Dim wsTarget As Worksheet Dim wsSource As Worksheet Dim rnTarget As Range Dim rnSource As Range 'Initialize the Excel objects and delete any artifacts from the last time the macro was run. Set wbBook = ThisWorkbook With wbBook Set wsSource = .Worksheets("Sheet2") Set wsTarget = .Worksheets("Sheet1") On Error Resume Next .Names("Source").Delete On Error GoTo 0 End With 'On the source worksheet, create a range in column A of up to 98 cells long, and name it "Source". With wsSource .Range(.Range("A2"), .Range("A100").End(xlUp)).Name = "Source" End With 'On the target worksheet, create a range 8 cells long in column D. Set rnTarget = wsTarget.Range("D2:D10") 'Clear out any artifacts from previous macro runs, then set up the target range with the validation data. With rnTarget .ClearContents With .Validation .Delete .Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Formula1:="=Source" 'Set up the Error dialog with the appropriate title and message .ErrorTitle = "Value Error" .ErrorMessage = "You can only choose from the list." End With End With End Sub Циклический переход по ячейкам в именованном диапазоне
В приведенном ниже примере выполняется циклический переход по каждой ячейке именованного диапазона с помощью цикла For Each. Next. Если значение любой ячейки в диапазоне превышает значение Limit , цвет ячейки изменяется на желтый.
Sub ApplyColor() Const Limit As Integer = 25 For Each c In Range("MyRange") If c.Value > Limit Then c.Interior.ColorIndex = 27 End If Next c End Sub Об участнике
Деннис Валлентин является автором блога VSTO & .NET & Excel, в который основное внимание уделяется решениям платформа .NET Framework для Excel и службы Excel. Деннис разрабатывает решения Excel более 20 лет и также является соавтором книги «Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET (2nd Edition)».
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Обратная связь
Были ли сведения на этой странице полезными?
Именованный диапазон в EXCEL
Обычно ссылки на диапазоны ячеек вводятся непосредственно в формулы, например =СУММ(А1:А10) . Другим подходом является использование в качестве ссылки имени диапазона. В статье рассмотрим какие преимущества дает использование имени.
Назовем Именованным диапазоном в MS EXCEL, диапазон ячеек, которому присвоено Имя (советуем перед прочтением этой статьи ознакомиться с правилами создания Имен ).
Преимуществом именованного диапазона является его информативность. Сравним две записи одной формулы для суммирования, например, объемов продаж: =СУММ($B$2:$B$10) и =СУММ(Продажи) . Хотя формулы вернут один и тот же результат (если, конечно, диапазону B2:B10 присвоено имя Продажи ), но иногда проще работать не напрямую с диапазонами, а с их именами.
Совет : Узнать на какой диапазон ячеек ссылается Имя можно через Диспетчер имен расположенный в меню Формулы/ Определенные имена/ Диспетчер имен .
Ниже рассмотрим как присваивать имя диапазонам. Оказывается, что диапазону ячеек можно присвоить имя по разному: используя абсолютную или смешанную адресацию .
Задача1 (Именованный диапазон с абсолютной адресацией)
Пусть необходимо найти объем продаж товаров (см. файл примера лист 1сезон ):
Присвоим Имя Продажи диапазону B2:B10 . При создании имени будем использовать абсолютную адресацию .
- выделите, диапазон B2:B10 на листе 1сезон ;
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
- в поле Имя введите: Продажи ;
- в поле Область выберите лист 1сезон (имя будет работать только на этом листе) или оставьте значение Книга , чтобы имя было доступно на любом листе книги;
- убедитесь, что в поле Диапазон введена формула =’1сезон’!$B$2:$B$10
- нажмите ОК.
Теперь в любой ячейке листа 1сезон можно написать формулу в простом и наглядном виде: =СУММ(Продажи) . Будет выведена сумма значений из диапазона B2:B10 .
Также можно, например, подсчитать среднее значение продаж, записав =СРЗНАЧ(Продажи) .
Обратите внимание, что EXCEL при создании имени использовал абсолютную адресацию $B$1:$B$10 . Абсолютная ссылка жестко фиксирует диапазон суммирования: в какой ячейке на листе Вы бы не написали формулу =СУММ(Продажи) – суммирование будет производиться по одному и тому же диапазону B1:B10 .
Иногда выгодно использовать не абсолютную, а относительную ссылку, об этом ниже.
Задача2 (Именованный диапазон с относительной адресацией)
Теперь найдем сумму продаж товаров в четырех сезонах. Данные о продажах находятся на листе 4сезона (см. файл примера ) в диапазонах: B2:B10 , C 2: C 10 , D 2: D 10 , E2:E10 . Формулы поместим соответственно в ячейках B11 , C 11 , D 11 , E 11 .
По аналогии с абсолютной адресацией из предыдущей задачи, можно, конечно, создать 4 именованных диапазона с абсолютной адресацией, но есть решение лучше. С использованием относительной адресации можно ограничиться созданием только одного Именованного диапазона Сезонные_продажи .
- выделите ячейку B11 , в которой будет находится формула суммирования (при использовании относительной адресации важно четко фиксировать нахождение активной ячейки в момент создания имени );
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
- в поле Имя введите: Сезонные_Продажи ;
- в поле Область выберите лист 4сезона (имя будет работать только на этом листе);
- убедитесь, что в поле Диапазон введена формула =’4сезона’!B$2:B$10
- нажмите ОК.
Мы использовали смешанную адресацию B$2:B$10 (без знака $ перед названием столбца). Такая адресация позволяет суммировать значения находящиеся в строках 2 , 3 ,… 10 , в том столбце, в котором размещена формула суммирования. Формулу суммирования можно разместить в любой строке ниже десятой (иначе возникнет циклическая ссылка).
Теперь введем формулу =СУММ(Сезонные_Продажи) в ячейку B11. Затем, с помощью Маркера заполнения , скопируем ее в ячейки С11 , D 11 , E 11 , и получим суммы продаж в каждом из 4-х сезонов. Формула в ячейках B 11, С11 , D 11 и E 11 одна и та же!
СОВЕТ: Если выделить ячейку, содержащую формулу с именем диапазона, и нажать клавишу F2 , то соответствующие ячейки будут обведены синей рамкой (визуальное отображение Именованного диапазона ).
Использование именованных диапазонов в сложных формулах
Предположим, что имеется сложная (длинная) формула, в которой несколько раз используется ссылка на один и тот же диапазон:
Если нам потребуется изменить ссылку на диапазон данных, то это придется сделать 3 раза. Например, ссылку E2:E8 поменять на J14:J20 .
Но, если перед составлением сложной формулы мы присвоим диапазону E2:E8 какое-нибудь имя (например, Цены ), то ссылку на диапазон придется менять только 1 раз и даже не в формуле, а в Диспетчере имен !
Более того, при создании формул EXCEL будет сам подсказывать имя диапазона! Для этого достаточно ввести первую букву его имени.
Excel добавит к именам формул, начинающихся на эту букву, еще и имя диапазона!
Создание именованного диапазона из выбранных ячеек на листе
Вы можете быстро создать именованный диапазон путем выделения ячеек на листе.
Примечание: Именованные диапазоны, созданные из выделенных ячеек, относятся к области на уровне книги.
- Выберите диапазон, которому вы хотите присвоить имя, включая заголовки строк и столбцов.
- Щелкните Формулы >Создать из выделенного.
- В диалоговом окне Создание имен из выделенного диапазона установите флажки в зависимости от расположения строки или столбца заголовков. Если строка заголовков присутствует только в верхней части таблицы, просто выберите В строке выше. Предположим, что вы используете верхнюю строку и левый столбец заголовков. В этом случае выберите параметры В строке выше и В столбце слева и т. д.
- Нажмите кнопку ОК.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как создать и выбрать именованный диапазон
Одной из наиболее полезных функций в Excel является возможность дать диапазону имя, таким образом, чтобы можно было легко идентифицировать и обратиться к нему с помощью VBA.
Вот шаги, которые нужно выполнить, чтобы создать диапазон вручную:
- Выберите диапазон, который вы хотите назвать.
- Перейдите на вкладку Формулы в ленте и выберите команду Определить имя.
- Дайте выбранному диапазону удобное для пользователя имя в диалоговом окне «Новое имя».
После нажатия кнопки ОК, ваш диапазон именуется. Чтобы убедиться в этом, вы можете перейти на вкладку Формулы и выбрать команду Диспетчер имен. Это активизирует диалоговое окно Диспетчер имен , где вы можете увидеть все примененные именованные диапазоны.
Создание именованного диапазона с помощью VBA является менее трудоемким.
Код макроса
Sub PrisvaivaemImyaDiapazonu() 'Присваиваем имя диапазону Range("D6:D17").Name = "MyData" End Sub
Как этот код работает
Следует признать, что трудно найти такую ситуацию, когда вам нужно было бы автоматизировать создание именованных диапазонов. Реальная эффективность достигается в манипулировании их с помощью VBA.
Как макрос работает
Вы просто передаете имя диапазона с помощью объекта Range. Этот макрос позволяет выбрать диапазон:
Код макроса
Sub ViborDiapazona() Range("MyData").Select End Sub
Как этот код работает
Как и в случае нормальных диапазонов, вы можете обратиться к диапазону с помощью оператора With … End With. Этот оператор говорит Excel, что любое действие, которое вы выполняете относится к объекту, который вы указали. Это не только избавит вас от необходимости повторять синтаксис, но и позволит легко добавлять действия, просто добавляя их между With и End With.
Код макроса
Sub ViborDiapazonaFormatirovanieWith() With Range("MyData") .NumberFormat = "#,##0" .Font.Bold = True .Interior.ColorIndex = 36 End With End Sub
Как использовать
Для реализации такого рода макроса, вы можете скопировать и вставить его в стандартный модуль:
- Активируйте редактор Visual Basic, нажав ALT + F11.
- Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта.
- Выберите Insert➜Module.
- Введите или вставьте код