Имена в MS EXCEL

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

Имена часто используются при создании, например, Динамических диапазонов, Связанных списков. Имя можно присвоить диапазону ячеек, формуле, константе и другим объектам EXCEL.

Ниже приведены примеры имен.

 

Объект именования

Пример

Формула без использования имени

Формула с использованием имени

Диапазон

имя ПродажиЗа1Квартал присвоено диапазону ячеек C20:C30

=СУММ(C20:C30)

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

Константа

имя НДС присвоено константе 0,18

=ПРОИЗВЕД(A5;0,18)

=ПРОИЗВЕД(А5;НДС)

Формула

имя УровеньЗапасов присвоено формуле ВПР(A1;$B$1:$F$20;5;ЛОЖЬ)

=СУММ(ВПР(A1;$B$1:$F$20;5;ЛОЖЬ))

=СУММ(УровеньЗапасов)

Таблица

имя МаксПродажи2006 присвоено таблице, которая создана через меню Вставка/Таблицы/Таблица

=$C$4:$G$36

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

Массив констант

имя Диапазон1 присвоено диапазону чисел 1, 2, 3

=СУММ({1;2;3})

=СУММ(Диапазон1)

А. СОЗДАНИЕ ИМЕН

Для создания имени сначала необходимо определим объект, которому будем его присваивать.

Присваивание имен диапазону ячеек

Создадим список, например, фамилий сотрудников, в диапазоне А2:А10. В ячейку А1 введем заголовок списка – Сотрудники, в ячейки ниже – сами фамилии. Присвоить имя Сотрудники диапазону А2:А10 можно несколькими вариантами:

1.Создание имени диапазона через команду Создать из выделенного фрагмента:

  • выделить ячейки А1:А10 (список вместе с заголовком);
  • нажать кнопку Создать из выделенного фрагмента(из меню Формулы/ Определенные имена/ Создать из выделенного фрагмента);
  • убедиться, что стоит галочка в поле В строке выше;
  • нажать ОК.

Проверить правильность имени можно через инструмент Диспетчер имен (Формулы/ Определенные имена/ Диспетчер имен)

2.Создание имени диапазона через команду Присвоить имя:

  • выделитьячейки А2:А10 (список без заголовка);
  • нажать кнопку Присвоить имя (из меню Формулы/ Определенные имена/ Присвоить имя);
  • в поле Имя ввести Сотрудники;
  • определить Область действия имени;
  • нажать ОК.

3.Создание имени в поле Имя:

  • выделить ячейки А2:А10 (список без заголовка);
  • в поле Имя (это поле расположено слева от Строки формул) ввести имя Сотрудники и нажать ENTER. Будет создано имя с областью действия Книга. Посмотреть присвоенное имя или подкорректировать его диапазон можно через Диспетчер имен.

4.Создание имени через контекстное меню:

ВНИМАНИЕ! По умолчанию при создании новых имен используются абсолютные ссылки на ячейки (абсолютная ссылка на ячейку имеет формат $A$1).

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

5. Быстрое создание нескольких имен

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

Необходимо создать 9 имен (Строка1, Строка2, ... Строка9) ссылающихся на диапазоны В1:Е1, В2:Е2, ... В9:Е9. Создавать их по одному (см. пункты 1-4) можно, но долго.

Чтобы создать все имена сразу, нужно:

  • выделить выделите таблицу;
  • нажать кнопку Создать из выделенного фрагмента(из меню Формулы/ Определенные имена/ Создать из выделенного фрагмента);
  • убедиться, что стоит галочка в поле В столбце слева;
  • нажать ОК.

Получим в Диспетчере имен (Формулы/ Определенные имена/ Диспетчер имен) сразу все 9 имен!

Присваивание имен формулам и константам

Присваивать имена формулам и константам имеет смысл, если формула достаточно сложная или часто употребляется. Например, при использовании сложных констант, таких как 2*Ln(ПИ), лучше присвоить имя выражению =2*LN(КОРЕНЬ(ПИ()))  Присвоить имя формуле или константе можно, например, через команду Присвоить имя (через меню Формулы/ Определенные имена/ Присвоить имя):

  • в поле Имя ввести, например 2LnPi;
  • в поле Диапазон нужно ввести формулу =2*LN(КОРЕНЬ(ПИ())).

Теперь введя в любой ячейке листа формулу =2LnPi, получим значение 1,14473.

О присваивании имен формулам читайте подробнее в статье Именованная формула.

Присваивание имен таблицам

Особняком стоят имена таблиц. Имеются ввиду таблицы в формате EXCEL 2007, которые созданы через меню Вставка/ Таблицы/ Таблица. При создании этих таблиц, EXCEL присваивает имена таблиц автоматически: Таблица1, Таблица2 и т.д., но эти имена можно изменить (через Конструктор таблиц), чтобы сделать их более выразительными.

Имя таблицы невозможно удалить (например, через Диспетчер имен). Пока существует таблица – будет определено и ее имя. Рассмотрим пример суммирования столбца таблицы через ее имя. Построим таблицу из 2-х столбцов: Товар и Стоимость. Где-нибудь в стороне от таблицы введем формулу =СУММ(Таблица1[стоимость]). EXCEL после ввода =СУММ(Т предложит выбрать среди других формул и имя таблицы.

EXCEL после ввода =СУММ(Таблица1[ предложит выбрать поле таблицы. Выберем поле Стоимость.

В итоге получим сумму по столбцу Стоимость.

Ссылки вида Таблица1[стоимость] называются Структурированными ссылками.

В. СИНТАКСИЧЕСКИЕ ПРАВИЛА ДЛЯ ИМЕН

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

  • Пробелы в имени не допускаются. В качестве разделителей слов используйте символ подчеркивания (_) или точку (.), например, «Налог_Продаж» или «Первый.Квартал».
  • Допустимые символы. Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (\). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания.
  • Нельзя использовать буквы "C", "c", "R" и "r" в качестве определенного имени, так как эти буквы используются как сокращенное имя строки и столбца выбранной в данный момент ячейки при их вводе в поле Имя или Перейти.
  • Имена в виде ссылок на ячейки запрещены. Имена не могут быть такими же, как ссылки на ячейки, например, Z$100 или R1C1.
  • Длина имени. Имя может содержать до 255-ти символов.
  • Учет регистра. Имя может состоять из строчных и прописных букв. EXCEL не различает строчные и прописные буквы в именах. Например, если создать имя Продажи и затем попытаться создать имя ПРОДАЖИ, то EXCEL предложит выбрать другое имя (если Область действия имен одинакова).

В качестве имен не следует использовать следующие специальные имена:

  • Критерии – это имя создается автоматически Расширенным фильтром (Данные/ Сортировка и фильтр/ Дополнительно);
  • Извлечь и База_данных – эти имена также создаются автоматически Расширенным фильтром;
  • Заголовки_для_печати – это имя создается автоматически при определении сквозных строк для печати на каждом листе;
  • Область_печати – это имя создается автоматически при задании области печати.

Если Вы в качестве имени использовали, например, слово Критерии с областью действия Лист1, то оно будет удалено при задании критериев для Расширенного фильтра на этом листе (без оповещения).

С. ИСПОЛЬЗОВАНИЕ ИМЕН

Уже созданное имя можно ввести в ячейку (в формулу) следующим образом.

  • с помощью прямого ввода. Можно ввести имя, например, в качестве аргумента в формуле: =СУММ(продажи) или =НДС. Имя вводится без кавычек, иначе оно будет интерпретировано как текст. После ввода первой буквы имени EXCEL отображает выпадающий список формул вместе с ранее определенными названиями имен.
  • выбором из команды Использовать в формуле. Выберите определенное имя на вкладке Формула в группе Определенные имена из списка Использовать в формуле.

Для правил Условного форматирования и Проверки данных нельзя использовать ссылки на другие листы или книги (с версии MS EXCEL 2010 - можно). Использование имен помогает обойти это ограничение в MS EXCEL 2007 и более ранних версий. Если в Условном форматировании нужно сделать, например, ссылку на ячейку А1 другого листа, то нужно сначала определить имя для этой ячейки, а затем сослаться на это имя в правиле Условного форматирования. Как это сделать - читайте здесь: Условное форматирование и Проверка данных.

D. ПОИСК И ПРОВЕРКА ИМЕН ОПРЕДЕЛЕННЫХ В КНИГЕ

Диспетчер имен: Все имена можно видеть через Диспетчер имен (Формулы/ Определенные имена/ Диспетчер имен), где доступна сортировка имен, отображение комментария и значения.

Клавиша F3: Быстрый способ найти имена — выбрать команду Формулы/ Определенные имена/ Использовать формулы/ Вставить имена или нажать клавишу F3. В диалоговом окне Вставка имени щелкните на кнопке Все имена и начиная с активной ячейки по строкам будут выведены все существующие имена в книге, причем в соседнем столбце появятся соответствующие диапазоны, на которые ссылаются имена. Получив список именованных диапазонов, можно создать гиперссылки для быстрого доступа к указанным диапазонам. Если список имен начался с A1, то в ячейке С1 напишем формулу:

=ГИПЕРССЫЛКА("[бд_заказы.xlsx]"&A1;A1)

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

Клавиша F5 (Переход): Удобным инструментом для перехода к именованным ячейкам или диапазонам является инструмент Переход. Он вызывается клавишей F5 и в поле Перейти к содержит имена ячеек, диапазонов и таблиц.

Е. ОБЛАСТЬ ДЕЙСТВИЯ ИМЕНИ

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

Например, если при создании имени для константы (пусть Имя будет const, а в поле Диапазон укажем =33) в поле Область выберем Лист1, то в любой ячейке на Листе1 можно будет написать =const. После чего в ячейке будет выведено соответствующее значение (33). Если сделать тоже самое на Листе2, то получим #ИМЯ? Чтобы все же использовать это имя на другом листе, то его нужно уточнить, предварив именем листа: =Лист1!const. Если имеется определенное имя и его область действия Книга, то это имя распознается на всех листах этой книги. Можно создать несколько одинаковых имен, но области действия у них должны быть разными. Присвоим константе 44 имя const, а в поле Область укажем Книга. На листе1 ничего не изменится (область действия Лист1 перекрывает область действия Книга), а на листе2 мы увидим 44.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.6 (8 оценок)