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

Применяется к: Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel Starter

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

В этой статье

Дополнительно об использовании имен
Сведения о синтаксических правилах для имен
Создание на листе имени для ячейки или диапазона ячеек
Создание имени путем выделения ячеек на листе
Создание имени с помощью диалогового окна «Создание имени»
Управление именами с помощью диалогового окна диспетчера имен
Изменение имени
Удаление одного или нескольких имен

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

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

Тип примера Пример без имени Пример с именем
Ссылка =СУММ(C20:C30) =СУММ(ПродажиЗаПервыйКвартал)
Константа =ПРОИЗВЕД(A5;8,3) =ПРОИЗВЕД(Цена;НСП)
Формула =СУММ(ВПР(A1;B1:F20;5;ЛОЖЬ); -G5) =СУММ(УровеньЗапасов; -СуммаЗаказов)
Таблица C4:G36 =МаксПродажи06

Типы имен

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

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

Имя таблицы    Имя таблицы Excel, являющейся набором данных по отдельной теме, которые хранятся в записях (строках) и полях (столбцах). Excel создает имена таблиц Excel по умолчанию (Таблица1, Таблица2 и т. д.) каждый раз при вставке таблицы 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 знаков.Примечание. Если при сохранении книги в Microsoft Office SharePoint Server 2007 Службы Excel задан один или несколько параметров, это примечание используется как подсказка для инструмента на панели инструментовПараметры.
  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.

Примечание. Если при сохранении книги в Microsoft Office SharePoint Server 2007 Службы Excel задан один или несколько параметров, это примечание используется как подсказка для инструмента на панели инструментов Параметры.

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

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

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

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

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

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

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

Для фильтрации списка имен выполните одно или несколько из перечисленных ниже действий.

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

 

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

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

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

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

 

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

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

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