Как посмотреть все именованные диапазоны в excel

Использование Диспетчера имен в Excel

Диалоговое окно «Диспетчер имен» используется для работы со всеми определенными именами и именами таблиц в книге. Например, может потребоваться найти имена с ошибками, подтвердить значение и ссылку на имя, просмотреть или изменить описательные комментарии или определить область. Вы также можете отсортировать и отфильтровать список имен, а также легко добавлять, изменять или удалять имена из одного расположения.

Чтобы открыть диалоговое окно Диспетчер имен, на вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.

В диалоговом окне Диспетчер имен отображаются следующие сведения о каждом имени в списке.

Имя столбца

Одно из следующих значений:

• определенное имя, которое обозначается значком определенного имени;
• имя таблицы, которое обозначается значком имени таблицы.

Примечание: Имя таблицы — это имя таблицы Excel, которая представляет собой коллекцию данных об определенной теме, хранящихся в записях (строках) и полях (столбцах). Excel создает имя таблицы Excel таблицы table1, Table2 и т. д. при каждой вставке Excel таблицы. Вы можете изменить имя таблицы, чтобы сделать ее более понятной. Дополнительные сведения о Excel таблицах см. в статье «Использование структурированных ссылок с Excel таблицами».

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

Текущая ссылка для имени. Вот типичные примеры.

• =Лист1!$A$3
• =8,3
• =HR!$A$1:$Z$345
• =СУММ(Лист1!A1,Лист2!B2)

• имя листа, если область действия — локальный уровень листа;
• «Книга», если областью является глобальный уровень книги. Этот вариант применяется по умолчанию.

Дополнительные сведения об имени длиной до 255-ти знаков. Вот типичные примеры.

• Срок действия этого значения истекает 2 мая 2007 г.
• Не удалять! Критическое имя!
• Основано на числах экзамена на сертификацию ISO.

Ссылка на выбранное имя.

Диапазон имени можно быстро изменить, изменив сведения в поле «Ссылки «. После внесения изменений можно нажать кнопку « сохранить изменения», или отменить изменения.

• При изменении содержимого ячейки нельзя использовать диалоговое окно диспетчера имен.
• В диалоговом окне диспетчера имен не отображаются имена, определенные в Visual Basic для приложений (VBA) или скрытые имена (свойство Visible имени имеет значение False).

Создание именованного диапазона

1. На вкладке Формулы в группе Определенные имена выберите команду Присвоить имя.
2. В диалоговом окне «Новое имя» введите имя, которое вы хотите использовать для ссылки.

Примечание: Длина имени не может превышать 255 знаков.

• Нажмите кнопку «Свернуть » (при этом диалоговое окно временно сжимается), выделите ячейки на листе и нажмите кнопку » Развернуть диалоговое окно» .
• Чтобы указать константу, введите = (знак равенства), а затем значение константы.
• Чтобы указать формулу, введите =, а затем формулу.

• Будьте внимательны при использовании абсолютных или относительных ссылок в формуле. Если создать ссылку, щелкнув ячейку, на которую вы хотите ссылаться, Excel создать абсолютную ссылку, например «Sheet1!$B$1». Если ввести ссылку, например «B1», это относительная ссылка. Если при выборе имени активной ячейкой является A1, то ссылка на «B1» действительно означает «ячейка в следующем столбце». Если вы используете определенное имя в формуле в ячейке, ссылка будет на ячейку в следующем столбце относительно места ввода формулы. Например, если ввести формулу в C10, ссылка будет иметь значение D10, а не B1.
• Дополнительные сведения: переключение между относительным, абсолютным и смешанным ссылками

Примечание: Чтобы расширить или удлинить диалоговое окно Создание имени, щелкните и перетащите маркер захвата, расположенный внизу.

Изменение имени

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

1. На вкладке Формулы в группе Определенные имена выберите пункт Диспетчер имен.
2. В диалоговом окне диспетчера имен дважды щелкните имя, которое вы хотите изменить, или выберите имя, которое вы хотите изменить, а затем нажмите кнопку «Изменить».
3. В диалоговом окне Изменение имени введите новое имя для ссылки в поле Имя.
4. Измените ссылку в поле Диапазон и нажмите кнопку ОК.
5. В диалоговом окне Диспетчер имен в поле Диапазон измените ячейку, формулу или константу, представленную этим именем.

Удаление одного или нескольких имен

1. На вкладке Формулы в группе Определенные имена выберите пункт Диспетчер имен.
2. В диалоговом окне Диспетчер имен щелкните имя, которое нужно изменить.
3. Выделите одно или несколько имен одним из способов, указанных ниже.
   • Для выделения имени щелкните его.
   • Чтобы выбрать несколько имен в непрерывной группе, щелкните и перетащите их, а затем нажмите клавишу SHIFT и нажмите кнопку мыши для каждого имени в группе.
   • Чтобы выбрать несколько имен в несмежной группе, нажмите клавишу CTRL и нажмите кнопку мыши для каждого имени в группе.
4. Нажмите кнопку Удалить.
5. Нажмите кнопку ОК, чтобы подтвердить удаление.

Фильтрация имен

Используйте команды раскрывающегося списка Фильтр для быстрого отображения подмножества имен. Выбор каждой команды включает или отключает операцию фильтра, что упрощает объединение или удаление различных операций фильтра для получения нужных результатов.

Можно выполнить фильтрацию из следующих параметров:

Имена на листе

Отобразить только локальные имена листа.

Имена в книге

Отобразить только глобальные имена в книге.

Имена с ошибками

Отображение только имен со значениями, содержащими ошибки (например, #REF, #VALUE или #NAME).

Имена без ошибок

Отобразить только те имена, в значениях которых нет ошибок.

Определенные имена

Отобразить только те имена, которые определены пользователем или Excel, такое как имя области печати.

Имена таблиц

Отобразить только имена таблиц.

Ссылка на именованные диапазоны

Диапазоны легче идентифицировать по имени, чем с помощью нотации A1. Чтобы присвоить имя выбранному диапазону, щелкните поле имени с левой стороны строки формул, введите имя и нажмите клавишу ВВОД.

Примечание Существует два типа именованных диапазонов: именованный диапазон книги и именованный диапазон для таблицы.

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

Именованный диапазон книги относится к определенному диапазону в любом месте книги (применяется глобально).

Как создать именованный диапазон книги:

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

Именованный диапазон определенного листа

Именованный диапазон определенного листа относится к диапазону конкретного листа и не является глобальным для всех листов в книге. Ссылайтесь на этот именованный диапазон только по имени на том же листе, но на другом листе необходимо использовать имя листа, включая «!», имя диапазона (например: «Имя» «=Лист1! Имя»).

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

Как создать именованный диапазон определенного листа:

1. Выделите диапазон, которому нужно присвоить имя.
2. Перейдите на вкладку «Формулы» на ленте Excel в верхней части окна.
3. Нажмите кнопку «Присвоить имя» на вкладке формул.
4. В диалоговом окне «Создание имени» в поле «Область» выберите конкретный лист, где расположен диапазон, которому нужно присвоить имя (например, «Лист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 и обратная связь.

Обратная связь

Были ли сведения на этой странице полезными?

Поиск именованных диапазонов

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

Именующий диапазон можно найти на вкладке «Главная», нажав кнопку «Найти &Выбрать» и выбрав «Перейти».

• Во всплываемом окне «Перейти» показаны имененные диапазоны на всех книгах.
• Чтобы перейти к диапазону неименованых ячеек, нажмите CTRL+G, введите диапазон в поле «Ссылка» и нажмите ввод (или кнопку ОК). Поле «Перейти» отслеживает диапазоны по мере их ввода, и вы можете вернуться к любому из них, дважды щелкнув их.
• Чтобы перейти к ячейке или диапазону на другом листе, введите в поле «Ссылка» следующее: имя листа вместе с восклицательный индекс и абсолютные ссылки на ячейки. Например, лист2!$D $12 для перейти к ячейке, а лист3!$C$12:$F$21 — для перейти к диапазону.
• В поле «Ссылка» можно ввести несколько именовых диапазонов или ссылок на ячейки. Разделяя каждую из них запятой, например: Price, Typeили B14:C22,F19:G30,H21:H29. Когда вы нажмете ввод или нажмете кнопку«ОК», Excel выделит все диапазоны.

Дополнительные сведения о поиске данных в Excel

• Поиск или замена текста и чисел на листе
• Поиск объединенных ячеек
• Удаление или разрешение циклической ссылки
• Поиск ячеек, содержащих формулы
• Поиск ячеек с условным форматированием
• Поиск скрытых ячеек на листе

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Именованные диапазоны в Excel — несколько трюков использования

Опубликовано 11.07.2013 Автор Ренат Лотфуллин

Именованные диапазоны, вероятно, один из самых полезных инструментов в Excel. Именованные диапазоны добавляют интерактивность в книгу, делают длинные формулы короткими и, при правильном использовании, обеспечивают механизм обмена информации по всей книге. Учитывая, какую пользу несут именованные диапазоны в Excel, решил уделить немного внимания им на этом блоге.

Итак, несколько советов, которые сделают вашу работу с именованными диапазонами в Excel более быстрой и продуктивной.

Многоразовое создание именованного диапазона в один прием

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

К примеру, у вас имеется набор данных (как показано ниже) и вы хотите создать отдельные именованные диапазоны для каждой колонки. Вместо того чтобы создавать их по одному, вы можете воспользоваться сочетанием клавиш CTRL + SHIFT + F3, которое откроет диалоговое окно Создание имен из выделенного диапазона. Тоже самое окно доступно во вкладке Формулы -> Определенные имена –> Создать из выделенного. Теперь вы можете создать больше одного диапазона – по строкам, столбцам, оба варианта.

Когда вы щелкните ОК, Excel создаст четыре именованных диапазона. Заголовок каждого диапазона будет служить его названием. При необходимости вы можете легко отредактировать любой атрибут диапазонов.

Доступ к управлению именованными диапазонами

Чтобы открыть диалоговое окно Диспетчер имен, перейдите по вкладке Формулы в группу Определенные имена и щелкните по кнопке Диспетчер имен. Либо нажатием сочетаний клавиш Ctrl + F3.

Использование формулы СМЕЩ

Именованные диапазоны и вполовину не были бы такими полезными и интересными без формулы СМЕЩ. Функция СМЕЩ помогает позиционировать и расширять данный диапазон. Результатом использования ее может стать мощный динамический диапазон, который имеет способность расширяться и изменяться.

Использование абсолютных ссылок при работе с именованными диапазонами

На самом деле не уверен, это конструктивная особенность или ошибка. Используя относительные ссылки (A1 вместо $A$1) при определении именованного диапазона, они не остаются на том же месте, как бы вы этого не хотели. Давайте рассмотрим этот случай на примере. Предположим, вы хотите создать диапазон, который смещается вниз на 10 строк от ячейки A1. Первое, что приходит в голову, это написать формулу =СМЕЩ(A1;10;0).

Пока все хорошо. Если вы захотите воспользоваться этим именованным диапазоном, необходимо подобрать для нее ячейку (скажем B1) и ввести что-то типа =мой_имен_диап. Где мой_имен_диап — это имя, которое вы дали диапазону на предыдущем шаге.

Но если вы выберите другую ячейку и снова откроете диспетчер имен, формула, которую вы ввели до этого =СМЕЩ(Лист1!A1;10;0), магическим образом преобразится (к примеру, =СМЕЩ(Лист1!A1048576;10;0)). Это происходит потому, что при создании именованного диапазона мы использовали относительные ссылки, т.е. целевой диапазон будет все время смещаться в зависимости от адреса, выбранной на данный момент, ячейки.

Использование F2 для изменения именованного диапазона

Еще одна полезная вещь, использование F2 при изменении именованного диапазона. Попробуйте воспользоваться кнопками стрелок на клавиатуре для навигации по формуле именованного диапазона, вы увидите замечательные преобразования.

Чтобы избежать недоразумений при использовании стрелок, нажмите клавишу F2.

Возможно у вас имеются свои трюки по использованию именованных диапазонов?! Не хотите поделиться?)

Вам также могут быть интересны следующие статьи

• Проверка данных с помощью выпадающего списка в ячейке
• Как отфильтровать список элементов из длинного-длинного списка
• Импорт данных с Excel книги, находящейся на веб-сайте
• Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel
• Excel дашборд по обслуживанию клиентов — формулы и система расчетов [часть 2 из 4]
• Формулы массивов в Excel — синтаксис формул массивов и массивов констант
• SkyDrive Excel или Как организовать одновременную работу нескольких людей в одном файле
• Гиперссылка в Excel — создание, изменение и удаление
• Инструмент Найти и заменить в Excel
• Поиск и выделение конкретных значений

Рубрика: Ввод данных | Метки: именованные диапазоны, Проверка данных | 2 комментария | Permalink

2 комментария

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

Здравствуйте, Ренат! Очень интересная диаграмма, даже при том, что и не классический тримап. Скажите, а можно построить подобную диаграмму так, чтобы её составляющие были положительные и отрицательные. Чтобы их размер зависел, насколько далеко их значение от 0, а располагались они справа и слева от оси, в зависимости от знака? Была бы очень интересная диаграмма весов.