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

2024 Автор: Geoffrey Carr | [email protected]. Последнее изменение: 2024-01-12 05:25

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

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

В приведенной ниже формуле мы ссылаемся на диапазон ячеек (выделенных жирным шрифтом) из другого листа, называемого «База данных продуктов», в той же книге. В этом случае имя рабочего листа дает нам представление о том, что содержится в диапазоне ячеек «A2: D7». Однако мы могли бы использовать имя для этого диапазона ячеек, чтобы сделать формулу короче и легче читать.


=IF(ISBLANK(A11),'',VLOOKUP(ALL,' Product Database'!A2:D7,2,FALSE))

ПРИМЕЧАНИЕ. Дополнительную информацию о функции VLOOKUP, используемой в приведенной выше формуле, см. В нашей статье об использовании VLOOKUP в Excel. Вы также можете узнать, как использовать функцию «IF» и другие полезные функции.

Как создать имя для ячейки или диапазона ячеек с помощью поля имени

Чтобы назначить имя диапазону ячеек, выберите ячейки, которые вы хотите назвать. Ячейки не обязательно должны быть смежными. Чтобы выбрать непересекающиеся ячейки, используйте клавишу «Ctrl» при их выборе.

Щелкните мышью в поле «Имя» над сеткой ячейки.

Введите имя для диапазона ячеек в поле и нажмите «Ввод». Например, мы назвали выбранные ячейки на нашей «Таблице продуктов» на листе «Продукты». Существуют синтаксические правила, которые вы должны соблюдать при выборе имени. Вы можете только начинать имя с буквы, подчеркивания ( ) или обратной косой черты (). Остальная часть названия может состоять из букв, цифр, периодов и символов подчеркивания. Существуют дополнительные правила синтаксиса о том, что действительно, а не при определении имен.

Помните формулу с начала этой статьи? В нем содержалась ссылка на рабочий лист «База данных продуктов» в рабочей книге и ряд ячеек на этом листе. Теперь, когда мы создали имя «Продукты» для представления диапазона ячеек на листе «База данных продуктов», мы можем использовать это имя в формуле, выделенной полужирным шрифтом.


=IF(ISBLANK(A11),'',VLOOKUP(ALL, Products,2,FALSE))

ПРИМЕЧАНИЕ. При создании имени с использованием «Ящика имени» область имени по умолчанию соответствует книге. Это означает, что имя доступно для использования на любом рабочем листе в текущей книге без ссылки на конкретный рабочий лист. Вы можете ограничить область видимости на конкретном листе, чтобы имя листа было использовано при обращении к имени, например, в примере в начале этой статьи.

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

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

В разделе «Определенные имена» на вкладке «Формулы» нажмите «Диспетчер имен».

Откроется диалоговое окно «Диспетчер имен». Чтобы изменить существующее имя, выберите имя в списке и нажмите «Изменить». Например, мы будем редактировать имя «Продукты».

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

ПРИМЕЧАНИЕ. Вы увидите, что раскрывающийся список «Сфера» неактивен. Когда вы редактируете существующее имя, вы не можете изменить «Область» этого имени. Вы должны выбрать область, когда вы сначала создадите имя. Если вы хотите, чтобы область была конкретным листом, а не всей рабочей книгой, вы можете создать имя таким образом, чтобы вы могли сначала указать область. Мы покажем вам, как это сделать в следующем разделе.

Например, мы добавили еще один продукт в нашу «База данных продуктов», и мы хотим включить его в диапазон ячеек под названием «Продукты». Когда мы нажимаем кнопку «Развернуть диалог», диалоговое окно «Изменить имя» сокращается, чтобы содержать только поле «Относится к». Мы выбираем диапазон ячеек непосредственно на листе «База данных продуктов», включая строку, содержащую недавно добавленный продукт. Имя рабочего листа и диапазон ячеек автоматически вводятся в поле «Относится к». Чтобы принять ваш выбор и вернуться к полному диалоговому окну «Редактировать имя», нажмите кнопку «Свернуть Диалог». Нажмите «ОК» в диалоговом окне «Редактировать имя», чтобы принять изменения в имени.

Как удалить имя с помощью диспетчера имен

Если вы решите, что вам больше не нужно имя, его легко удалить. Просто перейдите в диалоговое окно «Диспетчер имен», как мы обсуждали в предыдущем разделе.Затем выберите имя, которое вы хотите удалить, в списке имен и нажмите «Удалить».

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

Как создать имя с помощью диалогового окна «Новое имя»

Когда вы создаете новое имя, выбирая одну или несколько ячеек, а затем вводите имя в поле «Имя», область имен по умолчанию - это вся книга. Итак, что вы будете делать, если хотите ограничить область имени только определенным листом?

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

ПРИМЕЧАНИЕ. Вам не нужно сначала выбирать ячейки. Вы также можете выбрать их, используя кнопку «Развернуть диалог» позже, если хотите.

Откроется диалоговое окно «Новое имя». Обратите внимание, что это очень похоже на диалоговое окно «Изменить имя», упомянутое ранее. Основное различие заключается в том, что теперь вы можете изменить область имени. Предположим, мы хотим ограничить область действия имени только таблицей «Счет-фактура». Мы сделали бы это, если бы хотели иметь одно и то же имя для ряда ячеек на другом листе.

Сначала мы введем имя, которое хотим использовать, в нашем случае это «Продукты». Помните правила синтаксиса при создании своего имени. Затем, чтобы ограничить область названия «Продукты» только листом «Счет», мы выбираем его из раскрывающегося списка «Область».

ПРИМЕЧАНИЕ. Диалоговое окно «Новое имя» также можно получить, нажав «Создать» в диалоговом окне «Диспетчер имен».

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

Имя автоматически вставляется в одно и то же «поле имен», которое мы использовали для присвоения имени диапазону ячеек в начале этой статьи. Теперь мы можем заменить ссылку на диапазон ячеек («База данных продуктов»! $ A $ 2: $ D: 7) с именем (Продукты) в формулах на листе «Счет-фактура», как это было сделано ранее в этой статье.

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

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

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

Чтобы создать имя, которое будет присвоено постоянному значению, откройте диалоговое окно «Новое имя», щелкнув вкладку «Формулы», а затем нажав «Определить имя» в разделе «Определенные имена». Введите имя для представления постоянного значения, например «ExchangeRate». Чтобы присвоить значение этому имени, введите знак равенства (=) в поле «Относится к», а затем значение. Между значком равенства и значением не должно быть пробела. Нажмите «ОК», чтобы завершить создание имени.

ПРИМЕЧАНИЕ. Если в вашей книге есть формула, которую вы используете во многих местах, вы можете ввести эту формулу в поле «Относится к», чтобы вы могли просто ввести имя в каждую ячейку, где вам нужно использовать формулу.