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

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

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

В этой статье

Дополнительно об использовании имен

Сведения о синтаксических правилах для имен

Создание на листе имени для ячейки или диапазона ячеек

Создание имени путем выделения ячеек на листе

Создание имени с помощью диалогового окна "Создание имени"

Управление именами с помощью диалогового окна диспетчера имен

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

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

Дополнительно об использовании имен

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

Тип примера

Пример без имени

Пример с именем

Ссылка

=СУММ(C20:C30)

=СУММ(ПродажиЗаПервыйКвартал)

Константа

=ПРОИЗВЕД(A5,8.3)

=ПРОИЗВЕД(Цена,НСП)

Формула

=СУММ(ВПР(A1;B1:F20;5;ЛОЖЬ); -G5)

=СУММ(УровеньЗапасов; -СуммаЗаказов)

Таблица

C4:G36

=МаксПродажи06

Типы имен

Существует несколько типов имен, которые можно создавать и использовать.

Определенное имя.    Имя, используемое для представления ячейки, диапазона ячеек, формулы или константы. Вы можете создавать собственные определенные имена. Иногда (например, при настройке области печати) Excel самостоятельно создает определенное имя.

Имя таблицы.    Имя таблицы Excel, являющейся набором данных по отдельной теме, которые хранятся в записях (строках) и полях (столбцах). Excel создает стандартные имена ("Таблица1", "Таблица2" и т. д.) каждый раз при вставке таблицы, но их можно изменить, чтобы сделать их более понятными. Дополнительные сведения о таблицах Excel см. в статье Использование структурированных ссылок в таблицах Excel.

Область действия имени

Все имена имеют область действия: это либо конкретный лист (локальный уровень листа), либо вся книга (глобальный уровень книги). Область действия имени — это область, в которой имена распознаются без уточнений. Пример:

  • Если определено имя "Бюджет_ФГ08", а его область действия — "Лист1", то это имя без уточнения распознается только на листе "Лист1", но не на листе "Лист2" или "Лист3".

    Чтобы использовать локальное имя листа в другом листе, его можно уточнить, предварив именем листа. Например:

    Лист1!Бюджет_ФГ08

  • Если вы определили имя, например "Продажи_Отдела_Сбыта", а его область действия — книга, то это имя распознается на всех листах этой книги, но не в какой-либо другой книге.

Имя должно быть уникальным в пределах своей области. В Excel запрещено определять имя, которое уже существует в той или иной области. При этом можно использовать одинаковые имена в разных областях. Например, можно определить имя "ВаловойДоход" в областях "Лист1", "Лист2" и "Лист3" в одной и той же книге. Несмотря на одинаковость, каждое имя уникально в своей области. Так можно сделать, чтобы формула, использующая имя "ВаловойДоход", всегда ссылалась на одни и те же ячейки на локальном уровне листа.

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

ИмяФайлаКниги!ВаловойДоход

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

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

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

  • Поле определенных имен в строке формул.    Это поле лучше всего использовать для создания имени на уровне книги для выделенного диапазона.

  • Создание имени по выделению.     Удобно создавать имена из существующих имен строк и столбцов с помощью выделения ячеек на листе.

  • Диалоговое окно Создание имени    Это лучше всего использовать, если нужна большая гибкость при создании имен, например, задание области определения на локальном уровне листа или создание примечания к имени.

Примечание. По умолчанию в именах используются абсолютные ссылки на ячейки.

Можно ввести имя таким образом.

  • Ввод   . Введите имя, например, в качестве аргумента формулы.

  • С помощью автозавершения формулы    Используйте раскрывающийся список автозавершения формулы, в котором действительные имена перечислены автоматически.

  • Выбором из команды Использовать в формуле    Выберите определенное имя на вкладке Формулы в группе Определенные имена из списка Использовать в формуле.

Проверка имен

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

К началу страницы

Сведения о синтаксических правилах для имен

Ниже приведен список синтаксических правил для создания и редактирования имен.

  • Допустимые символы    Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (\). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания.

    Совет. В качестве определенного имени нельзя использовать буквы "C", "c", "R" и "r", так как они используются в качестве сокращенного имени строки и столбца выбранной в данный момент ячейки при их вводе в поле Имя или Перейти.

  • Имена в виде ссылок на ячейки запрещены    Имена не могут быть такими же, как ссылки на ячейки, например Z$100 или R1C1.

  • Пробелы не допускаются    Пробелы не допускаются. В качестве разделителей слов используйте символ подчеркивания (_) и точку (.), например "Налог_с_Продаж" или "Первый.Квартал".

  • Длина имени    Имя может содержать до 255 символов.

  • Учет регистра.    Имя может состоять из строчных и прописных букв. Excel не различает строчные и прописные буквы в именах. Например, если создать имя "Продажи", а затем попытаться добавить имя "ПРОДАЖИ" в той же книге, Excel предложит выбрать уникальное имя.

К началу страницы

Создание на листе имени для ячейки или диапазона ячеек

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

  2. Щелкните поле Имя у левого края строки формул.

    Поле "Имя"

    Поле "Имя"

  3. Введите имя, которое нужно использовать для ссылки. В имени может быть до 255 знаков.

  4. Нажмите клавишу ВВОД.

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

К началу страницы

Создание имени путем выделения ячеек на листе

Можно преобразовать в имена существующие заголовки строк и столбцов.

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

  2. На вкладке Формулы в группе Определенные имена выберите команду Создать из выделенного фрагмента.

    Изображение ленты Excel

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

К началу страницы

Создание имени с помощью диалогового окна "Создание имени"

  1. На вкладке Формулы в группе Определенные имена выберите пункт Присвоение имени.

    Изображение ленты Excel

  2. В диалоговом окне Создание имени введите имя, которое нужно использовать в качестве ссылки, в поле Имя.

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

  3. Для задания области действия имени в поле со списком Область выберите элемент Книга или имя листа в книге.

  4. Также в поле Примечание можно ввести описание длиной до 255 знаков.

  5. В поле Диапазон выполните одно из указанных ниже действий.

    • Чтобы указать ссылку на ячейки, введите ссылку на ячейки.

      Совет. По умолчанию указан выделенный диапазон. Чтобы ввести в качестве аргумента ссылку на другие ячейки, нажмите кнопку Свернуть Изображение кнопки (диалоговое окно будет временно скрыто), выделите ячейки на листе и нажмите кнопку Развернуть Изображение кнопки .

    • Чтобы указать константу, введите = (знак равенства), а затем значение константы.

    • Чтобы указать формулу, введите =, а затем формулу.

  6. Чтобы закончить и вернуться на лист, нажмите кнопку ОК.

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

К началу страницы

Управление именами с помощью диалогового окна диспетчера имен

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

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

Изображение ленты Excel

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

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

Этот столбец:

Отображает:

Значок и имя

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

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

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

Значение

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

  • "это моя строковая константа"

  • 3,1459

  • {2003;12,2002;23,;2001,18}

  • #ССЫЛКА!

  • {...}

Диапазон

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

  • =Лист1!$A$3

  • =8,3

  • =HR!$A$1:$Z$345

  • =СУММ(Лист1!A1,Лист2!B2)

Область

  • Имя листа, если область действия — локальный уровень листа.

  • "Книга", если область действия — глобальный уровень листа.

Примечание

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

  • Окончание срока действия этого значения 2-мая-2007.

  • Не удалять! Критическое имя!

  • Основано на числах экзамена на сертификацию ISO.

  • Нельзя использовать диалоговое окно Диспетчер имен во время изменения содержимого ячейки.

  • Диалоговое окноДиспетчер имен не отображает имена, определенные в Visual Basic для приложений (VBA) или скрытые имена (для свойства Видимый имени установлено значение «Ложь»).

Изменение размера столбца

  • Для автоматического изменения размера столбца в соответствии с самым большим значением в нем дважды щелкните правый край заголовка столбца.

Сортировка имен

  • Чтобы отсортировать список имен в порядке возрастания или убывания, щелкните заголовок столбца.

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

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

Для фильтрации списка имен выполните одно или несколько следующих действий.

Выберите

Чтобы

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

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

Имена в книге

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

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

Отобразить только те имена, в значениях которых есть ошибка (такие как #ССЫЛ, #ЗНАЧ, #ИМЯ и т. д.).

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

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

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

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

Имена таблиц

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

К началу страницы

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

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

  1. На вкладке Формулы в группе Определенные имена выберите команду Диспетчер имен.

    Изображение ленты Excel

  2. В диалоговом окне Диспетчер имен щелкните имя, которое нужно изменить, и нажмите кнопку Изменить.

    Совет. Можно также дважды щелкнуть имя.

  3. В диалоговом окне Изменение имени введите новое имя для ссылки в поле Имя.

  4. Измените ссылку в поле Диапазон и нажмите кнопку ОК.

  5. В диалоговом окне Диспетчер имен в поле Диапазон измените ячейку, формулу или константу, представленную этим именем.

    • Чтобы отменить нежелательные или случайные изменения, нажмите кнопку Отмена Кнопка "Отмена" или клавишу ESC.

    • Чтобы сохранить изменения, нажмите кнопку ОК Кнопка ВВОД или клавишу ВВОД.

Кнопка Закрыть только закрывает диалоговое окно Диспетчер имен. Она не требуется для завершения уже сделанных изменений.

К началу страницы

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

  1. На вкладке Формулы в группе Определенные имена выберите команду Диспетчер имен.

    Изображение ленты Excel

  2. В диалоговом окне Диспетчер имен щелкните имя, которое нужно изменить.

  3. Выделите одно или несколько имен одним из способов, указанных ниже.

    • Для выделения имени щелкните его.

    • Для выделения нескольких смежных имен щелкните и протащите указатель по именам или щелкните каждое имя при нажатой клавише SHIFT.

    • Для выбора нескольких несмежных имен щелкните каждое имя при нажатой клавише CTRL.

  4. Нажмите кнопку Удалить. Можно также нажать клавишу DELETE.

  5. Нажмите кнопку ОК для подтверждения удаления.

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

К началу страницы

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×