Как присвоить имя ячейке в excel

Имена диапазонов ячеек

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

ПРИМЕР ПРИСВОЕНИЯ ИМЕНИ В EXCEL

Управляя магазином, мы хотим знать все о ценах на его товары. Почем покупаем, почем продаем. То есть нам нужен прайс с оптово-закупочными и розничными ценами, где определена наша прибыль. Для упрощения примера установим одинаковую наценку 10% на все товарные позиции.

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

Присвоим наценке имя и пока установим ее значение на уровне 10%, а потом будем изменять. Чтобы присвоить значению имя делаем следующее:

Выберите инструмент: «Формулы»-«Определенные имена»-«Присвоить имя».

Введите имя «Наценка1», а в поле «Диапазон» просто значение 10% так как указано на рисунке. При необходимости выберите область распространения имени (на конкретный лист или на целую книгу). И нажмите ОК.

Теперь нам нужно применить наше имя «Наценка1» в формулах.

При создании имен следует учитывать несколько простых правил:

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

Начиная со второго символа можно использовать: буквы, цифры, точки и знаки нижнего подчеркивания. Другие символы запрещено использовать (даже пробел).

Нельзя использовать пробелы.

Теперь мы без проблем можем изменять наценку, а формулы будут автоматически пересчитывать результат вычисления. Например, увеличим нашу наценку на 2%:

Выберите инструмент «Формулы»-«Диспетчер имен».

В диалоговом окне выберите «Наценка1» и нажмите на кнопку «Изменить». В поле «Диапазон» введите новое значение 12% (или 0,12). И нажмите ОК. После нажатия все формулы с именем «Наценка1» автоматически выполнять пересчет и выдадут новые результаты.

Примечание. Если формулы не пересчитаны значит у Вас в настройках Excel включена опция ручного пересчета формул («Формулы»-«Параметры вычислений»-«Вручную»), в таком случаи новые цены мы получим после нажатия на клавишу F9.

ПРЕИМУЩЕСТВА ИМЕН В EXCEL

При запуске программы нам сразу доступны для нашего взгляда: ячейки, листы, строки, столбцы, кнопки инструментов и много других элементов, только не имена. Чтобы их использовать до них нужно добираться лишь с помощью соответствующих инструментов. Большинство пользователей так и не решаться использовать, а вместо этого ищут решения более сложными путями. Очень жаль, ведь с помощью имен можно изящно решать большинство задач в Excel.

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

Как присвоить имя ячейке в excel

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

Присвоение имени ячейке

Введите имя в поле Имя.

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

Чтобы ссылаться на это значение в другой таблице, введите знак равенства (=) и Имя, а затем нажмите клавишу ВВОД.

Присвоение имен из выделенного диапазона

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

На вкладке Формулы нажмите кнопку Создать из выделенного

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

Нажмите кнопку ОК.

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

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

Выделите ячейку и введите формулу.

Поместите курсор туда, где вы хотите использовать в формуле имя.

Введите первую букву имени и выберите его из появившегося списка.

Или выберите Формулы > Использовать в формуле и выберите имя, которое хотите использовать.

Нажмите клавишу Ввод.

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

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

Присвоение имени ячейке

Введите имя в поле Имя.

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

Присвоение имен из выделенного диапазона

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

На вкладке Формулы нажмите кнопку Создать из выделенного

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

Выберите OK.

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

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

Выделите ячейку и введите формулу.

Поместите курсор туда, где вы хотите использовать в формуле имя.

Введите первую букву имени и выберите его из появившегося списка.

Или выберите Формулы > Использовать в формуле и выберите имя, которое хотите использовать.

Нажмите клавишу Ввод.

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

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

В Excel в Интернете вы можете использовать именованные диапазоны, которые вы определили в Excel для Windows или Mac. Выберите имя в поле «Имя» для перехода к расположению диапазона или использования именованного диапазона в формуле.

На данный момент создание нового именованного диапазона в Excel в Интернете недоступно.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Имена в EXCEL

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

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

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

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

Пример

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ВНИМАНИЕ! По умолчанию при создании новых имен используются абсолютные ссылки на ячейки (абсолютная ссылка на ячейку имеет формат $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 . В диалоговом окне Вставка имени щелкните на кнопке Все имена и начиная с активной ячейки по строкам будут выведены все существующие имена в книге, причем в соседнем столбце появятся соответствующие диапазоны, на которые ссылаются имена. Получив список именованных диапазонов, можно создать гиперссылки для быстрого доступа к указанным диапазонам. Если список имен начался с A 1 , то в ячейке С1 напишем формулу:

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

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

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

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

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

Как присвоить имя ячейке или диапазону в Excel

Excel предлагает несколько способов присвоить имя ячейке или диапазону. В рамках этого урока мы рассмотрим только 2 самых распространенных, думаю, любой из них обязательно пригодится. Но прежде чем мы увидим, как назначать имена в Excel, обратитесь к этому руководству, чтобы запомнить некоторые простые, но полезные правила для создания имени.

Используем поле Имя

Этот метод — самый быстрый способ присвоить имя ячейке или диапазону в Excel. Чтобы использовать его, выполните следующие действия:

1. Выделите ячейку или диапазон, которому хотите присвоить имя. В нашем случае это диапазон B2: B13.
2. Щелкните поле Имя и введите необходимое имя, следуя правилам, описанным здесь. Пусть это будет имя Sales_Months. alt=»Назовите его в Excel» />
3. Нажмите клавишу Enter, и имя будет создано.
4. Если вы щелкните раскрывающийся список поля «Имя», вы увидите все имена, созданные в этой книге Excel. В нашем случае это просто имя, которое мы только что создали. alt=»Назовите его в Excel» />
5. Например, давайте создадим формулу с названием «Ежемесячные продажи». Пусть это будет формула, по которой рассчитывается общий объем продаж за прошлый год: alt=»Назовите его в Excel» />
6. Как видите, если дать значимые имена ячейке или диапазону, к которому относится формула, формула станет намного понятнее.

Используем диалоговое окно Создание имени

Чтобы присвоить такое имя ячейке или диапазону, сделайте следующее:

1. Выберите нужную область (на этом шаге вы можете выбрать любую область, позже вы можете переопределить ее). Мы выберем ячейку C3, а затем переделаем ее заново. alt=»Назовите его в Excel» />
2. Перейдите на вкладку «Формулы» и выберите команду «Присвоить имя. alt=»Назовите его в Excel» />
3. Откроется диалоговое окно «Новое имя. alt=»Назовите его в Excel» />
4. В поле Имя введите желаемое имя. В нашем случае это название «Коэффициент». В некоторых случаях Excel автоматически заменяет имя на основе данных в соседних ячейках. В нашем случае так и произошло. Если в Excel этого не делал или такое имя вам не подходит, введите желаемое.
5. В раскрывающемся списке «Область действия» вы можете указать создаваемую область имен. Область действия — это область, в которой может использоваться сгенерированное имя. Если вы укажете книгу, вы можете использовать имя во всей книге Excel (на всех листах) и, если это конкретный лист, только внутри этого листа. Как правило, выбирается сфера применения — Книга.
6. В поле «Примечание» вы можете ввести пояснение к имени, которое вы создаете. В некоторых случаях это рекомендуется делать, особенно когда имен слишком много или когда вы ведете совместный проект с другими людьми. alt=»Назовите его в Excel» />
7. В поле Диапазон отображается адрес активной области, то есть адрес ячейки или диапазона, который мы ранее выбрали. При необходимости этот интервал можно перезаписать. Для этого поместите курсор в поле «Диапазон», и вокруг указанной области появится динамическая рамка. alt=»Назовите его в Excel» />Используйте мышь, чтобы выбрать новую область, или укажите эту область, введя диапазон непосредственно в текстовое поле. В нашем случае мы выберем ячейку D2. alt=»Назовите его в Excel» />
8. Если вас все устраивает, смело нажимайте ОК. Имя будет создано. alt=»Назовите его в Excel» />